Automated Dimensional Modeling with Astera: 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 a data warehouse. 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 modeling.

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 an easy, no-code way–the Astera way.

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

View Demo

What is Automated Dimensional Modeling?

Automated dimensional modeling is a technique used in data warehousing that uses tools to streamline the process of building dimensional models. Automated dimensional modeling enables you to:

• Automatically discover and understand the structure of your data
• Organize your data into facts and dimensions based on best practices and data characteristics
• Automatically create the necessary code to implement the dimensional model in your data warehouse

Traditional dimensional modeling involves manually designing the structure of a data warehouse for data analytics. The process includes defining fact tables to store measurable data and dimension tables to provide context for the facts. It can be a time-consuming and complex process.

Automating Dimensional Modeling with Astera

Astera’s data warehousing solution leverages end-to-end automation and no-code development to streamline dimensional modeling. Using advanced code generators, Astera DW Builder automates manual design tasks and reduces dimensional model design time by nearly 80%.

Let’s see how Astera simplifies complex data modeling tasks for developers, for example, 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 Wide World 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 spend 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 is as much about flexibility as it is about automation.

The automated dimensional model does away with all the hassle of manual configurations. However, it doesn’t mean that our dimensional data model is set in stone. Prior to its deployment, you can also adjust the model manually 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.

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

Free 14-Day Trial

Automated Dimensional Modeling with Astera

Astera’s automated dimensional modeling is transformational for how you approach data warehousing. By automating the whole process and providing a 100% no-code 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.