Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

One or two fact tables? Expand / Collapse
Author
Message
Posted Thursday, August 8, 2013 9:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
Koen Verbeeck (8/8/2013)
OK, now that I see the data, I think you should have 2 fact tables, because indeed they are at two different grains.


Interesting topic. I've been struggling with a similar, two grains but related dimensions situation. Was coming to the conclusion that two fact tables were needed. Any suggested references for reading more about the use of two fact tables? My attempts to search for/find references were not fruitful.
Post #1482409
Posted Friday, August 9, 2013 12:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,253, Visits: 11,033
aaa121 (8/8/2013)
Thanks for your time on this. The conclusion I kept coming to was two fact tables as well.

What did you think about the mini dimension solution for those more frequently changing attributes?


If the risk and premium class have fixed number of combinations, the mini-dimension is probably a good idea.
I do think you'd drop the policy risk dimension though, and add a surrogate key into the fact table(s), so that your policy dimension stays small. This way, the risk/premium class is directly tied to the facts.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482657
Posted Friday, August 9, 2013 12:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,253, Visits: 11,033
kl25 (8/8/2013)
Koen Verbeeck (8/8/2013)
OK, now that I see the data, I think you should have 2 fact tables, because indeed they are at two different grains.


Interesting topic. I've been struggling with a similar, two grains but related dimensions situation. Was coming to the conclusion that two fact tables were needed. Any suggested references for reading more about the use of two fact tables? My attempts to search for/find references were not fruitful.


The Data Warehouse Toolkit by Ralph Kimball would be a good start.
Also check out his regular design tips. For example:

http://www.kimballgroup.com/2013/08/05/design-tip-158-making-sense-of-the-semantic-layer/




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482658
Posted Friday, August 9, 2013 8:43 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
Koen Verbeeck (8/9/2013)
kl25 (8/8/2013)
Koen Verbeeck (8/8/2013)
OK, now that I see the data, I think you should have 2 fact tables, because indeed they are at two different grains.


Interesting topic. I've been struggling with a similar, two grains but related dimensions situation. Was coming to the conclusion that two fact tables were needed. Any suggested references for reading more about the use of two fact tables? My attempts to search for/find references were not fruitful.


The Data Warehouse Toolkit by Ralph Kimball would be a good start.
Also check out his regular design tips. For example:

http://www.kimballgroup.com/2013/08/05/design-tip-158-making-sense-of-the-semantic-layer/


Been doing a lot of reading in the Toolkit. Clearly haven't gotten to the right section yet.

Have only run into the design tips a few times based on general searches. Looks like I need to do a more thorough look at that section of the kimballgroup site. Thanks so much for both suggestions!
Post #1482800
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse