Skip to content

3.3 How Dimensions Work Together

Here are some fundamental traits of dimensions:

  • Units Inside a Dimension Are Always Related. Inside a dimension, dimensional units relate to each other through parent-child relations that correspond to foreign keys in the underlying table structure.
  • The Atomic Unit. Each dimension has an atomic unit that represents the most detailed level in that dimension and typically carries the same name as the dimension itself. For example Customer in the Customer dimension is the child of Customer Group, Customer Country, Customer Payment Terms etc.
  • Business dimensions are Flat. Business dimensions such as Customer, Item and Vendor are usually flat, meaning most parent-child relation chains only have two levels, e.g. "Customer Country" > "Customer", or "Customer Group" > "Customer".
  • Date Dimensions Are Multi-level. Date dimensions on the other hand are usually deep, with multi-level parent-child relations, e.g. "Year" > "Quarter" > "Month" > "Date".
  • Dimensions Relate Through Facts and Metrics. Different dimensions don't connect to each other directly—they connect through the metrics you analyze. When you ask "Show me turnover by customer and item," that works because Turnover comes from sales facts that include both Customer and Item. Choose a different metric (like G/L Amount), and that combination might not work.

This chapter explains how metrics bind dimensions through facts, and why some combinations work while others don't.

Metrics Bind Dimensions Through Facts

When you choose a metric, you're choosing the underlying facts it aggregates. Those facts determine which dimensions can meaningfully combine.

Example: Turnover metric - Comes from: Sales Invoice Line and Header facts - Those facts include: Customer, Date, Item, Location, Salesperson - You can combine: Any of those dimensions together

Example: G/L Amount metric - Comes from: General Ledger Entry facts - Those facts include: G/L Account, Date, Department - You cannot combine: Customer + Item (G/L facts don't include these)

[Conceptual diagram showing Sales Invoice facts connecting to Customer, Date, Item, Location, Salesperson dimensions]

Valid and Invalid Combinations

Works: Turnover with Customer + Item + Date Sales facts connect all three dimensions.

Doesn't work: G/L Amount with Customer + Item G/L facts don't include customers or items.

Works: Inventory Value with Item + Location Inventory facts connect these dimensions.

The dimensional model knows which dimensions connect through which facts. If you try combinations that don't share facts, you'll get metric values repeated for each value of a dimension that doesn't related to the metric.

Two Types of Relationships

Within a dimension (parent-child hierarchies): - Customer Country → Region → City → Customer - Always work together, regardless of metric - Based on foreign keys in the dimension table itself

Across dimensions (fact-based): - Customer ↔ Item, Customer ↔ Date - Only work when the metric's facts include both - Depend on which metric you choose

You can always combine Customer Country and Customer City—they're in the same dimension. Combining Customer and Item only works when using metrics whose facts include both (like Turnover).

Mixed-Grain Analysis: Different Metrics, Different Dimensions

Here's where it gets useful: You can combine metrics that relate to different dimensions in the same visual.

Example: - Dimensions: Customer, Sales Order, Item - Metric 1: Sales Order Amount (relates to all three) - Metric 2: Inventory Qty (relates to Item only)

Datashop processes each metric independently, then merges results. Inventory Qty appears the same across all Customer/Order combinations for each item—because inventory doesn't vary by customer or order:

Customer Sales Order Item Sales Amount Inventory
ACME SO-001 Widget A $1,000 50
ACME SO-002 Widget A $500 50
XYZ SO-003 Widget A $750 50

The repeated inventory value is intentional—it's item-level reference data shown alongside order-level sales data. Users understand this and find it useful.

Common patterns:

  • Sales orders with current inventory levels
  • Customer performance with company-wide targets
  • Regional sales with national averages

Even "meaningless" combinations like Customer + Inventory Qty work technically (inventory repeats for every customer), but they're not useful for analysis.

[!CAUTION]

Although Datashop doesn't prevent you from creating cartesian products, you should still be careful and avoid them when possible because they can lead to heavy processing on your database system. For example a cartesian product between Item and Customer will generate a result with all combinations of Item and Customer. With 10,000 Items and 10,000 Customers that would result in 10,000 * 10,000 = 100 million rows. Processing that much data can stress your database server.

Filtering Within Context

Filters work within your chosen metric's context. When you filter to "Q2 2024" and "Western Europe," Datashop includes only facts matching both criteria—but only facts relevant to your metric.

If you're analyzing Turnover (sales facts), Customer filters work. If you're analyzing Inventory Value (inventory facts), Customer filters have no effect because inventory facts don't connect to customers.

Handling Real-World Complexity

ERP systems have complex structures: transactional headers and lines, multi-company data, intricate relationships. Acinta's Extended Multidimensional Model (xMDD) handles these scenarios automatically.

When you analyze Items by Customer using Turnover, xMDD knows to join through Invoice Line → Invoice Header → Customer, even though Customer isn't directly on the line. These capabilities are configured in the Modeller tool by your data team—once set up, they work transparently.

We explore xMDD features in detail in Chapter 3.5 for those interested.

Tips – How Dimensions Work Together

  • Think metric first: The metric determines which dimensions can combine meaningfully.
  • Mixed-grain analysis is powerful: Combine metrics at different dimensional levels for useful context.
  • Repeated values are often intentional: If a metric doesn't vary by a dimension, it provides reference information.
  • Within-dimension hierarchies always work: Customer Country + Customer City work together regardless of the metric.

Next Steps

Ready to explore the dimensions available in your data model? Learn how to navigate: 3.4 Exploring Your Data Model