none
Error while creating the offline .cub file

    Question

  • Hi All,

    I am getting following error when I try to create the offline .cub file from excel


    ---------------------------

    Microsoft OLE DB Provider for Analysis Services 2005 :
    OLE DB error: OLE DB or ODBC error: XML for Analysis parser: The 'CreatedTimestamp' read-only element at line 1, column 6570 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Cubes/Cube/Scripts/MdxScript was ignored.; XML for Analysis parser: The 'LastSchemaUpdate' read-only element at line 1, column 6626 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Cubes/Cube/Scripts/MdxScript was ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 88, column 722 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 88, column 6613 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 88, column 10736 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 88, column 16649 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; The ddl2:MemberKeysUnique element at line 88, column 17211 (namespace http://schemas.microsoft.com/analysisservices/2003/engine/2) cannot appear under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension/Hierarchies/Hierarchy..
    ---------------------------


    I am connecting to AS cube using the http connection, I can browse the cube properly, but when I go to create the .cub file I get the above error.
    Can anybody help on this??

    Also when I browse the cube locally on my machine and create the .cub file, it has no issues. It creates the .cub file which I can later use to work in offline mode.

    Please help.
    Thanks in advance
    Tuesday, October 6, 2009 2:42 PM

Answers

All replies

  • Guys,

    Have anybody come across such issue?

    Please guide me.

    Thanks
    Wednesday, October 7, 2009 7:15 AM
  • Hi All,

    Can some body guide me what are the ways to create the off-line cubes?

    I tried creating one using the excel by setting http connection to AS db, but wasn't successful.

    Can anybody help??

    Thanks in advance
    Wednesday, October 7, 2009 12:31 PM
  • Hi XLP_Clarion,
    I tried the steps to 'Create an offline cube file from an OLAP server database' on http://office.microsoft.com/en-us/excel/HP100870401033.aspx using Excel 2007 and it works like a charm:
    1. Open Excel, Date menu, Data, From Other Sources, Analysis Services, specify server name, specify AS DB, specify connection name, finish.
        Pivot table created.
    2. Options menu, OLAP tools, Offline OLAP, Create offline data file, select the attributes, specify the measures and members, specify output file, finish
        The offline cube file is created (file.cub)
    3. After you finish Create Cube File wizard and back on the Offline OLAP setting dialog, click OK to specify that you are using Offline cube not online cube.
        (important - I missed this first time round)
    4. Create Pivot Table in Excel as normal. We only see the attribute and members we specify on point 2.
    5. Save the Excel file.
    We can now open the Excel file and
    a) Edit the offline cube (specify different attributes/members/measures) and produce a new offline cube file.
    b) Modify the pivot table.
    c) Switch back to 'online OLAP mode': Options menu, OLAP tools icon, click On-line OLAP, OK.
    HTH,
    Vincent

    Wednesday, October 7, 2009 3:17 PM
  • Hi Vincent,

    Thanks for reply.

    Well - even I was able to create off-line cube when I directly connect to AS server which is locally installed on my machine.

    However issue arises when I use http connection to connect to AS cube which is on server.

    Please have a look at this thread.
    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/8d1f618b-eac0-47e4-967b-7740fda476dc


    Also do you have any resources related to ASSL? Please let me know if you have.

    Thanks
    Thursday, October 8, 2009 6:33 AM
  • Hi,

     

    I found a similar known issue through bugcheck. There is known issue when creating offline cube even if you are using SSMS (2005), and this issue has been fixed. So could you install the latest server pack for test? If this issue still exists after installed the latest SP, please connect product group through the below link to report the issue:

    https://connect.microsoft.com/SQLServer/

     

    Thanks,

    Raymond

    Thursday, October 8, 2009 7:57 AM
    Moderator
  • Hi Raymond,

    Thanks for the reply.

    By latest server pack do you mean SQL Server 2005 Service Pack 3?

    I have that installed on my server.

    Any Ideas?

    Thanks
    Thursday, October 8, 2009 8:47 AM
  • Hi XLP,
    I managed to configure http connection (following Edward Melomed's notes on MSDN) on local PC (local IIS, local SSAS, Integrated Windows authentication) and managed to connect from Excel to http://localhost/olap/msmdpump.dll as SSAS data source. I could create Pivot Table on Excel, created the off-line .cub file, and can browse the .cub file using Excel PivotTable. It's a simple Sales cube with Product and Time dim, and only 1 measure.

    The error message on the link that you mentioned is:
    The 'CreatedTimestamp' read-only element under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Cubes/Cube/Scripts/MdxScript was ignored. Same with LastSchemaUpdate and CurrentStorageMode.
    I think we need to see the MDX Script on the calculation tab of the cube, search for CreatedTimestamp. My guess is that there are certain MDX functions/expressions/syntax which can't be used on off-line cube. A structured way to troubleshoot this warning/error is to remove the calculated measures/members from the cube, rebuild and try creating the offline cube again. Probably just pick 1 dim and 1 measure -- the simpler the structure the better. If this simpler offline cube file can be browsed by Excel, then try a more complex one. Eventually we'll find out which calculation generate the error message.

    Not sure what you mean by resources. If you meant online resources, the ASSL reference is on http://msdn.microsoft.com/en-us/library/ms128525(SQL.90).aspx. If you meant books, I don't think there is one specificly covering ASSL (XMLA + DDL). From memory I think Irina, Edward & Alexander's Microsoft SQL Server 2008 Analysis Services Unleashed mentioned ASSL little bit. I think in chapter 2 or 3 they explained several XMLA elements related to the anatomy of AS database, i.e. cube, dimensions, etc.

    Vincent

    Thursday, October 8, 2009 4:05 PM
  • What it the version of SQL Server? select @@version

     

    And what is the version of OLE DB provider in your client? Did OLE DB provider version match the SQL Server version? You can check the msmdlocal.dll at %ProgramFiles%\Common Files\System\Ole DB\.

     

    It seems the OLE DB provider version much older than the SP3, you may need to update the MSOLAP provider to a new version.

     

    Hope this helps,

    Raymond

    Friday, October 9, 2009 1:59 AM
    Moderator
  • Hi Raymond,

    Thanks for the reply.

    I updated the OLEDB provider and the error is gone now.

    But there's a new error

    Microsoft OLE DB Provider for Analysis Services 2005 :
    OLE DB error: OLE DB or ODBC error: The following system error occurred:  No such host is known. .; Either a connection cannot be made to the SERVER-NAME server, or Analysis Services is not running on the computer specified.; Server: The operation has been cancelled..


    Can you help??

    Thanks
    Friday, October 9, 2009 6:55 AM
  • Where did you see this error? When you deploy the project or connect to Analysis Services? If I remember correctly, you are using HTTP access to SSAS, right? If so, check the provider on the IIS computerm. if necessary, update that provider. This error generally indicates that the Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider is not installed on the IIS computer. To verify that the 9.0 OLE DB provider is installed on the IIS computer

     

    More information about Troubleshooting Steps:

    http://technet.microsoft.com/en-us/library/cc917670.aspx

     

    Hope this helps,

    Raymond

    Friday, October 9, 2009 7:16 AM
    Moderator
  • I got this error while trying to create the off-line cube from excel from client machine.
    I installed the latest OLEDB provider on client as per your suggestion.

    I will check the settings on server and IIS, mean while please tell me if you have thoughts.

    Will post the findings ASAP.

    Thanks
    Friday, October 9, 2009 7:32 AM
  • Hi Raymond,

    I installed the latest Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider on server


    But even that didn't seem to help :(
    Both on server and on client I installed same version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider on server

    Can you help?

    Thanks


    Friday, October 9, 2009 11:40 AM
  • Hi XLP_Clarion,

    I cannot confirm what's the root cause of this issue, sorry to say that. I got a feekback from one member of product group, it seems it due to a known issue somewhere "I'm pretty sure that this is not supported due to a known issue. The DDL for the local cube is constructed on the server and then sent back to the client and executed on the client – but the server only knows its "name\instance" information. Since it doesn't know that the client connected to it using a URL, it generates a data source that contains "name\instance" instead of the URL – and therefore the local cube cannot re-connect back to the server to actually fetch the data using the data source." I suggest you open a support case through MIcrosoft customer support services or connect the product group through the offical link I posted above. Or someone else came across such issue can give you other suggestions. 

    Also, please post the link here if you open a case through:
    https://connect.microsoft.com/SQLServer/

    Regards,
    Raymond

    Friday, October 9, 2009 7:04 PM
    Moderator
  • Hi Raymond,

    Can you please give me the link where to open the case?

    I am trying to search it in connect site but not able to find till now.

    Thanks
    Monday, October 12, 2009 8:40 AM
  • You can find it here:
    Submit Product Feedback

    Send your feedback directly to our development teams, or vote on issues already reported to help determine community priorities.

    Regards,
    Raymond

    Monday, October 12, 2009 8:48 AM
    Moderator
  • Monday, October 12, 2009 9:03 AM