Connecting Technology and Business.

Power BI enabled by MS Excel

If you are familiar with Excel, then Power BI from Microsoft will be just a natural extension of your experience with Excel. We know Excel is the best number crunching tool ever since Microsoft Office came into being. It has ruled the computers of Information Workers who wanted to gather data in one place, extracting information out of them using formulae and functions and present them as tables and visualize them as charts. Some went another step ahead to analyze the numbers using pivot tables and other What if analysis tools offered by Excel. Statisticians used the Data Analysis tool pack to dig deep into the data to study patterns and put the data to various tests to pull out hidden information and forecast various business or social outcomes.

The Self-service BI capability offered by Microsoft today capitalizes on these capabilities of Excel and brings the power of Business intelligence into the hands of the ordinary end users.

Microsoft Office 2013 ProPlus or Office 365 ProPlus today has the BI capability in its Excel application. All one needs to do is download the free add ins that are required for this purpose. Power Query, Power Pivot, Power View and Power Maps are the four additional components that are required to add BI capability to Excel.

Power BI 1.png 

Power Query

Power Query helps in discovering data from various data stores and sources - be it proprietary or open source databases and tables. A powerful search capability built in Power Query also helps in discovering data from the public data sets available disseminated in the internet. It can import data from Excel files, .txt or .csv or .xml files. It can also import metadata and links about files in a folder.

Power Query also can pull out queries from various databases like SQL Server, Azure SQL, Access, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase and Teradata. It also can discover data from other data sources like SharePoint list, OData feed, Azure Marketplace, Hadoop file (HDFS), Azure HDInsight, Azure Blob Storage, Azure Table Storage, Active Directory, MS Exchange, Facebook or SAP Business Objects BI Universe.

Power Query also allows users to create their own customized queries.

Power Pivot

Excel today supports 10 million+ of rows of data normally. With the Power Pivot add-in Power Pivot can handle even more. In fact, the data it can handle can match the needs of customers that handle hundreds of thousands of record per day (like in retail chains) or that which handles passenger traffic. Power Pivot helps user build data models by merging building relations between the tables brought inside Excel by Power Query. Relationships are either automatically detected by Power Pivot or can be created by users. Managing the data tables and cleaning the dataset of redundancy or removing the outliers can done here. Transformation of data and adding additional columns with new data or data that have relation to one or more columns of information in the table are made possible by Power Pivot.

Power View and Power Maps

Visualization of data and making it interactive is done by the Power View feature of Excel. Here is where a dashboard of the various aspects of the data model are built for the interpretation of the information produced by Excel. Tables and graphs appear on the same page and make the BI experience richer. Applying filters and sorting info are also possible in this Power View. There is also an animation axis that is provided in the charts created by Power view that brings to life the data in one or more columns of the pivoted tables. If the tables contain geospatial data (names of continents, countries or cities and towns), they can also be plotted on the maps to make the information more meaningful. This also enables the users to relate to the info in the tables in a better way. A 3D view of the maps with animation of data over time helps derive greater insights into the data. An option to create a video of the view is an added bonus feature.

Power BI from Microsoft has more to it than what Excel can do. We will learn about this in the next posting.