I love Power tools. My father was a carpenter in his spare time and used a table saw, drill press, band saw, etc to make furniture and novelty products like a pencil holder shaped like the head of E.T. (I drew that design when I was kid). He sold quite a few of those. Times change. My power tools are quite different from his. I use Power Query, Power Pivot, Power View, and Power BI. However, the names of those tools don’t relate as directly to their function as my dad’s tools did. For most people, when you mention a table saw, they have a pretty solid idea that it is used for cutting. And a drill press, well, that puts holes in things. That’s pretty clear, too. From talking with other people in the Data world, particularly some of my friends on the DBA side of the house, it is just not clear what the Power tools are and what each one is for. So, my goal with this post is to help those folks out.

In the Excel BI stack, sometimes called the Power BI stack, we have a number of power tools:

• Power Query
• Power Pivot
• Power View
• Power Map
• Power BI
• Power Azure (Just kidding… This one does not exist. I just couldn’t resist.)

Let’s take a brief look at each one.

Power Query

Power Query is an add-in for Excel (both 2010 and 2013) that provides the ability to not only bring data into Excel from a number of sources, but also the ability to shape it, clean it up, and do some modeling on the way. Many people (myself included) have defined it as a Self-Service ETL (Extract, Transform, and Load) tool, a much simpler to use SQL Server Integration Services. And it is that. But it is so much more. I feel like that description just does not capture the power (can’t help it) of this fabulous tool. When all is said and done, Power Query is like a machine that takes data in on one side, does stuff to it, and spits it out the other end into Excel.

Power Pivot

Power Pivot was first released as a free add-in for Excel 2010. It was baked right into Excel 2013. Power Pivot lets you build an Analysis Services style data model IN Excel. You can augment that model with DAX (Data Analysis Expressions) to do powerful (I’m at it again, aren’t I?) calculations. It is Power Pivot that lets you drag join columns together to create relationships between tables in your model, thereby eliminating the need for doing VLOOKUP expressions. Power Pivot uses the xVelocity column-store engine to hold the data in the model you design. The data in Power Pivot is extracted from several different data sources (or sent to the model via Power Query). Power Pivot is a container that you design and fill with data. In addition to providing a means of self-service BI, Power Pivot is a fantastic tool for creating prototypes of larger BI solutions. You can put something in front of users in a few hours and make sure you are on the right track with what they are looking for. Just awesome.

Power View

Power View is a data visualization tool that is meant for end users. It is VERY simple to use. It was first release as a web part in SharePoint 2010 and later baked directly into Excel 2013. A key design tenet of Power View was “Two click to ROI.” If it takes more than two clicks to use a feature, it’s probably not worth it. This is a huge contrast from tools like Report Designer (in Business Intelligence Development Studio and SQL Server Data Tools) and Report Builder. Both of those tools have lots of menus within menus within menus. It can take a dozen clicks or more to do one thing. These tools provide much more functionality than Power View, but that functionality adds complexity. Power View is designed to be simple. A key feature of Power View is the cross filtering that happens automatically. Sometimes Power View gets a bad wrap because it is not as robust are Reporting Services; but it is not meant to be. It was not meant to replace (and does not replace) existing tools like Reporting Services. It complements them and is a great tool when used appropriately.

Power Map

Power Map is a tool for creating visualizations of geo-spatial data. While Power View supports creating maps, Power Map supports creating 3-dimensional maps. You can record the use of your maps as short videos called Scenes and play them back. While Power Map looks really cool and provides great eye candy, I must confess I have not played with it all that much.

Power BI

Power BI is an add-on service for Office 365. It provides a specially configured SharePoint Online site that enables some very exciting functionality. Once you have created a data model in Excel with Power Pivot and optionally created Power View reports against it, you can upload that file to a Power BI site to share it with others within and outside of your organization. An exciting feature of Power BI is called Q & A. It lets you use natural language to query your data and get results formatted in a appropriate visualization. If you have sales data up in Power BI and ask for sales over time, if will put your results into a line chart in order to highlight the trend you are likely seeking. The licensing of Power BI is currently very confusing. I can assure you that Microsoft has heard the feedback on this.

So, to sum up VERY simply:

Power Query extracts, transforms, and loads data into Excel.

Power Pivot models and stores data in Excel.

Power View and Power Map visualize data in Excel (Power View is available in SharePoint as well, though).

Power BI shares what you created with the tools above and adds other cool functionality, too.

I hope this helps clarify things a bit. My goal is to do a blog series on each of these tools. I never thought I would say this, but, any day I get to work with data in Excel is a good day for me. These tools provide so much capability for helping people make better use of what they already have. These tools certainly don’t replace the Enterprise BI tools like SQL Server Analysis services. We will need that for quite some time to come. But they do offer an option for enabling better decisions with a much lower barrier to entry than learning to develop cubes and write MDX. Microsoft Senior Technical Fellow Amir Netz has said many times, “BI doesn’t just have to mean Business Intelligence, it can also mean Basic Intelligence.” So much of BI is about making things simpler. These tools do that and more.