On Premise Data Gateway Refresh Success - but Some Tables Return Empty

[JoCurry_Aus]

I have been manually refreshing pbix datasets and publishing to the Power Bi Service and am now ready to transition to using the on-premise data gateway and scheduling daily refreshes. The data gateway is working - my data contains a combination of files and an ODBC connection. There is a single ODBC database with connections to multiple tables within that database.

The main table in my dataset - the transactional table, is updating perfectly using pbix or service. The query is a entirely made up of a SQL statement - there are no further instructions using Power BI functionality. Anything added to this table is outisde of the query as a measure etc.

The 2 tables I am having trouble with are far smaller. One is Customers, the other Products. They both are under 10k rows (the transactional table is at about 7 million rows) and they were added to the dataset using the Power BI wizard - still ODBC connections to the same database as the transactional table. Both Customers and Products tables are the product of multiple merged tables. The merged tables are present in the pbix dataset and are ticked to be included in report refresh, but load is not enabled. I also have used Power BI to rename columns, delete columns etc, unlike the transactional table where everthing happens in the SQL statement. I do want to restate that the transactional table has no merged tables.

When I manually refresh the tables using the pbix file and publish them to Power BI service, everything works perfectly. When I use the Refresh Now option in Power BI service I do not get a gateway error but the Customers and Products tables are empty. Same happens if I schedule the refresh using Power BI Service.

I have checked that all queries in the dataset require orgaisational credentials and the settings are the same in the gateway on the service. I've extracted the pbix file of the dataset after the "failed" refresh and the organisational credential settings are still on the queries, they haven't reset (I've read that's been an issue for some others).

Another potential reason I have read is boolean components in filters which is something I need to check. 

In the mean time, has anyone else experienced this and has any tips before I call Microsoft? 

[GilbertQ]

Hi there

I would first check and validate that the Boolean filters are not affecting anything when the dataset is getting refreshed?

In my experience when refreshing is showing different results it is always something that has been filtered either in the Power Query Editor or filters on the report. Make sure you click on the "Reset to Default" button in the report.

[JoCurry_Aus]

Thanks - checking the filters now. If it works I'll mark as resolved

[GilbertQ]

No worries, hopefully that solves it for you!

[JoCurry_Aus]

no luck unfortunately - boolean calcs did exist while the dataset was being built but the have all been removed. I'll leave this open in case anyone else has other ideas and I'll raise a ticket with Microsoft.

[JoCurry_Aus · Accepted solution]

OK - for any others that have had similar issues - the problem was in the data warehouse and was due to the unicode settings. Once a new view was created in the data warehouse with unicode disabled  the data passed correctly without issue.