Certified Data & Business Analyst (CDBA) Program

Working with Name Range: –

• What does naming a cell range mean?
• Rules for naming cells and ranges
• Naming cell range(s) in a worksheet
• Navigating through workbooks using named ranges
• Creating named ranges automatically based on cell values

Numeric/Statistial Functions/ Lookup Function:-

• SUM, SUMIF, SUMIFS
• AVERAGE, AVERAGEIF, AVERAGEIFS
• COUNT, COUNTIF, COUNTA, COUNTBLANK
• MAX, MIN, Vlookup, HLookup

Understanding References

• Understanding References
• Relative References
• Absolute References
• Mixed References
• Utilizing names for writing formulae.

Filtering / Sorting

• Filtering and Sorting
• Concept and Implementation

Logical Functions

• If Function
• AND, OR Function
• Nested Functions

Conditional Formatting

• Create Conditional Formatting
• Rule 1 to 6

Creating Dropdown List

• Simple Dropdown List
• Conditional Dropdown List

Date & Time Functions

• TODAY, NOW
• DAY, MONTH, YEAR
• WEEKDAY

Working with PivotTables Charts

• Creating Pivot Table
• Exploring Table Design Tab
• Slicers
• Creating Chart
• Formatting Chart

Using AI and ChatGPT in MS Excel -New

Module 1: Understanding Analytics & Business Intelligence (BI) Concept

This module will guide the candidate through the basics of Analytics. Learn about the Data & Analytics

• Data & Analytics
• Introduction to Business Intelligence
• Importance of Business Intelligence
• Overview of Self-Service BI

Module 2: Microsoft Power BI Introduction
• Power BI introduction and Overview
• Power BI Desktop – Installation
• Explain Power BI Desktop
• Power BI Screen Elements and Toolbars

Module 3: Connecting Power BI with Different Data Source
• Connect to CSV files in Power BI
• Connect to Excel in Power BI
• Connect to PDF in Power BI
• Connect to Web Page in Power BI

Module 4: Data Cleaning with Power Query
• Power Query Introduction
• Query Editor interface
• Column Transformations
• Combining Data: Append Query
• Combining Data: Merge Query
• Number, Date and Time Calculations
• Text Transformation
• Sorting & Filters Data
• Splitting & Merging Data
• Transforms Data
• Pivot and Unpivot Data
• Adding Columns
• M Built-in Function

Module 5: Data Modelling in Power BI

• What is a PowerPivot?
• What Is a Data Model?
• What is a Relationship?
• Introduction to DAX
• Detecting and Creating Relationships of Tables
• Understanding the Star Schema
• Creating Calculated Columns
• Creating Tables
• Creating Measure
• Row Context vs Set Context
• Working with DAX Functions
• Getting Data from Related Tables

Module 6: Creating Report & Visualization Type in Power BI
• Create a New Power BI Report
• Report Editor in Power BI
• Add, Rename, Delete & Duplicate Page to Power BI Report
• Save Report in Power BI
• Change how visuals interact in a report
• Open a Power BI in Reading View
• Page Display Setting in Power BI report
• Type of Visualization in Power BI ReportAdding Visuals
(Bar, Column, Line, Area, Ribbon, Pie, Scatter, Tree Map, Waterfall, Combo,
Funnel, KPI, Radial Gauge, Doughnut Chart etc.)
• Data Categories, Geo-Data and Maps
• Creating and Working with Tables and Matrix Views
• Customizing Visuals by Field, Format & Analytic
• Filtering Visual, Page, All Pages
• Using Interactive Slicers – Dropdowns and Searches
• Number, Date, Text Slicers
• Applying Conditional Formats to Visualizations
• Drill Visualizations to see underlying Data
• Date Hierarchies
• Show Values as and Quick Measures
• Custom Visualization in Power BI
• Selection Pane
• Lock Objects
• Adding Graphics
• Bookmarks
• Spotlight
• Report Themes
• Using Security by Creating Role Based Views
• Insight – Explain Increase/Decrease
• Export Power BI Data to CSV

Module 6: Creating a Power BI Dashboard

• Dashboard vs Reports
• Creating a Power BI Dashboard
• Dashboard Tiles in Power BI
• Pin a Tiles to Power BI Dashboard from a report
• Power BI publisher in Excel
• Data alert in Power BI Service
• Add Image, Text box, Video or web code to your dashboard
• Tips for designing a great Power BI Dashboard
• Create Phone View of a Dashboard
• Power BI Q&A
• Quick Insights with Power BI

Module 7: Refreshing Data
• Configure schedule refresh data
• Refresh a dataset created from Power BI Desktop (Local)
• Refresh a dataset created from Power BI Desktop (Cloud)
• Disable privacy setting

Module 1: Introduction to SQL and Relational Databases

• Relational database management systems (RDBMS)
• Comparison of SQL and Excel features and use cases

Module 2: SQL Basics
• SQL syntax, keywords, and conventions
• Data types and operators (arithmetic, comparison, logical)
• SQL constraints (Primary/check/default/null….)
• SELECT statement, projection, and filtering data with WHERE aliases and expressions
• Sorting data with ORDER BY

Module 3: Working with Joins and Unions
• Different types of joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN) ON,
USING, and NATURAL keyword usage
• UNION and UNION ALL set operations

Module 4: Aggregating Data and Group Functions
• GROUP BY clause and grouping sets
• HAVING clause for filtering aggregated results
• Aggregate functions: SUM, COUNT, AVG, MIN, MAX

Module 5: Advanced SQL Queries
• EXISTS and NOT EXISTS operators
• Window functions: RANK, DENSE_RANK, ROW_NUMBER, NTILE, etc.
• Index in SQL
• Nested Stored procedure

Module 6: SQL for Data Management
• Data Manipulation Language (DML): INSERT, UPDATE, DELETE
• Transactions: BEGIN, COMMIT, ROLLBACK
• Optimization SQL Query
• Procedure/Function

Module 7: SQL for Data Analysis and Reporting
• Pivoting data: transforming rows to columns
• Unpivoting data: transforming columns to rows
• GROUP BY extensions (ROLLUP, CUBE, and GROUPING SETS)

• Discussion on Statistics concepts and theories
• Correlation, Var-Covar, Beta, Regression, Monte Carlo Simulation, among others

Module 1 – Introduction to Python Basics – 6 hours

• Introduction to Python (History & Usage)
• Data Types in Python (Int, Boolean, Strings)
• Maths in ALU (Division, Addition & Multiplication)
• User Input & String Functions (Len, Lower, Upper)
• Variables & their memory allocation
• Control Flows (if, elif, else)
• Conditional Loops (for, while & Nested If)
• Indexing & Slicing the Strings

Module 2 – Introduction to Functions & List Comprehensions – 4 hours

• What are Functions?
• Types of functions (built-in, user-defined, anonymous)
• Creating your own function (def)
• Local vs. global scope
• Lambda Function (Anonymous Function)
• Map, Filter & Reduce functions
• List comprehensions & Its structure & simple Example

Module 3 – Data Structures in Python – 4 hours

• Introduction to NumPy arrays (creation- np.array())
• 1-D, 2-D, 3-D Arrays & Use cases in Python
• Array attributes (shape, size, dtype, etc.)
• Built-in Functions for Array Creation (np.zeros(), np.ones(), np.arange(), np.random)
• Array operations and broadcasting (Addition, multiplication, etc.)
• Accessing elements in Arrays & Negative indexing
• Multidimensional array indexing
• Reshaping Arrays (reshape(), flatten(), ravel())
• Aggregation Functions – (sum(), mean(), std(), min(), max())
• Correlations and Covariance – (corrcoef(), cov())
• Random Number Generation (np.random.rand(), np.random.randint())

Module 4 – All about Numpy – Numerical Python – 6 hours

• Introduction to Pandas DataFrames and Series
• Read excel, csv & connect to sql (pd.read_csv)
• Inspecting DataFrames (.head(), .tail(), .info(), .describe(), .shape)
• DataFrame Attributes and Methods (.shape, .columns, .index, .dtypes, .values)
• Indexing with loc and iloc
• Making a conditions columns (if/ else or by lambda function)
• Filtering data with conditions (np.where)
• Combining multiple conditions
• Data Cleaning techniques – Fillna, isna, dropna, drop_duplicates()
• Data Transformation & Preparation (df.rename(), astype, pd.to_datetime())
• Returning a new DataFrame – Understanding the inplace parameter
• Data manipulation with Pandas Selecting columns, rows
• Grouping data with groupby()
• Aggregating data (sum, mean, count, etc.)
• Applying custom aggregation functions
• Concatenating & Merging DataFrames (.merge, .join, .concat())

Module 5 – Introduction to OOP

• Classes and Objects – Defining classes, Creating objects
• Encapsulation – Access modifiers: Public, Private, Protected
• Inheritance – Base and derived classes & Multiple inheritance
• Polymorphism – Method overriding
• Abstraction – Abstract classes (abc module)
• Real-world OOP examples (e.g., Banking system, E-commerce platform)

Module 6 – Introduction to Matplotlib & Seaborn – 4 hours

• What is Matplotlib?
• Importance of data visualization
• Plotting simple lines and points
• Customizing Plots – Adding titles, customizing axes and labels, Adding legends
• Basic plot functions (plot(), scatter(), bar(), Histograms, Heatmaps etc.)
• Box & whisker Plot (Stats Plot)
• Creating multiple plots in a single figure (plt.subplot() and plt.subplots())
• Overview of Seaborn & Plotly for Interactive visualizations
• distplot(), kdeplot(), rugplot()
• 3D Plots

Module 7 – Introduction to OOPs – 3 hours

• It’s time to showcase what you have learnt so far..
• EDA Exercise: Analyzing a Dataset
• NumPy Exercise: Array Manipulations
• Pandas Exercise: DataFrame Manipulations
• Matplotlib Exercise: Data Visualization

• Soft Skills Training.
• LinkedIn Profile Update.
• CV Samples to follow.
• Mock Interviews.