Hi,
I'm refining a dimensional model that includes several multivalued dimensions at the grain of the fact.
Has anybody implemented the ETL surrounding helper / group tables surrounding mutlivalued dimensions as although I am still in the knowledge assimilation phase, I'd like to get a grasp on the potential complexities that are involved as I have not implemented a multivalued dimension structure before. I would therefore appreciate any views from anyone who has been there before and any advice / design tips.
Many Thanks,
Daren
Multivalued Dimensions - Group Tables and ETL
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
We narrowed it down to two options: create a bridging table where the fact row links to a group of rows in the bridge table that lists each of the multiple values and links through to the dimension for those values. When you bring the dimension in you duplicate your facts and need to apply the bridge table weighting accordingly. If this doesn't float your boat you can create multiple fact rows per event - one for each of the multiple values - so the data comes pre flattened. You then do distinct counts or summed counts or weighted counts on those fact rows in reporting depending on what you need.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Another possibility is that you build your data warehouse in a database that supports multi-valued fields, such as UniVerse or UniData. If and when required these support dynamic normalization of those data.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.