Excel formulas and pivot tables might arguably be the most popular programming language in the world and these new tools – Power Query, Power Pivot, Power Map and Power View – are even more powerful. You can connect to, clean, deduplicate and transform almost any data; explore and filter millions of rows of data; build KPIs; and create visualizations.
Even though you use Excel to build the data model that you’re going to analyze, the tools you do that analysis with aren’t part of the Excel interface. Even worse, they all have their own interfaces – which look almost, but not quite like Excel – in separate windows. You can end up connecting to data and cleaning it in Power Query, analyzing it in Power Pivot and then visualizing it in Power Map and Power View as three or four disconnected steps.
Powerful also means complex -- very complex compared to self-service analytics solutions like Tableau. Some relatively simple things (like drawing lines on a map) can get complicated with Excel power tools. And when you’ve finished using up to four different tools on top of Excel, what you get needs a complex mix of SharePoint, Silverlight, Excel and HTML5 to share with other users.
Compare that to Microsoft’s Power BI service, which is both powerful and easy to use – with new features being added every week – and those complex Excel tools make even less sense. That’s why Power BI, the cloud service, has a desktop companion app, Power BI Desktop (recently renamed from Power BI Designer), that’s taking over from the complex Excel extras.
“Business analysts have wanted the self-service BI capability that allows them to not have to stand in line to get their work done, to not have to go get someone else to do measures for them in Cognos or Analysis Services,” says James Phillips, the general manager of data experiences at Microsoft. “I want something that allows me to get my work done without being a BI professional.”
[Related: 10 Microsoft Power BI for Office 365 success stories]
Phillips has overseen the transition of Power BI from a SharePoint feature to a service that he expects to have a billion users. The service only came out of preview in July 2015, and it was already being used by 500,000 users at 45,000 companies in 185 countries (even though it’s only officially available in 145 of those).
Excel has been a business intelligence service for a long time and hasn’t been losing users directly to tools like Tableau because, Phillips claims, all those Tableau users still use Excel as well. “They’re not replacements for each other. I’ve never met a business analyst who doesn’t use Excel.” But they’ve turned to those other tools as well, because they want to get value out of their data in a different way than they can in Excel.
Instead of trying to cram those options into Excel, or bolting them onto the side, Microsoft is now supporting a much broader set of analysis tools – from the Power BI service, to its purchase of Revolution Analytics and its investment in R, to the way the Excel tools have become their own program.
“Instead of saying ‘it slices, it dices, it does julienne fries, you don’t need anything else,’ we’re keeping Excel good at what it’s good at,” Philips says. “Excel offers unbelievably powerful ad hoc analysis. It’s great for analyzing any kind of data. It’s effectively a programming environment for data. Really, it’s beautiful! But the interactive designer, where you can slice and dice and do visualizations for your reports, is Power BI Desktop.”
Like the rest of Power BI, that’s on an aggressively fast schedule. “We’re shipping every month,” says Phillips. “That pace of innovation I expect to actually accelerate. We’re on a massively fast track to mature this thing.”
In August, for example, Power BI Desktop got an option for importing Power Query queries, Power Pivot models and Power View worksheets…and there will be more import and export options to Excel in the future (you can already get data from a wide range of databases, file formats and Azure services, now including HDInsight Spark and Azure SQL Data Warehouse).
That’s in addition to all the ways you can bring data into the Power BI service itself, which can connect to both cloud services and on-premises data (which you don’t have to move into the cloud to analyze). The free version of Power BI works with cloud services and gives you a limited amount of storage; the subscription version adds connections to your own data, as well as more storage.
[Related: 11 must-know tricks for Excel power users]
That combination is what makes Power BI so appealing. It can already pull data from a wide range of cloud services, including MailChimp, Zendesk, Salesforce, QuickBooks, Twilio, GitHub, Uservoice and even Google Analytics. Sage and Adobe Analytics are coming soon, and there’s a long list of others on the way. “We've gotten a cadence where we’re releasing at least one content pack every single week,” says Phillips. “We’re really on a rocket ship road. If we only ship one a week, with the commitments we already have, we could sustain another year of weekly content packs.”
Organizations can create their own content packs for getting data from their on-premise systems and the cloud services they use. What the content packs do is give Power BI the data model of the services it gets data from, so it can automatically build dashboards and charts to expose useful data, and so you can use the natural language Q&A feature. That lets you type in a question like “what are the best selling products in each territory in spring” and get a map showing that data without typing a single line of code.
Power BI Desktop is for when you want to go beyond the built-in visualizations, and Microsoft’s vision is for it to be much more than a companion to Excel.
Phillips says he hopes that Microsoft’s regular updates to Power BI Desktop won’t be the only way it gets more features. Microsoft has released both the desktop software and the visualization stack that powers it as open source on GitHub. “That’s all the Power BI visualization stack and all the native visuals,” he says.
“Very soon, we are going to offer the ability to make custom visualizations. You will be able to go crazy with visualizations. People talk about other vendors having a lot of visualizations; we’re completely unshackling the notion that your BI provider should tell you how to use visualizations.”
Microsoft is hoping that other data analytics services will pick up Power BI Desktop; that would give users some degree of interoperability between different services, Phillips suggests, and it would also mean more developers working on improving the software. Israeli BI vendor Pyramid Analytics is already adopting it.
“Arming a business analyst with visual reports should not cost an arm and a leg,” claims Phillips. “It should be free to start modelling data and visualizing it.”