Forum Discussion
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 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,
12 Replies
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.
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,
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.
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
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.
