Duration: 5 days – 35 hrs
Overview
This five-day course combines the training on SQL Server Integration Services (SSIS) integration with Power BI and Power BI essentials. It covers the development of SSIS packages for ETL processes, working with various data sources, transforming and cleaning data, loading data into Power BI, and the essentials of Power BI, including data modeling, data visualization, sharing and collaboration, data refresh, and security. The course provides a comprehensive
understanding of integrating SSIS and Power BI, as well as the essential skills needed to work with Power BI effectively. Hands-on exercises and practical examples are included to reinforce the concepts and techniques learned throughout the training.
Objectives
Audience
• Business Analysts: Individuals working in business analysis roles who need to analyze and interpret data to support decision-making processes within their organization.
• Data Analysts: Professionals responsible for collecting, organizing, and analyzing data to uncover insights and trends.
• Business Intelligence Professionals: Individuals involved in designing, developing, and implementing business intelligence solutions within their organizations.
• Data Managers: Professionals who oversee data management and governance and need to understand how to leverage Power BI for effective data visualization and reporting.
• Reporting Specialists: Individuals responsible for creating and presenting reports and dashboards to stakeholders and management teams.
• IT Professionals: Those working in IT departments who need to understand how to utilize Power BI for data analysis and visualization tasks.
• Project Managers: Professionals responsible for overseeing data-driven projects and need to gain insights from data analysis using Power BI.
• Decision-makers: Managers and executives who need to understand how to utilize Power BI to gain data-driven insights and make informed decisions.
Prerequisites
• Basic understanding of data concepts and database management.
• Familiarity with business intelligence and data analysis principles.
• Proficiency in working with Microsoft Power BI for data visualization and reporting.
• Basic knowledge of SQL and data querying.
• Understanding of cloud computing concepts, specifically Microsoft Azure.
Course Content
Day 1
Introduction to SQL Server Integration Services (SSIS)
• Overview of SSIS and its capabilities
• Understanding ETL (Extract, Transform, Load) processes
• Integration scenarios with SQL Server and Power BI
Setting Up the Environment
• Installing SQL Server Data Tools (SSDT)
• Configuring SSIS packages and project settings
• Connecting to SQL Server databases and Power BI services
Creating SSIS Packages
• Creating a new SSIS project
• Adding and configuring data sources
• Designing data flow tasks
• Transforming and cleansing data using SSIS transformations
Deploying and Scheduling SSIS Packages
• Deploying SSIS packages to SQL Server or the Integration Services Catalog
• Configuring package parameters and connection managers
• Scheduling package execution using SQL Server Agent
Day 2
SSIS Package Development Best Practices
• Error handling and logging techniques
• Package configuration and parameterization
• Designing for performance and scalability
• Debugging and troubleshooting SSIS packages
Working with Flat Files and Excel Data
• Importing and exporting data from flat files
• Reading and writing data to Excel files
• Handling file formats, delimiters, and encoding
Extracting Data from SQL Server Databases
• Using SQL Server as a data source in SSIS packages
• Implementing data extraction strategies
• Incremental data loading techniques
Day 3
Transforming and Cleaning Data in SSIS
• Working with SSIS transformations (e.g., Derived Column, Lookup, Conditional Split)
• Data type conversions and validations
• Handling null values and data quality issues
Loading Data into Power BI
• Integrating SSIS with Power BI using Power BI Desktop
• Establishing connections and data source settings
• Configuring data refresh schedules and methods
Day 4
Introduction to Power BI
• Overview of Power BI and its capabilities
• Connecting to various data sources
• Building data models in Power BI Desktop
Data Visualization in Power BI
• Creating interactive reports and dashboards
• Using visualizations and charts effectively
• Applying filters, slicers, and drill-through actions
Day 5
Power BI Data Transformation and Querying
• Transforming and shaping data using Power Query Editor
• Querying data using Power BI Desktop
• Advanced data modeling techniques
Power BI Sharing and Collaboration
• Sharing reports and dashboards with colleagues
• Collaboration features in Power BI
• Publishing Power BI reports to the web
Power BI Data Refresh and Security
• Configuring data refresh schedules and options
• Implementing row-level security in Power BI
• Managing data source credentials and gateways
