March 22, 2012 at 6:27 am
Hi,
I've built a cube which seems to function perfectly well except for one issue. When my Customer dimension is used with the Time dimension, everything slows to a crawl. It can take upwards of 10 minutes to display the data in the Cube Browser. There are only 1087 rows in the DimTime table and under 400,000 rows in the DimCustomer table. Using either of the two dimensions separately with any of the remaining dimensions works perfectly. It is only when both these dimensions are used in the Cube Browser at the same time that it turns to mud.
The Atrribute Primary Key for the DimTime table is a standard DateTime field. The Attribute Primary Key for the DimClient table is an Integer (being a Surrogate Key).
Does anyone have any suggestions as to why the Cube Browser slows down so much when these dimensions are used together and/or how to resolve the issue please?
Thank you.
Kevin
May 9, 2012 at 8:44 am
Hi Kevin,
Do you have a defined relationship (dimension usage in BIDS) between Customer and Time? If so what is it?
In my experience - the datetime format type is slow and should be considered converting to the date iso format (20120509) and stored as an INT/TINYINT and this key needs to be added to your DimCustomer with the associated DimTime key.
Here are some scripts/posts to generate some example Date (Time) Dimensions:
http://www.codeproject.com/Articles/25852/Creating-Time-Dimension-in-Microsoft-Analysis-Serv
gsc_dba
May 10, 2012 at 7:26 am
I have 1,442 rows in my time dimension and use a smallint as the key. It works very fast, even against my largest dimension, which has over 2 million rows.
The use of meaningless surrogates vs a human readable key is contended, I admit, but for my money, the meaningless surrogates yield a much better performance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply