With an estimated 500 million Excel users in the world, it’s no wonder that Excel is the #1 business intelligence too in many organizations around the globe. And with the release of Excel 2013, the collection of powerful and flexible data analysis tools built into Excel has only continued to grow. Microsoft is constantly adding new features and functionality to Power BI at pretty fast rate, so now is a great time to start learning about everything that MS Power BI can offer your organization.
Because Excel is just full of a slew of incredible tools, its important for us to understand the difference between the tools, when you should choose each tool, and the role each tool can fill in your organization. That’s what this blog post is all about: Choosing the right self-service tool for the job.
Power Query: Data Discovery & Transformation
What: Power Query is a totally free add-in for Excel 2010 and Excel 2013 to search for and discover data from both within and outside your organization. All you have to do is open the Online Search window, type in what you’re looking for, and viola! Power Query will return a list of data source that you can preview and import into your Excel workbook and/or Power Pivot model.
And the list of possible data source types that Power Query can connect to is growing every month. Aside from the traditional data sources such as SQL Server, Oracle, Access, or SAP, Power Query can also connect to a pretty ridiculous amount of non traditional data sources such as: Hadoop, Facebook, Twitter, text files, Azure, Active Directory, Microsoft Exchange, and a bunch more. Gone are the days of being forced to work with IT in order to get the data you’re looking for. That’s kind of the point of self-service BI, right? 🙂
Not only can you import the data from your selected data source, you also have the ability to clean, transform, filter, and merge the data. With Power Query, we can easily apply a series of transformations in order to perfectly shape our data for our specific reporting requirements. Do you need to pivot your data? Transpose it? Split a column by a delimiter? Rename fields? Create calculations? Upper case the first letter of each word? Change a data type? Apply formatting? Merge census data with your corporate data? Power Query can do all of that and more without writing a single line of code. That’s pretty impressive if you ask me.
And once you’ve customized the data to your liking, the query can be saved for future use or shared with other business users within your organization.
Where: Like I said, Power Query is a free add-in for Excel (2010 or 2013). Once the Power Query add-in is installed, Power Query gets it’s own little ribbon in Excel. Download Power Query here.
When: You’ll most likely find yourself using Power Query when you need to find a specific data set that may or may not currently exist within your organization in order to gain new insights into your business in ways that you previously haven’t had the capability for. If your data needs needs to transformed and shaped for your analysis, Power Query is a perfect candidate for the job.
Power Pivot: Data Modeling & Analysis
What: Power Pivot is a data modeling and data analysis tool all rolled up into one. With Power Pivot, we have the ability to extract data from multiple sources of differing types, create data relationships between the sources, and then model and format the data to our liking using Power Pivot’s drag and drop interface. We can embed hierarchies, unique calculations, and Key Performance Indicators into our Power Pivot model for use in Pivot Tables, Pivot Charts, and Power View reports.
With Power Pivot, your data can be refreshed and updated from its original data sources as often if you like. And if you deploy your Power Pivot workbook to SharePoint, your Power Pivot workbook can even be refreshed on a schedule of your choosing.
And with Power Pivot’s in-memory X-Velocity storage engine, performance is pretty incredible (like, don’t-blink fast), even with large data sets!
Where: Power Pivot is an add-in for Excel 2010 that must be downloaded and install. Power Pivot comes included with Excel 2013 but must simply be turned on. Power Pivot gets its own ribbon in Excel and has its own development window that opens for designing your Power Pivot model.
When: You’ll be using Power Pivot when you need to relate disparate data source to each other and create a custom data model, create calculations for new metrics, create natural hierarchies for navigating through your data, or create Key Performance Indicators (KPI) for dashboard or scorecard type reporting.
Power View: Interactive & Intelligent Data Visualizations
What: Power View is an adhoc & data exploration tool that is designed with the end user in mind. You don’t need to be a data visualizations expert to be able to put together impressive and always presentable reports with Power View. With Power View, you’re always working with live data whether you’re building a Power View report in Excel 2013 or in SharePoint 2010/2013.
Power View gives you the capabilities to explore you data in multiple formats quite easily by switching between chart types with a single click. No coding is required with Power View making this an ideal tool for the business person that just wants to do their job with no fuss or muss.
If you’re developing your Power View reports in SharePoint, you also have the very cool ability export your Power View report to Power Point in an interactive format! That’s right! I can export my Power View report into a Power Point presentation and still interact with it! Very cool!
Where: Power View reports can be developed in Excel 2013 using an Excel table or Power Pivot model as a data source. Power View reports can also be developed in SharePoint 2010 or 2013 using a Power Pivot model, SSAS Tabular Model, or an SSAS Multidimensional Cube as a data source.
Power Map: Geographic Data Visualizations
What: Power Map is a data visualization and data exploration tool that specializes in visualizes geographic data in a 3-dimensional format (using BING maps) and even animate it across time. Power Map allows you tell a story with your data by creating a Tour comprised of multiple scenes that focus on different aspects and trends in your data in a very unique and compelling way.
Once you’ve created your Tour, it can be exported as a movie to be shared with your team or to be shown during a presentation as an interactive guide of your data. I guarantee your audience hasn’t seen their data like this before!
Where: Power Map is a free add-in that can be downloaded and installed in Excel 2013. Get to Power Map on the Insert ribbon in Excel 2013. Download Power Map here.
When: Use Power Map when you want to tell a story with your geographic data on a 3D or 2D map, animate it across time to identify trends and gain new insights, or to simply explore your data in a different way.
I hope you find this information useful and that you’ve gained a better understanding of the self-service business intelligence tools that are part of Excel. Feel free to post any questions, comments, and let me know if you’ve enjoyed this post. Thanks for reading!