All Forums Database
Willimoa 63 posts Joined 10/09
14 Jan 2014
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

Willimoa 63 posts Joined 10/09
21 Jan 2014

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.

You must sign in to leave a comment.