Dataset Refresh Failure - No error message (Storage Mode - Mixed)

[Anonymous]

Hello all, hope you're all well.

I'm experiencing a failure when trying to refresh a particular dataset in my organisations Power Bi service, which has found me beginning to tear my hair out !

This particular dataset refreshes completley fine via the PowerBi Desktop, but then fails to do so in the Power Bi Service.

The most frustrating part of this problem is the lack of error messaging/detail, as below screenshot : -

In addition, I'm also failing to see anything glaringly obvious in the on-premises gateway connector logs that would indicate a problem.... Although, I'm far from an expert in this field so could well be missing something.

Additional Details: -

- Dataset Size: 371 MB

- Storage Mode: Mixed (Import & DirectQuery).

- Data Source: Just the one, which is a SQL Database - The differing tables, due to their size, use a variation of Import/DirectQuery.

- On-premises gateway connector: Yes, which uses SQL credentials to authenticate - I can confirm connectivity is successful.

- The same on-premises gateway connector is used for other datasets & data sources in the service and works fine.

- This particular dataset has never successfully refreshed in the web service, and has only recently been published up there.

Apologies if I've missed any other important details out, please just let me know if I have.

I'm really starting to strike out on this one, so any help/guidance would be greatly appreciated.

Thanks in advance !!

[v-deddai1-msft]

Hi @Anonymous ,

What's you have provide us is not error message. Would you please try to use fiddler to capture https request.

 Fiddler is a free tool from Telerik that monitors HTTP traffic.  You can see the back and forth with the Power BI service from the client machine. This may show errors and other related information.

It's better that you can show the trace file with sensitive information removed.

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

Best Regards,

Dedmon Dai

[GilbertQ]

Hi @Anonymous 

No worries, if you have not heard of Tabular Editor then I would say you have done what everyone else has used the product for.

The only option is to enable additional logging on your Gateway Server to see what the logs say?

Troubleshoot the on-premises data gateway | Microsoft Docs

[Anonymous]

Thanks again for your response.

I have just tried another refresh, which has of course failed, and have reviewed the additional logs thereafter.

Nothing strikes me as an immediate cause for concern, but again, I'm no expert. This is something I did pick up on though, which means next to nothing to the likes of me: -

The longer errors are some variation of the below: -

DM.EnterpriseGateway Verbose: 0 : 2021-03-03T22:12:42.9174312Z DM.EnterpriseGateway  b402c487-8b30-47d9-9c92-243e7c586f00  a3fd679b-35a0-a298-1ff5-399448b1baf1  MGGC  3fc53cf2-8a0e-4324-9e27-f8e7193f071b  36f9441d-5c48-4033-846f-21b812cf535f  36f9441d-5c48-4033-846f-21b812cf535f  92D52C70 [DM.GatewayCore] Retrieving database connection establishment to [<pi>System.Data.SqlClient:multisubnetfailover=True;applicationintent=ReadOnly;data source=*servername*;initial catalog=nethelpdesk;application name=PowerBIShared-DirectQuery:[Basic] Encrypted Credential information omitted</pi>]

[GilbertQ]

Hi @Anonymous 

Can you connect from the Gateway server to the SQL Server instance?

[Anonymous]

Hello, yes, I have just confirmed this now.

The server that is hosting the gateway connector happens to have an instance of SQL on, and is able to successfully connect to the remote SQL server instance.

[GilbertQ]

Hi @Anonymous 

Have you used tabular editor at all for your PBIX file?

If you have it could well be that you changed a setting in the PBIX file which is not currently supported in the Power BI Service?

[Anonymous]

Hello,

Thank you very much for your response, so quickly too.

Apologies though, I'm about to really show my lack of PowerBi knowledge & ignorance here... 

I'm not quite sure what the Tabular Editor is or what would come under its scope? Whether that's a clue as to whether I've used it or not...

The only modification/editing that's been done to the data has been internal to PowerBi desktop iteslf. There's no third party modules, connectors, or visuals etc.

Any & all manipulation has been done via the data modeling view using DAX, or using the PowerQuery functionality.

Thanks again, really appreciate your response.