--- title: "Build a Point in Time Table" canonical: "https://docs.vaultspeed.com/space/VPD/3038412817/Build%20a%20Point%20in%20Time%20Table" format: markdown --- > Macro (toc) The base type HUB - LNK(Link) - LND (Many to Many Link) should be used to build a Point in Time Table. Choosing these base types will automatically include all the dependent satellites on this object. Examples: A Pit on hub_addresses with Satellite sat_ms_addresses and sat_mm_addresses can be created by choosing the base type HUB. And make sure you add the Data Vault table to the dependencies. The logic that will be written in the template: DDL ```sql drop table if exists bv_layer.pit_addresses; create table if not exists bv_layer.pit_addresses( pit_hkey varchar(64), addresses_hkey varchar(64), snapshot_date timestamp, sat_ms_addresses_adresses_hkey varchar(64), sat_ms_addresses_load_date timestamp, sat_mm_addresses_adresses_hkey varchar(64), sat_mm_addresses_load_date timestamp ); ``` ELT/ETL ```sql insert into bv_layer.pit_addresses( pit_hkey, addresses_hkey, snapshot_date, sat_ms_addresses_adresses_hkey, sat_ms_addresses_load_date, sat_mm_addresses_adresses_hkey, sat_mm_addresses_load_date ) with snapshotdates as ( select snapshot_date from bv_layer.snapshot_table ) ,sat_tgt1 as ( select sat.addresses_hkey as addresses_hkey, sat.load_date as load_date, COALESCE(LEAD(sat.load_date) OVER (PARTITION by sat.addresses_hkey ORDER BY sat.load_date),to_date('31/12/2999 23:59:59','DD/MM/YYYY HH24:MI:SS')) as LAST_LOAD_TIMESTAMP from fl_layer.sat_mm_addresses sat ) ,sat_tgt2 as ( select sat.addresses_hkey as addresses_hkey, sat.load_date as load_date, COALESCE(LEAD(sat.load_date) OVER (PARTITION by sat.addresses_hkey ORDER BY sat.load_date),to_date('31/12/2999 23:59:59','DD/MM/YYYY HH24:MI:SS')) as LAST_LOAD_TIMESTAMP from fl_layer.sat_ms_addresses sat ) select UPPER(md5(hub.addresses_hkey || ‘#' || snapshotdates.snapshot_date )) as pit_hkey, hub.addresses_hkey as addresses_hkey, sndt.snapshot_date as snapshot_date, COALESCE(sat1.addresses_hkey,unhub.addresses_hkey) as sat_ms_addresses_adresses_hkey, COALESCE(sat1.load_date,unhub.load_date) as sat_ms_addresses_load_date, COALESCE(sat2.addresses_hkey,unhub.addresses_hkey) as sat_mm_addresses_adresses_hke, COALESCE(sat2.load_date,unhub.load_date) as sat_mm_addresses_load_date from fl_layer.hub_addresses hub join mtd_layer.mtd_exceptions mex ON mex.record_type = 'U’ join fl_layer.hub_addresses unhub on unhub.load_cycle_id = mex.load_cycle_id join snapshotdates sndt ON 1 = 1 left outer join sat_tgt1 on (hub.addresses_hkey = sat_tgt1.addresses_hkey and sndt.snapshot_date >= sat_tgt1.load_date and sndt.snapshot_date < sat_tgt1.LAST_LOAD_TIMESTAMP) left outer join sat_tgt1 on (hub.addresses_hkey = sat_tgt1.addresses_hkey and sndt.snapshot_date >= sat_tgt1.load_date and sndt.snapshot_date < sat_tgt1.LAST_LOAD_TIMESTAMP) ; ``` # **Step 1: Define a new template:** Click the +Add button to add a new template: ![image](media://debbf95d-dac1-4476-8010-14635418958e) - **Name/Description:** point_in_time / Point in time table template - **Prefix/suffix:** pit / This affects the name of the target table. It will take the base type's name (without the specific pre and suffixes), and the prefix above, HUB_COUNTRIES, will become PIT_COUNTRIES as the target table. - **Signature Object**: PIT_TGT - **Object type**: This PIT will be persisted, so the Object Type table is chosen. - **Load type**: If a separate template is needed for an initial load, this can be specified here. In this case, only an incremental template will be added. So INCR is chosen from the droplist. - **Signature schema**: BUSINESS_VAULT_LAYER - **Base type**: Since a PIT is based on hubs, the base type is HUB, so all related SATS can be used in the template. - **Upload ETL:** This will be done in a next step - **Upload DDL:** This will be done in the next step Click the **Create** button when ready to create a first template definition. ![image](media://139a7860-c05e-4218-ba62-84ac12062847) The template configuration is added, but as the warning message states, no attributes for the target table (PIT) are defined yet, and the template is not yet applied to specific tables. # **Step 2: Configure template objects** Continuing with the template setup, we can define which objects the template should generate a mapping in the dependencies screen. Since the base type we have chosen in this example is HUB, this screen will show all hubs in the Data Vault. A set of unlinked Hub objects can be selected to include in the template, as shown in the screenshot below (Linked Objects table). ![image](media://8242690d-0a3b-43cb-b143-0d56187cee7a)   Clicking the arrow next to an object will show the dependent objects and their table type, which is how they can be referenced in the templates. ![image](media://408df189-a712-419c-8d94-f27005fd2582) These tab types can be used directly in the template. If just one satellite is needed, SAT$1 can be used, else, SAT can be used together with the loop symbol to loop through both Satellites. Just select the object and click the arrow > pointing to the right. ![image](media://61325dd2-8fb8-4f2e-bb59-193aad9347c6) The addresses will be the objects that the template is created on. ![image](media://0503cd1f-794d-4da8-9cd9-d07937b67f98) Once linking/unlinking of the objects is done, attributes for the target table can be defined. # **Step 3: Configure the target attribute definition.** There is a separate window for configuring target definitions, as shown below. ![image](media://d4da5e2a-1aff-4028-ba77-51c90a329e5f) The pit in this example has the following attributes: ``` pit_hkey varchar(64), addresses_hkey varchar(64), snapshot_date timestamp, sat_ms_addresses_adresses_hkey varchar(64), sat_ms_addresses_load_date timestamp, sat_mm_addresses_adresses_hkey varchar(64), sat_mm_addresses_load_date timestamp ``` So we need a ‘new’ pit hkey, the hub hkey, a ‘new’ snapshot date and new hash key for the sats and load_timestamp from the sats, so to list them for existing attributes: ## 3.1 Existing attributes In this screen, these three Signature Attributes will be added. So click the + attribute button. ![image](media://d614debd-3d48-49ce-ac5a-8ac8fe2e102f) and add the existing Signature attributes. ![image](media://740d7045-cfed-4589-b83b-0632f6cb4697) | **Signature Object** | **Signature attribute** | | --- | --- | | HUB | OBJECT_H_KEY | | SAT | LOAD_TIMESTAMP | This only determines which attributes are stored in the target object. All other attributes can still be used in the template for intermediate logic. ## 3.2 New attributes In this example, a new pit_hkey and snapshot_date should be added. These are ‘unique’ Signature attributes, not based upon an existing Signature attribute. So they can be defined as seen in the following screenshot: ![image](media://ac76d2af-9de2-4c08-99b2-3897245e19de) Also, the sat hash keys need to be stored. It's a non-unique signature attribute on top of the hash key of the satellite. We can’t just take the OBJECT_H_KEY as it would conflict with the HUB OBJECT_H_KEY. | **Signature Object** | **Signature attribute** | | --- | --- | | SAT | SAT_OBJECT_H_KEY | ![image](media://97433de5-fc99-4df9-91d2-323c9da81f19) Important remark: if a temporary attribute is needed in the template but does not need to be stored in the target table. Without defining it here, it can be used in the template using the keyword 'Artifact'. # Step 4: Build an ETL template To show how to write a template (.dvt file format) that compiles into that query, the query will be split into different blocks, and then we will show how each of these blocks can be defined in the template language: ``` /* MAIN BLOCK FOR INSERT */ insert into bv_layer.pit_addresses( ... ) /* CTE BLOCK SNAPSHOTDATES */ with snapshotdates as ( ... ) /* CTE BLOCK SAT_SRC (Will be repeated for every SAT) */ ,sat_tgt1 as ( ... ) ,sat_tgt2 as ( ... ) /* SAME MAIN BLOCK FOR INSERT */ select ... from ... join ...; ``` The main block is defined as the last block in the template. This ins_grp block will generate the INSERT statement and the main select clause. The interpreter will take care of how to print the insert block based on the target technology. Overview of blocks to be built: - SNAPSHOTDATES INL_V_GRP (CTE): Get the information about the snapshots. - SAT_SRC INL_V_GRP (CTE): Calculate the load end date using insert-only logic. - INSERT_GROUP INS_GRP (Main insert): Insert the calculations in the PIT. To start: a template always gets a name. This can match the name in the VaultSpeed studio, but is not necessary : ``` Template POINT_IN_TIME ``` Next step, defining the first CTE, Snapshotdates: ``` with snapshotdates as ( select snapshot_date from bv_layer.snapshot_table ) ``` This block itself selects the snapshot_date from the snapshot_table. When a table outside the tool is used, this can be specified by using the actual schema and name of the table instead of the table type. In this case, a CTE was unnecessary and could have been used directly in the INSERT_GROUP. Notice the use of PIT_TGT as the repeater for the group. This is because PIT_TGT is always one object. So this group will only be printed once. CTEs / inline_views are added to a template in the same way. The component_group specifies the type and alias. There is a ‘target’ which triggers the select keyword. This target must have the same name as the group but without the _GROUP suffix. Since the attribute exists in the PIT_TGT, it was defined in the target attribute screen. It can be used directly in the template. The expressionDefinedByAttribute is used together with SNAPSHOTDATES.SNAPSHOT_DATE, this is possible because snapshotdates uses the PIT_TGT type. So, the interpreter will search in the metadata of the target object for this attribute and repeat it as many times as it is available, once in this case. Usually, the expressionDefinedByAttribute is done by the same Signature Attribute as in the ExpressedBy. But this is an exception since the SSDV_SRC alias reflects an object from outside the metadata. This means that it can only be used in the ExpressedBy. ``` Template POINT_IN_TIME comp_group_start SNAPSHOTDATES_GROUP INL_V_GRP RepeatedByObject PIT_TGT consists of target inline_view SNAPSHOTDATES RepeatedByObject PIT_TGT connectsFrom (SSDV_SRC) Attribute SNAPSHOT_DATE expressedBy SSDV_SRC.SNAPSHOT_DATE expressionDefinedByAttribute SNAPSHOTDATES.SNAPSHOT_DATE consists of source table SSDV_SRC RepeatedByObject bv_layer.snapshot_table comp_group_end ``` The next step will be the SAT_TGT CTE: ``` ,sat_tgt1 as ( select sat.addresses_hkey as addresses_hkey, sat.load_date as load_date, COALESCE(LEAD(sat.load_date) OVER (PARTITION by sat.addresses_hkey ORDER BY sat.load_date),to_date('31/12/2999 23:59:59','DD/MM/YYYY HH24:MI:SS')) as LAST_LOAD_TIMESTAMP from fl_layer.sat_mm_addresses sat_src1 ) ,sat_tgt2 as ( select sat.addresses_hkey as addresses_hkey, sat.load_date as load_date, COALESCE(LEAD(sat.load_date) OVER (PARTITION by sat.addresses_hkey ORDER BY sat.load_date),to_date('31/12/2999 23:59:59','DD/MM/YYYY HH24:MI:SS')) as LAST_LOAD_TIMESTAMP from fl_layer.sat_ms_addresses sat_src1 ) ``` This is twice the same code and has to be added in the template only once by using a $-symbol to iterate. The structure of the SAT_TGT: Looping for every related SAT can be achieved by adding a $ just before the group's name and repeating it by the Signature object (SAT) without the dollar symbol. This will create a loop which repeats this template part for every satellite. In this case, there are two satellites so everything will be repeated twice. It's essential to put a dollar sign behind every alias in this group, such that they are unique in the compiled result. The $ represents the loop's index, so inside the loop, SAT$ represents the current SAT in the loop. The interpreter will replace every dollar with a dollar + the number of loops during the loop. In this example: - the type SAT$ becomes `SAT$1` &` SAT$2` which respectively refers to `sat_mm_addresses` & `sat_ms_addresses`, as seen on the dependencies screen. - the alias `SAT_SRC$`becomes `SAT_SRC1` & `SAT_SRC2` As the query can start from a table (Satellite) in the metadata, the RepeatedByObject can be a signature object and not the real name of a table. Another unique part of this template is the use of the keyword Artifact. This tells the interpreter not to look for this attribute in the metadata and use it as a temporary calculation attribute. In this case, the end_load_timestamp is just used for getting a validity period but is not used in the final target table. So no need to define it in VaultSpeed studio. The active end date is defined in the tool using the parameter CURRENT_RECORD_LOAD_END_DATE. This can be used in the template instead of typing the date directly. Example: GTIMECAST[@#CURRENT_RECORD_LOAD_END_DATE#] . The @ will tell the interpreter to look up a parameter, and the # will trigger quotes. This whole part is then cast in the correct format. ``` comp_group_start $ SAT_TGT_GROUP$ INL_V_GRP RepeatedByObject SAT consists of target inline_view SAT_TGT$ RepeatedByObject SAT$ connectsFrom (SAT_SRC$) Attribute OBJECT_H_KEY expressedBy SAT_SRC$.OBJECT_H_KEY expressionDefinedByAttribute SAT_SRC$.OBJECT_H_KEY Attribute LOAD_TIMESTAMP expressedBy SAT_SRC$.LOAD_TIMESTAMP expressionDefinedByAttribute SAT_SRC$.LOAD_TIMESTAMP Artifact LAST_LOAD_TIMESTAMP expressedBy COALESCE(LEAD(SAT_SRC$.LOAD_TIMESTAMP) OVER (PARTITION BY SAT_SRC$.OBJECT_H_KEY ORDER BY SAT_SRC$.LOAD_TIMESTAMP),GTIMECAST[@#CURRENT_RECORD_LOAD_END_DATE#]) expressionDefinedByAttribute SAT_SRC$.LOAD_TIMESTAMP consists of source table SAT_SRC$ RepeatedByObject SAT$ comp_group_end ``` All the CTEs are now added to the template, so only the main insert is left: The target is the PIT_TGT (Pit). Using the keywords target table in the INS_GRP will trigger the INSERT INTO … keywords. The target table connects from the last join with the unknown satellite record. This last element of a loop can be referenced using the LAST keyword in the connectsfrom. The pit_hkey is based on combining the object_h_key of the hub and the snapshot_date. These need to be in different expressedBy’s since the attribute in the expressionDefinedByAttribute will be used for casting. So the snapshot_date can not be in the same expressesBy as the object_h_key as they both have different data_types, and that would lead to the wrong casting. When no casting is needed, they can be used in the same expressedBy. For the OBJECT_H_KEY and LOAD_TIMESTAMP coming from the satellites, we need the $ iterator again since it should be repeated for every satellite liked to the hub. In this case, a coalesce is done with the unknown satellite record to ensure inner joins are always possible in the target satellite. Joins will always be in the format that a join component is specified and a ‘joined’ component, ensuring the interpreter knows these two belong together. The CTE queries also need to be joined, so a subgroup is used to ensure the join component and joined component are looped over together instead of one and then the other. A subgroup allows grouping components together to loop over them. Based on whether it’s the first or the next loop element, the connectsFrom will be different. Important notice, when not generating for ODI (Oracle Data integrator) or Talend, the connectsFrom can just be removed from the template. These are only needed when the target is an etl/elt tool. All joins or filters below that group also need to be in a subgroup when using a subgroup in the group. You can see an example of joining the mex table below. ``` comp_group_start MAIN_GROUP INS_GRP RepeatedByObject PIT_TGT consists of target table PIT_TGT RepeatedByObject PIT_TGT connectsFrom (JOIN_UNSAT_TGT$ LAST) Attribute PIT_HKEY GROUP_1 expressedBy HASHFUNC[ GROUP_2 expressedBy HUB_SRC.OBJECT_H_KEY || @#HASHKEY_DELIMITER# || expressionDefinedByAttribute HUB_SRC.OBJECT_H_KEY GROUP_3 expressedBy CHARCASTFRMTMAX[SNAPSHOTDATES.SNAPSHOT_DATE] expressionDefinedByAttribute SNAPSHOTDATES.SNAPSHOT_DATE GROUP_4 expressedBy HASHFUNC] Attribute OBJECT_H_KEY expressedBy HUB_SRC.OBJECT_H_KEY expressionDefinedByAttribute HUB_SRC.OBJECT_H_KEY Attribute SNAPSHOT_DATE expressedBy SNAPSHOTDATES.SNAPSHOT_DATE expressionDefinedByAttribute SNAPSHOTDATES.SNAPSHOT_DATE Attribute $ SAT_OBJECT_H_KEY RepeatedByObject SAT expressedBy COALESCE(SAT_TGT$.OBJECT_H_KEY,UNSAT_TGT$.OBJECT_H_KEY) expressionDefinedByAttribute SAT_TGT$.OBJECT_H_KEY Attribute $ LOAD_TIMESTAMP RepeatedByObject SAT expressedBy COALESCE(SAT_TGT$.LOAD_TIMESTAMP,UNSAT_TGT$.LOAD_TIMESTAMP) expressionDefinedByAttribute SAT_TGT$.LOAD_TIMESTAMP consists of source table HUB_SRC RepeatedByObject HUB consists of inner join JOIN_SNAPSHOTDATES RepeatedByObject PIT_TGT connectsFrom (HUB_SRC) connectsFrom (SNAPSHOTDATES) Artifact GENERAL_EXPRESSION expressedBy 1 = 1 consists of joined inline_view SNAPSHOTDATES RepeatedByObject PIT_TGT comp_subgroup_start $ SAT_TGT_SUBGROUP RepeatedByObject SAT consists of left_outer join JOIN_SAT_TGT$ RepeatedByObject SAT$ connectsFrom (JOIN_SNAPSHOTDATES) connectionConditionedBy [(LOOP: SAT_SPLIT_LOOP = 1)] (JOIN_SAT_TGT$ PREV) connectionConditionedBy [(LOOP: SAT_SPLIT_LOOP > 1)] connectsFrom (SAT_TGT$) Artifact GENERAL_EXPRESSION expressedBy HUB_SRC.OBJECT_H_KEY = SAT_TGT$.OBJECT_H_KEY AND SNAPSHOTDATES.SNAPSHOT_DATE >= SAT_TGT$.LOAD_TIMESTAMP AND SNAPSHOTDATES.SNAPSHOT_DATE < SAT_TGT$.LAST_LOAD_TIMESTAMP consists of joined inline_view SAT_TGT$ RepeatedByObject SAT$ comp_subgroup_end comp_subgroup_start MEX_SUBGROUP RepeatedByObject MEX consists of inner join JOIN_MEX_SRC RepeatedByObject MEX connectsFrom (JOIN_SAT_TGT$ LAST) connectsFrom (MEX_SRC) Artifact GENERAL_EXPRESSION expressedBy MEX_SRC.RECORD_TYPE = @#UNKNOWN_RECORD_TYPE# expressionDefinedByAttribute MEX_SRC.RECORD_TYPE consists of joined table MEX_SRC RepeatedByObject MEX comp_subgroup_end comp_subgroup_start $ UNSAT_TGT_SUBGROUP RepeatedByObject SAT consists of inner join JOIN_UNSAT_TGT$ RepeatedByObject SAT$ connectsFrom (JOIN_MEX_SRC) connectionConditionedBy [(LOOP: SAT_SPLIT_LOOP = 1)] (JOIN_UNSAT_TGT$ PREV) connectionConditionedBy [(LOOP: SAT_SPLIT_LOOP > 1)] connectsFrom (UNSAT_TGT$) Artifact GENERAL_EXPRESSION expressedBy INTCAST[MEX_SRC.LOAD_CYCLE_ID] = UNSAT_TGT$.LOAD_CYCLE_ID expressionDefinedByAttribute MEX_SRC.LOAD_CYCLE_ID consists of joined table UNSAT_TGT$ RepeatedByObject SAT$ comp_subgroup_end comp_group_end ``` So the complete template will look like: ``` Template POINT_IN_TIME comp_group_start SNAPSHOTDATES_GROUP INL_V_GRP RepeatedByObject PIT_TGT consists of target inline_view SNAPSHOTDATES RepeatedByObject PIT_TGT connectsFrom (SSDV_SRC) Attribute SNAPSHOT_DATE expressedBy SSDV_SRC.SNAPSHOT_DATE expressionDefinedByAttribute SNAPSHOTDATES.SNAPSHOT_DATE consists of source table SSDV_SRC RepeatedByObject bv_layer.snapshot_table comp_group_end comp_group_start $ SAT_TGT_GROUP$ INL_V_GRP RepeatedByObject SAT consists of target inline_view SAT_TGT$ RepeatedByObject SAT$ connectsFrom (SAT_SRC$) Attribute OBJECT_H_KEY expressedBy SAT_SRC$.OBJECT_H_KEY expressionDefinedByAttribute SAT_SRC$.OBJECT_H_KEY Attribute LOAD_TIMESTAMP expressedBy SAT_SRC$.LOAD_TIMESTAMP expressionDefinedByAttribute SAT_SRC$.LOAD_TIMESTAMP Artifact LAST_LOAD_TIMESTAMP expressedBy COALESCE(LEAD(SAT_SRC$.LOAD_TIMESTAMP) OVER (PARTITION BY SAT_SRC$.OBJECT_H_KEY ORDER BY SAT_SRC$.LOAD_TIMESTAMP),GTIMECAST[@#CURRENT_RECORD_LOAD_END_DATE#]) expressionDefinedByAttribute SAT_SRC$.LOAD_TIMESTAMP consists of source table SAT_SRC$ RepeatedByObject SAT$ comp_group_end comp_group_start MAIN_GROUP INS_GRP RepeatedByObject PIT_TGT consists of target table PIT_TGT RepeatedByObject PIT_TGT connectsFrom (JOIN_UNSAT_TGT$ LAST) Attribute PIT_HKEY GROUP_1 expressedBy HASHFUNC[ GROUP_2 expressedBy HUB_SRC.OBJECT_H_KEY || @#HASHKEY_DELIMITER# || expressionDefinedByAttribute HUB_SRC.OBJECT_H_KEY GROUP_3 expressedBy CHARCASTFRMTMAX[SNAPSHOTDATES.SNAPSHOT_DATE] expressionDefinedByAttribute SNAPSHOTDATES.SNAPSHOT_DATE GROUP_4 expressedBy HASHFUNC] Attribute OBJECT_H_KEY expressedBy HUB_SRC.OBJECT_H_KEY expressionDefinedByAttribute HUB_SRC.OBJECT_H_KEY Attribute SNAPSHOT_DATE expressedBy SNAPSHOTDATES.SNAPSHOT_DATE expressionDefinedByAttribute SNAPSHOTDATES.SNAPSHOT_DATE Attribute $ SAT_OBJECT_H_KEY RepeatedByObject SAT expressedBy COALESCE(SAT_TGT$.OBJECT_H_KEY,UNSAT_TGT$.OBJECT_H_KEY) expressionDefinedByAttribute SAT_TGT$.OBJECT_H_KEY Attribute $ LOAD_TIMESTAMP RepeatedByObject SAT expressedBy COALESCE(SAT_TGT$.LOAD_TIMESTAMP,UNSAT_TGT$.LOAD_TIMESTAMP) expressionDefinedByAttribute SAT_TGT$.LOAD_TIMESTAMP consists of source table HUB_SRC RepeatedByObject HUB consists of inner join JOIN_SNAPSHOTDATES RepeatedByObject PIT_TGT connectsFrom (HUB_SRC) connectsFrom (SNAPSHOTDATES) Artifact GENERAL_EXPRESSION expressedBy 1 = 1 consists of joined inline_view SNAPSHOTDATES RepeatedByObject PIT_TGT comp_subgroup_start $ SAT_TGT_SUBGROUP RepeatedByObject SAT consists of left_outer join JOIN_SAT_TGT$ RepeatedByObject SAT$ connectsFrom (JOIN_SNAPSHOTDATES) connectionConditionedBy [(LOOP: SAT_SPLIT_LOOP = 1)] (JOIN_SAT_TGT$ PREV) connectionConditionedBy [(LOOP: SAT_SPLIT_LOOP > 1)] connectsFrom (SAT_TGT$) Artifact GENERAL_EXPRESSION expressedBy HUB_SRC.OBJECT_H_KEY = SAT_TGT$.OBJECT_H_KEY AND SNAPSHOTDATES.SNAPSHOT_DATE >= SAT_TGT$.LOAD_TIMESTAMP AND SNAPSHOTDATES.SNAPSHOT_DATE < SAT_TGT$.LAST_LOAD_TIMESTAMP consists of joined inline_view SAT_TGT$ RepeatedByObject SAT$ comp_subgroup_end comp_subgroup_start MEX_SUBGROUP RepeatedByObject MEX consists of inner join JOIN_MEX_SRC RepeatedByObject MEX connectsFrom (JOIN_SAT_TGT$ LAST) connectsFrom (MEX_SRC) Artifact GENERAL_EXPRESSION expressedBy MEX_SRC.RECORD_TYPE = @#UNKNOWN_RECORD_TYPE# expressionDefinedByAttribute MEX_SRC.RECORD_TYPE consists of joined table MEX_SRC RepeatedByObject MEX comp_subgroup_end comp_subgroup_start $ UNSAT_TGT_SUBGROUP RepeatedByObject SAT consists of inner join JOIN_UNSAT_TGT$ RepeatedByObject SAT$ connectsFrom (JOIN_MEX_SRC) connectionConditionedBy [(LOOP: SAT_SPLIT_LOOP = 1)] (JOIN_UNSAT_TGT$ PREV) connectionConditionedBy [(LOOP: SAT_SPLIT_LOOP > 1)] connectsFrom (UNSAT_TGT$) Artifact GENERAL_EXPRESSION expressedBy INTCAST[MEX_SRC.LOAD_CYCLE_ID] = UNSAT_TGT$.LOAD_CYCLE_ID expressionDefinedByAttribute MEX_SRC.LOAD_CYCLE_ID consists of joined table UNSAT_TGT$ RepeatedByObject SAT$ comp_subgroup_end comp_group_end ``` Once this template is written, it can be uploaded in the Vaultspeed studio. # Step 5: Build a DDL template Because our target PIT persisted, we also need to build a DDL template. The structure of the DDL template completely matches the structure of the ‘target table’ in the ETL template, so to transform this into a DDL template: - Take the target table and its attributes and write a DDL component group shell around it. - Change the target into create_table. - Remove the connectsfrom and all other components. - Remove the expressedBy, expressionDefinedByAttribute and expressionConditionedBy So this will give you the next template: ``` Template POINT_IN_TIME comp_group_start DDL_GROUP CREA_GRP RepeatedByObject PIT_TGT consists of CREATE_TABLE table PIT_TGT RepeatedByObject PIT_TGT Attribute PIT_HKEY Attribute OBJECT_H_KEY Attribute SNAPSHOT_DATE Attribute SAT_OBJECT_H_KEY Attribute LOAD_TIMESTAMP comp_group_end ``` Important to notice is that an attribute can have multiple Signature Attributes, but the interpreter will make sure every attribute will only be printed once in the script. # **Step 6: Upload the template(s)** Click View on the line in the templates table to upload the templates. This will bring you to the editor. Click Upload or copy and paste into the editor. ![image](media://ea293022-ab25-4e42-b6e8-c6cd245d01cb) Choose your template (.dvt) file ![image](media://5502829d-7b81-4549-a55d-fa0845b64613) Now click Upload and Save. ![image](media://bfdfbd1f-363f-42cb-adde-973c9eebe037) If the validation test passed: ![image](media://24a33be8-5512-4480-b589-7c36caceb9b6) Once all your templates are ok, the business Vault can be locked, and code can be generated. The ETL and DDL for custom templates will be generated together with the other Business Vault objects during the ETL generation. Note that the DDL for the custom templates is included in the ETL generation instead of a separate DDL generation.