How to turn SCD type 4 into type 6

91 Views Asked by At

I am working with a data model (in a data mart) that has a type 4 slowly-changing dimension. The implementation is unlike what I typically see. The model looks like:

enter image description here

(If not clear, the issue is that DimProductHistory is not part of the fact table's composite key. Alternatively, DimProduct is not snowflaked off of DimProduct.)

In a BI tool (e.g., Power BI), my users want the option to slice by the current product attributes or by the historical product attributes. For example, they want the ability to see measure Total Sales sliced by Current Product Color and by Historical Product Color. How can I achieve this? My thought process right now is to transform the model so that product becomes a type 6 dimension table. But, this seems like the incorrect solution. (Why fight it being type 4?)

If it helps, I have the following table definitions:

CREATE TABLE dbo.FactSales(
    DateKey bigint
    , ProductKey bigint
    , SaleAmount decimal(10,2)
)

CREATE TABLE dbo.DimProduct(
    ProductKey bigint
    , Color varchar(15)
    , Size varchar(3)
)

CREATE TABLE dbo.DimProductHistory(
    ProductHistoryKey bigint
    , ProductKey bigint
    , RowCurrentFlag bit
    , RowStartDate datetime
    , RowEndDate datetime
    , Color varchar(15)
    , Size varchar(3)
)
0

There are 0 best solutions below