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.
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?
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.
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
Hi Chris, how do run ascmd against a local cube? Dont you have to supply an instance and databasename? Thanks Tuan
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.
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">
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> </Parallel>
</Batch>
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.
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
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
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
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
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
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.
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.