Course Outline
Module 1: Fundamentals of Modern Data Warehousing and Business Intelligence:
- Understanding the shifting landscape of Data Warehousing (DW) and Business Intelligence (BI)
- Exploring Cloud-Native Data Warehousing solutions (Azure Synapse Analytics, Azure SQL Data Warehouse)
- Examining Modern Data Warehouse Architectures (including Lambda and Kappa architectures)
- Mastering Data Modeling Concepts (Star Schema and Snowflake Schema)
- Gaining a brief overview of Data Vault methodology
- Grasping Key BI Concepts: ETL/ELT, OLAP, DWH, and Data Governance
- Surveying the Microsoft BI Stack: SQL Server (T-SQL, SSIS, SSAS, SSRS), Azure Synapse Analytics, Azure Analysis Services, Azure Data Factory, and Power BI
Module 2: Implementing Modern ETL/ELT with SQL Server Integration Services (SSIS)
- Exploring SSIS Core Components (Integration Services, Connection Managers, Data Flow, and Control Flow)
- Utilizing Modern Data Access methods (ADO.NET, OLE DB, ODBC, and Python Script Tasks)
- Achieving Cloud Integration (loading and unloading data between Azure Blob Storage, Azure SQL Database/DW, and Azure Data Lake Storage Gen2)
- Applying Data Transformation Techniques (Derived Column, Lookup, Aggregate transformations, Conditional Split, and Script Components)
- Managing Big Data within SSIS (integration with Azure Databricks and PolyBase)
- Implementing Error Handling, Logging, and Debugging procedures in SSIS
- Handling Deployment and Scheduling via SQL Agent and Azure Automation Runbooks
Module 3: Developing Analytical Models with SQL Server Analysis Services (SSAS - Tabular)
- Introduction to the Tabular Model (contrasted with Multidimensional models)
- Fundamentals of the DAX (Data Analysis Expressions) language (covering Context, Calculations, and Aggregations)
- Model Design Strategies: Relationships, Hierarchies, Perspectives, Roles, and Security
- Utilizing Time Intelligence Functions in DAX
- Managing and Deploying Tabular Models (using BIML and SSDT)
- Optimizing Performance of SSAS Tabular Models
Module 4: Cloud Analytics with Azure Analysis Services (AAS)
- Introduction to Azure Analysis Services (AAS)
- Understanding AAS Deployment Options (PaaS - Azure App Service Plan, Dedicated Compute Instance)
- Connecting to Azure Databases (Azure Synapse Analytics, Azure SQL Database, Azure Analysis Services)
- Performing Model Authoring in Azure (using Azure Purview or Azure Analysis Services Studio)
- Ensuring Scalability and High Availability with AAS
- Implementing Security in AAS (Role-Based Security)
Module 5: Querying and Analyzing Data with T-SQL and DAX
- Advanced T-SQL for Data Analysis (CTEs, Window Functions, PIVOT/UNPIVOT, MERGE)
- In-depth DAX exploration (Row Context vs Filter Context, Iterators, Time Intelligence, KPIs, and Q&A)
- Integrating T-SQL and DAX (using PolyBase queries and linked servers)
- Leveraging AI-Enhanced Analytics (Azure Synapse Analytics Machine Learning Services)
Module 6: Data Discovery and Visualization
- Introduction to Power BI (connecting to data sources, utilizing the Query Editor)
- Designing Effective Visualizations (Charts, Graphs, Maps)
- Applying DAX in Power BI (Creating Calculated Columns and Measures)
- Report Design and Formatting techniques in Power BI
- Introduction to Azure Synapse Studio for BI
Module 7: Course Review, Advanced Concepts & Hands-on Labs
- Mastering Advanced Data Transformation Patterns (Slowly Changing Dimensions, Type 1/2)
- Overview of Data Quality Services (DQS) Integration
- Optimizing Performance and Troubleshooting (using Query Store and Execution Plans)
- Extending BI Capabilities (Power Query, Power Automate)
- Participating in Hands-on labs covering end-to-end BI scenarios (ETL, Model Building, Reporting)
Requirements
Familiarity with the Windows operating system and foundational understanding of SQL and relational databases.
Testimonials (3)
personalised to our understanding and data
Vincent Long - ASSMANG PTY LTD
Course - Business Intelligence with SSAS
Abhi has excellent knowledge of Alteryx and he explained things very clearly. He understood our goals and created bespoke demo datasets that were relevant to our organisation, which was very impressive. The training was well-structured and delivered at a good pace, with time for questions.
Samuel Taylor - Manchester Metropolitan University
Course - Alteryx for Data Analysis
Deepthi was super attuned to my needs, she could tell when to add layers of complexity and when to hold back and take a more structured approach. Deepthi truly worked at my pace and ensured I was able to use the new functions /tools myself by first showing then letting me recreate the items myself which really helped embed the training. I could not be happier with the results of this training and with the level of expertise of Deepthi!
