CODEWITHSUNDEEP
data-modelingdata-warehousefact
magicdotjs
magicdotjs

Reputation: 21

Fact Table Type2 History to show changes

I have 2 source tables, asset + asset attributes which I’ve modelled into a fact and 3-4 dimensions.

I created a dimension for some of the attributes to show change so if an asset is a 2 bedroom but then it changes to a 3 bedroom I can see this using SCD2. This is sort of a junk dimension as I can capture a few things that and I am happy with this.

The issue I have is the fact holds the FK for things like type and other dims, if these changes currently my fact table will just show the correct/latest key after ETL runs.

Client wants to show if a fact changed, so in 2023 it could be type A but then in 2024 it changed to type B.

This is like a slowly changing fact, can’t do snapshot so would the best thing for me to do is have fact asset and dim asset.

I am trying to keep my star schema but I don’t think I can in this scenario unless I do sort deletes on my fact table. There is only 1 update date in source as well so when something changes I need to check all the dims

Updated Example

Fact.Asset Asset Key Asset ID Type Key Tenure Key

DimTenure TenureKey Desc isActive ActiveFrom ActiveTo

DimType TypeKey Desc isActive ActiveFrom ActiveTo

SELECT a.AssetID,t.Desc
FROM Fact.Asset a
LEFT JOIN Dim.Tenure t ON t.TenureKey = A.TenureKey AND t.isActive = 1

This query will work and give me the current tenure for this asset, if the tenure desc renames this will also be handled with SCD2, the issue I have is if the asset changes to a different tenure my fact table will not hold the previous tenure key since when I update my fact table I clear out anything I have in staging and insert a new row with the latest keys.

What would be the best way of me handling this scenario or do I need to move these keys into a dimension.

Upvotes: 0

Views: 121

Answers (1)

NickW
NickW

Reputation: 9798

The simplest way of doing this is probably just joining the dimension to itself. For example, if you wanted the dimension attributes on the 2023-01-01:

SELECT F.*, D2.*
FROM FACT_TABLE F
INNER JOIN DIM_TABLE D1 ON F.DIM_TABLE_SK = D1.DIM_TABLE_SK
INNER JOIN DIM_TABLE D2 ON D1.DIM_TABLE_BK = D2.DIM_TABLE_BK
WHERE D2.EFF_START_DT <= '2023-01-01' AND D2.EFF_END_DT > '2023-01-01'

SK/BK are the surrogate and business keys, respectively

Upvotes: 0

Related Questions