You are previewing Microsoft Excel 2013: Building Data Models with PowerPivot.
O'Reilly logo
Microsoft Excel 2013: Building Data Models with PowerPivot

Book Description

Your guide to quickly turn data into results.

Transform your skills, data, and business—and create your own BI solutions using software you already know and love: Microsoft Excel. Two business intelligence (BI) experts take you inside PowerPivot functionality for Excel 2013, with a focus on real world scenarios, problem-solving, and data modeling. You'll learn how to quickly turn mass quantities of data into meaningful information and on-the-job results—no programming required!

  • Understand the differences between PowerPivot for Self Service BI and SQL Server Analysis Services for Corporate BI

  • Extend your existing data-analysis skills to create your own BI solutions

  • Quickly manipulate large data sets, often in millions of rows

  • Perform simple-to-sophisticated calculations and what-if analysis

  • Create complex reporting systems with data modeling and Data Analysis Expressions

  • Share your results effortlessly across your organization using Microsoft SharePoint

  • Authors’ note on using Microsoft Excel 2016: This book’s content was written against Excel 2013, but it is useful and valid for users of Excel 2016 too. Excel 2016 introduces several new DAX functions and an improved editor for DAX without changing any existing behavior. In other words, all of the concepts and examples explained in this book continue to work with Excel 2016.

    Table of Contents

    1. Special Upgrade Offer
    2. A Note Regarding Supplemental Files
    3. Introduction
      1. Who this book is for
      2. Assumptions about you
      3. Organization of this book
      4. Conventions
      5. About the companion content
      6. Acknowledgments
      7. Support and feedback
        1. Errata
        2. We Want to Hear from You
        3. Stay in Touch
    4. 1. Introduction to PowerPivot
      1. Using a PivotTable on an Excel table
      2. Using PowerPivot in Microsoft Office 2013
        1. Adding information to the Excel table
        2. Creating a data model with many tables
        3. Understanding relationships
      3. Understanding the data model
        1. Querying the data model
      4. The PowerPivot add-In
      5. Using OLAP tools and converting to formulas
      6. Understanding PowerPivot for Excel 2013
      7. Creating a Power View report
    5. 2. Using the unique features of PowerPivot
      1. Loading data from external sources
        1. Creating a PowerPivot PivotTable
      2. Using the DAX language
        1. Creating a calculated column
        2. Creating a calculated field
        3. Computing complex aggregations like Distinct Count
      3. Refreshing the PowerPivot data model
    6. 3. Introducing DAX
      1. Understanding DAX calculations
        1. DAX syntax
        2. DAX data types
        3. DAX operators
        4. DAX values
      2. Understanding calculated columns and fields
        1. Calculated columns
        2. Calculated fields
          1. Choosing between calculated columns and measures
      3. Handling errors in DAX expressions
        1. Conversion errors
        2. Arithmetical operations
          1. Empty or missing values
        3. Intercepting errors
      4. Formatting DAX code
      5. Common DAX functions
        1. Aggregate functions
        2. Logical functions
        3. Information functions
        4. Mathematical functions
        5. Text functions
        6. Conversion functions
        7. Date and time functions
        8. Relational functions
      6. Using basic DAX functions
    7. 4. Understanding data models
      1. Understanding the basics of data modeling
        1. Producing a report without a data model
        2. Building a data model
      2. More about relationships
      3. Understanding normalization and denormalization
      4. Denormalizing within SQL queries
        1. The PowerPivot query designer
        2. When to denormalize tables
      5. Understanding over-denormalization
      6. Understanding OLTP and data marts
        1. Querying the OLTP database
        2. Data marts, facts, and dimensions
        3. Star schemas
        4. Which database is the best to query?
      7. Using advanced relationships
    8. 5. Publishing to SharePoint
      1. SharePoint 2013 and PowerPivot integration
      2. Licensing and setup
      3. Publishing a workbook to SharePoint
      4. Using the PowerPivot Gallery
      5. Connecting Excel to a SharePoint Excel data model
      6. Creating a Power View report
      7. Managing the PowerPivot data refresh
    9. 6. Loading data
      1. Understanding data sources
      2. Loading from a database
        1. Loading from a list of tables
        2. Loading relationships
        3. Selecting related tables
        4. Loading from a SQL query
        5. Loading from views
      3. Opening existing connections
      4. Loading from Access
      5. Loading from SQL Server Analysis Services
        1. Using the MDX editor
        2. Handling of keys in the OLAP cube
        3. Loading from a tabular database
      6. Loading from SharePoint
      7. Using linked tables
      8. Loading from Excel files
      9. Loading from text files
      10. Loading from the Clipboard
      11. Loading from a report
      12. Loading from a data feed
      13. Loading from Windows Azure Marketplace
        1. Suggest related data
      14. Refreshing connections
    10. 7. Understanding evaluation contexts
      1. Introduction to evaluation contexts
        1. Understanding the row context
      2. Testing your evaluation context understanding
        1. Using <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>SUM</em></span> in a calculated column in a calculated column
        2. Using fields in a calculated field
      3. Creating a row context with iterators
      4. Understanding <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>FILTER, ALL</em></span>, and context interactions, and context interactions
      5. Working with many tables
        1. Row contexts and relationships
        2. Filter context and relationships
        3. Introducing <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>VALUES</em></span>
        4. Introducing <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>ISFILTERED</em></span> and and <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>ISCROSSFILTERED</em></span>
      6. Evaluation contexts recap
      7. Creating a parameter table
    11. 8. Understanding CALCULATE
      1. Why is <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>CALCULATE</em></span> needed? needed?
      2. <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>CALCULATE</em></span> examples examples
        1. Filtering a single column
        2. Filtering with complex conditions
      3. Using <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>CALCULATE</em></span> inside a row context inside a row context
      4. Understanding circular dependencies
      5. <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>CALCULATE</em></span> rules rules
      6. Understanding <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>ALLSELECTED</em></span>
    12. 9. Using hierarchies
      1. Understanding hierarchies
        1. When to build hierarchies
        2. Building hierarchies
        3. Creating hierarchies on multiple tables
        4. Performing calculations using hierarchies
      2. Using parent/child hierarchies
    13. 10. Using Power View
      1. What is Power View?
      2. Power View basics
        1. Using the Filters pane
        2. Decorating your report
      3. Understanding table, matrix, and cards
        1. Using the matrix visualization
        2. Using the card visualization
        3. Using a table as a slicer
      4. Using charts
        1. Using the line chart
        2. Using the pie chart
        3. Using the scatter chart
      5. Using maps
      6. Understanding drill-down
      7. Using tiles
      8. Understanding multipliers
      9. Using Power View effectively
    14. 11. Shaping the reports
      1. Key Performance Indicators (KPIs)
      2. Creating data models for Power View
      3. Understanding Power View metadata
        1. Using Summarize By
        2. Using the default field set
        3. Using the Table Behavior dialog box
      4. Defining sets
      5. Creating dynamic sets with MDX
      6. Using perspectives
      7. Understanding drill-through
    15. 12. Performing date calculations in DAX
      1. Building a calendar table
        1. Working with multiple calendar tables
        2. Calculating working days
        3. Computing the difference in working days
      2. Aggregating and comparing over time
        1. Year-to-Date (YTD), Quarter-to-Date (QTD), and Month-to-Date (MTD)
        2. Time intelligence with <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>CALCULATE</em></span>
        3. Computing periods from the prior year (PY)
        4. Computing the moving annual total
        5. Using other aggregation functions
        6. Computing difference over a previous year
      3. Closing balance over time
        1. Semiadditive measures
        2. <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>OPENINGBALANCE</em></span> and and <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>CLOSINGBALANCE</em></span> functions functions
        3. Updating balances by using transactions
      4. Computing moving averages
    16. 13. Using advanced DAX
      1. Banding
      2. Ranking
      3. Using many-to-many relationships
      4. Computing new and returning customers
      5. Understanding <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>KEEPFILTERS</em></span>
      6. Implementing basket analysis
      7. Understanding the power of calculated columns: ABC analysis
      8. Handling currency conversion
    17. 14. Using DAX as a query language
      1. Understanding <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>EVALUATE</em></span>
      2. Creating an Excel table with <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>EVALUATE</em></span>
      3. Using common functions in queries
        1. Using <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>FILTER</em></span>
        2. Using <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>CALCULATETABLE</em></span>
      4. Using <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>ADDCOLUMNS</em></span>
        1. Using <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>VALUES</em></span> with with <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>ADDCOLUMNS</em></span>
      5. Using <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>SUMMARIZE</em></span>
        1. Using the <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>ROLLUP</em></span> option option
      6. Linking back a DAX query
      7. Computing ABC analysis with a linked-back table
      8. Using <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>CROSSJOIN</em></span>
      9. Using <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>GENERATE</em></span>
      10. Querying with DAX Studio
    18. 15. Automating operations using VBA
      1. Enabling the DEVELOPER tab of the ribbon
      2. Updating a linked-back DAX query through VBA
      3. Using the <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>Model</em></span> object object
      4. Importing data into the data model using VBA
      5. Understanding data connections
    19. 16. Comparing Excel and SQL Server Analysis Services
      1. Understanding the different versions of the engine
      2. Feature matrix
      3. Securing your data
      4. Programmability and flexibility
      5. Translations
      6. Database size
      7. Number of databases
      8. PowerPivot as a prototyping system
    20. A. About the Authors
    21. Index
    22. About the Authors
    23. Special Upgrade Offer
    24. Copyright