July 22, 2016 at 1:33 pm
I have a dimension that represents a hierarchy that will change less often.
I have a fact table and a dimension at the same granularity. The dimension in this case is needed to relate two fact tables together that are different granularities.
I'm going to use a baseball example to illustrate this scenario since some of my business entities are a little abstract.
TeamDim:
TeamDimId
TeamName
ManagerName
HomeStadiumName
PrimarySponsorName
OwningCompanyName
-- Let's assume we are only analyzing games "our" teams participate in, so we don't relate to two teams, just one TeamDimId to simplify the example
GameFact:
GameFactId
GameDimId
TeamDimId
GameDurationSeconds
GameInningDim (one row per inning):
GameDimId
InningId
GameDate -- DateDim related to GameFact via indirect relationship
BatterSwingFact:
BatterSwingFactId
TeamDimId
InningId
SwingResultDimId -- Dimension has attributes for whether they swung and whether they hit/stroke/foul: "Swung, Strike", "Swung, Hit, Ground", "Swung, Hit, Foul", "Swung, Hit, Homerun"
Note: BatterSwingFact is related to GameInningDim at the Inning granularity, and GameFact is related at the Game granularity. I have these facts setup to not aggregate with unrelated dimensions. This ensures I can add facts to a pivot report, and the GameFact measures will only show up in subtotals outside the context of dimensions such as SwingResultDim that it is unrelated to. This way someone could have a report that includes facts from both fact tables(which is a requirement) but only see aggregations at the appropriate granularities.
Here's my question:
Should I combine TeamDim with GameInningDim? TeamDim is a dimension that rarely changes, and GameInningDim changes often.
My intuition was originally they should be separate. There's nothing really stopping me from combining them though. There's a clear hierarchy that a game is a child of a Team(in our simplified scenario where the game "belongs" only to one team), and an inning a child of a game. It just seems overboard to denormalize to that extent, but that might be the OLTP DB designer in me fighting that.
Opinions?
July 22, 2016 at 3:30 pm
One thing about my business case that's not apparent from my trivial example: The "child" GameDim dimension would grow by 1000s of records everyday, being roughly a million to 10 million records after a single year, where as the parent dimension would never exceed more than a hundred total ever.
Without spending alot of time trying both ways and benchmarking, my intuition would be that combining this extremely large dimension with the very small one would negatively impact performance. From a data warehouse standpoint, to ensure good performance for use cases where I'm grouping by only attributes in the TeamDim and not the GameDim, then I'd definitely keep them separate.
But I'm thinking if I am catering my design to SSAS, having the Team-Game hierarchy as a single dimension might perform better since I'd be able to create aggregations at a few different granularity levels, and SSAS would understand the relationships so it can rollup aggregations between intermediate levels. I don't have enough knowledge of the inner workings of SSAS aggregations or personal experience with SSAS to make a good judgement call.
July 27, 2016 at 11:46 am
Providing a direct answer is a little tough because of your obscure case, but I'll attempt to provide a few thoughts:
From your example, I wouldn't necessarily agree that the GameInningDim and TeamDim dimensions can be combined. I would rather want to combine the GameDim and GameInningDim dimensions, as those dimensions seem to be directly related but at a different grain. This would of course only be possible if the duration in the GameFact can be expressed by innings.
Assuming it is (expressing the duration by innings that it) and that you have an InningDim dimension, you could maybe even do without the GameInningDim and use the fact to create the association between the Game, Inning and Team dimensions.
Hope this helps...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply