Direct Query behaviour with column order change

[IMett]

Hi everybody,

I am analyzing a very weird behaviour in our data model. I tried to simplify the model as far as possible:

Issue:

I have 2 models, which seem absolutely identical. They have the same 3 tables (1 fact table and 2 dimensions), with the same columns, the same relationships, no power query transformations and no specific DAX measures.
All tables are from the same data source and are in direct query storage mode.

However when I try to make a simple visualitzion, they behave totally different. While one model is absolutely fast, the other query takes quite long and eventually throws a 1.000.000 rows exception.

Analysis:
Using Performance Analyser I was able to figure out that the queries, which are sent to the data source (Azure SQL Server DB) are different in the 2 models:

The first model sends just one query to the data source which contains the necessary 2 LEFT JOINS to display the visual. The query is fast and gives an output of ~3.000 rows, everything fine.

The second model sends 5 separate queries to the data source, one of which collects every value of the primary key of DIM_TABLE1. The queries are later evaluated in DAX in order to obtain the sums we need for the visualisation. This is the problem - this dimension table is quite big and actually has more then 1.000.000 distinct values, so the query fails (and is very slow  because of the high I/O apart from that).

Now the question is of course: Why do those identical models behave so differently?
After investigating further, I made a stunning discovery. The 2nd model was built from scratch in Power BI Desktop, while the first was already used for a while by a business user.
Indeed, DIM_TABLE1 had the same attributes, but they were arranged in a slightly different order. I didn't find any other differences. After talking to the developer, we found out that the dimension table was altered in the database between the creation of model 1 and 2, leading to a different column order.

So I suspect following behaviour:
In model 1, the retrieved column order doesn't match the column order in the database's information schema. Because of this inconsistence, the tables are evaluated separately and joined together in DAX, as one would expect for different data sources.

So my questions are:
- Am I right? Did anyone experience a similar behaviour?

- If so, why does Power BI not update the column order automatically whenever the table is refreshed? There is no explicit ordering in any power query step.

- And, most importantly: how can I avoid this behaviour in the future? These tables are in our core data warehouse and can be used in dozens different data models spread over the whole organization. How can we make sure that they don't all suffer bad performance whenever there is a small structural change in the data source?

I was able to solve this specific problem by dropping the dimension table from the original data model and adding it again, adjusting all relationships afterwards. However this is troublesome and error-prone if every report designer has to do that regularly, especially if you don't know which tables might be affected and there are lots of relationships.

Any help is highly appreciated. If it helps, I can provide further information (for example the .bim files for model analysis)

[IMett]

Thanks a lot for the hints, 
Indeed, there was a key difference between the queries which I didn't recognize.

The data source is an SQL Server and on one of the queries the "SQL Server Failover" option was checked. Apprently Power BI treats this as a different data source, although it only appears once in the data source settings.

The query itself doesn't contain any logic (also no native query), so there is nothing to fold.

Query 1:

let
    Source = Sql.Database("xxx-xxxx-xxxx-001.public.xxxxxxx.database.windows.net,3342", "xxxxx"),
    BL_Vertrag_Dim_LPersonenbezug = Source{[Schema="BL_Vertrag",Item="Dim_LPersonenbezug"]}[Data]
in
    BL_Vertrag_Dim_LPersonenbezug

Query 2:

let
    Source = Sql.Database("xxx-xxxx-xxxx-001.public.xxxxxxx.database.windows.net,3342", "xxxxx", [MultiSubnetFailover=true]),
    BL_Vertrag_Dim_LPersonenbezug = Source{[Schema="BL_Vertrag",Item="Dim_LPersonenbezug"]}[Data]
in
    BL_Vertrag_Dim_LPersonenbezug

Pretty tricky to be honest!
It would be great if those data sources could also be displayed separately  in the data source settings.

[v-jingzhang]

Hi @IMett 

Thank you for your feedback. I didn't know this option will have such an influence on the performance. I found an old thread which has a good explanation about this feature if someone hopes to learn more about it: https://community.powerbi.com/t5/Service/SQL-Query-new-advanced-setting-quot-enable-sql-server-failo... 

Best Regards,
Jing

[KNP]

Are you certain query folding is happening in both models?

Are either of the models using native queries as opposed to connecting to the tables/views?

I'd be very surprised if column order had any effect on this.

[v-jingzhang · Accepted solution]

Hi @IMett 

Have you checked whether the M codes of all queries in both models are identical? You can click Advanced Editor to see all M codes of a query and compare it with the query in the other model. At present I couldn't think of other possible causes. 

Best Regards,
Community Support Team _ Jing