Cube Design Question 2

[Skittle]

When designing a cube is it good practice to have just one fact table and associated dimension tables( I think this is a star schema )?

I have worked through a book example that has three fact tables in one cube and I would expect that good design would be to have one fact table in the underlying data warehouse to improve performance and to reduce potential problems with different levels of detail on each fact table.


Dazed and confused

 

[RiverGuy]

If you're talking about multiple fact tables for the same transactions (different granularities), then I think it's best practice to have one fact table at the smallest granularity--regardless if it's in a cube or not. If you're talking different fact tables which cover different topics yet share the same dimensions, then AS2005 makes it easy to keep those in the same cube.

 

[Skittle]

OK on the transaction example, I think that is what I had in mind.

With regard to different topics sharing the same dimension
that is a bit of a mind melting statement for my steam powered brain cells.

Why would you want one cube to represent different topics?
Couldn't that cause confusion? Do you have a simple example of where more than one topic in one cube would be a
good idea?


Dazed and confused

 

[RiverGuy]

First thing, I'm not suggesting one put a fact table for G/L entries in the same cube as web-site clicks. Those would be two completely different subject areas consumed by different users.

But...for an example of where it would work, consider a department which oversees customer relationships. A fact table for Sales and a fact table for SupportCalls could be used by the same group. Sales would store the amount and value of the goods and services which were sold to the customers, and SupportCalls would store the amount and duration of the calls placed by the customer.

 

[MDXer]

If you read the MS best practices they actually recommend 1 large cube that contains all subject matter. The purpose of the perspectives is to then make the logical analytic units from this single cube. Now we all know what people say to do and what is actually useable in the real world are typically as different as night and day. Rules to follow if you have multiple cubes are:

1) Use a single olap database. This prevents you from having the same dimension existing in multiple cubes.
2) If a subject matter is realted but the grain is different put it in the same cube and handle the different grain through the use of perspectives. o do this simply remove the dimensions or elements that allow the difference in grain to be exposed.
3)Whenever Possible use natural hierarchies and keep the use of attribute heirarchies to a minimum.
4) If you can create a base measure as part of the ETL or cube process then do it to avoid using a calculated measure unless their is no other option.


Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Skittle]

Good info.

Dazed and confused