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.