Presenting SCD Data in Semantic Models

In this lesson, you will learn how to structure your semantic model so that end-users can perform accurate point-in-time analysis without needing to navigate the underlying complexities of Type 2 versioning.

The Challenge of SCD Exposure

When you implement Type 2 SCD tables, your dimension table grows every time an attribute changes. If a user simply drags a "Sales Amount" measure and a "Customer Region" attribute into a BI report, the tool might inadvertently aggregate values across multiple historical versions of the same customer, leading to inflated numbers. If you expose the raw surrogate keys and version columns directly, you force your users to become data engineers just to write a simple query.

Simplifying the Semantic Layer

To shield users from this, you should configure your semantic layer to handle the "current version" vs. "historical version" distinction automatically.

1. Expose a "Current" View: Create a filtered view or a semantic model perspective that only includes records where is_current = True (or end_date is null). This should be the default for standard reporting.
2. Use Valid-From/To Filtering: If a user needs historical analysis, ensure your semantic model exposes valid_from and valid_to as date attributes that can be used in report filters.
3. Hide Surrogate Keys: Users should never see customer_sk. Instead, expose the business-friendly customer_id and handle the join logic in the semantic model's relationship definition.

For example, your semantic model relationship should map the FactTable to the DimensionTable using the customer_sk, but the BI tool’s interface should be configured to group by the customer_name and effective_date ranges:

-- Semantic model logic (conceptual)
-- Always prefer the current version for default measures
SELECT * 
FROM dim_customer 
WHERE is_current = TRUE;

By encapsulating these SCD rules—such as filtering for active flags or mapping date ranges to report filters—you turn a complex technical implementation into a seamless drag-and-drop experience. Your end-users get the power of historical accuracy without needing to understand the row-versioning logic you built in the background.

Key Takeaway: The semantic layer should abstract Type 2 SCD complexity by providing "current" defaults for standard reporting while offering clear, date-based access paths for historical analysis.