Tutorial 06: Transactions

What We Learn

How transaction-type data translates to a non-historized link table. Also how to perform custom or ad hoc transformations during load.

Scenario, Mapping And Modeling

Satellites capture change. If the data never changes, they are not needed, and a non-historized link is used. It is a link table containing all the properties modeled for the class. The same hash is never expected to be loaded again.

We will be expanding the model with financial data from the FinMaster source system like this:

Source table…	…goes to the Staging Area as…	…and maps to the Class
DS_DemoData.FinMaster.ACCOUNT	FinMaster.RAW_ACCOUNT	Account
DS_DemoData.FinMaster.COSTCENTER	FinMaster.RAW_COSTCENTER	Cost center
DS_DemoData.FinMaster.ACCOUNTINGENTRY	FinMaster.ACCOUNTINGENTRY	Accounting entry

Model the new classes as below. Assume Code to be the Business key for both Cost Center and Account.

Accounting entry is the transaction. For it to be correctly implemented, it first of all needs to have a Primary key (since a Business key would result in it having a hub). By looking at the data, you feel confident that the Primary key is composited of the attributes Fiscal year, Voucher number and Voucher row number. Write these in the Identifying key field in the class metadata similarly to how you did it for Project membership in Tutorial 3. This results in a link with a satellite. To finalize the class and turn the link into a non-historized link, set the Is transaction metadata parameter to true.

From the source data you can also see that Cost Center connects to Organization unit. Draw that association to the existing Organization unit class.

Mark the classes for implementation, export the model and refresh D♯ Engine.

Take a closer look at the source data. First the accounting data:

And the Cost centers:

Since a hashed ACCOUNTINGENTRY.COSTCENTER value (100A) will never match a hashed COSTCENTER.CODE (000000100A), either six leading zeros will need to be added to the acconting data or removed from the cost center data. This can be done using the Transformation column in the mapping file as explained here. In this case, we will be using an existing custom transformation. Custom transformations are implemented as scripted C# classes directly in the D# Engine workspace, and they will be a subject for another tutorial. For reference, this is the custom class implementing the transformation:

class StripLeadingZerosTransformation : ColumnValueTransformation
{ 
    public override String Transform(String pSource)
    {
        return "SUBSTRING(%SOURCE%, PATINDEX('%[^0]%', %SOURCE% + '.'), LEN(%SOURCE%))".Replace("%SOURCE%", pSource);
    }
}

Map the the data into the new file 04_Mappings_FinMaster.csv. The final mapping file will look like this (note the syntax for the transformation):

Note that the reference to ORGMASTER.RAW_ORGANIZATIONUNIT.ORGUNIT_ID contains the schema name as qualifier, as the RAW_ORGMASTER tale resides in a different schema than RAW_COSTCENTER.

Save the mappings and refresh D# Engine. Verify that the Raw Vault implementation for the new classes looks like this:

Deploy the classes, and take a look at the SQL code for [DemoDW].[etl].[Hash_FinMaster_RAW_COSTCENTER] and spot the lines of code that handle the transformation.

Run Tutorial Scripts

Run the following tutorial script commands fron the Help -> Tutorials -> Intro Course -> Transactions menu, and inspect the results.

Script	Source data	Main points of interest
Step 1: Load accounting data		Only a link table created for Accounting entry. Cost center also stores the original, non-transformed, value for Code (in the hub, as Code is the Business key). This happens when the original value goes through a datatype conversion or a transformation.
Step 2: Reload corrected data	An error has been fixed on one row, and a new row has been added.	The link table load fails. A non-historized link does not expect the same hash to appear again. This is by design.

Next tutorial