CODEWITHSUNDEEP
sql-serversql-server-2016data-warehousesql-data-warehousetemporal-tables
Andrey Morozov
Andrey Morozov

Reputation: 7979

Is it possible to use system-versioned temporal table in the fact-dimension DWH design?

Say I want to implement SCD type2 history dimension table (or should I say table with SCD type2 attributes) in the DWH system which for now I has been implementing as a "usual table" with a natural key + primary surrogate key + datefrom + dateto + iscurrent additional columns.

where

Now I want to use a system-versioned temporal table in the fact-dimension DWH design, but MSDN is said that:

A temporal table must have a primary key defined in order to correlate records between the current table and the history table, and the history table cannot have a primary key defined.

So it looks like I should use a view with a primary surrogate key generating "on the fly" or another ETL process, but I do not like the both ideas...

Maybe there is another way?

Upvotes: 0

Views: 1544

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89166

You would use a Temporal Table in the persistent staging area of your data warehouse. Then you can simply apply changes from the source systems, and not loose any historical versions.

Then when you are querying, or when building a dimensional datamart, you can join facts to the current or to the historical version of a dimension. Note that you do not need surrogate keys to do this, but you can produce them to simplify and optimize querying the dimensional model. You can generate the surrogate key with an expression like

 ROW_NUMBER () OVER (ORDER BY EmployeeID, ValidTo) AS EmployeeKey

And then joining the dimension table when loading the fact table as usual.

But the interesting thing is that this can defer your dimensional modeling, and you choice of SCD types until you really need them. And reducing and deferring data mart design and implementation helps you deliver incremental progress faster. You can confidently deliver an initial set of reports using views over your persistent staging area (or 'data lake' if you prefer that term), while your design thinking for the datamarts evolves.

Upvotes: 1

Related Questions