In the last few days I've been doing alot of work on processing SQL Server Analysis Services (SSAS) 2005 cubes, measure groups and partitions and I thought I'd share some of my experiences here.
1. First up - I'm a huge fan of XMLA. The ease with which one can now carry out and customise SSAS administration tasks (including processing) has increased tenfold since SSAS2000. I did encounter one or two problems though - more on this later.
2. The multitude of processing options now available provides far greater flexibility for processing your cubes. Hopefully I'll find time to put together a more detailed synopsis of this later.
3. ascmd.exe - A fantastic tool for querying SSAS cubes and issuing XMLA commands from the command-line which negates the previous reliance on the feature-blown alternative (i.e. SSIS). I've been using ascmd.exe to automate processing of cubes from DTS 2000 - more to come on that in the very near future. Kudos to Dave Wickert and his cohorts for coming up with ascmd.exe. Download the project for ascmd.exe from here.
4. All design-time information about a cube is stored in a .cube file. All, that is, except for information about partitions which is stored in a seperate .partitions file. The reason being that putting all the partition info into the .cube file would cause it to take longer to open up in the designer.
There is a <Partitions> collection element within the <MeasureGroup> element in the .cube file so as Adrian Dumitrascu says here don't rule out the possibility of this behaviour changing in the future.
5. So that's the good, informative and useful stuff...now onto the not-so-good stuff. The cube I am working on contains multiple measure groups and within those measure groups, multiple partitions. My partitioning strategy is to have a partition for every year's activity up to 2015 - the reason being that I don't want to process the whole cube every night - I only want to process data for the current year.
Here is an illustration of how one of my measure groups is partitioned up:
I now have a requirement to dynamically build an XMLA command at runtime that will process the partition for the current year. On the surface it seemed like a fairly easy thing to achieve but there were a few traps lying in wait for me.
As you can see from the illustration above I followed a strict naming convention for my partitions i.e. My partition name is the name of the measure group (spaces replaced by underscores) concatenated with the year for which the partition holds data. Knowing that, it should be pretty easy to construct the XMLA command because its just a matter of concatenating the current year onto the name of the measure group right??? Wrong!
It turns out that the XMLA Process command (which is used to process SSAS objects) does not accept partition names as arguments. In fact, it doesn't accept ANY object names. All object references have to be made via the immutable object IDs that are assigned to the object when it is created.
I have to be honest and say I think this is a bit amateurish. When do we ever reference entities by ID rather than name in ANY discipline such as this? We use the name because that's the mutable property so that we can make it something meaningful. My requirement to dynamically generate the XMLA command is a legitimate one but I am prevented from doing it because I can't use the object names in the Process command. I hope that this changes in Katmai!
In the end my workaround was a nasty one - I hacked the underlying XML in my .cube and .partition files so that the object IDs followed the same naming convention as the object names. This is dangerous because
a) You have to be aware of dependancies on the objects that you are changing
b) There is much more scope to make mistakes. I made one myself - a copy and paste mistake meant that I was no longer able to see my partitions within BIDS. Thankfully I had backed up my project previously so I was able to rollback
c) You are putting real-world meaning into your IDs and that is not what they are for.
I've complained about this issue here and I'm hoping that people will click-through and vote, comment and agree with me. I really raelly don't like this behaviour.
One aside to this - the default object IDs usually bear a resemblance to the underlying table/column in the DSV that the object was initially created from. Remember that IDs are immutable but there is nothing to stop a cube developer from changing the object to use a different table/column from the DSV. This means that someone who is not au fait with this peculiarity could easily look at the deployed cube objects IDs and make false assumptions about where the data is coming from. I was guilty of this myself only recently. For this reason I would much prefer object IDs to be GUIDs just as they are in SSIS - that way there is no ambiguity.
I've suggested this at Microsoft Connect here.
6. CTRL-F5 does not parse XMLA commands in a SSMS query window like it does SQL and MDX queries. I have requested this be changed here.
7. Its not really possible to see certain pertinent information about a deployed cube from within SSMS. For example:
- There is no indication in the UI as to whether an object is processed or not. This is a bit of a step back from SSAS2000.
- We don't know the ID of an object without scripting it out and as I mentioned above, you need the ID in order to write a Process command
- We don't know when an object was last processed
Personally I think the criminally underused 'Summary' tab in SSMS could be used to display this information and it seems as though someone agrees with me.
Any comments on any of this? I'd like to see some feedback - see if people agree or not.
-Jamie