How to Model Complex Related Dimensions

This document covers how to model complex related dimensional modeling in one-to-many, many-to-one and many-to-many relationships.

Concept

Please take a look at the data model above. It would be challenging to design this model straight out in AtScale. The reason is because there are values in a dimension table that are also in a fact table.

Here is the modeling concept for handling nested many-to-many relationships with Facts and Dimensions in the same table.

Design

To handle this model, pick one measure from one of the fact tables and design it one measure and one dimension at a time.

You can start with a simple measure first, like the Sales Document Item. Since the data from the Sales Document Item and Sales Document can be combined, create a QDS to merge the two tables. This will provide the keypair value for the Dimension join.

SELECT B."Sales Document Key"
 ,B."Sales Document ID"
 ,B."Sales Document Create Date"
 ,B."Sales Document Type Key"
 ,B."Sales Document Sold To Customer Key"
 ,B."Sales Document Customer PO Date"
 ,B."Sales Document Ct"
 ,A."Sales Document Item Key"
 ,A."Sales Document Item ID"
 ,A."Sales Document Item Incoterms 1 Key"
 ,A."Sales Document Item Plant Key"
 ,A."Sales Document Item Profit Center Key"
 ,A."Sales Document Item Material Key"
 ,A."Sales Document Item Ct"
 ,A."Sales Order Item Net Weight"

FROM QT.INVISTA.SALES_DOCUMENT_ITEM A
 ,QT.INVISTA.SALES_DOCUMENT B

WHERE A."Sales Document Key" = B."Sales Document Key"

As a fact table, add these measures into the Measures box.

Then, construct the Dimension, starting with one from the closest to the end. Delivery Item -> Delivery -> Shipment Item -> Shipment.

Then, construct the QDS for the Dimension Bridge.

SELECT "Delivery Item Key"
 ,"Delivery Key"
 ,"Sales Document Item Key"

FROM QT.INVISTA.DELIVERY_ITEM

GROUP BY "Delivery Item Key"
 ,"Delivery Key"
 ,"Sales Document Item Key"

Once the bridge table is defined, construct the Dimension needed to pull the Measure from the SDI_FACT Table.

Here is the list of dimensions that are needed:

• Sales Dim
• Delivery Dim
• Shipment Dim 
• Billing Dim

First, construct a simple bridge to grab the Sales Document and Sales Document Item:

SELECT "Sales Document Item Key"
 ,"Sales Document Key"
 ,"Sales Document Item ID"
 ,"Sales Document Item Incoterms 1 Key"
 ,"Sales Document Item Plant Key"
 ,"Sales Document Item Profit Center Key"
 ,"Sales Document Item Material Key"
 ,"Sales Document Item Ct"
 ,"Sales Order Item Net Weight"

FROM QT.INVISTA.SALES_DOCUMENT_ITEM

Call it Sales_Dim, which consists of two Dimension tables: Sales Order ID and Sales Order Item ID (from the Sales Document & Sales Document Item).

Then, continue with the rest of the Dimensions.  The final result is shown below.

The Bridge table will satisfy every Dimension of the Sales Order Fact table.

To continue, repeat the process for the second Fact table. 

The final data model will look like this (below).

Handling N to N to 1

In this example, consider the relationship between Delivery to Delivery Item (1-to-many) and Delivery Item to Sales Document Item (1-to-many) and Sales Document (many-to-1). How can this be handled? 

The answer is simple because we already broke out the Measure calculated in Delivery. You can just grab the Sales Document Key with the relationship to the Delivery.

SELECT A.”Sales Document Key" ,
B."Delivery Key" 

FROM QT.INVISTA.SALES_DOCUMENT_ITEM A, QT.INVISTA.DELIVERY_ITEM B

WHERE A."Sales Document Item Key" = B."Sales Document Item Key" 

GROUP BY A."Sales Document Key" , B."Delivery Key" 

This will eliminate the duplication of Delivery, Delivery Item, and Sales Document Item.

Summary

While this approach to modeling in AtScale is unique, it is practical to handle complex dimensional relationship scenarios.

Each Fact table and Dimension have no relationship to other Fact tables.

You do not have to enable unrelated Dimensions. The reason is that each Fact table contains its relationship definition on navigating between Measures to the Dimension in the Bridge table. Practically, you are forcing AtScale to create a query for each Fact table and then merge that with the key reference you created in the Bridge table.