Adventure works Progress Task

Complete Roadmap

We will complete it in this order.

Phase 1

Excel (Data Cleaning + Calcula ons)

Union Data

VLOOKUP/XLOOKUP

Date Fields

Sales Amount

Produc on Cost

Profit

Pivot Tables

Charts

Phase 2

MySQL Workbench

Import all Excel sheets

Create tables

Primary Keys

Foreign Keys

Joins

Views

SQL Queries

Phase 3

Tableau

Import Data

Rela onships

Calculated Fields
Dashboard

Filters

KPIs

Phase 4

Power BI

Power Query

Data Model

DAX

Dashboard

KPIs

Interac ve Reports

STEP 1 : Understand Dataset

You have these files.

File Purpose

FactInternetSales Main Sales Data

Fact_Internet_Sales_New New Sales Data

DimCustomer Customer Details

DimProduct Product Details

DimDate Date Table

DimSalesTerritory Region Details

DimProductCategory Category

DimProductSubCategory Subcategory

Fact tables store transac ons, while dimension tables provide descrip ve informa on about
products, customers, dates, and territories.

STEP 2

Excel
Task 0

Union FactInternetSales + Fact_Internet_Sales_New

Open both files.

Copy all rows from

Fact_Internet_Sales_New

Paste below

FactInternetSales

Now rename sheet

Sales

Done.

Task 1

Lookup Product Name

Sales sheet contains

ProductKey

Product sheet contains

ProductKey

ProductName

Use

=XLOOKUP(A2,

DimProduct!A:A,

DimProduct!B:B,

"Not Found")

OR

=VLOOKUP(A2,

DimProduct!A:B,

2,

FALSE)

New Column

ProductName
Task 2

Customer Full Name

Customer Sheet

Contains

CustomerKey

FirstName

LastName

Create

=FirstName&" "&LastName

Name it

CustomerFullName

Now lookup

=XLOOKUP(CustomerKey,

DimCustomer!A:A,

DimCustomer!FullNameColumn)

Unit Price

Lookup from Product

=XLOOKUP(ProductKey,

DimProduct!A:A,

DimProduct!UnitPriceColumn)

Task 3

Date Calcula ons

OrderDateKey

Looks like

20050701

Need to convert

Use

=DATE(LEFT(A2,4),

MID(A2,5,2),
RIGHT(A2,2))

Now create

Year

=YEAR(Date)

Month Number

=MONTH(Date)

Month Name

=TEXT(Date,"mmmm")

Quarter

="Q"&ROUNDUP(MONTH(Date)/3,0)

Year Month

=TEXT(Date,"yyyy-mmm")

Weekday Number

=WEEKDAY(Date)

Weekday Name

=TEXT(Date,"dddd")

Financial Month

Assume FY starts in July

=IF(MONTH(Date)>=7,

MONTH(Date)-6,

MONTH(Date)+6)

Financial Quarter
=CHOOSE(

ROUNDUP(FinancialMonth/3,0),

"Q1","Q2","Q3","Q4")

Task 4

Sales Amount

Formula

Sales Amount

Unit Price

Order Quan ty

(1-Unit Discount)

Excel

=UnitPrice*OrderQuan ty*(1-UnitDiscount)

Task 5

Produc on Cost

=UnitCost*OrderQuan ty

Task 6

Profit

=SalesAmount-Produc onCost
Task 7

Pivot Table

Insert

Pivot Table

Rows

Month

Values

Sales Amount

Filter

Year

Done.

Task 8

Year Wise Sales

Insert

Bar Chart

Axis

Year

Values

Sales Amount

Task 9

Month Wise Sales

Insert

Line Chart

Axis

Month

Value
Sales Amount

Task 10

Quarter Sales

Insert

Pie Chart

Quarter

Sales Amount

Task 11

Combo Chart

Axis

Year

Columns

Sales Amount

Line

Produc on Cost

Task 12

Extra KPIs

Top 10 Products

Top Customers

Sales by Region

Profit %

Average Order Value

Total Orders

Sales by Category

Sales by SubCategory

Profit by Territory

Customer Count
Task 13

Dashboard

Use Slicers

Year

Month

Region

Category

Customer

KPI Cards

Total Sales

Total Profit

Produc on Cost

Orders

Charts

Bar

Pie

Line

Map

Treemap

Donut

MySQL Workbench

We'll recreate the same project using SQL.

Import Excel

Each Excel sheet becomes a table:

 FactInternetSales

 Fact_Internet_Sales_New

 DimCustomer

 DimProduct

 DimDate
  DimSalesTerritory

 DimProductCategory

 DimProductSubCategory

SQL Tasks

1. Union Sales Tables

CREATE TABLE Sales AS

SELECT * FROM FactInternetSales

UNION ALL

SELECT * FROM Fact_Internet_Sales_New;

2. Product Name

SELECT

s.*,

[Link]

FROM Sales s

LEFT JOIN DimProduct p

ON [Link]=[Link];

3. Customer Name

SELECT

FirstName,

LastName,

CONCAT(FirstName,' ',LastName)

FROM DimCustomer;

4. Sales Amount

UnitPrice*OrderQuan ty*(1-UnitPriceDiscountPct)

5. Produc on Cost

StandardCost*OrderQuan ty

6. Profit

SalesAmount-Produc onCost

We'll also create SQL views for repor ng so Tableau and Power BI can consume clean data.

Tableau
Connect

Open Tableau → Connect to Microso Excel.

Load all tables.

Rela onships

Sales

ProductKey

DimProduct

CustomerKey

DimCustomer

OrderDateKey

DimDate

SalesTerritoryKey

DimSalesTerritory

Calculated Fields

Sales Amount

SUM([Unit Price]*[Order Quan ty]*(1-[Discount]))

Produc on Cost

SUM([Unit Cost]*[Order Quan ty])

Profit

[Sales Amount]-[Produc on Cost]

Dashboard

Include:

 KPI cards (Sales, Profit, Cost, Orders)

 Region filter

 Product filter

 Bar chart: Year vs Sales

 Line chart: Month vs Sales

 Pie chart: Quarter vs Sales

 Treemap: Category

 Map: Sales Territory

 Top 10 Products

 Top Customers

Power BI

Power Query

 Load all Excel files.

 Append FactInternetSales and Fact_Internet_Sales_New.

 Merge Product to fetch Product Name and Unit Price.

 Merge Customer to fetch Customer Full Name.

 Create the addi onal date fields if required.

Model

Create one-to-many rela onships from Sales to each dimension table exactly as shown in the project
document.

DAX Measures

Sales Amount =

SUMX(

Sales,

Sales[Unit Price]*

Sales[Order Quan ty]*

(1-Sales[Unit Discount])

Produc on Cost =

SUMX(
Sales,

Sales[Unit Cost]*

Sales[Order Quan ty]

Profit =

[Sales Amount]-[Produc on Cost]

Profit % =

DIVIDE([Profit],[Sales Amount])

Total Orders =

COUNTROWS(Sales)

Recommended Dashboard

 KPI Cards: Total Sales, Profit, Produc on Cost, Orders, Profit %

 Clustered Bar: Year-wise Sales

 Line: Month-wise Sales

 Pie/Donut: Quarter-wise Sales

 Combo Chart: Sales vs Produc on Cost

 Treemap: Category

 Map: Sales by Territory

 Top 10 Products

 Top 10 Customers

 Slicers: Year, Month, Category, Region, Customer

I can guide you through this like a live classroom

We'll complete it in 10 prac cal sessions:

1. Excel – Data cleaning, XLOOKUP, formulas, PivotTables, charts

2. MySQL – Import Excel, create tables, UNION, JOINs, views

3. Power BI (Power Query) – Append, Merge, Transform

4. Power BI (Data Modeling) – Rela onships and star schema

5. Power BI (DAX) – Measures and calculated columns

6. Power BI Dashboard – Professional interac ve report

7. Tableau – Data model and worksheets

9. Valida on – Cross-check Excel, SQL, Tableau, and Power BI results

10. Project Presenta on – How to explain the project confidently in interviews.

This will closely mirror a real-world business intelligence project and give you experience across the
full analy cs stack.