Situation: Large retail company with many subcompanies. We're building a BI solution covering all main processes (eg. finance, production, procurement, inventory etc.) Potential users > 1000. We are building a datawarehouse as the base. Question is were do we go.
I'll try to list the main question at this stage.
1. building data model. Tabular cube vs Power Pivot vs Power BI desktop
- Should we stick to cubes. If we do that all users have to have pro licence to use the reports, right?
- Should we mix?
- If working with Power Pivot we could export to both cube and PBI desktop.
- If using PBI desktop we're stuck in Power BI.
- We need row level security so I guess that points to cubes for at least those cases.
2. Visualization: Creating the reports in Excel, Power BI desktop or Powerbi.com
- Creating them on Powerbi.com seems bad in that we can't for example change data source or keep track of changes
- Creating them using PBI desktop seems better in that we can version control the pbix file.
- PBI desktop reports can use a cube as a data source or the internal data model but not a Power Pivot model, right?
The bigger question is, creating a enterprise solution, what is the proposed way of working.
So, not sure that I can answer any/all of your questions but I'll give it a shot at some of them. Enterprise features for Power BI 2.0 are really just starting to appear. Power BI started out as a self-service BI tool and we are just starting to see the more Enterprise features come about.
For an enterprise, probably a good idea to look at using SQL cubes. This provides a solid backend for security and opens up the ability to use SSRS, Power BI or anything else that supports cubes. For Power BI, you would want to use the Enterprise Gateway to ingest that data and provide the autorefresh capabilities.
You'll also want to strongly consider SQL Server 2016 as it adds a lot of enterprise features to Tabular data models and supports some crossover between SSRS and Power BI. For some good articles, take a look at:
And yes, my current understanding is that you cannot ingest a Power Pivot model into Power BI currently in terms of the Desktop. But, you can load an Excel file with a Power Pivot model into the Service and it functions.
Proud to be a Datanaut!
Just on the point of ingesting Power Pivot models to PBI Desktop...
You can migrate Excel (including Power Pivot models, Power View reports, etc) to Power BI Desktop, through the Import >Excel workbook contents feature. - see here --> https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-import-excel-workbooks/
Hope that helps!
Yes, we're going for 2016 so yes, we also have the new SSRS capabillities to weigh in.
Everything points to cubes except that then all users would have to be pro users. I'm thinking that some of the users will be both infrequent and only use a few reports. They might sufice with the free subscription given that the data source is not an on prem cube.
We're trying to find a solution were we can say for solution X we use a cube for solution Y we use PBI desktop. And if solution Y scales up we upgrade that to a cube.
One solution would be to build every model in Power Pivot because that model is migrateable to both SSAS and PBI desktop.
I just feel that there are a lot of ifs and buts here.
Also keep in mind the use cases for end user. I'm not sure about licensing for SQL 2016 yet, but I imagine it'll be similar to SQL 2014. Enterprise Edition is just core licensing - unlimited users. BI Edition is cores + CAL - pay per head. Anything below BI edition isn't worth much if you're looking to leverage SSAS and the upgrade path from Power Pivot.
So, in SQL 2016, you'll have SSRS mobile, which is just rebranded Datazen, available through the Power BI mobile app, and in a web viewer with no need for PBI Pro licenses.
You'll also have paginated reports - SSRS Classic, as it were.
What do your users need to do with BI? If your rare users are primarily consumers, then host those reports through SSRS, exposed however you want, and then don't worry about Pro PBI licenses. Power BI is really more for the use case of interactive exploration and personalization.
Further down the line, there will be the ability to host PBI data models in SQL 2016 SSRS. I haven't heard confirmation on dates, but it sounds like that would be a service pack update.