CREATE compound Temporal view to allow AS OF Reporting


CREATE compound Temporal view to allow AS OF Reporting


I would like to create a Semantic Layer compound view that includes a TransactionTime temporal table, the goal is that it should run a "TRANSACTIONTIME AS OF ...  SELECT ....." type of query.   The issue is that in order for the view to be "temporal" I need to have SEQUENCED TRANSACTIONTIME in the view, but as there are joins I get Error 9353.  Multiple tables cant be specified with sequenced transactiontime.

Is this possible ?  Can I create a Compound View that will allow "AS OF" Transaction Time reporting.

Other options:

I can create the view with "TRANSACTIONTIME AS OF ...  SELECT ....." in the query OK, but then I have hardcoded a point in time - I would like the user, or reporting tool, to specify that at query time.   I could also just create a 1:1 view for the temporal table but that will change (complicate) the Semantic Layer design.

A parameterized macro perhaps ?


Andrew W

Tags (1)

Re: CREATE compound Temporal view to allow AS OF Reporting

I can partly answer my own question:

To expose a compound view to allow AS OF reporting,  the view must be SEQUENCED VALIDTIME, and not SEQUENCED TRANSACTIONTIME (as this doesn't work!).  

From a business reporting perspective, this makes sense as the VALIDTIME is the "real world" perspective that should be reported.  So I have changed the tables to include VALIDTIME and the views work.

My next question is:  IS VALIDTIME in a temporal table sufficient on its own ?  It meets the requirement, but does adding TRANSACTIONTIME provide the extra security and additional level of detail that is good practice ?    We are loading in min batches so we will potentially be getting multiple updates in a day.

Additionally, there is another post that asks about SEQUENCED TRANSACTIONTIME:

Is this disallowed for technical reasons, or to encourage the use of VALIDTIME as the preferred approach ?   Access to sequenced transactiontime data is still availabe, but by directly accessing , 1 to 1, the temporal table.