Template Studio
Jonas De Keuster
Jonas De Keuster
·Posted in Template Studio

Computed satellite template

We've recently updated our studio templates with the possibility to create multiple layers.
Since then I've been demoing how to build a presentation layer on top of data vault using 4 templates:

- computed satellite
- custom PIT
- dimension
- fact (this case based on transactional link).

Here is an explanation on the first of those 4 templates: the computed SAT.

To define a template, we need to do some prep first. First we need to create a new template.
In this case, with the following settings:

Name: CSAT
Description: calculated satellite
Signature object: CSAT
Prefix: SAT
Suffic: CALC
Object type: Table (so will be a persisted object with DDL and ETL template)
Load Type: ALL (so our ETL logic should cover both initial and incremental loads)
Signature schema: BUSINESS VAULT
DB type: <your desired DB platform>
Base type: Sattelite on HUB (so our template will be based on raw vault satellites)

Next, we need to create our target definition. This defines what the object will look like.
In this case, I want to inherit all existing attribute types (or signatures) from the raw vault SATs.

And I want to create 1 addtitional object that calculates AGE on top of start dates (birthdates, first purchase dates,...)

For this I also need to create and apply a new signature: START_DATE, and assign it to all raw vault attributes that consider a start date of any kind.

Finally, I can start writhing template code. You'll notice that this template is fairly simple. We directly build an insert group, we define the attributes we want and where to find them in the sources.

Note that the calculated attribute contains the logic for the age calculation. See how we use our newly created signature START_DATE to apply the logic on all raw vault attributes that were tagged with that signature.


And finally, we add the source for our template, being the raw vault sat.

Template SAT_CALC

comp_group_start INSERT_GOUP INS_GRP
componentGroupRepeatedByComponent SAT

	consists of target table SAT_CALC_TGT
	componentrepeatedbycomponent CSAT

		Attribute OBJECT_H_KEY
			expressedBy SAT_SRC.OBJECT_H_KEY
				expressionRepeatedByColumn SAT_SRC.OBJECT_H_KEY

		Attribute LOAD_TIMESTAMP
			expressedBy SAT_SRC.LOAD_TIMESTAMP
				expressionRepeatedByColumn SAT_SRC.LOAD_TIMESTAMP

		Attribute LOAD_CYCLE_ID
			expressedBy SAT_SRC.LOAD_CYCLE_ID
				expressionRepeatedByColumn SAT_SRC.LOAD_CYCLE_ID

		Attribute HASH_DIFF
			expressedBy SAT_SRC.HASH_DIFF
				expressionRepeatedByColumn SAT_SRC.HASH_DIFF

		Attribute DELETE_FLAG
			expressedBy SAT_SRC.DELETE_FLAG
				expressionRepeatedByColumn SAT_SRC.DELETE_FLAG

		Attribute BUSINESS_SRC_KEY
			expressedBy SAT_SRC.BUSINESS_SRC_KEY
				expressionRepeatedByColumn SAT_SRC.BUSINESS_SRC_KEY

		Attribute PRIMARY_KEY
			expressedBy SAT_SRC.PRIMARY_KEY
				expressionRepeatedByColumn SAT_SRC.PRIMARY_KEY

        Attribute NUMBER_OF_YEARS_SINCE_START_DATE
			expressedBy  DATEDIFF(year,SAT_SRC.START_DATE,current_date())
				expressionRepeatedByColumn SAT_SRC.START_DATE
                   
	consists of source table SAT_SRC
	componentrepeatedbycomponent SAT

comp_group_end

Finally we need to select the objects on which our template will be applied: just select and link them.

Finally, see the generated code using the preview tool.

More (advanced) templates like CPIT, DIM and FACT are coming soon!

3