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/
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP