All 4 files used in video and homework are available in this zipped folder: http://bit.ly/2JnYkyT
Download file individually at class web site: http://bit.ly/2GF25Ni
pfd notes for Video #21: http://bit.ly/2JfGAW1
In this video learn key strategies and Power Query Transformation Methods for reducing the size of your Data Model, or Columnar Database or File size. This is an important part of Data Analysis and Business Intelligence when you have Big Data because it can help your solution to run more efficiently by creating smaller Data Model behind the scenes Columnar Databases that must be loaded into RAM. Apply concepts of how the Columnar Database stores data when deciding what tables, columns and transformations should be part of the final Data Model.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:15) Introduction
2. (01:11) Download HUGE CSV File.
3. (01:34) Talk about How Columnar Database works and how we can reduce size.
4. (03:11) Rules for Efficient Data Modeling
5. (06:07) Get Data From Government Web Site: http://bit.ly/2IYfRht
6. (07:18) Import CSV Text File
7. (07:46) Load Entire CSV File and look at Model Size.
8. (09:20) Filter Rows and look at Model Size.
9. (10:56) Remove Columns and look at Model Size.
10. (11:40) Reduce Cardinality of Columns with 1) Add Column for Time
11. (13:59) Reduce Cardinality of Columns with 2) Transform Columns and get Date Only
12. (15:21) Edit Time Column to Create only Hour and reduce size further.
13. (16:39) Edit Query can lead to trouble in subsequent steps. See how to fix the problem.
14. (17:28) Add Date Table
15. (18:28) Create Relationship
16. (18:44) Create Measure to Count Rows for Call Type. Use COUNTROWS DAX Function.
17. (19:16) Create Dashboard
18. (19:16) Create Pivot Table
19. (20:17) Filter to show Top 20, 911 Call Types
20. (20:45) Show Values As in Data Model PivotTable
21. (22:07) Sort Measure Biggest to Smallest
22. (22:15) Add Slicers
23. (23:05) Scrape Data form Web Site
24. (24:18) Summary