Question on relationship between Cube and Fact tables?

Hi, all,

Sorry for this title which may be a bit confused. But what I am trying to ask is: Any best practices on fact tables to be included in a cube? Is it better to create a separate cube for each fact table and its related dimension tables or any other ideas to put several fact tables and their related dimension tables into one cube?

Thanks in advance for your kind advices.

With best regards,

Yours sincerely,

Helen,

First Analysis your requirement, is there any clubed information you desired from multiple Fact Tables, How may shared dimensions are among them.

How frequently U require that aggregated [from both Fact Table] information etc.

Simply analysis what can have U benifit when make a cube having multiple fact tables.

Or other way when there is no share facts between to fact table donot make Cube having multiple Fact Tables.

Reference is below:

Cube Design Best Practices

Well-designed cubes will better match the needs of your end-users. The following best practices can improve usability and also performance of the cube.

Avoid including unrelated measure groups in the same cube

Having many measure groups in a cube can adversely affect the query performance of the cube, even for queries that only use a single measure group. If your DSV contains many potential fact tables and they will not usually be used together, consider creating multiple smaller cubes containing topic areas that are used together. Then, you can enable occasional cross-topic querying by creating linked measure groups that join together all measure groups in a special cross-topic cube.

Bhudev

|||

Dear Helen,

Depends on your system requirements.

But probably you only need one cube, and I think it because in the SQL 2005 you can process parts of the cube separately, in spite of all the cube...

For example, one thing I usually do, is in the datasource view, in spite of use a table from database, I use a view based on this table with only the field I will need in the cube. Only in case we have a lot of fields that will not used...

Other thing is used named calculations, or the compute calculation in database...

Regards!

|||

Hi, Bhudeve,

Thanks for you advices.

Yes, as there are shared dimensions for different measure groups and you never know when and what the users would like to see on their side. Thus, to optimally meet the querying needs, always put them together in a cube? But we can always create partitions for different measure groups if we want?

Does it make any sense? Looking forward to hearing from more of your advices.

With best regards,

Yours sincerely,

|||

Hi, PedroCGD,

Thanks for your suggestions.

Are you saying you put all data together in a single cube? And then partition the cube on different measure groups? Thanks a lot and would like to hear more from you for that.

With best regards,

Yours sincerely,

|||

Yes!

And I will try to explain in my blog, how I'm doing in my project!

Each FactTable will be your measure group... with only the measures you need for your cube!

regards!!

|||

Hi, PedroCGD,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||

Helen,

I'm not against to keep separate Cubes when I do not know the actual requirement. But still there is need to consider all Pros and Cons before completing Design Document for your data Warehouse.

OLAP Design Best Practices for Analysis Services 2005

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssas2005.mspx#ELGAC

document states Having many measure groups in a cube can adversely affect the query performance of the cube, even for queries that only use a single measure group. If your DSV contains many potential fact tables and they will not usually be used together, consider creating multiple smaller cubes containing topic areas that are used together. Then, you can enable occasional cross-topic querying by creating linked measure groups that join together all measure groups in a special cross-topic cube.

Bhudev

|||

Hi, Bhudeve,

Thanks for that. Very helpful.

With best regards,

Yours sincerely,