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
No Reviews found for this course.