O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

PowerPivot for Advanced Reporting and Dashboards

Book Description

Leverage the capabilities of the Microsoft Excel PowerPivot add-on to create advanced BI solutions

  • Learn how to create advanced BI solutions quickly and efficiently using easy-to-follow, step-by-step examples
  • Understand the key principles of PowerPivot with the help of real-time examples
  • Discover more about some interesting DAX functions with examples of how they work

In Detail

Business intelligence technology gives an organization the power to make decisions using large volumes of data. By obtaining an adequate amount of data and integrating data from a variety of sources, a user can get a comprehensive knowledge of their business and business strategies. PowerPivot is a free add-in to the 2010 version of the spreadsheet application MS Excel. It extends the capabilities of the PivotTable data summarization and cross-tabulation feature with new features such as expanded data capacity, advanced calculations, the ability to import data from multiple sources, and the ability to publish workbooks as interactive web applications.

PowerPivot for Advanced Reporting and Dashboards will teach you the fundamentals of PowerPivot as well as how to use the different data types available. This book also discusses useful tips and tricks for handling and resolving errors that might pop up while creating your report. With this book, you will be able to create relevant BI reports quickly and efficiently.

Moving on from the basics, this book will explain the types of data sources that can be imported into PowerPivot. You will then delve into relationships, hierarchies, and data model creation using imported data. You will also learn how to employ DAX functions to transform unstructured data into structured data. Finally, this book will teach you how to create reports such as Pivot Tables, Pivot Charts, Slicers, KPIs, and Perspective reports using PowerPivot and how to publish them using the SharePoint server.

Table of Contents

  1. PowerPivot for Advanced Reporting and Dashboards
    1. Table of Contents
    2. PowerPivot for Advanced Reporting and Dashboards
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers and more
        1. Why Subscribe?
        2. Free Access for Packt account holders
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    8. 1. Principles and Installing PowerPivot
      1. The principle of PowerPivot for Excel
      2. Benefits of using PowerPivot
      3. Installing PowerPivot
        1. Requirements (32-bit and 64-bit versions of Windows)
          1. Hardware requirements
          2. Software requirements
        2. Recommendations
        3. Getting the PowerPivot add-in for Excel 2010
        4. Downloading and installing the PowerPivot add-in for Excel 2010
        5. Enabling the PowerPivot add-in for Excel 2013
        6. Verifying installation
      4. Testing the PowerPivot add-in
      5. Summary
    9. 2. Preparation Analysis of Data Source
      1. List of data sources
        1. Relational database
        2. Multidimensional sources
        3. Data feeds
        4. Text files
      2. Purpose of importing data from a variety of sources
        1. General overview of ETL
          1. Examples
        2. PowerPivot performance in ETL
        3. Introducing Data Analysis Expressions (DAX)
      3. Adding a data source in a single worksheet from a variety of data sources
        1. E-commerce customer activity data importing
          1. Importing data from the SQL Server
          2. Importing data from a flat file
        2. Sales analysis data importing
          1. Importing data from the MS Access database
          2. Importing data using a SQL query
          3. Importing data based on copy and paste
          4. Importing data using an Excel-linked table
      4. Data refresh
      5. Summary
    10. 3. Data Model
      1. General overview of a data model
      2. A data model in PowerPivot
      3. Sales analysis data model design process
        1. Creating a data relationship for the sales analysis data
        2. Adding a new column using DAX for sales analysis data
        3. Creating hierarchies for sales analysis data
        4. Viewing the logical design data model of sales analysis
      4. E-commerce data model design process
        1. Splitting the User table for e-commerce data analysis
        2. Creating relationships
        3. Adding a new calculated column using DAX and creating hierarchies for e-commerce data
        4. Viewing the logically designed data model for e-commerce data analysis
      5. Summary
    11. 4. Business Intelligence Solutions Analysis
      1. A general overview of BI reports creation
        1. OLTP versus OLAP
        2. OLAP analyses
        3. The importance of reports
          1. Dashboards
          2. Business scorecards
            1. Key Performance Indicators (KPIs)
      2. Creating a report using the PowerPivot interface
        1. Creating simple reports in PowerPivot
        2. Creating a PivotTable
        3. Creating a PivotChart
        4. Creating a PivotTable with Slicers
        5. Creating PivotChart with Slicers
        6. PowerPivot Dashboards
        7. Creating key performance indicators
        8. Creating a KPI using a Perspective
      3. Summary
    12. 5. Publishing Reports Using the SharePoint Server
      1. PowerPivot for SharePoint
        1. Configuring PowerPivot for SharePoint
        2. Verifying the installation
          1. Types of users
      2. Deploying the reports in a PowerPivot site
      3. Summary
    13. Index