Parent Child Dimension Processing Time

In our data warehouse we have several parent child relationships which are updated with every load, or every hour. These relationships also exist in the OLAP environment as PC dimensions and are updated with every warehouse load.
 
Being that our cubes process every hour, the faster we make the data available to the end users, the better. A big chunk of their analysis is done on the data for the current day, and if it’s more than a few minutes late, I have several emails letting me know. I’m always trying to find ways to shave that precious time off of the hourly processing job.
 
The hourly cube job was taking approximately 14 minutes. This was longer than I expected it to be. Curious I looked at the cube processing log table (all OLAP processing actions are logged into a SQL table) to see what was going on in that 14 minutes.
 
 
Processing breakdown:
 
  • Partitions: one minute
  • Dimensions: 13 minutes
    • Parent Child dimension: seven minutes
    • All other dimensions: six minutes

This suprised me. The PC dimension isn’t used in many aggregations and is fairly small in member number compared to the other dimensions (two of them contain more than two million members).

Next step was to manually process the dimensions via Management Studio. Did this and it became very clear at what was taking so long.

 

Regular dimension. Notice the non-processing of the individual partitions under TestMG01.

 

Parent Child dimension. Notice the processing of the individual partitions under TestMG01.

Note: The above screen shots are based off test data. The production cube has hundreds of partitions.

Updating the PC dimension required all the partitions to be reprocessed. The actual partitions weren’t being "reprocessed" per se, but rather indexes or metadata or something else was going on. I ran a trace and it wasn’t of much help. 

Not wanting to make a call into PSS, I tried updating the dimension against a static dataset. This didn’t make any difference. The partitions were still reprocessing.

I finally made a call into PSS. About a day later they got back and informed me that it was a bug in Analysis Services and the call wouldn’t be decremented against our pre-purchased incidents. My reaction to the response was bittersweet. I wanted a solution, but on the other hand, knew that I had tried everything in the book to try and get it working right. It won’t be fixed in SP2 and will probably end up being a hot fix.

Now only if Microsoft could come up with a hot fix for all the hair I lost while pulling it out on this issue…

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s