From SQL to PowerBI : intermediary file

Hello Anonymous 

Not sure if you have tried this

Build a Power BI report that connects to your SQL database and performs all necessary data transformations.
 Publish this report to the Power BI Service. This creates a dataset in the service

In Power BI Desktop, use Get Data > Power BI datasets to create new reports that reference the shared dataset ().
• This uses a Live Connection, so visuals query the centralized dataset instead of the SQL database directly.

Or 

use Dataflows to extract and transform data from SQL. This stores the data in Azure Data Lake Storage (ADLS) 
• use Power BI dataflows to build reports from the pre-processed data.

if this is helpful please accept the answer and give kudos

Well i'm trying to do as you said.

I did the Power BI report getting its data from the SQL database. It does a few needed transformations. I published it : where is the dataset ? What i get from publishing is a report and a semantic model.

If i use correctly "Get Data", i see then "Power BI semantic model", which is nice because i can select the one i just published, but then i select it and click "connect", it says "DirectQuery isn't available for this dataset. Either this dataset doesn't support DirectQuery, either you selected a dataset stored in My Workspace". Well yeah i published it to my workspace, didn't i ?

I'm okay with the dataflow. Are we talking of Power Automate here ? Could you describe more precisely the process please ?

Thanks for your answer ! I will accept and give kudos as soon as my problem is solved, no worries.

Hi Anonymous,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you’re trying to optimize the refresh process of multiple PowerBI reports which are pulling data from a single SQL database. As nilendraFabric responded to your queries kindly go through his responses and if you are able to solve your issue then please mark the helpful reply as solution.

I would also take a moment to thank nilendraFabric, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

When i use "Get data", "Semantic model", and want to use the semantic model i published on the newly created workspace, it tries to do a direct query, then fails saying

AnalysisServices : L'utilisateur '[email of my user]' n'a pas l'autorisation d'appeler la méthode Discover. Détails techniques : RootActivityId : [some id] Date (UTC) : 2/20/2025 9:34:21 AM

Also i don't see where the request newly created "DirectQuery à AS – [nameOfTheSemanticModel]" is in Power Query, i searched everywhere. I do see it "opened" but i don't know where from. It has the 3 steps i've written earlier. On the second one, called "Cubes" and being "= Table.Combine(Source[Data])", there is a warning "This step generates a query that isn't supported by DirectQuery." and there's a button to swap all tables to Import mode, which would be good to me, but when i do, it tries to refresh all data, and does the same mistake i've written up there.

In https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-discovery it is said that there's an option to tick to make the dataset discoverable. There isn't : the menu "Endorsement and discovery" is actually a menu "Endorsement" only. I can't find the option anywhere.

If needed i can provide more details on more things i tried to solve this problem one way or another.

Hi Anonymous,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

You have all my apologies for the delay. This topic is still an issue to my company.

I'll be back to it with what you asked as soon as possible. In the meanwhile, the issue might become easier to solve, since the company is considering using a Premium Per User account instead of a Pro one.

Hi Anonymous

Thanks for sharing this here!!!