We have a new community! Please visit support.timextender.com
Follow

How to use the Add Related Records feature

Author: Permanently deleted user Last updated:
howto updated_20140401 example related_records

Note: This is an old article.  However, these tips may still apply to newer versions.  

Introduction

The Add Related Records feature is a table transformation that is capable of creating records in one table based on input from another table. The typical scenario is to create records in a dimension table, using the fact table as input.

Example

In this example, we have a dimension table and a fact table:

2012-05-29_14h40_03.png

 

The Fact Table contains 3 rows of data:

2012-05-29_14h46_49.png

 

The Dimension Table contains 2 rows of data:

2012-05-29_14h49_29.png

 

Select Add Related Records on the dimension table:

2012-05-29_14h50_46.png

 

Assign a name to the Table Transformation and setup the Conditions, mappings etc in the Add Related Records dialogue:

2012-05-29_14h55_09.png

The “Allow Default Value” and “Default Value” can be used to overwrite the value of a mapped field, if the field is empty.

 

The Table Transformation will appear on the Dimension Table:

2012-05-29_15h00_22.png

 

The result, when looking at the Dimension Table is:

2012-05-29_15h01_54.png

 

So the dimension Record with Key = C was created, the Value of the DimKey field from the fact table was used for the DimKey and DimDescription fields and the text "Auto Generated Entry" was used for the DimAttribute field.

This article applies to: tX2012, TX2014. Last updated: 2014-04-01.

Was this article helpful?
1 out of 1 found this helpful

5 Comments

Date Votes
  • 0
    Avatar
    Brian Petersen

    Hello Thomas, I was getting stuck on the last part (adding the relations) when trying to teach it to myself but this post was very helpful. Great job!

  • 0
    Avatar
    Permanently deleted user

    Hi there, thanks for the article, it was very useful in figuring out how to do this. One question - how does this get generated in the backend? I can't find any reference to this functionality anywhere in SSIS or in stored procedures

  • 0
    Avatar
    Permanently deleted user

    Hi,

    The code is generated and executed by the execution engine, you can check the code by using the "View Log" / "Preview Script" option after a successfull execution.

    2012-08-28_10h36_49.png

  • 0
    Avatar
    Permanently deleted user

    thanks! you know, more and more I keep finding that things that I am looking for in TimeXtender are right there staring me in the face! :)

  • 1
    Avatar
    Steven Koppenol

    Hi TX, if you load the fact table first all fact records for new dimension keys will trigger the "early arriving fact" scenario, not because the source system hasn't recorded the key but just because TX hasn't loaded the dimension table yet.

    I am used to loading the dimensions first so that you can perform lookups when loading the fact (lookup dimension surrogate keys, transform a snowflake into a star schema, bring in context for calculations on the fact table, etc.)

    This also seems to be the general principle in TX demos, where "parent" tables (that have the PK) are loaded before "child" tables (that have FK to parent).

    So my question is can TX also handle early arriving facts when the dimension is loaded before the fact table?

     

Please sign in to leave a comment.