Temporal Table Reporting

Question

Temporal Table Reporting

Leighton21

Mr or Mrs. 500

Points: 557
More actions
June 21, 2020 at 1:04 pm

#3764586

Hi All,
I have inherited a database which has a number of system versioned (temporal tables). One of the things I have been tasked with is to create a report which basically shows what has changed across the entire database (i.e. Audit), not just a single table. Is there an efficient way to generate a report/query that can achieve this? My initial thought was just to let the end user select each table a review the changes. However managements requirement is to have a list of all the changes in a list i.e.
Table | Time of Change | Who Changed | What Changed

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 1

First things first... has management approved the purchase of at least 10 times the amount of disk space you'll need to store just the next year's worth of changes? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Leighton21 Mr or Mrs. 500 Points: 557 More actions · Answer 2

Thanks for the response,

The tables that system versioning is enabled do not get transacted against often as far as I can tell.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 3

What I'm concerned about is that it appears that it sounded like you have several temporal tables but management was asking you for any and all changes to every table. Is that true? Every table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Leighton21 Mr or Mrs. 500 Points: 557 More actions · Answer 4

Only the temporal tables. So one would be customer and another would be customer address. The report would show the transactions against each chronologically for example

Username, EventDate, Table, NewValue, OldValue

Bob, 2020-06-18,Customer,{"SLA":"Tier1"},{"SLA":"Tier2"}

Bob, 2020-06-18,CustomerAddress,{"Line":"12 Test Street"},{"Line":"13 Test Street"}

I could possibly have the application modified so that it rights to a central audit table and use that instead. Our extended reporting uses SSRS and getting the entire record at a point in time maybe problematic. This system is also multi tenanted so the tables could grow quite quickly

Joe Torre SSChampion Points: 10248 More actions · Answer 5

My suggestion is to create a separate database and insert the changes periodically into tables in that database using tables one for each temporal table. I would produce the report then truncate the tables in the report database. If the need to reproduce the report down the road exists you could backup the report database before truncating.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 6

Leighton21 wrote:

Only the temporal tables. So one would be customer and another would be customer address. The report would show the transactions against each chronologically for example
Username, EventDate, Table, NewValue, OldValue
Bob, 2020-06-18,Customer,{"SLA":"Tier1"},{"SLA":"Tier2"}
Bob, 2020-06-18,CustomerAddress,{"Line":"12 Test Street"},{"Line":"13 Test Street"}
I could possibly have the application modified so that it rights to a central audit table and use that instead. Our extended reporting uses SSRS and getting the entire record at a point in time maybe problematic. This system is also multi tenanted so the tables could grow quite quickly

How many columns do the Customer and CustomerAddress tables each contain. In fact, can you provide the CREATE TABLE statements for each including the Clustered Index and the PK for each?

I ask so that I can help advise you on whether or not to consider the "by column" auditing you bring up, which can be (as suggest) a huge PITA when it comes to reassembling full "records". We have both where I work and there are advantages and disadvantages to both but the number of datatypes of columns in the table have a huge affect on which way to do things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)