Size Of Publishing Compromise Implementation Scenario

[viera00]

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 machine with PowerBI Gateway.

3- PowerBI Desktop file connects to SQL Server, DataModel and Reports Created. I've 7 beautiful reports working fine.

4- Publish from PowerBI Desktop to PowerBI.com and create a dashboard.

PROBLEM:

I've an SQL Server database "on premise". This database is really "serious" in terms of size. It has a table I need to analize wich has 13MM rows. Using the Power BI Desktop application I was able to import the data and create the reports over that data. The problem now is that When I clicked "Publish" from the PowerBI Desktop it never ends. I receive no error. I suspect that the "size" of the data might be a problem. The PowerBi Desktop file has 580MB.

My question is if this is really gonna work ? I mean, If I cannot publish this amount of data (that in the future will grow) the application will not serve the purpose. Is there a way to reduce the amount of "data" in the powerbi file, and then "referesh" the datasource from the powerbi.com service, and that the gateway connects to the SQL server and upload the rest of data ?

If the scenario is not achievable, probably my custome will not implement powerbi and will select another tool.

Thanks for the help.

Regards,

[AlexBarbeau]

Hi Viera,

How many columns do you have in the 13million row table you're pulling into Power BI Desktop and how many total tables are in your model?

Is this in a dimensional model, otherwise known as an EDW, a star schema, or a snowflake schema, or was the table made by joining many other tables onto the 13 million row table? A very wide table can take up a lot of space in memory depending on what sort of data is being compressed, particularly if there are numerous columns with unique values.

Or, are you just pulling in the SQL Server tables directly, as in are these operational tables?

I'm asking this as the nature of the data may indicate the model could take up less size if shaped in a different way.

Best of luck either way,

Alex

[viera00]

HI @AlexBarbeau,

Q: How many columns do you have in the 13million row table you're pulling into Power BI Desktop and how many total tables are in your model?

A: My table has almost 50 columns, total tables only 5.

Q: Is this in a dimensional model, otherwise known as an EDW, a star schema, or a snowflake schema, or was the table made by joining many other tables onto the 13 million row table?

A: Is a plain table from a transactional system. No dimensional model.

Q: A very wide table can take up a lot of space in memory depending on what sort of data is being compressed, particularly if there are numerous columns with unique values.

A: Yes, its true, only the table with a couple of relationships to other small tables is consuming huge space.

Q: Or, are you just pulling in the SQL Server tables directly, as in are these operational tables?

A: Only pulling 1 table, that has lots of columns.

Q:I'm asking this as the nature of the data may indicate the model could take up less size if shaped in a different way.

A: Sure, I understand this is not particular optimized. But in the future this table will grow very much. I'm thinking in a start model, grouping values by a couple of dimensions. But even with this grouping, I feel the facts table will still be huge.

As a conclusion, I think i will move to a SSAS tabular, as working only with the file is not really an option at this point.

Regards.

[mike_honey]

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/

[viera00]

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]

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]

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]

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!

[greggyb]

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.

[viera00]

Hi @greggyb,

This is the scenario I'm moving to now. Creating a BI database from the original SQL database.

I'll keep you posted on the progress.

Regards,

[Seth_C_Bauer]

Have you explored using SQL Server Analysis Services Tabular models? What edition of SQL are you running? 

[viera00]

I do not know exaclty the SQL Server Version.

But the customer wants to avoid any new configuration or licence.

So lets assume that SSAS is not avaiable. 

Regards,

[Seth_C_Bauer]

@viera00 The only option you have then is to reduce the size of the model by removing elements you may not need. I am not aware of any plans to increase the size of PBI desktop files accepted by the Service. This issue has been brought up many times, and there is a request here which won't help your immediate need.

If you change your PBIX file extension to .zip you can see the size of all elements of your model. The only way around this size limitation at this point is to either skip the model and direct query the database, or use a tabular or multidimensional model. Both of which require BI or Enterprise level SQL edition.

Creating a Tabular model is exactly what you are doing in the Desktop file, only it would be re-usable with other reports - so there is really no downside there. Unless they only have "Standard" edition SQL, then you don't really have any options that I am aware of.