Creating local cubes from relational sources using XMLA

As a follow-up to my recent post on creating local cubes from server cubes with XMLA, I’ve just been asked on the newsgroup about options for creating local cubes direct from relational sources without going via a server cube. Since you can’t use the CREATE GLOBAL CUBE syntax to do this, you’ve got two options: use the old CREATE CUBE syntax (which is very unpleasant and, I believe, only creates ‘AS2K’ local cubes and not the new-improved ‘AS2005′ local cubes which seem to perform so well and have all the cool new features) or use XMLA – which seems to be the best choice by default. Here are the steps to get the XMLA needed to create your local cube:
  • Design a server database/cube (not using any features like partitions or distinct count, which aren’t supported in local cubes) in BIDS. Deploy it but don’t process it.
  • In SSMS script the database you’ve just created to a new query editor window.
  • Still in SSMS, right-click on the database again and select Process, select ‘Process Full’ then instead of clicking OK click the Script button and choose to script to a new query editor window.
  • You now have the two XMLA commands you need to create and process your local cube, you just need to combine them into one. Select all of the Create statement generated when you scripted the database and paste it into the Batch statement generated from the Process dialog, immediately after the first line (containing the ‘Batch’ command) and before the XMLA that actually does the processing.
  • Copy the new statement, open up MDX Sample app, connect to the path of the new local cube file, paste the statement into MDX Sample app and then run it. And voila, your local cube will be built direct from the relational data source.

 

15 thoughts on “Creating local cubes from relational sources using XMLA

  1. Is there any to to make a job, which rums automatically every day?
    Which Control Flow Item have I to use to build the job in BIDS?

  2. I\’ve realized theses steps…. – and now I\’ve a "big script".
    But I use SQLServer2005 Analysis Services with the Business Intelligence Management Studio…
    I\’ve no .cub-file created… but I\’ve only the scripts… I\’ve no MDX Sample app
    Now my question…. how can I run theses scripts against a file which does not exists… – and without the mdx sample application…

    You now have the two XMLA commands you need to create and process your local cube, you just need to combine them into one. Select all of the Create statement generated when you scripted the database and paste it into the Batch statement generated from the Process dialog, immediately after the first line (containing the \’Batch\’ command) and before the XMLA that actually does the processing.
    Copy the new statement, open up MDX Sample app, connect to the path of the new local cube file, paste the statement into MDX Sample app and then run it. And voila, your local cube will be built direct from the relational data source.

  3. Sorry for the late reply…
     
    OK, if you don\’t have access to MDX Sample app you\’ll have to do some coding. All you need to do is open a connection to where you want the cub file created – it doesn\’t matter that it doesn\’t exist, it\’ll get created when you run the query – and then execute your XMLA statement. Another alternative might be to use the ascmd tool that\’s available with the SQL2005 code samples from SP1 onwards – for more information see here:
    http://msdn2.microsoft.com/en-us/library/ms365187.aspx
     

  4. I\’m not sure it\’s supported, but when you connect to a local cube to query it you supply the path to the .cub file rather than an instance name.

  5. Hi Chris,
    I have looked at your example in creating local cubes. I´m trying to following your instructions but I think that the syntax or the code is incorrect somehow.
    CREATE GLOBAL CUBE statement. FILENAME|C:\\myCube.cub|DDL|
    <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"&gt;
      <Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"&gt;
      <Parallel>    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema&quot; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  </Parallel>
    </Batch>

  6. Are you trying to execute that snippet? The first line is going to be the problem: 
    CREATE GLOBAL CUBE statement. FILENAME|C:\\myCube.cub|DDL|
     
    Delete that and try again – it\’s just a bit of information put there by AS, it isn\’t XMLA.

  7. I realize the steps but still have problems.
     
    1. I have my batch statement.
    2. Start Management Studio and connect to Analysis Services.
    3. Then I click on Analysis Services XMLA query and writing c:\\test.cub in the server name field writing.
    4. When I run the query the following error message appears though the AS server is running:
    Executing the query …A connection cannot be made. Ensure that the server is running.Execution complete
     
     

  8. Hi Chris,
     
    I have the following problem.
     
    I have designed a cube, wich have scope statements. The scope statements make possible to do my calculations in the cube. Now i tried to create a local cube using the MDX syntax CREATE GLOBAL CUBE.
    CREATE GLOBAL CUBE [LocalBM20_CO]
    Storage \’C:\\BM20_CO(local).cub\’
    FROM [BM20_CO]
    (
    MEASURE [BM20_CO].[KPI Value],
    DIMENSION [BM20_CO].[KPI],
    DIMENSION [BM20_CO].[Company],
    DIMENSION [BM20_CO].[Time],
    DIMENSION [BM20_CO].[Source]
    )
    Then I open the offline cube with excel, but my calculations with the scope statement are not there….
    Do you have some idea, how I can make local cube with scope statements and calculations?
    Thanks,
    Ivo Manolov

  9. Hi Ivo,
     
    My guess is that what\’s happening is that the calculations are there, but they\’re failing somehow. Do they reference dimensions and measures that aren\’t in your local cube? If they do then that would be why you can\’t see them.
     
    Chris

  10. Hi Chris,
     
    in the scope statement i have calculations based on distinct count measure. I think, thats the problem. Is it possible to create offline cube with distinct count measures? for example, if i choose xmla?
     
    thanks,
     
    Ivo
     
     
     

  11. Hi chris,
    i have been trying to populate an SSAS 2005 cube. i dont think its possible using mdx( am i rite?) so tried to do it using XMLA. but couldnot come across any materail where i ud learn much about it. i want to poupulate my cube using a SQL statement. is it possible using XMLA, may be through the command line thing in XMla?  i dont have much idea about Xmla, so can u please suggest 2 things.
    First, is my requirement possible through xmla and sql, if not then is there any way to do it.
    Second, if it is possible, then please suggest some source where i can find proper material to learn xmla or whatever required for this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s