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.
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 ?
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.