Data model best practices in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst at a retail company.

📋 Request: Your manager wants you to create a clean and efficient data model in Tableau to analyze sales performance by product category and region.

📊 Data: You have sales transaction data with columns: Order ID, Product ID, Product Category, Region, Sales Amount, Order Date.

🎯 Deliverable: A Tableau data model with proper relationships and calculated fields that supports fast and accurate sales analysis.

Sample Data

Order ID	Product ID	Product Category	Region	Sales Amount	Order Date
1001	P01	Electronics	North	250	2024-01-15
1002	P02	Furniture	South	450	2024-01-17
1003	P03	Electronics	East	300	2024-01-20
1004	P04	Clothing	West	150	2024-01-22
1005	P05	Furniture	North	500	2024-01-25
1006	P06	Clothing	South	200	2024-01-28
1007	P07	Electronics	East	350	2024-02-01
1008	P08	Furniture	West	400	2024-02-03

1

Step 1: Connect the sales transaction data to Tableau as the main data source.

Use 'Text File' or 'Excel' connection to import the sales data table.

Expected Result

Sales data is loaded and visible in Tableau's Data pane.

2

Step 2: Create separate dimension tables for Product and Region to build a star schema.

Extract unique Product IDs and Product Categories into a Product dimension table. Extract unique Regions into a Region dimension table.

Expected Result

Two new tables: Product dimension with Product ID and Category, Region dimension with Region names.

3

Step 3: Establish relationships between the sales fact table and the dimension tables.

In Tableau's Data Model, link Sales.Product ID to Product.Product ID, and Sales.Region to Region.Region.

Expected Result

Data model shows relationships connecting fact and dimension tables.

4

Step 4: Create a calculated field for Total Sales in the sales fact table.

Create calculated field named 'Total Sales' with formula: SUM([Sales Amount])

Expected Result

Total Sales measure is available for use in visualizations.

5

Step 5: Build a simple dashboard showing Total Sales by Product Category and Region.

Create a bar chart with Columns = Product Category, Rows = Region, and Values = Total Sales measure.

Expected Result

Dashboard displays sales totals grouped by product category and region.

6

Step 6: Optimize the data model by hiding unnecessary fields and disabling unused data source filters.

In Data pane, right-click and hide fields not needed for analysis. Remove any default filters that limit data.

Expected Result

Cleaner data model with faster performance and easier navigation.

Final Result

---------------------------------------------
| Product Category | North | South | East | West |
|------------------|-------|-------|------|------|
| Electronics      | 250   |       | 650  |      |
| Furniture        | 500   | 450   |      | 400  |
| Clothing         |       | 200   |      | 150  |
---------------------------------------------

✓Electronics sales are strongest in the East region.

✓Furniture has consistent sales across all regions.

✓Clothing sales are lower compared to other categories.

Bonus Challenge

Add a date dimension table and create a calculated field to analyze sales trends by month.

Show Hint

Create a Date dimension with Year and Month columns. Link Order Date from sales to Date dimension. Use MONTH(Order Date) and YEAR(Order Date) for grouping.