Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Creating local cubes from relational sources using XMLA

with 15 comments

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.

 

Written by Chris Webb

September 25, 2006 at 11:20 am

Posted in Analysis Services

15 Responses

Subscribe to comments with RSS.

  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?

    Miroslav

    November 5, 2006 at 9:05 am

  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.

    Unknown

    November 13, 2006 at 10:51 am

  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

     

    Chris

    November 13, 2006 at 9:04 pm

  4. Hi Chris, how do run ascmd against a local cube? Dont you have to supply an instance and databasename? Thanks Tuan

    Unknown

    April 24, 2007 at 11:48 pm

  5. 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.

    Chris

    April 25, 2007 at 8:54 am

  6. 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>

    Inmon

    August 21, 2007 at 10:33 am

  7. 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.

    Chris

    August 22, 2007 at 4:34 pm

  8. 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
     
     

    Inmon

    August 23, 2007 at 8:57 am

  9. 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

    Unknown

    April 28, 2008 at 2:02 pm

  10. 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

    Chris

    April 30, 2008 at 10:32 am

  11. 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
     
     
     

    Unknown

    May 5, 2008 at 3:20 pm

  12. Ah, ok, yes I think that\’s the problem – I\’m fairly sure that distinct count measures are not supported in local cubes. Perhaps you can redesign your cube so that instead of using a distinct count measure you use a many-to-many dimension in the way Marco Russo describes in this paper: http://www.sqlbi.eu/LinkClick.aspx?fileticket=rCRqK7Qtrqc%3d&tabid=36&mid=375&language=en-US

    Chris

    May 6, 2008 at 10:17 am

  13. 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.

    Siddhartha

    September 15, 2008 at 6:30 am

  14. It is possible to create a local cube in MDX, using the CREATE GLOBAL CUBE statement (see several examples on this page). But it doesn\’t always work properly, so using XMLA and SQL is a good alternative. I would recommend you take a look at http://www.localcubetask.com/infocntr.htm for some good articles on how to do this.

    Chris

    September 16, 2008 at 1:56 pm

  15. [...] kein SSAS Server notwendig. Man braucht das XMLA von der SSAS Datenbank. Chris Webb hat das schon hier beschrieben. CubeSlice, Bi-Lite, OlapCube und weitere BI Anbieter nutzen diese Funktionalität. Der [...]


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

Follow

Get every new post delivered to your Inbox.

Join 3,190 other followers

%d bloggers like this: