Forum Discussion
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
EdvanceIQ_Chris , I doubt we have that available in the Semantic Model Yet.
We have a Power Query function get DeltaLake.Table M (https://youtu.be/D_8ldwRTkyg), but that is also static to the data load
Snapshot is one way, but that will have too much data.
5 Replies
- amitchandakSuper User
EdvanceIQ_Chris , I doubt we have that available in the Semantic Model Yet.
We have a Power Query function get DeltaLake.Table M (https://youtu.be/D_8ldwRTkyg), but that is also static to the data load
Snapshot is one way, but that will have too much data.- EdvanceIQ_ChrisRegular 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
- v-saisrao-msftCommunity Support
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_ChrisRegular 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-msftCommunity 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.