Configuring and Deploying SCD-Ready Dimensional Models for Optimal Data Warehousing

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.  

Learn More About Generating Database Schema Using Automated Forward Engineering

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:

Here’s our sample dimensional data model—designed with Astera DW Builder—based on a fictitious World Wide Importers database.

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. 

Dimension SCD roles for each field in a dimension table.

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.  

Assigning SCD 2 to a dimension field.

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. 

Previewed data after changes in the StockItemName. The ’USB missile launcher (Green – New Name)’ is the latest change to the field.

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.

Slowly Changing Dimension (SCD) Types

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: 

The changes in unit price are reflected in two columns—signifying new and old unit prices—and a new row showing updated records.

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. 

Data Model Verification for Read and Write Deployment

Here’s an example of how the verification module identifies common errors affecting deployment.  

This error shows that there’s no business key defined in our StockItem. Simply assign a business key to the StockItemID in the StockItem dimension to rectify the error.

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. 

Press the deployment button and identify the path to the data model config file.

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%. 

More Resources

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.