Designing an Automated Dimensional Model: A Step-by-Step Guide

To begin our exploration of Automated Dimensional Modeling, it’s essential to first gain an understanding of Dimensional modeling. Dimensional modeling is among the most preferred design approaches for building analytics-friendly data warehouses. First introduced in 1996, Kimball’s dimensional data models have now become cornerstones of modern data warehouse design and development. The denormalized star schema reduces complexity and optimizes the maintenance of historical data, query performance, retrieval, and consumption for reporting.

Learn More About Dimensional Modelling.

At a glance, a subject-oriented dimensional model consists of fact and denormalized dimension tables connected through foreign keys. Facts carry numerical information about a specific business measure/transaction, e.g., a sales invoice. Dimensions carry descriptive attributes for the fact, which eventually allow the business intelligence (BI) layer to easily filter and query data for reporting.

Designing the right dimensional data model for your data warehouse, however, can be a challenge! Maintaining an up-to-date model requires writing long, complex scripts regularly. But there’s a hard way of doing dimensional modeling, and then there’s the Astera way. 

Build Your End-to-end Data Warehouse Without Any Coding Required

View Demo

Automated Dimensional Modeling – The Astera Way 

Astera’s Automated Dimensional Modeling approach is the cornerstone of the data warehousing solution, which utilizes end-to-end automation through no-code development. Using advanced code generators, Astera’s DW Builder automates manual design tasks and reduces dimensional model design time by nearly 80%.

Do you want to know how it makes the life of developers easier? Let’s see how automation simplifies complex data modeling tasks, e.g., defining and configuring dimensions and facts in a star schema. 

Here, we have a source model containing multiple tables from a transactional system based on the fictitious World Wide Importers databases for Microsoft SQL. Alternatively, it can be a staging/ODS database as well.  

Fig 1. Source Model

Step 1: Identify Facts and Dimensions: 

Simply pick ‘Build Dimension Model’ from the dropdown menu and mark relevant entities as facts and dimensions for star schema. 

Fig 2. Build a Dimension Model Feature

What you choose as facts or dimensions depends primarily on the OLAP reporting use case and the type of entity. You can also auto-detect the entities to automatically deformalize schema tables for faster query results in the BI layer. With this option, you don’t need to expend time on manually denormalizing relatable tables.  

In this use case, invoice transactions have been marked as facts, whereas descriptive attributes, e.g., suppliers, customers, stock items, and city information, have been marked as supporting dimensions.  

Fig 3. ‘Build a Dimension Model’ Configuration Window

Step 2: Configuring Facts and Dimensions 

Other pre-built options in the window include Row Identifiers, Child Fact Entity Options, and Add Date/Time relationships dropdown.  

If you want to employ row identifiers for maintaining historical data, you have plenty of options in the dimensions. Here, the row identifier can be any of the following: current record designators, version number, effective and expiration dates, and effective expiration range.  

Similarly, in the builder window, you can add date or time dimensions for incorporating time-specific attributes in the model.  

Proceed from the window, and voila—your dimension model schema is practically ready! 

Fig 4. Dimensional Data Model

Step 3: Further Configurations

Astera’s way is as much about flexibility as it is about automation! 

The automated dimensional model does away with all the hassle of manual configurations. But don’t worry! Our dimensional data model is not set in stone. Prior to its deployment, the model can also be manually adjusted to match your data warehouse use case requirements.  

For example, you can add new entities, redefine relationships, reconfigure individual facts and dimensions, add surrogate keys and row identifiers.  

Similarly, create new relationships by picking identifying or non-identifying link entities from the toolbar and joining your entities.  

Individual links or relationships can be further configured through the ‘Edit Relationship’ window.

Fig 5. Change relationship types, alias names, or add different foreign key(s)

Additionally, if you plan on making changes to entities, you can access a variety of options by just right-clicking on the entity of interest. 

You can reconfigure entities by adding surrogate key/row identifiers, changing schemas, or editing element names and types, etc.  

Lastly, by selecting properties, you can open an entity’s layout builder and access the settings for individual columns.  

This is what the layout builder for a dimension (stocks) looks like:

Fig 7. Layout Builder for Individual Entities

The layout builder is used to reconfigure specific fields and assign specific fact or dimension roles (in the form of SCDs) for optimal query performance and history maintenance.  

Finally, on the entity indexes field, you can add indexes to increase the speed of retrieval based on entity fields.

Fig 8. Entity Indexes

The figure above holds an existing index, ‘PK_Sales_Customers’, which is a primary key index present in the database. For index optimizations, you can add, delete, or autogenerate indexes based on field characteristics with just a single click. 

Are there any more steps? No, this is it. your automated dimensional data model’s star schema is ready! However, it’s important to note that this model currently exists on a logical level. We can create a physical layer in the database of our choice using the forward engineering feature, which again is really simple through Astera’s way.  

Automate manual design tasks and reduce dimensional model design time by 80%

Free 14-Day Trial

Dimensional Modeling with Astera DW Builder 

Astera’s automated dimensional modeling is transformational for how you approach data warehousing. By automating the whole process and providing a code-free environment for model optimizations, Astera DW Builder significantly cuts down the design and development time. On top of that, it doesn’t lose out on the flexibility aspect, given the plethora of configuration options available In our enterprise-grade data warehouse automation solution.  

Learn More About Generating Database Schema Using Automated Forward Engineering.