Excel PowerPivot Overview by Vincent Brown

Office Productivity Author, Trainer & Speaker AUTOMATION, LEVERAGE & INNOVATION

September, 2012

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

Introducing PowerPivot for Microsoft Excel 2010

DATA CAN BE DRAWN FROM VIRTUALLY ANY DATA SOURCE AND THEN ‘MASHED UP’ INTO SINGLE DATA OBJECT.

Table of Contents What is PowerPivot?

How does PowerPivot Surpass standard PivotTables?

What Data Sources Does PowerPivot Support?

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.

What Benefits does PowerPivot provide organisations? 2 What are PowerPivot Data Analysis Expressions?

What DAX Functions are available to me?

How do I get my Data Sets into PowerPivot?

How does PowerPivot utilise Slicer technology?

Where can I get PowerPivot from?

How do I install the PowerPivot Add-In?

For more information…

3. PowerPivot Can Create Visually Rich Analytical Models

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:

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

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. Copyright © 2012 Vincent Brown

POWERPIVOT CHARTING CAPABILITIES ARE PERFECT FOR CREATING DASHBOARD VIEWS OF DATA.

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.

P a g e |1

vincentbrown.com.au

Office Productivity Author, Trainer & Speaker AUTOMATION, LEVERAGE & INNOVATION

WHAT DATA SOURCES DOES POWERPIVOT SUPPORT?

WHAT BENEFITS DOES POWERPIVOT PROVIDE ORGANISATIONS?

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 Access databases SQL Server relational databases

SQL Server Parallel Data Warehouse (PDW) 3 Oracle relational databases Teradata relational databases Informix relational databases IBM DB2 relational databases Sybase relational databases Other relational databases Text files Microsoft Excel files

PowerPivot workbook

Analysis Services cube

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

Versions Microsoft Access 2003, 2007, 2010. Microsoft SQL Server 2005, 2008, 2008 R2, SQL Server 2012; Microsoft SQL Azure Database 2 2008 R2, SQL Server 2012 Oracle 9i, 10g, 11g. Teradata V2R6, V12

File type .accdb or .mdb N/A

N/A

.txt, .tab, .csv .xlsx, xlsm, .xlsb, .xltx, .xltm xlsx, xlsm, .xlsb, .xltx, .xltm

Microsoft SQL Server 2008 R2 and SQL Server 2012 Analysis Services Microsoft SQL Server 2005, 2008, 2008 R2, SQL Server 2012 Analysis Services Atom 1.0 format Any database or document that is exposed as a Windows Communication Foundation (WCF) Data Service (formerly ADO.NET Data Services).

Office Database Connection files



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

N/A

Excel 97-2003, 2007, 2010

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

N/A

N/A 8.1

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.

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 applicationspecific 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.

N/A

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

DAX FORMULAS ARE DESIGNED TO WORK WITH TABLES AND COLUMNS, NOT RANGES. P a g e |2

vincentbrown.com.au

Office Productivity Author, Trainer & Speaker AUTOMATION, LEVERAGE & INNOVATION

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.

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:

DAX DATE AND TIME FUNCTIONS ARE BASED ON THE DATA TYPES USED BY MICROSOFT SQL SERVER.

Using the Table Import Wizard

Using a Custom Query

Using Copy and Paste

Using an Excel Linked Table

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.

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.

P a g e |3

vincentbrown.com.au

Office Productivity Author, Trainer & Speaker AUTOMATION, LEVERAGE & INNOVATION

3. Add Data by Using Copy and Paste



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

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.



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

HOW DO I INSTALL THE POWERPIVOT ADD-IN?

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 oneclick 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.

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 POWERPIVOT SEAMLESSLY INCORPORATES SLICER TECHNOLOGY TO CREATE HIGHLY RESPONSIVE AND INTERACTIVE DATA FILTERS.

Master of Internet Computing Bachelor of Education Diploma of Training and Assessment Cert IV in Training and Assessment

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:

P a g e |4

vincentbrown.com.au