Summary
Keywords
Full Transcript
Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/AllFilesBI348Analytics.htm This videos is an introduction to Building Data Models in PowerPivot. For people who have used VLOOKUP and Helper Columns to prepare data tables for PivotTables and who want to learn what building Data Models in PowerPivot is all about: This is the perfect videos for you. We will have a PowerPoint Introduction to what PowerPivot and Data Models are, then we will remind ourselves how we build Data Models in Excel all the while comparing what we are used to doing in Excel and how it will change when we get to PowerPivot, then we will build a simple Data Model in PowerPivot to get an introduction before we move on to our next video where we will learn how to build a Big Data Data Model in PowerPivot. From Excel Data Models to Big Data PowerPivot Data Models Topics in Video: 1) (00:16) Introduction to building Data Models with PowerPivot 2) (00:55) Topics in this video 3) (02:05) What is PowerPivot? 4) (03:21) What versions of Excel have PowerPivot? Notes for Excel 2013 and Excel 2016 5) (03:54) What is the Data Model? 6) (04:37) What DAX stands for (Data Analysis Expressions) 7) (06:05) What is Data Modeling? 8) (07:38) What are Related Tables? 9) (08:55) References for more advanced PowerPivot and Data Modeling 10) (09:23) Where to find notes in the downloadable PowerPoint about DAX formulas and Calculated Columns and Measures (Calculated Fields) 11) (09:40) IMPORTANT if you are NEW to PowerPivot: Remind ourselves how we build Data Models in Excel all the while comparing what we are used to doing in Excel and how it will change when we get to PowerPivot 12) (15:46) Step 1 in Building Data Model: Import Proper data sets into PowerPivot Data Model: 1) Convert tables to Excel Tables (Ctrl + T and Alt, J, T, A), 2) Click Add to Data Model button in PowerPivot Ribbon Tab. 13) (17:41) Intro to Manage Data Model window 14) (20:17) Step 2 in Building Data Model: Build Relationships using Diagram View. Simply drag and drop fields from dimension table to fact table. 15) (22:07) Step 3 in Building Data Model: Build DAX Calculated Columns 16) (22:58) DAX function: RELATED (substitute for VLOOKUP) 17) (23:23) Introduction to Table Names and Field Names (Field name in square brackets) in DAX formulas 18) (23:45) How formulas are copied in a Calculated Column 19) (24:21) Convention for building DAX formulas: 1) When you use a column name (field name) in a formula use both Table Name AND Field Name, when you use a Measure (Calculated Field) use only the Measure Name in Square Brackets. 20) (26:10) DAX function: ROUND 21) (26:30) Row Context for DAX Calculated Columns 22) (27:26) Example of Implicit Formula in a PowerPivot Data Model PivotTable. Why we should not use Implicit Formulas. 23) (30:02) Step 3 in Building Data Model: Build DAX Calculated Columns and Measures (Calculated Fields). This will not be an Implicit Formula. It will be an Explicit Formula. 24) (31:11) The Implicit “Sum of Net revenue” Formula shows up in Formula Field Dropdown List: DO NOT use it! 25) (31:51) Add Number Formatting to our Measures (Calculated Fields). 26) (32:05) Create PivotTable Report using our new DAX Measures (Calculated Fields). 27) (32:38) Filter Context for DAX Measures (Calculated Fields). 28) (32:58) Why we should not use Implicit Formulas AND Why we should use Explicit Formulas AND why DAX Measures (Calculated Fields) Calculate quickly on Big Data: Filter Context cause DAX Measures (Calculated Fields) to filter the underlying table so it has to process on a smaller table than the full-unfiltered table. 29) (34:59) Summary and Conclusion Download Excel File Not: After clicking on link, Use Ctrl + F (Find) and search for “Highline BI 348 Class” or for the file name as seen at the beginning of the video.
Continue this lesson in the app
Install CourseHive on Android or iOS to keep learning while you move.
