Data Analysis Using Microsoft Excel:
1.Data Analysis using Microsoft Excel:
Basics of Excel, Formatting, Database functions
Excel Operators , Logical &Text formulas./Formula error checking.Excel Sheet Formatting.
Date and time functions,Database functions likeDSUM,DAVERAGE,DCOUNT,DGET.
2.Relative,Absolute & Mixed Referencing:
Creating Absolute/Mixed References. Problems with Absolute/Relative Cell Referencing
3. Look up Functions:
Named Ranges,VLOOKUP() – a practical perspective.VLOOKUP() w. TRUE – When & Why?
VLOOKUP() with MATCH(), lookup INDEX() and 2 MATCH(),XLOOKUP
4.Pivot Tables,Pivot Charts & Power Pivot:
Creating, Formatting Simple PivotTables.Page Field in a PivotTable.Formatting a PivotTable.
Creating/Modifying a PivotChart. Data Modelling in Power Pivot
5. Complex & Logical Formulas:
IF(), Nested IFs, AND(),OR() Combo Logics; IFERROR().
6.MIS Reporting & Dashboard Techniques:
Automatic row-wise Subtotal, Gridlines, Data Validation (list),Cell-Range Naming, Grouping.
Build Business reports like Sales, Inventory, Salary report, dashboards etc.
7. Charting ,Conditional Formatting & Data Cleaning:
Creating Multiple Chart Type, Primary, Secondary Chart ,Trend line Charting, Scatterplot,
Conditional Formatting
8.Protection,Validation,Consolidation:
Protecting, Validating & Consolidating data from sheets and workbooks.
9. Macros Application:
Developer tab; Record & Run a Macro,Understanding & Editing VBA Code of Macros.
SQL For Data Analysis:
1. Data Analysis and AI Tools:
Power Query, What if Analysis, Forecast, Analyze,Chat GPT in Excel, AI Add-ins in Excel
2.SQL Course Content for Data Analysis:
Understanding SQL: Definition and Importance Overview of Databases: Types and Uses
Installing and Setting Up MySQL and MySQL Workbench
3.Exploring SQL Data Types:
Understanding Primary Keys and Foreign Keys
Applying Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT
4.Creating and Managing Databases:
Creating Databases and Tables, Modifying Table Structures with ALTER TABLE
Dropping Tables and Databases
5. Data Manipulation:
Inserting Data into Tables (INSERT), Updating Data in Tables (UPDATE),Deleting Data from
Tables (DELETE),Best Practices for Data Integrity
6. Querying Data:
Writing Basic SELECT Statements, Filtering Data with the WHERE Clause, Using Logical
Operators (AND, OR, NOT),Sorting Data with ORDER BY
7. Importing and Exporting Data:
Importing Data from CSV Files into SQL, Exporting Data from SQL to CSV Files
Handling Large Data Imports Efficiently
8.Functions in SQL:
Using String Functions: CONCAT, SUBSTRING, LENGTH, etc. Exploring Numeric Functions:
ROUND, AVG, SUM, etc. Date and Time Functions: NOW(), CURDATE(), EXTRACT(), etc.
9. Aggregate Functions and Grouping Data:
Using Aggregate Functions: COUNT, MAX, MIN, AVG, SUM Grouping Data with GROUP BY
Filtering Groups with HAVING Clause
10. Advanced Data Analysis Techniques:
Working with Subqueries: Types and Usage, Using Window Functions: ROW_NUMBER,
RANK, DENSE_RANK, Applying Conditional Logic with CASE Statements
11. Joining Data from Multiple Tables:
Understanding SQL Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
Advanced Joins: SELF JOIN, CROSS JOIN, Combining Results with UNION and UNION ALL
12.Common Table Expressions (CTE) and Views:
Writing and Using Common Table Expressions (CTEs), Creating and Managing Views
13.Practical Data Analysis Projects:
Real-World Data Analysis Scenarios ,Hands-On Projects Using Sample Databases
Interpreting and Presenting Analysis Results
14.Performance Optimization:
Indexing for Faster Query Performance,Analyzing and Optimizing Query Execution Plans
15. Advanced Topics in SQL:
Understanding and Implementing Triggers, Working with Stored Procedures and Functions
Microsoft Power BI:
1.Microsoft Power BI:
Installing and Running Power ,BI Desktop,The parts of Power BI,Core areas of Power BI,
Workflow of PBI,Data Load Process,The Power BI Desktop Window.
2. Loading Data with Power BI Desktop:
Data Sources,Loading Data,Web Pages,CSV Files,Text Files,Excel,Databases.
3. Transforming Data in Power Query:
Power BI Desktop Queries,Transforming Data The Power Query Editor, The Applied Steps
List Dataset Shaping, Renaming/Reordering/Removing Columns, Removing Error Records,
Removing Duplicate Records Sorting Data, Filtering Data
4.Data Cleaning & Data Mashup:
Changing Data Type,Detecting Data TypesReplacing Values,Transforming Column Contents
Filling Down,Using the First Row As Headers,Grouping Duplicating,Splitting & Merging
Columns,Custom/ Index Columns,Merging & Appending Data, Adding the Contents of One
Query to Another,Adding Multiple Files from a Source Folder,Changing the Data Structure
Pivoting & Unpivoting ,Query Duplicates & References.
5. Creating & Designing Data Model:
Data Modeling in the Power BI Desktop Environment Star Schema Design Principal, Creating
Relationships Manually & Automatically Cardinality- Many to One, One to One, Many to
Many. Deleting, Managing, Deactivating Relationships.
6. Data Analysis Expressions (DAX) :
Introduction to DAX, Calculated Measures,Calculated Columns, Calculated Tables,
DAX Aggregation Functions like SUM,SUMX,COUNT,COUNTX,AVERAGEX etc.
DAX Filter Functions like CALCULATE,CALCULATE TABLE ,REMOVE FILTERS,ALL etc.
DAX Logical Functions like IF,AND,OR,SWITCH,TRUE
DAX Relationship Functions like RELATED,RELATEDTABLE, USERELATIONSHIP.
DAX Table Manipulation functions like ADDCOLUMNS,CROSSJOIN,NATURALINNERJOIN.
DAX Date and Time Functions like CALENDER,CALENDERAUTO,DATE,DATEDIFF,EDATE etc.
DAX Time-intelligence functions like TOTAL MTD,TOTALQTD,TOTALYTD,DATESMTD,
DATESQTD, OPENING & CLOSING BALANCE, DATESBETWEEN ETC
7.Power BI Visualizations:
Canvas, Visualizations and Fields,Power BI Desktop, Dashboards ,Creating a Basic Table,
Creating a MatrixMulti-Row Cards,Switching Between Table Types.A First Chart,Basic Chart
Types,Column ChartsLine Charts,Pie Charts,Essential Chart Adjustments,Donut Charts,Funnel
Charts,Multiple Data Values in Charts 100% Stacked Column and Bar Charts Scatter Charts,
Bubble Charts Map and Filled Map visual Tree Maps Q&A AI based Visual AI Based Key
Influences Visual Decomposition Tree.
8.Filtering Data:
Filters, Visual-Level Filters,Page-Level Filters,Report-Level Filters,Removing Filters,Using the
Filter Hierarchy,Using Slicers,Formatting Slicers,Specifying Visual Interactions,
Choosing the Correct Approach to Interactive, Data Selection
9. Power BI Deployment on Cloud:
Power BI Workspace, Publishing report on workspace, Report Sharing
Tableau:
1.Data Analysis using Tableau :
Introduction to Tableau Desktop and Tableau Public, Understanding Tableau interface
and workspace, Connecting to data sources, Creating your first Tableau workbook.
2.Data Preparation and Transformation:
Importing and connecting to different data sources, Data cleaning and transformation
techniques, Working with joins, blends, and unions, Introduction to calculated fields and
parameters.
3.Data Visualization Basics:
Understanding basic visualization concepts, Creating various chart types like bar charts,
line charts, pie charts, Histograms, Box plot, Bar chart, Line, Bubble, Bullet, Scatter plot.
Applying filters and sorting data,Formatting and customizing visualizations.
4. Advanced Visualization Techniques:
Exploring advanced chart types like heat maps, tree maps, Pareto, Funnel etc. Using
reference lines, bands, and distributions,Working with maps and geographical data
5. Advanced Data Manipulation and Filtering:
Mark and highlight, Groups, Sets (creating and editing sets, IN/OUT),Bins, Hierarchies,
Folders, Sorting and Types, Filtering in Tableau, Types of filters,
Filtering continuous dates, dimensions, and measures
6. Exploring and Analyzing Data:
Calculation, Expressions and Parameters,
Calculation syntax and functions in Tableau, Various types of calculations, including Table,
String, Date, Aggregate, Logic, and Number, Levels of details: fixed level, lower level, and
higher level, LOD expressions, including concept and syntax, Nested LOD expressions,
Aggregation and replication with LOD expressions, Quick table calculations
The creation of calculated fields
7.Using Parameter:
Creating parameters, Parameters in calculations, Using parameters with filters,
Column selection parameters, Chart selection parameters, Using parameters in the filter
session, Using parameters in the reference line