OptimaTrain

Practical Excel 2013: PowerPivot & Advanced Business Intelligence Tools for the Workplace (ILT)

0 STUDENTS ENROLLED

    COURSE DESCRIPTION

    This course takes up where the Optima Train Excel 2013 Pivot Tables and Pivot Charts course leaves off. It has two primary themes, each ultimately related to data analysis with pivot tables. First, it teaches you a number of methods for importing external data (from a database, text files, or other sources) into Excel for analysis. Second, it devotes considerable time to the PowerPivot add-in originally introduced with Excel 2010 and now included (with a new look) in Excel 2013.

    Category: Office Productivity/Microsoft

    Duration: 1-2 Days

    What are the requirements?

    • Microsoft Office 2013

    Prerequisites: Practical Excel 2013 Pivot Tables and Pivot Charts or equivalent knowledge

    What am I going to get from this course?

    • An understanding of relational databases, a great source for pivot tables, and how queries can be used to import the data you need
    • How to import data from various relational sources into Excel with Microsoft Query
    • An understanding of the relationship between the Excel Data Model and the PowerPivot add-in, as well as Microsoft’s marketing strategy in Excel 2013
    • An understanding of how the new PowerPivot add-in, as part of Microsoft’s “self-service BI” offerings, adds significant power to Excel’s existing data analysis tools
    • How to use the (revised) PowerPivot user interface for Excel 2013
    • How to import data into PowerPivot and how to use PowerPivot to create relationships between relatable tables
    • How to build pivot tables from PowerPivot data
    • How to use the DAX language in PowerPivot to create calculated columns and measures, and why DAX is so powerful
    • How to create named sets, hierarchies, and KPIs (key performance indicators) with PowerPivot
    • How PowerPivot is related to OLAP cubes
    • How the Power View add-in, new to Excel 2013, complements PowerPivot

    What is the target audience?

    • Any competent Excel users who want to be on the leading edge with Excel’s latest and most powerful data analysis tools

    Lesson Plan

    Introduction to Course
    Welcome to PowerPivot and Advanced BI Tools Course
    Importing Data into Excel
    Overview of Importing External Data into Excel
    Introduction to Relational Databases
    Introduction to OLAP
    Introduction to Queries
    Importing Access Data
    Importing Web Data
    Using Microsoft Query 1
    Using Microsoft Query 2
    Using the Pivot Table External Data Option
    Excel Data Model and PowerPivot
    Understanding the Excel Data Model Versus PowerPivot
    Advantages of the Excel Data Model Over Regular Excel Pivot Tables
    Building Pivot Tables with the Excel Data Model
    Advantages of the PowerPivot Add-In Over the Excel Data Model
    Migrating from PowerPivot 2010 to PowerPivot 2013
    Help in PowerPivot
    PowerPivot User Interface
    Importing Azure DataMarket Data
    Getting Data into PowerPivot
    Creating Relationships in PowerPivot
    Building Pivot Tables in PowerPivot
    More on Building Pivot Tables in PowerPivot
    Slicers in PowerPivot
    Working in Data View in PowerPivot
    DAX Language in PowerPivot
    Introduction to DAX in PowerPivot
    Learning about DAX Functions
    Creating Calculated Columns with DAX
    Using the RELATED and RELATEDTABLE Functions
    Whether to Denormalize an Excel Data Model
    Denormalizing with SQL Queries
    Creating Measures with DAX
    One Huge Benefit of DAX Measures
    Understanding Pivot Table Evaluation
    Many-to-Many Relationships in PowerPivot
    Counting Distinct Values in PowerPivot
    Using the CALCULATE Function 1
    Using the CALCULATE Function 2
    Using Date Functions in PowerPivot
    Other PowerPivot Tools
    Using a Parameter Table in PowerPivot
    Hierarchies in PowerPivot
    Named Sets in PowerPivot
    KPIs in PowerPivot
    Cube Functions in PowerPivot
    Power View
    Introduction to Power View
    Power View Data Model and User Interface
    Using Power View to Create Reports
    Conclusion to Course
    Conclusion to PowerPivot and Advanced BI Tools Course

    Course Reviews

    N.A

    ratings
    • 1 stars0
    • 2 stars0
    • 3 stars0
    • 4 stars0
    • 5 stars0

    No Reviews found for this course.