Note: This is an old article. However, these tips may still apply to newer versions.
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.
In this example, we have a dimension table and a fact table:
The Fact Table contains 3 rows of data:
The Dimension Table contains 2 rows of data:
Select Add Related Records on the dimension table:
Assign a name to the Table Transformation and setup the Conditions, mappings etc in the Add Related Records dialogue:
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:
The result, when looking at the Dimension Table is:
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.
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!
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
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.
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! :)
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?