Course Curriculum

• 1. Python
• 2. Power BI
• 3. SQL
• 4. Excel
• 5. Latest gen AI tools
• 6. AI: ChatGPT, CoPilot, Gemini
• 7. Projects: Healthcare & Banking

1. Python:

• 1. Introduction to Python for Data Analysis
  • Overview of Python and Its Applications in Data Analysis
  • Installing Python and IDEs (Jupyter Notebook, VS Code, PyCharm)
  • Python Basics
    • Syntax, Variables, and Data Types
    • Input and Output Operations
    • Control Structures: Conditional Statements and Loops
• 2. Python Data Structures
  • Lists
    • Creating, Accessing, and Modifying Lists
    • List Comprehensions
  • Tuples and Sets
    • Immutability and Use Cases
    • Set Operations and Membership Testing
  • Dictionaries
    • Key-Value Pairs and Operations
    • Dictionary Comprehensions
• 3. Working with Python Libraries for Data Analysis
  • NumPy:
    • Introduction to Arrays and Their Applications
    • Array Operations: Indexing, Slicing, and Reshaping
    • Mathematical and Statistical Operations on Arrays
  • Pandas:
    • Introduction to DataFrames and Series
    • Reading and Writing Data from Various Formats (CSV, Excel, SQL)
    • Data Cleaning: Handling Missing Values and Duplicates
    • Data Manipulation: Merging, Joining, and Concatenating
    • Grouping and Aggregation
  • Matplotlib and Seaborn:
    • Creating Basic Plots (Line, Bar, Scatter)
    • Customizing Plots (Titles, Labels, Legends)
    • Advanced Visualization Techniques with Seaborn
    • Heatmaps, Pairplots, and Boxplots
• 4. Data Cleaning and Preparation
  • Handling Missing Data
    • Imputation Techniques
    • Dropping Null Values
  • Data Transformation
    • Scaling and Normalizing Data
    • Encoding Categorical Variables
  • Working with Dates and Times
    • Parsing and Formatting Dates
    • Handling Time Series Data
• 5. Exploratory Data Analysis (EDA)
  • Summary Statistics
    • Mean, Median, Mode, and Standard Deviation
    • Quantiles and Percentiles
  • Data Visualization for EDA
    • Histograms and Distribution Plots
    • Scatter and Pair Plots
    • Correlation Matrix and Heatmaps
• 6. Advanced Data Analysis Techniques
  • Feature Engineering
    • Creating New Features
    • Feature Selection Techniques
  • Statistical Analysis
    • Hypothesis Testing
    • ANOVA and Chi-Square Tests
    • Correlation and Covariance
• 7. Introduction to Machine Learning
  • Supervised Learning
    • Linear Regression and Logistic Regression
    • Decision Trees and Random Forest
  • Unsupervised Learning
    • Clustering Algorithms (K-Means, Hierarchical)
    • Dimensionality Reduction with PCA
  • Evaluation Metrics
    • Accuracy, Precision, Recall, and F1-Score
    • Confusion Matrix and ROC-AUC
• 8. Automating Tasks with Python
  • Introduction to Automation
    • Reading and Writing Files (CSV, Excel, JSON)
    • Automating Email and Report Generation
  • Web Scraping
    • Introduction to BeautifulSoup
    • Fetching Data from Websites
    • Handling Dynamic Web Pages with Selenium
• 9. Working with Databases
  • Introduction to SQL with Python
    • Connecting Python to Databases
    • Performing CRUD Operations with SQLAlchemy
  • Using Pandas with SQL
    • Reading Data from Databases into DataFrames
    • Writing DataFrames Back to Databases
• 10. Real-World Projects
  • Customer Segmentation Analysis
  • Sales Forecasting with Time Series Data
  • Market Basket Analysis
  • Creating Dashboards with Matplotlib and Seaborn
  • Building an End-to-End Data Analysis Pipeline

2. Power BI

• Module 1: SQL SERVER INTRODUCTION
  • Data, Databases and RDBMS Software
  • Database Types: OLTP, DWH, OLAP
  • Microsoft SQL Server Advantages, Use
  • Versions and Editions of SQL Server
  • SQL: Purpose, Real-time Usage Options
  • SQL versus Microsoft T-SQL [MSSQL]
  • Microsoft SQL Server - Career Options
  • SQL Server Components and Usage
  • Database Engine Component and OLTP
  • BI Components, Data Science Components
  • ETL, MSBI and Power BI Components
  • Course Plan, Concepts, Resume, Project
  • 24 x 7 Online Labs for Remote DB Access
  • Software Installation Pre-Requisites

• Module 2: Introduction to Power BI
  • Power BI Job Roles in Real-time
  • Power BI Data Analytics Job Roles
  • Business Analytics - Job Roles
  • Power BI Developer - Job Roles
  • Power BI for Data Scientists
  • Comparing MSBI and Power BI
  • Comparing Tableau and Power BI
  • MCSA 70-778, MCSA 70-779 Exam
  • Types of Reports in Real-World
  • Interactive & Paginated Reports
  • Analytical & Mobile Reports
  • Data Sources Types in Power BI
  • Power BI Licensing Plans - Types
  • Power BI Training : Lab Plan
  • Power BI Dev & Prod Environments
  • Understanding the Power BI Tools
  • Installing Power BI & Connecting to Data
  • The "Locale" used in the curriculum
  • Working with the Query Editor
  • Working with the Data Model and Creating a Visualization

• Module 3: Basic Report Design
  • Power BI Desktop Installation
  • Data Sources & Visual Types
  • Canvas, Visualizations and Fields
  • Get Data and Memory Tables
  • In-Memory xvelocity Database
  • Table and Tree Map Visuals
  • Format Button and Data Labels
  • Legend, Category and Grid
  • PBIX and PBIT File Formats
  • Visual Interaction, Data Points
  • Disabling Visual Interactions
  • Edit Interactions - Format Options
  • SPOTLIGHT & FOCUSMODE
  • CSV and PDF Exports. Tooltips
  • Power BI Ecosystem, Architecture

• Module 4: Visual Sync, Grouping
  • Slicer Visual: Real-time Usage
  • Orientation, Selection Properties
  • Single & Multi Select, CTRL Options
  • Slicer: Number, Text and Date Data
  • Slicer List and Slicer Dropdowns
  • Visual Sync Limitations with Slicer
  • Disabling Slicers, Clear Selections
  • Grouping: Real-time Use, Examples
  • List Grouping and Binning Options
  • Grouping Static / Fixed Data Values
  • Grouping Dynamic / Changing Data
  • Bin Size and Bin Limits (Max, Min)
  • Bin Count and Grouping Options
  • Grouping Binned Data, Classification

• Module 5: Hierarchies, Filters
  • Creating Hierarchies in Power BI
  • Independent Drill-Down Options
  • Dependent Drill-Down Options
  • Conditional Drilldowns, Data Points
  • Drill Up Buttons and Operations
  • Expand & Show Next Level Options
  • Dynamic Data Drills Limitations
  • Show Data and See Records
  • Filters: Types and Usage in Real-time
  • Visual Filter, Page Filter, Report Filter
  • Basic, Advanced and TOP N Filters
  • Category and Summary Level Filters
  • DrillThru Filters, Drill Thru Reports
  • Keep All Filters Options in Drill Thru
  • Cross Report Filters, Include, Exclude

• Module 6: Bookmarks, Azure, Modeling
  • Drill-thru Filters, Page Navigations
  • Bookmarks: Real-time Usage
  • Bookmarks for Visual Filters
  • Bookmarks for Page Navigations
  • Selection Pane with Bookmarks
  • Buttons, Images with Actions
  • Buttons, Actions and Text URLs
  • Bookmarks View & Selection Pane
  • OLTP Databases, Big Data Sources
  • Azure Database Access, Reports
  • Import & Direct Query with Power BI
  • SQL Queries and Enter Data
  • Data Modelling: Currency, Relations
  • Summary, Format, Synonyms
  • Web View & Mobile View in PBI

• Module 7: Visualization Properties
  • Stacked Charts and Clustered Charts
  • Line Charts, Area Charts, Bar Charts
  • 100% Stacked Bar & Column Charts
  • Map Visuals: Tree, Filled, Bubble
  • Cards, Funnel, Table, Matrix
  • Scatter Chart: Play Axis, Labels
  • Series Clusters & Selections
  • Waterfall Chart and ArcGIS Maps

• Module 8: Power Query Level 1
  • Power Query M Language Purpose
  • Power Query Architecture and ETL
  • Data Types, Literals and Values
  • Power Query Transformation Types
  • Table & Column Transformations
  • Text & Number Transformations
  • Date, Time and Structured Data
  • List, Record and Table Structures
  • let, source, in statements @ M Lang
  • Power Query Functions, Parameters
  • Invoke Functions, Execution Results
  • Get Data, Table Creations and Edit
  • Merge and Append Transformations
  • Join Kinds, Advanced Editor, Apply
  • ETL Operations with Power Query

• Module 9: Power Query Level 2
  • Query Duplicate, Query Reference
  • Group By and Advanced Options
  • Aggregations with Power Query
  • Transpose, Header Row Promotion
  • Reverse Rows and Row Count
  • Data Type Changes & Detection
  • Replace Columns: Text, NonText
  • Replace Nulls: Fill Up, Fill Down
  • PIVOT, UNPIVOT Transformations
  • Move Column and Split Column
  • Extract, Format and Numbers
  • Date & Time Transformations
  • Deriving Year, Quarter, Month, Day
  • Add Column: Query Expressions
  • Query Step Inserts and Step Edits

• Module 10: Power Query Level 3
  • Creating Parameters in Power Query
  • Parameter Data Types, Default Lists
  • Static/Dynamic Lists For Parameters
  • Removing Columns and Duplicates
  • Convert Tables to List Queries
  • Linking Parameters to Queries
  • Testing Parameters and PBI Canvas
  • Multi-Valued Parameter Lists
  • Creating Lists in Power Query
  • Converting Lists to Table Data
  • Advanced Edits and Parameters
  • Data Type Conversions, Expressions
  • Columns From Examples, Indexes
  • Conditional Columns, Expressions

• Module 11: DAX Functions - Level 1
  • DAX: Importance in Real-Time
  • Real-world usage of Excel, DAX
  • DAX Architecture, Entity Sets
  • DAX Data Types, Syntax Rules
  • DAX Measures and Calculations
  • ROW Context and Filter Context
  • DAX Operators, Special Characters
  • DAX Functions, Types in Real-Time
  • Vertipaq Engine, DAX Cheat Sheet
  • Creating, Using Measures with DAX
  • Creating, Using Columns with DAX
  • Quick Measures and Summaries
  • Validation Errors, Runtime Errors
  • SUM, AVERAGEX, KEEPFILTERS
  • Dynamic Expressions, IF in DAX

• Module 12: DAX Functions - Level 2
  • Data Modelling Options in DAX
  • Detecting Relations for DAX
  • Using Calculated Columns in DAX
  • Using Aggregated Measures in DAX
  • Working with Facts & Measures
  • Modelling: Missing Relations
  • Modelling: Relation Management
  • CALCULATE Function Conditions
  • CALCULATE & ALL Member Scope
  • RELATED & COUNTROWS in DAX
  • Entity Sets and Slicing in DAX
  • Dynamic Expressions, RETURN
  • Date, Time and Text Functions
  • Logical, Mathematical Functions
  • Running Total & EARLIER Function

• Module 13: DAX Functions Level 3
  • Connection with CSV, MS Access
  • AVERAGEX and AVERAGE in DAX
  • KEEPFILTERS and CALCULATE
  • COUNTROWS, RELATED, DIVIDE
  • PARALLELPERIOD, DATEDADD
  • CALCULATE & PREVIOUSMONTH
  • USERELATIONSHIP, DAX Variables
  • TOTALYTD , TOTALQTD
  • DIVIDE, CALCULATE, Conditions
  • IF..ELSE..THEN Statement
  • SELECTEDVALUE, FORMAT
  • SUM, DATEDIFF Examples in DAX
  • TODAY, DATE, DAY with DAX
  • Time Intelligence Functions – DAX

• Module 14: Power BI Cloud - 1
  • Power BI Service Architecture
  • Power BI Cloud Components, Use
  • App Workspaces, Report Publish
  • Reports & Related Datasets Cloud
  • Creating New Reports in Cloud
  • Report Publish and Report Uploads
  • Dashboards Creation and Usage
  • Adding Tiles to Dashboards
  • Pining Visuals and Report Pages
  • Visual Pin Actions in Dashboards
  • LIVE Page Interaction in Dashboard
  • Adding Media: Images, Custom Links
  • Adding Chs and Embed Links
  • API Data Sources, Streaming Data
  • Streaming Dataset Tiles (REST API)

• Module 15: Power BI Cloud - 2
  • Dashboards Actions, Report Actions
  • DataSet Actions: Create Report
  • Share, Metrics and Exports
  • Mobile View & Dashboard Themes
  • Q & A [Cortana] and Pin Visuals
  • Export, Subscribe, Subscribe
  • Favorite, Insights, Embed Code
  • Featured Dashboards and Refresh
  • Gateways Configuration, PBI Service
  • Gateway Types, Cloud Connections
  • Gateway Clusters, Add Data Sources
  • Data Refresh: Manual, Automatic
  • PBIEngw Service, ODG Logs, Audits
  • DataFlows, Power Query Expressions
  • Adding Entities and JSON Files

• Module 16: Excel & RLS
  • Import and Upload Options in Excel
  • Excel Workbooks and Dashboards
  • Datasets in Excel and Dashboards
  • Using Excel Analyzer in Power BI
  • Using Excel Publisher in PBI Cloud
  • Excel Workbooks, PINS in Power BI
  • Excel ODC Connections, Power Pivot
  • Row Level Security (RLS) with DAX
  • Need for RLS in Power BI Cloud
  • Data Modeling in Power BI Desktop
  • DAX Roles Creation and Testing
  • Adding Power BI Users to Roles
  • Custom Visualizations in Cloud
  • Histogram, Gantt Chart, Infographics

• Module 17: Report Server, RDL
  • Need for Report Server in PROD
  • Install, Configure Report Server
  • Report Server DB, Temp Database
  • Web service URL, Webportal URL
  • Creating Hybrid Cloud with Power BI
  • Using Power BI DesktopRS
  • Uploading Interactive Reports
  • Report Builder For Report Server
  • Report Builder For Power BI Cloud
  • Designing Paginated Reports (RDL)
  • Deploy to Power BI Report Server
  • Data Source Connections, Report
  • Power BI Report Server to Cloud
  • Tenant IDs Generation and Use
  • Mobile Report Publisher, Usage

• Module 18: PowerApp
  • Overview
  • Basic Power App Concept
  • Canvas Apps | Navigation | Customization
  • Contents (Galleries, Data Cards, Forms, Triggers, Functions & Formulas, Edit Forms, Text Boxes)

• Module 19: Power BI Service & Power BI Mobile
  • Why Power BI Service?
  • Comparison Power BI Free & Premium
  • Logging into Power BI Service
  • Interface overview
  • Importing data from Desktop to Service
  • Dataset menu
  • Working on reports
  • Dashboard overview
  • Workspace & Gateways
  • Installing Gateways - Personal & On-premise
  • Working alone or collaborating with colleagues
  • Collaborating in App Workspace
  • Sharing the results
  • Publishing the app
  • Content packs from online services
  • Power BI Mobile Overview
  • Excluding dataset from sharing

• Module 20: Power BI and Excel Together
  • Options for Publishing from Excel
  • Pin Excel Elements to Power BI
  • Analyze in Excel (Power BI Pro or Premium)
  • Excel Publish: Upload and Export to Power BI
  • Sharing Published Excel Dashboards (Power BI Pro or Premium)

• Module 21: Real-Time Project
  • Project Requirement Analysis
  • Implementing SDLC Phases
  • Requirement Gathering, FSA

• Phase 1:
  • Report Design
  • Visualizations
  • Properties, Analytics and Formatting

• Phase 2:
  • Modelling
  • Power Query
  • Dynamic Connections
  • Azure DB
  • Parameters and M Lang Scripts

• Phase 3:
  • DAX Requirements, Analysis
  • Cloud and Report Server
  • Project FAQs and Solutions

3. Microsoft SQL Server Course

Course Overview

Microsoft SQL Server is a leading Relational Database Management System (RDBMS) in the Microsoft ecosystem, ideal for data storage and retrieval in both desktop and web applications.

Course Objectives

• Master SQL (Structured Query Language) for database management.
• Design and manage databases using Data Definition Language (DDL) statements.
• Perform basic CRUD operations with Data Manipulation Language (DML) statements.
• Develop and execute stored procedures, functions, triggers, cursors, and indexes.

Prerequisites

• Basic familiarity with Windows Operating System.
• Suitable for beginners aiming to start a career in Information Technology.

Course Modules

• Module 1: Basic Database Concepts
  • What is Data, Field, Record, and Database?
  • Limitations of File Management Systems.
  • DBMS Basics and Advantages.
  • Relational DBMS and Client-Server Architecture.
• Module 2: Entity-Relationship (E-R) Modeling
  • Identifying Entities, Attributes, and Relationships.
  • Drawing E-R Diagrams.
  • Converting E-R Diagrams into Relational Schemas.
• Module 3: Normalization
  • Understanding Normalization and its Benefits.
  • Practical Examples: 1NF, 2NF, 3NF.
• Module 4: SQL Server Overview
  • SQL Server History and Editions.
  • Features, Components, and Management Studio.
  • Understanding System Databases.
• Module 5: Introduction to SQL
  • Types of SQL Statements (DDL, DML, DQL, DCL, TCL).
  • Creating Databases and Understanding Data Types.
  • Exploring DDL Statements.
• Module 6: DDL and DML Operations
  • Creating, Altering, and Dropping Tables.
  • Insert, Update, Delete, and Truncate Statements.
• Module 7: Querying with DQL
  • Writing SELECT Statements with Keywords (TOP, DISTINCT).
  • Using WHERE Clause, Operators, and Subqueries.
  • Sorting Data with ORDER BY.
• Module 8: Aggregate Functions
  • Using COUNT, SUM, MIN, MAX, AVG.
  • GROUP BY and HAVING Clause.
  • Using GROUP BY with ROLLUP and CUBE.
• Module 9: Joins and Set Operations
  • Types of Joins: Inner, Outer, Cross, Self.
  • Subqueries and Set Operations (UNION, INTERSECT, EXCEPT).
• Module 10: Data Integrity
  • Entity Integrity.
  • Domain Integrity.
  • Referential Integrity.
  • Types of Constraints.
• Module 11: Working with Constraints
  • Unique.
  • Not NULL.
  • Primary Key.
  • CHECK and Foreign Key Constraints.
• Module 12: Implementing Views
  • Creating, Altering, and Dropping Views.
  • Advantages of Views.
  • Advanced Options in Views.
• Module 13: Data Control Language (DCL)
  • Creating Users and Roles.
  • Granting and Revoking Permissions.
• Module 14: Indexing
  • Clustered and Non-Clustered Indexes.
  • Creating, Dropping, and Understanding Index Structures.
• Module 15: Writing Transact-SQL (T-SQL)
  • Scripts, Batches, and Variables.
  • Using Temporary Tables.
  • Dynamic SQL and System Functions.
• Module 16: Stored Procedures and Functions
  • Creating, Executing, and Modifying Procedures.
  • User-Defined Functions.
  • Benefits of Stored Procedures.
• Module 17: Triggers
  • Creating and Managing Triggers.
  • Types of Triggers (FOR, AFTER, INSTEAD OF).
  • Using ROLLBACK TRAN with Triggers.
• Module 18: Cursors
  • Creating and Managing Cursors.
  • Types and Advantages of Cursors.
• Module 19: Transaction Control Language (TCL)
  • Transaction Management and ACID Properties.
  • Isolation Levels and Lock Management.
• Module 20: Backup and Restore
  • Backing Up and Restoring Databases.
  • Generating and Executing SQL Scripts.
  • Attaching and Detaching Databases.
• Module 21: Advanced Features
  • Ranking Functions.
  • Using Pivot Tables.
  • Working with XML and BLOB Data Types.

4. Excel:

• 1. Introduction to Excel for Data Analysis
  • Excel Interface Overview
    • Understanding Ribbons, Menus, and Toolbars
    • Creating and Saving Workbooks
    • Keyboard Shortcuts for Efficiency
  • Basics of Excel
    • Working with Rows, Columns, and Cells
    • Formatting Data (Fonts, Borders, Colors)
    • Using Undo, Redo, and Clipboard Options
• 2. Excel Formulas and Functions
  • Basic Formulas
    • Understanding Relative and Absolute References
    • Arithmetic Operations (SUM, AVERAGE, COUNT)
  • Intermediate Functions
    • Logical Functions (IF, AND, OR)
    • Lookup Functions (VLOOKUP, HLOOKUP, XLOOKUP)
    • Text Functions (CONCATENATE, LEFT, RIGHT, MID, LEN)
  • Advanced Functions
    • Array Formulas and Dynamic Arrays
    • INDEX and MATCH for Advanced Lookups
    • Using INDIRECT and OFFSET
• 3. Data Management and Cleaning
  • Sorting and Filtering Data
    • Custom Sort Options
    • Filtering Data with Criteria
  • Data Cleaning Techniques
    • Removing Duplicates
    • Handling Missing Values
    • Text to Columns for Data Parsing
    • Data Validation Rules
  • Using Conditional Formatting
    • Highlighting Cells Based on Rules
    • Creating Custom Conditional Formatting Rules
• 4. Data Visualization in Excel
  • Creating Charts and Graphs
    • Bar, Line, and Pie Charts
    • Scatter Plots and Bubble Charts
    • Combo Charts
  • Advanced Visualization
    • Using Sparklines for Mini-Charts
    • Waterfall Charts and Histogram Charts
    • Adding Trendlines and Error Bars
  • Formatting and Customizing Charts
    • Adding Titles, Labels, and Legends
    • Adjusting Axes and Gridlines
• 5. Pivot Tables and Pivot Charts
  • Creating Pivot Tables
    • Understanding Rows, Columns, and Values
    • Grouping Data by Categories
  • Advanced Pivot Table Techniques
    • Using Calculated Fields and Items
    • Creating Slicers for Interactive Filtering
  • Pivot Charts
    • Visualizing Pivot Table Data
    • Formatting and Customizing Pivot Charts
• 6. Advanced Excel Features for Data Analysis
  • Data Analysis Tools
    • Using Goal Seek for Scenario Testing
    • Solver for Optimization Problems
    • Data Tables for Sensitivity Analysis
  • Working with Large Datasets
    • Handling Millions of Rows
    • Using Excel Tables for Structured Data
    • Applying Filters and Aggregations
  • Integration with Power Query
    • Connecting to External Data Sources
    • Transforming Data with Power Query
• 7. Macros and VBA for Automation
  • Introduction to Macros
    • Recording and Running Macros
    • Understanding Macro Security
  • VBA Basics
    • Introduction to Visual Basic for Applications
    • Writing and Editing VBA Code
    • Creating User-Defined Functions (UDFs)
  • Automating Tasks with VBA
    • Automating Reports and Dashboards
    • Building Interactive Forms
• 8. Excel Integration with Other Tools
  • Integration with Python
    • Using OpenPyXL for Excel Manipulation
    • Automating Data Analysis with Pandas
  • Connecting Excel to Databases
    • Importing Data from SQL
    • Using ODBC Connections
• 9. Real-World Projects
  • Building a Sales Dashboard
    • Summarizing Sales Data with Pivot Tables
    • Visualizing KPIs with Charts
  • Inventory Management Analysis
    • Tracking Stock Levels and Trends
    • Automating Reorder Level Alerts
  • Customer Segmentation
    • Analyzing Demographics with Filters
    • Creating Visual Reports for Insights

5. Latest Gen AI Tools:

• Introduction to Generative AI Tools
• AI-Powered Automation for Business
• Data Analytics using AI

6. ChatGPT & CoPilot & Gemini

7. Projects: Healthcare & Banking