Help Combining Business Central and SQL Data

[SMid]

Dear all,

One of our Navision servers is moving to Business Central. We have several group reports combining data from our Navision servers (data coming from SQL views). I am trying to append the new Business Central queries (running on app) to one of the existing queries so our group reports remain complete. This works like a charm on Power BI desktop. Unfortunately I run into the following error when I try to refresh the new query on Power BI Service:

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_InvalidMashupConnectionString","pbi.error":{"code":"DM_GWPipeline_Gateway_InvalidMashupConnectionString","parameters":{},"details":[],"exceptionCulprit":1}}} Table: Backorders.
Cluster URI: WABI-WEST-EUROPE-D-PRIMARY-redirect.analysis.windows.net
Activity ID: ee17a836-0f4e-403c-b06e-b807d3fb52fc
Request ID: bed53e3e-6f5c-fa93-28dc-865e2c483265
Time: 2022-04-30 14:27:58Z

I can refresh both the original SQL query and the BC query separately (even in the same file) without a problem, so it does not appear to be a credential issue. The error appears as soon as I append or merge the data. Is it not possible to combine data coming from BC and SQL?

Thanks for your help!

Smid

[bcdobbs · Accepted solution]

Hi,

A few things to try...

1) Make sure your gateway has been updated to the latest version.

2) Check privacy levels match on both data sources.

3) Does the gateway have the same permissions on the new data source as you have locally. (Sometimes hit weird issues with SQL RLS).

4) Wrap the last lines of each source query in Table.Buffer to force the engine to load both fully before merging.

I suspect however that the above might not work. 

If not try to build 3 dataflows. First 2 are straight ingests of esch seperate source.

Then reference the 2 ingest data sources in a 3rd dataflow to so the merge. This allows power bi to bring everything in (and stores in a data lake) and then merge cloud side. Whereas your exisiting setup is loading everything into memory in the gateway engine and doing the merge there (would be worth watching memory utilisation during a refresh to see if it's maxing out).

Drop me a direct message if you need help with any of the above.

[SMid]

Using dataflows worked! Thank you so much.

[bcdobbs]

Do they refresh separately when inside the service?

[SMid]

They are separate queries, one is used as the reference source for the combined query, the other append after a few steps.

I also created two simple queries with only two or three steps each, one SQL, one BC and I get the same result. The two queries are able to refresh individually in the service, but as soon as the data is combined the refresh in the service fails.