Month: December 2012

Changing database ID in Analysis Services

Every object in SSAS database, such as a Cube, a Measure Group, a Measure and a Dimension, has an ID which is generated automatically when the object is created. The ID, once generated, cannot be changed from the Properties window of the object; it is read-only. When an object is created,  by default, object name and it’s ID are same, however, over a period, objects get renamed leaving the ID with old value. In majority of situations, this does not create a problem but there are a few cases when this can be problematic. Now some people might say I have an OCD (Obsessive Compulsive Disorder) but I like my object names and IDs to be consistent.For most of the objects, the ID can be changed by opening the object code (right click–> View Code) and changing the value in <ID> tag.  A word of warning though, be extremely careful when changing the IDs. It can create quite an issue in your project. For example if you change ID of dimension, you would have to make same change in cube XML else you would loose the relationship.

Anyway, that’s all find and dandy. But how would you change the database id? If you rename the database, the ID does not change and there is no View Code for project. As it turns out, it is in *.database file of the project. In Solution Explorer, click on Show All Files. You would see a your_project_name.database file. Right click on it and then View Code. You will find <ID> tag which contains the ID of the database.

Are you setting ‘Slice’ property in SSAS partitions?

Are you setting ‘Slice’ property in SSAS partitions? If not, you should. Here’s what happened with me.  We have a fairly large SSAS cube. Initially this cube was having only two partitions, current data and historic data. The performance was degrading and so we partitioned the cube by month since majority of the queries are by month and below. This also helped us in improving the processing performance because we can now process only those partitions for which data has changed, two or three at the maximum. On the downside though, there are large number of partitions in cube; a fact we decided we can live with.
So I created the partitions and got the cube processed. I wrote a simple MDX query to observe how cube responds in profiler. Here is the query (slightly changed for anonymity).

[Measures].[Measure Dummy] ON COLUMNS
FROM [Playbox]
WHERE [Period].[YearMonthDaySettlementPeriod].[Calendar Year].&[2010].&[4]

And here’s what I was seeing in profiler.

Without Slice all partitions are scanned

Without Slice, all the partitions are scanned

Hang on! I am querying only April 2010 data, why are those scans on all the other partitions? This defeated the whole purpose of partitioning by month because queries were scanning all the partitions. As mentioned earlier, we had a number of partitions and this is bound to have an impact.Even after creating partitions I was still not getting the benefit I wanted.

Now on the crux of the matter. As it turns out (after spending hours on google, msdn), automatic detection of Slice of SSAS 2008 is not so effective. Hence even though the partitions are clearly for a month, SSAS still has to scan all of them to get data. There are a number of blogs which elaborate the issue. I will just point to this, this and this.

So I set the slice property on each partition, run the same query and guess what, it is hitting only the required partition.

With Slice defined, only required partition is scanned

With Slice defined, only required partition is scanned

The performance benefit was quite apparent as well. I selected a very common real life query. I noted the query timing using the AS Performance Workbench which I have mentioned in my previous blog.  As you can see in the below images, the response time improved dramatically.

Query Duration without Slice

Query Duration without Slice

Query duration with Slice

Query duration with Slice

So the bottom line is “Always define slice when you create SSAS partition”. This has to go in my list of things to look for when you are building SSAS cube.