excel powerpivot overview

Copyright © 2012 Vincent Brown P a g e | 1 vincentbrown.com.au

AUTOMATION, LEVERAGE

& INNOVATION

Office Productivity

Author, Trainer & Speaker

September, 2012

Introducing PowerPivot for Microsoft Excel 2010

Table of Contents

What is PowerPivot? 1

How does PowerPivot Surpass standard PivotTables? 1

What Data Sources Does PowerPivot Support? 2

What Benefits does PowerPivot provide organisations? 2

What are PowerPivot Data Analysis Expressions? 2

What DAX Functions are available to me? 3

How do I get my Data Sets into PowerPivot? 3

How does PowerPivot utilise Slicer technology? 4

Where can I get PowerPivot from? 4

How do I install the PowerPivot Add-In? 4

For more information… 4

WHAT IS POWERPIVOT?

PowerPivot is a free add-in for Microsoft Excel 2010. Its purpose is to dramatically extend the capabilities of the PivotTable data summarisation and cross-tabulation feature, providing the ability to import data with huge row counts from multiple sources. It primary purpose is to act as a business intelligence and analysis tool for staff who need to easily model and analyse very large data sets.

HOW DOES POWERPIVOT SURPASS STANDARD PIVOTTABLES?

PowerPivot provides four (4) main capabilities that traditional Pivot Tables in Excel simply cannot accomplish:

1. PowerPivot Can Analyse Massive Data Sets

PowerPivot breaks the 1,048,576 row barrier that restricts traditional Pivot Tables due to Excel’s inherent limitations, in theory placing no limit on the number of data rows that can be processed. However, row limits do exist dependent on the available memory and processing power of the computer being used. However, the processing of millions of rows pose no problems for standard organisational workstations.

2. PowerPivot Can Combine (Mash up) Data from Disparate Sources

DATA CAN BE DRAWN FROM VIRTUALLY ANY DATA SOURCE AND

THEN ‘MASHED UP’ INTO SINGLE DATA OBJECT.

PowerPivot can create relationships between different data sources and analyse them as a single data object quickly and easily. Data can be drawn from virtually any data source including Access databases, SQL Server relational databases, Oracle relational databases, text files, Microsoft Excel files, and a wide range of data feeds.

3. PowerPivot Can Create Visually Rich Analytical Models

POWERPIVOT CHARTING CAPABILITIES ARE PERFECT FOR CREATING

DASHBOARD VIEWS OF DATA.

PowerPivot for Excel allows you to output a variety of visual data to your Excel worksheet. You can return data in a PivotTable, PivotChart, Chart and Table (horizontal and vertical), Two Charts (horizontal and vertical), Four Charts and a Flattened PivotTable. Multiple worksheet outputs are also supported, which is great for creating rich, dashboard-like views of data.

4. PowerPivot Uses DAX to Create Calculated Fields for Quantitative Data Analysis

DAX stands for Data Analysis Expressions and is the formula language used in PowerPivot tables. There are similarities between Excel formulas and DAX, making them relatively easy to learn for experienced Excel users.. The DAX formula language is used primarily in creating calculated columns. For example, DAX date functions make date fields more useful by allowing them to be used to filter or group by year, quarter, month or day.



& INNOVATION

Office Productivity


WHAT DATA SOURCES DOES POWERPIVOT SUPPORT?

PowerPivot supports the importation of data sets from a variety of sources, including SQL Server databases, Analysis Services cubes, Access databases, Excel worksheets, text files, data feeds, and more. The table below shows the full list of data sources supported.

Source Versions File type

Access databases Microsoft Access 2003, 2007, 2010.

.accdb or

.mdb

SQL Server relational databases

Microsoft SQL Server 2005, 2008, 2008 R2, SQL Server 2012; Microsoft SQL Azure Database 2

N/A

SQL Server Parallel Data Warehouse (PDW) 3

2008 R2, SQL Server 2012

N/A

Oracle relational databases

Oracle 9i, 10g, 11g.

N/A

Teradata relational databases

Teradata V2R6, V12

N/A

Informix relational databases

N/A

IBM DB2 relational databases

8.1 N/A

Sybase relational databases

N/A

Other relational databases

N/A N/A

Text files N/A .txt, .tab, .csv

Microsoft Excel files Excel 97-2003, 2007, 2010

.xlsx, xlsm,

.xlsb, .xltx,

.xltm

PowerPivot workbook Microsoft SQL Server 2008 R2 and SQL Server 2012 Analysis Services

xlsx, xlsm, .xlsb, .xltx, .xltm

Analysis Services cube Microsoft SQL Server 2005, 2008, 2008 R2, SQL Server 2012 Analysis Services

N/A

Data feeds (used to import data from Reporting Services reports, Atom service documents, Microsoft Azure Marketplace DataMarket, and single data feed)

Atom 1.0 format Any database or document that is exposed as a Windows Communication Foundation (WCF) Data Service (formerly ADO.NET Data Services).

.atomsvc for a service document that defines one or more feeds .atom for an Atom web feed document

Office Database Connection files

.odc

WHAT BENEFITS DOES POWERPIVOT PROVIDE

ORGANISATIONS?

In essence, PowerPivot places powerful data analysis and reporting capabilities into the hands of Excel 2010 users, without requiring them to possess extensive experience or expertise with these functions.

As such, users of this nature can solve a wide range of ‘ad hoc’ data-driven questions themselves on their own desktop, thus:

Taking considerable pressure off data management / reporting departments

Providing organisational staff with the ability to acquire quality aggregated / summarised data in a rapid and timely manner

Employing a desktop application resource most organisational staff possess and are familiar with, i.e. Microsoft Excel

WHAT ARE POWERPIVOT DATA ANALYSIS EXPRESSIONS?

The Data Analysis Expressions (DAX) language is a formula language specific to PowerPivot that facilitates the creation of custom calculated columns within PowerPivot tables. The DAX language employs some existing Excel functions, but is primarily comprised of application-specific functions designed to work with relational data and perform dynamic aggregation.

In many respects, DAX formulas share considerable similarity to traditional Excel formulas, in that they are constructed with an appropriate combination of functions, operators, and values, however, differ in that they are designed to work with tables and columns, not ranges.

DAX FORMULAS ARE DESIGNED TO WORK WITH TABLES AND

COLUMNS, NOT RANGES.



& INNOVATION

Office Productivity


Further, DAX formulas allow the rapid construction and application of highly complex and sophisticated lookups which incorporate custom calculations in a manner that would require huge amounts of time and skill to replicate in Excel.

WHAT DAX FUNCTIONS ARE AVAILABLE TO ME?

PowerPivot’s DAX functions, fall into eight (8) categories, these being:

Date and Time Functions: DAX Date and Time Functions are similar to date and time functions in Microsoft Excel, except that they are based on the datetime data types used by Microsoft SQL Server.

DAX DATE AND TIME FUNCTIONS ARE BASED ON THE DATA TYPES

USED BY MICROSOFT SQL SERVER.

Filter Functions: DAX filter and value functions differ greatly from Excel functions, allowing for the complex and rapid manipulation of PowerPivot data sets using tables and relationships in much the same way as a relational database, complete with data context manipulation to dynamic calculations.

Information Functions: DAX information functions examine cells or rows identified by the user, and determine if the values they contain match the expected data type. For example, the ISERROR function returns TRUE if the value that you reference constitutes a data type mismatch.

Logical Functions: DAX functions act upon an expression to return information about the values or sets in the expression. For example, you can use the IF function to check the result of an expression and create conditional results.

Mathematical and Trigonometric Functions: DAX mathematical functions are similar to those found in Excel.

Statistical Functions: DAX aggregation functions such as sums, count and average are very similar to those found in Excel.

Text Functions: DAX text functions are based on Excel’s string functions, but have been modified to work with tables and columns.

Time Intelligence Functions: Data Analysis Expressions (DAX) includes time intelligence functions to support the needs of Business Intelligence analysis by enabling the manipulation of data using time periods such as days, months, quarters and years, subsequently generating and comparing calculations over those periods.

HOW DO I GET MY DATA SETS INTO POWERPIVOT?

Datasets can be imported into PowerPivot by using any one of four (4) methods:

1. Using the Table Import Wizard

2. Using a Custom Query

3. Using Copy and Paste

4. Using an Excel Linked Table

POWERPIVOT OFFERS AN ARRAY OF MEANS BY WHICH TO IMPORT

DISPARATE DATA SETS FOR PIVOTTABLE ANALYSIS.

1. Add Data by Using the Table Import Wizard

PowerPivot’s Table Import Wizard allows you to connect to a database query dynaset, report, or table, data feed, text file and a number of other supported dataset types. Simply complete the Wizard’s fields to select the data to load, and import that data into your PowerPivot workbook..

2. Add Data by Using a Custom Query

PowerPivot allows you to connect to a Microsoft Access database and use a custom query to import data into the PowerPivot workbook being created. PowerPivot can also import data from a variety of other relational sources including SQL Server, Oracle, Sybase, Informix and DB2.



& INNOVATION

Office Productivity


3. Add Data by Using Copy and Paste

PowerPivot allows you to add data to your PowerPivot workbook by copying it from a Microsoft Excel worksheet and paste it into the PowerPivot window.

4. Add Data by Using an Excel Linked Table

In the context of PowerPivot, a Linked Table is an Excel worksheet table that has been linked to a table in the PowerPivot window. The advantage of creating and maintaining the data in Excel, instead of importing it or pasting it into PowerPivot over and over again, is that you can continue to modify the values in the Excel worksheet, while you are using the data for analysis in PowerPivot. This technique is great for rapid and dynamic data updating as any change made to the source data will be automatically updated in the linked table in PowerPivot.

HOW DOES POWERPIVOT UTILISE SLICER TECHNOLOGY?

PowerPivot seamlessly incorporates the Slicer technology introduced in Excel 2010, thus providing users with one-click filtering controls that narrow down the data shown in PowerPivot PivotTables and PivotCharts. This allows a highly responsive and interactively means by which to display data changes when filters are applied. PowerPivot integrates both Horizontal and Vertical Slicers as fields in the PowerPivot PivotTable Field list, and when populated as required, allow users to quickly select criteria and instantly show the changes.

POWERPIVOT SEAMLESSLY INCORPORATES SLICER TECHNOLOGY TO

CREATE HIGHLY RESPONSIVE AND INTERACTIVE DATA FILTERS.

WHERE CAN I GET POWERPIVOT FROM?

The PowerPivot add-in can be downloaded from www.powerpivot.com. Be sure to get the correct version for your computer configuration, this being either the:

64 bit version (ENU\x64\PowerPivot_for_Excel_amd64.msi)

32 bit version (ENU\x86\PowerPivot_for_Excel_x86.msi).

HOW DO I INSTALL THE POWERPIVOT ADD-IN?

Prior to installing the PowerPivot add-in make sure that the latest Visual Studio 2010 Tools for Office Runtime and Microsoft .NET Framework 4 have been installed first. You can then proceed to install the PowerPivot add-in.

FOR MORE INFORMATION…

I will be writing extensively on the effective integration of PowerPivot into the data modelling and analysis processes of large organisations over the coming months. To access this information, visit vincentbrown.com.au on a regular basis. Happy data crunching

About Vincent

Vincent is a highly qualified and experienced technology training professional with over a decade’s experience working in the corporate training and development space. During this time, he has worked with countless organisational staff, developed scores of courses, programs and resources, and managed numerous large scale technology training projects, one of which saw him awarded Australasian Best IT Trainer in 2008.

Vincent writes and speaks extensively on the effective use of Microsoft Office in the workplace, employing his unique knowledge, experience and perspective to develop highly outcome-focused publications and presentations specifically designed to enhance staff productivity, problem solving skills, process development, and encourage the adoption of innovative workplace practices.

AUSTRALASIAN BEST IT TRAINER 2008

Microsoft Certified Trainer (MCT) Microsoft Office Specialist Master (MOS)

PRINCE2

Master of Internet Computing Bachelor of Education

Diploma of Training and Assessment Cert IV in Training and Assessment

excel powerpivot overview

Documents