---
title: "Build Dimension"
canonical: "https://docs.vaultspeed.com/space/VPD/3291349211/Build%20Dimension"
format: markdown
---
> Macro (toc)

To build dimensions, the VaultSpeed studio can be used when logic can be repeatable.  
The Data Vault model, in this case, looks like this:

![image](media://bbdd6ce3-3eda-4d5f-87fc-a31bfa313ceb)

Internal Pits were configured 

![image](media://6cf524f5-502e-45b6-8be4-8ff3371bb0a2)

![image](media://b58d821a-0ac1-4681-bec0-4f148c3ef57a)

# Dimension

The logic that will be written in the template is a dimension with multiple levels (in this case, three):  
The dimension hash key on the lowest level equals the hub hashkeys. The other levels are based upon the attributes in that level.

```sql
	INSERT INTO "information_mart"."dim_bi_hourly_addresses"(
		 "dim_addresses_hkey"
		,"snapshot_timestamp"
		,"address_number_id"
		,"street_number"
		,"street_name"
		,"postal_code_id"
		,"postal_code"
		,"city"
		,"province_id"
		,"province"
	)
	WITH "not_exists_in_dimension" AS 
	( 
		SELECT DISTINCT 
 			  "pit_src"."snapshot_timestamp" AS "snapshot_timestamp"
		FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src"
		LEFT OUTER JOIN "information_mart"."dim_bi_hourly_addresses" "dim_src" ON  "pit_src"."snapshot_timestamp" > COALESCE("dim_src"."snapshot_timestamp", TO_TIMESTAMP('01/01/1900 00:00:01', 
			'DD/MM/YYYY HH24:MI:SS'::varchar))
	)
	SELECT 
		  "hub_src"."addresses_hkey" AS "dim_addresses_hkey"
		, "pit_src_main"."snapshot_timestamp" AS "snapshot_timestamp"
		, "sat_src2"."address_number" AS "address_number_id"
		, "sat_src2"."street_number" AS "street_number"
		, "sat_src2"."street_name" AS "street_name"
		, "sat_src3"."postal_code" AS "postal_code_id"
		, "sat_src3"."postal_code" AS "postal_code"
		, "sat_src3"."city" AS "city"
		, "sat_src1"."province" AS "province_id"
		, "sat_src1"."province" AS "province"
	FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src_main"
	INNER JOIN "dv_vsstd_opt_fl"."hub_addresses" "hub_src" ON  "pit_src_main"."addresses_hkey" = "hub_src"."addresses_hkey"
	INNER JOIN "not_exists_in_dimension" "not_exists_in_dimension" ON  "pit_src_main"."snapshot_timestamp" = "not_exists_in_dimension"."snapshot_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_province" "sat_src1" ON  "pit_src_main"."sat_msvs_addresses_province_hkey" = "sat_src1"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_province_trans_timestamp" =
		 "sat_src1"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_street" "sat_src2" ON  "pit_src_main"."sat_msvs_addresses_street_hkey" = "sat_src2"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_street_trans_timestamp" =
		 "sat_src2"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src3" ON  "pit_src_main"."sat_msvs_addresses_city_hkey" = "sat_src3"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_city_trans_timestamp" =
		 "sat_src3"."trans_timestamp"
	UNION 
	SELECT 
		  UPPER(ENCODE(DIGEST(  "sat_src3"."postal_code"  || "hub_src"."addresses_hkey" ,'MD5'),'HEX')) AS "dim_addresses_hkey"
		, "pit_src_main"."snapshot_timestamp" AS "snapshot_timestamp"
		, TO_NUMBER("mex_src"."key_attribute_numeric", '9999999999999D9999999999'::varchar) AS "address_number_id"
		, TO_NUMBER("mex_src"."key_attribute_numeric", '9999999999999D9999999999'::varchar) AS "street_number"
		, "mex_src"."key_attribute_varchar"::text AS "street_name"
		, "sat_src3"."postal_code" AS "postal_code_id"
		, "sat_src3"."postal_code" AS "postal_code"
		, "sat_src3"."city" AS "city"
		, "sat_src1"."province" AS "province_id"
		, "sat_src1"."province" AS "province"
	FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src_main"
	INNER JOIN "dv_vsstd_opt_fl"."hub_addresses" "hub_src" ON  "pit_src_main"."addresses_hkey" = "hub_src"."addresses_hkey"
	INNER JOIN "not_exists_in_dimension" "not_exists_in_dimension" ON  "pit_src_main"."snapshot_timestamp" = "not_exists_in_dimension"."snapshot_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_province" "sat_src1" ON  "pit_src_main"."sat_msvs_addresses_province_hkey" = "sat_src1"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_province_trans_timestamp" =
		 "sat_src1"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_street" "sat_src2" ON  "pit_src_main"."sat_msvs_addresses_street_hkey" = "sat_src2"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_street_trans_timestamp" =
		 "sat_src2"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src3" ON  "pit_src_main"."sat_msvs_addresses_city_hkey" = "sat_src3"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_city_trans_timestamp" =
		 "sat_src3"."trans_timestamp"
	INNER JOIN "moto_sales_vsstd_mtd"."mtd_exception_records" "mex_src" ON  1 = 1
	WHERE  "mex_src"."record_type" = 'N'
	UNION 
	SELECT 
		  UPPER(ENCODE(DIGEST(  "sat_src1"."province"  || "hub_src"."addresses_hkey" ,'MD5'),'HEX')) AS "dim_addresses_hkey"
		, "pit_src_main"."snapshot_timestamp" AS "snapshot_timestamp"
		, TO_NUMBER("mex_src"."key_attribute_numeric", '9999999999999D9999999999'::varchar) AS "address_number_id"
		, TO_NUMBER("mex_src"."key_attribute_numeric", '9999999999999D9999999999'::varchar) AS "street_number"
		, "mex_src"."key_attribute_varchar"::text AS "street_name"
		, "mex_src"."key_attribute_varchar"::text AS "postal_code_id"
		, "mex_src"."key_attribute_varchar"::text AS "postal_code"
		, "mex_src"."key_attribute_varchar"::text AS "city"
		, "sat_src1"."province" AS "province_id"
		, "sat_src1"."province" AS "province"
	FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src_main"
	INNER JOIN "dv_vsstd_opt_fl"."hub_addresses" "hub_src" ON  "pit_src_main"."addresses_hkey" = "hub_src"."addresses_hkey"
	INNER JOIN "not_exists_in_dimension" "not_exists_in_dimension" ON  "pit_src_main"."snapshot_timestamp" = "not_exists_in_dimension"."snapshot_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_province" "sat_src1" ON  "pit_src_main"."sat_msvs_addresses_province_hkey" = "sat_src1"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_province_trans_timestamp" =
		 "sat_src1"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_street" "sat_src2" ON  "pit_src_main"."sat_msvs_addresses_street_hkey" = "sat_src2"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_street_trans_timestamp" =
		 "sat_src2"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src3" ON  "pit_src_main"."sat_msvs_addresses_city_hkey" = "sat_src3"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_city_trans_timestamp" =
		 "sat_src3"."trans_timestamp"
	INNER JOIN "moto_sales_vsstd_mtd"."mtd_exception_records" "mex_src" ON  1 = 1
	WHERE  "mex_src"."record_type" = 'N'
	;
```

As we can see in the SQL, the table should be deployed in the information_mart schema.

**Signature Schema**

We can define different schemas in VaultSpeed studio. Noteworthy, these are expected to be already available in the database and will never be created by VaultSpeed.

To add the schema, click on:

![image](media://ad3b4c2e-8e3f-42aa-9f8e-ab85aa12a6ab)

and then click 

![image](media://7602eeb0-8477-4dfd-b0c6-9970db3b35e0)


and add the schema:  


![image](media://0b77f885-8ae2-4e0b-90c7-7d2f12ab323c)

The order will be the Business Vault + Order added here. This ensures that generating FMC code will load schemas in the correct order.

When the schema is added, go back to the main VaultSpeed studio screen.

**Signature object**

As in our example, different SATs represent different levels of the dimensions. Signature objects are added to tag those level SATS.

![image](media://0e675d5b-dc8c-4e1c-a275-0fd62b040504)

and assigned to the different satellites

![image](media://4b75920e-e03e-4460-bcc5-528f838a33a6)

![image](media://8339dd4d-8bc2-4f2c-b229-291ea52058ad)

![image](media://e6bd7326-17f4-4256-a6e6-59f1f2f46fe0)

As seen above, not every dimension will have the same levels. Only the object addresses has three levels, while products and parts only have two.  
But we will define the template so that it can handle these differences.

**Signature attributes**

To define the Key, Name and Code for each level, signature attributes are added to tag those attributes.

![image](media://bf3e5633-f667-46e8-aea3-bd21aa42ffe7)

And then assigned to the Data Vault objects

![image](media://ad0a462c-bc9c-4da2-8e48-45d659eee45f)

![image](media://c75ad18d-5b94-4e95-a98a-cc546706960c)

## **1.1: Define a new template:**

Click the +Add button to add a new template:

![image](media://c0551baf-eb82-440c-b469-cd6f660ebb68)


- **Name/Description:** dim_template / star schema Dimension Template
- **Prefix/suffix:** dim / …
- **Signature object:** DIM
- **Object type**: TABLE
- **Load type**: ALL
- **Signature Schema: **information_mart
- **Base type**: Point in Time table
- **Upload ETL:** This will be done afterwards.
- **Upload DDL:** This will be done afterwards.










Click the **Create** button when ready to create a first template definition.

## **4.2: Configure dependent objects of the template**

Continuing with the template setup, we can define for which objects the template should generate a mapping in the dependencies screen. Since we have chosen PIT as the base type in this example, this screen will show all Pits. A set of unlinked PITs objects can be included in the template, as shown in the screenshot below (Linked Objects table).

![image](media://5f4d2653-4688-4227-9fcb-5ad86613c7e4)


Select the Pits(s) by checking the checkbox in this template and click the > arrow to link them to the template.

![image](media://0074844e-4b1a-4be2-be69-ed3fb80af955)


## **4.3: Configure the target attribute definition**

We need to configure the target definition for this dimension, as shown below.

![image](media://6677fe76-96c0-48f7-a4d4-cfd72eb2ebb1)

### 4.3.1 Existing attributes

The attributes in the dimension will be the new Signature attributes from the SAT and the snapshot_timestamp from the PIT.

![image](media://e51f63c1-6180-4045-bf1a-57dc7671ef3a)

### 4.3.2 New attributes

In this case, we add four UNIQUE attributes, one for each level key and the Dimension hash key based upon the hub hash key

![image](media://31d19874-3bd2-4ee0-aa65-8e26768b5dad)


## 4.4: Build the Dimension templates

To show how to write a template that compiles into that query, the query will split be split into different blocks, and we will show how each of these blocks can be defined in the template language:

```sql
CREATE OR REPLACE FUNCTION "projectvssoptimisations_proc"."bv_etl_dim_bi_hourly_addresses_dim_template"() 
RETURNS void 
LANGUAGE 'plpgsql' 

AS $function$ 
/*
 __     __          _ _                           _      __  ___  __   __   
 \ \   / /_ _ _   _| | |_ ___ ____   ___  ___  __| |     \ \/ _ \/ /  /_/   
  \ \ / / _` | | | | | __/ __|  _ \ / _ \/ _ \/ _` |      \/ / \ \/ /\      
   \ V / (_| | |_| | | |_\__ \ |_) |  __/  __/ (_| |      / / \/\ \/ /      
    \_/ \__,_|\__,_|_|\__|___/ .__/ \___|\___|\__,_|     /_/ \/_/\__/       
                             |_|                                            

Vaultspeed version: 5.2.1.12, generation date: 2023/01/10 10:31:38
DV_NAME: dv_vsstudio_optimisations - Release: R1(1) - Comment: VSStudio Optimizations Release 1 - Release date: 2022/11/25 13:22:42, 
BV release: r3(4) - Comment: 3 - Release date: 2023/01/05 10:46:29, 
SRC_NAME: moto_sales_vsstd - Release: moto_sales_vsstd(1) - Comment: R1 - Release date: 2022/11/25 00:21:18
 */


BEGIN 

BEGIN -- dimension

END;


END;
$function$;
```

In the template file (.dvt file format), nothing special is needed to trigger this function definition, only a single template definition (only when generating tables can we add multiple templates, more about this in the template documentation):

```none
Template DIMENSION
```

### 4.4.1. Not exist in dimension CTE

```sql
	WITH "not_exists_in_dimension" AS 
	( 
		SELECT DISTINCT 
 			  "pit_src"."snapshot_timestamp" AS "snapshot_timestamp"
		FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src"
		LEFT OUTER JOIN "information_mart"."dim_bi_hourly_addresses" "dim_src" ON  "pit_src"."snapshot_timestamp" > COALESCE("dim_src"."snapshot_timestamp", TO_TIMESTAMP('01/01/1900 00:00:01', 
			'DD/MM/YYYY HH24:MI:SS'::varchar))
	)
```

This can be templated as such:

```
comp_group_start NOT_EXISTS_IN_DIMENSION_GROUP INL_V_GRP
	componentGroupRepeatedByComponent PIT

	consists of target distinct NOT_EXISTS_IN_DIMENSION
	componentrepeatedbycomponent PIT
 
		Attribute SNAPSHOT_TIMESTAMP
			expressedBy PIT_SRC.SNAPSHOT_TIMESTAMP
				expressionRepeatedByColumn PIT_SRC.SNAPSHOT_TIMESTAMP	

	consists of source table PIT_SRC
	componentrepeatedbycomponent PIT

	consists of left_outer join JOIN_DIM_SRC
	componentrepeatedbycomponent DIM

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC.SNAPSHOT_TIMESTAMP > COALESCE(DIM_SRC.SNAPSHOT_TIMESTAMP, GTIMECAST[#01/01/1900SPACE00:00:01#])
					expressionDefinedByAttribute PIT_SRC.SNAPSHOT_TIMESTAMP

	consists of joined table DIM_SRC		
	componentrepeatedbycomponent DIM

  comp_group_end
```

This is a CTE (INL_V_GRP) where a dataset of new dates is filtered based upon a left outer join is done between data coming from the PIT and checking data already available in the Dimension.

### 4.4.2. Main clause (level 1 of the dimension)

```
	INSERT INTO "information_mart"."dim_bi_hourly_addresses"(
		 "dim_addresses_hkey"
		,"snapshot_timestamp"
		,"address_number_id"
		,"street_number"
		,"street_name"
		,"postal_code_id"
		,"postal_code"
		,"city"
		,"province_id"
		,"province"
	)
	WITH "not_exists_in_dimension" AS 
	( 
		SELECT DISTINCT 
 			  "pit_src"."snapshot_timestamp" AS "snapshot_timestamp"
		FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src"
		LEFT OUTER JOIN "information_mart"."dim_bi_hourly_addresses" "dim_src" ON  "pit_src"."snapshot_timestamp" > COALESCE("dim_src"."snapshot_timestamp", TO_TIMESTAMP('01/01/1900 00:00:01', 
			'DD/MM/YYYY HH24:MI:SS'::varchar))
	)
	SELECT 
		  "hub_src"."addresses_hkey" AS "dim_addresses_hkey"
		, "pit_src_main"."snapshot_timestamp" AS "snapshot_timestamp"
		, "sat_src2"."address_number" AS "address_number_id"
		, "sat_src2"."street_number" AS "street_number"
		, "sat_src2"."street_name" AS "street_name"
		, "sat_src3"."postal_code" AS "postal_code_id"
		, "sat_src3"."postal_code" AS "postal_code"
		, "sat_src3"."city" AS "city"
		, "sat_src1"."province" AS "province_id"
		, "sat_src1"."province" AS "province"
	FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src_main"
	INNER JOIN "dv_vsstd_opt_fl"."hub_addresses" "hub_src" ON  "pit_src_main"."addresses_hkey" = "hub_src"."addresses_hkey"
	INNER JOIN "not_exists_in_dimension" "not_exists_in_dimension" ON  "pit_src_main"."snapshot_timestamp" = "not_exists_in_dimension"."snapshot_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_province" "sat_src1" ON  "pit_src_main"."sat_msvs_addresses_province_hkey" = "sat_src1"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_province_trans_timestamp" =
		 "sat_src1"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_street" "sat_src2" ON  "pit_src_main"."sat_msvs_addresses_street_hkey" = "sat_src2"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_street_trans_timestamp" =
		 "sat_src2"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src3" ON  "pit_src_main"."sat_msvs_addresses_city_hkey" = "sat_src3"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_city_trans_timestamp" =
		 "sat_src3"."trans_timestamp"
	;
```

This can be templated as

```
Template DIMENSION

comp_group_start NOT_EXISTS_IN_DIMENSION_GROUP INL_V_GRP
	componentGroupRepeatedByComponent PIT

	consists of target distinct NOT_EXISTS_IN_DIMENSION
	componentrepeatedbycomponent PIT
 
		Attribute SNAPSHOT_TIMESTAMP
			expressedBy PIT_SRC.SNAPSHOT_TIMESTAMP
				expressionRepeatedByColumn PIT_SRC.SNAPSHOT_TIMESTAMP	

	consists of source table PIT_SRC
	componentrepeatedbycomponent PIT

	consists of left_outer join JOIN_DIM_SRC
	componentrepeatedbycomponent DIM

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC.SNAPSHOT_TIMESTAMP > COALESCE(DIM_SRC.SNAPSHOT_TIMESTAMP, GTIMECAST[#01/01/1900SPACE00:00:01#])
					expressionDefinedByAttribute PIT_SRC.SNAPSHOT_TIMESTAMP

	consists of joined table DIM_SRC		
	componentrepeatedbycomponent DIM

  comp_group_end

  comp_group_start MAIN_GROUP INS_GRP
	componentGroupRepeatedByComponent DIM

	comp_subgroup_start DIM_LVL1
    componentConditionedBy [(TAB ALL_OBJECTS : SIGNATURE_OBJECT = DIM_LVL1)]
	componentsubgrouprepeatedbycomponent DIM

	consists of UNION SET DIM_TGT
	componentrepeatedbycomponent DIM
 
		Attribute DIM_OBJECT_H_KEY
			expressedBy HUB_SRC.OBJECT_H_KEY
				expressionRepeatedByColumn HUB_SRC.OBJECT_H_KEY

		Attribute SNAPSHOT_TIMESTAMP
			expressedBy PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP
				expressionRepeatedByColumn PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP				

		Attribute $ SRC_LVL1_KEY_ID
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL1_KEY
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL1_KEY

		Attribute $ SRC_LVL1_CODE
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL1_CODE
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL1_CODE

		Attribute $ SRC_LVL1_NAME
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL1_NAME
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL1_NAME

		Attribute $ SRC_LVL2_KEY_ID
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL2_KEY
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL2_KEY

		Attribute $ SRC_LVL2_CODE
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL2_CODE
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL2_CODE

		Attribute $ SRC_LVL2_NAME
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL2_NAME
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL2_NAME

		Attribute $ SRC_LVL3_KEY_ID
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL3_KEY
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL3_KEY

		Attribute $ SRC_LVL3_NAME
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL3_NAME
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL3_NAME

	consists of source table PIT_SRC_MAIN
	componentrepeatedbycomponent PIT		

	consists of inner join JOIN_HUB_SRC		
	componentrepeatedbycomponent HUB

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC_MAIN.OBJECT_L_H_KEY = HUB_SRC.OBJECT_H_KEY
					expressionRepeatedByColumn HUB_SRC.OBJECT_H_KEY

	consists of joined table HUB_SRC		
	componentrepeatedbycomponent HUB

	consists of inner join JOIN_NOT_EXISTS_IN_DIMENSION	
	componentrepeatedbycomponent PIT

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP = NOT_EXISTS_IN_DIMENSION.SNAPSHOT_TIMESTAMP
					expressionRepeatedByColumn PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP

	consists of joined inline_view NOT_EXISTS_IN_DIMENSION
	componentrepeatedbycomponent PIT

    comp_subgroup_start $ SAT_SRC_SUBGROUP
	componentConditionedBy [(TAB SAT$ : SIGNATURE_OBJECT LIKE DIM_LVL% )]
	componentsubgrouprepeatedbycomponent SAT

		consists of inner join JOIN_SAT_SRC$
		componentrepeatedbycomponent SAT$
	
					Artifact GENERAL_EXPRESSION
						GROUP_1 expressedBy PIT_SRC_MAIN.OBJECT_F_H_KEY = SAT_SRC$.OBJECT_H_KEY
							expressionRepeatedByColumn SAT_SRC$.OBJECT_H_KEY
						GROUP_2 expressedBy AND PIT_SRC_MAIN.SAT_LOAD_TIMESTAMP = SAT_SRC$.TRANS_INDICATOR
                            expressionRepeatedByColumn SAT_SRC$.TRANS_INDICATOR
                            
		consists of joined table SAT_SRC$
		componentrepeatedbycomponent SAT$

	comp_subgroup_end

	comp_subgroup_end

	comp_group_end
```

This component_group is an ins_grp as we want to trigger INSERT.   
The main target component is a UNION set because we want to union in the next step other levels to it.

As this is the lowest level the dimension hash key equals the normal addresses hash key.  
For the other attributes we take what we can find for the different levels for the id, code, name.  


The query itself is a join between the pit, hub, the not exists cte and all linked sats (Looping by $)

### 4.4.3. Main clause (all levels of the dimension)

```
	INSERT INTO "information_mart"."dim_bi_hourly_addresses"(
		 "dim_addresses_hkey"
		,"snapshot_timestamp"
		,"address_number_id"
		,"street_number"
		,"street_name"
		,"postal_code_id"
		,"postal_code"
		,"city"
		,"province_id"
		,"province"
	)
	WITH "not_exists_in_dimension" AS 
	( 
		SELECT DISTINCT 
 			  "pit_src"."snapshot_timestamp" AS "snapshot_timestamp"
		FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src"
		LEFT OUTER JOIN "information_mart"."dim_bi_hourly_addresses" "dim_src" ON  "pit_src"."snapshot_timestamp" > COALESCE("dim_src"."snapshot_timestamp", TO_TIMESTAMP('01/01/1900 00:00:01', 
			'DD/MM/YYYY HH24:MI:SS'::varchar))
	)
	SELECT 
		  "hub_src"."addresses_hkey" AS "dim_addresses_hkey"
		, "pit_src_main"."snapshot_timestamp" AS "snapshot_timestamp"
		, "sat_src2"."address_number" AS "address_number_id"
		, "sat_src2"."street_number" AS "street_number"
		, "sat_src2"."street_name" AS "street_name"
		, "sat_src3"."postal_code" AS "postal_code_id"
		, "sat_src3"."postal_code" AS "postal_code"
		, "sat_src3"."city" AS "city"
		, "sat_src1"."province" AS "province_id"
		, "sat_src1"."province" AS "province"
	FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src_main"
	INNER JOIN "dv_vsstd_opt_fl"."hub_addresses" "hub_src" ON  "pit_src_main"."addresses_hkey" = "hub_src"."addresses_hkey"
	INNER JOIN "not_exists_in_dimension" "not_exists_in_dimension" ON  "pit_src_main"."snapshot_timestamp" = "not_exists_in_dimension"."snapshot_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_province" "sat_src1" ON  "pit_src_main"."sat_msvs_addresses_province_hkey" = "sat_src1"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_province_trans_timestamp" =
		 "sat_src1"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_street" "sat_src2" ON  "pit_src_main"."sat_msvs_addresses_street_hkey" = "sat_src2"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_street_trans_timestamp" =
		 "sat_src2"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src3" ON  "pit_src_main"."sat_msvs_addresses_city_hkey" = "sat_src3"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_city_trans_timestamp" =
		 "sat_src3"."trans_timestamp"
	UNION 
	SELECT 
		  UPPER(ENCODE(DIGEST(  "sat_src3"."postal_code"  || "hub_src"."addresses_hkey" ,'MD5'),'HEX')) AS "dim_addresses_hkey"
		, "pit_src_main"."snapshot_timestamp" AS "snapshot_timestamp"
		, TO_NUMBER("mex_src"."key_attribute_numeric", '9999999999999D9999999999'::varchar) AS "address_number_id"
		, TO_NUMBER("mex_src"."key_attribute_numeric", '9999999999999D9999999999'::varchar) AS "street_number"
		, "mex_src"."key_attribute_varchar"::text AS "street_name"
		, "sat_src3"."postal_code" AS "postal_code_id"
		, "sat_src3"."postal_code" AS "postal_code"
		, "sat_src3"."city" AS "city"
		, "sat_src1"."province" AS "province_id"
		, "sat_src1"."province" AS "province"
	FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src_main"
	INNER JOIN "dv_vsstd_opt_fl"."hub_addresses" "hub_src" ON  "pit_src_main"."addresses_hkey" = "hub_src"."addresses_hkey"
	INNER JOIN "not_exists_in_dimension" "not_exists_in_dimension" ON  "pit_src_main"."snapshot_timestamp" = "not_exists_in_dimension"."snapshot_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_province" "sat_src1" ON  "pit_src_main"."sat_msvs_addresses_province_hkey" = "sat_src1"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_province_trans_timestamp" =
		 "sat_src1"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_street" "sat_src2" ON  "pit_src_main"."sat_msvs_addresses_street_hkey" = "sat_src2"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_street_trans_timestamp" =
		 "sat_src2"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src3" ON  "pit_src_main"."sat_msvs_addresses_city_hkey" = "sat_src3"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_city_trans_timestamp" =
		 "sat_src3"."trans_timestamp"
	INNER JOIN "moto_sales_vsstd_mtd"."mtd_exception_records" "mex_src" ON  1 = 1
	WHERE  "mex_src"."record_type" = 'N'
	UNION 
	SELECT 
		  UPPER(ENCODE(DIGEST(  "sat_src1"."province"  || "hub_src"."addresses_hkey" ,'MD5'),'HEX')) AS "dim_addresses_hkey"
		, "pit_src_main"."snapshot_timestamp" AS "snapshot_timestamp"
		, TO_NUMBER("mex_src"."key_attribute_numeric", '9999999999999D9999999999'::varchar) AS "address_number_id"
		, TO_NUMBER("mex_src"."key_attribute_numeric", '9999999999999D9999999999'::varchar) AS "street_number"
		, "mex_src"."key_attribute_varchar"::text AS "street_name"
		, "mex_src"."key_attribute_varchar"::text AS "postal_code_id"
		, "mex_src"."key_attribute_varchar"::text AS "postal_code"
		, "mex_src"."key_attribute_varchar"::text AS "city"
		, "sat_src1"."province" AS "province_id"
		, "sat_src1"."province" AS "province"
	FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src_main"
	INNER JOIN "dv_vsstd_opt_fl"."hub_addresses" "hub_src" ON  "pit_src_main"."addresses_hkey" = "hub_src"."addresses_hkey"
	INNER JOIN "not_exists_in_dimension" "not_exists_in_dimension" ON  "pit_src_main"."snapshot_timestamp" = "not_exists_in_dimension"."snapshot_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_province" "sat_src1" ON  "pit_src_main"."sat_msvs_addresses_province_hkey" = "sat_src1"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_province_trans_timestamp" =
		 "sat_src1"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_street" "sat_src2" ON  "pit_src_main"."sat_msvs_addresses_street_hkey" = "sat_src2"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_street_trans_timestamp" =
		 "sat_src2"."trans_timestamp"
	INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src3" ON  "pit_src_main"."sat_msvs_addresses_city_hkey" = "sat_src3"."addresses_hkey" AND "pit_src_main"."sat_msvs_addresses_city_trans_timestamp" =
		 "sat_src3"."trans_timestamp"
	INNER JOIN "moto_sales_vsstd_mtd"."mtd_exception_records" "mex_src" ON  1 = 1
	WHERE  "mex_src"."record_type" = 'N'
	;
END;
```

This can be templated as

```
Template DIMENSION

comp_group_start NOT_EXISTS_IN_DIMENSION_GROUP INL_V_GRP
	componentGroupRepeatedByComponent PIT

	consists of target distinct NOT_EXISTS_IN_DIMENSION
	componentrepeatedbycomponent PIT
 
		Attribute SNAPSHOT_TIMESTAMP
			expressedBy PIT_SRC.SNAPSHOT_TIMESTAMP
				expressionRepeatedByColumn PIT_SRC.SNAPSHOT_TIMESTAMP	

	consists of source table PIT_SRC
	componentrepeatedbycomponent PIT

	consists of left_outer join JOIN_DIM_SRC
	componentrepeatedbycomponent DIM

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC.SNAPSHOT_TIMESTAMP > COALESCE(DIM_SRC.SNAPSHOT_TIMESTAMP, GTIMECAST[#01/01/1900SPACE00:00:01#])
					expressionDefinedByAttribute PIT_SRC.SNAPSHOT_TIMESTAMP

	consists of joined table DIM_SRC		
	componentrepeatedbycomponent DIM

  comp_group_end
  
   comp_group_start MAIN_GROUP INS_GRP
	componentGroupRepeatedByComponent DIM

	comp_subgroup_start DIM_LVL1
    componentConditionedBy [(TAB ALL_OBJECTS : SIGNATURE_OBJECT = DIM_LVL1)]
	componentsubgrouprepeatedbycomponent DIM

	consists of UNION SET DIM_TGT
	componentrepeatedbycomponent DIM
 
		Attribute DIM_OBJECT_H_KEY
			expressedBy HUB_SRC.OBJECT_H_KEY
				expressionRepeatedByColumn HUB_SRC.OBJECT_H_KEY

		Attribute SNAPSHOT_TIMESTAMP
			expressedBy PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP
				expressionRepeatedByColumn PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP				

		Attribute $ SRC_LVL1_KEY_ID
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL1_KEY
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL1_KEY

		Attribute $ SRC_LVL1_CODE
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL1_CODE
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL1_CODE

		Attribute $ SRC_LVL1_NAME
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL1_NAME
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL1_NAME

		Attribute $ SRC_LVL2_KEY_ID
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL2_KEY
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL2_KEY

		Attribute $ SRC_LVL2_CODE
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL2_CODE
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL2_CODE

		Attribute $ SRC_LVL2_NAME
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL2_NAME
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL2_NAME

		Attribute $ SRC_LVL3_KEY_ID
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL3_KEY
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL3_KEY

		Attribute $ SRC_LVL3_NAME
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL3_NAME
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL3_NAME

	consists of source table PIT_SRC_MAIN
	componentrepeatedbycomponent PIT		

	consists of inner join JOIN_HUB_SRC		
	componentrepeatedbycomponent HUB

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC_MAIN.OBJECT_L_H_KEY = HUB_SRC.OBJECT_H_KEY
					expressionRepeatedByColumn HUB_SRC.OBJECT_H_KEY

	consists of joined table HUB_SRC		
	componentrepeatedbycomponent HUB

	consists of inner join JOIN_NOT_EXISTS_IN_DIMENSION	
	componentrepeatedbycomponent PIT

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP = NOT_EXISTS_IN_DIMENSION.SNAPSHOT_TIMESTAMP
					expressionRepeatedByColumn PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP

	consists of joined inline_view NOT_EXISTS_IN_DIMENSION
	componentrepeatedbycomponent PIT

    comp_subgroup_start $ SAT_SRC_SUBGROUP
	componentConditionedBy [(TAB SAT$ : SIGNATURE_OBJECT LIKE DIM_LVL% )]
	componentsubgrouprepeatedbycomponent SAT

		consists of inner join JOIN_SAT_SRC$
		componentrepeatedbycomponent SAT$
	
					Artifact GENERAL_EXPRESSION
						GROUP_1 expressedBy PIT_SRC_MAIN.OBJECT_F_H_KEY = SAT_SRC$.OBJECT_H_KEY
							expressionRepeatedByColumn SAT_SRC$.OBJECT_H_KEY
						GROUP_2 expressedBy AND PIT_SRC_MAIN.SAT_LOAD_TIMESTAMP = SAT_SRC$.TRANS_INDICATOR
                            expressionRepeatedByColumn SAT_SRC$.TRANS_INDICATOR
                            
		consists of joined table SAT_SRC$
		componentrepeatedbycomponent SAT$

	comp_subgroup_end

	comp_subgroup_end

	comp_subgroup_start DIM_LVL2
    componentConditionedBy [(TAB ALL_OBJECTS : SIGNATURE_OBJECT = DIM_LVL2)]
	componentsubgrouprepeatedbycomponent DIM

	consists of UNION SET DIM_TGT
	componentrepeatedbycomponent DIM
 
		AttributePart DIM_OBJECT_H_KEY 
			GROUP_1 expressedBy HASHFUNC[
				
		AttributePart $ DIM_OBJECT_H_KEY
		AttributeConditionedBy [(TAB SAT$ : SIGNATURE_OBJECT = DIM_LVL2)] 
		attributerepeatedbycomponent SAT
			GROUP_2 expressedBy SAT_SRC$.SRC_LVL2_KEY
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL2_KEY

		AttributePart DIM_OBJECT_H_KEY
			GROUP_3 expressedBy || HUB_SRC.OBJECT_H_KEY
				expressionRepeatedByColumn HUB_SRC.OBJECT_H_KEY					
			GROUP_4 expressedBy HASHFUNC]

		Attribute SNAPSHOT_TIMESTAMP
			expressedBy PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP
				expressionRepeatedByColumn PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP				

		Attribute $ SRC_LVL1_KEY_ID
		Attributerepeatedbycomponent SAT
			expressedBy CASTFRMT[MEX_SRC.KEY_EXR]
				expressionRepeatedByColumn DIM_TGT.SRC_LVL1_KEY_ID

		Attribute $ SRC_LVL1_CODE
		Attributerepeatedbycomponent SAT
			expressedBy CASTFRMT[MEX_SRC.KEY_EXR]
				expressionRepeatedByColumn DIM_TGT.SRC_LVL1_CODE

		Attribute $ SRC_LVL1_NAME
		Attributerepeatedbycomponent SAT
			expressedBy CASTFRMT[MEX_SRC.KEY_EXR]
				expressionRepeatedByColumn DIM_TGT.SRC_LVL1_NAME

		Attribute $ SRC_LVL2_KEY_ID
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL2_KEY
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL2_KEY

		Attribute $ SRC_LVL2_CODE
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL2_CODE
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL2_CODE

		Attribute $ SRC_LVL2_NAME
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL2_NAME
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL2_NAME

		Attribute $ SRC_LVL3_KEY_ID
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL3_KEY
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL3_KEY

		Attribute $ SRC_LVL3_NAME
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL3_NAME
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL3_NAME

	consists of source table PIT_SRC_MAIN
	componentrepeatedbycomponent PIT		

	consists of inner join JOIN_HUB_SRC		
	componentrepeatedbycomponent HUB

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC_MAIN.OBJECT_L_H_KEY = HUB_SRC.OBJECT_H_KEY
					expressionRepeatedByColumn HUB_SRC.OBJECT_H_KEY

	consists of joined table HUB_SRC		
	componentrepeatedbycomponent HUB

	consists of inner join JOIN_NOT_EXISTS_IN_DIMENSION	
	componentrepeatedbycomponent PIT

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP = NOT_EXISTS_IN_DIMENSION.SNAPSHOT_TIMESTAMP
					expressionRepeatedByColumn PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP

	consists of joined inline_view NOT_EXISTS_IN_DIMENSION
	componentrepeatedbycomponent PIT

    comp_subgroup_start $ SAT_SRC_SUBGROUP
	componentConditionedBy [(TAB SAT$ : SIGNATURE_OBJECT LIKE DIM_LVL% )]
	componentsubgrouprepeatedbycomponent SAT

		consists of inner join JOIN_SAT_SRC$
		componentrepeatedbycomponent SAT$
	
					Artifact GENERAL_EXPRESSION
						GROUP_1 expressedBy PIT_SRC_MAIN.OBJECT_F_H_KEY = SAT_SRC$.OBJECT_H_KEY
							expressionRepeatedByColumn SAT_SRC$.OBJECT_H_KEY
						GROUP_2 expressedBy AND PIT_SRC_MAIN.SAT_LOAD_TIMESTAMP = SAT_SRC$.TRANS_INDICATOR
                            expressionRepeatedByColumn SAT_SRC$.TRANS_INDICATOR
                            
		consists of joined table SAT_SRC$
		componentrepeatedbycomponent SAT$

    comp_subgroup_end

    comp_subgroup_start MEX_SUBGROUP
	componentsubgrouprepeatedbycomponent MEX

	consists of inner join JOIN_MEX_SRC
	componentrepeatedbycomponent MEX

				Artifact GENERAL_EXPRESSION				
					expressedBy 1 = 1

	consists of joined table MEX_SRC
	componentrepeatedbycomponent MEX

	consists of filter FILTER_MEX_SRC
	componentrepeatedbycomponent MEX

				Artifact GENERAL_EXPRESSION				
					expressedBy MEX_SRC.RECORD_TYPE = @#NULL_RECORD_TYPE#

    comp_subgroup_end

	comp_subgroup_end

	comp_subgroup_start DIM_LVL3
    componentConditionedBy [(TAB ALL_OBJECTS : SIGNATURE_OBJECT = DIM_LVL3)]
	componentsubgrouprepeatedbycomponent DIM

	consists of UNION SET DIM_TGT
	componentrepeatedbycomponent DIM
 
		AttributePart DIM_OBJECT_H_KEY 
			GROUP_1 expressedBy HASHFUNC[
				
		AttributePart $ DIM_OBJECT_H_KEY
		AttributeConditionedBy [(TAB SAT$ : SIGNATURE_OBJECT = DIM_LVL3)] 
		attributerepeatedbycomponent SAT
			GROUP_2 expressedBy SAT_SRC$.SRC_LVL3_KEY
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL3_KEY

		AttributePart DIM_OBJECT_H_KEY
			GROUP_3 expressedBy || HUB_SRC.OBJECT_H_KEY
				expressionRepeatedByColumn HUB_SRC.OBJECT_H_KEY					
			GROUP_4 expressedBy HASHFUNC]

		Attribute SNAPSHOT_TIMESTAMP
			expressedBy PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP
				expressionRepeatedByColumn PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP				

		Attribute $ SRC_LVL1_KEY_ID
		Attributerepeatedbycomponent SAT
			expressedBy CASTFRMT[MEX_SRC.KEY_EXR]
				expressionRepeatedByColumn DIM_TGT.SRC_LVL1_KEY_ID

		Attribute $ SRC_LVL1_CODE
		Attributerepeatedbycomponent SAT
			expressedBy CASTFRMT[MEX_SRC.KEY_EXR]
				expressionRepeatedByColumn DIM_TGT.SRC_LVL1_CODE

		Attribute $ SRC_LVL1_NAME
		Attributerepeatedbycomponent SAT
			expressedBy CASTFRMT[MEX_SRC.KEY_EXR]
				expressionRepeatedByColumn DIM_TGT.SRC_LVL1_NAME

		Attribute $ SRC_LVL2_KEY_ID
		Attributerepeatedbycomponent SAT
			expressedBy CASTFRMT[MEX_SRC.KEY_EXR]
				expressionRepeatedByColumn DIM_TGT.SRC_LVL2_KEY_ID

		Attribute $ SRC_LVL2_CODE
		Attributerepeatedbycomponent SAT
			expressedBy CASTFRMT[MEX_SRC.KEY_EXR]
				expressionRepeatedByColumn DIM_TGT.SRC_LVL2_CODE

		Attribute $ SRC_LVL2_NAME
		Attributerepeatedbycomponent SAT
			expressedBy CASTFRMT[MEX_SRC.KEY_EXR]
				expressionRepeatedByColumn DIM_TGT.SRC_LVL2_NAME

		Attribute $ SRC_LVL3_KEY_ID
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL3_KEY
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL3_KEY

		Attribute $ SRC_LVL3_NAME
		Attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.SRC_LVL3_NAME
				expressionRepeatedByColumn SAT_SRC$.SRC_LVL3_NAME

	consists of source table PIT_SRC_MAIN
	componentrepeatedbycomponent PIT		

	consists of inner join JOIN_HUB_SRC		
	componentrepeatedbycomponent HUB

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC_MAIN.OBJECT_L_H_KEY = HUB_SRC.OBJECT_H_KEY
					expressionRepeatedByColumn HUB_SRC.OBJECT_H_KEY

	consists of joined table HUB_SRC		
	componentrepeatedbycomponent HUB

	consists of inner join JOIN_NOT_EXISTS_IN_DIMENSION	
	componentrepeatedbycomponent PIT

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP = NOT_EXISTS_IN_DIMENSION.SNAPSHOT_TIMESTAMP
					expressionRepeatedByColumn PIT_SRC_MAIN.SNAPSHOT_TIMESTAMP

	consists of joined inline_view NOT_EXISTS_IN_DIMENSION
	componentrepeatedbycomponent PIT

    comp_subgroup_start $ SAT_SRC_SUBGROUP
	componentsubgrouprepeatedbycomponent SAT

		consists of inner join JOIN_SAT_SRC$
        componentConditionedBy [(TAB SAT$ : SIGNATURE_OBJECT LIKE DIM_LVL% )]
		componentrepeatedbycomponent SAT$
	
					Artifact GENERAL_EXPRESSION
						GROUP_1 expressedBy PIT_SRC_MAIN.OBJECT_F_H_KEY = SAT_SRC$.OBJECT_H_KEY
							expressionRepeatedByColumn SAT_SRC$.OBJECT_H_KEY
						GROUP_2 expressedBy AND PIT_SRC_MAIN.SAT_LOAD_TIMESTAMP = SAT_SRC$.TRANS_INDICATOR
                            expressionRepeatedByColumn SAT_SRC$.TRANS_INDICATOR
                            
		consists of joined table SAT_SRC$
	    componentConditionedBy [(TAB SAT$ : SIGNATURE_OBJECT LIKE DIM_LVL% )]
		componentrepeatedbycomponent SAT$

	comp_subgroup_end

    comp_subgroup_start MEX_SUBGROUP
	componentsubgrouprepeatedbycomponent MEX

	consists of inner join JOIN_MEX_SRC
	componentrepeatedbycomponent MEX

				Artifact GENERAL_EXPRESSION				
					expressedBy 1 = 1

	consists of joined table MEX_SRC
	componentrepeatedbycomponent MEX

	consists of filter FILTER_MEX_SRC
	componentrepeatedbycomponent MEX

				Artifact GENERAL_EXPRESSION				
					expressedBy MEX_SRC.RECORD_TYPE = @#NULL_RECORD_TYPE#

    comp_subgroup_end
    
	comp_subgroup_end

	comp_group_end
```

For the other levels, the template is almost a copy paste.  
The main differences are the calculation of a dimension hash key (the levels are having unique hash keys based upon the keys of the level and the hash key of the hub) and also a join with the MEX (Exception record) table to have Null value replacements for attributes of more detailed levels.

## **4.5: Upload the ETL Dimension template**

If the template was built in Visual Studio Code, it could be uploaded by clicking on the View button of the template. And then Clicking Upload in the editor.

![image](media://d8d5d5de-b8ed-4594-81b0-7f9a6377a7c3)

Choose your template (.dvt) file

Now click Upload and Save.

## **4.6: Upload the Dimension template**

For the DDL, just take the ETL template and strip one level out of the Union INS_GRP.  
  
Update the **INS_GRP** to **CREA_GRP** and update the **UNION SET** to **CREATE_TABLE** **table.**

Then remove the dollar signs that were used and everything lower than the attribute level.

The template should look then like this.

```
Template DIMENSION
  
comp_group_start MAIN_GROUP CREA_GRP
componentGroupRepeatedByComponent DIM

	consists of CREATE_TABLE table DIM_TGT
	componentrepeatedbycomponent DIM
 
		Attribute DIM_OBJECT_H_KEY

		Attribute SNAPSHOT_TIMESTAMP			

		Attribute SRC_LVL1_KEY_ID

		Attribute SRC_LVL1_CODE

		Attribute SRC_LVL1_NAME

		Attribute SRC_LVL2_KEY_ID

		Attribute SRC_LVL2_CODE

		Attribute SRC_LVL2_NAME

		Attribute SRC_LVL3_KEY_ID

		Attribute SRC_LVL3_NAME

	comp_group_end
```

Upload it into the VaultSpeed Studio by Clicking View on the DDL Template column.

![image](media://43d33e2b-82bd-46c6-87e6-7125c962bc77)

And Upload and Save.

![image](media://23238663-b35c-47b0-9dd8-fb66a6fe39d8)