Skip to Content

Universe design suggestions to support SQL Server Temporal Tables

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Mar 26 at 09:35 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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)