Dual Storage Mode Refresh In Service

[IMett]

Hi everyone,

I have a small dataset with 2 data soucres:

- a CSV file located in the file share 

- a MSSQL table located in the Azure cloud 

Now I try to update the dataset using scheduled refresh (or manual refresh) in Power BI Service:

In all cases, the CSV file has to be registrered in the enterprise gateway, while the MSSQL table should be updated without using a gateway.

Depending on the storage mode, I get different results:

- CSV file in import mode / database table in import mode --> works

- CSV file in import mode / database table in Direct query mode --> works

- CSV file in import mode / database table in Dual mode --> error

COM error: Microsoft.Sql.Analysis.ASOnPremisesDataAccess, Could not extract mashup from connection string. A connection could not be made to the data source with the Name of '1f595dfe-4aa5-4c7a-9d4f-b16d18178b50

Does anyone have an idea, what might be the problem?

[Anonymous]

Hi @IMett,

Do any advanced operations apply to composite mode data sources? (e.g. merge, combine, reference, invoke custom functions, join table fields between different connection mode data tables...)

As the query editor message notices, you can add M query functions into the direct query mode data source but it not works properties when you try to mix or calculate between different modes query tables.

Use composite models in Power BI Desktop#limitations-and-considerations 

In addition, they may also be blocked by power bi service security reasons.

Power BI Security#data-storage-security 
Regards,

Xiaoxin Sheng

[IMett]

Hi @Anonymous , thx for your reply.

Actually in the described report, there are no calculations at all. I simply connect to 3 different tables, one in Import Mode and the other two in Direct query mode, establish a relationship between them and then change the central one to Dual.

@lbendlin is certainly right, that in this particular case there is no need to use the dual mode, however in the actual business report, where we first discovered this problem, Power BI Desktop shows a warning, that we should switch some tables to Dual Mode because otherwise, weak relationships are going to occur.
I just tried to simplify the problem as much as possible.

Regarding your 2nd link, I do not really understand how to proceed here, to be honest. How can I determine whether there is  a service security problem?

Dual Mode

[Anonymous]

Hi @IMett,

In fact, service security also means the permissions these functions/formulas execute on the server who handle/host these power bi contents and records.

When you work on a local device, obviously you have enough permission to execute these functions with high permissions. (it includes the scripts, formulas, or some additional requests e.g. extract device information, traverse file/directory contents...) 

After you publish to power bi service, you only have general 'user' permissions for using power bi service features and operate on the published contents.

For specific actions(high-level requests or sensitive operations) that require advanced permissions(e.g. access to folder/files, export data to a local path, data privacy related settings...) may be blocked by power bi service policies and permissions.

Regards,

Xiaoxin Sheng

[IMett]

Just noticed this announcement on the Power BI Support Website:

Power BI customers using the September(SU09) Gateway or Power BI desktop with an on-premises SQL data source that has a self-signed certificate and "Force Encryption" set to Yes may experience issues with reports using Import or Direct Query modes and see the message "A connection was successfully established with the server, but then an error occurred during the pre-login handshake". As a workaround, customers can install a previous version of Gateway and Desktop versions as needed. Engineers are working on a fix which will be included in the October versions of Power BI Desktop and Gateway due to be released by 10/07/2020.

Maybe this is a reason for our issue. I will try again after a gateway update.

[lbendlin]

Are you trying to use aggregations with your MSSQL data source?

Also, check your gateway settings.

Does your gateway have the first checkbox set?

[IMett]

Hi Ibendlin,

No, I am not using aggregations (however this might be a relevant use case for the future).

Yes, the first checkbox is set - however the other 2 are not.

[lbendlin]

I think you have more than two data sources.  (MS SQL in import  plus MS SQL in direct query = two separate sources). You may want to decompose the issue and start over, adding sources one by one.

[IMett]

Unfortunately not.
I created 3 separate datasets. The error occurs only if I change the storage mode of the MS SQL data source from Direct Query to Dual.

[lbendlin]

Well that's the thing. You can't change from Direct Query to Dual. You can only change from Direct Query to Import, which then shows as Dual - but only if you still have other Direct Query sources.