Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlexS__0001
Regular Visitor

Power BI Top N does not push down SELECT TOP(N)... ORDER BY col to SQL Server level

Hi guys

 

I caught an issue with TOP(N) feature. I tested it for both cases:

  1. when datasource is SQL Server. it does not push SELECT TOP(N) ... FROM... ORDER BY col to SQL Server level
  2. when data source is Analysis Services it works perfect and pushes down DAX TOPN https://msdn.microsoft.com/en-us/library/gg492198.aspx

 

The problem in the 1 case: power BI pulls big dataset, then orders it by col, then get TOP(N) rows on his own side instead of delegate the work to SQL Server side. Are there any way to workaround?

7 REPLIES 7
v-jiascu-msft
Employee
Employee

Hi @AlexS__0001,

 

1. There are TOPN in DAX and TOP n in SQL. Which did you use? 

2. Sql Server connector uses SQL statements while SSAS connector uses MDX or DAX query. They are two different things.

3. You can see from my test that the SQL statement is sent to the SQL Server side.Power BI Top N does not push down SELECT TOP(N)... ORDER BY col to SQL Server level2.jpgPower BI Top N does not push down SELECT TOP(N)... ORDER BY col to SQL Server level.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

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

That is not what i asked about.

 

To reproduce the situation.

 

- connect to any SQL database from Power BI in direct query mode

- create table visual with visual level filtration TOP(N)

- catch queries, that Power BI generates automatically

 

You will see the visual TOPN filter does not force Power BI to generate SQL queries like that: select top(n) * from tbl order by col.

Instead of that it generates query like that select * from tbl, then pull entire result set on its own side, then order by it LOL, then get top(N) rows. So in the case of SQL Server, all mentioned work is done on Power BI side. It is bad approach if i working with big tables. I hope uys you can fix that easily.

 

But in the case of SSAS it works as expected, it pushes down topn dax expression to SSAS and does all dirty work at SSAS side.

 

I use both approaches, in the case of SQL Server i have ROLAP cube, in the case of SSAS i use MOLAP cube...

Hi @AlexS__0001,

 

1. DAX is supported by SSAS natively. Sending the DAX formula to the SSAS Server would be a great idea as you can see from the backend. SQL Server doesn't support DAX. If we translate a DAX formula, which we can't expect what it would be, to a SQL statement, I don't think this would be efficient.

Reference: https://msdn.microsoft.com/en-us/library/gg413422.aspx.

 

2. The SSAS model is defined all in the Server side. We can't do any customization in the Power BI as we can see from the greyed interface. So it would be a good idea to send the work to the server that already does the work. But it's different for SQL Server. If we use a measure in the "By value" field, and if Power BI translate it to SQL statements then send it to the Server, Power BI has to evaluate the measures (DAX) again when it receive the return values because of the other measures. This is obvious inefficient.Power BI Top N does not push down SELECT TOP(N)... ORDER BY col to SQL Server level3.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

 

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

topn_desc.png

Anonymous
Not applicable

Hi @AlexS__0001,

 

I have found that power bi initially sends out query only calulating measure. After getting measure values it takes top N from it and again sends back a query with where clause " columnname in (value 1,value2, . . . . ,valune n) . so it would atlast fetch only top n rows.

 

Even I haven't found a way to make it work like you asked i.e to have "select top(n)".

 

If you have found the answer please do post it here.

Thanks !

I am also pretty interested in this topic. Any update about this?

Could you please forget about SSAS? Power BI + SSAS work well together. My question is not about SSAS or Power BI + SSAS. I mentioned it as an example where things are working well. Dont speak about that please.

 

 

Lets speak about SQL Server + Power BI and about how Power BI's feature TopN filtration is implemented in this case topn.png

 

It does not generate queries like that (as expected😞

 

select top(100) * from tbl order by Site_Url

 

It generetes like that

 

select * from tbl

 

And then pulls ALL resultset, then it does order by on Power BI side and get 100 rows.

 

Please dont speak me about it cannot be implemented. It must be implemented, because if you try to use Power BI on big tables in real life without the feature, Power BI is meaningless. All concurent products can do that in eficcient way.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.