Which is the best approach for a dimension (SCD-2 or a SCD-1 + a whole new dimension)

Question

Let´s say I have the following situation:

A dimension Product with some attributes that aren't volatile (Description and Diameter - they can only be changed by a SCD-1 change for correction) and a attribute that can be volatile (Selling Group, it can change over time for the same product).

So, when a change occurs in these volatile attributes of one product, I need to somehow track them.

I have come with these 2 approaches:

For both: keep using SCD-1 for non-volatile attributes.

Approach #1: Use SCD-2 in product_dim only for volatile attributes.

Approach #2: Make Selling Group a whole new dimension and every sell will track the current value in moment of ETL. No need for SCD-2 here.

I am new in Data Warehousing and I'm trying to understand which is better and why. One of my aims is to use a OLAP software to read all of this stuff.

Answer 1

It all comes to the business needs of your model. I don't know the business enough from your question, but as a rule of thumb if you wanna do analysis by Selling Group (i.e: Total Quantity of all products sold by Selling Group X) then you should create as a separate dimension. So in this case approach#2 is correct. Considering general concepts and assuming a selling group is some kind of group of products, it doesn't make sense having it as an attribute of a product.

If you want to learn more about Dimensional Modelling I'd suggest looking into Ralph Kimball's work if you haven't done yet. An excellent resource is his book The Data Warehouse Toolkit which covers your question and many more techniques. It's a nice tool to have over your desk when questions like this pop up. Most of the experienced Data Modellers have a copy of it to consult every now and then.