---
title: "Build a Supernova view in Denodo"
canonical: "https://docs.vaultspeed.com/space/VPD/3291447886/Build%20a%20Supernova%20view%20in%20Denodo"
format: markdown
---
For general Denodo information, please check the Denodo section in  [https://vaultspeed.atlassian.net/wiki/spaces/VPD/pages/3013705746](https://vaultspeed.atlassian.net/wiki/spaces/VPD/pages/3013705746).  

To build a Denodo supernova view, we will show an example based upon a Pit linked with its related hub and sats.

In the following example, we will use VaultSpeed Studio to build multiple templates:

- PIT (Persisted table)
- Supernova view (Remote view)

# 1: Define a new template for the hub pits

The logic that will be written in the template:

DDL:

```sql
CREATE TABLE "rep_pattern_dv_bv"."pit_customers"
(
		 "pit_hkey" VARCHAR
		,"customers_hkey" VARCHAR(32)
		,"sat_customers_hkey" VARCHAR(32)
		,"snapshot_date" TIMESTAMP
		,"end_snapshot_date" TIMESTAMP
		,"load_date" TIMESTAMP
) 
;
```

ETL:

```sql
CREATE OR REPLACE FUNCTION "rep_pattern_proc"."bv_etl_pit_customers_hub_pit"() 
RETURNS void 
LANGUAGE 'plpgsql' 

AS $function$ 

BEGIN 

BEGIN -- point_in_time

	INSERT INTO "rep_pattern_dv_bv"."pit_customers"(
		 "pit_hkey"
		,"customers_hkey"
		,"snapshot_date"
		,"end_snapshot_date"
		,"sat_customers_hkey"
		,"load_date"
	)
	WITH "snapshotdates" AS 
	( 
		SELECT 
		DISTINCT 
			  "sat_src1"."customers_hkey" AS "customers_hkey"
			, "sat_src1"."load_date" AS "snapshot_date"
		FROM "rep_pattern_dv_fl"."sat_rep_pattern_customers" "sat_src1"
	)
	, "sat_tgt1" AS 
	( 
		SELECT 
			  "sat_src1"."customers_hkey" AS "customers_hkey"
			, "sat_src1"."load_date" AS "load_date"
			, COALESCE(LEAD("sat_src1"."load_date")OVER(PARTITION BY "sat_src1"."customers_hkey" ORDER BY "sat_src1"."load_date")
				, TO_TIMESTAMP('31/12/2999 23:59:59', 'DD/MM/YYYY HH24:MI:SS'::varchar)) AS "ending_load_timestamp"
		FROM "rep_pattern_dv_fl"."sat_rep_pattern_customers" "sat_src1"
	)
	SELECT 
		  UPPER(ENCODE(DIGEST( "hub_src"."customers_hkey" || '#' || TO_CHAR("snapshotdates"."snapshot_date", 
			'DD/MM/YYYY HH24:MI:SS.US'::varchar) ,'MD5'),'HEX')) AS "pit_hkey"
		, "hub_src"."customers_hkey" AS "customers_hkey"
		, "snapshotdates"."snapshot_date" AS "snapshot_date"
		, COALESCE(LEAD("snapshotdates"."snapshot_date")OVER(PARTITION BY "hub_src"."customers_hkey" ORDER BY "snapshotdates"."snapshot_date")
			, TO_TIMESTAMP('31/12/2999 23:59:59', 'DD/MM/YYYY HH24:MI:SS'::varchar)) AS "end_snapshot_date"
		, COALESCE("sat_tgt1"."customers_hkey","unsat_tgt1"."customers_hkey") AS "sat_customers_hkey"
		, COALESCE("sat_tgt1"."load_date","unsat_tgt1"."load_date") AS "load_date"
	FROM "rep_pattern_dv_fl"."hub_customers" "hub_src"
	INNER JOIN "snapshotdates" "snapshotdates" ON  "snapshotdates"."customers_hkey" = "hub_src"."customers_hkey"
	LEFT OUTER JOIN "sat_tgt1" "sat_tgt1" ON  "hub_src"."customers_hkey" = "sat_tgt1"."customers_hkey" AND "snapshotdates"."snapshot_date" >= "sat_tgt1"."load_date" AND 
		"snapshotdates"."snapshot_date" < "sat_tgt1"."ending_load_timestamp"
	INNER JOIN "rep_pattern_mtd"."mtd_exception_records" "mex_src" ON  "mex_src"."record_type" = 'U'
	INNER JOIN "rep_pattern_dv_fl"."sat_rep_pattern_customers" "unsat_tgt1" ON  "mex_src"."load_cycle_id"::int = "unsat_tgt1"."load_cycle_id"
	;
END;


END;
$function$;
```

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

Click the +Add button to add a new template:

![image](media://b4484f0c-b173-4da3-956c-46f69a8e34dc)

 

- **Name/Description:** hub_pit / hub_pit
- **Prefix/suffix:** pit / …
- **Signature object: **cpit
- **Object type**: TABLE
- **Load type**: ALL
- **Signature Schema: **Business Vault Layer
- **Base type**: HUB
- **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.

![image](media://03a5c700-f52c-4212-9dc1-931db735da91)

As seen in the screenshot above, a template should appear in the overview.

## **1.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 HUB as the base type in this example, this screen will show all HUBs in the Data Vaults. A set of unlinked Hub objects can be included in the template, as shown in the screenshot below (Linked Objects table).

![image](media://c60efb25-cd74-406c-b632-85f5ff0e7262)

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

![image](media://e14d287e-c796-4304-9def-a9336d13bf69)

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

There is a separate window for configuring target definitions, as shown below.

![image](media://c60efb25-cd74-406c-b632-85f5ff0e7262)

The pit needs a few attributes from the hub and the satellite

### 1.3.1 Existing attributes

To add attributes, click the

![image](media://a1eae82f-be47-4171-b801-d67921d3825b)

In this case, we want the hub object_h_key and the satellite load_timestamp to be an attribute of the final pit object.

![image](media://9db476a1-9595-4fe4-b750-87dbb636b289)

### 1.3.2 New attributes

In this case, we add three UNIQUE signature attributes, which means they are not based on an existing attribute:

- PIT_HKEY (VARCHAR)
- SNAPSHOT_DATE (TIMESTAMP)
- END_SNAPSHOT_DATE (TIMESTAMP)

![image](media://b9f5ccb0-fbf5-47e3-a4c5-58bc666a4ce0)


Next, we add one attribute based upon an existing attribute (Not UNIQUE). We didn’t add this OBJECT_H_KEY of the satellite in the Existing attributes because we want the attribute to be distinct from the hub OBJECT_H_KEY.

- Prefix: SAT - > based upon OBJECT_H_KEY of the SAT. (VARCHAR)

![image](media://f4f1b468-a7e9-42f3-a269-c8e49c2b7ded)

## 1.4: Build the PIT HUB 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 "rep_pattern_proc"."bv_etl_pit_customers_hub_pit"() 
RETURNS void 
LANGUAGE 'plpgsql' 

AS $function$ 

BEGIN 

BEGIN -- point_in_time


......



END;


END;
$function$;


```

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

```
Template point_in_time
```

Next :

```sql
INSERT INTO "rep_pattern_dv_bv"."pit_customers"(
		 "pit_hkey"
		,"customers_hkey"
		,"snapshot_date"
		,"end_snapshot_date"
		,"sat_customers_hkey"
		,"load_date"
	)
	WITH "snapshotdates" AS 
	(
		SELECT 
		DISTINCT 
			  "sat_src1"."customers_hkey" AS "customers_hkey"
			, "sat_src1"."load_date" AS "snapshot_date"
		FROM "rep_pattern_dv_fl"."sat_rep_pattern_customers" "sat_src1"
	)
	, "sat_tgt1" AS 
	( 
		SELECT 
			  "sat_src1"."customers_hkey" AS "customers_hkey"
			, "sat_src1"."load_date" AS "load_date"
			, COALESCE(LEAD("sat_src1"."load_date")OVER(PARTITION BY "sat_src1"."customers_hkey" ORDER BY "sat_src1"."load_date")
				, TO_TIMESTAMP('31/12/2999 23:59:59', 'DD/MM/YYYY HH24:MI:SS'::varchar)) AS "ending_load_timestamp"
		FROM "rep_pattern_dv_fl"."sat_rep_pattern_customers" "sat_src1"
	)
	SELECT 
		  UPPER(ENCODE(DIGEST( "hub_src"."customers_hkey" || '#' || TO_CHAR("snapshotdates"."snapshot_date", 
			'DD/MM/YYYY HH24:MI:SS.US'::varchar) ,'MD5'),'HEX')) AS "pit_hkey"
		, "hub_src"."customers_hkey" AS "customers_hkey"
		, "snapshotdates"."snapshot_date" AS "snapshot_date"
		, COALESCE(LEAD("snapshotdates"."snapshot_date")OVER(PARTITION BY "hub_src"."customers_hkey" ORDER BY "snapshotdates"."snapshot_date")
			, TO_TIMESTAMP('31/12/2999 23:59:59', 'DD/MM/YYYY HH24:MI:SS'::varchar)) AS "end_snapshot_date"
		, COALESCE("sat_tgt1"."customers_hkey","unsat_tgt1"."customers_hkey") AS "sat_customers_hkey"
		, COALESCE("sat_tgt1"."load_date","unsat_tgt1"."load_date") AS "load_date"
	FROM "rep_pattern_dv_fl"."hub_customers" "hub_src"
	INNER JOIN "snapshotdates" "snapshotdates" ON  "snapshotdates"."customers_hkey" = "hub_src"."customers_hkey"
	LEFT OUTER JOIN "sat_tgt1" "sat_tgt1" ON  "hub_src"."customers_hkey" = "sat_tgt1"."customers_hkey" AND "snapshotdates"."snapshot_date" >= "sat_tgt1"."load_date" AND 
		"snapshotdates"."snapshot_date" < "sat_tgt1"."ending_load_timestamp"
	INNER JOIN "rep_pattern_mtd"."mtd_exception_records" "mex_src" ON  "mex_src"."record_type" = 'U'
	INNER JOIN "rep_pattern_dv_fl"."sat_rep_pattern_customers" "unsat_tgt1" ON  "mex_src"."load_cycle_id"::int = "unsat_tgt1"."load_cycle_id"
	;
```

This block itself is the entire query which we are going to split into three blocks:

- **CTE** snapshotdates
- **CTE** sat_tgt1
- **MAIN INSERT/SELECT**

Important to notice that CTEs will always be first in a template and that the INSERT/SELECT is always the last block in a template. Based on the database type, the interpreter will reorder the blocks to ensure they comply with the database.

### 1.4.1. CTE SNAPSHOTDATES

```sql
WITH "snapshotdates" AS 
	( 
		SELECT 
		DISTINCT 
			  "sat_src1"."customers_hkey" AS "customers_hkey"
			, "sat_src1"."load_date" AS "snapshot_date"
		FROM "rep_pattern_dv_fl"."sat_rep_pattern_customers" "sat_src1"
	)
```

This can be templated as such:

```
comp_group_start SNAPSHOTDATES_GROUP INL_V_GRP
repeatedByObject CPIT

	comp_subgroup_start $ SNAPSHOTDATES
	repeatedByObject SAT

			consists of UNION set SSDV_TGT
			repeatedByObject CPIT
			With Integrationtype distinsert

				Attribute OBJECT_H_KEY
					expressedBy SAT_SRC$.OBJECT_H_KEY
						expressionDefinedByAttribute SAT_SRC$.OBJECT_H_KEY

				Attribute SNAPSHOT_DATE
					expressedBy SAT_SRC$.LOAD_TIMESTAMP
					expressionDefinedByAttribute SAT_SRC$.LOAD_TIMESTAMP

			consists of source table SAT_SRC$
			repeatedByObject SAT$

		comp_subgroup_end


comp_group_end
```

The target signature object of this template was defined as CPIT, which contains the new attributes OBJECT_H_KEY and SNAPSHOT_DATE used here for the union. The reason for a Union set is the possibility of having multiple satellites attached to this HUB/PIT.

The Dollar triggers this loop based on the signature object SAT. As many SATS there are, so many loops will be triggered.

We use a subgroup here to repeat these two components (union set + source table) together.

### 1.4.2. CTE SAT_TGT

```sql
"sat_tgt1" AS 
	( 
		SELECT 
			  "sat_src1"."customers_hkey" AS "customers_hkey"
			, "sat_src1"."load_date" AS "load_date"
			, COALESCE(LEAD("sat_src1"."load_date")OVER(PARTITION BY "sat_src1"."customers_hkey" ORDER BY "sat_src1"."load_date")
				, TO_TIMESTAMP('31/12/2999 23:59:59', 'DD/MM/YYYY HH24:MI:SS'::varchar)) AS "ending_load_timestamp"
		FROM "rep_pattern_dv_fl"."sat_rep_pattern_customers" "sat_src1"
	)
```

This can be templated as such:

```
comp_group_start $ SAT_TGT_GROUP$ INL_V_GRP
repeatedByObject SAT

	consists of target inline_view SAT_TGT$
	repeatedByObject SAT$

			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 ENDING_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
```

This whole CTE will be repeated for every satellite linked to the hub, so the loop is, in this case, on the component group (CTE) level.  
From this satellite, we want to collect the object_h_key, load_timestamp and calculate an end_load_timestamp.   
Unique here is the use of the Artifact keyword instead of Attribute because ending_load_timestamp is just a temporary alias not in the metadata of any object, so just for calculations.

### 1.4.3. MAIN INSERT/SELECT

```sql
SELECT 
		  UPPER(ENCODE(DIGEST( "hub_src"."customers_hkey" || '#' || TO_CHAR("snapshotdates"."snapshot_date", 
			'DD/MM/YYYY HH24:MI:SS.US'::varchar) ,'MD5'),'HEX')) AS "pit_hkey"
		, "hub_src"."customers_hkey" AS "customers_hkey"
		, "snapshotdates"."snapshot_date" AS "snapshot_date"
		, COALESCE(LEAD("snapshotdates"."snapshot_date")OVER(PARTITION BY "hub_src"."customers_hkey" ORDER BY "snapshotdates"."snapshot_date")
			, TO_TIMESTAMP('31/12/2999 23:59:59', 'DD/MM/YYYY HH24:MI:SS'::varchar)) AS "end_snapshot_date"
		, COALESCE("sat_tgt1"."customers_hkey","unsat_tgt1"."customers_hkey") AS "sat_customers_hkey"
		, COALESCE("sat_tgt1"."load_date","unsat_tgt1"."load_date") AS "load_date"
	FROM "rep_pattern_dv_fl"."hub_customers" "hub_src"
	INNER JOIN "snapshotdates" "snapshotdates" ON  "snapshotdates"."customers_hkey" = "hub_src"."customers_hkey"
	LEFT OUTER JOIN "sat_tgt1" "sat_tgt1" ON  "hub_src"."customers_hkey" = "sat_tgt1"."customers_hkey" AND "snapshotdates"."snapshot_date" >= "sat_tgt1"."load_date" AND 
		"snapshotdates"."snapshot_date" < "sat_tgt1"."ending_load_timestamp"
	INNER JOIN "rep_pattern_mtd"."mtd_exception_records" "mex_src" ON  "mex_src"."record_type" = 'U'
	INNER JOIN "rep_pattern_dv_fl"."sat_rep_pattern_customers" "unsat_tgt1" ON  "mex_src"."load_cycle_id"::int = "unsat_tgt1"."load_cycle_id"
	;
```

This can be templated as such:

```
comp_group_start MAIN_GROUP INS_GRP
repeatedByObject CPIT

	consists of target table PIT_TGT
	repeatedByObject CPIT
 
		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 END_SNAPSHOT_DATE
			expressedBy COALESCE(LEAD(SNAPSHOTDATES.SNAPSHOT_DATE) OVER (PARTITION BY HUB_SRC.OBJECT_H_KEY ORDER BY SNAPSHOTDATES.SNAPSHOT_DATE),GTIMECAST[@#CURRENT_RECORD_LOAD_END_DATE#])
				expressionDefinedByAttribute SNAPSHOTDATES.SNAPSHOT_DATE		

		Attribute $ SAT_OBJECT_H_KEY
		attributerepeatedbycomponent SAT
			expressedBy COALESCE(SAT_TGT$.OBJECT_H_KEY,UNSAT_TGT$.OBJECT_H_KEY)
				expressionDefinedByAttribute SAT_TGT$.OBJECT_H_KEY

		Attribute $ LOAD_TIMESTAMP
		attributerepeatedbycomponent 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 CPIT

					Artifact GENERAL_EXPRESSION				
						expressedBy SNAPSHOTDATES.OBJECT_H_KEY = HUB_SRC.OBJECT_H_KEY
							expressionDefinedByAttribute SNAPSHOTDATES.OBJECT_H_KEY		

	consists of joined inline_view SNAPSHOTDATES		
	repeatedByObject CPIT

	consists of inner join JOIN_MEX_SRC		
	repeatedByObject MEX

				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_start $ SAT_TGT_SUBGROUP
	repeatedByObject SAT

		consists of left_outer join JOIN_SAT_TGT$
		repeatedByObject SAT$
	
					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$.ENDING_LOAD_TIMESTAMP


		consists of joined inline_view SAT_TGT$
		repeatedByObject  SAT$

	comp_subgroup_end
			
    comp_subgroup_start $ UNSAT_TGT_SUBGROUP
	repeatedByObject SAT

		consists of inner join JOIN_UNSAT_TGT$
		repeatedByObject SAT$
	
					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

```

In this case, we join the hub with the snapshot_dates, the satellite, and the unknown satellite to fill the gaps.

Unique parts in this template are the GROUP_… in front of the ‘expressedby’, which are used when different expression parts need to be repeated by different attributes.

Also, the subgroup is used again to ensure the join and joined table are repeated together.

### 1.4.4. Full ETL Template

```
Template POINT_IN_TIME

comp_group_start SNAPSHOTDATES_GROUP INL_V_GRP
repeatedByObject CPIT

	comp_subgroup_start $ SNAPSHOTDATES
	repeatedByObject SAT

			consists of UNION set SSDV_TGT
			repeatedByObject  CPIT
			With Integrationtype distinsert

				Attribute OBJECT_H_KEY
					expressedBy SAT_SRC$.OBJECT_H_KEY
						expressionDefinedByAttribute SAT_SRC$.OBJECT_H_KEY

				Attribute SNAPSHOT_DATE
					expressedBy SAT_SRC$.LOAD_TIMESTAMP
					expressionDefinedByAttribute SAT_SRC$.LOAD_TIMESTAMP

			consists of source table SAT_SRC$
			repeatedByObject SAT$

		comp_subgroup_end


comp_group_end

comp_group_start $ SAT_TGT_GROUP$ INL_V_GRP
repeatedByObject SAT

	consists of target inline_view SAT_TGT$
	repeatedByObject SAT$

			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 ENDING_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 CPIT

	consists of target table PIT_TGT
	repeatedByObject CPIT
 
		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 END_SNAPSHOT_DATE
			expressedBy COALESCE(LEAD(SNAPSHOTDATES.SNAPSHOT_DATE) OVER (PARTITION BY HUB_SRC.OBJECT_H_KEY ORDER BY SNAPSHOTDATES.SNAPSHOT_DATE),GTIMECAST[@#CURRENT_RECORD_LOAD_END_DATE#])
				expressionDefinedByAttribute SNAPSHOTDATES.SNAPSHOT_DATE		

		Attribute $ SAT_OBJECT_H_KEY
		attributerepeatedbycomponent SAT
			expressedBy COALESCE(SAT_TGT$.OBJECT_H_KEY,UNSAT_TGT$.OBJECT_H_KEY)
				expressionDefinedByAttribute SAT_TGT$.OBJECT_H_KEY

		Attribute $ LOAD_TIMESTAMP
		attributerepeatedbycomponent 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 CPIT

		Artifact GENERAL_EXPRESSION				
			expressedBy SNAPSHOTDATES.OBJECT_H_KEY = HUB_SRC.OBJECT_H_KEY
				expressionDefinedByAttribute SNAPSHOTDATES.OBJECT_H_KEY		

	consists of joined inline_view SNAPSHOTDATES		
	repeatedByObject CPIT

	consists of inner join JOIN_MEX_SRC		
	repeatedByObject MEX

		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_start $ SAT_TGT_SUBGROUP
	repeatedByObject SAT

		consists of left_outer join JOIN_SAT_TGT$
		repeatedByObject SAT$
	
					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$.ENDING_LOAD_TIMESTAMP


		consists of joined inline_view SAT_TGT$
		repeatedByObject  SAT$

	comp_subgroup_end
			
    comp_subgroup_start $ UNSAT_TGT_SUBGROUP
	repeatedByObject SAT

		consists of inner join JOIN_UNSAT_TGT$
		repeatedByObject SAT$
	
					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

```

### 1.4.5. Full 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 attributes and write a DDL component group shell around it.
- Change the target into create_table.
- Remove the expressedBy, expressionDefinedByAttribute and expressionConditionedBy. This will give you the following template:

```
Template POINT_IN_TIME
	
    comp_group_start DDL_GROUP CREA_GRP
	repeatedByObject CPIT

		consists of CREATE_TABLE table PIT_TGT
		repeatedByObject CPIT
 
		Attribute PIT_HKEY

		Attribute OBJECT_H_KEY
		
		Attribute SAT_OBJECT_H_KEY

		Attribute SNAPSHOT_DATE				

		Attribute END_SNAPSHOT_DATE

		Attribute LOAD_TIMESTAMP
	
	comp_group_end

```

Important to notice that an attribute can have multiple Signature Attributes, but the interpreter will ensure every attribute will only be printed once in the script.

## **1.5: Upload the PIT HUB templates**

Click on the View button on the template.

![image](media://b602e7c2-fd7f-491e-b656-d0e08c057b21)

Click upload or copy-paste code in the editor and click save.

![image](media://dcf0c987-7469-4a5f-9987-f8239e6eaac4)

# 2: Define a new template for the Supernova View

The logic that will be written in the template:

VIEW

```sql
DROP VIEW IF EXISTS "supernova_hub_customers";
CREATE VIEW "supernova_hub_customers"
FOLDER = '/SUPERNOVA'
DESCRIPTION = '/**/'
AS
SELECT DISTINCT 
		  "hub_src"."customers_hkey" AS "customers_hkey"
		, "hub_src"."national_person_id_bk" AS "national_person_id_bk"
		, "pit_src"."snapshot_date" AS "load_date"
		, "pit_src"."end_snapshot_date" AS "end_load_timestamp"
		, "sat_src1"."first_name" AS "first_name"
		, "sat_src1"."last_name" AS "last_name"
		, "sat_src1"."birth_date" AS "birth_date"
		, "sat_src1"."gender" AS "gender"
	FROM "rep_pattern_dv_bv_pit_customers" "pit_src"
	INNER JOIN "rep_pattern_dv_fl_hub_customers" "hub_src" ON  "pit_src"."customers_hkey" = "hub_src"."customers_hkey"
	LEFT OUTER JOIN "rep_pattern_dv_fl_sat_rep_pattern_customers" "sat_src1" ON  "hub_src"."customers_hkey" = "sat_src1"."customers_hkey" AND "pit_src"."snapshot_date" = "sat_src1"."load_date";
```


## **2.1: Define a new template:**

Click the +Add button to add a new template:

![image](media://6dc87d68-d18e-4d7f-8ded-930e2781e15f)

 

- **Name/Description:** supernova_hub / supernova hub
- **Prefix/suffix:** supernova_hub / …
- **Signature object:** SHUB
- **Object type**: REMOTE VIEW
- **Load type**: ALL
- **Database type:** Denodo
- **Folder: **/SUPERNOVA
- **Base type**: HUB
- **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.

![image](media://7de56ef4-eef1-4895-8d4b-956cb15fc766)

As seen in the screenshot above, a template should appear in the overview.

## **2.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 HUB as the base type in this example, this screen will show all Hubs. A set of unlinked HUBs objects can be included in the template, as shown in the screenshot below (Linked Objects table).

![image](media://f4e62a41-356b-415d-9150-1c7667b7909b)

 

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

![image](media://047d5ce6-fc65-4926-9fa7-52782d4c839b)


Clicking on the > next to hub_customers will show which signature objects can be used on top of the base type. (HUB). So in this template, we will be able to use the SATs and the already built pit template CPIT.

![image](media://7fd3496d-075e-49e3-b2e5-c0a742f13f18)

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

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

![image](media://f4e62a41-356b-415d-9150-1c7667b7909b)

### 2.3.1 Existing attributes

The attributes in the supernova view from other objects in this example will be hub business_keys, hash key and load_timestamp and the descriptive attributes from the satellite.

![image](media://f55a8056-c16d-4cf4-9ba6-4ce1c2f3db80)

### 2.3.2 New attributes

In this case, we add three UNIQUE attributes, a hash key which will be the values of the pit hash key and an end_load_timestamp.

- END_LOAD_TIMESTAMP (TIMESTAMP)

![image](media://f14e0131-ef22-40b4-b502-5366235ae9db)

## 2.4: Build the Supernova view 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
DROP VIEW IF EXISTS "supernova_hub_customers";
CREATE VIEW "supernova_hub_customers"
FOLDER = '/SUPERNOVA'
DESCRIPTION = '/**/'
AS
```

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

```
Template SUPERNOVA
```

### 2.4.1. Main insert/select

```sql
SELECT DISTINCT 
		  "hub_src"."customers_hkey" AS "customers_hkey"
		, "hub_src"."national_person_id_bk" AS "national_person_id_bk"
		, "pit_src"."snapshot_date" AS "load_date"
		, "pit_src"."end_snapshot_date" AS "end_load_timestamp"
		, "sat_src1"."first_name" AS "first_name"
		, "sat_src1"."last_name" AS "last_name"
		, "sat_src1"."birth_date" AS "birth_date"
		, "sat_src1"."gender" AS "gender"
	FROM "rep_pattern_dv_bv_pit_customers" "pit_src"
	INNER JOIN "rep_pattern_dv_fl_hub_customers" "hub_src" ON  "pit_src"."customers_hkey" = "hub_src"."customers_hkey"
	LEFT OUTER JOIN "rep_pattern_dv_fl_sat_rep_pattern_customers" "sat_src1" ON  "hub_src"."customers_hkey" = "sat_src1"."customers_hkey" AND "pit_src"."snapshot_date" = "sat_src1"."load_date";
```

This block itself is the full query:

- **MAIN INSERT/SELECT**

This can be templated as such:

```
comp_group_start MAIN_GROUP INS_GRP
  repeatedByObject SHUB

	consists of target table SHUB_TGT
	repeatedByObject SHUB
 
	With Integrationtype distinsert

		Attribute OBJECT_H_KEY
			expressedBy HUB_SRC.OBJECT_H_KEY
				expressionDefinedByAttribute HUB_SRC.OBJECT_H_KEY

		Attribute BUSINESS_KEY
			expressedBy HUB_SRC.BUSINESS_KEY
				expressionDefinedByAttribute HUB_SRC.BUSINESS_KEY				

		Attribute LOAD_TIMESTAMP
			expressedBy PIT_SRC.SNAPSHOT_DATE
				expressionDefinedByAttribute PIT_SRC.SNAPSHOT_DATE	

		Attribute END_LOAD_TIMESTAMP
		attributerepeatedbycomponent SAT
			expressedBy PIT_SRC.END_SNAPSHOT_DATE
				expressionDefinedByAttribute PIT_SRC.END_SNAPSHOT_DATE	

		Attribute $ OTHER_ATTR
		attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.OTHER_ATTR
				expressionDefinedByAttribute SAT_SRC$.OTHER_ATTR	

	consists of source table PIT_SRC
	repeatedByObject CPIT		

	consists of inner join JOIN_HUB_SRC		
	repeatedByObject HUB

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC.OBJECT_H_KEY = HUB_SRC.OBJECT_H_KEY

	consists of joined table HUB_SRC		
	repeatedByObject HUB
			
       comp_subgroup_start $ SAT_SRC_SUBGROUP
	repeatedByObject SAT

		consists of left_outer join JOIN_SAT_SRC$
		repeatedByObject SAT$
	
					Artifact GENERAL_EXPRESSION
						expressedBy HUB_SRC.OBJECT_H_KEY = SAT_SRC$.OBJECT_H_KEY AND PIT_SRC.SNAPSHOT_DATE = SAT_SRC$.LOAD_TIMESTAMP

		consists of joined table SAT_SRC$
		repeatedByObject  SAT$

	comp_subgroup_end

comp_group_end
```

This is a join between the pit component, hub component and multiple sat component, which explains the subgroup. The “With Integrationtype distinsert" will trigger the distinct keyword.

### 2.4.2. Full ETL Template

```
Template SUPERNOVA
  
  comp_group_start MAIN_GROUP INS_GRP
  repeatedByObject SHUB

	consists of target table SHUB_TGT
	repeatedByObject SHUB
 
    With Integrationtype distinsert
    
		Attribute OBJECT_H_KEY
			expressedBy HUB_SRC.OBJECT_H_KEY
				expressionDefinedByAttribute HUB_SRC.OBJECT_H_KEY

		Attribute BUSINESS_KEY
			expressedBy HUB_SRC.BUSINESS_KEY
				expressionDefinedByAttribute HUB_SRC.BUSINESS_KEY				

		Attribute LOAD_TIMESTAMP
			expressedBy PIT_SRC.SNAPSHOT_DATE
				expressionDefinedByAttribute PIT_SRC.SNAPSHOT_DATE	

		Attribute END_LOAD_TIMESTAMP
		attributerepeatedbycomponent SAT
			expressedBy PIT_SRC.END_SNAPSHOT_DATE
				expressionDefinedByAttribute PIT_SRC.END_SNAPSHOT_DATE	

		Attribute $ OTHER_ATTR
		attributerepeatedbycomponent SAT
			expressedBy SAT_SRC$.OTHER_ATTR
				expressionDefinedByAttribute SAT_SRC$.OTHER_ATTR	

	consists of source table PIT_SRC
	repeatedByObject CPIT		

	consists of inner join JOIN_HUB_SRC		
	repeatedByObject HUB

				Artifact GENERAL_EXPRESSION				
					expressedBy PIT_SRC.OBJECT_H_KEY = HUB_SRC.OBJECT_H_KEY

	consists of joined table HUB_SRC		
	repeatedByObject HUB
			
       comp_subgroup_start $ SAT_SRC_SUBGROUP
	repeatedByObject SAT

		consists of left_outer join JOIN_SAT_SRC$
		repeatedByObject SAT$
	
					Artifact GENERAL_EXPRESSION
						expressedBy HUB_SRC.OBJECT_H_KEY = SAT_SRC$.OBJECT_H_KEY AND PIT_SRC.SNAPSHOT_DATE = SAT_SRC$.LOAD_TIMESTAMP

		consists of joined table SAT_SRC$
		repeatedByObject SAT$

	comp_subgroup_end

comp_group_end
```

## **2.5: Upload the Supernova view template**

Click the View on the template.

![image](media://e5a774ed-be7f-4805-aea9-f421153b4257)

 Choose your template (.dvt) file.

Now click save changes, and the view button should be enabled.

![image](media://8d9c95c9-e287-44fb-8e51-93b10f02cf7c)