Summary
Keywords
Full Transcript
Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/AllFilesBI348Analytics.htm Learn about PowerPivot: Import Big Data, Build Data Model, Create Reports: 1) (00:04) Info about files for project 2) (00:20) Intro to Video and look at end result reports 3) (01:56) Overview of steps in building a Data Model in PowerPivot 4) (03:03) Look at source data files including text files with 5 million rows of records 5) (04:46) From Text Files, use Power Query to import 5 million rows of transactional sales data into the a Fact Table in the Data Model in PowerPivot 6) (09:48) From Excel file, use Power Query to import Product Retail and Standard Cost Dimension Table into the Data Model in PowerPivot 7) (10:52) Build Calendar Dimension Table in Excel and then use Power Query to import into the Data Model in PowerPivot 8) (13:44) From web get ISO 3166-1 County Code Data to build and then use Power Query to import Country Code Dimension Table into the Data Model in PowerPivot 9) (17:50) Build One-to-Many Relationships between Fact Table and Dimension Tables 10) (20:16) Build DAX Calculated Column for Net Revenue for each transaction. See the DAX functions: RELATED (Like VLOOKUP in Excel) and ROUND. 11) (22:30) Build DAX Measure (Calculated Field) for Total Net Revenue (Overall Total Net Revenue for entire Fact table). See the DAX function: SUM. 12) (24:16) Build PivotTable to see that the relationships are working and that we can pull fields from Dimension Tables and Fact Tables. 13) (24:16) Build PivotTable Report to show Net Revenue for each Country. 14) (25:33) “Trouble Shooting” Part of Example: Tracking Down Error between web site data and company data, including finding error and updating Country Code Excel Table and refreshing the linked table in the Data Model so that the PivotTable report updates and has correct results. 15) (30:35) Hide Fields from Field Lists using “Hide From Client Tools” 16) (31:59) Build PivotTable to see that with a Data Model PivotTable you can NOT group Dates 17) (33:20) Create DAX Calculated Column for Month Number and Month Name. See the DAX functions: MONTH and FORMAT (Like TEXT in Excel). 18) (35:20) Build PivotTable to see that Month Name does NOT sort correctly in a Data Model PivotTable. 19) (35:52) Learn how to Sort Month Name column by Month Number so that Month Name sorts correctly in a data Model PivotTable 20) (36:36) Build relationship between Calendar Table and Fact Table 21) (36:36) Build PivotTable Report to show Net Revenue by Month 22) (37:33) Create DAX Calculated Column for Year. See the DAX function: YEAR. 23) (38:02) Build PivotTable Report to show Net Revenue by Month & Year 24) (39:10) Build DAX Calculated Column for COGS for each transaction. See the DAX functions: RELATED (Like VLOOKUP in Excel) and ROUND. 25) (40:55) Build DAX Measure (Calculated Field) for Total COGS (Overall Total COGS for entire Fact table). See the DAX function: SUM. 26) (41:29) Build DAX Measure (Calculated Field) for Gross Profit using Measures (Calculated Fields) in our DAX formula. 27) (42:21) Build PivotTable Report to show Net Revenue, COGS and Gross Profit for each Year and Month. 28) (44:04) Build PivotTable Report to show Percentage Change for Net Revenue over Same Period Last Year. 29) (45:00) Build DAX Measure (Calculated Field) for Percentage Change over Same Period Last Year using the DAX functions: CALCULATE, SAMEPERIODLASTYEAR and IF. 30) (52:22) Build PivotTable Report to show Percentage of Grand Total for Each Product. Concept behind the formula. 31) (54:11) Build DAX Measure (Calculated Field) for Percentage of Grand Total using the DAX functions: CALCULATE and ALL. 32) (56:45) Refresh Reports when source data changes. In our example we bring 7 million rows into the Excel PowerPivot Data Model. 33) (57:48) Update Calendar table 34) (59:34) 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. More about CALCULATE function: https://www.youtube.com/watch?v=kMMohkVk8Ds PowerPivot Playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nGCx21PRFbsJpUIH06LKs-
Continue this lesson in the app
Install CourseHive on Android or iOS to keep learning while you move.
