If I’d have been writing this 6 months ago, I would have said Power BI is for self-service BI. End of story. Well, things aren’t that simple anymore with Power BI V2. The way I see it, there are two main ways to utilize Power BI:
This method utilizes only the reporting & dashboarding capabilities of Power BI. The source data stays where it’s located and is *not* replicated within the Power BI data model (aka Power Pivot). This approach requires a “direct connection” to the underlying data source and data refresh is handled at the source rather than in Power BI.
As of now (early August 2015), there are four data sources which support direct connection to Power BI (thus allowing the ‘Reporting Only’ option without requiring an intermediary data model):
- Analysis Services Tabular Model
- Azure SQL Database
- Azure SQL Data Warehouse
- Spark on HDInsight
The ‘Reporting Only’ method is appropriate for:
- Reporting on higher data volumes (because the size limit for a Power BI model is currently 250MB compressed)
- Reporting when row-level security is a priority (for example: Power BI can utilize the security roles defined in an SSAS Tabular Model to implement row-level security by using Effective User Name)
- Reporting on data sources which already exist and there’s no requirement to join to other independent data sources
Query, Model, and Report
With this approach, the data is replicated and stored in a data model. This data model can be created in either Excel (using Power Pivot) or via the Power BI Designer. The replicated data is typically refreshed on a regular schedule. This is the way we’ve thought of the Excel add-ins of Power Query, Power Pivot, and Power View for some time now.
The Query>Model>Report approach is most useful for:
- One-time or infrequent analyses (ex: a one-time analysis of whether to open a new store)
- Mashing up data from multiple sources (ex: financial data from an internal data warehouse plus external industry metrics)
- Small, self-service projects (i.e., those which cannot be justified to add to a larger, centralized solution)
- Use of the Power BI SaaS connectors (because it stores and refreshes the data in a model)
- Use of the Power BI APIs (because it stores and refreshes the data in a model)
Bimodal BI: Self-Service and Corporate BI
I’m really happy to see V2 maturing and evolving. We already know that it’s a powerful self-service BI tool. At this point it can be used for *certain* projects that might be considered corporate BI:
- The ‘Reporting Only’ approach with direct connectivity to a centralized data source
- The Power BI SaaS connectors, particularly if used in conjunction with groups and organizational content packs
- The Power BI APIs, particularly if used in conjunction with groups and organizational content packs
Just how am I differentiating self-service BI from corporate BI you ask? Great question. I see self-service BI as being driven by business users, with less governance and a focus on freedom of exploration. Self-service can mean a lot of things (like parameterized reports delivered to users from IT), but in the context of Power BI, we tend to think of the functional user handling the cleansing, shaping, modeling, and reporting -- though in practice that’s not always true. The definition of self-service isn’t the same in every company, or even across departments or business units.
Conversely, traditional corporate BI places a lot of emphasis on things like standardization, scalability, efficiency, reusability, and accuracy of data. Both of these worlds usually do exist, whether it’s formally sanctioned and planned or not.
Over time we’re going to continue seeing more integration points (particularly with Cortana Analytics Suite), more APIs to bring data in and out of Power BI, more SaaS connectors, and more direct connection abilities. I'm really interested in finding the best balance between corporate and self-service BI, so it’ll be interesting to watch as Power BI evolves with its capabilities to support both.