Dimension Modeling

View previous topic View next topic Go down

Dimension Modeling

Post  pedelstein on Mon Oct 21, 2013 4:29 pm

I am looking for advice on how I should setup some dimensons.  I have 2 sets of data and would like to combine them into one dimension so that a hierarchy can be used in an SSAS cube but have come across some setbacks if I were to do it that way.  I have a set of Vendors and a set of what I will call Subvendors.  Typically a Subvendor can only be tied to one Vendor but multiple Subvendors can be tied to the same Vendor except for the case where the Subvendor is Unknown then we have multiple Unknown Subvendors tied to multiple Vendors.  There are a lot of Vendors with an unknown Subvendor essentially causing what should be a M:1 relationship to now be a M:M.  

The other catch is that we need to track changes on Subvendors as they can be purged or changed in the source system.  The Vendors however are never purged and therefor do not need to track changes.

The options I have been considering are setting up 2 dimenstions, a SCD 2 for the Subvendors and a Type 1 for the Vendors so that if the name changes it will update but do not need a new record.  This would be easiest but I am not sure how I would be able to create the hierarchy in SSAS with the dimensions being separate tables.

The next option was to create a single SCD 2 table with all possible combinations of Subvendors and Vendors that will track changes when a subvendor is updated, changed, or assigned to a new Vendor.  This one is difficult because of the Unknown Subvendors being tied to many Vendors.  If I were to make the business key the Subvendor # and Vendor # then any time a subvendor is reassigned to a vendor it will treat it as a new record and not expire the old.  If i were to only make the subvendor # as the business key then it would treat all the of Unknown Subvendors as expired except for the latest one.  

The last option which I am not sure if this is a good choice or not is to snowflake the Vendors and Subvendors.  If I were to do this I also do not know if I would be able to create a hierarchy for these in SSAS (I am new to data warehousing and ssas.)

Which of these would be the best option with regard to ease of use for the users and setup and maintenance on my end? Any other options that might be better?

Hopefully this all makes sense but if I need to explain more let me know.  Thanks in advance for any help.

pedelstein

Posts : 1
Join date : 2013-10-21

View user profile

Back to top Go down

Re: Dimension Modeling

Post  BoxesAndLines on Tue Oct 22, 2013 10:36 am

You can't create a hierarchy if you don't have one-to-many relationships. You have a "network" which is a many-to-many relationship. That's why you're having difficulty modeling a snowflake or hierarcy in a dimension table. The two dimension approach is your best solution.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimension Modeling

Post  Jeff Smith on Wed Oct 23, 2013 11:15 am

Is the Subvendor unknown or is it really a case where there is no subvendor.

In any case, you could treat the Vendor as it's subvendor. If there is no subvendor, the default would be the Vendor. It's more of a ragged hierarchy. If the hierarchy has only 2 levels, then default the subvendor to the Vendor.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Dimension Modeling

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum