Tabular Model / XMLA returning no / empty results for 1 out of my 3 fact tables from PowerBI Premium

[Anonymous]

When viewing my PowerBI Premium dataset, one of my fact tables shows no results. The other tables work properly online (in the PowerBI service).

All measures of all fact tables work correctly in my local Visual Studio's integrated Analysis Services server (having Excel connected to it after clicking "Analyze in Excel" in Visual Studio).

Context:

• I am creating a PowerBI Premium dataset using Tabular Model. The Tabular Model is created using Visual Studio using the Analysis Services Projects extension. 
• The dataset is analysed using Excel.
• The data source is a set of CSV files located on a storage account.
• My users and I have PowerBI Premium Per-User licenses.

I created a tabular model with around 10 dimension tables and 3 fact tables. On these fact tables I created measures.
When the model is deployed to PowerBI Premium Per-User (via the XMLA endpoint) - only 2 out of 3 fact tables return values for the measures. When connected using Excel to the PowerBI Premium dataset, the 3rd fact table shows no result for its measures. However, no error is shown during the model deployment neither during the processing (ingestion of the data).

I tried inspecting the dataset in the PowerBI Premium service in 3 ways:

• using Excel - it shows no row at all in the pivot table
• using the PowerBI Portal to make a chart of the offending table - it shows no data points
• using SQL Server Management Studio

Using SQL Server Management Studio I can connect to the dataset in PowerBI Premium and validate that for the bugging fact table:

• the partitions did ingest the correct amount of rows
• a simple DAX query like "EVALUATE opbrengst" returns a bunch of empty rows, and their date column shows 31-12-1899 (likely a null representation?)

• the entire model size is around 275MB - nowhere near the 100GB limit of PowerBI Premium Per-User

Forcing the table or the individual partitions to process does not change something

What can I do to debug further?

[GilbertQ]

Hi @Anonymous 

What I would suggest doing is to install Tabular Editor and view the meta data that way and see if there are any pointers there to see why it is not working?

Tabular Editor 3

I typically in the past have used Power BI Desktop and then used ALM Toolset and Tabular Editor to deploy and make changes. I know it is not visual studio but I would be hesitant to use visual studio because it does not have all the Power BI related updates.

[Anonymous · Accepted solution]

I found the issue - the culprit was an underscore instead of a hyphen in a locale in the PowerQuery for the partition definition that somebody inserted manually in the .bim file (source of the Tabular Model).

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"datum_key", type date}, {"tijd_key", Int64.Type}, {"apparaat_key", type text}, {"garage_key", type text}, {"bronsysteem_key", type text}, {"betaalwijze_key", type text}, {"product_key", type text}, {"betaling_atl", Int64.Type}, {"betaalregel_omzet_bdg", Currency.Type}, {"betaalregel_btw_bdg", Currency.Type}}, "en_US")

Locally this was not causing issues but in the PowerBI service this caused the following symptoms:

• processing the table took over 30 minutes (sometimes almost an hour) - likely stopping due to a timeout
• no error messages were returned
• the correct amount of rows was loaded but they all appeared empty
• measures on the table did not return data

Fixing the locale fixed the issues for me. While I'm happy that I found the issue, I'm not amused by the lack of developer support in PowerBI and Visual Studio...

I would expect these things at least:

• an error message to be returned when triggering the Full processing command.
• similar behavior between PowerBI and the integrated Analysis Services server from Visual Studio