Forum Discussion
Size Of Publishing Compromise Implementation Scenario
Have you looked at the new Power BI Gateway Enterprise (Preview)? It has DirectQuery mode for SQL Server on-premises, so it should avoid this issue. Filtered SQL Queries would be run as the users interact with the content, instead of sucking up all your data during the Publish. I havent had a chance to try it out fully yet, but here's the doco:
https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise/
Hi mike_honey,
I've installed Enterprise Gateway and I'm in the process of testing it. I'm a little bit concerned about performance and the limitiations of Direct Query (non calculated columns, not full DAX, only one direction for navigation). I'll make the test and write about it.
Any idea if Microsoft plans to sell SSAS Enterprie standalone (without the need of sull SQL Server Enterprise)? This really is making real wolrd solutions very costly. I mean, PowerBI is competing with big players in this field (Tableau, Qlikview,etc) If I need to purchase a licence of SQL Server Enterprise to reach similar functionality, it places the tool under an unfair position.
Thanks for the information.
Regards,
SSAS Tabular is available in SQL Server BI Edition. SSAS Multidimensional is available in SQL Server Standard edition. Both can be queried directly from Power BI.
Hi viera00
From my understanding of your original post, you want to query SQL Server Database Engine directly, not via SSAS? This is supported by the new Power BI Gateway - Enterprise. I'm not aware of any Edition limitations.
Hi mike_honey
Yes, it is true. It is supported by Power BI Gateway - Enterprise. I made some tests, lets me tell you about my experience.
As a conclusion, this option has "huge" limitations for real scenarios.
1- You need to create your model as "Direct Query". This forces many limitations as you cannot use DAX (you cannot create columns or metrics with DAX, neither use the date intelligence functions,etc.) This makes the tool as visualization option for SQL reports.
2- Performance of this option is not really og. every time you interact with a visualizacion, it means a query to the DB, and that has its own roundtrip time, and of course, if the table is big, SQL performance issues. Its true that Power BI "caches" a lot of information (once you try all natural combinations of data, lets say in a barchart with filters) then performance is acceptable, but once you refresh data, cache is erased.
So, using Enterprise Gateway is a way of "making" it work, but is not really a solution. Reading your posts and other collaborators of the community really helped to polish possibilties in a trial and error fashion.
My conclusion is that a hardcore BI solution is needed for this scenario (SSAS with tabular, SSAS with multi dimensional model)
Thanks a lot for your help.
I will post later my experience with future implementations.
Regards
Hi viera00
Thanks for the update and sharing your experience.
Going to SSAS is a big step, as is creating a "BI database". I would first review the indexing on the existing SQL tables. If you can, use columnstore indexes covering the most common columns. If not, make sure that the foreign keys and most commonly filtered columns each have an index. I regularly see this strategy improving query times by a factor of 100.
Good luck!
