Product and Related Dimensions

View previous topic View next topic Go down

Product and Related Dimensions

Post  DavidStein on Thu Aug 26, 2010 2:22 pm

As I've mentioned previously I am dealing with an OLTP system which is too permissive. Proper workflows are not enforced and this makes reporting difficult.

For example, we have an Item Master Table which contains every single part in the system. It lists relevant specifications and has a default Product Class for that part number. The Product Classes are tied to General Ledger Accounts for the purposes of inventory transactions, accounts receivable, etc.

However, the users of this system can select a part number with a certain product class and arbitrarily assign it another product class when they create a sales item for it. Since I can and do have multiple relationships between a product (part number) and a product class, I see as I have to pragrmatic options.

1. I can separate the Product Class out into it's own dimension and then have a key value for it in every fact row.
2. I can include Product Class as one of the values for the natural key in the Product Dimension and therefore have multiple copies of a Part Number based upon how many different Product Classes that have been assigned to it.

I would really like to provide easy drill down which would tend to favor a single dimension. I suspect even with going with option 2, which would generate more records in the product dimension than 1, we are still dealing with less than 100,000 records.

What do you folks recommend?

DavidStein

Posts : 24
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Product and Related Dimensions

Post  ngalemmo on Thu Aug 26, 2010 2:54 pm

Option 3: Continue to include product class as a product attribute (this represents the 'standard class') and have a product class dimension as well, with an FK from sales which represents the 'assigned class'.
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 and Related Dimensions

Post  DavidStein on Thu Aug 26, 2010 3:23 pm

I'm not trying to be argumentative, but how is option 3 any better than option 1?

Oh, and somehow I knew that you would give an option 3.

DavidStein

Posts : 24
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Product and Related Dimensions

Post  ngalemmo on Thu Aug 26, 2010 4:18 pm

There is ALWAYS an option 3! ...)

It allows capturing both categorizations. Not knowing the business requirements, I am assuming that the OLTP system has the product class on the product master for a reason, and, that it can be overridden on the sale for other reasons.

I also assumed, and maybe incorrectly, that, under option 1, the classification would only be assigned at the sale and would not be carried in the product dimension... the assumption was based on option 2, which would create rows for every product/product classification combination.

I definitly do not like option 2. It weakens the product dimension as a usable conforming dimension. If option 1 implies the product dimension would continue to carry the stated classification on the product master, then that is the same as option 3, and I'm good with that.
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 and Related Dimensions

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