1

I am working on a star schema and I want to track the history of data for some dimensions and specifically for some columns. Is it possible to work with temporal tables as an other alternative ? If yes, how to store the current record in a temporal table? Also, is it logic that the source of my dimension will be the historical table of my temporal table?

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
  • 4
    Temporal tables will track history for all columns. The current state of the record would be stored in the main table and all the history will be stored in the history table, however you do not need to reference history table in the queries, you can get historical data by using special temporal table syntax – Dmitrij Kultasev Jun 24 '19 at 09:04
  • 2
    The major "problem" with temporal tables is that current versions of SQL Server allow only system-versioned temporal tables. What this means is that rows are recorded historically according to how you update them in the warehouse, not according to whenever the source data was modified -- the time stamps associated with the history are taken from the server's system time. For some scenarios, this is not a problem, for others it is. When initially loading the history you can work around this, but for incremental updates turning history off and on just to get temporal syntax is a hassle. – Jeroen Mostert Jun 24 '19 at 09:30
  • 1
    Furthermore, temporal tables are "all or nothing" -- the whole row is versioned; you can't choose to have only particular columns versioned, or have separate versioning per column. This is not typically a big problem as data should logically be organized as to make sense only on a whole row basis, but it can be annoying if you want particular attributes to *not* be historical -- that's not possible. – Jeroen Mostert Jun 24 '19 at 09:32
  • 1
    If I recall, temporal tables do not have unique row ids either. You have to query the past using the PK and a time value, since the historical values still all have the same PK. This can cause issues with how the fact table are expected to reference a specific dimension record. – Wes H Aug 07 '19 at 22:04
  • 1
    If you are only interested in tracking some of the columns, you could create a trigger to populate a history table that you define when information is Inserted, Updated or Deleted. You would have control over which data is recorded and when. – Steve Ford Feb 06 '20 at 12:17

1 Answers1

0

Determining if two rows or expressions are equal can be a difficult and resource intensive process. This can be the case with UPDATE statements where the update was conditional based on all of the columns being equal or not for a specific row.
To address this need in the SQL Server environment the CHECKSUM function ,in your case is helpful as it natively creates a unique expression for comparison between two records.
So you will compare between your two sources which are logically the ODS and Datawarehouse. If the Chescksum between two different sources isn't the same, you will update the old record and insert the new updated one.

mohamed-mhiri
  • 202
  • 3
  • 22