Product and Related Dimensions

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?

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'.

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.

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.