I caught an issue with TOP(N) feature. I tested it for both cases:
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?
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.
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...
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.
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.
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.
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
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.
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.