Modelling

Star Schema

1. We use Star Schema or Constellation Schema unless there is a compelling reason not to. Personal preference or experimentation is not a valid reason.

2. We use 1-n relationships, except for tags and similar entities where we use many-to-many.

3. We use bidirectional relationships only where needed (cross-filtering from Fact to Dim).

4. We hide reference columns in fact tables.

5. We use business IDs where possible (rather than artificial DWH_IDs) to improve performance.

Constellation Schema Example

erDiagram
    DimCalendar ||--o{ FactDocumentLine : "PostingDate"
    DimCalendar ||--o{ FactDocumentLine : "OrderDate"
    DimCalendar ||--o{ FactDocumentLine : "DeliveryDate"


    DimCalendar ||--o{ FactCustomerLedger : "1-n"

    DimCustomer ||--o{ FactDocumentLine : "1-n"
    DimCustomer ||--o{ FactCustomerLedger : "1-n"

    DimItem ||--o{ FactDocumentLine : "1-n"

Shared dimensions keep both fact tables aligned while keeping relationships single-directional (Dim → Fact) unless a specific cross-filter is required. Reference keys on the facts stay hidden; prefer business IDs in dims/facts where available.

Multiple Dimensions vs. Single Dimension Table

Assume you have a table FactTransaction. Transactions are categorized into TransactionType and TransactionSubtype.

Which design is preferable?

1. Redundant Subtypes: A single DimTransactionType with columns for both TransactionType and TransactionSubtype

2. Type-Dimension: Two separate dimension tables, DimTransactionType and DimTransactionSubtype

When to Use the Redundant Subtypes Pattern

This is almost always the better design because there is a natural hierarchy between TransactionType and TransactionSubtype. With the Redundant Subtypes pattern, this hierarchy is explicitly visible in the model. Additionally, your model is simpler.

We do not concern ourselves with the redundancy introduced (each Type appears multiple times in the model). This is what denormalization in analytical data models is all about.

When to Use the Type-Dimension Pattern

The Type-Dimension pattern is preferable in this situation:

Assume you have:

1. A constellation schema (multiple fact tables)

2. Fact Table A is defined at the Subtype level

3. Fact Table B is defined at the Type level

A common business example:

• FactGLTransaction at the Account level

• FactBudget at the AccountGroup level

Column Types

1. Use Text for codes, even if they are technically integers (e.g., AccountNumber, CustomerNumber, ItemNumber). This allows adding search mode to slicers.

2. Use Whole Number for keys, counters, and discrete quantities (IDs, year, quantities) to reduce memory and avoid floating-point rounding issues.

3. Points 1 and 2 are in conflict. Point 1 takes precedence, but if you encounter performance issues, you can:

   1. Keep the reference attribute as an integer
   2. On the dimension table, "copy" the column by creating a calculated column
   3. Change the type to text
      

4. Use Currency/Fixed Decimal for financial values (prices, amounts, margins) to ensure precise arithmetic and consistent totals. Avoid generic Decimal for money unless you need huge ranges.

5. Reserve Decimal Number (double) for measurements that naturally require fractions and large ranges (rates, percentages, sensor values), but be aware of binary rounding in complex DAX.

Dates

Auto Date/Time

We do not use the Auto Date/Time feature. Instead, we explicitly model the DimCalendar table.

DimCalendar

DimCalendar =

VAR BaseCalendar =
    CALENDARAUTO()

RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        VAR QuarterNumber = QUARTER( BaseDate)
        VAR MonthName = FORMAT ( BaseDate, "mmm" )
        VAR YearMonthName = FORMAT ( BaseDate, "mmm yy" )
        VAR YearMonthNumber = FORMAT (BaseDate, "yyyy-mm")
        VAR YearQuarterName = FORMAT( BaseDate, "yy") & "Q" & FORMAT( BaseDate, "Q")
        VAR QuarterName = "Q" & FORMAT( BaseDate, "Q")
        RETURN ROW (
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", MonthName,
            "Year Month Number", YearMonthNumber,
            "Year Month", YearMonthName,
            "Quarter Number", QuarterNumber,
            "Year Quarter", YearQuarterName,
            "Quarter", QuarterName
        )
    )

Single Date Dimension

• Use a single DimCalendar with multiple relationships.

• Define the most commonly used relationship as active.

• Use USERELATIONSHIP() for measures that reference inactive relationships.

Why This Is Important

In most reports, you will want to display different measures on the same time x-axis. If your measures depend on different date fields, you need to "reconcile" the dates in a single DimCalendar table to ensure consistent time-based analysis.

Side note: The other commonly used pattern is the role-playing dimension, where there is a distinct Dim table for each date. In case you do not need a common axis, or no common slicer is needed, that pattern is easier.