By Martin Heller
Contributor, InfoWorld |
Microsoft formerly offered a rather confusing array of centralized business intelligence, reporting, and analytics products aimed primarily at IT developers, with users often assumed to be working with Excel against on-premise SQL Server Analysis Server and SharePoint data sources. The company added cloud-based collaboration in Power BI for Office 365 in 2014, at a rich subscription price. This complemented the Power Query, Power Pivot, Power View, and Power Map self-service BI features added to Excel 2013.
With the introduction of the new stand-alone Power BI, Microsoft hopes to compete with and perhaps leapfrog self-service BI products such as Tableau. The new Power BI includes a Web interface to a service hosted on Azure and a Power BI Desktop application for the Windows desktop, and it’s much more modestly priced: A standard account and the Power BI Desktop are both free, while a Pro account is $9.99 per user per month.
Both the website and the Desktop application are updated on a regular basis. The Power BI Desktop is updated monthly; it’s hard to tell when the site is updated.
You can import data into Excel, Power BI Desktop, and the Azure site. The data can come from Access, Active Directory, Azure SQL, DB2, Excel, Exchange, Facebook, GitHub, Google Analytics, HDFS, HDInsight, Marketo, Microsoft Dynamics CRM, Microsoft Dynamics Marketing, MySQL, OData, Oracle, Postgres, Power BI Designer files, Salesforce, SAP BusinessObjects BI Universe, SendGrid, SharePoint, SQL Server, SQL Server Analysis Services, Sybase, Teradata, Web tables, Visual Studio Online, and Zendesk. (See Figure 1.)
Figure 1: Power BI can connect to online, desktop (uploaded from your computer or residing on your OneDrive), and on-premises data. Connecting to an on-premises SQL Server Analysis Server requires installing a connector on your server. The figure shows the available online services that can provide data.
For some data sources, Power BI has predefined charts, dashboards, and reports. For example, the default Visual Studio Online dashboard and report provide at-a-glance views of Git, pull request, and version control activity across the projects you configure for your account. For other sources, Power BI expects to see certain markers for its data. For instance, it supports Excel 2007 and later named Worksheet tables, Excel Data Model tables, and Power View sheets. If you have only raw data in your Excel worksheet, you need to go back to it and create one or more named tables; it also helps if you make sure your data types are correct.
If you upload a worksheet from your computer, Power BI will use it as a static snapshot. If the worksheet resides on your OneDrive, Power BI can refresh its data on demand.
If you want to import text or CSV data files, you need to start by importing them into Excel or Power BI Desktop. This is a less satisfying user data import experience than you’ll get from the current version of Tableau, which can interactively condition your data source at any time without requiring you to exit to Excel. Power BI is also harder to learn than Tableau. (Read my review of Tableau.)
When you bring data into Power BI, that data becomes a data set. A data set can be used in multiple reports, and visualizations from the data set can display on multiple dashboards. Similarly, a dashboard (Figure 2) can display visualizations from many reports and many data sets.
Figure 2: Power BI reports can contain multiple visualizations. Visualizations can be interactive, and interactions can affect the appearance of other visualizations.
A Power BI report created on the website can use data from a single data set; a report created in Power BI Desktop (Figure 3) can join multiple data sets. On the other hand, some data sets, such as Power View sheets and SQL views, may look and act like they have already joined multiple tables. A parent-child relationship in the data unlocks one of the coolest features of Power BI, the ability to drill down into segments of the data interactively.
Figure 3: Power BI dashboards can display multiple visualization tiles. Asking a question (near the top) can create a new visualization.
Power BI Desktop (Figure 4) allows you to shape and transform your data in ways the Power BI service can’t. Once you define the shaping steps in the Desktop, however, the service can rerun them every time it connects to the data. The Power BI Desktop cannot currently deal with Power View Sheets; that requires the use of Excel 2013 or later with the Power View plug-in enabled.
Figure 4: Power BI Desktop makes heavy use of contextual menus. Here we are sorting a sheet by a column of data. This could also have been done from the ribbon.
In addition to transforming data columns, the Designer can merge tables, drop columns, and create new measures with Data Analysis Expressions (DAX). DAX is very much in the spirit of Excel formulas, only using named columns, not cell ranges. For example:Previous Quarter Sales = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSQUARTER(Calendar[DateKey])).
DAX reminds me a lot of Tableau formulas.
In general, you have two ways to create charts in Power BI. You can select variables from a data set, then select a chart type, or you can ask a question in Q&A (natural-language query). You can select fields by checking their box, by dragging them into the visualization area, or by dragging them into the correct bucket (axis, size, and so on).
Power BI — both the service and the Desktop — can currently create some 16 types of charts from your data, although not all of them can be pinned to a dashboard or recognized by Q&A. The visualizations you can pin to a dashboard are bar and column charts (clustered, stacked, and 100 percent stacked), cards (with or without KPIs or images), combo charts, funnel charts, gauge charts, line charts, maps, tree maps, pie charts (with a legend but without details), scatter and bubble charts, stand-alone images, tables or matrixes (with no images or KPIs), and single-number card tiles (made from the question box or a card with a single measure). If you’re using a card, make sure you have set an aggregation in the Field Well rather than choosing the Do Not Summarize option. (View examples of the supported Power BI visualizations.)
Excluded from use on a dashboard are choropleth (filled) maps, pie charts with details, and tables with images or KPIs. Only half of the charts that can be used on dashboards are known to the Q&A engine.
Any numeric variable can be used as a computed aggregate. Non-numeric variables can only be counted. You pick an aggregate from the context menu associated with the field in the Field Well; options include Do Not Summarize, sum, average (mean), minimum, maximum, count (not blanks), and count (distinct). Median, standard deviation, and other common statistical aggregates are not available from the online report builder, but you will find more statistical operations available in the “group by” and “aggregate column” operations available on the Power BI Desktop Query screen.
The Power BI iPad app (Figure 5) can view dashboards and drill down into reports. It also lets you interact with tiles on your dashboards, create favorites, share dashboards, and annotate and share a snapshot of a tile.
Figure 5. Microsoft Power BI apps for the iPad and Android tablets can view dashboards and drill down into reports. The image shows a sample dashboard for a director of marketing.
From the Power BI site, you can share and unshare dashboards with people in your organization — that is, those who have the same email domain or occupy the same Power BI tenant. They will have to sign into the service, using either the site or the mobile viewer, to view and interact with them. A free account is sufficient for viewers.
Colleagues with whom you’ve shared a dashboard can see your dashboard and interact with your reports in Reading View. They can’t create new reports or save changes to existing reports. They see updates once you save your changes. Colleagues can’t see or download the data set, nor can they use any of the data refresh operations.
Overall, Power BI is a promising self-service business intelligence system. While Microsoft is starting to get the idea that not everyone wants to use Excel for analysis, Power BI currently relies on Excel or the Excel-like Power BI Desktop application for data conditioning and joins between data sources. This is acceptable for the price, but not as convenient as Tableau. Similarly, Power BI usually does a reasonable job of setting the axes on visualizations and setting the sizes of bubbles (in bubble charts), but doesn’t let you refine the appearance of the visualization. Again, this is acceptable for the price, but not as convenient as Tableau.
Next read this:
Martin Heller is a contributing editor and reviewer for InfoWorld. Formerly a web and Windows programming consultant, he developed databases, software, and websites from 1986 to 2010. More recently, he has served as VP of technology and education at Alpha Software and chairman and CEO at Tubifi.
Copyright © 2015 IDG Communications, Inc.
All three next-gen BI solutions make data discovery and analysis remarkably easy, but Tableau does…
Copyright © 2024 IDG Communications, Inc.
Leave a Reply