Product Dimension Question

View previous topic View next topic Go down

Product Dimension Question

Post  DavidStein on Sun Aug 15, 2010 10:19 am

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?

DavidStein

Posts : 24
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Product Dimension Question

Post  BoxesAndLines on Mon Aug 16, 2010 1:57 pm

If revision is dependent on PartNo, then I would have two dimensions, Product (PartNo, Revision) and Facility.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Product Dimension Question

Post  ngalemmo on Mon Aug 16, 2010 2:26 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Product Dimension Question

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