Multiple DataFlows with the same SQL Server from Gateway

[JangoChained]

I have a problem with the scheduled updates on the dataflows that I want to have.  The Dataflows are connected to a remote server by a Gateway, where the SQL Server is located. 

I have a Dataflow that retrieves some tables from the SQL Server and has scheduled updates on the refresh, and It works fine. This first DataFlow is for a PowerBI. 

But I want another Dataflow that gathers the data of just one view from the same SQL Server and goes to an Excel, with the purpose of making an Excel with Dynamic Data. When I make this DataFlow and make the schedule refreshes the first DataFlow begins to fail on his updates. Seems like I can't have more than one DataFlow to the same SQL Server. 

I can't gather the data of the view that goes to the excel in the power bi DataFlow, because I need to make the excel dataflow in another account, for security purposes (The information in the Power BI dataflow can't be seen by the excel DataFlow). 

How Can I make the second/excel dataflow to work or maybe I can retrieve the data of one view for the excel with a gateway with another solution that isn't a dataflow?

[collinq]

Hey @JangoChained ,

In the Excel you can use the get data and just get the output from the dataflow.  But, if you want one single bit itself and only it, you will have to make that its own dataflow.  Does that help?

[JangoChained]

Hey @collinq thanks for your help, but I think that I'm not explaining the whole situation right (I'm still learning about dataflows and also excel, a software that I don't know quite much like Power BI).

So I have a dataflow with some sql server views, that refreshes everyday 5 times. This dataflow is for the Power BI let's call it PB Dataflow. The information of the flow comes from a server that has a gateway on premise so the PB can update the data from the power bi service. This one works fine.

I want a second dataflow with 2 diferent views for making an excel that refreshes the data with the flow, let's call this the Excel Dataflow. When I try to connect this dataflow it works, but the refresh from the PB Dataflow fails (running at diferent times, it's not a resource problem from the server).

The PB is made from an account and shared with an account from another organization. The excel needs to be opened with the same account that the PB is shared, the client org, but I can't make it work.

I tested some things like putting all the views in the PB Dataflow, making a new semantic model with the 2 views for the excel and sharing the semantic model with the other account. This method doesn't work and I don't understand why, the dataflow refreshes fine but I can't acces the data from the another account (even than the semantic model is shared and even also I tested sharing the whole workspace). When I try to get the data from dataflow or from power bi in excel, the dataflow doesn't show, and i changed the configuration of the origin of data with the info from the org account. I tested many methods but I can't understand why this fails, it's some tennant issue but I don't know how to surpass it. 

Hope this explanation was more detailed and thanks a lot for your insights, they helped me focusing on the problems and testing new things.

[collinq]

Hi @JangoChained ,

I am keying in on this sentence (2 sentences 🙂 ) that you stated:
"The PB is made from an account and shared with an account from another organization. The excel needs to be opened with the same account that the PB is shared, the client org, but I can't make it work."

In your Admin Portal do you have teh external users set to enabled?  Also, does that other account have the right permissions in your workspace or even your tenant?

[JangoChained]

Hi @collinq thanks for your time. 

My Org account has admin role in the server and tennant from the client, because we are the IT department for this client. 

I tested giving permissions to the client org account to the excel dataflow semantic model, even giving permissions to the whole workspace, but even then I can't get data from dataflows in Excel, the dataflow doesn't show on the list (the PB Dataflow doesn't show either) so i don't understand. 

Right now I think that I will mantain the PB Dataflow because it works right and I will try to get the data for the excel with an ODBC data source, between the client and the client server. 

I made a little diagram in case someone sees it better this way.

[collinq · Accepted solution]

Hey @JangoChained ,

I am still struggling iwth this one as well.  Thanks for the chart.  So, to make sure I totally udnestand,  EITHER the Excel Dataflow or the PB Dataflow works?  But not both, right?

I am also wondering about the Gateway and Data Source setup for the Excel file.  Since the Dataflow fails right away, does the second account have the rights to the Excel Data Source connection in the "Manage Connections and Gateway" settings?

And, the account that is not working with Excel has at least a Power BI Pro license, right?

And, in Desktop you can get to the Excel file with that other account and the file opens as you expect?  

Do you have RLS set on any of this?  Or even Object level security on the file?

I am also assuming that you are not using a Live Connection to the Excel file?  I see this note "Users external to the tenant hosting the Power BI semantic model can't analyze Power BI data nor load Power BI data with live connection in Excel." at this article: Create Excel workbooks with refreshable Power BI data - Power BI | Microsoft Learn   

Also, you used the word "dynamic" in this thread.  Based on this article (Data refresh in Power BI - Power BI | Microsoft Learn) and this comment, maybe you are running into a feature and not a bug?  "In most cases, Power BI semantic models that use dynamic data sources can't be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed."

Let me know!

[JangoChained]

The client org account is the same, I should specified in the chart. The account where the dataflows are shared has a power bi pro license indeed. 

The Power BI dataflow works but If I put the excel dataflow stops updating right. Only one dataflow can work at the same time, and by working I mean that the scheduled refreshes go through. I don't have RLS or other security measure. 

The problem it's not about permissions, I'm sure it's about tennants and servers, because I tested changing the granted permissions to the client account, but that didn't change the outcome. 

I solved it taking the excel data from an ODBC data source, so I won't be making more changes because the project it's ending soon. 

Thanks a lot for the help, I will choose your last answer as the solution since it's the most complete answer and the information from the links is pretty good. Sorry for my late response, I was on vacation. 

[collinq]

Hey @JangoChained ,

I think that the issue may be that you can't have them both running at the same time.  Perhaps if you schedule them to run at different times ,it should work fine.  If not, then perhaps your SQL server is a bit underpowered and can't handle that much processing at one time.  If it still has problems, watch the SQL logs to see what might be the issue.

[JangoChained]

Hey @collinq , 

I tested your theory, with the sheduled refreshes at diferent times. The moment I connect the dataflow for the excel the refresh from the Power BI dataflow fails.

I need one gateway for each dataflow because the excel users can't acces the data from the Power BI dataflow, that's why I can't just put the view of the excel dataflow in the PB dataflow, I need separate flows for better security / privacy of the data.