Forum Discussion

EdvanceIQ_Chris's avatar
EdvanceIQ_Chris
Regular Visitor
9 months ago
Solved

Version AsOf in semantic model.

I ingest data via notebooks into CDC enabled lakehouse tables and they enable timetravel for up to 7 years and can be updated up to three times a day.  The select * from table version asof xx works ok from notbooks and views, but I need to be able to do this from a directlake model.  I need to be able to query the data as of a version across all the tables (about 150) returning the version for each that I specify.  My question is, how can I do this in the model, as there does not appear to be support for version in the models yet.  How can I query the tables as of the version number from the model?  Creating thousands of views is not practical.  If there is no way to do this now, when will the version asof be available to run through models?  Is it on the roadmap?  There must be a way of doing this?  I need users to be able to select a date and then the model returns data from the underlying tables asof the version at the date and time specified (I capture the version number for each table after each back import).  Thanks

5 Replies

    • EdvanceIQ_Chris's avatar
      EdvanceIQ_Chris
      Regular Visitor

      I could not get it to run live, but compromised with allowing users to pass parameters as a batchID and then refresh a model.  Took time bit worked.  Based on the function I used to decide if the parameter for the batch was passed or not would be how the data was queried.  For those that may find this useful the function was : 

      // Query: GetDeltaLakeTable
      (tableName as text) as table =>
      let
          // Build base connection string
          conn = "https://onelake.dfs.fabric.microsoft.com/" 
                 & pWorkspaceName & "/" 
                 & pLakeName & ".Lakehouse/Tables/" 
                 & pSchema & "/",
      
          // Load the target table
          Source = AzureStorage.DataLake(conn & tableName, [HierarchicalNavigation=true]),
          ToDelta = DeltaLake.Table(Source),
      
          // Decide whether to time travel
          timeTravel = Text.Upper(pBatchID) <> "BLANK",
      
          // Branch logic
          returnVal =
              if timeTravel then
                  let
                      // Load CDC batch log table
                      batchtbl = AzureStorage.DataLake(conn & "_cdc_batch_log", [HierarchicalNavigation=true]),
                      logTable = DeltaLake.Table(batchtbl),
      
                      // Lookup version for this batch + table
                      Version = GetTableVersion(tableName),
      
                      // Get snapshot at that version
                      GetVersions = Value.Versions(ToDelta),
                      Result = if Version <> null then GetVersions{Version}[Data] else ToDelta
                  in
                      Result
              else
                  ToDelta
      in
          returnVal
  • Hi EdvanceIQ_Chris,

    Thank you amitchandak, for your insights.

    DirectLake reads the Delta table only at the most recent framed state, so time-travel queries aren’t available in this mode. The Warehouse time-travel documentation also notes that time travel is not available on the Lakehouse SQL analytics endpoint, and that Power BI DirectQuery cannot use time-travel syntax.

    Time Travel - Microsoft Fabric | Microsoft Learn
    Direct Lake overview - Microsoft Fabric | Microsoft Learn

    Thank you.

    • EdvanceIQ_Chris's avatar
      EdvanceIQ_Chris
      Regular Visitor

      Thanks for the comment but it is not particularly helpful.  I can see that it is not available at the moment, so how can I get it to work?  What is the solution?  Or, is there a roadmap where this functionality is due to be added.  I want to allow users to select a date in the reports and for the data for those reports to be served up as of the version that existed for all the tables at the required date.  With the functionality available in the underlying tables, it seams crazy that there is no way for a user to be able to select the date/version and use the data from that point.  

       

      • v-saisrao-msft's avatar
        v-saisrao-msft
        Community Support

        Hi EdvanceIQ_Chris,

        At the moment, DirectLake does not have built-in support for user-initiated time-travel. You may want to submit a feature request on the Fabric Ideas forum for Microsoft’s consideration. If the idea receives enough votes, it will be prioritized. You can submit your idea here.

        Fabric Ideas - Microsoft Fabric Community

        Thank you.