What you need to know about Power BI now

Come microservice, come cloud, the line-of-business application is always going to be with us. We need to know how our businesses are working: how much we’re selling, how much we’re buying, how our customers are feeling, and every one of a thousand little markers that show the pulse of business.

And that leaves us with the ever-present question: How do we show that information? That’s where business intelligence tools come in, to let us ask questions and get answers, exploring the ever-growing pool of business data we’re storing in our myriad business systems.

Microsoft’s tool for that business data exploration is Power BI, even if you didn’t realize that.

After all, Power BI began life as a set of extra query tools for Excel, taking tabular data and helping you slice and dice it before delivering well-formatted graphical answers. It could connect to SQL Server’s OLAP tool, acting as a familiar (if not user-friendly) user interface to billions of rows of data.

Now a full-fledged web service and a companion desktop app, Power BI is a standalone analytics tool that keeps much of the familiar Excel way of working but adds its own tools. With natural language tools for querying data, you can ask questions and get answers—via the Cortana voice interface if you like. Power BI’s machine learning-powered features also help identify interesting data, guide visualizations of trends, and point out outliers in your data.

Power BI brings the desktop and cloud together

Power BI is an interesting mix of software-as-a-service and desktop app. You can use the desktop app to explore your data and build reports; but it’s the combination of app and cloud service that really makes sense.

It’s best to think of the desktop Power BI app as a way to get initial insights, where you find out the right questions to ask. You can turn the resulting report into a live dashboard on the cloud service, connected to live data and giving near-real-time analytics of trends and indicators.

One of Power BI’s more useful features is the sheer number of data sources it can use: from traditional databases and spreadsheets to modern SaaS services like Zendesk and Github. Business data now resides in so many different places that it’s difficult to be sure what’s relevant, so it’s good to be able to query as many of the repositories as possible. You can even connect Power BI to the statistical language R, using it to display complex statistical information on a range of charts.

In Power BI, you can also use public data with your own information. By mixing public and private data, you can put business information in context. If sales are dropping, is that because your sales team isn’t doing its job, or is there a general slowdown in your business sector? And if there is a sector slowdown, are you performing better than expected, or worse? The more information you have, the better the decisions you’ll make.

Power BI’s query language for data transformation

Building queries and data transformations is easy enough in Power BI. Its underlying query language is at heart a functional programming system that’s designed to extract and transform data from different sources, building new tables for your reports.

Starting in an Excel-like table view of your raw data, you use the query tools to construct a series of transformation steps, adding columns and changing data types using a formula-like approach. Once you’ve constructed a query, an advanced editor shows the resulting Power Query code, ready for additional editing or adding new steps.

Power BI’s visual editing tools also help simplify your data, removing unwanted columns and changing names. Data from other sources can be merged into your query, adding additional information where necessary. Other tools pivot data into aggregate tables or add custom columns based on calculations.

Sharing reports is as important as building them, and Power BI gives you several options. Perhaps the most useful is the ability to build and publish web dashboards that show key performance indicators and tie them to appropriate visualizations. An upcoming Power BI release will add tools that map data to Microsoft Visio diagrams, so you can display the performance of a workflow or a network, color-coding blocks as necessary.

Customize Power BI visualizations

You’re not limited to Power BI’s built-in visualizations: You can also design your own. For example, if you’re using Power BI to display data from IoT sensors, why not show the results on a diagram of the equipment you’re monitoring? It’s helpful to just glance at a diagram and see if a high reading is where it might cause damage, or where it’s just an indicator that equipment needs preventative maintenance. The same goes for geographic information, where different types of maps allow different interpretations of the same data.

You can get tools for creating your own Power BI visualizations, including test suites and sample code, from GitHub. Command-line tools running on a local Node.js installation handle building and compiling your code. Your code is written in Microsoft’s TypeScript language, which builds on existing JavaScript skills with added type protections; styles are added via CSS. It’s a good idea to start any new visualization with static data, to see if it renders the way you want. Once it’s working as expected, add data bindings to live data. When you’ve tested your code, use the command-line tools to package it for sharing.

Microsoft offers Power BI on a subscription model, with access to most of its features available to Office 365 enterprise subscribers. A new premium tier adds new ways to share reports, with the option of embedding your data in apps and sharing it with an unlimited number of viewers. Experienced business analysts and data scientists build interactive reports, and users then drill down into data on their smartphones or PCs. All you need to do is publish a report (using Power BI’s mobile view tools) and then embed it as a web view in an app. Apps can refresh their data sets up to 48 times a day.