Forum Discussion

viera00's avatar
viera00
Helper II
10 years ago

Size Of Publishing Compromise Implementation Scenario

Hi Everyone,   I'm having a serious problem with a real implementation scenario.     I've a PowerBI Pro licence.   The architecture is the following:   1- SQL server on premise. 2- Windows ...
Need Help
viera00's avatar
viera00
Helper II
10 years ago

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,

 

 

mike_honey's avatar
mike_honey
Memorable Member
10 years ago

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. 

  • viera00's avatar
    viera00
    Helper II
    10 years ago

    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

    • mike_honey's avatar
      mike_honey
      Memorable Member
      10 years ago

      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!