Overview

Field Transformations enable the ability to work with data coming from the data source(s) and transform the data so that it is cleaned up for the business users. This article will cover a summary of Field Transformations and the various ways they can be used.

Details

Field Transformations provide a way to clean up the raw data that is stored in the Staging Area before it is moved into the reporting tables that are used by the business when reports are created and run. Transformations within the data model are made by first checking out the extension that the transformations should be associated with and then navigating to the Transformations section. 

You will see all the tables that have been added to the Staging Area, and next to each table is a + icon that will provide a way for you to add a transformation to the table.

There are three primary types of Field Transformation types that can be added to a table:

• Lookup: Lookup transformations are used to lookup a value from one table and bring it into another table. You will define the table and field to do the lookup to, and then you will specify the join criteria to be used when pulling in the value so that Cosmos knows how the tables are related. An example could be grabbing the Salesperson Name from the Salesperson table and bringing it into the Customer table based on the Salesperson Code field that is present on both tables.
• Calculated Field: Calculated Field transformations will add a new field to the table and calculations can then be defined for this new field based on other fields in the table. These could be as simple as adding or subtracting two fields together, or more advanced logic can be built in using case statements. 
• Fixed Value: Fixed Value transformations are used when you need to create a new field in the table that has a specific value in it. You will specify the data type of the field and then the value that should be placed within it. 

Detailed instructions on working with each of these Field Transformation types can be found in the articles below:

• Working with Lookup Fields
• Working with Calculated Fields
• Working with Fixed Values