I am having quite a lot of problems when running a ProcessUpdate and then a ProcessIndex on a multidimensional cube. The ProcessIndex takes hours to run on a large cube.

So I wanted to test an alternative approach. I really wanted to use ProcessAdd on my cube. But I also know that my data will be added, changed and deleted. So how can I make this work?

First I wrote a simple MDX to see the internet sales.

select [Measures].[Internet Sales Amount] on 0
from [Adventure Works]

And the result I got was $29,358,677.22

To simulate an update I will add a new row that changes the original row. The original row might be a sales of 10 dollars. So if this row should be updated to a sale of 7 dollars I will add a new identical row with -3 dollars. So in the cube it will look like a 7 dollar sale.

In my database I can split my fact table in 2. So that I have one table (current) with data that changes. And one table (archive) with data that does not change. I will do the same in my cube. I will make a partition for data that changes (from the current table). This partition will be processed with ProcessAdd and only return new rows. The other partition will have a regular SQL that returns everything from the archive table. The Archive partition should only be processed when new data is added. Maybe find a way that the archive table only contains real rows? And not the rows for updating and deleting?

If you only have small amounts of data you can of course keep your current table in your DWH with original rows from your source. And then drop and re-create your current SSAS partition. Then run a ProcessAdd.

Any comments?

I tested this in SSAS multidimensional but I am quite sure it will work in SSAS Tabular too

In this post I will describe how to get started with aggregations in SSAS multidimensional. Since this is a rather complicated thing I will start with a really basic sample.

First I will modify my Adventure Works database. I will create a new aggregation design with no aggregations. This can be done by running the aggregation wizard and selecting “No aggregations”. Then save the aggregation design and assign it to the partitions.

It will look like something like this

Do a full process on the measure group

Then run a test query against this cube using one of the measures from that measure group

As you can see here I get a query subcube event. I only get one of these events because this query is really simple. For more complex queries you get loads of them. In a typical production environment you will get loads of them before the aggregations are optimized.

My main goal now is to eliminate this subcube event by designing aggregations.

To do this I open the aggregations. Then chooses “Advanced view”. And then “AggregationDesign EMPTY”

Right click in the gray area in the middle and choose “New aggregation”

In the check boxes check the following. Product -> Subcategory and Date -> Calendar Year

Then save and do a full process

Re-run the previous query and watch profiler

Now you can see that my query did hit the aggregation.

If you choose to run the queries multiple times on each step make sure you clear the cache in between.

A few days back I wanted to optimize a SSAS multidimensional cube. I was testing different queries to see if any of them used my aggregations. As I understand SSAS multidimensional provides results on different ways.

1. From cache

2. From aggregations3. Build the result from storage engine / formula engine

Number 1 is the fastest way and number 3 is the slowest. So it’s quite important for query performance that you have well considered aggregations. Please note that aggregations adds time to cube processing. When I saw that my queries did not hit any aggregations I started to Google around. And when reading some forum posts I started to suspect that my aggregations was unprocessed. I was running a query similar to this to check the size of my aggregations

This result looks to be okay. But the result from my cube was that almost every size was set to 0. How can I reproduce and identify this issue? I did read the documentation on processing options and figured out that “all flexible relationships we be dropped when performing a process update on a dimension”.

To figure out what was going on I tried to change a value in a dimension with a flexible relations and then run a process update on the dimension. Then followed by a full process of my test partition.

Now you can see that most of the size is set to 0. How can I get them back now? Process partion / measure group / cube with “Process index”. And the your aggregations should be fine. If you run “Process default” on your dimension or full process on the cube the aggregations should be fine.

I will certainly check my processing routines after this 🙂 Does anyone have any comments on this?