Dan Clark - Beginning Power BI with Excel 2013: Self-Service Business Intelligence Using Power Pivot, Power View, Power Query, and Power Map

CHAPTER 1

Introducing Power Pivot

The core of Microsofts self-service business intelligence (BI) toolset is Power Pivot. The rest of the tools, Power View, Power Query, and Power Map, build on top of a Power Pivot tabular model. In the case of Power View this is obvious because you are explicitly connecting to the model. In the case of Power Query and Power Map it may not be as obvious because the Power Pivot tabular model is created for you behind the scenes. Regardless of how it is created, to get the most out of the tool set and gain insight into the data you need to know how Power Pivot works.

This chapter provides you with some background information on why Power Pivot is such an important tool and what makes Power Pivot perform so well. It instructs you on the requirements for running Power Pivot and how to enable it. The chapter also provides you with an overview of the Power Pivot interface and provides you with some experience using the different areas of the interface.

After reading this chapter you will be familiar with the following:

• Why use Power Pivot?
• The xVelocity in-memory analytics engine
• Enabling Power Pivot for Excel
• Exploring the Data Model Management interface

Why Use Power Pivot?

You may have been involved in a traditional BI project consisting of a centralized data warehouse where the various data stores of the organization are loaded, scrubbed, and then moved to an OLAP (online analytical processing) database for reporting and analysis. Some goals of this approach are to create a data repository for historical data, create one version of the truth, reduce silos of data, clean the company data and make sure it conforms to standards, and provide insight into data trends through dashboards. Although these are admirable goals and are great reasons to provide a centralized data warehouse, there are some downsides to this approach. The most notable is the complexity of building the system and implementing change. Ask anyone who has tried to get new fields or measures added to an enterprise-wide warehouse. Typically this is a long, drawn-out process requiring IT involvement along with data steward committee reviews, development, and testing cycles. What is needed is a solution that allows for agile data analysis without so much reliance on IT and formalized processes. To solve these problems many business analysts have used Excel to create pivot tables and perform ad hoc analysis on sets of data gleaned from various data sources. Some problems with using isolated Excel workbooks for analysis are conflicting versions of the truth, silos of data, and data security.

So how can you solve this dilemma of the centralized data warehouse being too rigid while the Excel solution is too loose? This is where Microsofts self-service BI tool set comes in. These tools do not replace your centralized data warehouse solution but rather augment it to promote agile data analysis. Using Power Pivot you can pull data from the data warehouse, extend it with other sources of data such as text files or web data feeds, build custom measures, and analyze the data using pivot tables and pivot charts. You can create quick proofs of concepts that can be easily promoted to become part of the enterprise wide solution. Power Pivot also promotes one-off data analysis projects without the overhead of a drawn-out development cycle. When combined with SharePoint, Power Pivot, workbooks can be secured and managed by IT, including data refresh scheduling and resource usage. This goes a long way to satisfying ITs need for governance without impeding the business users need for agility.

Here are some of the benefits of Power Pivot:

• Functions as a free add-in to Excel
• Easily integrates data from a variety of sources
• Handles large amounts of data upward of tens to hundreds of millions of rows
• Uses familiar Excel pivot tables and pivot charts for data analysis
• Includes a powerful new Data Analysis Expressions (DAX) language
• Has data in the model that is read only, which increases security and integrity

When Power Pivot is hosted in SharePoint, here are some of its added benefits:

• Enables the sharing and collaboration of Power Pivot BI Solutions
• Can schedule and automate data refresh
• Can audit changes through version management
• Can secure users for read-only and updateable access

Now that you know some of the benefits of Power Pivot, lets see what makes it tick.

The xVelocity In-memory Analytics Engine

The special sauce behind Power Pivot is the xVelocity in-memory analytics engine (yes, that is really the name!). This allows Power Pivot to provide fast performance on large amounts of data. One of the keys to this is it uses a columnar database to store the data. Traditional row-based data storage stores all the data in the row together and is efficient at retrieving and updating data based on the row key, for example, updating or retrieving an order based on an order ID. This is great for the order entry system but not so great when you want to perform analysis on historical orders (say you want to look at trends for the past year to determine how products are selling, for example). Row-based storage also takes up more space by repeating values for each row; if you have a large number of customers, common names like John or Smith are repeated many times. A columnar database stores only the distinct values for each column and then stores the row as a set of pointers back to the column values. This built-in indexing saves a lot of space and allows for significant optimization when coupled with data compression techniques that are built into the xVelocity engine. It also means that data aggregations (like those used in typical data analysis) of the column values are extremely fast.

Another benefit provided by the xVelocity engine is the in-memory analytics. Most processing bottlenecks associated with querying data occur when data is read off of or written to a disk. With in-memory analytics, the data is loaded into the RAM memory of the computer and then queried. This results in much faster processing times and limits the need to store pre-aggregated values on disk. This advantage is especially apparent when you move from 32-bit to 64-bit operating systems and applications, which are becoming the norm these days.

In addition to the benefits provided by the xVelocity engine, another benefit that is worth mentioning is the tabular structure of the Power Pivot model. The model consists of tables and table relationships. This tabular model is more familiar to most business analysts and database developers. Traditional OLAP databases such as SSAS (SQL Server Analysis Server) present the data model as a three dimensional cube structure that is more difficult to work with and requires a complex query language, MDX (Multidimensional Expressions). I find, in most cases (but not all), that it is easier to work with tabular models and DAX than OLAP cubes and MDX.

Enabling Power Pivot for Excel

Power Pivot is a free add-in to Excel available in the Office Professional Plus and Office 365 Professional Plus editions. If you are using Excel 2010, you need to download and install the add-in from the Microsoft Office web site. If you are using Excel 2013 (the version covered in this book), the add-in is already installed and you just have to enable it. To check what edition you have installed, select the File menu in Excel and select the Account tab.

On the Excel Account tab. Take note of the edition and the version. It should be the Professional Plus edition and ideally the 64-bit version. The 32-bit version will work fine for smaller data sets, but to get the optimal performance and experience from Power Pivot you should use the 64-bit version running on a 64-bit version of Windows with about 8 gigs of RAM.