April 28, 2006 at 9:57 am
I'm a neophyte to building cubes so please bear with me. I'm using a star schema. I have a field in my relational database which allows multiple values. For example, one person's record may have A,B,C,D and someone else may have A,C. When I bring this data into a cube, the entire string comes over so the field is rather useless as there is really no way to do an analysis on people who just have 'A' or just have 'C' in the field etc.. Is there any way around this? Ideally, I want every value in the field treated separately but they all refer to the same field! Any assistance would be appreciated.
April 28, 2006 at 7:04 pm
Are u sharing the dimensions.
Is the relation between the tables are correct.
from
Killer
April 29, 2006 at 5:29 am
A common approach used to solve this (many-to-many) is to use a bridging table with a weighting factor (that you use to ensure that the totals are correct when rolling up). This is a table that may use a group value as the foreign key for the fact table that is the primary key for the bridging table. The bridge has a foreign key value that then joins to the (true dimension) table containing A,B,C,D.... For a good expansion on this see Kimball's (et al) 'The Data Warehouse Lifecycle Toolkit'.
When this is in place, if you're using Analysis Services 2005 you can create a many-to-many dimension, for help on this see the 2005 BOL. I don't think AS2K supports these, though it might be possible through snow-flake dims.
Steve.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply