Multivalued Dimensions - Group Tables and ETL

droberts · Post by **droberts** » Wed Apr 08, 2009 4:18 am

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

vmcburney · Post by **vmcburney** » Wed Apr 08, 2009 6:41 am

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.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 08, 2009 7:12 am

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.

droberts · Post by **droberts** » Thu Apr 09, 2009 5:26 am

Thanks guys - I had thought of the helper table method, but was curious to hear of any problems implementing such a method in your ETL.