Dimension Modeling
3 posters
Page 1 of 1
Dimension Modeling
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.
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
Re: Dimension Modeling
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Dimension Modeling
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.
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
Similar topics
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Modeling a Product Dimension
» Modeling time dimension
» Modeling a dimension table
» Dimension - Fact Modeling
» Modeling a Product Dimension
» Modeling time dimension
» Modeling a dimension table
» Dimension - Fact Modeling
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum