Course ID: CLBIPP019

Introducing PowerPivot

  • Accessing the PowerPivot add-in
  • Navigating the interface
  • Loading a single data source
  • Creating a dashboard

Employing PowerPivot in the BI process

  • Extracting information from data with PowerPivot
  • Uncovering data interpretation issues

Building the Data Model with PowerPivot

  • Defining a consolidated view of data
    • Generating a data mashup from structured and unstructured data sources into a data model
    • Deriving relationships from data sources with the Relationships tool and the Diagram View
  • Denormalizing data to simplify usage within other BI reporting tools
    • Acquiring data from related tables
    • Defining calculated columns
    • Consolidating information available to BI tools
  • Querying SQL Server data
    • Designing queries to import data from SQL Server
    • Relating tables with outer joins
  • Fixing common data issues with Power Query
    • Extracting, Transforming and Loading (ETL) data
    • Converting data formats with Power Query steps
    • Parsing columns to aid analysis
    • Removing duplicates from a data set
    • Constructing a single data set from multiple sources with the same field headings

Manipulating and Analyzing Data with the Data Analysis eXpressions (DAX) Language

  • Defining calculated fields for business performance
    • Distinguishing the role of calculated fields
    • Translating key business concepts into calculated fields
    • Providing context for calculated fields within a PivotTable
    • Determining between implicit and explicit calculated fields
  • Implementing DAX functions in PowerPivot
    • Expressing information with calculated fields
    • Exposing hidden information from data
    • Troubleshooting and debugging DAX calculations
  • Exploiting data analytics with aggregation
    • Quantifying and mining information with DAX functions
    • Summarizing and aggregating data from other tables with the X functions
    • Evaluating expressions with the CALCULATE() function and filter functions
    • Substituting values with the SWITCH() function
  • Mining for information with date and time analysis
    • Grouping dates for time analysis
    • Comparing and categorizing time periods with Time Intelligence functions
  • Setting key business targets with KPIs
    • Analyzing performance with calculated fields
    • Gauging performance against goals

Presenting Information with Dashboards

  • Articulating and analyzing data
    • Drilling down into data using a hierarchy
    • Managing data with perspectives
    • Identifying patterns and trends in your PowerPivot data with Power View charts
    • Classifying data into different geographical regions
  • Designing effective dashboards
    • Contextualizing calculated fields with PivotTable slicers and Power View filters
    • Documenting structures with Hierarchical Diagrams
    • Visualizing and comparing performance matrices with Power View multipliers
    • Globalizing location-based results to identify trends and patterns on a 3D scale with Power Map
  • Creating Team BI Solutions
    • Defining the requirements for a team solution
    • Coordinating results with team members
    • Sharing a PowerPivot solution

    [accordion id="faq"]
    [accordion_item in="true" id_parent="faq" title="What is PowerPivot for Excel?"]
    PowerPivot for Excel is a graphical Extract, Transform and Load (ETL) add-in that was introduced in Excel 2010 to help the Excel user to consume large volumes of data as well as data from multiple sources such as Oracle, SQL Server, Access and other databases.
    [accordion_item in="false" id_parent="faq" title="What is this course about?"]
    In the past, business intelligence solutions have required complex technical tools. In this course, you will learn how to use the PowerPivot tools and features in Excel to mine large amounts of sophisticated data to provide better business insights and empower informed decisions. Additionally, you will learn how to employ PivotTables, Power View, PivotCharts and Power Maps to present key information to senior management via dashboards, enabling them to see a complete picture of their organization.
    [accordion_item in="false" id_parent="faq" title="Who will benefit from this course?"]
    This course is valuable for data analysts, business analysts, financial analysts, managers, business intelligence professionals and anyone who wants to better understand data derived from multiple sources and/or large volumes of data using PowerPivot for Excel.
    [accordion_item in="false" id_parent="faq" title="What background do I need?"]
    Experience with Excel at the level of Course 195, Power Excel®: Analyzing Data to Make Business Decisions, is assumed. You should have a solid knowledge of the following:

    • Applying conditional formatting
    • Using and implementing functions such as IF, VLOOKUP, SUM and AVERAGE
    • Creating, modifying and formatting PivotTables and PivotCharts
    • Grouping dates in PivotTables and PivotCharts
    • Filtering data with slicers

    [accordion_item in="false" id_parent="faq" title="What version of Excel is used in this course?"]
    This course will cover everything you need to know about PowerPivot whether you use Excel 2010 or 2013. The hands-on exercises in this course are conducted in Excel 2013. PowerPivot does not run on versions of Excel prior to Excel 2010.[/accordion_item]
    [accordion_item in="false" id_parent="faq" title="What is DAX?"]
    The Data Analysis eXpressions (DAX) language provides functions to extend and refine data within the PowerPivot tool. Many of the functions are similar to Excel functions.
    [accordion_item in="false" id_parent="faq" title="I'm interested in creating BI solutions with SharePoint. Will I benefit from this course?"]
    Yes! This course empowers the Excel user to create individual and team BI solutions with Excel. If you are interested in a SharePoint BI solution, see Course 146, SharePoint®2010 Business Intelligence: Hands-On, to learn how to produce an enterprise solution using SharePoint.
    [accordion_item in="false" id_parent="faq" title="If I learn PowerPivot for Excel, can that knowledge be applied to PowerPivot for SharePoint?"]
    Yes! PowerPivot for SharePoint also includes DAX and provides all of the same functionality on a SharePoint server rather than on the workstation. [/accordion_item]
    [accordion_item in="false" id_parent="faq" title="Are BI tools for Excel covered in this course?"]
    Yes! Power View, Power Query and Power Map are all covered in this course
    [accordion_item in="false" id_parent="faq" title="How much time is devoted to the hands-on exercises?"]
    More than 50 percent of class time is spent on hands-on exercises. These exercises are designed to provide you with practical, real-world experience using the Excel features in an efficient and effective manner. Hands-on exercises include:

    • Loading data from multiple data sources into PowerPivot
    • Merging and consolidating data to build a data model
    • Summarizing and aggregating data with DAX functions
    • Defining Measures and KPIs
    • Representing strategic PowerPivot data and KPIs in a dashboard

    [accordion_item in="false" id_parent="faq" title="Build Agents with Event Studio (Optional)"]

    • Create agents
    • Use calculations and parameters in Event Studio
    • Add tasks (email, report, database update, agent)
    • Manage task execution rules
    • Test an event through its lifecycle
    • Schedule an agent


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>