Telegram Group: [Link]

me/+VQgeExQT-oI5NWZl

Assignment 1:
[Link]

Assignment 2:
[Link]

Assignment 3:
[Link]

Selfpaced badge Submission Link: [Link]

IIC Portal link: [Link]

Refr Link:
[Link]

Drive Link:
[Link]

Project Development templates link:

1.​ Ideation Phase:
[Link]
e_link
2.​ Requirement Analysis​
[Link]
ink
Note: For customer journey Map visit [Link] and search Customer Experience
Journey Map ([Link]

Self Paced Course:

[Link]
artbridge-T30M-APSCHE-event#1
Go to above Link
SignUp with Registered Email
Click on enrol button for the Corse Getting started with Data

Recordings: [Link]
Project Development Phases
1.​ Ideation Phase
a.​ Brainstorming files
b.​ Empathy Map
c.​ Problem Statement
2.​ Requirement Analysis
a.​ Customer Journey Map
b.​ Data Flow Diagram
c.​ Solution Requirement
d.​ Technology Stack
3.​ Project Design Phase
a.​ Problem Solution Fit
b.​ Proposed Solution
c.​ Solution Architecture
4.​ Project Planing Phase
a.​ Project Planning Template
5.​ Functional and Performance Testing
a.​ Performance Testing

Files to submit
1.​ Dataset
2.​ Tableau file
3.​ Screeshot of Dashboard
4.​ Screenshot of Story
5.​ Public links of Dashboard and Story
6.​ Final Report
7.​ Video Demonstration

My SQL Workbench
​ Link: [Link]
​ Link: [Link]

Customer Needs,
●​ Quality of Product
●​ Excellent Customer Service
●​ Value for Money
●​ Transparency and Honesty

Decision making Process

1.​ Identify the Problem
2.​ Gather Information
3.​ Generate the Options
4.​ Evaluate the Option
5.​ Select the Best Option
6.​ Implement the Decision

Need of Data Visualization

●​ Simplifies Complexity
●​ Identifies Patterns
●​ Supports Decision
●​ Enhances Communication

Data Analytics- Use of tools, techniques to understand data for better decision making
DA Application
●​ HealthCare
●​ Retail
●​ Finance
●​ Sports
●​ Education

Data Analytics Process

1.​ Define Problem- Set clear objectives and goals
2.​ Data Collection- Gather relevant data from appropriate sources
3.​ Data Cleaning- Prepare and ensure data quality
4.​ Data Preprocessing- Transform and organise data for analysis
5.​ Data Analysis-Apply techniques to derive insights
6.​ Interpretation- Make sense of result and draw conclusion
7.​ Communication- Present insights in clear actionable manner

Types of Analytics
 1.​ Descriptive Analytics- What happened
2.​ Diagnostic Analytics- Why did it happen
3.​ Predictive Analytics- What is likely to happen
4.​ Prescriptive Analytics- What should we do

Supermarket
1hr-20 transaction
10 hr- 200 transaction
30 days-6000 transaction

Small data
Large data

Business Intelligence: Business Intelligence helps to turn raw data into useful information that
business can act on.

Difference between BI tools and Excel

1.​ Scalability
2.​ Data Integration
3.​ Advanced Analytics
4.​ Visualization and Dashboard

Tableau- It was started in 2003 but was acquired by Salesforce in 2019

1.​ Data Connectivity

2.​ Drag and Drop Interface
3.​ Wide range of visualizations
4.​ Dashboard and storytellings
5.​ Sharing and collaboration

Products of Tableau
1.​ Tableau Desktop
2.​ Tableau Public
3.​ Tableau Server
4.​ Tableau Online
For Students– Tableau for Student till 1st Feb 2025
1 year free Tableau Desktop and Tableau Prep

Tableau Desktop Professional Edition( 14 days free trail)

Tableau Prep ( 14 days free trail)
Tableau Desktop- Public Edition ( free of Cost)

Visit : [Link]
Click on get Tableau for Free
Fill the form
Click on Download the app

Mysql
DataBase: Databases are used to store large amount of data in structured format.

Types of Databases:
●​ Relational Databases
●​ Operational Database
●​ Distributed Database
●​ Cloud Database
●​ End User Database

MySQL: Open Source RDBMS by Oracle Corporation

Open Source
Relational Database
Cross Platform
Security
Performance
Community and Support

SQL- Structured Query Language

MySQL Workbench is IDE

MYSQL Edition
●​ MySQL Community Edition
●​ MySQl Standard Edition
●​ MySQL Enterprise Edition
Basic SQl Components
1.​ DDL- Data Definition Language- CREATE, ALTER and DROP
2.​ DML- Data Manipulation Language- INSERT, UPDATE and DELETE
3.​ DQL- Data Query Language- SELECT
4.​ DCL- Data Control Language- GRANT and REVOKE
5.​ TCL- Transaction Control Language

Basic SQL Commands

●​ SELECT- Used to retrieve the data from tables in the database.
●​ INSERT-Used to add new records to a table
●​ UPDATE- Used to modify existing record in table
●​ DELETE- Used to remove record from table
●​ CREATE- Used to create new database objects
●​ ALTER- Used to modify the structure of existing database object.
●​ DROP-Used to delete database objects
●​ GRANT- Used to grant specific privileges to database user
●​ REVOKE- Used to revoke previously granted privileges.

Create database studentdb;

use studentdb;

Comments are of two types

1 Single line Comment -- single line comment
2. Multiline Line Comment
/*------------------------
-----------------*/

Primary Key- Uniquely identifying each row in a table

Foreign Key- referencing another column in another table

CRUD Operation
●​ Create (C)
●​ Read (R)
●​ Update (U)
●​ Delete (D)

Student table
​ Student_Id, Name, age and grade

Create Table:
create Table Students(
student_id int Primary Key auto_increment,
name varchar(50) not null,
age int,
grade varchar(5)
)

Insert Data

Insert into students(name, age, grade) values

('John',20, 'A'),
('Smith',21,'B'),
('Johny',23, 'A'),
('Sam',22,'B'),
('Bob',19,'C');

Read Data

Select * from Students;

Update Data

Update students Set age=21 where name='john';

Delete data
Delete from students where name='Bob';

truncate table students;

alter table students rename column age to years;

SQL Operations
select ABS(-5) as absolute_value;
select round(3.14159) as rounded_value;
select round(3.14159,3) as rounded_value;

Ceil() and floor()

Select Ceil(4.25) as ceil_value;
select floor(4.75) as floor_value;

Power()
select power(4,2);
select power(10,3) as cubes;

Square root()
select sqrt(144);

Exponential()
select exp(1) as exponential_value;

Rand()
select rand() as random_value;

Mod()
select mod(14,3) as remainder;
select greatest(2,5,18,6,12);
select least(2,5,18,6,12);
select truncate(22.89734235,2);

select upper('Indra Prakash') as Upper_case;

select lower('INDRA PRAKASH') as lower_case;

select character_length('India is My Country') as total_length;

select length('India is My Country') as length_of_string;

CONCAT()
select concat('India' ' is' ' in Asia') as merged;

TRIM()
select trim('​ Hello​ ') as Trimmed_String;

Replace()
select replace('Hello World', 'World','Universe') as replaced_string;

Current Date
select current_date as today;
select current_time as time;
select current_timestamp as current_timstamp;

Format()
select date_format(Now(),'%d-%m-%y')as formatted_date;

DateDiff()
select DateDiff('2025-01-01','2024-01-01') as date_difference;

Joins:
Inner Join: Return all matching values in both table
Left Join: Return all records from left table and matching values from right table
Right Join: return all records from right table and matching values from left table
Cross Join:Return all reforms from both table

create database joins;

use joins;

create table cricket_students(

student_id int primary key,
student_name varchar(50)
);

create table football_students(

student_id int primary key,
student_name varchar(50)
);

Insert into cricket_students(student_id, student_name) values

(1,'Raju'),
(2,'Suraj'),
(3,'Mohan'),
(4,'Karan'),
(5,'Virat');

select * from cricket_students;

Insert into football_students(student_id, student_name) values

(2,'Suraj'),
(3,'Mohan'),
(5,'Virat'),
(6,'Alex'),
(7,'Taylor');

select * from football_students;

-- Inner Join
Select * from cricket_students
Inner join football_students
on cricket_students.student_id=football_students.student_id;

-- Inner Join using Alias

Select * from cricket_students as c
Inner join football_students as f
on c.student_id=f.student_id;

-- Left Join
select * from cricket_students as c
Left join football_students as f
on
c.student_id=f.student_id;

-- Right Join
select * from cricket_students as c
Right join football_students as f
on
c.student_id=f.student_id;

Cross Join-
select *
from cricket_students
cross join football_students;

26/5/2025
Dataset link:
[Link]

Segment- Consumer, Corporate and Home Office

Category- Furniture, Office Supply and Technology

Order Date, Segment, Category, Sub - Category

create database superstore;

use superstore;
select * from superstore;
select * from superstore Limit 5 ;

Select `Order Date`,Segment,Category,`Sub-Category`, sales from superstore limit 5;

Count()
select count(*) as `No of records` from superstore;
Sum()
select sum(sales) as Total_sales from superstore;
select round(sum(sales),2) as Total_sales from superstore;

Average()
Select avg(discount) as `Average Discount` from superstore;
Select round(avg(sales),2) as `Average Sales` from superstore;

Min()
select min(sales) as lowest_sales from superstore;

Max()
select max(sales) as Max_sales from superstore;

Rename a Column

alter table superstore

change column `Customer Name` Customer_name varchar(255);

27/5/2025

Where: Filters the rows based on condition before grouping or aggregation

select * from superstore where Category='Furniture';
select * from superstore where Category="furniture" and region="south";
select * from superstore where state="New York" or state="texas";
select * from superstore where not country='United States';

Group By: Group rows that have same values in specified column, often used with aggregate
function.

-- No of Customers in United States

select country,count(*) from superstore
Group By Country;

-​ No of Order by State
select State, count(`Row ID`) as total_customer from superstore
group by State;

-​ No of Orders By region
select region,count(*) from superstore group by region;

Select Count( Distinct `Customer ID`) as Unique_customer

from superstore ;
Select Category , Count(Distinct `Customer ID`) as Unique_Customer
from superstore
group by Category;

– Having: filters group based on aggregate condition ( Used after Group By)

List of States with orders more than 500

Select State, Count(*) as Total_Orders
from superstore group by State
having count(*)>1000;

List of States with sales>100000

select state, round(sum(sales)) as total_sales from superstore
group by state
having total_sales > 100000 ;

List of Customer with Total Sales> 15000

select Customer_Name,round(sum(sales)) as Total_sales
from superstore
group by Customer_Name
having Total_sales>15000;

List of Sub-Category with Average Profit more than $50

select `Sub-category`,round(avg(profit)) as Avg_profit
from superstore
group by(`Sub-Category`)
having Avg_profit > 50;

Order By- Sort the result in ascending of descending order

Select * from superstore order by Sales;
Select * from superstore order by Sales Desc;
Select * from superstore order by `Category`;

Select * from superstore order by Region, Customer_name;

Select `Sub-Category`, round(Sum(sales)) as total_sales

from superstore
group by `Sub-Category`
Order by total_Sales Desc limit 5;

Adding New Columns

Alter table superstore add Revenue int;
update Superstore set Revenue= Sales * Quantity;

Describe Superstore;
Update superstore
set `Order Date`=str_to_date(`Order Date`, '%d-%m-%Y');

alter table superstore

Modify `Order Date` Date;

-- Total Sales and Total Profit for each region in year 2015
select Region,
round(sum(sales)) as Total_sales,
round(sum(profit)) as total_profit
from superstore
where year(`Order Date`)=2015
group by region;

Bottom Sales
-​ Select * from superstore order by Sales limit 5;

Top 5 State and their Sales

Select State, Sales from Superstore
order by Sales Desc limit 5;

To delete a column
alter table superstore
drop column Revenue;
Tableau Prep Builder: It was created for Data Preparation
Tableau Prep was introduced in 2018
Rebranded to Tableau Prep builder in 2019

Operations in Tableau Prep

1.​ Connection: You can take data from multiple sources
2.​ Clean Data:
a.​ Data type Conversion
b.​ Normalization: Standardise formats
c.​ Data Renaming:
d.​ Remove Unwanted Columns
3.​ Transform Data:
a.​ Splitting Columns- US-2002-113456 -> Country Code, Year, Ser No
b.​ Joining Data-
c.​ Unioning Data
d.​ Aggregation
e.​ Pivoting
 f.​ Calculating New Fields
g.​ Sorting and Filtering
4.​ Handling Null Values

Tableau Prep: [Link]

Dataset Link: [Link]

Order Central- Separate Columns for Year, month and day, Region field is missing

Orders west- State in abbreviations

Orders east- USD with Sales values

Central:

Calculation Field1:
Name- Region
Expression- ‘Central’

Calculation Field 2
Name- Order Date
Expression- MAKEDATE([Order Year],[Order Month],[Order Day])

Calculation filed 3
Name- Ship Date
Expression-MAKEDATE([Ship Year],[Ship Month], [Ship Day])

Remove fields: Order Year, Order Month, Order Day, Ship Year, Ship Month. Ship Day

In Discount we have replace None with 0 and Changed datatype to Number Decimal

West:

Az- Arizona
CA- California
CO- Colorado
ID- Idaho
MT-Montana
NV- Nevada
NM- New Mexico
OR-Oregon
UT-Utah
WA-Washington
WY- Wyoming

East- Click on Sales> Clean> Remove Letters

Change type to decimal

In Return Reason:

Remove - Row_ID, Order Date,Sub- Category, Manufacturer and Product Name

Notes>Clean> Trim Spaces

Notes> Split Values> Automatic Split

Click on Notes-Split2> Group> Common Characters

Remove the Notes

Rename Note Split1- Customer Notes

Rename Notes Split 2 - Approver Name

Join- Inner Join, Left Join, Right Join

Orders Table (Left Table)

Return Table(Right Table)

Join Return table with orders table and perform left join between them as we want all orders and
only return data

Click on + to add clean step

Remove Order ID1

Remove Product ID1

Calculation field
Returned
If ISNULL([Return Reason]) then 'No'
else 'Yes' End

Calculation Field
Days to Ship
DATEDIFF('day',[Order Date],[Ship Date])

3/6/2025

Tableau Desktop Public Edition

Connect with Data- Sample Superstore

Dataset Link:
[Link]
=sharing&ouid=113247709954189786236&rtpof=true&sd=true

Calculation Field
Revenue
[Sales]*[Quantity]-[Discount]

When a user connects data to tableau, data fields are automatically assigned a role and a type

Qualitative Data vs Quantitative Data

Qualitative Data-
​ Describes or categories the data
​ Cannot Perform Calculations like Sum, Average, Mean

Quantitative Data
​ Numerical Data
​ Can be used for Calculations

Field Data type is a type, example- String, Integer, Date

Fields are assigned 2 kinds of role

1.​ Dimension
2.​ Measure
Tableau Autogenerates 1 Dimension(Measure Names) and 4 Measures( Latitude, Longitude,
No of Records and Measure Values)

Tableau File Types:

[Link]

4/6/2025
Bar Chart:
Average Sales for Different Sub-Category
C- Sub Category
R- Avg(Sales)
Col- Sub- Category

Stack Bar Chart

Segment- Consumer, Corporate and Home Office

Category→ Sub-Category→ Product​
C- Segment
R- Sales
Col- Category
Side By Side Bar Chart
C- Category, Category
R- Sales
Col- Category

Line Chart
C- Month
R- Sales
Col- Sales ( Red-Green Diverging)
Label- Sales

Area Chart
C- Month
R- Profit
Col- Segment
Heat Map
C- Segment
R- Sub Category
Col- Profit
Size- Sales

Bubble Chart
C- Sub - Category
R- Revenue
Click on Show Me and Select Packed Bubbles
Col- Category
In filter → Region→ Select a Region randomly> Click Ok→ Rt click on Region Filter and Select
Show Filter
Pie Chart
C- Region
R- Profit
Click on Show me and Select Pie Chart
Drag and Drop Region on labels
Drag and Drop Profit on Labels

Rt click on Profit label and Select Quick table calculation → Percentage of total
Column- Category
Box Plot
Ref Link: [Link]

Scatter Plot
Ref Link : [Link]

Tree Map
C- State
R- Sales
Click on Show me and Select Tree Map
Col- Region

Bullet Chart
Create Calculation Field
Target= Sales *1.1
C- Sales
R-Sub Category
Select Target and select Bullet Chart in Show me
Rt Click on Axis and Select swap reference line

Word Cloud
Drag and Drop Sub Category to text
Drag and drop profit to size
Select the Text in the Mark Card Dropdown

Text table

Highlight Table
C- Quarters
R- Sub Category
Label- Profit
Donut Chart

Create a pie chart with Column as Segment and Rows with Sales
In Rows pill type avg(0) and hit Enter, Repeat it One more time
Remove all the pills from 2nd Agg Avg(0) in Marks section
Go to Second Agg Avg(0) in rows > Click on drop down and select dual axis
Add white color in 2nd Agg avg(0) in marks section
Pareto Chart
Sets
Sets can be either In Set or Out Set
●​ Static Set
●​ Dynamic Set
●​ Combined Set

Filters

1.​ Extract Filter

2.​ Data Source Filter
3.​ Context Filter
4.​ Dimension Filter
5.​ Measure Filter

Parameters in Tableau
1.​ Top N and Bottom N Parameter
2.​ Date Field Parameter
CASE [DateParameter]
WHEN 'Year' THEN STR(YEAR([Order Date]))
WHEN 'Quarter' THEN 'Q' + STR(DATEPART('quarter', [Order Date]))
WHEN 'Month' THEN DATENAME('month', [Order Date])
WHEN 'Week' THEN STR(DATEPART('week', [Order Date]))
WHEN 'Day' THEN STR(DATEPART('day', [Order Date]))
END

3.​ Dynamic Dimension

CASE[DmensionParameter]
when "Category" Then [Category]
when "Sub-Category" THEN [Sub-Category]
WHEN "Segment" THEN[Segment]
END

4.​ Dynamic Measure

CASE [MeasureParameter]
When "Sales" then [Sales]
when "Profit" then [Profit]
when "Discount" then [Discount]
When "Quantity" then [Quantity]
end

5.​ Dynamic Dimension and Measure

6.​ Reference Line Parameter
7.​ Sets
Hierarchies
Hide/UnHIde
Joins
Cross DataBase Joins
Data Blending
Folders
Groups

Quick Table Calculation

1.​ Running Total: Computes the cumulative sum of measure over the dimension
2.​ Percent of Total:Calculates the percentage contribution of each datapoint to the
total
3.​ Difference: Calculate the difference between two consecutive data point
4.​ Percent Difference: Calculate the percentage difference between two consecutive
data points
5.​ Moving Average: Computes average of measure over moving window of
datapoints
6.​ Percentile: It allows to calculate the value of specified percentile for a given
measure
7.​ Rank: It gives rank to each datapoint within the partition based on measure value
8.​ YTD (Year to Date): Computes the cumulative sum of measure from beginning of
the year upto current data point
9.​ YTD Growth:Calculates the percentage change in measure from the beginning of
the year upto current data point
10.​YOY (Year over Year)- Percentage change in measure compared to the same
period in previous year
 11.​CAGR- Measure annual growth rate over specified period of time

Analytics Pane
1.​ Constant Line
2.​ Average
3.​ Total
4.​ Cluster
5.​ Forecast
6.​ Trend Line
7.​ Reference line

LOD Expressions- Level of Detail

Lets you control the granularity (detail level) at which the calculation happens,
independent of what's shown in the visualization

Granularity??

Country> States>District>Cities

1.​ What is level you need

2.​ What is aggregation?

Type:
[Link] LOD
2. Exclude LOD
3. Include LOD

Syntax:
{type[Dim] : Aggregate }
1.​ Fixed LOD:Calculates the values at specific level of detail ignoring the view of
dimension
{ fixed[Dimention]: Aggregation}

2.​ Exclude LOD : Removes the specific dimension from the calculation, aggregating
at higher level
{Exclude[Dim]: Aggregation}

3.​ Include LOD: Adds more detail in the existing view, considering addition
dimension.
{Include[Dim]: Aggregation}

1.​ Total Sales- {Fixed:SUM([Sales])}

2.​ Regional Sales: { EXCLUDE [State/Province]: SUM([Sales])}
3.​ Average Sales by Customer : AVG({INCLUDE[Customer ID]: SUM([Sales])})
You need to create account on [Link]
Remember your Username and Password

Go to specific Dashboard> Files> Save to Tableau public as> Provide your Tableau Public
Credentials> Give a Name

Share the Tableau Public link for Both Dashboard and Story.