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.

Practice

1. Which data model structure is recommended in Tableau for better performance and clarity?

easy

A. Randomly joined tables without keys

B. Flat table with all data combined

C. Snowflake schema with many nested joins

D. Star schema with clear fact and dimension tables

Solution

1. Step 1: Understand common data model types

   Star schema organizes data into fact and dimension tables, simplifying relationships.

2. Step 2: Identify best practice for Tableau

   Tableau performs best with star schema due to clear joins and simpler queries.

3. Final Answer:

   Star schema with clear fact and dimension tables -> Option D

4. Quick Check:

   Star schema = Best practice [OK]

Hint: Choose star schema for clear, fast Tableau models [OK]

Common Mistakes:

• Confusing snowflake schema as better
• Using flat tables causing slow performance
• Ignoring relationship clarity

2. Which of the following is the correct way to define a relationship between tables in Tableau's data model?

easy

A. Using a calculated field to join unrelated columns

B. Joining tables without any common columns

C. Creating a relationship on matching key columns

D. Using multiple joins on non-key columns

Solution

1. Step 1: Identify how relationships work in Tableau

   Relationships require matching key columns to link tables logically.

2. Step 2: Evaluate options for correct syntax

   Only creating relationships on matching keys ensures correct data blending and filtering.

3. Final Answer:

   Creating a relationship on matching key columns -> Option C

4. Quick Check:

   Relationships need matching keys [OK]

Hint: Always link tables on matching keys [OK]

Common Mistakes:

• Joining on unrelated columns
• Using calculated fields as join keys
• Ignoring key columns in relationships

3. Given a star schema with a fact table 'Sales' and dimension table 'Products', what happens if you join them on a non-unique column in 'Products'?

medium

A. The join filters out unmatched sales rows

B. The join duplicates sales rows, inflating totals

C. The join returns only unique sales rows

D. The join causes a syntax error in Tableau

Solution

1. Step 1: Understand join behavior with non-unique keys

   Joining on non-unique keys duplicates fact rows for each matching dimension row.

2. Step 2: Predict impact on sales totals

   Duplicated rows inflate aggregated sales, causing incorrect totals.

3. Final Answer:

   The join duplicates sales rows, inflating totals -> Option B

4. Quick Check:

   Non-unique join keys cause duplicates [OK]

Hint: Check uniqueness of join keys to avoid duplicates [OK]

Common Mistakes:

• Assuming join filters data instead of duplicating
• Thinking Tableau throws errors on such joins
• Believing totals remain accurate despite duplicates

4. You created a relationship between 'Orders' and 'Customers' tables in Tableau, but your report shows incorrect totals. What is the most likely cause?

medium

A. The relationship uses non-matching key columns

B. The data source is missing required columns

C. The relationship is set as a join instead of a relationship

D. The tables have no data at all

Solution

1. Step 1: Analyze relationship setup

   Incorrect totals often result from relationships on columns that don't match properly.

2. Step 2: Check relationship keys

   If keys don't match, Tableau can't correctly link data, causing wrong aggregations.

3. Final Answer:

   The relationship uses non-matching key columns -> Option A

4. Quick Check:

   Non-matching keys cause incorrect totals [OK]

Hint: Verify keys match exactly in relationships [OK]

Common Mistakes:

• Confusing joins with relationships
• Ignoring missing columns
• Assuming empty tables cause totals errors

5. You have a complex data model with multiple fact tables and dimension tables. To improve performance and clarity in Tableau, what is the best approach?

hard

A. Create a star schema by consolidating facts and linking dimensions clearly

B. Join all tables into one large flat table

C. Use multiple snowflake schemas with deep nested joins

D. Avoid relationships and use calculated fields to combine data

Solution

1. Step 1: Assess complex data model issues

   Multiple fact tables and complex joins slow performance and confuse users.

2. Step 2: Apply best practice for simplification

   Consolidating facts and using star schema with clear dimension links improves speed and clarity.

3. Step 3: Avoid approaches that increase complexity

   Flat tables or snowflake schemas with deep joins reduce performance and maintainability.

4. Final Answer:

   Create a star schema by consolidating facts and linking dimensions clearly -> Option A

5. Quick Check:

   Star schema consolidation = Best for complex models [OK]

Hint: Simplify complex models into star schema for best results [OK]

Common Mistakes:

• Flattening all tables causing slow queries
• Using deep nested joins increasing complexity
• Relying on calculated fields instead of relationships