February 1, 2012 at 8:14 am
Hi,
I've been mulling over a dimensional design problem.
Please forgive the slightly contrived example 🙂
- Say there are head gardeners and (ordinary) gardeners, and each head gardener can instruct multiple gardeners, and each gardener can accept work from many head gardeners at the same time
- Furthermore, each gardener and head gardener accept contracts to work in 0 or more gardens (the head gets a contract on 1/1/12 and the subordinate also gets one on the same or later day).
- So my facts are the contracts covering work in the garden (of which there are always at least 2, 1 for the head, and 1 for the subordinate).
- Finally, I wish when performing analysis to be able to drill-down the hierarchy of head gardener to gardener
Is there a way to model the dimension, giving the ability to drill-down the hieararchy, work with the many-many relationship, and avoid any double-counting (particularly for facts associated with head gardeners)?
At the moment, I have a flattened dimension of head gardener * gardener, and each row in the fact table includes the the date for the gardener and the date for the head gardener as well. So for each head gardener (particular for large gardens) there are multiple occurences of their start date (some what unsatisfactory).
Suggestions to improve on the above are most welcome 🙂
Thank you.
February 1, 2012 at 10:53 am
Craig@Work (2/1/2012)
Hi,I've been mulling over a dimensional design problem.
Please forgive the slightly contrived example 🙂
- Say there are head gardeners and (ordinary) gardeners, and each head gardener can instruct multiple gardeners, and each gardener can accept work from many head gardeners at the same time
- Furthermore, each gardener and head gardener accept contracts to work in 0 or more gardens (the head gets a contract on 1/1/12 and the subordinate also gets one on the same or later day).
- So my facts are the contracts covering work in the garden (of which there are always at least 2, 1 for the head, and 1 for the subordinate).
- Finally, I wish when performing analysis to be able to drill-down the hierarchy of head gardener to gardener
Is there a way to model the dimension, giving the ability to drill-down the hieararchy, work with the many-many relationship, and avoid any double-counting (particularly for facts associated with head gardeners)?
At the moment, I have a flattened dimension of head gardener * gardener, and each row in the fact table includes the the date for the gardener and the date for the head gardener as well. So for each head gardener (particular for large gardens) there are multiple occurences of their start date (some what unsatisfactory).
Suggestions to improve on the above are most welcome 🙂
Thank you.
Like the "gardener" example 😛
Your scenario seems very similar to a hospital claim and diagnosis relationship, where one claim could have been for multiple diagnosis, and a diagnosis could have many claims.
The exception here of course would be the fact that you have a parent/child relationship, although I suppose a similar scenario could also exist with a diagnosis (one main and many possible sub or minor).
One solution to this problem would be a bridge table between the fact and dimension, taking care of the many-to-many relationship. You would have something like a "group key" in this bridge table, and this "group key" would be your reference in the fact table. The challenge with this approach is that you would have to decide on how to distribute monetary values. This is usually done by including a factor in the bridge table by which you multiply the fact values...to evenly distribute them between the members. This ensures accurate values on aggregated levels.
An alternative would be to have 2 keys in the fact table...one for the head gardener and one for the gardener. This means that the relationship will be defined through the fact only, and there would be no parent/child relationship in the dimension itself. You would have to build 2 separate hierarchies in the cube, but it will work.
You also mention above that you have at least 2 contracts (fact records) for the work performed in a garden at a time (one for the head gardener and one or more for the other gardeners). This sounds similar to a transaction/line item scenario. I would suggest that you store your facts at the lowest level (gardener contract level), assuming that the values will roll up correctly. If not, you should maybe consider 2 different fact tables...one for contract and one for sub-contracts.
I hope the above makes sense, and it gives you a few more options to consider...
February 2, 2012 at 2:54 am
Thank for the reply and pointers 🙂
As always, it comes down to what people want from their cube, and this certainly will help the discussion move along. 😀
February 2, 2012 at 5:47 am
Craig@Work (2/2/2012)
Thank for the reply and pointers 🙂As always, it comes down to what people want from their cube, and this certainly will help the discussion move along. 😀
Glad I could help, and you are absolutely right. In the end, your design will and should reflect the business requirements because that will ultimately dictate how the data should be presented to the end-users.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply