Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Cube Design Question

Cube Design Question

Cube Design Question

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

RE: Cube Design Question

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.

RE: Cube Design Question

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

RE: Cube Design Question

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.   

RE: Cube Design Question

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.

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

RE: Cube Design Question

Good info.

Dazed and confused

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close