For many years now, Microsoft has been providing a variety of Business Intelligence (BI) tools that can address key business workloads. In addition to Microsoft, there is a wide range of other BI tool vendors in the market that address the increasing analytics needs of businesses and individuals, including Tableau, Qlik, IBM, Tibco and many others.
However, I've decided to go with Microsoft BI tools, for 5 simple reasons: they are very powerful, affordable, easy to use, accessible to almost everyone with a PC and MS Office, and on top of that, they are leading the BI market. Check out the reports below from Gartner and Forrester - two of the largest technology analysts in the world - comparing different BI platforms in the market today.
Microsoft BI Suite
Now with the "Why" put aside, let's look at what Microsoft has to offer for the Self-Service BI landscape (to learn more about Self-Service BI, have a look at this blog post).
Let’s start out with some high level context. The suite of Microsoft Self-Service BI tools includes: Power Pivot, Power Query, Power Map, Power View and Power BI. They may not necessarily go by those names currently, but this is the context, so bear with me.
So basically PowerPivot, Power Query, Power View and Power Map are all included within Excel 2016. Power BI can be downloaded from this link.
Microsoft Excel enables you to collect and analyze data, and to develop reports. Excel 2016 for Windows has all the functionality and features you're used to with some added features and enhancements. Several of the top new and improved features include the following:
For more information, see the following articles.
Power Pivot is a data modeling technology that lets you create data models, establish relationships, and create calculations. You can work with large data sets, build extensive relationships, and create complex (or simple) calculations, within the familiar experience of Excel.
With Power Pivot you can filter data and rename columns and tables while importing the data, and create perspectives to limit the number of columns and tables that your workbook consumers see. You can write advanced formulas with the Data Analysis Expressions (DAX) expression language. You can create key performance indicators (KPIs) to use in PivotTables and Power View reports.
You can also create a relationship between two tables of data, based on matching data in each table, even when the tables are from different sources. The following Diagram view in Power Pivot shows relationships between several tables.
If you don’t see Power Pivot in your own Excel, follow these steps:
For more information, see the following articles.
By itself, Power Pivot can connect to data sources and pull in data, but data isn’t always ready to be pulled straight into an application and used. Sometimes it requires massaging, transforming, aggregating, and clean up. This is where Power Query steps in and assists users with transforming raw data for analysis. Power Query is optional. If all of your data is served up neat and tiny, perhaps in a data warehouse or cubes, Power Query might not be necessary. It could also be a huge help.
In Excel 2013, Power Query was an add-in with it’s own menu in the ribbon. It went native in Excel 2016, and its functionality is now part of the "Get & Transform" section on the Data tab. It disappeared because Microsoft retired the Power Query name. It still exists under the hood but is not called out distinctly.
Power Map is a three-dimensional (3-D) data visualization tool that lets you look at information in new ways. In Excel 2013, it was included as an add-in, but it is now fully integrated into Excel 2016. You'll find 3D Maps alongside other visualization features on the Insert tab.
3D Maps lets you discover insights you might not see in traditional two-dimensional (2-D) tables and charts. 3D Maps include the following features:
For more information, see Get started with 3D Maps
Power View renders Power Pivot data into interactive dashboards. In Excel 2013, Power View was added to the ribbon (in Pro Plus only). It was located in the Ribbon, under Insert, in the Reports section. It was removed from the ribbon in Excel 2016 and became part of other visualization features on the Insert tab. Note, it is not part of the ribbon as a default, you need to make sure you have the add-in enabled and the add Power View to the ribbon in a custom group. Steps are located here.
The same site also notes that all of the functionality of Power View is now available as part of Power BI Desktop. It will continue to be supported in Excel 2016, but it doesn’t specify whether it will be continued in the future. If you find yourself using Power View, consider taking a stronger look at Power BI.
With Power BI you can bring together in one place dashboards that help you track the pulse of your business or organization, drill down to report details, and connect multiple datasets from a variety of data sources. You can share your dashboards with people in your organization.
Users can import Excel workbooks that contain Power Query queries, Power Pivot models and Power View worksheets. All Power Pivot external data connections will be converted to queries in Power BI Desktop, and users will have the same functionality to create relationships, etc as in Excel Power Pivot.
The features of Power BI are best explained in this short video below.
I hope you found this post useful. Please use the comments sections for feedback or additional info required.