Data Analytics Certificate Program

Note: The Live Virtual course is presented in collaboration with CPA Western Provinces. The content is applicable to all participants. If you have questions regarding this course, please contact pdregistration@cpaalberta.ca.

Overview:

Do you want to progress beyond traditional Excel modelling to using Power Query and Power Pivot in Excel and/or Power BI? Learn to build reliable, interactive reports and dashboards that automate the extraction, transformation, loading, and analysis of data from multiple large and diverse data sources with just a click. 

CPAs face increasing demands for faster reporting and for rapid and flexible analysis of large and diverse data sets. Time-consuming and error-prone traditional Excel models with simple tabular data structures are no longer suitable to meet many of these requirements.  Learning Microsoft Power Query and Power Pivot (included in both Excel and Power BI) will enable rapid development, automation of updating reports/ analysis with new data, better structured and scalable data storage and interactive analysis. 
 
This saves CPAs valuable time and effort, reduces the risk of manual processing errors and can ensure more reliable and timely reporting and analysis.  Interactivity and drill down into the data enables rapid investigation and analysis to answer queries and resolve issues and enables the production of ‘self service’ models for users if required. 
 
The course will cover how to use the structured data model not just for tabular pivot reports and dashboards, but also how to extract and build free-format report layouts in Excel.  This enables users to combine the power of the same data engine as used in Power BI with the free format reporting flexibility of traditional Excel. 
 
The course will be taught around a hands-on case study starting from raw data and will cover a method and approach for users to apply what they learn to developing their own models. 

Agenda:

Note: To earn the Data Analytics Certificate, you must attend all the live sessions. 

Course Content:

In this course, you will gain knowledge about the following topics: 

• Apply a method and design rules to create effective, efficient, and reliable data models, reports and dashboards 
  
• Connect to data sources (including databases and folders with multiple files) using Power Query for data extraction, transformation, and loading. Record a script to extract and transform the desired data, load it into the data model or other queries, and then automate the process to run it with just a click, eliminating manual work 
  
• Design and build diverse data structures, from simple single tables to complex normalized multi-table models that ensure data integrity and ease of maintenance. Create key metrics and measures including some examples using DAX coding 
  
• Use data visualization and dashboards to enhance interpretation and insights, and structure dashboards for storytelling 
  
• Create free format reports in Excel using the Power Pivot data engine that are not limited to pivot table or pivot chart layouts 
  
• Apply the knowledge of Power Query and Power Pivot to build data models in Power BI Desktop 
  
• Troubleshoot common problems, reuse data queries in other models and develop strategies for tackling more complex models and problems
  

Who Will Benefit: 

This course is relevant for all CPAs and accounting professionals who want to build models for reporting/ analysis, particularly if they want to 

• Automate processing to remove the time and risks of manual processes 
  
• Connect to large/diverse data sets that may need to be combined/ transformed before they can be used 
  
• Enable interactive exploration and analysis of tabular data, visual data/ dashboards and free format layouts 
  
• Progress their skills in Excel and apply this to development in Power BI