AI Powered Data Analytics

Introduction

• What is Data Analytic
• Common Terms in Data Analytics
• What is data
• Classication of data
• Relevance in industry and need of the hour
• Types of problems and business objectives in various industries
• How leading companies are harnessing the power of analytics
• Critical success drivers.
• Overview of Data Analytics tools & their popularity.
• Data Analytics Methodology & problem-solving framework.

Introduction To Python

• Installation and Working with Python
• Understanding Python variables
• Python basic Operators
• Understanding the Python blocks.

Python Keyword and Identiers

• Python Comments, Multiline Comments.
• Python Indentation
• Understating the concepts of Operators
  • Arithmetic
  • Relational
  • Logical
  • Assignment
  • Membership
  • Identity

Introduction To Variables

• Variables, expression condition and function
• Global and Local Variables in Python
• Packing and Unpacking Arguments
• Type Casting in Python
• Byte objects vs. string in Python
• Variable Scope

Python Data Type

• Declaring and using Numeric data types
• Using string data type and string operations
• Understanding Non-numeric data types
• Understanding the concept of Casting and Boolean.
• Strings
• List
• Tuples
• Dictionary
• Sets

Control Structure & Flow

• Statements ? if, else, elif
• How to use nested IF and Else in Python
• Loops
• Loops and Control Statements.
• Jumping Statements ? Break, Continue, pass
• Looping techniques in Python
• How to use Range function in Loop
• Programs for printing Patterns in Python
• How to use if and else with Loop
• Use of Switch Function in Loop
• Elegant way of Python Iteration
• Generator in Python
• How to use nested Loop in Python
• Use If and Else in for and While Loop
• Examples of Looping with Break and Continue Statement
• How to use IN or NOT IN keyword in Python Loop.

Python Function, Modules and Packages

• Python Syntax
• Function Call
• Return Statement
• Arguments in a function ? Required, Default, Positional, Variable-length
• Write an Empty Function in Python ?pass statement.
• Lamda/ Anonymous Function
• *args and **kwargs
• Help function in Python
• Scope and Life Time of Variable in Python Function
• Nested Loop in Python Function
• Recursive Function and Its Advantage and Disadvantage
• Organizing python codes using functions
• Organizing python projects into modules
• Importing own module as well as external modules
• Understanding Packages
• Random functions in python
• Programming using functions, modules & external packages
• Map, Filter and Reduce function with Lambda Function
• More example of Python Function

List

• What is List.
• List Creation
• List Length
• List Append
• List Insert
• List Remove
• List Append & Extend using ?+? and Keyword
• List Delete
• List related Keyword in Python
• List Revers
• List Sorting
• List having Multiple Reference
• String Split to create a List
• List Indexing
• List Slicing
• List count and Looping
• List Comprehension and Nested Comprehension

Tuple

• What is Tuple
• Tuple Creation
• Accessing Elements in Tuple
• Changing a Tuple
• Tuple Deletion
• Tuple Count
• Tuple Index
• Tuple Membership
• TupleBuilt in Function (Length, Sort)

Dictionary

• Dict Creation
• Dict Access (Accessing Dict Values)
• Dict Get Method
• Dict Add or Modify Elements
• Dict Copy
• Dict From Keys.
• Dict Items
• Dict Keys (Updating, Removing and Iterating)
• Dict Values
• Dict Comprehension
• Default Dictionaries
• Ordered Dictionaries
• Looping Dictionaries
• Dict useful methods (Pop, Pop Item, Str , Update etc.)

Sets

• What is Set
• Set Creation
• Add element to a Set
• Remove elements from a Set
• PythonSet Operations
• Frozen Sets

Strings

• What is Set
• Set Creation
• Add element to a Set
• Remove elements from a Set
• PythonSet Operations

Python Exception Handling

• Python Errors and Built-in-Exceptions
• Exception handing Try, Except and Finally
• Catching Exceptions in Python
• Catching Specic Exception in Python
• Raising Exception
• Try and Finally

Python File Handling

• Opening a File
• Python File Modes
• Closing File
• Writing to a File
• Reading from a File
• Renaming and Deleting Files in Python
• Python Directory and File Management
• List Directories and Files
• Making New Directory
• Changing Directory

Python Database Interaction

• SQL Database connection using
• Creating and searching tables
• Reading and Storing cong information on database
• Programming using database connections

Reading an excel

• Working With Excel
• Reading an excel le using Python
• Writing to an excel sheet using Python
• Python| Reading an excel le
• Python | Writing an excel le
• Adjusting Rows and Column using Python
• ArithmeticOperation in Excel le.
• Play with Workbook, Sheets and Cells in Excel using Python
• Creating and Removing Sheets
• Formatting the Excel File Data
• More example of Python Function

Complete Understanding of OS Module of Python

• Check Dirs. (exist or not)
• How to split path and extension
• How to get user prole detail
• Get the path of Desktop, Documents, Downloads etc.
• Handle the File System Organization using OS
• How to get any les and folder?s details using OS

AI and LLM Integration in Python:

• PandasAI: Natural language queries on DataFrames
• OpenAI API (GPT) to generate code, EDA, and reports
• LangChain for building chat-based data apps
• Autogen/Code Interpreter tools for automating insights

Introduction to Statistics

• Categorical Data
• Numerical Data
• Mean
• Median
• Mode
• Outliers
• Range
• Interquartile range
• Correlation
• Standard Deviation
• Variance
• Box plot

Understanding Statistics

• Descriptive Statistics
• Sample vs Population Statistics
• Random variables
• Probability distribution functions
• Expected value
• Normal distribution
• Gaussian distribution
• Z-score
• Spread and Dispersion
• Correlation and Co-variance

Data Pre-Processing & Data Mining

• Data Preparation
• Feature Engineering
• Feature Scaling
• Datasets
• Dimensionality Reduction
• Anomaly Detection
• Parameter Estimation
• Data and Knowledge
• Selected Applications in Data Mining

EDA (Exploratory Data Analysis)

• Need for structured exploratory data
• EDA framework for exploring the data and identifying any problems with the data (Data Audit Report)
• Identify missing data
• Identify outliers? data
• Imbalanced Data Techniques

Data Analysis and Visualization using Pandas.

• Statistics
  • Categorical Data
  • Numerical Data
  • Mean
  • Median
  • Mode
  • Outliers
  • Range
  • Interquartile range
  • Correlation
  • Standard Deviation
  • Variance
  • Box plot
• Pandas
  • Read data from Excel File using Pandas More Plotting, Date Time Indexing and writing to les
  • How to get record specic records Using Pandas Adding & Resetting Columns, Mapping with function
  • Using the Excel File class to read multiple sheets More Mapping, Filling Nonvalue?s
  • Exploring the Data Plotting, Correlations, and Histograms
  • Getting statistical information about the data Analysis Concepts, Handle the None Values
  • Reading les with no header and skipping records Cumulative Sums and Value Counts, Ranking etc
  • Reading a subset of columns Data Maintenance, Adding/Removing Cols and Rows
  • Applying formulas on the columns Basic Grouping, Concepts of Aggre gate Function
  • Complete Understanding of Pivot Table Data Slicing using iLoc and Loc property (Setting Indices)
  • Under sting the Properties of Pivot Table in Pandas Advanced Reading CSVs/HTML, Binning, Categorical Data
  • Exporting the results to Excel Joins
  • Python | Pandas Data Frame Inner Join
  • Under sting the properties of Data Frame Left Join (Left Outer Join)
  • Indexing and Selecting Data with Pandas Right Join (Right Outer Join)
  • Pandas | Merging, Joining and Concatenating Full Join (Full Outer Join)
  • Pandas | Find Missing Data and Fill and Drop NA Appending Data Frame and Data
  • Pandas | How to Group Data How to apply Lambda / Function on Data Frame
  • Other Very Useful concepts of Pandas in Python Data Time Property in Pandas (More and More)

Data Analysis and Visualization using NumPy and MatPlotLib

• NumPy
  • Introduction to NumPy Numerical Python
  • Importing NumPy and Its Properties
  • NumPy Arrays
  • Creating an Array from a CSV
  • Operations an Array from a CSV
  • Operations with NumPy Arrays
  • Two-Dimensional Array
  • Selecting Elements from 1-D Array
  • Selecting Elements from 2-D Array
  • Logical Operation with Arrays
  • Indexing NumPy elements using conditionals
  • NumPy?s Mean and Axis
  • NumPy?s Mode, Median and Sum Function
  • NumPy?s Sort Function and More
• MatPlotLib
  • Bar Chart using Python MatPlotLib
  • Column Chart using Python MatPlotLib
  • Pie Chart using Python MatPlotLib
  • Area Chart using Python MatPlotLib
  • Scatter Plot Chart using Python MatPlotLib
  • Play with Charts Properties Using MatPlotLib
  • Export the Chart as Image
  • Understanding plt. subplots () notation
  • Legend Alignment of Chart using MatPlotLib
  • Create Charts as Image
  • Other Useful Properties of Charts.
  • Complete Understanding of Histograms
  • Plotting Different Charts, Labels, and Labels Alignment etc.

Introduction to Data Visualization with Seaborn

• Introduction to Seaborn
  • Introduction to Seaborn
  • Making a scatter plot with lists
  • Making a count plot with a list
  • Using Pandas with seaborn
  • Tidy vs Untidy data
  • Making a count plot with a Dataframe
  • Adding a third variable with hue
  • Hue and scattera plots
  • Hue and count plots
• Visualizing Two Quantitative Variables
  • Introduction to relational plots and subplots
  • Creating subplots with col and row
  • Customizing scatters plots
  • Changing the size of scatter plot points
  • Changing the style of scatter plot points
  • Introduction to line plots
  • Interpreting line plots
  • Visualizing standard deviation with line plots
  • Plotting subgroups in line plots
• Visualizing a Categorical and a Quantitative Variable
  • Current plots and bar plots
  • Count plots
  • Bar plot with percentages
  • Customizing bar plots
  • Box plots
  • Create and interpret a box plot
  • Omitting outliers
  • Adjusting the whisk
  • Point plots
  • Customizing points plots
  • Point plot with subgroups
• Customizing Seaborn Plots
  • Changing plot style and colour
  • Changing style and palette
  • Changing the scale
  • Using a custom palette
  • Adding titles and labels Part 1
  • Face Grids vs. Axes Subplots
  • Adding a title to a face Grid object
  • Adding title and labels Part 2
  • Adding a title and axis labels
  • Rotating x-tics labels
  • Putting it all together
  • Box plot with subgroups
  • Bar plot with subgroups and subplots
  • Well done! What?s next

SQL Server Fundamentals

• SQL Server 2019 Installation
• Service Accounts & Use, Authentication Modes & Usage, Instance Congurations
• SQL Server Features & Purpose
• Using Management Studio (SSMS)
• Conguration Tools & SQLCMD
• Conventions & Collation

SQL Server 2019 Database Design

• SQL Database Architecture
• Database Creation using GUI
• Database Creation using T-SQL scripts
• DB Design using Files and File Groups
• File locations and Size parameters
• Database Structure modications

SQL Tables in MS SQL Server

• SQL Server Database Tables
• Table creation using T-SQL Scripts
• Naming Conventions for Columns
• Single Row and Multi-Row Inserts
• Table Aliases
• Column Aliases & Usage
• Table creation using Schemas
• Basic INSERT
• UPDATE
• DELETE
• SELECT queries and Schemas
• Use of WHERE, IN and BETWEEN
• Variants of SELECT statement
• ORDER BY
• GROUPING
• HAVING
• ROWCOUNT and CUBE Functions

Data Validation and Constraints

• Table creation using Constraints
• NULL and IDENTITY properties
• UNIQUE KEY Constraint and NOT NULL
• PRIMARY KEY Constraint & Usage
• CHECK and DEFAULT Constraints
• Naming Composite Primary Keys
• Disabling Constraints & Other Options

Views and Row Data Security

• Benets of Views in SQL Database
• Views on Tables and Views
• SCHEMA BINDING and ENCRYPTION
• Issues with Views and ALTER TABLE
• Common System Views and Metadata
• Common Dynamic Management views
• Working with JOINS inside views

Indexes and Query tuning

• Need for Indexes & Usage
• Indexing Table & View Columns
• Index SCAN and SEEK
• INCLUDED Indexes & Usage
• Materializing Views (storage level)
• Composite Indexed Columns & Keys
• Indexes and Table Constraints
• Primary Keys & Non-Clustered Indexes

Stored Procedures and Benets

• Why to use Stored Procedures
• Types of Stored Procedures
• Use of Variables and parameters
• SCHEMABINDING and ENCRYPTION
• INPUT and OUTPUT parameters
• System level Stored Procedures
• Dynamic SQL and parameterization

System functions and Usage

• Scalar Valued Functions
• Types of Table Valued Functions
• SCHEMABINDING and ENCRYPTION
• System Functions and usage
• Date Functions
• Time Functions
• String and Operational Functions
• ROW_COUNT
• GROUPING Functions

Triggers, cursors, memory limitations

• Why to use Triggers
• DML Triggers and Performance impact
• INSERTED and DELETED memory tables
• Data Audit operations & Sampling
• Database Triggers and Server Triggers
• Bulk Operations with Triggers

Cursors and Memory Limitations

• Cursor declaration and Life cycle
• STATIC
• DYNAMIC
• SCROLL Cursors
• FORWARD_ONLY and LOCAL Cursors
• KEYSET Cursors with Complex SPs

Transactions Management

• ACID Properties and Scope
• EXPLICIT Transaction types
• IMPLICIT Transactions and options
• AUTOCOMMIT Transaction and usage

AI Integration in SQL:

• AI Tools:
  • ChatGPT/Vanna.ai/DataGPT for Natural Language to SQL
  • Explain SQL queries using AI
  • Suggest query improvements with AI
• Copilot in Azure Data Studio or GitHub for SQL suggestions

Understanding Concepts of Excel

• Creation of Excel Sheet Data
• Range Name, Format Painter
• Conditional Formatting, Wrap Text, Merge & Centre
• Sort, Filter, Advance Filter
• Different type of Chart Creations
• Auditing, (Trace Precedents, Trace Dependents)Print Area
• Data Validations, Consolidate, Subtotal
• What if Analysis (Data Table, Goal Seek, Scenario)
• Solver, Freeze Panes
• Various Simple Functions in Excel(Sum, Average, Max, Min)
• Real Life Assignment work

Ms Excel Advance

• Advance Data Sorting
• Multi-level sorting
• Restoring data to original order after performing sorting
• Sort by icons
• Sort by colours
• Lookup Functions
  • Lookup
  • VLookup
  • HLookup
• Subtotal, Multi-Level Subtotal
• Grouping Features
  • Column Wise
  • Row Wise
• Consolidation With Several Worksheets
• Filter
  • Auto Filter
  • Advance Filter
• Printing of Raw & Column Heading on Each Page
• Workbook Protection and Worksheet Protection
• Specified Range Protection in Worksheet
• Excel Data Analysis
  • Goal Seek
  • Scenario Manager
• Data Table
  • Advance use of Data Tables in Excel
  • Reporting and Information Representation
• Pivot Table
  • Pivot Chat
  • Slicer with Pivot Table & Chart
• Generating MIS Report In Excel
  • Advance Functions of Excel
  • Math & Trig Functions
• Text Functions
• Lookup & Reference Function
• Logical Functions & Date and Time Functions
• Database Functions
• Statistical Functions
• Financial Functions
• Functions for Calculation Depreciation

AI Integration in Excel:

• Excel Copilot (Microsoft 365):
  • AI-assisted formula creation
  • Data summary using natural language prompts
  • Generate charts and pivot tables via prompts
• Ideas in Excel: Automated pattern recognition
• Integration with Power Automate for Workflow Automation

Introduction to Power BI

• Overview of BI concepts
• Why we need BI
• Introduction to SSBI
• SSBI Tools
• Why Power BI
• What is Power BI
• Building Blocks of Power BI
• Getting started with Power BI Desktop
• Get Power BI Tools
• Introduction to Tools and Terminology
• Dashboard in Minutes
• Interacting with your Dashboards
• Sharing Dashboards and Reports

Power BI Desktop

• Power BI Desktop
• Extracting data from various sources
• Workspaces in Power BI

Power BI Data Transformation

• Data Transformation
• Query Editor
• Connecting Power BI Desktop to our Data Sources
• Editing Rows
• Understanding Append Queries
• Editing Columns
• Replacing Values
• Formatting Data
• Pivoting and Unpivoting Columns
• Splitting Columns
• Creating a New Group for our Queries
• Introducing the Star Schema
• Duplicating and Referencing Queries
• Creating the Dimension Tables
• Entering Data Manually
• Merging Queries
• Finishing the Dimension Table
• Introducing the another DimensionTable
• Creating an Index Column
• Duplicating Columns and Extracting Information
• Creating Conditional Columns
• Creating the FACT Table
• Performing Basic Mathematical Operations
• Improving Performance and Loading Data into the Data Model

Modelling with Power BI

• Introduction to Modelling
• Modelling Data
• Manage Data Relationship
• Optimize Data Models
• Cardinality and Cross Filtering
• Default Summarization & Sort by
• Creating Calculated Columns
• Creating Measures & Quick Measures

Data Analysis Expressions (DAX)

• What is DAX
• Data Types in DAX
• Calculation Types
• Syntax, Functions, Context Options
• DAX Functions
  • Date and Time
  • Time Intelligence
  • Information
  • Logical
  • Mathematical
  • Statistical
  • Text and Aggregate
• Measures in DAX
• Measures and Calculated Columns
• ROW Context and Filter Context in DAX
• Operators in DAX - Real-time Usage
• Quick Measures in DAX - Auto validations
• In-Memory Processing DAX Performance

Power BI Desktop Visualisations

• How to use Visual in Power BI
• What Are Custom Visuals
• Creating Visualisations and Colour Formatting
• Setting Sort Order
• Scatter & Bubble Charts & Play Axis
• Tooltips and Slicers, Timeline Slicers & Sync Slicers
• Cross Filtering and Highlighting
• Visual, Page and Report Level Filters
• Drill Down/Up
• Hierarchies and Reference/Constant Lines
• Tables, Matrices & Conditional Formatting
• KPI's, Cards & Gauges
• Map Visualizations
• Custom Visuals
• Managing and Arranging
• Drill through and Custom Report Themes
• Grouping and Binning and Selection Pane, Bookmarks & Buttons
• Data Binding and Power BI Report Server

Introduction to Power BI Dashboard and Data Insights

• Why Dashboard and Dashboard vs Reports
• Creating Dashboards
• Conguring a Dashboard Dashboard Tiles, Pinning Tiles
• Power BI Q&A
• Quick Insights in Power BI

Direct Connectivity

• Custom Data Gateways
• Exploring live connections to data with Power BI
• Connecting directly to SQL Server
• Connectivity with CSV & Text Files
• Excel with Power BI Connect Excel to Power BI, Power BI Publisher for Excel
• Content packs
• Update content packs

Publishing and Sharing

• Introduction and Sharing Options Overview
• Publish from Power BI Desktop and Publish to Web
• Share Dashboard with Power BI Service
• Workspaces (Power BI Pro) and Content Packs (Power BI Pro)
• Print or Save as PDF and Row Level Security (Power BI Pro)
• Export Data from a Visualization
• Export to PowerPoint and Sharing Options Summary

Refreshing Datasets

• Understanding Data Refresh
• Personal Gateway (Power BI Pro and 64-bit Windows)
• Replacing a Dataset and Troubleshooting Refreshing

AI Integration in Power BI:

• Smart Narrative visual (AI-generated insights)
• Decomposition Tree (Root Cause Analysis)
• Q&A Visual (Natural Language Querying)
• Azure Cognitive Services integration
• Power BI Copilot (Preview): Report creation via prompts
• Integration with Power Automate for alerts and workflows

Introduction to Data Preparation using Tableau Prep

• Data Visualization
• Business Intelligence tools
• Introduction to Tableau
• Tableau Architecture
• Tableau Server Architecture
• VizQL Fundamentals
• Introduction to Tableau Prep
• Tableau Prep Builder User Interface
• Data Preparation techniques using Tableau Prep Builder tool

Data Connection with Tableau Desktop

• Features of Tableau Desktop
• Connect to data from File and Database
• Types of Connections
• Joins and Unions
• Data Blending
• Tableau Desktop User Interface

Basic Visual Analytics

• Visual Analytics
• Basic Charts Bar Chart, Line Chart, and Pie Chart
• Hierarchies
• Data Granularity
• Highlighting
• Sorting
• Filtering
• Grouping
• Sets

Calculations in Tableau

• Types of Calculations
• Built-in Functions (Number, String, Date, Logical and Aggregate)
• Operators and Syntax Conventions
• Table Calculations
• Level of Detail (LOD) Calculations
• Using R within Tableau for Calculations

Advanced Visual Analytics

• Parameters
• Tool tips
• Trend lines
• Reference lines
• Forecasting
• Clustering

Level of Detail (LOD) Expressions in Tableau

• Count Customer by Order
• Profit per Business Day
• Comparative Sales
• Profit Vs Target
• Finding the second order date
• Cohort Analysis

Geographic Visualizations in Tableau

• Introduction to Geographic Visualizations
• Manually assigning Geographical Locations
• Types of Maps
• Spatial Files
• Custom Geocoding
• Polygon Maps
• Web Map Services
• Background Images

Advanced charts in Tableau

• Box and Whisker?s Plot
• Bullet Chart
• Bar in Bar Chart
• Gantt Chart
• Waterfall Chart
• Pareto Chart
• Control Chart
• Funnel Chart
• Bump Chart
• Step and Jump Lines
• Word Cloud
• Donut Chart

Dashboards and Stories

• Introduction to Dashboards
• The Dashboard Interface
• Dashboard Objects
• Building a Dashboard
• Dashboard Layouts and Formatting
• Interactive Dashboards with actions
• Designing Dashboards for devices
• Story Points

Get Industry Ready

• Tableau Tips and Tricks
• Choosing the right type of Chart
• Format Style
• Data Visualization best practices

Exploring Tableau Online

• Publishing Workbooks to Tableau Online
• Interacting with Content on Tableau Online
• Data Management through Tableau Catalog
• AI-Powered features in Tableau Online (Ask Data and Explain Data)
• Understand Scheduling
• Managing Permissions on Tableau Online
• Data Security with Filters in Tableau Online

AI Integration in Tableau:

• Ask Data: Natural Language Data Exploration
• Explain Data: Automatic statistical insights
• Tableau GPT (Einstein Copilot - Salesforce)
• AI forecasting in visualizations
• Integration with Python (TabPy) and R

Capstone Project