XMLA – Steve Novoselac's Blog

SSAS 2005/2008: Creating Sub-Cubes Using XMLA, Variables, and Named Query Where Statements

I blogged a few weeks ago about creating Local Cubes with XMLA and ascmd.exe, and that is pretty cool, works great. There are some snags though if you use a server that is 64 bit. You can’t connect to the local cubes via .NET, Excel, or any other way. There are no drivers for local .cub files for 64-bit Windows. What a downer, and I am not going to wait around for them to come out. So in the mean time, you can easily create “sub cubes” as well in SSAS.

One easy way to create sub cubes is using this method. If you have one dimension that all your measures relate to (or two, usually they all relate to date), but say one dimension that relates to everything is Location, DimLocation. And you want to create sub cubes based on a set of locations. Well what I did was this.

1) In my Data Source View (.dsv) in my SSAS solution, I replaced all views/tables with Named Queries. (you will see why later)

2) Once I have my “main” cube deployed (and processed if you like, doesn’t matter), I need to create an XMLA to create a sub cube.

3) In the newly created XMLA you have to edit some things

a) I edit the Database name, and Id

b) search for msprop:QueryBuilder="SpecificQueryBuilder" – this is where your named queries for all your tables are. You can go ahead and add a where statement to each one (eg: WHERE LocationId IN (1,2,3))

c) I usually wrap the whole XMLA in a Batch, and then at the bottom after the </Create> I put a process XMLA

    ProcessFull

4) Run your XMLA and it should create and process a new SSAS db, creating a pre-sliced version of your original cube.

Now, there are other ways to do all this stuff, one of them being <Filter> in XMLA, but I couldn’t get it to work the way I wanted, that is why I went this route, and it just so happens that I am lucky enough for one dim to relate to all measures 🙂

Microsoft BI: Creating Local OLAP Cubes using XMLA and ascmd.exe

Most people, when using OLAP cubes, are hitting the live version that is located on SQL Server Analysis Services (SSAS). They hit it with Excel, or some other reporting tool, etc. I have blogged previously about offline cubes and how you could possibly use them.

Now, the blog I did before, I talked about creating them with Excel, and that works great for end users. But what about automating the process? What about filtering dimension members, or measure groups, etc? Now that you can use the Panorama Gadget for Google Apps/iGoogle (http://google-pivot-tables.blogspot.com/2008/04/panorama-analytics-gadget-for-google.html) you can upload .cub files and do BI in the cloud, how cool is that!

Well, one option is purchase CubeSlice – http://www.localcubetask.com/ and use that to create your .cub files. CubeSlice works great, and is a good option if you want something with a ton of options and ease of use.

You can also create .cub’s using CREATE GLOBAL CUBE syntax in MDX, and you can also use XMLA to create a .cub. Options galore! Chris Webb blogged a few years about about using XMLA to create .cub’s here – http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!877.entry He was using the MDX Sample App (I believe that comes with SQL 2000?)

What I was looking for was an easy way to us GLOBAL CUBE or XMLA and automate it, CubeSlice is nice, but there has to be a way to replicate (at least in a basic way) what they are doing. Thinking about XMLA – as to me it seems more powerful, that was the route I chose. I didn’t want to install the MX Sample App from SQL2K, and what Chris Webb says in his blog, and other things I read, basically the problem with using XMLA to create you .cub in SQL Server Management Studio is that you can’t specify a connection string, you have to connect to an SSAS instance. Using the MDX Sample App, you can specify a local file, and then run the XMLA and it will create the .cub file. So I just need to replicate that.

I have also blogged about using ascmd.exe here . ascmd comes with the SSAS Samples with SQL 2005 (usually located here: C:Program FilesMicrosoft SQL Server90SamplesAnalysis ServicesAdministratorascmd) . You just need to compile it and you can use it to execute XMLA. So I decided to try that. I created an XMLA file to create my cube, and then executed it with ascmd.exe

ascmd -S c:MyOfflineCube.cub -i c:MyOfflineCube.xmla

In seconds, you have an offline cube. Now, in your XMLA you can have filters, etc. You could create an XMLA file that has some kind of variable, and you could create a replace task to replace that with what you wanted filtered, think maybe based on location, or employee, etc, and then kick out personal cubes for users, etc.

One thing I didn’t really get into is how to get your XMLA script. You could figure it out for yourself, but I actually just used CubeSlice for this example. You can create an offline cube with CubeSlice and actually see the XMLA code and use that. Maybe creating the XMLA script manually/yourself would be good info for another blog post 🙂