Microsoft Power BI Review
Microsoft has long been providing Business Intelligence (BI) platform technologies such as SQL Server Analysis Service (SSAS), but has just as long been absent from delivering client or presentation layer technologies such as Online Analytical Processing (OLAP). Excel Pivot tables have been around for a while and can facilitate simple multi-dimensional analysis, but Excel's flexibility threatens data integrity, Excel's memory limitations limit data set volumes, and this type of solution falls far short of enterprise data warehouse capabilities.
Power BI is Microsoft’s answer to completing the BI continuum.
The Power BI primary tools include Power Query to extract and transform; Power Pivot to model and analyze; and Power View and Map to visualize data. The Power BI Designer consolidates what were separate tools into an all-in-one application and removes dependencies to Excel or Office. PowerBI.com (sometimes called Power BI Site or Service) is used for sharing datasets, reports and dashboards.
Microsoft Power BI facilitates user generated, agile data analysis with self-service BI analytics managed in the cloud for collaboration and sharing. This is different than a data warehouse, as Power BI permits:
- User generated, agile extract inquiry and analysis (i.e. create calculated columns, change measures and dimensions on the fly and without IT involvement and rigid ETL tools); Data warehouses normally require data to be uploaded (via ETL programs) to the warehouse, then generate cubes, and then analyze, thereby making BI more rigid, IT dependent and difficult to iterate in short periods
- Self-service BI with Natural Language Processing (NLP); and
- A graphical Designer tool with online publishing so data can be shared from a central location
A typical Power BI process is to choose which tables and views you wish to extract from source systems into Power BI (subject to security, in a visual environment and without writing SQL statements or scripting), preview the data, import the data to create a data model which includes tables, table relationships, measures, (calculated) columns and hierarchies, and then cleanse, enrich and format the data into Reports and Dashboards.
So with proper context and positioning complete, here's my opinion of Power BI advantages and disadvantages.
Power BI Advantages
- Power BI puts business intelligence creation into the hands of analysts who can extract source data, create a dataset, transform or manipulate the data, visualize the data and publish the resulting reports and dashboards. For progressive or iterative analysis, analysts can evolve the BI with new measures or dimensions without requiring IT involvement.
- The Question and Answer (Q&A) function may be the top cited benefit and capability in achieving self-service BI.
- The dashboard visualizations are best in class and continually updated from the community. Interactive geo-map visualizations are empowered by Bing Maps.
- The underlying Power BI technology is an in-memory analytics engine and columnar database that supports tabular datastore structures used by Power Pivot. This achieves a balance between performance and ease of use (as compared to three dimensional cubes which require more complex assembly and query languages, such as MDX (multidimensional expressions) for SSAS).
- The DAX (Data Analysis Expressions) scripting language is a relatively simple construct used to create calculated columns and measures. It's similar to Excel, although while Excel is cell based, DAX is column based.
- The pace of innovation is amazing. The product is updated about monthly.
- The free version reduces acquisition barriers.
Power BI Disadvantages
- The integration to Dynamics CRM is evolving but has a few limitations. Power BI can extract CRM data via an OData feed, so getting the CRM data into the data model is easy. However, a security caution is that security is applied at the desktop tool, and thereafter the security granted to the person creating the data model is extended to all consumers of the data in PowerBI.com, unless the creator specifies other security permissions. Also, while Power BI dashboards can be appended to Dynamics CRM, the user cannot create a System dashboard (available to many), only a personal dashboard.
- Power BI reports and dashboards cannot accept or pass user, account or other entity parameters. This makes it impossible to create entity specific dashboards such as a dashboard for an account, opportunity, case, or campaign. Instead, dashboards are limited to aggregate views of entity data.
- There are only a few limited data sources that permit (near) real-time connections to your Power BI reports and dashboards. Also, using these real-time connections limits Power BI access to a single data source, voids the Edit View and eliminates key capabilities such as the Q&A and Quick Insights functions. Real-time connections to data sources other than SSAS also eliminates key behaviors such as DAX formulas.
- Dashboards and reports can only be shared with users who have the same email domains or email domains listed with your Office 365 tenant.
- While a dataset can include multiple data types, Power BI reports and dashboards can only source data from a single dataset. Similarly, Power BI cannot mix imported data with data accessed from real-time connections. It's one or the other.
- Power BI will not accept files larger than 250 MB. Power BI files - .pbix – are actually a type of archived zip files so they do compress the data until it's needed by the xVelocity in-memory database engine, but the maximum accepted file size may still limit Power BI to subsets of enterprise data warehouses.
- There is a 1GB limit per dataset. As a workaround, you can create multiple datasets. There is also a maximum of 100,000 records in PowerBI.com.
- The solution can be deployed on-premise using the Power BI Report Server, however, the cost rises dramatically.
- This Microsoft solution is normally used to extend ― not replace ― other reporting tools. In most cases, it will not replace your enterprise data warehouse. For most companies, it is likely that their enterprise data warehouse tools will continue to be used for high volume data processing reports which do not change much, while Power BI may be used for one time, progressive or more frequently changing analysis on smaller data sets.
Microsoft Power BI is still in early days. While the product roadmap is uncertain what is clear is that the product is incurring rapid advancement and will be an increasing component of Microsoft's BI strategy.
If you are looking to take the next step with analytics solution, check out the Power BI best practices.