Introduction to powerpivot ext945
1 / 26

Introduction to PowerPivot EXT945 - PowerPoint PPT Presentation

  • Uploaded on

Introduction to PowerPivot EXT945. David Dye. Introduction to Microsoft SQL Server Reporting Services. Introduction Introduction to PowerPivot Working With PowerPivot. Introduction. David Dye [email protected] HTTP://WWW.SQLSAFETY.COM. Introduction to PowerPivot. What is PowerPivot ?.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Introduction to PowerPivot EXT945' - kemp

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Introduction to powerpivot ext945

Introduction to PowerPivotEXT945

David Dye

Introduction to microsoft sql server reporting services
Introduction to Microsoft SQL Server Reporting Services

  • Introduction

  • Introduction to PowerPivot

  • Working With PowerPivot


Introduction to powerpivot
Introduction to PowerPivot

What is powerpivot
What is PowerPivot?

  • Free add in for Excel

  • Extends Excel capabilities

  • Allows business analysts to create self-service analytics solutions

  • UtilizesVertiPaq technology

    • PowerPivot 2010 uses VertiPaq

    • PowerPivot 2013 uses xVelocity

      • Next generation of the VertiPaq engine

    • VERY fast as it is in-memory technology

      • If you don’t have enough memory for the project it won’t open

      • Consider 64Bit

    • Provides native compression

  • Embedded Analysis database

Ssas tabular data mode
SSAS Tabular Data Mode

  • PowerPivot is client side technology

    • Requires users to have sufficient horsepower and resources on their laptop

      • REMEMBER PowerPivot is in memory

  • SSAS – SQL Server Analysis Services is server side technology

    • More resources are generally available at the server

  • Multidimensional mode

    • Have been available since SSAS was first introduced

      • More complex to create support

  • Tabular mode

    • PowerPivot at the server

    • Projects can be created in Excel or SSDT and deployed to an instance of SSAS in tabular data mode

Excel vs powerpivot
Excel vs. PowerPivot

  • PowerPivot

    • In-memory MUCH faster

    • 10x-15x data compression

    • Multidimensional

    • Supports

      • KPI’s

      • Hierarchies

    • DAX

      • Data analysis expression language

      • Similar, but more powerful, than the Excel expressions

    • Integrate multiple sources

      • Excel

      • SQL

      • OData

      • Multidimensional

      • Excel

    • Organize multiple sources in a relational way

Working with powerpivot1
Working with PowerPivot

  • Once installed the PowerPivot menu is available

  • Unless created in PowerPivot it is nothing more than a spreadsheet

    • KEEP IN MIND PowerPivot is ALL in memory

      • 64bit is preferred.

      • If there is not enough memory the PowerPivot project

        • Cannot be opened

        • Cannot be saved

Data sources
Data Sources

  • PowerPivot can utilize different data source:

    • SQL Server

    • SSAS multidimensional data models

    • Excel

    • Clipboard

    • PowerPivot

    • OData

    • SharePoint lists

    • Access

    • Windows Azure marketplace

    • Text

    • OLEDB

    • ODBC

    • ANYTHING you can connect to!!

Importing data
Importing Data

  • Based on data connection

  • Supports

    • Filtering

    • Friendly Name

    • Defining columns to import


        • In memory can quickly fill up with unused data

        • Once finished the data will be loaded in memory


  • Unique data source that imports data from the clipboard

    • Not recommended

    • The data exists ONLY in the PowerPivot sheet

    • Cannot be refreshed

    • Best practice to use persistent data sources

      • SQL

      • Excel

      • SSAS

      • Etc.

Working with data in powerpivot
Working with Data in PowerPivot

  • Once imported Data View is the default view

  • Almost identical to Excel Pivot Table

  • Data is in memory

Diagram view
Diagram View

  • Allows youto

    • Create relationships

    • Create hierarchies

  • Work with the layout visually

Data view
Data View

  • Allows youto

    • View the data in a spreadsheet type layout

  • Add calculated column

  • DAX measures


  • Provide an easy means for users to navigate

  • Can consist of multiple columns

    • Examples Year>Month>Day

    • Fiscal Year>Fiscal Quarter>Fiscal Month

    • State>City>Zip code


  • Provides a focused subset of the Power Pivot project

  • Allows a means to focus attention on specific areas of a project

  • IS NOT a means of security

  • Similar to a view in SQL Server without the security boundary


  • Once created the project can be navigated using a pivot table

  • Provides all of the features and functionality of any PivotTable

    • Add Excel formulas


  • Can be created from within Power Pivot or Excel


  • Introduced in Excel 2010

  • Provides the ability to filter data with the click of a button

  • Easily configurable

  • Available for

    • Pivot tables

    • Pivot charts

    • When connecting to SSAS

      • Both tabular data model multidimensional cubes


  • DAX – Data Analysis Expressions

  • The expression language used by Power Pivot

  • Similar to Excel expressions, but more powerful

  • Used to

    • Create measures

    • Calculated columns

    • Query Power Pivot

  • PowerPivot DAX Survival Guide


  • A numeric calculation used in data analysis

  • Created using DAX in the Data View window

  • Measures are used to create KPI’s

  • Different from Calculated columns

    • Measures are typically used in the Values area of a PivotTable or PivotChart

    • Calculated columns are used when you want to place calculated results in a different area of a PivotTable


  • KPI – Key Performance Indicator

  • Based on a specific calculated measures

  • Provides a graphical representation of measures of performance


  • Follow along