Error refreshing dataset using parameter

Power BI
1

Hi @sam,
My team is working on a model using SQL as a source in Import mode. The Directy Query mode is not viable since the backend is not sized to support it.

The issue developers face is whenever they do a change that affects the fact table, they need to run a full refresh for the update and to test if everything is ok. Time for the full update may take from 20 minutes to hours depending on their house’s connection speed.

I figured out that performance-wise, using Dataflow is a good solution, to minimize these waiting times during the development phase. Data from a dataflow is pulled in the model in less than 2 minutes when updating. Dataflow contains a query to the very same SQL source.
We cannot use the Dataflow as a permanent solution since our P1 node is already at capacity.

So I thought of a smarter approach that involves a Dataflow, a parameter, and a few lines of code in Power Query.

The idea is that developers can work using the dataflow as a source and when all is OK they can publish the model, then in the published dataset they can switch the parameter from the dataflow to the SQL and let the Power BI Service do the heavy load, that is pulling the data from the production source.

Untitled Diagram
Untitled Diagram634×962 47.2 KB

parameter
parameter593×645 10.1 KB

The switch mechanism is a simple if for the query of the final table.
let

  • Source = if SourceSelector = “SQL” then Table1 else Table2*
    in
  • Source*

Unfortunately, the dataset with the switch mechanism used, when updated return an error, apparently related to privacy settings. All privacy settings are consistent, both on PBI Desktop and the service.

error
error961×255 17.5 KB

This does not occur when the if is not used and the source for Table 3 is hardcoded, like below, and this is driving me nuts.

let

  • Source = Table2*
    in
  • Source*

I run numerous tests, but this seems to be the only variable that generates the error.
So privacy settings are all the same (set to None) all over the place, using the switch in Power BI Desktop work correctly (switching pulls from the data from the selected source).
What am I missing?
Googling around I found nothing that can help me to solve this riddle.

Do you have any suggestions on how to fix this or how to make the developers work in a more efficient way?

Your help is always very appreciated

Thanks

Roberto

2

@Roberto I tried the same setup and it works for me I can switch between data sources.

3

Hi @Roberto ,

Have you seen the response from @AntrikshSharma (thank you!)

Did his responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need? Please share your working .PBIX file as this will help our Experts better understand your question and provide more effective solutions.

If it did, please mark the answer as the SOLUTION.

Thank you!

4

Hi @AntrikshSharma thanks for testing. My query is solved.
My understanding from interpreting the error message is that the PrivacySetting to fix is in the dataflow, but I cannot find any privacy setting there.

Am I correct?

I forgot to mention I’m using a gateway to access SQL since it’s on-premises.Does it have privacy setting too? I don’t have access to check myself.

Thanks

Roberto

{
“error”: {
“code”: “DM_GWPipeline_Gateway_MashupDataAccessError”,
“pbi.error”: {
“code”: “DM_GWPipeline_Gateway_MashupDataAccessError”,
“parameters”: {},
“details”: [
{
“code”: “DM_ErrorDetailNameCode_UnderlyingErrorCode”,
“detail”: {
“type”: 1,
“value”: “-2147467259”
}
},
{
“code”: “DM_ErrorDetailNameCode_UnderlyingErrorMessage”,
“detail”: {
“type”: 1,
“value”: “Information is needed in order to combine data”
}
},
{
“code”: “DM_ErrorDetailNameCode_UnderlyingHResult”,
“detail”: {
“type”: 1,
“value”: “-2147467259”
}
},
{
“code”: “Microsoft.Data.Mashup.MashupSecurityException.DataSources”,
“detail”: {
“type”: 1,
“value”: “[{"kind":"SQL","path":"xxxxxxxxx.yyyyy.yyyyy.hhhhhhh.net;kkkkkkkk"},{"kind":"PowerPlatformDataflows","path":"PowerPlatformDataflows"}]”
}
},
{
“code”: “Microsoft.Data.Mashup.MashupSecurityException.Reason”,
“detail”: {
“type”: 1,
“value”: “PrivacySetting”
}
}
],
“exceptionCulprit”: 1
}
}
}

5

Hi @AntrikshSharma
I finally fixed it.
The PrivacySettings discrepancy was in one of the datasources (Manage connections and gateways->Datasources) that is automatically added when defining data sources included in this dataset. These new entries have None as PrivacySettings. Realigning makes the trick.
This is where I found the answer ‎ Solved: Re: Refresh error: Reason: PrivacySetting - "Infor… - Microsoft Power BI Community Mardin’s ‎11-19-2018 10:27 PM
Thanks all

Roberto

2 Likes