How to create a local cube file (.cub) using CREATE GLOBAL CUBE within a SSIS 2005 package? RRS feed

  • Question

  • Can anyone provide a simple example of how to create a local cube using CREATE GLOBAL CUBE in Integration Services? I am trying to automate the creation of local cubes, so manually running the statements is not an option.

    Consider the following often quoted example from Microsoft:
    CREATE GLOBAL CUBE [LocalReseller]
       Storage 'C:\LocalAWReseller1.cub'
       FROM [Adventure Works]
          MEASURE  [Adventure Works].[Reseller Sales Amount],
          DIMENSION [Adventure Works].[Reseller],
          DIMENSION [Adventure Works].[Date]

    I know the source cube exists on my server and is processed. I can browse it and see data.

    I can run the example successfully in an MDX query in Management Studio.  The .cub file is created, can be opened and manipulated using Excel. So far so good.

    By the way, I have written CREATE GLOBAL CUBE statements for my project specific local cubes and have successfully created them in Management Studio. Writing the Create statements is not an issue.

    Now when it comes to creating a local cube using an SSIS package, I hit a brick wall.
    I’ve read that you can use Execute SQL Task or the Analysis Services Execute DDL Task, but I can’t find any complete examples and can't get anything to work.

    I’ve created an Execute DDL Task with a connection to the Analysis Services Adventure Works DW  database on my server and put the Create Global Cube example in SourceDirect (with SourceType=Direct Input). The package won’t validate as a “DDL is not valid.” message is returned. Ok, either the Create statement can’t be used or I have used the wrong code. I tried wrapping the Create statement with <Statement> </Statement>. This fails execution, but the error message includes [Analysis Services Execute DDL Task] Error: CREATE GLOBAL CUBE statement. FILENAME|C:\LocalAWReseller1.cub|DDL|<Batch xmlns="http…. I have truncated the remainder of the message.

    Then I created the Analysis Services database LocalReseller on my Analysis Services server and put the code <Batch xmlns="http….  as the SourceDirect in an Execute DDL Task with the connection set to my Analysis Services server, it creates the cube LocalReseller on the server in the LocalReseller database. Well, ok, this makes sense because my connection is pointing to the server. Moreover, the code that was returned in the error message appears to be valid. I haven’t been able to change the connection so that a local cube file is created rather than a server based cube.  How do you maintain a connection to the server based source cube and still have SSIS know to create a local cube file not a cube on the server?

    Is there a relatively simple way to execute the Create Global Cube statement in an SSIS package? If so, could someone please provide me with the details I am missing.



    Wednesday, February 18, 2009 9:39 PM

All replies

  • Hi Ron,

    The ascmd command-line utility lets you run MDX queries from the command line. So, this could be working for you in a 'Execute process task' in SSIS.
    See where to get it.

    Egon Rijk
    Monday, February 23, 2009 9:05 AM
  • Hi Egon,

    Have you used ascmd yourself to run MDX?

    I have a batch file called mybat.bat:
    "C:\Program Files\Microsoft SQL Server\90\...\ascmd" -S localhost -d "Adventure Works DW" -i CreateGlobalCubeMDX.txt

    which includes the full path to ascmd.exe

    CreateGlobalCubeMDX.txt contains:

    Storage 'C:\MyLocalCube.cub'
     FROM [Adventure Works]
     (    MEASURE  [Adventure Works].[Reseller Sales Amount],
     DIMENSION [Adventure Works].[Reseller],
         DIMENSION [Adventure Works].[Date]

    I can run the Create Global Cube statement by cutting and pasting it into a MDX query window in Management Studio. The local cube is produced as expected. So I know the MDX statement is valid; the source database and cube exist and is processed.

    When I run the batch file or put the create statement directly on the ascmd command line with the -Q option, I get:

    Microsoft (R) Analysis Services 2005 Command Line Tool
    Version MSIL
    Copyright (C) 2006 Microsoft Corporation.  All Rights Reserved.
    <return xmlns="urn:schemas-microsoft-com:xml-analysis"><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"><Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" /><Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception"><Error ErrorCode="3238658247" Description="CREATE GLOBAL CUBE statement. FILENAME|c:\mylocalcube.cub|DDL|&lt;Batch xmlns=&quot;; xmlns:ddl2=&quot;; xmlns:ddl2_2=&quot;; xmlns:xsd=&quot;; xmlns:xsi=&quot;;&gt;&lt;Create AllowOverwrite=&quot;true&quot;&gt;&lt;ObjectDefinition&gt;&lt;Database&gt;&lt;ID&gt;mycube&lt;/ID&gt;&lt;Name&gt;mycube&lt;/Name&gt;&lt;Cubes&gt;&lt;Cube&gt;&lt;ID&gt;mycube&lt;/ID&gt;&lt;Name&gt;mycube&lt;/Name&gt;&lt;ScriptErrorHandlingMode&gt;IgnoreAll&lt;/ScriptErrorHandlingMode&gt;&lt;MeasureGroups&gt;&lt;MeasureGroup&gt;&lt;ID&gt;Fact Reseller Sales&lt;/ID&gt;&lt;Name&gt;Reseller Sales&lt;/Name&gt;&lt;Description/&gt;&lt;Source xsi:type=&quot;MeasureGroupBinding&quot;&gt;&lt;DataSourceID&gt;Adventure Works DW&lt;/DataSourceID&gt;&lt;CubeID&gt;Adventure Works DW&lt;/CubeID&gt;&lt;MeasureGroupID&gt;Fact Reseller Sales&lt;/MeasureGroupID&gt;&lt;Persistence&gt;All&lt;/Persistence&gt;&lt;/Source&gt;&lt;IgnoreUnrelatedDimensions&gt;true&lt;/IgnoreUnrelatedDimensions&gt;&lt;Dimensions&gt;&lt;Dimension

    and 267 KB more of xml like code

    A search for ErrorCode="3238658247" failed to return anything.

    The ascmd utility is being invoked. I can tell that much. If I run "ascmd -?", I do get help information, so I believe the ascmd file compiled ok.

    Any advice?
    Monday, February 23, 2009 6:10 PM
  • Hi Ron,

    No, I haven't used ascmd to create local cubes that way. But if you google on ascmd and create local cube you should find some references.
    Egon Rijk
    • Marked as answer by Tony Tang_YJ Thursday, March 5, 2009 7:09 AM
    • Unmarked as answer by Ron E T Wednesday, March 18, 2009 8:19 PM
    Tuesday, February 24, 2009 8:10 AM