Course Outline
Module 1: Modern Data Warehousing & Business Intelligence Fundamentals:
- Evolving Landscape of Data Warehousing (DW) and Business Intelligence (BI)
- Cloud-Native Data Warehousing (Azure Synapse Analytics, Azure SQL Data Warehouse)
- Modern Data Warehouse Architectures (Lambda Architecture, Kappa Architecture)
- Data Modeling Concepts (Star Schema, Snowflake Schema)
- Introduction to Data Vault methodology (brief overview)
- Key BI Concepts: ETL/ELT, OLAP, OLAP, DWH, Data Governance
- Overview of the Microsoft BI Stack: SQL Server (T-SQL, SSIS, SSAS, SSRS), Azure Synapse Analytics, Azure Analysis Services, Azure Data Factory, Power BI
Module 2: Modern ETL/ELT with SQL Server Integration Services (SSIS)
- SSIS Core Components (Integration Services, Connection Managers, Data Flow, Control Flow)
- Modern Data Access (ADO.NET, OLE DB, ODBC, Python Script Task)
- Cloud Integration (Loading/unloading data from/to Azure Blob Storage, Azure SQL Database/DW, Azure Data Lake Storage Gen2)
- Data Transformation Techniques (Derived Column, Lookup transformations, Aggregate transformations, Conditional Split, Script Component)
- Handling Big Data in SSIS (Integration with Azure Databricks, PolyBase)
- Error Handling, Logging, and Debugging in SSIS
- Deployment and Scheduling (SQL Agent, Azure Automation Runbooks)
Module 3: Building Analytical Models with SQL Server Analysis Services (SSAS - Tabular)
- Introduction to the Tabular Model (vs Multidimensional)
- DAX (Data Analysis Expressions) Language Fundamentals (Context, Calculations, Aggregations)
- Model Design: Relationships, Hierarchies, Perspectives, Roles, Security
- Using Time Intelligence Functions in DAX
- Managing and Deploying Tabular Models (BIML, SSDT)
- Performance Tuning SSAS Tabular Models
Module 4: Cloud Analytics with Azure Analysis Services (AAS)
- Introduction to Azure Analysis Services (AAS)
- AAS Deployment Options (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 (using Azure Purview or Azure Analysis Services Studio)
- Scaling and High Availability with AAS
- 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)
- DAX Deep Dive (Row Context vs Filter Context, Iterators, Time Intelligence, KPIs, Q&A)
- Combining T-SQL and DAX (PolyBase queries, linked servers)
- Using 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)
- Creating Effective Visualizations (Charts, Graphs, Maps)
- DAX for 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 & Hands-on Labs
- Advanced Data Transformation Patterns (Slowly Changing Dimensions, Type 1/2)
- Data Quality Services (DQS) Integration (overview)
- Performance Optimization and Troubleshooting (Query Store, Execution Plans)
- Extending BI Capabilities (Power Query, Power Automate)
- Hands-on labs covering end-to-end BI scenarios (ETL, Model Building, Reporting)
Requirements
Knowledge of Windows, basic knowledge of SQL and relational databases.
Custom Corporate Training
Training solutions designed exclusively for businesses.
- Customized Content: We adapt the syllabus and practical exercises to the real goals and needs of your project.
- Flexible Schedule: Dates and times adapted to your team's agenda.
- Format: Online (live), In-company (at your offices), or Hybrid.
Price per private group, online live training, starting from 3200 € + VAT*
Contact us for an exact quote and to hear our latest promotions
Testimonials (2)
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!
Deepthi - Invest Northern Ireland
Course - IBM Cognos Analytics
The adjustment made in the lecture/lessons by the trainer once he understood the current SSIS application that we are bound to maintain. The topics became more suitable/usable to us.
