Forum Discussion
How to query data directly from on-premises SQL Server using Microsoft Fabric
Hi,
so as per my understanding, i am gving this answer and also i replicated the scenarion on my local PBI desktop.
so you are pushing final table to lakehouse after applying tansfermations in datadlow and creating semantic model on top of the final tbale in lakehouse and trying to create PBI reports.
instead of creating semantic model on top of final tables in lakehouse, just open PBI desktop and goto Onelake data hub and select lakehouse as source and select required tables from required lakehouse and same way select from SQL server(select required table/write query), so that the sematic model will create in DQ mode insted of live(which is almost similar to live mode only, but it allows querying from SQL serverb data as well)
please refer my images for your reference.
They would want to keep the sensitive data on-primesis, and move the rest of the data into Fabric. We have suggested direct quering the sensitive data, but I can't seem to get this right in a demo I'm doing.
I've created an on-premises gateway connection to the SQL Server, moved some data into a fabric lakehouse, created a dataflow for some transformations, and created a semantic model to report from. From here, there is no option to direct query another source. So I downloaded the Power BI file, as there is more functionality in PBI Desktop, and tried using a direct query from PBI Desktop, but then I get an error saying that I need to change my model to enable Direct Query.
From the start of the project, right up to where I am now, I didn't see anywhere where I had the option to do this.
I hope this makes sense?
and created a semantic model to report fromInstead of using that I would create the semantic model in Power BI Desktop. Connect to the on-prem source in Direct Query mode, then connect to the lakehouse's SQL endpoint in the same way, then add local tables as needed.