What We Learn
How to split source data into several classes.
Scenario
We are expanding the DW with the HumMaster HR system, which contains employee and salary related information. Let’s look at the source data in DS_DemoData.HumMaster.EMPLOYEE:
Essentially this appears to be a Person with an added Employee number. We don’t want to add Employee number as an attribute to Person, since most persons aren’t employees, so we would like to use this same data to source both our existing Person class and a new class, Employee. This is enough for one tutorial. We will handle the rest of HumMaster in the next tutorial.
Modeling
This time, let’s make the model first, and only later decide how we will go about mapping it. In your modeling tool, draw the model like this (re-use existing class Person):
Set Employee number to be the Business key for Employee. Notice the 1 – 0..1 relationship between Person and Employee: not every Person is an Employee, but every Employee is a Person.
Export the model to your working directory.
Mapping
The high-level mappings would appear to go like this:
| Source table… | …goes to the Staging Area as… | …and maps to the ClassES |
|---|---|---|
| DS_DemoData.HumMaster.EMPLOYEE | HumMaster.RAW_EMPLOYEE | Employee Person |
The new phenomenon here is that one RAW_ table maps to several classes.
We start by adding the entire source data structure to the new mapping file 05_Mappings_HumMaster.csv and we map the columns that correspond to the Employee class like this:
We note that the PERSON_ID column uniquely identifies Person, so we go ahead and map that to represent the association between Employee and Person. Any other source column that doesn’t have a corresponding property in Employee is left unmapped.
Next we will need to map the Person properties. We notice that the data on one row can be split to the two corresponding classes like this:
By using the Alias=Person tag before the group of columns that are mapped to the Person class, we create a new table-to-class mapping set. Since the data comes from the same table, and the same row, the value in the PERSON_ID column works as both the source for the Business key of the Person class as wells the source for the relation between Employee and Person, which is why it is used in both mappings.
At this point, save the mapping file and refresh the application content.
Deploying
Select the HumMaster HR System folder in the Conceptual Model pane and then click the Table Structure button. Expand the tables to show the columns and arrange the diagram.
If your model looks like this, deploy the Person class (due to new mappings having been created for Person) as well as the contents of the HumMaster folder.
Run Tutorial Scripts
Run the following tutorial script commands fron the Help -> Tutorials -> Intro Course -> Loading Multiple Classes From One Source menu, and inspect the results.
| Script | Source data | Main points of interest |
|---|---|---|
| Step 1: Load Employee And Person | Both Employee and Person populated from same data. |
