Microsoft Power BI Desktop Playlist of Videos

Currently Playing: MSPTDA 21: Power Query: Reduce Data Model Size, Transformations to Columnar Database Size

All 4 files used in video and homework are available in this zipped folder: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DataModeling/MSPTDA-21-DownloadAllFiles.zip Download file individually at class web site: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DataModeling/DataModeling.htm pfd notes for Video #21: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DataModeling/021-MSPTDA-ReduceDataModelSize.pdf 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: https://data.seattle.gov/Public-Safety/Call-Data/33kz-ixgy 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