Power BI for Office 365 - A compelling data analytics platform for personal and team use
We are a Microsoft partner, with my line of business focusing on creating data-driven solutions for our customers, ranging from IT-sponsored enterprise data warehouse initiatives to small team and personal business analytics solutions. My consulting team uses Power BI for primarily two purposes: 1) to create pilot/proof-of-concept solutions for our customers to help launch them on their BI journey; 2) to perform data analytics on larger enterprise data-driven initiatives. The agility of the Excel-based solution, combined with the power of the in-memory PowerPivot data engine, more than meets our need for the these 2 purposes. Our customers appreciate it because it allows them to remain in their very familiar Excel environment.
We are working with mostly finance/accounting departments, and in some cases, business analysts (or "power users") within the business community (sales, marketing, etc.). We are solving finance challenges like Accounts Receivable collections, along with operational challenges such as monitoring year-over-year sales metrics and comparing them to budgets that come from two data locations. In addition, we're solving painful "process issues" like trying to import multiple files into a single data set. We are literally reducing process time from days to minutes!
- Provides flexible and powerful analytics on large datasets. PowerPivot, the in-memory database engine, can handle 100's of millions of rows of data, giving 1-2 second response time for aggregate data analysis.
- Provides an adaptable environment to perform analytics. When combining data together in PowerPivot, you have the ability to create Excel tables, combine them with database queries, and then join that with data coming from a web-service provider.
- Powerful web-based deployment capabilities. SharePoint technologies have been a part of the Microsoft BI solution stack for some time. Office 365 provides a powerful way to deploy the Power BI analytics to a wider audience in a secure way, allowing for flexible access from most any device, whether phone, tablet, or computer.
- Power Query, one of the components of Power BI, is one of my favorite parts of the solution. Basically, you have at your disposal an end-user "ETL" (Extract, Transform, Load) tool, all within Excel. We've reduced multi-step, manual processes to single-click, automatic application of business rules to collect and transform data into a usable format. No need for VLOOKUP any longer with this capability.
Cons
- Licensing: Currently, Microsoft has a fixed pricing model for Office 365 users, regardless of role/function of the user. Most organizations have a small number of "power users" that create usable content and many more "consumers" that simply view/run reports created by power users. Microsoft does not differentiate between these users, and thus the pricing limits organizations from large deployments of the software.
- Version incompatibility: Excel 2010 and 2013 workbooks are compatible with each other. However, workbooks created in 2010 that include PowerPivot databases must be upgraded to 2013 format to run in 2013. Subsequently, you cannot open these upgraded PowerPivot workbooks in 2010. This requires ALL users to be on the same version.
- Visualization: Excel charting with PowerPivot workbooks is adequate for many users. Power View also contains a number of GREAT visualizations, including animated bubble charts and a very flexible dashboard/report design canvas. However, compared to some of the other self-service BI solutions, it is still limited in its visualization capabilities.