PowerPivot – Microsoft’s Answer
to Self-Service Reporting
Microsoft’s Latest Foray in the Business Intelligence Arena
COLLABORATIVE WHITEPAPER SERIES
COLLABORATIVE WHITE PAPER SERIES:
PowerPivot – Microsoft’s Answer to Self-Service Reporting
In the last quarter of 2010, Microsoft first introduced PowerPivot into the Business Intelligence (BI)
market with the release of SQL Server 2008 R2. PowerPivot is the latest addition to the Microsoft BI
stack and is being touted by Microsoft as the answer to self-service BI.
So what exactly is PowerPivot? PowerPivot was initially, at the time of its launch, a free, downloadable
add-in (available from Microsoft) to Excel 2010. Today, with the latest version of Excel, it is still an
add-in but is now integrated in to the core product of both the professional plus version of Excel 2013
and the Office 365 professional plus version. One of the key features of PowerPivot is that it enables
an Excel user to bring in millions of rows of data from a variety of different data sources, join them
together and then create basic reports and charts in minutes. Virtually everyone has hit limits loading
data into Excel. With PowerPivot for Excel, a user can now load over 100 million rows of data very
quickly and get back aggregated results such as “total revenue,” on the fly, without the need for any
pre-calculated results.
To liken this to other capabilities in the BI marketplace, PowerPivot is an in-memory analytics tool like
QlikView. This is PivotTables on Steroids!
I. PowerPivot – What it is and what it isn’t
What it is
As stated above, PowerPivot is an add-in for Excel. Once installed (with Excel 2010) or
activated (with Excel 2013), PowerPivot appears as a menu option on the main Excel
ribbon. Clicking this option reveals the main PowerPivot menu as Figure 1 reveals.
PowerPivot enables a user to load and join data from a wide variety of sources –
Relational databases - such as SQL Server, DB2 or Oracle, Analysis Services cubes,
Reporting Services files, text files, atom data feeds, or any OLEDB/ODBC data source.
One of the key benefits with PowerPivot is that users can combine any of the above
data sources with data contained in an Excel Table. Each data source (including an
Excel Table) loads into a separate tab. Data can be sorted and filtered on the way
into PowerPivot without writing any “code” or SQL – all through a GUI interface!
It is important to note that using this GUI approach values that do not exist in the data
cannot be filtered out. The data brought in can be augmented by creating new Derived
Analysis Expressions (DAX).
2
Figure 1: PowerPivot in Excel
Most data warehouses or data marts do not contain all the
data sources that are needed across the organization and
so PowerPivot allows you to easily combine and link your
own Excel tables with these other sources—a feature that
is extremely important to virtually all business analysts.
With PowerPivot you can load and filter data on the way
into Excel without having to write a lick of SQL, as well as
filter data once it’s in. Different data sets can only (in this
version) be joined via a single column. If multiple columns
are needed to perform a join, a compound key could
be created within PowerPivot and then this key used for
the join. This is illustrated in Figure 2, which shows the
popup menu used to create a relationship, known as a
“join” in PowerPivot terminology.
of space needed to save an Excel file once it’s been loaded
with data. As the underlying engine is Analysis Services,
PowerPivot offers a vast array of date and time intelligence
functions and capabilities. With PowerPivot a user has
access to functions such as TOTALQTD, TOTALYTD,
SAMEPERIODLASTYEAR, NEXTMONTH, NEXTYEAR
and ENDOFMONTH.
PowerPivot also has special functions to deal with measures
that do not aggregate across time such as Inventory.
What can be done with the data once it’s in Excel? As stated
briefly above, new calculated columns of data can be added
in PowerPivot by using DAX. At first glance, DAX functions
look very similar to existing Excel functions, but the major
difference is that functions in Excel normally operate on
cells or ranges, whereas DAX functions operate on whole
columns, tables and measures, as well as relationally across
tables. There are numerous DAX functions that span the
following categories: filter, logical, text, aggregate, and date
and time intelligence. By default, the outcome of a DAX
formula would be based on the filter’s (Slicer) settings. For
example, if ‘Year’ was a slicer and ‘2008’ was selected and
you had two columns – ‘Revenue’ and ‘% of Total’ with
Products for Rows, the results of the table would show
product ‘Revenue’ and ‘% of Total for 2008’ for all products.
This can be overridden in cases where you may want to
show the ‘% of Total’ for all years and not just the Year or
Years selected. A few brief examples of DAX functions are:
Under the hood, PowerPivot creates a virtual Analysis
Services cube and uses Vertipaq (a compression technology
that Microsoft acquired) to drastically reduce the amount
Figure 2: PowerPivot “join”
3
COLLABORATIVE WHITE PAPER SERIES:
PowerPivot – Microsoft’s Answer to Self-Service Reporting
•SUM (Orders [OrderID]) - In this example, ‘Orders’
is the tab name (based on a table called orders) and
‘[OrderID]’ is the column name
organization, you need to use SharePoint 2010 or later (this
uses Excel Services and PowerPivot Galleries and Libraries
so that non PowerPivot users can view and interact with
the analysis without having PowerPivot on their desktops).
It should be noted that if a PowerPivot model is created
using Excel 2013, it cannot be shared to someone using
an earlier (Excel 2010) version.
•CALCULATE (SUM (‘Order Details’ [Sales Amount]),
ALL(Orders))
•RIGHT (Orders [OrderID], 2)
•RELATED (Products [Category])
Most organizations – even if they are Microsoft centric –
are normally slow to adopt the latest release (2013) of
Office and so will make the roll out and adoption of this
technology (other than on isolated PC’s) slower in the
market. Another important distinction is whether a user
has a 32 or 64 bit operating system (OS). Having a 64
bit OS allows a user to load in more rows and have faster
aggregations. In tests on a “normal” laptop using the 32
bit PowerPivot version (in Excel 2010), 3.7 million rows of
data were loaded in approximately five minutes. PowerPivot
had no problems with all totals appearing instantaneously.
After data has been brought in, all the necessary columns
added, and the joins made, there are several ways to
report on data:
•The main two ways are to create either PivotTables or
PivotCharts (an example of the type of reporting that
can be created is given at the end of this document).
•Write an SQL Server Reporting Services report on a
PowerPivot model published to SharePoint.
•Use the GETPIVOTDATA() function from Excel.
An example of this type of function would be
=GETPIVOTDATA(“[Measures].[Sum of Quantity]”,
$A$1, “[Products].[Category]”,“[Products].
[Category].&[Beverages]”).
Figure 3: Value fields
•Use Excel’s CUBE functions. An example of this type
of function would be =CUBEMEMBER(“PowerPivot
Data,” “[Measures].[Sales],” “Sales”).
•Use a third party tool such as Tableau.
Fortunately for the user, both the GETPIVOTDATA and
CUBEMEMBER functions are self-generating once a user
types the “=” sign in a cell.
Saving a PowerPivot analysis is just like saving an Excel file
and on the face of it seems like just an ordinary Excel file,
but by changing the extension to be .zip instead of .xlsx, the
difference becomes clear. If you open this file with a viewer
like WinZip, you will see that in fact there are several
folders, and by exploring one of them – ‘xl,’ ‘CustomData’ –
you will see the size of the compressed data file.
The term “free” was used earlier, but there is a hidden
cost to PowerPivot. First, you either need to upgrade to
Excel 2010 or Excel 2013, and secondly, in order to share
any information created using PowerPivot around the
4
Even outside of PowerPivot, Excel 2010 and later has
some interesting PivotTable features such as slicers (filters)
that allow a user to easily filter data. Slicers can be used
when creating any PowerPivot Analysis to enable a user to
very easily filter the data that they need. Another PivotTable
feature that is extremely useful, and again worthy of note,
enables the user to select a value field such as “Revenue,”
place this in the data area, and then drag the field again
into the data area and convert it to a variety of formats
such as ‘% of Total’ or “Rank.” This feature can create
a multitude of typical calculations used in reporting in a
few simple mouse clicks, whereas in other BI tools this
involves creation of formulas and utilizing various functions
within the various tools—no easy task.
semantic layer that can be shared across the organization
and security. A semantic layer is one element that many BI
tools provide (for example in BusinessObjects, this layer is
called a Universe, and in Cognos, it is called Framework
Manager Model) that shields a user from the joins and
underlying table structures. Another gap lies within the
security capabilities of PowerPivot. The only security
offered is by either SharePoint or the user account for the
underlying database access. Since its launch, Microsoft has
partially addressed the security issue and the semantic layer
issue by allowing a PowerPivot model to be deployed to a
Tabular Analysis Services model on a server. This model can
then have:
•Role-based security model using Active Directory
This feature is illustrated in Figure 4. In this example,
‘Internet Sales Amount’ was dragged into the data area
twice. By right clicking and selecting ‘Show Values As,’
a sub menu showing all the different options appears.
•Row and column level security to secure data at the
deepest level
Without SharePoint 2010 and beyond, the sharing of
analyses created using PowerPivot are going to be limited
to those who have PowerPivot installed on their local
machines. Another area worth mentioning, where caution
should be taken, is the use of date and time intelligence
functions. Some functions will only return correct or intuitive
results if the dates are contiguous. To get around this issue,
the creation of a separate date or time dimension with
continuous dates is recommended.
By selecting ‘% of Column Total’ the values appear as
follows, automatically formatted.
This new column can easily be renamed to a more
appropriate name.
What it isn’t
What are the gaps or cons of using PowerPivot? One of the
most important elements missing from this tool is a robust
Figure 4: Selecting value fields
II. Summary
Most business people conduct reporting in Excel – and it is
especially important to those in the finance function. When
Collaborative Consulting’s professionals introduce a new BI
tool to a client, one of the most frequently asked questions
is “can I export that to Excel?” The main reasons this may
not yet be possible are:
•The organization may not have a data warehouse,
and therefore the data needed for reporting is
contained in numerous places.
5
COLLABORATIVE WHITE PAPER SERIES:
PowerPivot – Microsoft’s Answer to Self-Service Reporting
•The organization has a data warehouse, but the
warehouse does not contain all sources necessary
for all reporting.
warehouse. Warehouses typically automate, standardize
and cleanse data as well as capture certain attributes
so that changes over time can be captured and reported
correctly, for example capturing sales force changes and
organizational hierarchies. These types of things are much
harder to do in PowerPivot. PowerPivot empowers a user
with the ability to combine this with other local data to
complete reporting without concern for size or row limits.
•The reporting tools typically offered by BI vendors
allow for simple report generation, but typically,
more complex reports are needed by the business
and therefore require more of an IT developer’s
skill set – where things like year over year percent
variance calculations are required.
Most BI vendors, and especially those in the in-memory
analytics category such as QlikView, will view Microsoft
as a threat. The only thing that will slow down adoption
in the market is the fact that Office and SharePoint 2010
(or later) will be necessary in order for users to be able
to share analysis (although it should be noted that only
Office 2010 or later is needed for standalone reporting).
The latest version of Excel 2013 with all the new features
such as Quick Analysis, Power Maps (allowing excellent
geospatial analysis), Power Query and built in office “apps”
to make everyday tasks in Excel even easier, make Excel a
formidable self-service tool.
For these reasons, many business analysts use tools
like Excel and Access to load, manipulate and report on
data from multiple sources. Historically, Access has been
chosen over Excel because of initial row limits or memory
limits with Excel. Access also has a limit on the size of
the database it can handle. With PowerPivot, a user has
the ability to load massive amounts of data – over 100
million rows – into Excel and do their reporting, thus
enabling business users to do their jobs with greater ease
and self-sufficiency.
Even though some entire data warehouses could now be
loaded into Excel, there will always be a need for a data
Figure 5: An example of the reporting that can be created in Excel. All of Excel’s features can be applied and numerous chart types and templates are available.
6
COLLABORATIVE WHITE PAPER SERIES:
PowerPivot – Microsoft’s Answer to Self-Service Reporting
Collaborative Consulting is a leading information
technology services firm dedicated to helping
our clients achieve business advantage through
the use of strategy and technology. We deliver a
comprehensive set of solutions across multiple
industries, with a focus on business process and
program management, information management,
software solutions, and software performance and
quality. We also have a set of offerings specific to
the life sciences and financial services industries.
Our unique model offers both onsite management
and IT consulting as well as U.S.-based remote
solution delivery.
To learn more about Collaborative, please
visit our website at www.collaborative.com,
email us at [email protected], or
contact us at 877-376-9900.
Copyright © 2014 Collaborative Consulting, LLC. All rights reserved. This product
is protected by U.S. and international copyright and intellectual property laws.
WP.614.24