I know this may sound like a homework question, but I assure you it is not.
I am seeking for a second opinion about a lecture, usually I would go and ask the lecturer or tutors, but their answers usually leave me with more questions and sadly is a weekend and won't be able to sleep. And this time my Google techniques didn't work.
The question is about Double Dipping and it is based on one of my lectures.
Starting from this Star Schema (Lvl 1):
LVL 1 Star Schema
Which is from some olympic tables and the number of medals is my aggregated attribute.
Before going to the Double Dipping problem, we start with the Drill-Down.
The Lvl 1 is able to drill down from sport (swimming, etc) to events (4x100m relay women, etc), and its star schema (Lvl 0) would be this:
LVL 0 Star Schema
So the SportDimension was replaced with the SportEventDimension.
Because each event can have only 1 of each type of medals, then this would effectively be the Level 0, there is no real aggregation on the NumberOfMedals attribute.
In my Level 1, the aggregate function is SUM.
In my Level 0, the aggregate function is SUM. (I was told that there MUST always be an aggregate function to be considered a fact attribute, even if there will be no real aggregation).
But what if we want to Drill down even further? this time to see who was the athlete to participate in each event.
Then we could end up with the following star schema (lvl -1 (just to don't change the numeration of the previous levels)):
LVL -1 Star Schema
It looks nice... but it is wrong, it was mentioned in my lecture that this is because now we won't be able to answer questions like "how many gold medals did (country) get in 4x100m freestyle relay women".
It is also mentioned that this is because the Lvl -1 Fact Table has a different purpose, this time it has to do with athletes and not with sport events.
The result of trying to answer the previous question, would be 4 gold medals, because there are 4 participants, each one got their gold medal, but the country only was awarded 1 gold medal because it is only 1 event, and that is Double Dipping, getting more than the real value (as far as I understood).
Sample data, the number of gold medals should be 1, but now because of the Athletes dimension and the Double Dipping, we would get 4.
Double Dipping Example
To solve this, it was mentioned that we can create a multi-fact star schema. But at the end, it is mentioned that we can avoid the multi-fact and leave the level -1 as it is but mentioning that the AthleteDimension is Determinant.
Is there an actual effect just mentioning that the dimension is determinant to eliminate the Double Dipping? My understanding for Determinant is that it is a dimension that MUST be used to give meaning to any query result. I don't quite get it how mentioning it will eliminate the Double Dipping from Level -1 and still being able to drill down from Level 0 successfully.
Can I not mention that is determinant, and use a "MAX" function when creating the report for the Level 0? (didn't rename levels, so the athletes level is still level -1, level 0 is the sport events and level 1 is the sport)
I was thinking on something like this when drilling down:
Number of medals:
- Lvl -1 (Blue): SUM (although it will always be 1)
- Lvl 0 (Green): MAX
- Lvl 1 (Yellow): SUM