Overview
The world runs on data, data is all around us, but without analytics it’s just numbers and noise. The role of the analytics is to convert this raw data into insights and drive successful decision.
The best way to learn analytics is to explore the most in demand and widely used analytics stack offered by Microsoft. Join us to learn end-to-end data analytics using SQL Server, Integration Services, Azure Data Factory and Power BI.
Tools Covered
- Business/Marketing Heads & Executives
- C-Suite Employees responsible for determining and achieving Revenue Targets
- Sales Heads & Executives
- IT employees assigned for assisting Sales, Marketing and Business Analytics Departments
- Students/graduates with business, commerce, marketing background
- Understand the fundamentals of data analytics and how to apply them using Microsoft SQL Server Tools.
- Develop skills in data manipulation and analysis using Microsoft SQL Server tools, including SQL Server Management Studio and SQL Server Integration Services.
- Gain proficiency in using data visualization tools such as Power BI to present insights and analysis from SQL Server data.
- Learn best practices for data management and optimization within SQL Server to improve query performance and database efficiency.
- Develop a comprehensive understanding of data warehousing and ETL (Extract, Transform, Load) processes using Microsoft SQL Server Integration Services.
Course Outline
- Week 1
- Week 2
- Introduction to Data Analytics
- Overview of Microsoft Stack for Data Analytics - SQL Server, SSIS, SSAS, SSRS, Power BI, Azure Data Factory
- Single Table Operations
- SSIS Introduction and Installation
- SQL Server Integration Services (SSIS) & ETL / DWH
- Advantages of SSIS for Data Loads, Operations, ETL, Warehouse
- SSIS Tools: SSDT (SQL Server Data Tools), ETL Wizards
- SSIS Design / Development and LIVE (Deployment) Environment
- Understanding Data Warehouse (DWH) Design and ETL Process
- DWH and ETL Structures, Implementations with MSBI SSIS
- SSIS ETL Operations for Data Reads, Data Cleansing, DWH
- Data Warehouse (DWH) Design Principles and Design Plan
- SSIS 2019: SSIS Database Installations
- SSIS Database & Catalog Folders – Purpose
- SSIS Catalog Database (SSIS DB): Creation, Verification
- Version Control Options and SSIS Design Plan – Tools Usage
- Multiple Table Operations
- SSIS ETL PACKAGES: EXTRACT, TRANSFORM, LOAD
- Control Flow Tasks – Architecture, Purpose and Usage
- Data Flow Tasks – Architecture, Purpose and Usage
- Creating SSIS Packages for Basic Data Flow Operations
- SSIS Solutions, Projects, and Package Creation. Tasks
- Need For Data Pipelines & Connections in Data Flow Tasks
- Understanding SSIS Package (Program) Execution Process
- Working with Data Flow Objects and Tasks in SSIS
- Using OLE DB and SQL Server Connections – Usage
- SSIS Package Creation Process – Using Control Flow Items
- Using DTSX Files for SSIS Package Execution. Audits
- SSIS Execution Context, SSIS Package Errors and Logs
- Data Flow Transformation: Conditional Split, Expressions
- Excel Connections, Sheets,
- SSIS 64 Bit and 32 Bit Configuration Settings and Options
- Week 3
- Week 4
- Data Description Language (DDL)
- MERGE Transformation and UNION ALL Transformation
- SORT Transformation, NOSORT Options, Advanced Sort
- Data Conversion Transformation and SSIS Expressions
- PACKAGE PROPERTIES, CHECKPOINT, PIVOT
- Execute SQL Task and OLE DB Queries – Connection Options
- Transaction Options for SSIS Executables – Package Level
- Precedence Constraints – Success / Failure / Completion
- SSIS Checkpoints – Purpose and Usage with Data Flow Tasks
- Checkpoint Files and SSIS Logging Options – Package, Tasks
- Transactions with Checkpoint File Usage in SSIS Packages
- Checkpoint Options – Advantages, Usage and Limitations
- SSIS Input Columns, Mappings and Source Column Values
- Using Data Viewer (Debugging) for Data Transfer
- Aggregate functions, Window aggregate functions, Order analytical functions
- Temporary Tables
- Sub-queries
- SSIS WITH ETL COMPONENT
- SSIS Package Events : Validation Events, Execution Events
- Pre Execution, Progress and Cleanup Events in SSIS
- Auditing SSIS Events, Errors / Warnings / Information
- Configuring and Using sysssislog System Tables
- Package Debugging : Data Viewers and Breakpoints
- ForEach Loop Container Usage. File Connections, Variables
- Defining Variables for Connections. DFT, Control Flow Links
- Dynamic Connections with Variables. Connection Strings
- SSIS Expressions and Usage for ETL and DWH
- FOR LOOP Expressions in SSIS, Examples for ETL
- SSIS Expression Builder, Functions, Evaluated Values
- Using Execute SQL Task : Variables, Return Values
- Week 5
- Week 6
- Data Warehouse design
- Conceptual, Logical and Physical Data Model
- Data Modeling and Schema Design
- ETL vs ELT
- BI Data Modeling
- Stored Procedures and Functions
- Indexing and Constraints
- SSIS PROJECT DEPLOYMENT, UPGRADES
- SSISDB Catalog Deployments – Process & ISPAC Files
- SSIS Package Jobs @ SQL Server Agent. SSIS Job Steps
- SSIS Job Schedules and Notifications
- SSIS Package Imports and Exports with ISPAC Files
- SSIS Package Execution Reports; Validation Reports
- SSIS ETL & DWH with STAR Schema
- SSIS ETL & DWH with SNOWFLAKE Schema
- Dimension Table Design & Business Keys
- Fact Table Design & Data Integrity
- Incremental Data Loads with Containers
- Staging Tables, Master – Child Packages
- Relationship modelling
- Introduction to Cloud and Advantages
- Cloud Architecture : IaaS,PasS,SaaS
- Microsoft Cloud Advantages : Azure
- Azure Data Factory (ADF) : Need
- Purpose of ADF & Advantages
- ADF Components Overview, Use
- SQL Server Data Tools (SSDT)
- Azure Data Factory Architecture
- ADF : Data Processing Components
- ADF : Data Movement Components
- Data Pipelines and ADF Activities
- ADF with Azure Services, Resources
- AZURE SQL CONFIG, DTU
- Week 7
- Week 8
- Introduction to DAX Language
- Creating DAX Measures
- Evaluating DAX Measures
- Leverage Calculate Functions functionality
- Power Function/ Divide Function
- MTD, QTD and YTD Date Calculations
- Business Use Case implementation in Power BI (Assignment)
- Live Vs Extract
- Data Source Filters
- Basic Report Creation
- Understanding of Rows and Columns
- Visualization best practices with real world examples
- Interactive Filters
- Types of filters
- Advanced Filter Calculations
- Pages
- Maps in PowerBI
- Visualize your data on map through spatial files
- Building a Dashboard
- Leveraging the use of Interactivity in Dashboards through Actions
- Designing and implementation of dashboard
- Designing of dashboard for mobile & Tablets
- Extensions
- Enriching information by creating Calculated Fields
- Calculation Syntax
- Date/Logic/String Calculations
- Visual analytics
- Pareto Chart
Our Methodology
Industry Usecases
With real world projects and immersive content built in partnership with top tier companies, you’ll master the tech skills companies want.
Technical Support
Our knowledgeable mentors guide your learning and are focused on answering your questions, motivating you and keeping you on track.
Career Mentorship
You’ll have access to resume support, portfolio review and optimization to help you advance your career and land a high-paying role.
Frequently Asked Questions
Yes, You will be awarded a course completion certificate by Bastakiya Tech if you pass the course.
Since our trainings are led by Industry Experts so it is made sure that content covered in workshop is designed with hand on knowledge of more than 70-75 % along with supporting theory.
For this professional workshop, you need to have a PC with minimum 4GB RAM and ideally 8GB RAM.
Don’t worry! We have got you covered. You shall be shared recorded lectures after each session, in case you want to revise your concepts or miss the lecture due to some personal or professional commitments.
Yes, you can rejoin the training within the span of an year of your registration. Please note following conditions in case you’re rejoining.
1) There are only 5 seats specified for rejoiners in each iteration.
2) These seats will be served on first come first basis.
3) If you have not submitted your complete fee, you may not be able to rejoin. Your registration would be canceled