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!