Course Outline
Module 1: Foundations of Modern Data Warehousing and Business Intelligence
- Shifting Trends in Data Warehousing (DW) and Business Intelligence (BI)
- Cloud-Native Data Warehousing Approaches (Azure Synapse Analytics, Azure SQL Data Warehouse)
- Contemporary Data Warehouse Architectures (Lambda Architecture, Kappa Architecture)
- Data Modeling Principles (Star Schema, Snowflake Schema)
- Introduction to Data Vault Methodology (summary overview)
- Core BI Concepts: ETL/ELT, OLAP, DWH, Data Governance
- Overview of the Microsoft BI Ecosystem: SQL Server (T-SQL, SSIS, SSAS, SSRS), Azure Synapse Analytics, Azure Analysis Services, Azure Data Factory, Power BI
Module 2: Implementing Modern ETL/ELT with SQL Server Integration Services (SSIS)
- Essential SSIS Components (Integration Services, Connection Managers, Data Flow, Control Flow)
- Modern Data Access Methods (ADO.NET, OLE DB, ODBC, Python Script Task)
- Cloud Integration Capabilities (Ingestion and export of data to and from Azure Blob Storage, Azure SQL Database/DW, Azure Data Lake Storage Gen2)
- Data Transformation Strategies (Derived Column, Lookup transformations, Aggregate transformations, Conditional Split, Script Component)
- Managing Big Data within SSIS (Integration with Azure Databricks, PolyBase)
- Error Handling, Logging, and Debugging Techniques in SSIS
- Deployment and Scheduling (SQL Agent, Azure Automation Runbooks)
Module 3: Developing Analytical Models with SQL Server Analysis Services (SSAS - Tabular)
- Overview of the Tabular Model (Comparison with Multidimensional)
- DAX (Data Analysis Expressions) Language Essentials (Context, Calculations, Aggregations)
- Model Architecture: Relationships, Hierarchies, Perspectives, Roles, Security
- Utilizing Time Intelligence Functions in DAX
- Managing and Deploying Tabular Models (BIML, SSDT)
- Performance Optimization for SSAS Tabular Models
Module 4: Executing Cloud Analytics with Azure Analysis Services (AAS)
- Introduction to Azure Analysis Services (AAS)
- AAS Deployment Models (PaaS - Azure App Service Plan, Dedicated Compute Instance)
- Connecting to Azure Databases (Azure Synapse Analytics, Azure SQL Database, Azure Analysis Services)
- Model Authoring in Azure (utilizing Azure Purview or Azure Analysis Services Studio)
- Scaling and Ensuring High Availability with AAS
- Security Framework in AAS (Role-Based Security)
Module 5: Querying and Analyzing Data using T-SQL and DAX
- Advanced T-SQL Techniques for Data Analysis (CTEs, Window Functions, PIVOT/UNPIVOT, MERGE)
- DAX In-Depth Analysis (Row Context vs Filter Context, Iterators, Time Intelligence, KPIs, Q&A)
- Integrating T-SQL and DAX (PolyBase queries, 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, Query Editor)
- Designing Effective Visualizations (Charts, Graphs, Maps)
- Applying DAX in Power BI (Calculated Columns, Measures)
- Report Design and Formatting in Power BI
- Introduction to Azure Synapse Studio for BI
Module 7: Course Review, Advanced Concepts & Practical Labs
- Advanced Data Transformation Patterns (Slowly Changing Dimensions, Type 1/2)
- Data Quality Services (DQS) Integration (summary overview)
- Performance Optimization and Troubleshooting (Query Store, Execution Plans)
- Extending BI Capabilities (Power Query, Power Automate)
- Practical Labs covering end-to-end BI scenarios (ETL, Model Building, Reporting)
Requirements
Familiarity with the Windows operating environment and fundamental understanding of SQL and relational database concepts.
Testimonials (2)
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!
