PowerBI Service - Scheduled Refresh

[Anonymous]

Hi 

I've been trying to schedule refresh a report which works perfectly on PowerBI desktop (Ignore privacy level setting).  PowerBI service  has been throwing the following error when I refresh the dataset on the service web portal

[Unable to combine data] Section1/WithTimeStamps/Source references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

The query in "WithTimeStamps" set is the following:

let
    final = Text.Combine(NotScannedAccounts[AccountId], "','"),
    Source = DocumentDB.Contents(@CosmosURL, "databasename", "configdata", [Query = "SELECT VALUE root from (SELECT c.AccountId,  MAX(c.Time) as maxval FROM c where c.Type = 'prereqdata' and c.AccountId in ('"&final&"') group by c.AccountId) as root "])
in
    Source

Where the final variable becomes a comma separated list of account ids that I need to pass to cosmos to get the data. The expected data is a table of record which contains an account id and the latest timestamp for a particular account.

Now, after spending hours on the forums everyone seems to be pointing towards the same excelguru website but that is not useful in this scenario (or atleast I am not able to properly understand how ot apply that in this situation.) Requesting the community for any help.

[v-kkf-msft]

Hi @Anonymous ,

Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[Anonymous]

Hey so the problem didn't get solved based on the documents that were shared here but I actually reimplemented the entire report and ended up running in this permission issue fiasco at the very end but this helped me realise what the actual problem was. I will try my best to explain it here so here goes.

So let's say we have a table (T1) with certain details like account id, license id, license name etc. Now the issue occurs since I am calling cosmos for each row. This for each on each row calls the following query:  "select * from c where c.Type = 'doctype' and c.AccountId = '"&[AccountId]&"' "

Now this works flawlessly for my case when no privacy level check is done but causes problems when I try to refresh the report on power bi service

[v-kkf-msft]

Hi @Anonymous ,

I tested and found that there are two ways to refresh successfully.

1. Manually enter the NotScannedAccounts[AccountId] column you quoted in the query, as shown below. But this will make the AccountID value fixed.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AccountId = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AccountId", type text}})
in
    #"Changed Type"

let
    final = Text.Combine(EnterTable[AccountId], "','"),
    Source = DocumentDB.Contents("https://xxxxxx", "ToDoList", null, [Query = "SELECT VALUE root from (SELECT c.id, MAX(c._ts) as maxval FROM Items c where c.id in ('"&final&"') group by c.id ) as root "]),
    #"Expanded Document" = Table.ExpandRecordColumn(Source, "Document", {"id", "maxval"}, {"Document.id", "Document.maxval"})
in
    #"Expanded Document"

2. Import all the data into PowerBI, and then use parameters or manual or DAX to filter the data.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexDantin]

I have several datasets that were working fine, now suddenly all my credentials were invalid and getting several different error messages like the above when trying to refresh using the Personal Gateway (which new version has come out and I have uninstalled and re-installed) -

Underlying error code-2147467259
Underlying error messageInformation is needed in order to combine data
DM_ErrorDetailNameCode_UnderlyingHResult-2147467259
Microsoft.Data.Mashup.MashupSecurityException.DataSources[{"kind":"Odbc","path":"dsn=[connect_string]"},{"kind":"File","path":"O:\\[file_info].xlsx"}]
Microsoft.Data.Mashup.MashupSecurityException.ReasonPrivacySetting
Cluster URIWABI-US-NORTH-CENTRAL-H-PRIMARY-redirect.analysis.windows.net
Activity ID1cff351d-839d-462f-ba04-694ea2365b7a
Request ID7014232d-23bc-7cc8-87a7-bde4968496e6
Time2021-06-10 13:26:38

a prior message spoke about "need to rebuild the data source"

none of these were an issue previously.

[AlexDantin]

none of the proscribed solutions helped. I had to adjust the connection string to our PostGRES AWS db in order to enable the refresh to occur.

[AlexDantin]

I have confirmed that all the privacy settings have been set to "ignore"

[arvindsingh802]

@AlexDantin  - Just wanted to make sure you also keep same Privacy level (Ignore) in your Gateway data source.

[v-kkf-msft]

Hi @Anonymous ,

See if these documentations are helpful.

Behind the Scenes of The Data Privacy Firewall 

Connect to Azure Cosmos DB using BI analytics tools with the ODBC driver 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I'll look into these articles. Initil glance suggests that the second document might not be relevant but tysm. I'll get back with an update.

[arvindsingh802]

If you are using Gateway then make sure the Privacy level is same in Dataset and Gateway Datasource.

If this post helps, then please consider Accept it as the solution and give it a thumbs up

[Anonymous]

I'll check that and get back to you. 

[Anonymous]

Replying to keep this post alive. Still looking for solutions.