topic Re: From SQL to PowerBI : intermediary file in Data Engineering

From SQL to PowerBI : intermediary file

LogiqueNeutre — Tue, 18 Feb 2025 08:42:26 GMT

Good morning community,

after a lot of work, i have to admit i fail at solving this problem.

I currently have several Power BI files, each using a connector, and each being refreshed twice a day by this connector. They get most of their data from a single SQL database, which also has the connector.

I thought i could reduce a lot the server load induced by the refreshes by having an intermediary file, doing the requests (twice a day, still), and then having each Power BI file getting their data from this intermediary file instead of the SQL database.

If i make this file a Power BI file, i can't find out how to properly get data to other Power BI files from this Power BI file.

If i make this file an Excel file, i tried many things, but i don't find how to properly get it updated twice a day without having a dirty solution like the server opening this Excel and a macro refreshing it (which will not happen). I found a "solution" in power automate, but it doesn't work (the script gets executed, supposedly, the data doesn't get refreshed, while power automate says the flow worked fine.

I also found this, https://damobird365.com/export-power-bi-to-excel-with-power-automate/#Populate-an-Excel-file-with-Graph-API , but i don't fully understand everything involved, which makes it a nightmare to reproduce, and even more to maintain, if it really is a solution, which i'm not certain of.

I also tried and considered other things, but all failed.

Thank you for any help, any lead, any info !

Re: From SQL to PowerBI : intermediary file

nilendraFabric — Tue, 18 Feb 2025 09:22:12 GMT

Hello @LogiqueNeutre

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.

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

Re: From SQL to PowerBI : intermediary file

LogiqueNeutre — Tue, 18 Feb 2025 09:41:03 GMT

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.

Re: From SQL to PowerBI : intermediary file

nilendraFabric — Tue, 18 Feb 2025 09:50:20 GMT

Semantic models in “My Workspace” cannot be used as Live/DirectQuery sources for other reports. This is a known restriction.

For shared access, semantic models must reside in a dedicated workspace (not “My Workspace”) and have proper permissions.

Create a new workspace (e.g., “Central Data”) in Power BI Service.

Republish your report to this workspace.
• This ensures the semantic model is stored in a shared workspace, bypassing “My Workspace” restrictions.

Re: From SQL to PowerBI : intermediary file

LogiqueNeutre — Tue, 18 Feb 2025 10:17:32 GMT

Ohhh it seems good.
I just have an issue, when i do the connection from the Power BI file to the Power BI intermediary file, it says i don't have the authorization to call the Discover method. But i just created the workspace, like you said, i'm administrator of it and all authorizations seem to be put... So i wonder what should i do to solve this.

In details :

the first operation in Power Query works, resulting in a table of 5 columns and 1 row, columns named "Id", "Name", "Description", "Kind", "Data". I don't know why the second query is Table.Combine(Source[Data]), but ok..., the third query fails, it is
= Cubes{[Id="Model", Kind="Cube"]}[Data]
and the failure is :

DataSource.Error : AnalysisServices : L'utilisateur '<euii>[my company email, i edited]</euii>' n'a pas l'autorisation d'appeler la méthode Discover.

Détails techniques :
RootActivityId : [some id, i edited]
Date (UTC) : 2/18/2025 10:03:52 AM
Détails :
DataSourceKind=AnalysisServices
DataSourcePath=powerbi://api.powerbi.com/v1.0/myorg/[nameoftheDataFile, i edited]

I feel we're close to the solution, thanks to you !

Re: From SQL to PowerBI : intermediary file

LogiqueNeutre — Tue, 18 Feb 2025 10:23:09 GMT

I have a question though.

If we succeed in solving this last problem, are we sure that updating the end Power BI file will only fetch the data from the intermediary file, and that it won't fetch the data from the entry database..? It's required, otherwise, there is no point in having an intermediary file (because each end file updating its data will again result in queries to the database) !

Re: From SQL to PowerBI : intermediary file

nilendraFabric — Tue, 18 Feb 2025 13:42:29 GMT

If you set up your Power BI intermediary file correctly and configure the downstream Power BI files to connect exclusively to this intermediary file, then refreshing the downstream files will not query the original database. Instead, they will fetch data only from the intermediary file.

Downstream Power BI files connect to the published dataset (semantic model) from the intermediary file using a Live Connection or DirectQuery.

Re: From SQL to PowerBI : intermediary file

Anonymous — Wed, 19 Feb 2025 06:21:55 GMT

Hi @LogiqueNeutre,

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.

Re: From SQL to PowerBI : intermediary file

LogiqueNeutre — Wed, 19 Feb 2025 08:32:44 GMT

I'm probably doing something wrong, i cannot connect the end Power BI files to the intermediary Power BI file.

Could you please provide a detailed step by step way to do it ? My online researches have so far not helped at all.

Re: From SQL to PowerBI : intermediary file

LogiqueNeutre — Thu, 20 Feb 2025 10:33:49 GMT

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

DirectQuery à AS – [nameOfTheModel]

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.

Re: From SQL to PowerBI : intermediary file

Anonymous — Fri, 21 Feb 2025 07:31:02 GMT

Hi @LogiqueNeutre,

It sounds like you're trying to connect multiple Power BI reports to a central dataset but are running into some issues. Here’s a step-by-step guide to ensure everything works fine
* Make sure the Intermediary Dataset is Published to a Shared Workspace. Go to your PowerBI Desktop

and connect to your SQL database. Do any necessary transformations and modeling and then publish

the report to a shared workspace.

* Enable DirectQuery for Live Connection by going to the workspace where you published the dataset in

PowerBi Service. Now go to Dataset's setting and enable DirectQuery for Live Connections if you want

real-time updates.

* Now connect other reports to the published dataset by creating a new report. here select the data

source in Get Data as Power BI Semantic Models. Select the workspace where you published the

dataset and choose the dataset and click Connect.

Now after your report is done you can publish them as usual.

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.

Re: From SQL to PowerBI : intermediary file

LogiqueNeutre — Fri, 21 Feb 2025 08:16:10 GMT

Thank you for your message.
Step 1 : "

Make sure the Intermediary Dataset is Published to a Shared Workspace. Go to your PowerBI Desktop

and connect to your SQL database. Do any necessary transformations and modeling and then publish

the report to a shared workspace."
Done.

Step 2 : "

Enable DirectQuery for Live Connection by going to the workspace where you published the dataset in

PowerBi Service. Now go to Dataset's setting and enable DirectQuery for Live Connections if you want

real-time updates."
This is the part i don't find at all. I search everywhere in the newly created workspace, in settings, also elsewhere, and i don't find this setting, DirectQuery for Live Connection.

I searched on the web also, and i find nowhere where can this option be.

Re: From SQL to PowerBI : intermediary file

Anonymous — Thu, 27 Feb 2025 01:35:58 GMT

Hi @LogiqueNeutre,

Thank you following up on our previous conversation. Regarding the "Enable DirectQuery for Live Connections" setting this option is only available if your Power BI workspace is in a Premium Capacity (P1 or higher). If your workspace is Pro or Free, this option will not appear in the dataset settings.

If you don't have Premium capacity, you can still connect your reports to the published dataset using Live Connection, which works similarly to DirectQuery.

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.

Re: From SQL to PowerBI : intermediary file

Anonymous — Sun, 02 Mar 2025 18:39:57 GMT

Hi @LogiqueNeutre,

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.

Re: From SQL to PowerBI : intermediary file

LogiqueNeutre — Tue, 04 Mar 2025 08:32:04 GMT

Hello,

i've tried what was told me, and once it did the same problem i reported, once it didn't. Since i wasn't using at that time a file that can properly test the solution, i'm first finishing the job so that i can ACTUALLY test the solution in real conditions and then tell you if it works or not, and in this last case, show a detailed report with screenshots of what's happening.

In a few days, you should see another more on-subject answer from me.

Re: From SQL to PowerBI : intermediary file

LogiqueNeutre — Tue, 04 Mar 2025 14:18:37 GMT

I don't know how to say it any other way. So i'll put images.

1. I published my first Power BI dataset, let's call it "Donnees" which means Data in french, to a workspace that is not "My workspace" but another.

2. When i am in the second Power BI, i try to do as you said, "you can still connect your reports to the published dataset using Live Connection". First, i click "Get data" then "Power BI semantic model", look :

3. then i click this dataset (hidden the dataset sensitive data in red, the other in dark red) and then "connect" :

4. a window appears, "Connection to your data", which shows the various tables existing in Donnees, and i choose which to import and click "send", look :

5. a window appears, showing it is processing the thing :

It works out,

6. Then i see i got new tables in Data, starting by the prefix i set,

7. Then i go to transform data editor, and i cannot find or seem to be able to load any of the tables i've just loaded.

I read online that direct query doesn't enable much transformation, that the data must be worked in the source file.

…ok then, how do i copy my hundreds of power query steps at once from my 96 tables into the source Power BI file ..??

Re: From SQL to PowerBI : intermediary file

Anonymous — Mon, 10 Mar 2025 09:54:22 GMT

Hi @LogiqueNeutre,

Thanks for sharing the details. Based on what you’ve described, the issue is that Power BI does not allow modifying Power Query transformations when using a Live Connection to a published dataset. Instead, all transformations need to be done in the source dataset before publishing. This is by design Microsoft doesn’t allow modifying a dataset from another report. To resolve this, you’ll need to move your Power Query transformations into the original dataset.

You can manually copy PowerQuery steps (M Code) by following these steps:

* Open your current PowerBI file then go to "Transform Data".
* Right-click each table and then click Advanced Editor. Then copy the M code.
* Open the original dataset file and go to Transform Data.
* Click New Query then Blank Query and then paste the copied M code.

* Repeat for all tables, then Close & Apply and Publish again.

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.

Re: From SQL to PowerBI : intermediary file

LogiqueNeutre — Mon, 10 Mar 2025 13:49:41 GMT

Hello,

thank you for your answer and the suggestion.

While i don't fully rule out this idea, i would like to check (with anyone willing to help) that there is no other solution to my problem.

Remember (as explained in my first posts here), my problem is that i don't find how to properly factorize my requests into one file only (whatever the type), and then have my 3 power bi getting their data from this single file.

We have explored here the solution of a single power BI file then sharing its dataset to the 3 next power bi files.

How about an excel file, with 1 table per tab ?

How about power automate ?

(My company doesn't use Azure, so i doubt the Azure Data Lake Storage can be used here)

The drawback of having to separately modify two files for one result only is a bit annoying. If i can avoid it with a better solution, let's find out, it will also avoid me copying 130+ sets of queries (yes, with the advanced editor, which factorizes the queries into one) from 3 Power BI files into 1 Power BI file.

Thank you everyone.

Re: From SQL to PowerBI : intermediary file

Anonymous — Mon, 10 Mar 2025 18:48:03 GMT

Hi @LogiqueNeutre,

Thanks for your follow-up. I understand that copying almost 130 queries manually is not an easy task, so let's find some other ways to do it:

As you wanted to try storing data in Excel, let’s check if it can work as a Data Source. Follow these steps to create an ecxel file for your data.

* First Create an Excel file with one table per sheet.

* Then Load data from SQL using Power Query in Excel.

* Now you have to save the file in OneDrive or SharePoint for accessibility.

* In Power BI, connect your reports to this Excel file.

* You have to use Power Automate to refresh the Excel file twice a day.

Another option is using Power Automate for Automated Refresh. Instead of Excel, keep your Donnees dataset and use Power Automate to refresh it twice a day. Your three Power BI reports connected to it via Live Connection.

You can also use Power BI Dataflows stored in OneDrive or SharePoint. First create a Dataflow in Power BI Service to centralize SQL queries then connect all three reports directly to the Dataflow instead of SQL. Using this you don't need to manage multiple copies of queries.

I hope this solves your current requirements. 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.

Re: From SQL to PowerBI : intermediary file

LogiqueNeutre — Tue, 11 Mar 2025 08:10:07 GMT

I'd love to find out how to properly use Power Automate to refresh the Excel file twice a day. Quoting my own very first post in this conversation,

"If i make this file an Excel file, i tried many things, but i don't find how to properly get it updated twice a day without having a dirty solution like the server opening this Excel and a macro refreshing it (which will not happen). I found a "solution" in power automate, but it doesn't work (the script gets executed, supposedly, the data doesn't get refreshed, while power automate says the flow worked fine.

I also found this, https://damobird365.com/export-power-bi-to-excel-with-power-automate/#Populate-an-Excel-file-with-Gr... , but i don't fully understand everything involved, which makes it a nightmare to reproduce, and even more to maintain, if it really is a solution, which i'm not certain of."

I followed several tutorials on how to refresh the Excel file by Power Automate, none worked, or i failed, i don't know.

I'm going to retry later on today and show images of the problems i have while trying to setup Power Automate. If in the meanwhile anyone has a method they KNOW to work, i'm all ready to follow it.