DbSchema Dimensional Model

This post builds on our previous article, Data Modeling for Greenplum, by diving deeper into how we leverage the extensibility features of the DbSchema Data Modeling Tool. Specifically, we’ll show how to design a dimensional model and generate DDL that directly integrates with Mugnano Data Consulting’s dimension utility functions.

This integration enables our customers to jumpstart ETL development by quickly populating type-2 historical dimensions, and to immediately deliver business value through pre-generated views that support common analytics use cases, right out of the box.

Let’s start with an example with a very simple dimensional model (shown in the DbSchema tool):

DbSchema : SQL Syntax Adjustments

DbSchema's extensibility enables dynamic DDL generation based on table attributes. We saw examples of this in the prior blog. Now we want to make some custom adjustments that go beyond what is delivered by the DbSchema product. Let’s extend the DDL so that a data modeler can easily “tag” that a particular table in the model is a Type-2 dimension along with providing a mechanism for the modeler to control the code generation for the DDL.

1.     Comment Tags:

Add metadata tags at table and column levels to automate dimension object generation

Creating Type-2 Dimension Table Template

Now that we’ve added those Comment Tags when we create a table in the model, the “Tag” tab will display our tag names and allow the modeler to choose from the value drop down. In the example below we’ve created a table called template_type2_dim and set the options to indicate it’s a type-2 dimension and that we want to generate functions for ETL and dimension lookups.

In this example, setting all tags to ‘Y’

Now we need the modeler to indicate which column (or columns) make up the dimension natural or business key. In this template example the natural key is the column nk1:

Set the “Part of Natural Key” tag name on the column to ‘Y’

Creating Type-2 Dimensions

Template Overview

To streamline the creation of Type-2 dimensions, use our pre-defined virtual table template. The template includes:

• Standard columns: sk_key, start_ts, end_ts, is_active, row_chksum, and placeholders for natural keys (nk1, nk2, ...).

• Default tags: Automatically set to facilitate dimension-specific functionality as shown above.

Steps to Create a Dimension

1.     Clone the Template:

• Right-click the template table, select Clone, and specify the target schema.

• Drag the cloned table into the desired layout.

• Rename the table to your desired name

2. Modify the table:

Add columns to our table from the starting columns on the template

Set physical design attributes specific to Greenplum.

Generating DDL Statements

DbSchema generates SQL based on model configurations.

1. Right-click the table and select Build SQL → Create Statement.

2. The generated DDL will include:

• Distribution clauses (default: primary key columns or random).

• Calls to the dimension_util framework for dimension object creation.

Here is our generated DDL:

The call to build_dimension_objects is custom to this solution and driven by the added tags we included in the model

Automated Database Object Creation

The dimension_util framework automates the creation of supporting objects for Type-2 dimensions. Once the DDL is executed, the following are generated:

• Dimension Tables: Dimension table, Staging table, and Current table.

• Views: Diff view, "What-Changed" view, and dimension current views.

• Functions (optional): Lookup and population functions.

Lets look at the objects that got generated when we ran this DDL (viewed in DBeaverEE):

Generated Tables: test_stg, test_dim, test_dim_curr

Views: v_test_dim_curr_wrk, v_test_dim_diff, v_test_dim_what_changed

Functions: lkp_test_dim, sp_populate_test_dim

Learn More

This post demonstrates how you can drive automated code generation directly from your data models using DbSchema. While we’ve used tools like Erwin and pgModeler in the past, DbSchema stands out for its user-friendly interface and robust extensibility, making it ideal for integrating with custom development workflows.

If you're interested in seeing this in action or exploring how it could accelerate your own ETL and modeling efforts, contact Mugnano Data Consulting to schedule a live demo.