Data Modelling With Power Pivot

Prerequisites

Basic knowledge of Microsoft Excel is essential with the following pre-requisites:

• Have attended Microsoft Excel Advanced Level; OR
• Any Pivot Table/Excel Reporting Classes
• Construct basic function using arithmetic Operators like +, -, * * /
• Able to Create simple lookup using VLOOKUP function
• Understand or use function like SUM, COUNT, AVERAGE, MAX, MIN, SUMIF, COUNTIF, AVERAGEIF, IF
• Visualize simple report into charts like column, line, pie & bar chart

Version Support - Excel 2016 Professional plus or Excel 365 (Power Pivot is located in File → Options → Add-Ins → Manage: COM addins → GO → Power Pivot)

Learning Outcomes / Benefits

Upon completion of this program, participants should be able to: Unlike the traditional pivot table and Excel sheets, user is limited to analyse data within a million row of data at a time, per table at a time (it is extremely slow). With the capability of Power Pivot, add ins of Business Intelligent engine to Excel, it has since supercharged your Excel into owning super power to analyst more than a million rows of data (safely 100 million rows). Creating reports by connection to relational data source (Multiple tables with relationship) created within excel using Power Pivot easily. With the fast performing processing engine, DAX (Data Analysis Expression) the functions used in Power Pivot allow users to customize any calculation whether to add in calculated column or measure faster than ever. Finally, all will be visualizing and with the help of slicer, participants will be able to turn the dashboard using slicer.

Key Content

Module 1 - Things You Should Know Before Getting Into Reporting

Lesson 1.1. What to do when a big data bomb drops onto your hand?

• Sorting, filtering and cleaning up data. Whoever are useful stays
• Say no to blank space, alignment and merge & centre. Why?
• Why keeping data consistent is important? Does it really matter?
• How your data should be arranged in order to be able to pivot.

Upon completion of this module, participants should be able to: How Excel behave when it comes to keeping too many unwanted, inconsistent and untidy data. Besides, they should also understand what tools are should be use to sort out and filter out useful data. Finally, they should learned what are the steps should be taken in order to create a report.

Module 2 - Building Your First Report With Pivot Table

Lesson 2.1. 3 Steps in creating a report

• Step 1: Converting range into a dynamic Table object
• Step 2: Inserting Pivot table
• Step 3: Understanding how to craft your report with row, column, value and filter field

Lesson 2.2. Facelift the report

• Adjusting the format displayed in the field
• Changing the way Value field summarizing the data
• Adding and removing Subtotal and Grad Total is easier than you think
• Trying out different layout for the report
• Styling report with different styles options

Lesson 2.3. Organizing and sort out useful info

• Filtering and sorting report by Values and Labels
• Grouping and ungrouping labels
• Refreshing report

Lesson 2.4. Using Pivot Table Value outside pivot table

• Using GetPivotData to highlight the important value outside the report

Lesson 2.5. Slicing and dicing data using Slicer and Timeline

• Inserting slicer using current report
• Setting up connection to slicer other report
• Hiding data with no values using slicer setting
• Insert timeline to view the report over a certain period

Upon completion of this module, participants should be able to: Extract information and construct a report using the most powerful feature in Excel. Summarizing large data into useful to they can have the overview. This enable them to and compare the expenses over the year or top sales product and others. Finally, user will be able to view a report from different angles and perspective using slicer within few clicks.

Module 3 - Visualizing Reports Using Pivot Chart

Lesson 3.1. Displaying the trends and comparison of data with Pivot Chart

• Converting report into a Pivot Chart
• Changing Chart type

Lesson 3.2. Formatting Pivot Charts

• Hiding unhiding field button in Pivot Chart
• Turning static chart title into dynamic chart title
• Changing the formats and the value display in label axis
• Labelling charts with its value

Upon completion of this module, participants should be able to: Visualize the pivot report using Pivot Chart. Pivot chart allow them to have the overview of the pivot report and it is a great way if they want to compare the value by region or month. Not only that, Pivot Chart allows user to take a look the projected and actual costing or KPI from one period to another. Decision making become easier when the big data is being crunch and visualize using Pivot Chart.

Module 4 - Ice Breaking Session With PowerPivot

Lesson 4.1. What is Power Pivot?

• Power Pivot 101
• Difference in between Power Pivot and Pivot Table
• Who needs Power Pivot

Lesson 4.2. Get your Power Pivot

• Software and hardware requirement for PowerPivot
• Step-by-step enabling Power Pivot
• Compatibility problem with the Power Pivot from previous version

Upon completion of this module, participants should be able to: Enable one of the powerful analysis tools in Excel PowerPivot and understand who and why should you use Power Pivot while analysing data. Finally, they should have more understanding with Power Pivot and not getting confuse Power Pivot with Pivot Table.

Module 5 - Where Should You Begins With Power Pivot

Lesson 5.1. Importing data into Data Model thru Excel

• Link data model from Excel Table
• Copy and paste data into Data Model
• Paste Append & Paste Replace

Lesson 5.2. Importing data from delimited file

• Import data from text file
• Import data from csv file

Lesson 5.3. Importing data from database file

• Load data from Access into Data Model

Lesson 5.4. Talk about relationship

• Import data from a relational Database
• Create relationship between tables with Diagram View

Upon completion of this module, participants should be able to: Import data from various sources, from excel spreadsheet to text files and relational database. Connecting all the related tables using relationship, using the shortest and simplest way to link up the tables. With the relationship created in data model, it eliminates the need of using the tedious and high maintenance Vlookup to extract information. Lastly, participants will be able to relate the benefits of link up tables using relationship instead of Vlookup to avoid unnecessary errors caused by it.

Module 6 - Adding Calculated Column Using Existing Information From Tables

Lesson 6.1. Inserting Calculated Column

• Using simple arithmetic operation to create calculated column

Lesson 6.2. Using DAX function

• Summarizing data with SUM, COUNT, AVERAGE, MAX & MIN
• Get the unique count with DISTINCTCOUNT function
• Using RELATED function to extract data
• Using CALCULATE function to summarize data with different filters
• Using FORMAT, DAY, MONTH & YEAR function to extract date
• Workaround with ROUNDUP, IF to extract fiscal year and quarters

Lesson 6.3. Define hierarchy

• Create hierarchy
• Editing and removing hierarchy
• Use hierarchy in Reports to create a drill down effects

Upon completion of this module, participants should be able to: Differentiate the differences in between Calculate Column and Measure. User will be able to use DAX function to summarize the relational data in Power Pivot. Besides, user gets to enjoy the fast calculation with DAX function especially when the data model involves more than a million rows of data. Finally with calendar table construct using DAX function allow user to create reports group by date(by year/ fiscal year/ quarters/ months/ day).

Module 7 - It’s Time To Put Everything Together. Power Pivot And Pivot Table.

Lesson 7.1. Create Reports by connecting to External Data Source

• Import relational data source from Power Pivot to Pivot Table
• Create a report from multiple tables

Lesson 7.2. Visualize Report

• Transform all reports into Pivot charts
• Turn the visualization into a dynamic Dashboard using Slicer

Lesson 7.3. Say no more to reporting routine

• Refreshing new data source
• Refreshing dashboard by one click

Upon completion of this module, participants should be able to: Summarize and visualize everything using Pivot Table by connecting the data source to Power Pivot. User finally can create a report by drag and drop fields from multiple tables from data model without having the heaving processing from VLOOKUP. With this feature, user will no longer need to create report from period to period. With “one click refresh” capability, they get to view dashboard base on latest updated data.

Target Audience

This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.

Methodology

This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise.