Multiple versions of the same attributes

Our requirements dictate that for certain attributes in a dimension, we will be required to allow the user to choose between different versions of those attributes. The source system contains a one-to-many relationship: a main entity table which is used to pull many common attributes, and a version table with one or more rows for each entity and the values for version-related attributes.

So far, we have identified ten version-related attributes, and we have about ten versions. The options I can think of are as follows:

1. Dimension table would include 100+ columns: each of ten verison-related attributes will be repeated for each version as a separate set of columns. Any new versions would need to have new sets of columns added to the table.

2. Creating two dimensions: one for the main dimension with common attributes, and one for the version-related attributes. New foreign keys would need to be added to the fact tables when new versions are created.

3. Maintaining two dimensions, plus a bridge table: The bridge table would contain a foreign key to the main entity dimension, a reference to the version used, and finally, the version attributes for that entity and version combination.

Obviously #3 seems the logical choice, however, we are feeding this data into SSAS. We prefer to keep things up to best practices for both the star schema and the SSAS design. The downside with #3 is that a SQL query written against the star schema would be very sensitive to joins in that it would be easy to return inflated values if the joins were not constructed properly. I'm just not sure if there is another option I haven't thought about.

Thanks

It's not out of the ordinary for a fact to contain two keys: one referencing the current version while another references a point in time (ie: type 1 & type 2 versions of a dimension). However, your requirement is a bit different.

From a historical perspective, rather than base history on the point in time of the transaction, you need to have history at any specified point in time (i.e. version). You can do this by using a compound primary key on the type 2 dimension. The key would be made up of the type 1 dimension key plus the version (time period or sequence number). This would allow you to have only one key on the fact and not need a bridge table.

Thanks, but unfortunately, this has nothing to do with historical tracking. All versions are valid simultaneously. There is no current version, or previous version. There's just versions, that the user can choose to look at at any time.

Ok. I'm stumped. Go with option 1 or maybe explain the situation a little clearer. What is a version a version of?

In this context, Version = Series, Accounting Books, etc.

Some of the dimension members' attribute values vary depending on which book is chosen.

Make version and related attributes a junk dimension off the fact. Or just junk the attributes if version/book is already a dimension. I would assume they need to select a book when reporting.

Or, if the measures are the same regardless of version/book and you don't want a lot of extra rows, then using a compond pk on the dimension as suggested would work.

If you were using Business Objects I would say go wtih what ngalemmo last suggested--a dimension with compound PK--and put a prompt in the universe to require the user to select a version when reporting.

However, I am not aware of a way to implement equivilent functionality in SSAS (no @prompt function!), so I think you might need to go with Option 3. with the bridge table after all. It might be worth it to see what happens in SSAS if you do two dimension tables w/o a bridge table (the second table with the version-specific attributes would have the compound PK consisting of the main dimension PK + FK to the verison.) I'm sure you could set this up in the DSV, but not sure SSAS would like it without a bridge table.