Tutorial 06 – Transactions

What We Learn

How transaction-type data translates to a non-historized link table. Also how to use custom transformations during load.

Scenario, Mapping And Modeling

Satellites capture change. If the data never changes, satellites 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 non-Business key (since a Business key would result in it having a hub). By looking at the data, you feel confident that the key is composited of the attributes Fiscal year, Voucher number and Voucher row number. Add these to the key. This results in a link with a satellite. To finalize the class and turn the link into a non-historized link, check the Is transaction metadata parameter of the class.

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.

Link Considerations

In the real world, the more certain you are that the data won’t change, the more likely it is to do so at the most inconvenient moment possible. In this case, if the original data were to be edited in the source system, against all odds, the source primary key will still be the same. This means that for the same hash value there will be different content in the incoming data. Since the hash value already exists in the link, these changes will simply be lost. To be sure to catch all possible edits in the original data after loading the first version, you should explicitly add each property as a key component that you expect to change. To be on the safe side, you can add them all. And as is the basic nature of a link, its hash should contain all hub references, so you should at least add those. Experiment with different combinations to learn the behaviour. Also, if the link table will contain logical versions of the same real-world data (original accounting entry and a corrected one), you may want to apply state tracking and soft delete to the class as well.

We will leave this key vulnerable to prove the point. But to honor the link table hash principle, add Cost center to the key at this point.

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 transformation. Transformations can be found in the Languages pane. Just drag the appropriate transformation onto the desired row, and it will settle itself into the correct cell. To preview the code that the transformation will execute, open the SQL pane and click on the transformation.

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 project and run the model. 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 -> DSharp Studio Professional 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