July 19, 2004 at 10:02 am
Hi!
I have a cube with ~30 dimensions. When doing storage design for
current year partition with 75% performance gain, I face very legthy
work. After 10 hours I got only 7% performance gain with ~2700
aggregations. These aggregations were processesd in 20 minutes during
cube processing. Microsoft states that I can have up to 128
dimensions per cube and up to 65535 aggregations per partition. So I
am very far from the limits. The fact that makes me most suprised is
that the cube processing takes very little time, compared to storage
design.
What am I doing wrong?
Thanks.
July 20, 2004 at 5:44 am
It sounds to me that you have some very large dimensions. Do you have any description type columns in the dimensions? It is not a good idea to hold large data columns in dimensions.
If you do, you are better off storing them in the fact table or the dimension storage will take a long time to complete.
It could also be that your dimensions are too large to be suitable for a star schema and you should consider a snowflake.
July 21, 2004 at 1:36 am
I have very different dimensions from two members (Account Type: Large account/Small account) to ~20000 members organized in three or even four level hierarchies (Customers, Date). My fact table has ~2700000 records and a cube built completely out of this fact table (no dimensions based on other tables) having ~15 dimensions is very problematic to design storage with more then 30% performance gain. (Gets worse if I make 30 dimensions using additional tables)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply