Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Brian Wilson]

Searched the forums and either my search was off or somehow missed it, but here is the situation.

We have a PowerBI report which gets data from two on-premises databases, and in one of those two queries it appends data from an Azure SQL DB.  On the desktop, this works with no issues, but when published to the PowerBI service, the dataset won’t refresh.  From there, it connects to two on-prem db’s using a gateway, and to Azure SQL directly.  The error it gets is:

Data source error{“error”:{“code”:”DM_GWPipeline_Gateway_MashupDataAccessError”,”pbi.error”:{“code”:”DM_GWPipeline_Gateway_MashupDataAccessError”,”parameters”:{},”details”:[],”exceptionCulprit”:1}}} Table: One Touch Action Register Template_InUse (Main).

I’m inclined to think it is that the gateway is trying to pull the Azure db data to append, rather than passing it up and having it done on the service, but am not positive.

If I transfer the Azure SQL data down to an on-premises db and then access through a gateway, it does work.  The same is true if I push the on-premises data up to Azure SQL, so it only fails if I try to mash up.

I do have a ticket open with support, and will update if I get anything through it.

——————————
Brian Wilson
Data Architect
——————————

[Lutz Bendlin]

this seems to be a continuation of the wobbles we have seen over the last couple of weeks with sharepoint connections (mostly on-prem, but some SPO as well – equally inexplicable as your Azure source). Good luck with the ticket, please do update us on what they found.

——————————
Lutz
——————————
——————————————-

[James Walsh]

Hi
Following as I’m also having issues mixing on prem SQL and cloud based data via a data gateway. Seems ok until the point where I merge two data sources in a single query. It’s very frustrating if you can’t do this, most of the excitement around PowerBI for my company is around the ability to combine data sources to get joined up reporting.
James.

——————————
James Walsh
Isosceles Finance Limited
Egham
——————————
——————————————-

[Lutz Bendlin]

I think you are on to something here.
This may be related to the new data privacy settings they recently implemented. Cross source scripting etc.

——————————
Lutz
——————————
——————————————-

[Rob Littler]

Hi Brian

Combining two sets of data from different data sources at the query level isn’t particularly good practice; could you bring the data together in a different way? You could do this within Power Query or if you have large datasets and are worried about performance you could look to process the data in advance e.g. into a data warehouse and then use that as the source.

Regards

Rob

——————————
Rob Littler
BI Developer
Warwick
——————————
——————————————-

[Brian Wilson]

Hi Rob,

I wholly agree on the advisability of combining data sets at the query level, although unfortunately staffing levels and deadlines sometimes lead us temporarily to sub-optimal solutions.  Longer term, our plan is to combine in our data warehouse, but we’re in an ERP transition, and this particular reporting requirement was missed early, so the mash up is a bridge to better options.

Any tips on the Power Query combo front would be welcome, it’s an area with which we have no specific internal skills.

Thanks

——————————
Brian Wilson
Data Architect
——————————
——————————————-

[Rob Littler]

You can achieve this in a number of ways in Power Query; this is loosely what I would do:

1. Import the two sets of data as separate queries (make sure there is something in the two sets of data that can be used to link them)
2. From the Home ribbon select Merge Queries, Merge Queries as New
3. Select the two imported tables and select the column or columns that provide the join. Also specify the join kind. Click OK
4. With the merged table selected expand the column that currently contains table records (click the icon in the column header and select the columns you want to keep)
5. Rename the merged table to something more meaningful
6. Disable the original tables from loading into the model (right-click and uncheck Enable load)

Hope this helps. Any questions please shout.

Rob

——————————
Rob Littler
BI Developer
Warwick
——————————
——————————————-

[Brian Wilson]

One more update – we actually are doing PowerQuery for the pull from the sources in question.  For better or worse, it’s just the append operation (which ideally, yes, we would do within a data warehouse) between the cloud and on-prem source which fails. 🙁

——————————
Brian Wilson
Data Architect
——————————
——————————————-

[Brian Wilson]

For all who were following, the issue turned out to be a lack of connectivity between the gateway and the Azure SQL DB.  As noted, the append operation between the on-premises source and our beginning data warehouse worked fine on desktop, but was failing to refresh when published to the service.  

Once the gateway could access the service, the append worked, as it appears the dataset is assembled on the gateway.

——————————
Brian Wilson
Data Architect
——————————
——————————————-