Product Dimension Question
3 posters
Page 1 of 1
Product Dimension Question
I'm building my first DW project as open source. I've read two of Kimball's books and have taken classes in the entire Microsoft BI Stack. It is based upon a rather poorly designed ERP solution database used by small to mid-sized manufacturers which utilizes SQL Server 2000 through 2008 R2 (soon). To give just a bit of background:
It doesn't have any Primary or Foreign Keys and lacks referential integrity. It does have clustered unique indexes created on identity columns in most tables.
A majority of the fields are CHAR fields, even those that should rightly be integer or other field types. It does however, correctly use Datetime fields.
Most fields are nullable.
The database size range I've run into is between 3GB and 100GB so we're not talking about large data sets.
I would be taking the Item Master table and creating a Product Dimension. These products (part numbers) have facilities to which they are assigned as well as revision numbers. Therefore, it's possible to have the following:
Facility PartNo Revision
GR 12345 A
UDT 12345 B
GR 12345 B
I understand the importance of using surrogate keys and intend to do so. However, when creating a Product Dimension in a situation such as this do people typically use all three fields as their business key? Do you concatenate it in the fact table to facilitate ETL loads?
What do you gurus recommend?
It doesn't have any Primary or Foreign Keys and lacks referential integrity. It does have clustered unique indexes created on identity columns in most tables.
A majority of the fields are CHAR fields, even those that should rightly be integer or other field types. It does however, correctly use Datetime fields.
Most fields are nullable.
The database size range I've run into is between 3GB and 100GB so we're not talking about large data sets.
I would be taking the Item Master table and creating a Product Dimension. These products (part numbers) have facilities to which they are assigned as well as revision numbers. Therefore, it's possible to have the following:
Facility PartNo Revision
GR 12345 A
UDT 12345 B
GR 12345 B
I understand the importance of using surrogate keys and intend to do so. However, when creating a Product Dimension in a situation such as this do people typically use all three fields as their business key? Do you concatenate it in the fact table to facilitate ETL loads?
What do you gurus recommend?
DavidStein- Posts : 24
Join date : 2010-04-01
Re: Product Dimension Question
If revision is dependent on PartNo, then I would have two dimensions, Product (PartNo, Revision) and Facility.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Product Dimension Question
Generally, I design dimensions with a NK column, which contains a delimited concatenation of fields that make up the natural key, as well as attributes for those fields for use in reporting. It makes ETL simpler. particulary if you need to integrate different sources.
Should Facility be part of the dimensional key or its own dimension depends on how it affects the product dimension. If the dimension includes costs, cycle count attributes, or other location specific data, you may want to consider a second dimension. It all depends on your processes. When an order is received, is it tied to an item in a facility, or does such an association occur later in the process, such as during fulfillment? If that is the case, including facility in the product dimension will cause problems.
Should Facility be part of the dimensional key or its own dimension depends on how it affects the product dimension. If the dimension includes costs, cycle count attributes, or other location specific data, you may want to consider a second dimension. It all depends on your processes. When an order is received, is it tied to an item in a facility, or does such an association occur later in the process, such as during fulfillment? If that is the case, including facility in the product dimension will cause problems.
Similar topics
» Product dimension with many options?
» Question on breaking out Degenerate Dimension to separate dimension
» Question about using date dimension keys in other dimension tables
» Product Portfolio Dimension
» Modeling a Product Dimension
» Question on breaking out Degenerate Dimension to separate dimension
» Question about using date dimension keys in other dimension tables
» Product Portfolio Dimension
» Modeling a Product Dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum