Back to all Blogs
Managing Change in Dimensional Models
Tracking changes in transactional data from ERP and CRM systems is essential for accurate reporting. Businesses need to understand not only the current state of a customer, product, or order but also how that state has evolved over time. This historical perspective is critical for compliance, trend analysis, and strategic decision-making.
Slowly Changing Dimensions (SCDs) - especially Type 2 - are the standard approach for capturing this history. However, implementing SCD logic manually can be complex and error-prone, requiring custom SQL scripts, surrogate key management, and careful handling of valid time windows.
AnalyticsCreator simplifies this process by allowing you to configure historization through metadata. It then automatically generates the necessary logic, schema changes, and ETL pipelines. These can be deployed into Microsoft Fabric SQL or any Microsoft SQL Server instance, making your data model ready for querying and analysis with full change tracking.
TL;DR
- SCD Type 2 is essential for tracking historical changes in dimensional models.
- Manual implementation is complex and time-consuming.
- AnalyticsCreator automates historization:
- Configure SCD rules in metadata.
- Auto-generate schema changes and ETL pipelines.
- Deploy to Microsoft Fabric SQL or SQL Server.
- Result: Faster, error-free historization with full governance.
Why Historization Matters
Historization ensures that your BI and analytics systems can answer questions like:
- What was the customer’s status last quarter?
- How did product pricing evolve over time?
- Which sales rep managed this account before the last reorganization?
Without historization, reports only reflect the current state, which can lead to misleading insights. SCD Type 2 solves this by maintaining multiple versions of a record, each valid for a specific time range.
Learn more about Slowly Changing Dimensions →
Challenges with Manual SCD Implementation
Traditionally, implementing SCD logic involves writing complex SQL queries to detect changes, updating old records with end dates, inserting new versions, and managing surrogate keys. This process is not only time-consuming but also prone to errors—especially in large-scale data warehouses with multiple dimensions.
How AnalyticsCreator Simplifies Historization
AnalyticsCreator eliminates manual coding by using a metadata-driven approach. You simply define historization rules in the model, and the platform generates everything else—schema changes, ETL logic, and pipelines.
Architecture Overview:
This solution uses Microsoft Fabric as thr reference platform, but the approach works with Azure SQL MI, Azure SQL VM, and on-prem SQL Server. The architecture combines data ingestion, modeling, and historization:
- Azure Data Factory (ADF) loads source data into Fabric SQL (IMP schema as a landing zone).
- AnalyticsCreator models dimensions and facts, including historized fields.
- Fabric SQL stores both the metadata repository and the data warehouse layers.
- OneLake Delta tables provide fast access for reporting and analytics.
Step-by-Step: How Historization Works with AnalyticsCreator
1. Define SCD Settings in the Metadata
In the AnalyticsCreator model, you flag the dimension (e.g., DIM_Orders) for SCD Type 2. This tells the tool to track attribute changes and maintain historical versions. The platform automatically adds columns such as:
DAT_VON: Start date for the versionDAT_BIS: End date for the versionSATZ_ID: Surrogate key for uniqueness
2. Automatic Generation of SCD Logic
Once historization is enabled, AnalyticsCreator compares incoming records with existing data in Fabric SQL. If a tracked field changes, the ETL logic:
- Updates the old row with an end date.
- Inserts a new row with updated values and a current start date.
This logic is generated automatically—no need to write delta queries or manage timestamps manually. Surrogate keys are handled consistently across all versions.
3. Execute and Validate
After deployment, you can query the historized table to see multiple versions of the same record. For example, in the Northwind Orders demo, editing an order and reloading the data creates a new version while preserving the old one. This confirms that SCD Type 2 behavior is applied correctly.
Comparison: Manual SCD vs AnalyticsCreator
| Feature | Manual Approach | AnalyticsCreator |
|---|---|---|
| Delta Detection | Custom SQL per table | Fully automated |
| Tracking Columns | Defined manually | Added by metadata |
| Surrogate Key Handling | Manual insert/update logic | Handled internally |
| Schema Deployment | Manual table creation | One-click deployment |
| Pipeline Generation | Requires coding in ADF or notebooks | Generated from metadata |
Use Case: Northwind Orders in Fabric with Historization
For this project, we imported the Northwind sample database and enabled SCD logic on the Orders dimension. The data was ingested through Azure Data Factory into Fabric SQL. Once historization was turned on, we edited some source values and refreshed the load.
AnalyticsCreator automatically detected changes, updated the previous records, and created new versions. We didn’t write any transformation logic or update scripts. Everything was handled by the tool.
When we queried the data, the table showed both old and new versions of the same order. This confirmed that SCD Type 2 behavior was applied correctly. The result matched Kimball principles and followed Fabric best practices.
Final Takeaway: SCD(Slow Changing Dimensions) Doesn’t Need to Be Complicated
Historization is critical for accurate reporting and compliance, but it doesn’t have to be complicated. AnalyticsCreator makes it easy by:
- Applying historization rules through metadata.
- Automating schema changes and ETL pipelines.
- Deploying to Microsoft Fabric SQL or any SQL Server environment.
