Universe design suggestions to support SQL Server Temporal Tables

[omacoder]

We are starting to use temporal tables in SQL Server 2016. This is going to be most beneficial for tracking trends overtime as using temporal tables basically automates the process of turning a table into a Type 2 table.

Now I'm trying to consume this temporal table in the universe. When the user runs a query against this table, it should prompt the user to choose the "As of Date", with a default to the current date. The user should be able to choose a list of predefined dynamic variables (ie, Yesterday, End of Prior Month, End of Prior Quarter, etc).

(Dynamic is a requirement so that the reports can be scheduled based upon a calculated date).

The syntax for querying this temporal table is as follows:

DECLARE @AsOfDate DATETIME2
SET @AsOfDate = SYSUTCDATETIME()

SELECT * 
FROM PS.DimActivityData_Current  
FOR SYSTEM_TIME AS OF @AsOfDate

Any suggestions on how I can implement this in IDT?

Running BI 4.2 SP4.

I tried creating a derived table in the data foundation using a prompt for the @AsOfDate and it works as expected, except I cannot figure out how a user can choose a dynamic variable or dimension as the prompt value.

[marton_retfalvi]

Hi Brian,

I think what you want is something similar to that what is explaind in this blog:

https://blogs.sap.com/2016/03/10/custom-predefined-date-range-filters-wtdmtdqtdytd-in-bo/

Marton

[omacoder]

Thank you Marton for your feedback! I tried implementing that example, but that doesn't work in this use case because the SQL doesn't validate.

For temporal table querying in SQL Server, the 'FOR SYSTEM_TIME AS OF' clause resides at the end of the statement as a query hint and not in the from clause. Nonetheless, it does not like case statements in that clause, and can only accept a date (or a variable as in the above example).

I tried putting the case statement in a declaration at the top of the derived table, but that also does not validate as it seems IDT doesn't know how to parse a derived table that starts with a DECLARE.

Validation fails in derived table:

SELECT * 
FROM PS.DimActivityData_Current  
FOR SYSTEM_TIME AS OF (case
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’WTD’ then DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’MTD’ then DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’QTD’ then DATEADD(q,DATEDIFF(q,0,GETDATE()),0)
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’YTD’ then DATEADD(year,DATEDIFF(year,0,GETDATE()),0)
END)

Validation fails in derived table:

DECLARE @AsOfDate DATETIME2
SET @AsOfDate = (case
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’WTD’ then DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’MTD’ then DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’QTD’ then DATEADD(q,DATEDIFF(q,0,GETDATE()),0)
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’YTD’ then DATEADD(year,DATEDIFF(year,0,GETDATE()),0)
END)

SELECT * 
FROM PS.DimActivityData_Current  
FOR SYSTEM_TIME AS OF @AsOfDate

[marton_retfalvi]

I checked the SQL what you copied here and the first problem is with the single quotation marks, you need to change them from ‘ and ’ to this: '

Furthermore, I used the below SQL with one of my test table and seems it works:

SELECT

MilkProduction.Date

FROM

MilkProduction

WHERE

MilkProduction.Date IN

(case

when @Prompt('Enter value','A',{'WTD','MTD','QTD','YTD'},Mono,Free,Not_Persistent)='WTD' then DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)

when @Prompt('Enter value','A',{'WTD','MTD','QTD','YTD'},Mono,Free,Not_Persistent)='MTD' then DATEADD(month,DATEDIFF(month,0,GETDATE()),0)

when @Prompt('Enter value','A',{'WTD','MTD','QTD','YTD'},Mono,Free,Not_Persistent)='QTD' then DATEADD(q,DATEDIFF(q,0,GETDATE()),0)

when @Prompt('Enter value','A',{'WTD','MTD','QTD','YTD'},Mono,Free,Not_Persistent)='YTD' then DATEADD(year,DATEDIFF(year,0,GETDATE()),0)

END)