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 🙂
Steve Novoselac's Blog 
3 replies on “SSAS 2005/2008: Creating Sub-Cubes Using XMLA, Variables, and Named Query Where Statements”
You're scripting the cube…msprop:QueryBuilder=”SpecificQueryBuilder” is part of the DSV script. You write that you changed your DSV to named queries but you really never state why. This post is a bit confusing…I know what you're tyring to do, but the steps involved lack detail. I've tried to ask specific questions but posting to this blog has been challenging to say the least.
LikeLike
In looking at this further, instead of scripting the cube you should script the database. For example, if you just scripted the cube, you will not find “msprop:QueryBuilder=”SpecificQueryBuilder” within the script as this relates to the DSV and not the cube. If you could revisit this blog and clarify it's content that would be helpful to others trying to accomplish this. Scripting the database is where the named queries come in relating to the DSV. However it should be noted that you shouldn't save the DSV on the AS side if you want to keep your original solution in it's orginal state.
LikeLike
yeah that is what i do, script the database
LikeLike