Forum Discussion
Memorable MemberBUG or FEATURE???::DirectLake mode not supported
And wouldn't you know it... you have to go traipsing through a labyrinthine thicket of uncommon sense and other assorted illogical UI paths. So here is the solution to connect to lakehouse tables in DirectQuery mode--the long and short of it, you need to figure out where the option to connect to the lakehouse SQL endpoint is, and once you do, select that and badabing-badaboom!, you are in business. Totally ridiculous UI/UX design if you ask me (not to mention that the previous attempts do not generate any visual feedback to let you know what the hell is going on), but there you've it:
Just a simple CREATE VIEW where I select a computed col in addition to all the cols from the source table. I'd be curious to learn what's the best approach though, in terms of efficiency and so on. What happens each time a report queries the lakehouse table (in DirectQuery or DirectLake mode)? Is the view recomputed from scratch every single time? Or does Fabric use materialized views like Oracle? The underlying data store being managed by Microsoft, I'd assume they configure it so that it is optimized for READ speed, but who knows, right?
I also heard you actually can WRITE to a lakehouse table, instead of just creating a virtual table, but only if using PySpark in a notebook. Haven't tried yet.DROP VIEW vTABLE_CALC_COL; GO CREATE VIEW vTABLE_CALC_COL AS SELECT *, ([count] /2 ) AS calcol FROM Data_15MThe new calculated col, calcol, then shows up in dataflows and models.
Community ChampionElement115 Power BI Cookbook 3rd Edition, I just finished up the first drafts. Has an entire chapter on Fabric.
Memorable Memberre Fabric, would be nice to have something about how to create calculated columns when ingesting into a lakehouse if not using a DFg2. Say, like the scenario of a pipeline with a bulk copy action and one does not want to push that through a DFg2 for whatever reason. From what I discovered playing around, one can create a view with the SQL Analytics endpoint to create calc cols, or using a Spark notebook.
- Greg_Deckler
Element115 What SQL Query did you use to add a column to your view? Because I haven't been able to get the ADD keyword to work in the SQL Analytics Endpoint.
- Element115
Just a simple CREATE VIEW where I select a computed col in addition to all the cols from the source table. I'd be curious to learn what's the best approach though, in terms of efficiency and so on. What happens each time a report queries the lakehouse table (in DirectQuery or DirectLake mode)? Is the view recomputed from scratch every single time? Or does Fabric use materialized views like Oracle? The underlying data store being managed by Microsoft, I'd assume they configure it so that it is optimized for READ speed, but who knows, right?
I also heard you actually can WRITE to a lakehouse table, instead of just creating a virtual table, but only if using PySpark in a notebook. Haven't tried yet.DROP VIEW vTABLE_CALC_COL; GO CREATE VIEW vTABLE_CALC_COL AS SELECT *, ([count] /2 ) AS calcol FROM Data_15MThe new calculated col, calcol, then shows up in dataflows and models.
- Greg_Deckler
Element115 Ah, I was trying to use the ADD keyword ADD ( [count] / 2) AS column. I'll try again.