Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Course Outline
Module 1: Fundamentals of Modern Data Warehousing and Business Intelligence:
- Understanding the evolving landscape of Data Warehousing (DW) and Business Intelligence (BI)
- Cloud-native data warehousing solutions (Azure Synapse Analytics, Azure SQL Data Warehouse)
- Architectural patterns for modern data warehouses (Lambda Architecture, Kappa Architecture)
- Data modeling principles (Star Schema, Snowflake Schema)
- Introduction to Data Vault methodology (brief overview)
- Core BI concepts: ETL/ELT, OLAP, DWH, and Data Governance
- Survey of the Microsoft BI ecosystem: SQL Server (T-SQL, SSIS, SSAS, SSRS), Azure Synapse Analytics, Azure Analysis Services, Azure Data Factory, and Power BI
Module 2: Modern ETL/ELT Processes with SQL Server Integration Services (SSIS)
- Core SSIS components (Integration Services, Connection Managers, Data Flow, Control Flow)
- Contemporary data access methods (ADO.NET, OLE DB, ODBC, Python Script Task)
- Cloud integration capabilities (Loading and unloading data to/from 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)
- Managing Big Data within SSIS (Integration with Azure Databricks, PolyBase)
- Error handling, logging, and debugging strategies in SSIS
- Deployment and scheduling mechanisms (SQL Agent, Azure Automation Runbooks)
Module 3: Developing Analytical Models with SQL Server Analysis Services (SSAS - Tabular)
- Introduction to the Tabular Model (comparison with Multidimensional)
- Fundamentals of the DAX (Data Analysis Expressions) language (Context, Calculations, Aggregations)
- Model design elements: Relationships, Hierarchies, Perspectives, Roles, and Security
- Applying Time Intelligence functions in DAX
- Managing and deploying Tabular Models (BIML, SSDT)
- Performance tuning for SSAS Tabular Models
Module 4: Cloud Analytics via 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 the Azure cloud (utilizing Azure Purview or Azure Analysis Services Studio)
- Ensuring scalability and high availability with AAS
- Security implementation in AAS (Role-Based Security)
Module 5: Data Querying and Analysis Using T-SQL and DAX
- Advanced T-SQL techniques for data analysis (CTEs, Window Functions, PIVOT/UNPIVOT, MERGE)
- In-depth DAX exploration (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
- Getting started with 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 best practices in Power BI
- Introduction to Azure Synapse Studio for BI
Module 7: Course Review, Advanced Concepts, and Practical 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
Familiarity with the Windows operating environment and foundational understanding of SQL and relational databases.
14 Hours
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.
