3.5 Real-World Complexity (Acinta's Extended Model)

[!NOTE] This chapter is optional. If you're new to Datashop or focused on business analysis rather than technical details, you can skip this section and move directly to Chapter 4 (The Datashop Main Window) or Chapter 5 (Working with Workbooks). The features described here work automatically—you benefit from them without needing to understand how they work.

Standard dimensional models work well with clean, simple data structures. Real-world ERP systems are rarely clean or simple. They have transactional headers and lines, multi-company structures, complex join requirements, and data spread across dozens of tables with intricate relationships.

Acinta's Extended Multidimensional Model (xMDD) handles these real-world complexities so you don't have to. This chapter explains the advanced features that make Datashop work seamlessly with production ERP systems like Microsoft Business Central, particularly when the dimensional model is defined directly on the ERP's database schema rather than a simplified data warehouse.

These features are configured in the Modeller tool by your data team. As a Datashop user, you simply benefit from them. Understanding what's happening behind the scenes can help you appreciate why Datashop "just works" in scenarios that would break simpler tools.

Dependent Dimensions

The Problem

In ERP systems, transactional data typically has header/line structures. For example: - A Sales Invoice Header contains Customer, Date, Payment Terms, Salesperson - A Sales Invoice Line contains Item, Quantity, Amount, but not Customer or Date

If you want to analyze items by customer, you need to join through both the header and the line. Traditional dimensional models struggle with this because they expect direct relationships between dimensions and facts.

The Solution

Dependent dimensions allow Datashop to establish join paths through intermediate tables automatically. The Sales Invoice dimension is marked as "dependent," meaning it has a header/line structure. When you analyze Invoice Lines by Customer, Datashop: 1. Recognizes that Customer is on the Invoice Header, not the Line 2. Automatically joins Invoice Line → Invoice Header → Customer 3. Presents the results seamlessly

You just drag Customer and Item into a visual. Datashop handles the multi-table join transparently.

[SCREENSHOT/DIAGRAM: Simple flow showing Invoice Line → Invoice Header → Customer join path]

Why It Matters

Without dependent dimensions, you'd need separate, pre-joined views or denormalized tables where every line includes all header information. This creates data redundancy, maintenance headaches, and performance issues. Dependent dimensions let the model work directly on the normalized ERP schema.

User impact: You can analyze transactional details (like Items) by header attributes (like Customer) without ever thinking about the underlying table structure.

Partitioned Tables

The Problem

Many ERP systems handle multi-company data in one of two ways: - Logical partitioning: A "Company" field appears in every table (e.g., Company = 'ACME Corp') - Physical partitioning: Separate tables for each company (e.g., ACME$Sales Line, XYZ$Sales Line)

Standard dimensional models expect consistent table names. They can't dynamically adapt to different physical structures or seamlessly combine data across companies while keeping them logically separate.

The Solution

Partitioned tables let Datashop treat Company as just another dimension, even when the underlying structure is complex. The model defines: - How companies are identified (a field, a table name prefix/suffix, or another method) - How to combine data across companies - How to isolate data for a single company when needed

When you filter to "Company = ACME Corp," Datashop: - Queries only the relevant tables or records - Presents results as if Company were a simple dimension - Handles the physical or logical partitioning transparently

[SCREENSHOT/DIAGRAM: Showing multiple company tables unified under one Company dimension]

Why It Matters

Multi-company reporting becomes straightforward. You don't see separate data models for each company or struggle with combining data. Company is just another dimension alongside Customer, Date, and Item. Filter to one company, analyze across all companies, or compare companies side-by-side—it all works naturally.

User impact: Multi-company organizations can analyze consolidated data or individual companies with equal ease, using a single unified model.

Custom Relations

The Problem

Sometimes the fields that conceptually link two tables don't form a unique join key. Joining on those fields alone would cause row multiplication or incorrect results. For example, joining a transaction table to a reference table on a non-unique field might duplicate records.

The Solution

Custom relations let the model define additional join conditions beyond standard foreign key relationships. These custom conditions ensure accurate joins even when the data structure is unconventional.

For example, a custom relation might specify: - Join Sales Line to Product Master on Item Number and Unit of Measure - Join G/L Entry to Account Master on Account Number and Date Range

Datashop uses these custom relations automatically when constructing queries, ensuring correct results without row multiplication.

Why It Matters

The dimensional model can work directly on complex ERP schemas without requiring pre-processing, views, or data transformations to create ideal join keys.

User impact: You get accurate results when analyzing data, even when the underlying database relationships are complicated.

Bridging Tables (N:N Relationships)

The Problem

Some business scenarios involve many-to-many relationships. For example: - A sales order might have multiple salespersons (team selling) - A product might belong to multiple categories (cross-categorization)

Standard dimensional models expect one-to-many relationships. They don't natively handle many-to-many scenarios.

The Solution

Bridging tables implement N:N relationships in the dimensional model. A bridging table sits between two dimensional units, defining the multiple relationships.

For instance: - Sales Order ↔ Bridging Table ↔ Salesperson - Item ↔ Bridging Table ↔ Category

When you analyze sales by salesperson in a team-selling scenario, Datashop: - Recognizes the N:N relationship through the bridging table - Properly attributes sales to all involved salespersons - Handles aggregation correctly (avoiding double-counting)

Why It Matters

Many-to-many relationships are common in real business processes. Bridging tables let the dimensional model reflect reality without forcing artificial simplifications.

User impact: You can analyze complex scenarios like team contributions, multi-category products, or other N:N relationships accurately.

Reverse Foreign Keys

The Problem

In traditional ER modeling, relationships point from child to parent (e.g., Invoice Line has a foreign key to Invoice Header). Dimensional models usually follow this convention.

Sometimes it's more convenient to define the relationship in reverse—mapping the child unit to the parent table directly, even though the foreign key physically points the other way.

The Solution

Reverse foreign keys allow the model to define relationships in either direction. You can map a parent dimensional unit to a child table, and Datashop understands the relationship works in reverse.

This flexibility simplifies model configuration in scenarios where the natural business concept is parent-to-child but the database structure is child-to-parent.

Why It Matters

Model designers can define relationships in the most intuitive way, regardless of physical foreign key direction. This reduces configuration complexity and makes the model easier to maintain.

User impact: Transparent—you don't see this, but it ensures the model works logically even when database structures are non-standard.

Compound Keys

The Problem

Some dimensional units require multiple fields to uniquely identify them (e.g., Item Number + Unit of Measure, or Account Number + Cost Center). Joining to these units requires matching all key fields.

The Solution

Compound keys let a dimensional unit specify that multiple fields constitute its identity. Datashop automatically uses all key fields when joining to that unit.

This is less common than custom relations, but it's available for scenarios where multi-field keys are unavoidable.

Why It Matters

The model can represent dimensional units with natural compound keys without forcing artificial single-field surrogate keys.

User impact: Dimensional units with compound keys work just like any other unit—you don't need to know they use multiple fields internally.

Date Mask Mapping

The Problem

Some ERP systems store dates as integers (e.g., 20251107 for November 7, 2025) or strings (e.g., "2025-11-07"). Converting these to proper date types in every query is slow and complicates SQL.

The Solution

Date mask mapping defines a format mask (e.g., yyyymmdd or yyyy-mm-dd) for date fields stored as integers or strings. Datashop: - Converts values only at the UI level (when displaying to users or accepting input) - Leaves values as-is in the database for optimal query performance - Uses efficient range comparisons in queries (e.g., WHERE DateField >= 20250101 AND DateField < 20250201 for January 2025)

This works for both integers and character fields, ensuring fast performance regardless of how dates are physically stored.

[SCREENSHOT/DIAGRAM: Showing integer date 20251107 displayed as "November 7, 2025" in UI but queried as integer]

Why It Matters

Date analysis remains fast even when the ERP stores dates in non-standard formats. You see human-readable dates, but queries run at optimal speed.

User impact: Dates work naturally in Datashop, with intuitive filtering and display, regardless of how they're stored in the database.

Virtual Attributes (Calculated Members)

The Problem

You might want to analyze by derived attributes that don't exist as physical columns. For example: - Month and Quarter derived from a date field - Profit Margin calculated from Profit and Revenue - Age Category derived from a birthdate

Creating physical columns or database views for every derived attribute is maintenance-heavy and inflexible.

The Solution

Virtual attributes define calculated columns in the dimensional model using SQL expressions. These appear in Datashop as regular dimensional units or metrics, but they're computed on-the-fly from existing fields.

For example: - Month = MONTH(OrderDate) - Quarter = CONCAT('Q', CEILING(MONTH(OrderDate) / 3.0)) - AgeCategory = CASE WHEN DATEDIFF(year, Birthdate, GETDATE()) < 30 THEN 'Young' ELSE 'Senior' END

Virtual Attribute Remapping (VAR): When a virtual attribute is defined on a field that maps to a dimension (like OrderDate mapping to Date dimension), Datashop can calculate the virtual attribute on any table containing that field without joining tables.

For instance: - Virtual attribute Month is defined on the Date dimension's OrderDate field - You analyze Invoice Line by Month - Datashop calculates Month directly from Invoice Line.OrderDate without joining to the Date dimension table

This saves expensive joins and dramatically improves performance.

[SCREENSHOT/DIAGRAM: Showing virtual attribute calculation on multiple tables without joins]

Why It Matters

You gain analytical flexibility without modifying the database. Date dimensions, in particular, benefit hugely—you can analyze by month, quarter, week, day-of-week, etc., all derived from a single date field. VAR ensures these calculations remain performant even on large datasets.

User impact: More analytical options (derived attributes) appear in the model, and they work fast thanks to VAR optimization.

How These Features Work Together

These xMDD features often combine in real-world models. For example: - Dependent dimensions + Partitioned tables: Analyze multi-company transactional data with header/line structures - Virtual attributes + Date mask mapping: Derive month/quarter from integer date fields efficiently - Custom relations + Bridging tables: Handle complex N:N scenarios with non-standard join keys

Your data team configures these features in Modeller based on your ERP's specific structure. Once set up, they work automatically. You interact with a clean, intuitive dimensional model that hides the underlying complexity.

Do You Need to Know This?

For most users: No. Understanding these features is optional. They work transparently, and you'll never encounter situations where you need to think about them.

For power users or those curious about how things work: This knowledge helps you understand why Datashop handles complex scenarios so elegantly. It also helps when discussing requirements with your data team or troubleshooting unusual situations.

For data modelers: If you're responsible for defining or maintaining the dimensional model in Modeller, you'll work with these features extensively. Consult the Modeller User Guide for detailed technical documentation.

Tips – Real-World Complexity (Acinta's Extended Model)

• These features are "under the hood"—you benefit without needing to configure or even think about them.
• If you're curious about how your specific model uses xMDD features, ask your data team or check the model documentation.
• The xMDD capabilities are what allow Datashop to work directly on production ERP schemas, avoiding the need for complex ETL processes or data warehouses.

Related Topics

• 3.3 How Dimensions Work Together - The user experience of seamless dimension joining
• [Modeller User Guide] - Technical documentation for configuring xMDD features (separate documentation)

Next Steps

You now have a complete understanding of how dimensional models work in Datashop, from basic concepts to advanced features. Ready to start building your own analyses? Learn about workbooks: 5.1 What is a Workbook

Or explore specific analytical techniques: - 14.1 What are Segmentations - Grouping and ranking dimensions - Chapter 9 Filters - Narrowing your analysis - 10.1 Understanding Metrics - Understanding the numbers you analyze