Dimensional modeling is still the most reliable modeling approach for designing a data warehouse for reporting use cases. Its denormalized structure significantly improves query performance, allowing for fast and seamless data consumption and reporting. The dimensions in a model-centric data warehouse use Slowly Changing Dimension (SCDs) to maintain historical data for recording and reporting. Any changes in existing or new records are identified by SCDs and maintained in your data warehouse tables. But more on SCDs in a bit.
SCDs are an important feature of your dimension tables since they determine the maintenance of historical records in dimension rows. However, adding multiple SCDs to different fields would need several lines of complicated SQL code.
But as we always say, where there’s a complicated way, there’s also Astera’s way. In the Astera DW Builder environment, you can add SCDs in minutes—without writing code.
This blog will walk you through how to add Slowly Changing Dimension (SCDs) to your dimensional model and verify and deploy that model with just a few clicks using Astera DW Builder.
Configuring the Slowly Changing Dimensions:
In this model, one fact table (Invoice Lines) is joined by multiple dimensions (stock items, customer details, suppliers, and cities). While this is an automated dimensional model, we can always revisit individual entities to configure dimension roles, e.g., SCDs.
Step 1: Access the Dimension Role Column in Dimension Properties
Simply open the layout builder for a dimension by heading into its properties. The layout builder contains a separate column for manually defining roles for each dimension field.
Step 2: Choose the Desired Slowly Changing Dimensions
Assign the desired SCD type from the four available options (SCDs 1, 2, 3, and 6) to each field based on how you want to maintain data in your data warehouse.
In this example, we will select SCD Type 2—Update and Insert—as a dimension role for the field ‘StockItemName’. Now, logically speaking, any change in the StockItemName would be reflected as a new row with the updated name. Furthermore, the automated current record designator field would ensure that the latest record, or StockItemName,’ is identifiable in this case.
Here on out, any change in the StockItemName in source would appear as an updated record in the dimension table.
For example, this is what previewed data for StockItemName looks like after we change a record twice in the source data. The CurrentRecordDesignator field shows the latest record.
Step 3: Mark the right roles for the required dimension fields
We assign specific SCDs in the dimension role column of each entity based on how we plan on updating data in the data warehouse.
Let’s assign SCD 6 to the ‘Unit Price’ field and change a field value in the source table multiple times.
This is how previewed data for the populated field (Unit Price) will appear after a few changes in the source:
After the specific dimension roles are assigned as per our data warehousing needs, the next step is to verify and deploy the model for consumption.
Verifying and Deploying the Dimensional Model:
We must verify our model prior to its deployment. Astera has an in-built data model verification module that automatically scans the model for any errors affecting the physical deployment.
Here’s an example of how the verification module identifies common errors affecting deployment.
After data model verification, we deploy the data warehouse on our server using the ‘Deploy Data Model’ button in the toolbar. Before deployment, we need to specify the data model name and the path to config file, which can be used to modify data model parameters.
Finally, the model is deployed as a virtual database on the server. This deployment—in the form of Astera Data Model—can serve multiple purposes. For example, we can use it in ETL pipelines for populating the dimensional model or consume it for BI and analytics.
Configuring and Deploying Dimensional Models with Astera DW Builder
Configuring dimensional models with SCDs and deploying them for consumption can’t get easier than that. You don’t need to perform manual scripting or complex queries to assign dimension roles or deploy data warehousing models. Here’s all you need to do:
- Assign SCD roles to entities in the dimensional model through a drag-and-drop interface.
- Verify models to rule out any potential errors.
- Deploy the models with just a click.
This is exactly how you cut down the data warehouse design and development lifecycle by up to 80%.
Do you want to know how Astera automates otherwise complex dimensional modeling? Read our how-to blog on Designing an Automated Dimensional Model.
Learn how to automatically generate a physical schema from your data model design here.