EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

MSSQL, SSIS, SSAS and Power BI

Course outline

4 days
Discuss this programme →

Duration

MSSQL, SSIS, SSAS and Power BI

4 days

Objectives

  • Comprehensive Understanding: To provide a thorough understanding of SQL Server's role in handling OLAP workloads and the design and implementation of Data Warehouses.
  • Integration Mastery: To master the integration of SQL Server Integration Services and Analysis Services for effective data warehouse management and analytical model preparation.
  • Advanced Reporting Skills: To develop expertise in using SSAS, Data Warehouse, and Snowflake for creating and managing reports and dashboards in Power BI, both on-premise and on Power BI Service.
  • Staying Current: To introduce the latest enhancements in Power BI, including the use of Microsoft Fabric, enabling participants to stay abreast of the evolving landscape of data analytics and reporting.

Learning Outcomes

By the end of this program, participants will:

  • Gain Expertise in SQL Server: Develop the ability to design and manage a Data Warehouse using SQL Server for OLAP workloads, understanding the intricacies of star schema design.
  • Proficiency in SSIS and SSAS: Attain practical skills in creating, deploying, and managing SSIS projects, and in using SSAS for analytical modeling and reporting.
  • Master Power BI Reporting: Acquire the capability to integrate various data sources like SSAS, Data Warehouse, and Snowflake to create comprehensive reports and dashboards in Power BI.
  • Learn Latest Trends and Tools: Get updated with the new enhancements in Power BI and learn the application of Microsoft Fabric, preparing for future trends in data analytics and reporting.
  • Apply Skills in Industry-Specific Scenarios: Apply the learned concepts through practical labs and exercises tailored to the microprocessor manufacturing industry, ensuring that the skills gained are relevant and immediately applicable.

Learning Objectives:

  • Understand the role of SQL Server in OLAP workloads.
  • Learn the fundamentals of designing a Data Warehouse.
  • Master the basics of star schema design with dimension and fact tables.

Outcome:

Participants will be able to design and implement a basic Data Warehouse suitable for OLAP workloads using SQL Server Enterprise 2016.

Schedule:

  • Introduction to SQL Server for OLAP:
  • Overview of OLAP concepts.
  • SQL Server features for OLAP.
  • Designing a Data Warehouse:
  • Understanding Data Warehouse architecture.
  • Star schema vs. Snowflake schema.
  • Practical Lab:
  • Creating a basic star schema for a microprocessor manufacturing dataset.
  • Lab Exercise:
  • Building dimension and fact tables for a sample microprocessor manufacturing database.

Learning Objectives:

  • Learn to use SQL Server Integration Services for managing Data Warehouses.
  • Understand the role of SQL Server Analysis Services in data modelling.

Outcome:

Participants will be able to manage and prepare models for analysis using SSIS and SSAS.

Schedule:

  • Introduction to SQL Server Integration Services (SSIS):
  • Basics of creating SSIS projects.
  • ETL process fundamentals.
  • SQL Server Analysis Services (SSAS):
  • Building and deploying analytical models.
  • Introduction to cubes and MDX queries and Tabular Model.
  • Practical Lab:
  • Creating and deploying an SSIS project for the microprocessor manufacturing industry.
  • Lab Exercise:
  • Scheduling SQL Server Agent jobs for deployed packages.

Learning Objectives:

  • Learn to integrate SSAS, Data Warehouse, and Snowflake for report generation.
  • Master deploying and maintaining reports on an on-premise Power BI Server.

Outcome:

Participants will be skilled in preparing and deploying reports using Power BI on local servers.

Schedule:

  • Integrating SSAS, Data Warehouse, and Snowflake:
  • Data preparation for reporting.
  • Leveraging Snowflake with Power BI.
  • Deploying Reports on Power BI Server:
  • Configuration and maintenance of on-premise Power BI Server.
  • Practical Lab:
  • Preparing and deploying a report for the microprocessor manufacturing industry using SSAS and Snowflake.
  • Lab Exercise:
  • Maintaining and updating reports on a local Power BI Server.

Learning Objectives:

  • Explore new enhancements in Power BI data pipeline.
  • Introduction to Microsoft Fabric and its applications.

Outcome:

Participants will be updated with the latest trends and tools in Power BI and Microsoft Fabric for reporting.

Schedule:

  • New Enhancements in Power BI Data Pipeline:
  • Exploring recent updates and features.
  • Introduction to Microsoft Fabric:
  • Understanding its role in reporting and analytics.
  • Practical Lab:
  • Applying new Power BI features in a microprocessor manufacturing use case.
  • Lab Exercise:
  • Utilizing improved features in Power BI for advanced reporting solutions on Power BI Service.

Detailed Outline

Learning Objectives:

  • Grasp the fundamentals of SQL Server's role in OLAP.
  • Master the design principles of Data Warehousing.
  • Understand the intricacies of star schema design for efficient data storage and retrieval.

Outcome:

Participants will competently design and implement a Data Warehouse using SQL Server, tailored for OLAP applications in the microprocessor manufacturing industry.

Detailed Schedule:

  • Introduction to SQL Server and OLAP (1 ½ hours):
  • Understanding OLAP and its significance in data analytics.
  • Exploring SQL Server Enterprise 2016 features tailored for OLAP.
  • Fundamentals of Data Warehouse Design (2 hours):
  • Key concepts: Data Warehouse architecture, OLTP vs. OLAP.
  • Designing for performance: Indexing strategies, partitioning.
  • Star Schema Design Workshop (1 ½ hours):
  • Detailed exploration of star schema design.
  • Identifying dimension and fact tables in a business scenario.
  • Hands-On Lab: Creating a Star Schema (1 ½ hours):
  • Building a star schema for a microprocessor manufacturing dataset.
  • Q&A and Recap Session (½ hour):

Learning Objectives:

  • Develop proficiency in using SSIS for ETL processes.
  • Gain insights into SSAS for creating analytical models.

Outcome:

Participants will be skilled in managing Data Warehouses and preparing data models for analysis using SSIS and SSAS.

Detailed Schedule:

  • Deep Dive into SSIS (2 hours):
  • Creating SSIS projects: Detailed guide.
  • ETL process design and implementation.
  • Exploring SQL Server Analysis Services (1 ½ hours):
  • Building and deploying SSAS models.
  • Introduction to data cubes and MDX and Tabular Model.
  • Practical Lab: SSIS Project Implementation (1 ½ hours):
  • Step-by-step creation and deployment of an SSIS project for the microprocessor manufacturing industry.
  • Interactive Workshop: SSAS Model Building (1 ½ hours):
  • Hands-on session on creating and using analytical models in SSAS.
  • Q&A and Day's Recap (½ hour):
  • Review of the day's learning and addressing participant queries.

Learning Objectives:

  • Master the integration of SSAS, Data Warehouse, and Snowflake for report generation.
  • Learn in-depth techniques for deploying and maintaining reports on an on-premise Power BI Server.

Outcome:

Participants will adeptly prepare and deploy reports using Power BI, leveraging data from SSAS, Data Warehouse, and Snowflake.

Detailed Schedule:

  • Integrating Data Sources for Reporting (2 hours):
  • Techniques for combining SSAS, Data Warehouse, and Snowflake data.
  • Best practices for data preparation and optimization.
  • Power BI Server Deployment and Management (1 ½ hours):
  • Setting up and configuring on-premise Power BI Server.
  • Managing and updating reports.
  • Hands-On Lab: Building a Power BI Report (2 hours):
  • Creating a report using integrated data sources for a real-world microprocessor manufacturing scenario.
  • Interactive Session: Report Maintenance (1 hour):
  • Strategies for effective report management and updates.
  • Q&A and Overview of the Day (½ hour):
  • Review and discussion session to consolidate learning.

Learning Objectives:

  • Get acquainted with the latest enhancements in Power BI data pipelines.
  • Explore the potential of Microsoft Fabric in data reporting and analytics.

Outcome:

Participants will be updated with current trends in Power BI, gaining the ability to utilize Microsoft Fabric for advanced data analytics.

Detailed Schedule:

  • Exploring New Features in Power BI (2 hours):
  • Detailed overview of recent Power BI updates.
  • Practical applications in data analytics.
  • Introduction to Microsoft Fabric (1 hour):
  • Understanding Microsoft Fabric and its role in advanced reporting.
  • Case studies and practical applications.
  • Hands-On Lab: Power BI Advanced Features (2 hours):
  • Applying new features in a microprocessor manufacturing use case.
  • Demo: Utilizing Microsoft Fabric (1 hour):
  • Demo session on leveraging Microsoft Fabric for reporting solutions.
  • Final Q&A and Program Wrap-Up (1 hour):
  • Comprehensive review of the program.
  • Closing remarks and next steps for continued learning.

Programme scope, delivery arrangements and suitability are confirmed during enquiry.