Đại học Kinh tế Quốc dân – National Economics UniversityMICRO4ASIA
🏆 Arena
Sign inSign up
MICRO4ASIAThe six micro-credentials

Courses are delivered on the Limio learning platform. Limio

← Data Visualization in Business

You are viewing a public preview

Sign in to save your progress, do assignments, join discussions and get feedback.

Sign in
Module 2 — Data Connection and Preprocessing in Tableau

2.1 · Data Structure and Data Models

Print / Save as PDF

In this lesson

Tabular data organization; tables, fields, records and data types; primary and foreign keys; levels of detail; one-to-one, one-to-many and many-to-many relationships; structuring data for analysis.

Learning outcomes

  • LO.3: Organize tabular data, identify fields and data types, set keys and relationships, and connect suitable data sources.

The lecture slides for this lesson are below.

Study notes

By the end of this lesson you can:

  • Describe a data model in terms of entities, tables, fields, records, data types and keys.
  • Identify the level of detail (grain) of a table and explain why it matters for aggregation.
  • Explain one-to-one, one-to-many and many-to-many relationships and check a key before combining tables.
  • Choose between inner, left, right and full outer joins, union and pivot for a combining or restructuring task.

Key idea: Reliable charts start with well-structured data. If you know what one row means and how tables link through keys, you avoid double counting, missing records and wrong totals.

From business systems to analytical tables

Business data come from many operational systems (sales, customer management, inventory), and these may use different names, formats and levels of detail. Good organisation means identifying:

  • Entities: the things or events you describe, such as customers, products and orders.
  • Attributes: the properties of each entity, such as a customer's Segment or a product's Category.
  • Record-level detail: what exactly one row represents.
  • Keys and relationships: how tables connect.

Poor structures lead to double counting, incorrect aggregation, missing records and inconsistent results.

Tables, fields, records and data types

A database contains related tables, and each table describes one entity or one kind of business event. Rows are records; columns are fields (attributes). The slides use the Orders table of Sales_Data.xlsx as the reference: Row ID uniquely identifies each row (the primary key), while Customer ID and Product ID are foreign keys that link to the Customer and Product tables.

Each field needs a consistent data type. Tableau uses String, Number (whole or decimal), Date and Date & Time, Boolean, and geographic roles for fields such as Country or Province. A wrong type causes problems later: a date stored as text cannot be put on a time axis, and a number stored as text cannot be summed.

Level of detail

The level of detail (or grain) is what one record represents. In the course Orders table, one row is one product line of an order, so one order with three products has three rows. The Customer table has one row per customer, the Product table one row per product, and the Targets table one row per Region and month.

Grain decides how you may aggregate. Summing Sales over Orders rows is correct. Counting rows gives the number of order lines, not the number of orders; for orders you count distinct Order ID values.

Relationships and linking keys

TypeMeaningExample
One-to-one (1:1)Each record matches at most one record in the other tableEmployee and employee's company car record
One-to-many (1:N)One record links to many recordsOne customer has many order lines
Many-to-many (N:M)Many records link to many in both directionsProducts and promotions, where a product can be in several promotions and a promotion covers several products

Before combining tables, check four things: the key has the same meaning in both tables, the data types are compatible, the values are standardised (no "C-001" versus "c001"), and the key is unique on the "one" side. A duplicated key on the "one" side silently multiplies rows.

Combining and restructuring data

  • Inner join keeps only records that match in both tables.
  • Left join keeps all records of the left table plus matches from the right; unmatched right fields become empty (NULL). Right join is the mirror image.
  • Full outer join keeps all records from both tables.
  • Union appends tables with compatible structures row by row, for example the same order table for two different years.
  • Wide format stores repeated attributes in columns (one column per month); long format stores them in rows (one row per month). A pivot converts between the two. Tableau works best with long data, because "Month" becomes one field you can put on an axis.

Joins and relationships combine columns from different tables; a union adds rows.

Worked example

Model the four tables of the course dataset before building any chart.

  1. Grain. Orders: one row per order line. Customer: one row per customer. Product: one row per product. Targets: one row per Region and month.
  2. Keys. Orders.Customer ID → Customer.Customer ID and Orders.Product ID → Product.Product ID. On the Customer and Product side these keys must be unique.
  3. Relationship types. Customer to Orders is 1:N; Product to Orders is 1:N.
  4. Choose a join for a question. "Which customers have never ordered?" needs every customer, matched or not, so Customer left join Orders on Customer ID, then look for rows where Order ID is NULL. An inner join would drop exactly those customers.
  5. Spot a grain mismatch. Targets are stored per Region and month, but Orders are per order line. If you joined Targets to Orders on Region, every order line would carry the full monthly target, and summing the target would multiply it by the number of order lines. The fix is to compare at the same grain: aggregate Sales to Region and month first, or let Tableau relate the tables (lesson 2.2) so each table keeps its own level of detail.
  6. Restructure. In the raw file the targets come as one column per month (wide). A pivot turns them into Region, Month, Sales Target rows (long), which Tableau can filter and chart by month.

Common mistakes

  • Counting rows as orders. In a line-level table, use a distinct count of Order ID for the number of orders.
  • Joining on a non-unique key. If a customer appears twice in the Customer table, every one of their order lines is duplicated after the join.
  • Joining tables at different grains and then summing. Targets or budgets get multiplied. Aggregate first or use relationships.
  • Using union when you need a join. Union stacks rows; it does not add a customer's Segment to an order.

Summary

  • A good data model defines entities, attributes, grain, keys and relationships.
  • Primary keys identify records; foreign keys link tables.
  • The level of detail of a table decides how measures may be aggregated.
  • Check key meaning, type, standardisation and uniqueness before combining tables.
  • Joins and relationships add columns, unions add rows, and pivots switch between wide and long formats.

Flashcards · Data Structure and Data Models

9 words
WordMeaningExample
  • Primary key

    A field whose value uniquely identifies each record in a table.

    Row ID in the Orders table.

  • Foreign key

    A field in one table that refers to the primary key of another table.

    Customer ID in Orders links to Customer.

  • Level of detail (grain)

    What a single record in a table represents; it determines how measures may be aggregated.

    Orders: one row per product line of an order.

  • One-to-many relationship

    One record in a table links to many records in another table.

    One customer, many order lines.

  • Many-to-many relationship

    Many records link to many records in both directions, often needing a bridge table.

    Products and promotions.

  • Left join

    Keeps all records of the left table and matching records from the right; non-matches get NULL.

    Customer left join Orders finds customers who never ordered.

  • Union

    Appends records of tables with compatible structures row by row.

    Orders 2023–2024 stacked with Orders 2025.

  • Pivot

    Restructures data between wide format (values in columns) and long format (values in rows).

    Twelve month columns become Month and Sales Target fields.

  • Double counting

    Inflated totals caused by duplicated rows after joining on a non-unique key or mismatched grain.

    Monthly target repeated on every order line.