--- title: "Build Fact" canonical: "https://docs.vaultspeed.com/space/VPD/3364389275/Build%20Fact" format: markdown --- > Macro (toc) To build Facts, the VaultSpeed studio can be used when logic can be repeatable. The Data Vault model, in this case, looks like this: ![image](media://60591405-8276-46ed-a3ba-da8fedd1bd53) Internal Bridges were configured Bridge address_part_invoice ![image](media://31f4ab9a-6531-4aca-914d-1874f3bd9659) Bridge address_product_invoice ![image](media://1d6bc6a0-3dee-4737-aa15-02d77768d25b) # Bridge The logic that will be written in the template is a bridge linking to the second level of the dimensions: ```sql INSERT INTO "information_mart"."fact_address_part_invoice"( "fact_snapshot_timestamp" ,"dim_addresses_hkey" ,"dim_parts_hkey" ,"level_snapshot_timestamp" ,"sum_unit_price" ,"sum_quantity" ,"sum_amount" ,"avg_unit_price" ,"avg_quantity" ,"avg_amount" ) WITH "not_exists_in_fact_pit_day" AS ( SELECT "pit_ss1_1"."snapshot_timestamp" AS "fact_snapshot_timestamp" , date_trunc('day',"pit_ss1_1"."snapshot_timestamp") AS "level_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_daily_pit_addresses" "pit_ss1_1" LEFT OUTER JOIN "information_mart"."fact_address_part_invoice" "fact_src" ON "pit_ss1_1"."snapshot_timestamp" > COALESCE("fact_src"."fact_snapshot_timestamp", TO_TIMESTAMP('01/01/1900 00:00:01', 'DD/MM/YYYY HH24:MI:SS'::varchar)) ) , "not_exists_in_fact_pit_month" AS ( SELECT DISTINCT "pit_ss1_2"."snapshot_timestamp" AS "fact_snapshot_timestamp" , date_trunc('month',"pit_ss1_2"."snapshot_timestamp") AS "level_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_monthly_pit_addresses" "pit_ss1_2" LEFT OUTER JOIN "information_mart"."fact_address_part_invoice" "fact_src" ON "pit_ss1_2"."snapshot_timestamp" > COALESCE("fact_src"."fact_snapshot_timestamp", TO_TIMESTAMP('01/01/1900 00:00:01', 'DD/MM/YYYY HH24:MI:SS'::varchar)) ) , "not_exists_in_fact_pit_bihourly" AS ( SELECT DISTINCT "pit_ss1_3"."snapshot_timestamp" AS "fact_snapshot_timestamp" , date_trunc('hourly',"pit_ss1_3"."snapshot_timestamp") AS "level_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_ss1_3" LEFT OUTER JOIN "information_mart"."fact_address_part_invoice" "fact_src" ON "pit_ss1_3"."snapshot_timestamp" > COALESCE("fact_src"."fact_snapshot_timestamp", TO_TIMESTAMP('01/01/1900 00:00:01', 'DD/MM/YYYY HH24:MI:SS'::varchar)) ) , "pit_dim1_3" AS ( SELECT "pit_src1_3"."addresses_hkey" AS "addresses_hkey" , UPPER(ENCODE(DIGEST( "sat_src1_3_3"."postal_code" || "pit_src1_3"."addresses_hkey" ,'MD5'),'HEX')) AS "dim_lvl2_object_h_key" , "pit_src1_3"."snapshot_timestamp" AS "snapshot_timestamp" , date_trunc('hourly',"pit_src1_3"."snapshot_timestamp") AS "bihourly_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src1_3" INNER JOIN "not_exists_in_fact_pit_bihourly" "not_exists_in_fact_pit_bihourly" ON "pit_src1_3"."snapshot_timestamp" = "not_exists_in_fact_pit_bihourly"."fact_snapshot_timestamp" INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src1_3_3" ON "pit_src1_3"."sat_msvs_addresses_city_hkey" = "sat_src1_3_3"."addresses_hkey" AND "pit_src1_3"."sat_msvs_addresses_city_trans_timestamp" = "sat_src1_3_3"."trans_timestamp" ) , "pit_dim1_2" AS ( SELECT "pit_src1_2"."addresses_hkey" AS "addresses_hkey" , UPPER(ENCODE(DIGEST( "sat_src1_2_3"."postal_code" || "pit_src1_2"."addresses_hkey" ,'MD5'),'HEX')) AS "dim_lvl2_object_h_key" , "pit_src1_2"."snapshot_timestamp" AS "snapshot_timestamp" , date_trunc('month',"pit_src1_2"."snapshot_timestamp") AS "month_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_monthly_pit_addresses" "pit_src1_2" INNER JOIN "not_exists_in_fact_pit_month" "not_exists_in_fact_pit_month" ON "pit_src1_2"."snapshot_timestamp" = "not_exists_in_fact_pit_month"."fact_snapshot_timestamp" INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src1_2_3" ON "pit_src1_2"."sat_msvs_addresses_city_hkey" = "sat_src1_2_3"."addresses_hkey" AND "pit_src1_2"."sat_msvs_addresses_city_trans_timestamp" = "sat_src1_2_3"."trans_timestamp" ) , "pit_dim1_1" AS ( SELECT "pit_src1_1"."addresses_hkey" AS "addresses_hkey" , UPPER(ENCODE(DIGEST( "sat_src1_1_3"."postal_code" || "pit_src1_1"."addresses_hkey" ,'MD5'),'HEX')) AS "dim_lvl2_object_h_key" , "pit_src1_1"."snapshot_timestamp" AS "snapshot_timestamp" , date_trunc('day',"pit_src1_1"."snapshot_timestamp") AS "day_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_daily_pit_addresses" "pit_src1_1" INNER JOIN "not_exists_in_fact_pit_day" "not_exists_in_fact_pit_day" ON "pit_src1_1"."snapshot_timestamp" = "not_exists_in_fact_pit_day"."fact_snapshot_timestamp" INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src1_1_3" ON "pit_src1_1"."sat_msvs_addresses_city_hkey" = "sat_src1_1_3"."addresses_hkey" AND "pit_src1_1"."sat_msvs_addresses_city_trans_timestamp" = "sat_src1_1_3"."trans_timestamp" ) , "pit_dim9_2" AS ( SELECT "pit_src9_2"."parts_hkey" AS "parts_hkey" , UPPER(ENCODE(DIGEST( "sat_src9_2_2"."part_category_code" || "pit_src9_2"."parts_hkey" ,'MD5'),'HEX')) AS "dim_lvl2_object_h_key" , "pit_src9_2"."snapshot_timestamp" AS "snapshot_timestamp" , date_trunc('month',"pit_src9_2"."snapshot_timestamp") AS "month_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_monthly_pit_parts" "pit_src9_2" INNER JOIN "not_exists_in_fact_pit_month" "not_exists_in_fact_pit_month" ON "pit_src9_2"."snapshot_timestamp" = "not_exists_in_fact_pit_month"."fact_snapshot_timestamp" INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_parts_category" "sat_src9_2_2" ON "pit_src9_2"."sat_msvs_parts_category_hkey" = "sat_src9_2_2"."parts_hkey" AND "pit_src9_2"."sat_msvs_parts_category_trans_timestamp" = "sat_src9_2_2"."trans_timestamp" ) , "pit_dim9_1" AS ( SELECT "pit_src9_1"."parts_hkey" AS "parts_hkey" , UPPER(ENCODE(DIGEST( "sat_src9_1_2"."part_category_code" || "pit_src9_1"."parts_hkey" ,'MD5'),'HEX')) AS "dim_lvl2_object_h_key" , "pit_src9_1"."snapshot_timestamp" AS "snapshot_timestamp" , date_trunc('day',"pit_src9_1"."snapshot_timestamp") AS "day_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_daily_pit_parts" "pit_src9_1" INNER JOIN "not_exists_in_fact_pit_day" "not_exists_in_fact_pit_day" ON "pit_src9_1"."snapshot_timestamp" = "not_exists_in_fact_pit_day"."fact_snapshot_timestamp" INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_parts_category" "sat_src9_1_2" ON "pit_src9_1"."sat_msvs_parts_category_hkey" = "sat_src9_1_2"."parts_hkey" AND "pit_src9_1"."sat_msvs_parts_category_trans_timestamp" = "sat_src9_1_2"."trans_timestamp" ) , "pit_dim9_3" AS ( SELECT "pit_src9_3"."parts_hkey" AS "parts_hkey" , UPPER(ENCODE(DIGEST( "sat_src9_3_2"."part_category_code" || "pit_src9_3"."parts_hkey" ,'MD5'),'HEX')) AS "dim_lvl2_object_h_key" , "pit_src9_3"."snapshot_timestamp" AS "snapshot_timestamp" , date_trunc('hourly',"pit_src9_3"."snapshot_timestamp") AS "bihourly_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_parts" "pit_src9_3" INNER JOIN "not_exists_in_fact_pit_bihourly" "not_exists_in_fact_pit_bihourly" ON "pit_src9_3"."snapshot_timestamp" = "not_exists_in_fact_pit_bihourly"."fact_snapshot_timestamp" INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_parts_category" "sat_src9_3_2" ON "pit_src9_3"."sat_msvs_parts_category_hkey" = "sat_src9_3_2"."parts_hkey" AND "pit_src9_3"."sat_msvs_parts_category_trans_timestamp" = "sat_src9_3_2"."trans_timestamp" ) , "sat_fact7_2" AS ( SELECT "sat_src7_2"."invoice_lines_hkey" AS "invoice_lines_hkey" , date_trunc('day',"sat_src7_2"."trans_timestamp") AS "day_trans_timestamp" , date_trunc('month',"sat_src7_2"."trans_timestamp") AS "month_trans_timestamp" , date_trunc('hourly',"sat_src7_2"."trans_timestamp") AS "hourly_trans_timestamp" , "sat_src7_2"."unit_price" AS "unit_price" , "sat_src7_2"."quantity" AS "quantity" , "sat_src7_2"."amount" AS "amount" FROM "dv_vsstd_opt_fl"."sat_msvs_invoice_lines_other" "sat_src7_2" ) , "aggr_day" AS ( SELECT "pit_dim1_1"."snapshot_timestamp" AS "fact_snapshot_timestamp" , "pit_dim1_1"."dim_lvl2_object_h_key" AS "dim_addresses_hkey" , "pit_dim9_1"."dim_lvl2_object_h_key" AS "dim_parts_hkey" , "not_exists_in_fact_pit_day"."level_snapshot_timestamp" AS "level_snapshot_timestamp" , SUM("sat_fact7_2"."unit_price") AS "sum_unit_price" , SUM("sat_fact7_2"."quantity") AS "sum_quantity" , SUM("sat_fact7_2"."amount") AS "sum_amount" , AVG("sat_fact7_2"."unit_price") AS "avg_unit_price" , AVG("sat_fact7_2"."quantity") AS "avg_quantity" , AVG("sat_fact7_2"."amount") AS "avg_amount" FROM "dv_vsstd_opt_bv"."bridge_address_part_invoice" "bridge_src" INNER JOIN "not_exists_in_fact_pit_day" "not_exists_in_fact_pit_day" ON 1 = 1 INNER JOIN "pit_dim1_1" "pit_dim1_1" ON "bridge_src"."addresses_hkey" = "pit_dim1_1"."addresses_hkey" AND "not_exists_in_fact_pit_day"."level_snapshot_timestamp" = "pit_dim1_1"."day_snapshot_timestamp" INNER JOIN "pit_dim9_1" "pit_dim9_1" ON "bridge_src"."parts_hkey" = "pit_dim9_1"."parts_hkey" AND "not_exists_in_fact_pit_day"."level_snapshot_timestamp" = "pit_dim9_1"."day_snapshot_timestamp" INNER JOIN "sat_fact7_2" "sat_fact7_2" ON "bridge_src"."invoice_lines_hkey" = "sat_fact7_2"."invoice_lines_hkey" AND "not_exists_in_fact_pit_day"."level_snapshot_timestamp" = "sat_fact7_2"."day_trans_timestamp" GROUP BY "pit_dim1_1"."snapshot_timestamp", "pit_dim1_1"."dim_lvl2_object_h_key", "pit_dim9_1"."dim_lvl2_object_h_key", "not_exists_in_fact_pit_day"."level_snapshot_timestamp" ) , "aggr_month" AS ( SELECT "pit_dim1_2"."snapshot_timestamp" AS "fact_snapshot_timestamp" , "pit_dim1_2"."dim_lvl2_object_h_key" AS "dim_addresses_hkey" , "pit_dim9_2"."dim_lvl2_object_h_key" AS "dim_parts_hkey" , "not_exists_in_fact_pit_month"."level_snapshot_timestamp" AS "level_snapshot_timestamp" , SUM("sat_fact7_2"."unit_price") AS "sum_unit_price" , SUM("sat_fact7_2"."quantity") AS "sum_quantity" , SUM("sat_fact7_2"."amount") AS "sum_amount" , AVG("sat_fact7_2"."unit_price") AS "avg_unit_price" , AVG("sat_fact7_2"."quantity") AS "avg_quantity" , AVG("sat_fact7_2"."amount") AS "avg_amount" FROM "dv_vsstd_opt_bv"."bridge_address_part_invoice" "bridge_src" INNER JOIN "not_exists_in_fact_pit_month" "not_exists_in_fact_pit_month" ON 1 = 1 INNER JOIN "pit_dim1_2" "pit_dim1_2" ON "bridge_src"."addresses_hkey" = "pit_dim1_2"."addresses_hkey" AND "not_exists_in_fact_pit_month"."level_snapshot_timestamp" = "pit_dim1_2"."month_snapshot_timestamp" INNER JOIN "pit_dim9_2" "pit_dim9_2" ON "bridge_src"."parts_hkey" = "pit_dim9_2"."parts_hkey" AND "not_exists_in_fact_pit_month"."level_snapshot_timestamp" = "pit_dim9_2"."month_snapshot_timestamp" INNER JOIN "sat_fact7_2" "sat_fact7_2" ON "bridge_src"."invoice_lines_hkey" = "sat_fact7_2"."invoice_lines_hkey" AND "not_exists_in_fact_pit_month"."level_snapshot_timestamp" = "sat_fact7_2"."month_trans_timestamp" GROUP BY "pit_dim1_2"."snapshot_timestamp", "pit_dim1_2"."dim_lvl2_object_h_key", "pit_dim9_2"."dim_lvl2_object_h_key", "not_exists_in_fact_pit_month"."level_snapshot_timestamp" ) , "aggr_bihourly" AS ( SELECT "pit_dim1_3"."snapshot_timestamp" AS "fact_snapshot_timestamp" , "pit_dim1_3"."dim_lvl2_object_h_key" AS "dim_addresses_hkey" , "pit_dim9_3"."dim_lvl2_object_h_key" AS "dim_parts_hkey" , "not_exists_in_fact_pit_bihourly"."level_snapshot_timestamp" AS "level_snapshot_timestamp" , SUM("sat_fact7_2"."unit_price") AS "sum_unit_price" , SUM("sat_fact7_2"."quantity") AS "sum_quantity" , SUM("sat_fact7_2"."amount") AS "sum_amount" , AVG("sat_fact7_2"."unit_price") AS "avg_unit_price" , AVG("sat_fact7_2"."quantity") AS "avg_quantity" , AVG("sat_fact7_2"."amount") AS "avg_amount" FROM "dv_vsstd_opt_bv"."bridge_address_part_invoice" "bridge_src" INNER JOIN "not_exists_in_fact_pit_bihourly" "not_exists_in_fact_pit_bihourly" ON 1 = 1 INNER JOIN "pit_dim1_3" "pit_dim1_3" ON "bridge_src"."addresses_hkey" = "pit_dim1_3"."addresses_hkey" AND "not_exists_in_fact_pit_bihourly"."level_snapshot_timestamp" = "pit_dim1_3"."bihourly_snapshot_timestamp" INNER JOIN "pit_dim9_3" "pit_dim9_3" ON "bridge_src"."parts_hkey" = "pit_dim9_3"."parts_hkey" AND "not_exists_in_fact_pit_bihourly"."level_snapshot_timestamp" = "pit_dim9_3"."bihourly_snapshot_timestamp" INNER JOIN "sat_fact7_2" "sat_fact7_2" ON "bridge_src"."invoice_lines_hkey" = "sat_fact7_2"."invoice_lines_hkey" AND "not_exists_in_fact_pit_bihourly"."level_snapshot_timestamp" = "sat_fact7_2"."hourly_trans_timestamp" GROUP BY "pit_dim1_3"."snapshot_timestamp", "pit_dim1_3"."dim_lvl2_object_h_key", "pit_dim9_3"."dim_lvl2_object_h_key", "not_exists_in_fact_pit_bihourly"."level_snapshot_timestamp" ) SELECT "aggr_day"."fact_snapshot_timestamp" AS "fact_snapshot_timestamp" , "aggr_day"."dim_addresses_hkey" AS "dim_addresses_hkey" , "aggr_day"."dim_parts_hkey" AS "dim_parts_hkey" , "aggr_day"."level_snapshot_timestamp" AS "level_snapshot_timestamp" , "aggr_day"."sum_unit_price" AS "sum_unit_price" , "aggr_day"."sum_quantity" AS "sum_quantity" , "aggr_day"."sum_amount" AS "sum_amount" , "aggr_day"."avg_unit_price" AS "avg_unit_price" , "aggr_day"."avg_quantity" AS "avg_quantity" , "aggr_day"."avg_amount" AS "avg_amount" FROM "aggr_day" "aggr_day" UNION ALL SELECT "aggr_month"."fact_snapshot_timestamp" AS "fact_snapshot_timestamp" , "aggr_month"."dim_addresses_hkey" AS "dim_addresses_hkey" , "aggr_month"."dim_parts_hkey" AS "dim_parts_hkey" , "aggr_month"."level_snapshot_timestamp" AS "level_snapshot_timestamp" , "aggr_month"."sum_unit_price" AS "sum_unit_price" , "aggr_month"."sum_quantity" AS "sum_quantity" , "aggr_month"."sum_amount" AS "sum_amount" , "aggr_month"."avg_unit_price" AS "avg_unit_price" , "aggr_month"."avg_quantity" AS "avg_quantity" , "aggr_month"."avg_amount" AS "avg_amount" FROM "aggr_month" "aggr_month" UNION ALL SELECT "aggr_bihourly"."fact_snapshot_timestamp" AS "fact_snapshot_timestamp" , "aggr_bihourly"."dim_addresses_hkey" AS "dim_addresses_hkey" , "aggr_bihourly"."dim_parts_hkey" AS "dim_parts_hkey" , "aggr_bihourly"."level_snapshot_timestamp" AS "level_snapshot_timestamp" , "aggr_bihourly"."sum_unit_price" AS "sum_unit_price" , "aggr_bihourly"."sum_quantity" AS "sum_quantity" , "aggr_bihourly"."sum_amount" AS "sum_amount" , "aggr_bihourly"."avg_unit_price" AS "avg_unit_price" , "aggr_bihourly"."avg_quantity" AS "avg_quantity" , "aggr_bihourly"."avg_amount" AS "avg_amount" FROM "aggr_bihourly" "aggr_bihourly" ; ``` 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 (if it wasn’t already done before), click on the following: ![image](media://3160e378-8cd5-486c-8ade-76652ed8aa11) and then click ![image](media://73436ca4-661e-43e1-badc-55be100a36e7) and add the schema: ![image](media://abad8e53-4fe3-47af-b699-e6e3a6e13f21) The order will be the Business Vault + Order added here. This ensures that generating FMC code will load schemas in the correct order. When 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. **DIM_LVL1 - DIM_LVL2 - DIM_LVL3** is added for that ![image](media://d81d66c0-e310-4e15-837a-fb1d7c1dc495) and assigned to the different satellites ![image](media://56199cbd-0c13-4d47-b622-60865fc5bd6c) ![image](media://1aeca46b-0015-4540-90ae-214ef7c17461) ![image](media://0f7f509f-c119-4eca-92b5-029c5f44aa38) 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. Next to those Signature Objects, **PIT_DIM_FACT** is added, which tags all the Pits that will be used for building links with the dimensions. ![image](media://2d586564-2d26-475f-98dd-ac4dc524a6a4) Also **SAT_FACT** is a Signature Object tagged to tag the satellite where the data should come from for the measures in the FACT. ![image](media://b765942d-25f2-49ff-a7af-2182c998d7fe) **Signature attributes** To define the Key for each level, signature attributes are added to tag those attributes. For the FACT, only the KEY Signature attribute is needed as we want to calculate the hash key the same way we did for the dimension. ![image](media://cd427274-9884-4d7e-b3b8-a3c4b5d0c3aa) And then assigned to the Data Vault objects ![image](media://d35ce467-f545-43d8-811d-6042f37a9439) ![image](media://6d74a8dc-4414-4df2-b9c0-4e6ef5ad0685) Also, SRC_MEASURE is added to tag the SRC measures that will be used in the FACT. ![image](media://378e197d-54f9-44e6-b823-cc8b0040f65b) ![image](media://7fa596dd-83f4-40df-8acd-79bb71d85b57) ## **1.1: Define a new template:** Click the +Add button to add a new template: ![image](media://b5e261f5-31d8-4ec9-a099-622198044476) - **Name/Description:** fact_template / Fact Template - **Prefix/suffix:** fact / … - **Object type**: TABLE - **Signature object:** FACT - **Signature Schema: **information_mart - **Load type**: INCR - **Base type**: Bridge table - **Upload ETL:** This will be done afterwards. - **Upload DDL:** This will be done afterwards. Click the **Create** button when ready. ## **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 Bridge as the base type in this example, this screen will show all Bridges. A set of unlinked Bridge objects can be included in the template, as shown in the screenshot below (Linked Objects table). ![image](media://e369e310-944a-4c0c-a8dd-b95705d8770e) Select the Bridge(s) by checking the checkbox in this template and click the > arrow to link them to the template. ![image](media://e2e76440-bd01-400d-8f14-5b3365db2459) ## **4.3: Configure the target attribute definition** We need to configure the target definition for this fact, as shown below. ![image](media://c071ec67-42cc-43c2-96b3-ebf16224884d) ### 4.3.1 Existing attributes None ![image](media://2a702d97-6046-470b-b1e4-210e217c62dc) ### 4.3.2 New attributes In this case, we add two UNIQUE Signature attributes, a fact_snapshot_timestamp and a level_snapshot_timestamp and three non-unique Signature attributes, a dimension hash key based upon the hub hash key and two measure Signature attributes, one for SUM and one for AVG. ![image](media://4d04c8a8-6e09-4bbc-87d8-b79c5edc4175) ## 4.4: Build the Fact 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_fact_address_part_invoice_fact_template"() RETURNS void LANGUAGE 'plpgsql' AS $function$ /* __ __ _ _ _ __ ___ __ __ \ \ / /_ _ _ _| | |_ ___ ____ ___ ___ __| | \ \/ _ \/ / /_/ \ \ / / _` | | | | | __/ __| _ \ / _ \/ _ \/ _` | \/ / \ \/ /\ \ V / (_| | |_| | | |_\__ \ |_) | __/ __/ (_| | / / \/\ \/ / \_/ \__,_|\__,_|_|\__|___/ .__/ \___|\___|\__,_| /_/ \/_/\__/ |_| Vaultspeed version: 5.2.1.12, generation date: 2023/01/10 11:21:13 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 -- fact_table 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 FACT_TABLE ``` ### 4.4.1. Not exist in fact CTE’s (One for each snapshot level - DAY - MONTH - BIHOURLY) ```sql WITH "not_exists_in_fact_pit_day" AS ( SELECT "pit_ss1_1"."snapshot_timestamp" AS "fact_snapshot_timestamp" , date_trunc('day',"pit_ss1_1"."snapshot_timestamp") AS "level_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_daily_pit_addresses" "pit_ss1_1" LEFT OUTER JOIN "information_mart"."fact_address_part_invoice" "fact_src" ON "pit_ss1_1"."snapshot_timestamp" > COALESCE("fact_src"."fact_snapshot_timestamp", TO_TIMESTAMP('01/01/1900 00:00:01', 'DD/MM/YYYY HH24:MI:SS'::varchar)) ) , "not_exists_in_fact_pit_month" AS ( SELECT DISTINCT "pit_ss1_2"."snapshot_timestamp" AS "fact_snapshot_timestamp" , date_trunc('month',"pit_ss1_2"."snapshot_timestamp") AS "level_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_monthly_pit_addresses" "pit_ss1_2" LEFT OUTER JOIN "information_mart"."fact_address_part_invoice" "fact_src" ON "pit_ss1_2"."snapshot_timestamp" > COALESCE("fact_src"."fact_snapshot_timestamp", TO_TIMESTAMP('01/01/1900 00:00:01', 'DD/MM/YYYY HH24:MI:SS'::varchar)) ) , "not_exists_in_fact_pit_bihourly" AS ( SELECT DISTINCT "pit_ss1_3"."snapshot_timestamp" AS "fact_snapshot_timestamp" , date_trunc('hourly',"pit_ss1_3"."snapshot_timestamp") AS "level_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_ss1_3" LEFT OUTER JOIN "information_mart"."fact_address_part_invoice" "fact_src" ON "pit_ss1_3"."snapshot_timestamp" > COALESCE("fact_src"."fact_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_FACT_PIT_DAY_GROUP INL_V_GRP comp_subgroup_start $ PIT_SS_SUBGROUP componentSubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] componentsubgrouprepeatedbycomponent PIT_DIM_FACT consists of target inline_view NOT_EXISTS_IN_FACT_PIT_DAY componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP expressedBy PIT_SS$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_SS$$.SNAPSHOT_TIMESTAMP Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy date_trunc(#day#,PIT_SS$$.SNAPSHOT_TIMESTAMP) expressionRepeatedByColumn PIT_SS$$.SNAPSHOT_TIMESTAMP consists of source table PIT_SS$$ componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent PIT_DIM_FACT$$ consists of left_outer join JOIN_FACT_SRC componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy PIT_SS$$.SNAPSHOT_TIMESTAMP > COALESCE(FACT_SRC.FACT_SNAPSHOT_TIMESTAMP, GTIMECAST[#01/01/1900SPACE00:00:01#]) expressionDefinedByAttribute FACT_SRC.FACT_SNAPSHOT_TIMESTAMP consists of joined table FACT_SRC componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT comp_subgroup_end comp_group_end comp_group_start NOT_EXISTS_IN_FACT_PIT_MONTH_GROUP INL_V_GRP comp_subgroup_start $ PIT_SS_SUBGROUP componentSubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] componentsubgrouprepeatedbycomponent PIT_DIM_FACT consists of target distinct NOT_EXISTS_IN_FACT_PIT_MONTH componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP expressedBy PIT_SS$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_SS$$.SNAPSHOT_TIMESTAMP Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy date_trunc(#month#,PIT_SS$$.SNAPSHOT_TIMESTAMP) expressionRepeatedByColumn PIT_SS$$.SNAPSHOT_TIMESTAMP consists of source table PIT_SS$$ componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent PIT_DIM_FACT$$ consists of left_outer join JOIN_FACT_SRC componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy PIT_SS$$.SNAPSHOT_TIMESTAMP > COALESCE(FACT_SRC.FACT_SNAPSHOT_TIMESTAMP, GTIMECAST[#01/01/1900SPACE00:00:01#]) expressionDefinedByAttribute FACT_SRC.FACT_SNAPSHOT_TIMESTAMP consists of joined table FACT_SRC componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT comp_subgroup_end comp_group_end comp_group_start NOT_EXISTS_IN_FACT_PIT_BIHOURLY_GROUP INL_V_GRP comp_subgroup_start $ PIT_SS_SUBGROUP componentSubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] componentsubgrouprepeatedbycomponent PIT_DIM_FACT consists of target distinct NOT_EXISTS_IN_FACT_PIT_BIHOURLY componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP expressedBy PIT_SS$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_SS$$.SNAPSHOT_TIMESTAMP Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy date_trunc(#hourly#,PIT_SS$$.SNAPSHOT_TIMESTAMP) expressionRepeatedByColumn PIT_SS$$.SNAPSHOT_TIMESTAMP consists of source table PIT_SS$$ componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent PIT_DIM_FACT$$ consists of left_outer join JOIN_FACT_SRC componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy PIT_SS$$.SNAPSHOT_TIMESTAMP > COALESCE(FACT_SRC.FACT_SNAPSHOT_TIMESTAMP, GTIMECAST[#01/01/1900SPACE00:00:01#]) expressionDefinedByAttribute FACT_SRC.FACT_SNAPSHOT_TIMESTAMP consists of joined table FACT_SRC componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT comp_subgroup_end 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 Fact. In this case, we only need to check one PIT, so [(LOOP: $ = 1)] is used to filter the subgroups as that will loop through every pit for a particular snapshot level. The snapshot level itself is filter by using the parameter PIT_TEMPLATE_NAME, which is the name we gave to our pit in the pit definition in VaultSpeed. componentSubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] We select in this case the new pit_snapshot_timestamp which will be used for the fact and the level_snapshot_timestamp which is a truncation on the specific level. ### 4.4.2. CTE collect all pits ``` , "pit_dim1_3" AS ( SELECT "pit_src1_3"."addresses_hkey" AS "addresses_hkey" , UPPER(ENCODE(DIGEST( "sat_src1_3_3"."postal_code" || "pit_src1_3"."addresses_hkey" ,'MD5'),'HEX')) AS "dim_lvl2_object_h_key" , "pit_src1_3"."snapshot_timestamp" AS "snapshot_timestamp" , date_trunc('hourly',"pit_src1_3"."snapshot_timestamp") AS "bihourly_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_addresses" "pit_src1_3" INNER JOIN "not_exists_in_fact_pit_bihourly" "not_exists_in_fact_pit_bihourly" ON "pit_src1_3"."snapshot_timestamp" = "not_exists_in_fact_pit_bihourly"."fact_snapshot_timestamp" INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src1_3_3" ON "pit_src1_3"."sat_msvs_addresses_city_hkey" = "sat_src1_3_3"."addresses_hkey" AND "pit_src1_3"."sat_msvs_addresses_city_trans_timestamp" = "sat_src1_3_3"."trans_timestamp" ) , "pit_dim1_2" AS ( SELECT "pit_src1_2"."addresses_hkey" AS "addresses_hkey" , UPPER(ENCODE(DIGEST( "sat_src1_2_3"."postal_code" || "pit_src1_2"."addresses_hkey" ,'MD5'),'HEX')) AS "dim_lvl2_object_h_key" , "pit_src1_2"."snapshot_timestamp" AS "snapshot_timestamp" , date_trunc('month',"pit_src1_2"."snapshot_timestamp") AS "month_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_monthly_pit_addresses" "pit_src1_2" INNER JOIN "not_exists_in_fact_pit_month" "not_exists_in_fact_pit_month" ON "pit_src1_2"."snapshot_timestamp" = "not_exists_in_fact_pit_month"."fact_snapshot_timestamp" INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src1_2_3" ON "pit_src1_2"."sat_msvs_addresses_city_hkey" = "sat_src1_2_3"."addresses_hkey" AND "pit_src1_2"."sat_msvs_addresses_city_trans_timestamp" = "sat_src1_2_3"."trans_timestamp" ) , "pit_dim1_1" AS ( SELECT "pit_src1_1"."addresses_hkey" AS "addresses_hkey" , UPPER(ENCODE(DIGEST( "sat_src1_1_3"."postal_code" || "pit_src1_1"."addresses_hkey" ,'MD5'),'HEX')) AS "dim_lvl2_object_h_key" , "pit_src1_1"."snapshot_timestamp" AS "snapshot_timestamp" , date_trunc('day',"pit_src1_1"."snapshot_timestamp") AS "day_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_daily_pit_addresses" "pit_src1_1" INNER JOIN "not_exists_in_fact_pit_day" "not_exists_in_fact_pit_day" ON "pit_src1_1"."snapshot_timestamp" = "not_exists_in_fact_pit_day"."fact_snapshot_timestamp" INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_addresses_city" "sat_src1_1_3" ON "pit_src1_1"."sat_msvs_addresses_city_hkey" = "sat_src1_1_3"."addresses_hkey" AND "pit_src1_1"."sat_msvs_addresses_city_trans_timestamp" = "sat_src1_1_3"."trans_timestamp" ) , "pit_dim9_1" AS ( SELECT "pit_src9_1"."parts_hkey" AS "parts_hkey" , UPPER(ENCODE(DIGEST( "sat_src9_1_2"."part_category_code" || "pit_src9_1"."parts_hkey" ,'MD5'),'HEX')) AS "dim_lvl2_object_h_key" , "pit_src9_1"."snapshot_timestamp" AS "snapshot_timestamp" , date_trunc('day',"pit_src9_1"."snapshot_timestamp") AS "day_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_daily_pit_parts" "pit_src9_1" INNER JOIN "not_exists_in_fact_pit_day" "not_exists_in_fact_pit_day" ON "pit_src9_1"."snapshot_timestamp" = "not_exists_in_fact_pit_day"."fact_snapshot_timestamp" INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_parts_category" "sat_src9_1_2" ON "pit_src9_1"."sat_msvs_parts_category_hkey" = "sat_src9_1_2"."parts_hkey" AND "pit_src9_1"."sat_msvs_parts_category_trans_timestamp" = "sat_src9_1_2"."trans_timestamp" ) , "pit_dim9_3" AS ( SELECT "pit_src9_3"."parts_hkey" AS "parts_hkey" , UPPER(ENCODE(DIGEST( "sat_src9_3_2"."part_category_code" || "pit_src9_3"."parts_hkey" ,'MD5'),'HEX')) AS "dim_lvl2_object_h_key" , "pit_src9_3"."snapshot_timestamp" AS "snapshot_timestamp" , date_trunc('hourly',"pit_src9_3"."snapshot_timestamp") AS "bihourly_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_bi_hourly_pit_parts" "pit_src9_3" INNER JOIN "not_exists_in_fact_pit_bihourly" "not_exists_in_fact_pit_bihourly" ON "pit_src9_3"."snapshot_timestamp" = "not_exists_in_fact_pit_bihourly"."fact_snapshot_timestamp" INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_parts_category" "sat_src9_3_2" ON "pit_src9_3"."sat_msvs_parts_category_hkey" = "sat_src9_3_2"."parts_hkey" AND "pit_src9_3"."sat_msvs_parts_category_trans_timestamp" = "sat_src9_3_2"."trans_timestamp" ) , "pit_dim9_2" AS ( SELECT "pit_src9_2"."parts_hkey" AS "parts_hkey" , UPPER(ENCODE(DIGEST( "sat_src9_2_2"."part_category_code" || "pit_src9_2"."parts_hkey" ,'MD5'),'HEX')) AS "dim_lvl2_object_h_key" , "pit_src9_2"."snapshot_timestamp" AS "snapshot_timestamp" , date_trunc('month',"pit_src9_2"."snapshot_timestamp") AS "month_snapshot_timestamp" FROM "dv_vsstd_opt_bv"."pit_monthly_pit_parts" "pit_src9_2" INNER JOIN "not_exists_in_fact_pit_month" "not_exists_in_fact_pit_month" ON "pit_src9_2"."snapshot_timestamp" = "not_exists_in_fact_pit_month"."fact_snapshot_timestamp" INNER JOIN "dv_vsstd_opt_fl"."sat_msvs_parts_category" "sat_src9_2_2" ON "pit_src9_2"."sat_msvs_parts_category_hkey" = "sat_src9_2_2"."parts_hkey" AND "pit_src9_2"."sat_msvs_parts_category_trans_timestamp" = "sat_src9_2_2"."trans_timestamp" ) ``` This can be templated as ``` comp_group_start $ PIT_DIM_GROUP INL_V_GRP componentGroupRepeatedByComponent PIT_DIM_FACT consists of target inline_view PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ Attribute OBJECT_L_H_KEY expressedBy PIT_SRC$$.OBJECT_L_H_KEY expressionRepeatedByColumn PIT_SRC$$.OBJECT_L_H_KEY ArtifactPart DIM_LVL2_OBJECT_H_KEY GROUP_1 expressedBy HASHFUNC[ ArtifactPart $ DIM_LVL2_OBJECT_H_KEY attributerepeatedbycomponent PITDIM_LVL2$$ GROUP_2 expressedBy SAT_SRC$$$.SRC_LVL2_KEY expressionRepeatedByColumn SAT_SRC$$$.SRC_LVL2_KEY COMMA ArtifactPart DIM_LVL2_OBJECT_H_KEY GROUP_3 expressedBy || PIT_SRC$$.OBJECT_L_H_KEY expressionRepeatedByColumn PIT_SRC$$.OBJECT_L_H_KEY GROUP_4 expressedBy HASHFUNC] Attribute SNAPSHOT_TIMESTAMP expressedBy PIT_SRC$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_SRC$$.SNAPSHOT_TIMESTAMP Artifact DAY_SNAPSHOT_TIMESTAMP AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] expressedBy date_trunc(#day#,PIT_SRC$$.SNAPSHOT_TIMESTAMP) expressionRepeatedByColumn PIT_SRC$$.SNAPSHOT_TIMESTAMP Artifact MONTH_SNAPSHOT_TIMESTAMP AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] expressedBy date_trunc(#month#,PIT_SRC$$.SNAPSHOT_TIMESTAMP) expressionRepeatedByColumn PIT_SRC$$.SNAPSHOT_TIMESTAMP Artifact BIHOURLY_SNAPSHOT_TIMESTAMP AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] expressedBy date_trunc(#hourly#,PIT_SRC$$.SNAPSHOT_TIMESTAMP) expressionRepeatedByColumn PIT_SRC$$.SNAPSHOT_TIMESTAMP Attribute OBJECT_L_H_KEY expressedBy PIT_SRC$$.OBJECT_L_H_KEY expressionRepeatedByColumn PIT_SRC$$.OBJECT_L_H_KEY consists of source table PIT_SRC$$ componentrepeatedbycomponent PIT_DIM_FACT$$ consists of inner join JOIN_NOT_EXISTS_IN_FACT_PIT_DAY componentConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy PIT_SRC$$.SNAPSHOT_TIMESTAMP = NOT_EXISTS_IN_FACT_PIT_DAY.FACT_SNAPSHOT_TIMESTAMP expressionDefinedByAttribute NOT_EXISTS_IN_FACT_PIT_DAY.FACT_SNAPSHOT_TIMESTAMP consists of joined inline_view NOT_EXISTS_IN_FACT_PIT_DAY componentConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] componentrepeatedbycomponent FACT consists of inner join JOIN_NOT_EXISTS_IN_FACT_PIT_MONTH componentConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy PIT_SRC$$.SNAPSHOT_TIMESTAMP = NOT_EXISTS_IN_FACT_PIT_MONTH.FACT_SNAPSHOT_TIMESTAMP expressionDefinedByAttribute NOT_EXISTS_IN_FACT_PIT_MONTH.FACT_SNAPSHOT_TIMESTAMP consists of joined inline_view NOT_EXISTS_IN_FACT_PIT_MONTH componentConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] componentrepeatedbycomponent FACT consists of inner join JOIN_NOT_EXISTS_IN_FACT_PIT_BIHOURLY componentConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy PIT_SRC$$.SNAPSHOT_TIMESTAMP = NOT_EXISTS_IN_FACT_PIT_BIHOURLY.FACT_SNAPSHOT_TIMESTAMP expressionDefinedByAttribute NOT_EXISTS_IN_FACT_PIT_BIHOURLY.FACT_SNAPSHOT_TIMESTAMP consists of joined inline_view NOT_EXISTS_IN_FACT_PIT_BIHOURLY componentConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] componentrepeatedbycomponent FACT comp_subgroup_start $ SAT_SRC_SUBGROUP componentsubgrouprepeatedbycomponent PITDIM_LVL2$$ consists of inner join JOIN_SAT_SRC$$$ componentrepeatedbycomponent PITDIM_LVL2$$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy PIT_SRC$$.OBJECT_F_H_KEY = SAT_SRC$$$.OBJECT_H_KEY expressionRepeatedByColumn SAT_SRC$$$.OBJECT_H_KEY GROUP_2 expressedBy AND PIT_SRC$$.SAT_LOAD_TIMESTAMP = SAT_SRC$$$.TRANS_TIMESTAMP expressionRepeatedByColumn SAT_SRC$$$.TRANS_TIMESTAMP consists of joined table SAT_SRC$$$ componentrepeatedbycomponent PITDIM_LVL2$$$ comp_subgroup_end comp_group_end ``` This component_group is another CTE (INL_V_GRP), here we calculate the dimension hash key and build the correct snapshot_timestamp to be able to use it later on. The query is a join between all tagged pits (PIT_DIM_FACT), the not exists and the linked sats (of level 2) to be able to build the dimension hash key. ### 4.4.3. CTE collect measures from SAT ``` , "sat_fact7_2" AS ( SELECT "sat_src7_2"."invoice_lines_hkey" AS "invoice_lines_hkey" , date_trunc('day',"sat_src7_2"."trans_timestamp") AS "day_trans_timestamp" , date_trunc('month',"sat_src7_2"."trans_timestamp") AS "month_trans_timestamp" , date_trunc('hourly',"sat_src7_2"."trans_timestamp") AS "hourly_trans_timestamp" , "sat_src7_2"."unit_price" AS "unit_price" , "sat_src7_2"."quantity" AS "quantity" , "sat_src7_2"."amount" AS "amount" FROM "dv_vsstd_opt_fl"."sat_msvs_invoice_lines_other" "sat_src7_2" ) ``` This can be templated as ``` comp_group_start $ SAT_FACT_GROUP INL_V_GRP componentGroupRepeatedByComponent SAT_FACT consists of target inline_view SAT_FACT$$ componentrepeatedbycomponent SAT_FACT$$ Attribute OBJECT_H_KEY expressedBy SAT_SRC$$.OBJECT_H_KEY expressionRepeatedByColumn SAT_SRC$$.OBJECT_H_KEY Artifact DAY_TRANS_TIMESTAMP expressedBy date_trunc(#day#,SAT_SRC$$.TRANS_TIMESTAMP) expressionRepeatedByColumn SAT_SRC$$.TRANS_TIMESTAMP Artifact MONTH_TRANS_TIMESTAMP expressedBy date_trunc(#month#,SAT_SRC$$.TRANS_TIMESTAMP) expressionRepeatedByColumn SAT_SRC$$.TRANS_TIMESTAMP Artifact HOURLY_TRANS_TIMESTAMP expressedBy date_trunc(#hourly#,SAT_SRC$$.TRANS_TIMESTAMP) expressionRepeatedByColumn SAT_SRC$$.TRANS_TIMESTAMP Attribute SRC_MEASURE expressedBy SAT_SRC$$.SRC_MEASURE expressionRepeatedByColumn SAT_SRC$$.SRC_MEASURE consists of source table SAT_SRC$$ componentrepeatedbycomponent SAT$$ comp_group_end ``` This component_group is another CTE (INL_V_GRP), here, we collect the measures for the fact. The query is a simple selection of the measures from the satellite. ### 4.4.4. CTE to aggregate measures for the different snapshot levels ``` , "aggr_day" AS ( SELECT "pit_dim1_1"."snapshot_timestamp" AS "fact_snapshot_timestamp" , "pit_dim1_1"."dim_lvl2_object_h_key" AS "dim_addresses_hkey" , "pit_dim9_1"."dim_lvl2_object_h_key" AS "dim_parts_hkey" , "not_exists_in_fact_pit_day"."level_snapshot_timestamp" AS "level_snapshot_timestamp" , SUM("sat_fact7_2"."unit_price") AS "sum_unit_price" , SUM("sat_fact7_2"."quantity") AS "sum_quantity" , SUM("sat_fact7_2"."amount") AS "sum_amount" , AVG("sat_fact7_2"."unit_price") AS "avg_unit_price" , AVG("sat_fact7_2"."quantity") AS "avg_quantity" , AVG("sat_fact7_2"."amount") AS "avg_amount" FROM "dv_vsstd_opt_bv"."bridge_address_part_invoice" "bridge_src" INNER JOIN "not_exists_in_fact_pit_day" "not_exists_in_fact_pit_day" ON 1 = 1 INNER JOIN "pit_dim1_1" "pit_dim1_1" ON "bridge_src"."addresses_hkey" = "pit_dim1_1"."addresses_hkey" AND "not_exists_in_fact_pit_day"."level_snapshot_timestamp" = "pit_dim1_1"."day_snapshot_timestamp" INNER JOIN "pit_dim9_1" "pit_dim9_1" ON "bridge_src"."parts_hkey" = "pit_dim9_1"."parts_hkey" AND "not_exists_in_fact_pit_day"."level_snapshot_timestamp" = "pit_dim9_1"."day_snapshot_timestamp" INNER JOIN "sat_fact7_2" "sat_fact7_2" ON "bridge_src"."invoice_lines_hkey" = "sat_fact7_2"."invoice_lines_hkey" AND "not_exists_in_fact_pit_day"."level_snapshot_timestamp" = "sat_fact7_2"."day_trans_timestamp" GROUP BY "pit_dim1_1"."snapshot_timestamp", "pit_dim1_1"."dim_lvl2_object_h_key", "pit_dim9_1"."dim_lvl2_object_h_key", "not_exists_in_fact_pit_day"."level_snapshot_timestamp" ) , "aggr_month" AS ( SELECT "pit_dim1_2"."snapshot_timestamp" AS "fact_snapshot_timestamp" , "pit_dim1_2"."dim_lvl2_object_h_key" AS "dim_addresses_hkey" , "pit_dim9_2"."dim_lvl2_object_h_key" AS "dim_parts_hkey" , "not_exists_in_fact_pit_month"."level_snapshot_timestamp" AS "level_snapshot_timestamp" , SUM("sat_fact7_2"."unit_price") AS "sum_unit_price" , SUM("sat_fact7_2"."quantity") AS "sum_quantity" , SUM("sat_fact7_2"."amount") AS "sum_amount" , AVG("sat_fact7_2"."unit_price") AS "avg_unit_price" , AVG("sat_fact7_2"."quantity") AS "avg_quantity" , AVG("sat_fact7_2"."amount") AS "avg_amount" FROM "dv_vsstd_opt_bv"."bridge_address_part_invoice" "bridge_src" INNER JOIN "not_exists_in_fact_pit_month" "not_exists_in_fact_pit_month" ON 1 = 1 INNER JOIN "pit_dim1_2" "pit_dim1_2" ON "bridge_src"."addresses_hkey" = "pit_dim1_2"."addresses_hkey" AND "not_exists_in_fact_pit_month"."level_snapshot_timestamp" = "pit_dim1_2"."month_snapshot_timestamp" INNER JOIN "pit_dim9_2" "pit_dim9_2" ON "bridge_src"."parts_hkey" = "pit_dim9_2"."parts_hkey" AND "not_exists_in_fact_pit_month"."level_snapshot_timestamp" = "pit_dim9_2"."month_snapshot_timestamp" INNER JOIN "sat_fact7_2" "sat_fact7_2" ON "bridge_src"."invoice_lines_hkey" = "sat_fact7_2"."invoice_lines_hkey" AND "not_exists_in_fact_pit_month"."level_snapshot_timestamp" = "sat_fact7_2"."month_trans_timestamp" GROUP BY "pit_dim1_2"."snapshot_timestamp", "pit_dim1_2"."dim_lvl2_object_h_key", "pit_dim9_2"."dim_lvl2_object_h_key", "not_exists_in_fact_pit_month"."level_snapshot_timestamp" ) , "aggr_bihourly" AS ( SELECT "pit_dim1_3"."snapshot_timestamp" AS "fact_snapshot_timestamp" , "pit_dim1_3"."dim_lvl2_object_h_key" AS "dim_addresses_hkey" , "pit_dim9_3"."dim_lvl2_object_h_key" AS "dim_parts_hkey" , "not_exists_in_fact_pit_bihourly"."level_snapshot_timestamp" AS "level_snapshot_timestamp" , SUM("sat_fact7_2"."unit_price") AS "sum_unit_price" , SUM("sat_fact7_2"."quantity") AS "sum_quantity" , SUM("sat_fact7_2"."amount") AS "sum_amount" , AVG("sat_fact7_2"."unit_price") AS "avg_unit_price" , AVG("sat_fact7_2"."quantity") AS "avg_quantity" , AVG("sat_fact7_2"."amount") AS "avg_amount" FROM "dv_vsstd_opt_bv"."bridge_address_part_invoice" "bridge_src" INNER JOIN "not_exists_in_fact_pit_bihourly" "not_exists_in_fact_pit_bihourly" ON 1 = 1 INNER JOIN "pit_dim1_3" "pit_dim1_3" ON "bridge_src"."addresses_hkey" = "pit_dim1_3"."addresses_hkey" AND "not_exists_in_fact_pit_bihourly"."level_snapshot_timestamp" = "pit_dim1_3"."bihourly_snapshot_timestamp" INNER JOIN "pit_dim9_3" "pit_dim9_3" ON "bridge_src"."parts_hkey" = "pit_dim9_3"."parts_hkey" AND "not_exists_in_fact_pit_bihourly"."level_snapshot_timestamp" = "pit_dim9_3"."bihourly_snapshot_timestamp" INNER JOIN "sat_fact7_2" "sat_fact7_2" ON "bridge_src"."invoice_lines_hkey" = "sat_fact7_2"."invoice_lines_hkey" AND "not_exists_in_fact_pit_bihourly"."level_snapshot_timestamp" = "sat_fact7_2"."hourly_trans_timestamp" GROUP BY "pit_dim1_3"."snapshot_timestamp", "pit_dim1_3"."dim_lvl2_object_h_key", "pit_dim9_3"."dim_lvl2_object_h_key", "not_exists_in_fact_pit_bihourly"."level_snapshot_timestamp" ) ``` This can be templated as ``` comp_group_start AGGR_DAY_GROUP INL_V_GRP consists of aggregated inline_view AGGR_DAY componentrepeatedbycomponent FACT Attribute $ FACT_SNAPSHOT_TIMESTAMP AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] AttributeRepeatedByComponent PIT_DIM_FACT expressedBy PIT_DIM$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_DIM$$.SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] AttributeRepeatedByComponent PIT_DIM_FACT expressedBy PIT_DIM$$.DIM_LVL2_OBJECT_H_KEY expressionRepeatedByColumn PIT_DIM$$.OBJECT_L_H_KEY Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy NOT_EXISTS_IN_FACT_PIT_DAY.LEVEL_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn NOT_EXISTS_IN_FACT_PIT_DAY.LEVEL_SNAPSHOT_TIMESTAMP Aggregated Attribute $ SUM_SRC_MEASURE AttributeRepeatedByComponent SAT_FACT expressedBy SUM(SAT_FACT$$.SRC_MEASURE) expressionRepeatedByColumn SAT_FACT$$.SRC_MEASURE Aggregated Attribute $ AVG_SRC_MEASURE AttributeRepeatedByComponent SAT_FACT expressedBy AVG(SAT_FACT$$.SRC_MEASURE) expressionRepeatedByColumn SAT_FACT$$.SRC_MEASURE consists of source table BRIDGE_SRC componentrepeatedbycomponent BRIDGE consists of inner join JOIN_NOT_EXISTS_IN_FACT componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy 1 = 1 consists of joined inline_view NOT_EXISTS_IN_FACT_PIT_DAY componentrepeatedbycomponent FACT comp_subgroup_start $ PIT_DIM_SUBGROUP componentsubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] componentsubgrouprepeatedbycomponent PIT_DIM_FACT consists of inner join JOIN_PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy BRIDGE_SRC.OBJECT_H_KEY = PIT_DIM$$.OBJECT_L_H_KEY expressionRepeatedByColumn PIT_DIM$$.OBJECT_L_H_KEY GROUP_2 expressedBy AND NOT_EXISTS_IN_FACT_PIT_DAY.LEVEL_SNAPSHOT_TIMESTAMP = PIT_DIM$$.DAY_SNAPSHOT_TIMESTAMP consists of joined inline_view PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ comp_subgroup_end comp_subgroup_start $ SAT_FACT_SUBGROUP componentsubgrouprepeatedbycomponent SAT_FACT consists of inner join JOIN_SAT_FACT$$ componentrepeatedbycomponent SAT_FACT$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy BRIDGE_SRC.OBJECT_H_KEY = SAT_FACT$$.OBJECT_H_KEY expressionRepeatedByColumn SAT_FACT$$.OBJECT_H_KEY GROUP_2 expressedBy AND NOT_EXISTS_IN_FACT_PIT_DAY.LEVEL_SNAPSHOT_TIMESTAMP = SAT_FACT$$.DAY_TRANS_TIMESTAMP expressionRepeatedByColumn NOT_EXISTS_IN_FACT_PIT_DAY.LEVEL_SNAPSHOT_TIMESTAMP consists of joined inline_view SAT_FACT$$ componentrepeatedbycomponent SAT$$ comp_subgroup_end comp_group_end comp_group_start AGGR_MONTH_GROUP INL_V_GRP consists of aggregated inline_view AGGR_MONTH componentrepeatedbycomponent FACT Attribute $ FACT_SNAPSHOT_TIMESTAMP AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] AttributeRepeatedByComponent PIT_DIM_FACT expressedBy PIT_DIM$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_DIM$$.SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] AttributeRepeatedByComponent PIT_DIM_FACT expressedBy PIT_DIM$$.DIM_LVL2_OBJECT_H_KEY expressionRepeatedByColumn PIT_DIM$$.OBJECT_L_H_KEY Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy NOT_EXISTS_IN_FACT_PIT_MONTH.LEVEL_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn NOT_EXISTS_IN_FACT_PIT_MONTH.LEVEL_SNAPSHOT_TIMESTAMP Aggregated Attribute $ SUM_SRC_MEASURE AttributeRepeatedByComponent SAT_FACT expressedBy SUM(SAT_FACT$$.SRC_MEASURE) expressionRepeatedByColumn SAT_FACT$$.SRC_MEASURE Aggregated Attribute $ AVG_SRC_MEASURE AttributeRepeatedByComponent SAT_FACT expressedBy AVG(SAT_FACT$$.SRC_MEASURE) expressionRepeatedByColumn SAT_FACT$$.SRC_MEASURE consists of source table BRIDGE_SRC componentrepeatedbycomponent BRIDGE consists of inner join JOIN_NOT_EXISTS_IN_FACT componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy 1 = 1 consists of joined inline_view NOT_EXISTS_IN_FACT_PIT_MONTH componentrepeatedbycomponent FACT comp_subgroup_start $ PIT_DIM_SUBGROUP componentsubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] componentsubgrouprepeatedbycomponent PIT_DIM_FACT consists of inner join JOIN_PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy BRIDGE_SRC.OBJECT_H_KEY = PIT_DIM$$.OBJECT_L_H_KEY expressionRepeatedByColumn PIT_DIM$$.OBJECT_L_H_KEY GROUP_2 expressedBy AND NOT_EXISTS_IN_FACT_PIT_MONTH.LEVEL_SNAPSHOT_TIMESTAMP = PIT_DIM$$.MONTH_SNAPSHOT_TIMESTAMP consists of joined inline_view PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ comp_subgroup_end comp_subgroup_start $ SAT_FACT_SUBGROUP componentsubgrouprepeatedbycomponent SAT_FACT consists of inner join JOIN_SAT_FACT$$ componentrepeatedbycomponent SAT_FACT$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy BRIDGE_SRC.OBJECT_H_KEY = SAT_FACT$$.OBJECT_H_KEY expressionRepeatedByColumn SAT_FACT$$.OBJECT_H_KEY GROUP_2 expressedBy AND NOT_EXISTS_IN_FACT_PIT_MONTH.LEVEL_SNAPSHOT_TIMESTAMP = SAT_FACT$$.MONTH_TRANS_TIMESTAMP expressionRepeatedByColumn NOT_EXISTS_IN_FACT_PIT_MONTH.LEVEL_SNAPSHOT_TIMESTAMP consists of joined inline_view SAT_FACT$$ componentrepeatedbycomponent SAT$$ comp_subgroup_end comp_group_end comp_group_start AGGR_BIHOURLY_GROUP INL_V_GRP consists of aggregated inline_view AGGR_BIHOURLY componentrepeatedbycomponent FACT Attribute $ FACT_SNAPSHOT_TIMESTAMP AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] AttributeRepeatedByComponent PIT_DIM_FACT expressedBy PIT_DIM$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_DIM$$.SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] AttributeRepeatedByComponent PIT_DIM_FACT expressedBy PIT_DIM$$.DIM_LVL2_OBJECT_H_KEY expressionRepeatedByColumn PIT_DIM$$.OBJECT_L_H_KEY Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy NOT_EXISTS_IN_FACT_PIT_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn NOT_EXISTS_IN_FACT_PIT_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP Aggregated Attribute $ SUM_SRC_MEASURE AttributeRepeatedByComponent SAT_FACT expressedBy SUM(SAT_FACT$$.SRC_MEASURE) expressionRepeatedByColumn SAT_FACT$$.SRC_MEASURE Aggregated Attribute $ AVG_SRC_MEASURE AttributeRepeatedByComponent SAT_FACT expressedBy AVG(SAT_FACT$$.SRC_MEASURE) expressionRepeatedByColumn SAT_FACT$$.SRC_MEASURE consists of source table BRIDGE_SRC componentrepeatedbycomponent BRIDGE consists of inner join JOIN_NOT_EXISTS_IN_FACT componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy 1 = 1 consists of joined inline_view NOT_EXISTS_IN_FACT_PIT_BIHOURLY componentrepeatedbycomponent FACT comp_subgroup_start $ PIT_DIM_SUBGROUP componentsubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] componentsubgrouprepeatedbycomponent PIT_DIM_FACT consists of inner join JOIN_PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy BRIDGE_SRC.OBJECT_H_KEY = PIT_DIM$$.OBJECT_L_H_KEY expressionRepeatedByColumn PIT_DIM$$.OBJECT_L_H_KEY GROUP_2 expressedBy AND NOT_EXISTS_IN_FACT_PIT_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP = PIT_DIM$$.BIHOURLY_SNAPSHOT_TIMESTAMP consists of joined inline_view PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ comp_subgroup_end comp_subgroup_start $ SAT_FACT_SUBGROUP componentsubgrouprepeatedbycomponent SAT_FACT consists of inner join JOIN_SAT_FACT$$ componentrepeatedbycomponent SAT_FACT$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy BRIDGE_SRC.OBJECT_H_KEY = SAT_FACT$$.OBJECT_H_KEY expressionRepeatedByColumn SAT_FACT$$.OBJECT_H_KEY GROUP_2 expressedBy AND NOT_EXISTS_IN_FACT_PIT_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP = SAT_FACT$$.HOURLY_TRANS_TIMESTAMP expressionRepeatedByColumn NOT_EXISTS_IN_FACT_PIT_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP consists of joined inline_view SAT_FACT$$ componentrepeatedbycomponent SAT$$ comp_subgroup_end comp_group_end ``` These component_groups (DAY - MONTH - BIHOURLY) are another set of CTEs (INL_V_GRP), here we aggregate the measures for the fact. The query is a join between the bridge, the not exists, the pits cte’s and measure cte. An aggregate is done over the measures to match the dimension and snapshot levels. ### 4.4.5. Main clause ``` INSERT INTO "information_mart"."fact_address_part_invoice"( "fact_snapshot_timestamp" ,"dim_addresses_hkey" ,"dim_parts_hkey" ,"level_snapshot_timestamp" ,"sum_unit_price" ,"sum_quantity" ,"sum_amount" ,"avg_unit_price" ,"avg_quantity" ,"avg_amount" ) SELECT "aggr_day"."fact_snapshot_timestamp" AS "fact_snapshot_timestamp" , "aggr_day"."dim_addresses_hkey" AS "dim_addresses_hkey" , "aggr_day"."dim_parts_hkey" AS "dim_parts_hkey" , "aggr_day"."level_snapshot_timestamp" AS "level_snapshot_timestamp" , "aggr_day"."sum_unit_price" AS "sum_unit_price" , "aggr_day"."sum_quantity" AS "sum_quantity" , "aggr_day"."sum_amount" AS "sum_amount" , "aggr_day"."avg_unit_price" AS "avg_unit_price" , "aggr_day"."avg_quantity" AS "avg_quantity" , "aggr_day"."avg_amount" AS "avg_amount" FROM "aggr_day" "aggr_day" UNION ALL SELECT "aggr_month"."fact_snapshot_timestamp" AS "fact_snapshot_timestamp" , "aggr_month"."dim_addresses_hkey" AS "dim_addresses_hkey" , "aggr_month"."dim_parts_hkey" AS "dim_parts_hkey" , "aggr_month"."level_snapshot_timestamp" AS "level_snapshot_timestamp" , "aggr_month"."sum_unit_price" AS "sum_unit_price" , "aggr_month"."sum_quantity" AS "sum_quantity" , "aggr_month"."sum_amount" AS "sum_amount" , "aggr_month"."avg_unit_price" AS "avg_unit_price" , "aggr_month"."avg_quantity" AS "avg_quantity" , "aggr_month"."avg_amount" AS "avg_amount" FROM "aggr_month" "aggr_month" UNION ALL SELECT "aggr_bihourly"."fact_snapshot_timestamp" AS "fact_snapshot_timestamp" , "aggr_bihourly"."dim_addresses_hkey" AS "dim_addresses_hkey" , "aggr_bihourly"."dim_parts_hkey" AS "dim_parts_hkey" , "aggr_bihourly"."level_snapshot_timestamp" AS "level_snapshot_timestamp" , "aggr_bihourly"."sum_unit_price" AS "sum_unit_price" , "aggr_bihourly"."sum_quantity" AS "sum_quantity" , "aggr_bihourly"."sum_amount" AS "sum_amount" , "aggr_bihourly"."avg_unit_price" AS "avg_unit_price" , "aggr_bihourly"."avg_quantity" AS "avg_quantity" , "aggr_bihourly"."avg_amount" AS "avg_amount" FROM "aggr_bihourly" "aggr_bihourly" ; ``` This can be templated as ``` comp_group_start MAIN_GROUP INS_GRP componentGroupRepeatedByComponent FACT comp_subgroup_start DAY componentsubgrouprepeatedbycomponent FACT consists of UNION_ALL SET FACT_TGT componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP expressedBy AGGR_DAY.FACT_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn AGGR_DAY.FACT_SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY$ AttributeRepeatedByComponent PIT_DIM_FACT expressedBy AGGR_DAY.DIM_OBJECT_H_KEY$ expressionRepeatedByColumn AGGR_DAY.DIM_OBJECT_H_KEY$ Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy AGGR_DAY.LEVEL_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn AGGR_DAY.LEVEL_SNAPSHOT_TIMESTAMP Attribute SUM_SRC_MEASURE expressedBy AGGR_DAY.SUM_SRC_MEASURE expressionRepeatedByColumn AGGR_DAY.SUM_SRC_MEASURE Attribute AVG_SRC_MEASURE expressedBy AGGR_DAY.AVG_SRC_MEASURE expressionRepeatedByColumn AGGR_DAY.AVG_SRC_MEASURE consists of source inline_view AGGR_DAY componentrepeatedbycomponent FACT comp_subgroup_end comp_subgroup_start MONTH componentsubgrouprepeatedbycomponent FACT consists of UNION_ALL SET FACT_TGT componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP expressedBy AGGR_MONTH.FACT_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn AGGR_MONTH.FACT_SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY$ AttributeRepeatedByComponent PIT_DIM_FACT expressedBy AGGR_MONTH.DIM_OBJECT_H_KEY$ expressionRepeatedByColumn AGGR_MONTH.DIM_OBJECT_H_KEY$ Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy AGGR_MONTH.LEVEL_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn AGGR_MONTH.LEVEL_SNAPSHOT_TIMESTAMP Attribute SUM_SRC_MEASURE expressedBy AGGR_MONTH.SUM_SRC_MEASURE expressionRepeatedByColumn AGGR_MONTH.SUM_SRC_MEASURE Attribute AVG_SRC_MEASURE expressedBy AGGR_MONTH.AVG_SRC_MEASURE expressionRepeatedByColumn AGGR_MONTH.AVG_SRC_MEASURE consists of source inline_view AGGR_MONTH componentrepeatedbycomponent FACT comp_subgroup_end comp_subgroup_start BIHOURLY componentsubgrouprepeatedbycomponent FACT consists of UNION_ALL SET FACT_TGT componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP expressedBy AGGR_BIHOURLY.FACT_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn AGGR_BIHOURLY.FACT_SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY$ AttributeRepeatedByComponent PIT_DIM_FACT expressedBy AGGR_BIHOURLY.DIM_OBJECT_H_KEY$ expressionRepeatedByColumn AGGR_BIHOURLY.DIM_OBJECT_H_KEY$ Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy AGGR_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn AGGR_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP Attribute SUM_SRC_MEASURE expressedBy AGGR_BIHOURLY.SUM_SRC_MEASURE expressionRepeatedByColumn AGGR_BIHOURLY.SUM_SRC_MEASURE Attribute AVG_SRC_MEASURE expressedBy AGGR_BIHOURLY.AVG_SRC_MEASURE expressionRepeatedByColumn AGGR_BIHOURLY.AVG_SRC_MEASURE consists of source inline_view AGGR_BIHOURLY componentrepeatedbycomponent FACT comp_subgroup_end comp_group_end ``` The main (insert - INS_GRP) component group is a union all between the different snapshot levels. The full template ``` Template FACT_TABLE comp_group_start NOT_EXISTS_IN_FACT_PIT_DAY_GROUP INL_V_GRP comp_subgroup_start $ PIT_SS_SUBGROUP componentSubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] componentsubgrouprepeatedbycomponent PIT_DIM_FACT consists of target inline_view NOT_EXISTS_IN_FACT_PIT_DAY componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP expressedBy PIT_SS$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_SS$$.SNAPSHOT_TIMESTAMP Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy date_trunc(#day#,PIT_SS$$.SNAPSHOT_TIMESTAMP) expressionRepeatedByColumn PIT_SS$$.SNAPSHOT_TIMESTAMP consists of source table PIT_SS$$ componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent PIT_DIM_FACT$$ consists of left_outer join JOIN_FACT_SRC componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy PIT_SS$$.SNAPSHOT_TIMESTAMP > COALESCE(FACT_SRC.FACT_SNAPSHOT_TIMESTAMP, GTIMECAST[#01/01/1900SPACE00:00:01#]) expressionDefinedByAttribute FACT_SRC.FACT_SNAPSHOT_TIMESTAMP consists of joined table FACT_SRC componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT comp_subgroup_end comp_group_end comp_group_start NOT_EXISTS_IN_FACT_PIT_MONTH_GROUP INL_V_GRP comp_subgroup_start $ PIT_SS_SUBGROUP componentSubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] componentsubgrouprepeatedbycomponent PIT_DIM_FACT consists of target distinct NOT_EXISTS_IN_FACT_PIT_MONTH componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP expressedBy PIT_SS$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_SS$$.SNAPSHOT_TIMESTAMP Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy date_trunc(#month#,PIT_SS$$.SNAPSHOT_TIMESTAMP) expressionRepeatedByColumn PIT_SS$$.SNAPSHOT_TIMESTAMP consists of source table PIT_SS$$ componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent PIT_DIM_FACT$$ consists of left_outer join JOIN_FACT_SRC componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy PIT_SS$$.SNAPSHOT_TIMESTAMP > COALESCE(FACT_SRC.FACT_SNAPSHOT_TIMESTAMP, GTIMECAST[#01/01/1900SPACE00:00:01#]) expressionDefinedByAttribute FACT_SRC.FACT_SNAPSHOT_TIMESTAMP consists of joined table FACT_SRC componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT comp_subgroup_end comp_group_end comp_group_start NOT_EXISTS_IN_FACT_PIT_BIHOURLY_GROUP INL_V_GRP comp_subgroup_start $ PIT_SS_SUBGROUP componentSubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] componentsubgrouprepeatedbycomponent PIT_DIM_FACT consists of target distinct NOT_EXISTS_IN_FACT_PIT_BIHOURLY componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP expressedBy PIT_SS$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_SS$$.SNAPSHOT_TIMESTAMP Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy date_trunc(#hourly#,PIT_SS$$.SNAPSHOT_TIMESTAMP) expressionRepeatedByColumn PIT_SS$$.SNAPSHOT_TIMESTAMP consists of source table PIT_SS$$ componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent PIT_DIM_FACT$$ consists of left_outer join JOIN_FACT_SRC componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy PIT_SS$$.SNAPSHOT_TIMESTAMP > COALESCE(FACT_SRC.FACT_SNAPSHOT_TIMESTAMP, GTIMECAST[#01/01/1900SPACE00:00:01#]) expressionDefinedByAttribute FACT_SRC.FACT_SNAPSHOT_TIMESTAMP consists of joined table FACT_SRC componentConditionedBy [(LOOP: $ = 1)] componentrepeatedbycomponent FACT comp_subgroup_end comp_group_end comp_group_start $ PIT_DIM_GROUP INL_V_GRP componentGroupRepeatedByComponent PIT_DIM_FACT consists of target inline_view PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ Attribute OBJECT_L_H_KEY expressedBy PIT_SRC$$.OBJECT_L_H_KEY expressionRepeatedByColumn PIT_SRC$$.OBJECT_L_H_KEY ArtifactPart DIM_LVL2_OBJECT_H_KEY GROUP_1 expressedBy HASHFUNC[ ArtifactPart $ DIM_LVL2_OBJECT_H_KEY attributerepeatedbycomponent PITDIM_LVL2$$ GROUP_2 expressedBy SAT_SRC$$$.SRC_LVL2_KEY expressionRepeatedByColumn SAT_SRC$$$.SRC_LVL2_KEY COMMA ArtifactPart DIM_LVL2_OBJECT_H_KEY GROUP_3 expressedBy || PIT_SRC$$.OBJECT_L_H_KEY expressionRepeatedByColumn PIT_SRC$$.OBJECT_L_H_KEY GROUP_4 expressedBy HASHFUNC] Attribute SNAPSHOT_TIMESTAMP expressedBy PIT_SRC$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_SRC$$.SNAPSHOT_TIMESTAMP Artifact DAY_SNAPSHOT_TIMESTAMP AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] expressedBy date_trunc(#day#,PIT_SRC$$.SNAPSHOT_TIMESTAMP) expressionRepeatedByColumn PIT_SRC$$.SNAPSHOT_TIMESTAMP Artifact MONTH_SNAPSHOT_TIMESTAMP AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] expressedBy date_trunc(#month#,PIT_SRC$$.SNAPSHOT_TIMESTAMP) expressionRepeatedByColumn PIT_SRC$$.SNAPSHOT_TIMESTAMP Artifact BIHOURLY_SNAPSHOT_TIMESTAMP AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] expressedBy date_trunc(#hourly#,PIT_SRC$$.SNAPSHOT_TIMESTAMP) expressionRepeatedByColumn PIT_SRC$$.SNAPSHOT_TIMESTAMP Attribute OBJECT_L_H_KEY expressedBy PIT_SRC$$.OBJECT_L_H_KEY expressionRepeatedByColumn PIT_SRC$$.OBJECT_L_H_KEY consists of source table PIT_SRC$$ componentrepeatedbycomponent PIT_DIM_FACT$$ consists of inner join JOIN_NOT_EXISTS_IN_FACT_PIT_DAY componentConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy PIT_SRC$$.SNAPSHOT_TIMESTAMP = NOT_EXISTS_IN_FACT_PIT_DAY.FACT_SNAPSHOT_TIMESTAMP expressionDefinedByAttribute NOT_EXISTS_IN_FACT_PIT_DAY.FACT_SNAPSHOT_TIMESTAMP consists of joined inline_view NOT_EXISTS_IN_FACT_PIT_DAY componentConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] componentrepeatedbycomponent FACT consists of inner join JOIN_NOT_EXISTS_IN_FACT_PIT_MONTH componentConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy PIT_SRC$$.SNAPSHOT_TIMESTAMP = NOT_EXISTS_IN_FACT_PIT_MONTH.FACT_SNAPSHOT_TIMESTAMP expressionDefinedByAttribute NOT_EXISTS_IN_FACT_PIT_MONTH.FACT_SNAPSHOT_TIMESTAMP consists of joined inline_view NOT_EXISTS_IN_FACT_PIT_MONTH componentConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] componentrepeatedbycomponent FACT consists of inner join JOIN_NOT_EXISTS_IN_FACT_PIT_BIHOURLY componentConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy PIT_SRC$$.SNAPSHOT_TIMESTAMP = NOT_EXISTS_IN_FACT_PIT_BIHOURLY.FACT_SNAPSHOT_TIMESTAMP expressionDefinedByAttribute NOT_EXISTS_IN_FACT_PIT_BIHOURLY.FACT_SNAPSHOT_TIMESTAMP consists of joined inline_view NOT_EXISTS_IN_FACT_PIT_BIHOURLY componentConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] componentrepeatedbycomponent FACT comp_subgroup_start $ SAT_SRC_SUBGROUP componentsubgrouprepeatedbycomponent PITDIM_LVL2$$ consists of inner join JOIN_SAT_SRC$$$ componentrepeatedbycomponent PITDIM_LVL2$$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy PIT_SRC$$.OBJECT_F_H_KEY = SAT_SRC$$$.OBJECT_H_KEY expressionRepeatedByColumn SAT_SRC$$$.OBJECT_H_KEY GROUP_2 expressedBy AND PIT_SRC$$.SAT_LOAD_TIMESTAMP = SAT_SRC$$$.TRANS_TIMESTAMP expressionRepeatedByColumn SAT_SRC$$$.TRANS_TIMESTAMP consists of joined table SAT_SRC$$$ componentrepeatedbycomponent PITDIM_LVL2$$$ comp_subgroup_end comp_group_end comp_group_start $ SAT_FACT_GROUP INL_V_GRP componentGroupRepeatedByComponent SAT_FACT consists of target inline_view SAT_FACT$$ componentrepeatedbycomponent SAT_FACT$$ Attribute OBJECT_H_KEY expressedBy SAT_SRC$$.OBJECT_H_KEY expressionRepeatedByColumn SAT_SRC$$.OBJECT_H_KEY Artifact DAY_TRANS_TIMESTAMP expressedBy date_trunc(#day#,SAT_SRC$$.TRANS_TIMESTAMP) expressionRepeatedByColumn SAT_SRC$$.TRANS_TIMESTAMP Artifact MONTH_TRANS_TIMESTAMP expressedBy date_trunc(#month#,SAT_SRC$$.TRANS_TIMESTAMP) expressionRepeatedByColumn SAT_SRC$$.TRANS_TIMESTAMP Artifact HOURLY_TRANS_TIMESTAMP expressedBy date_trunc(#hourly#,SAT_SRC$$.TRANS_TIMESTAMP) expressionRepeatedByColumn SAT_SRC$$.TRANS_TIMESTAMP Attribute SRC_MEASURE expressedBy SAT_SRC$$.SRC_MEASURE expressionRepeatedByColumn SAT_SRC$$.SRC_MEASURE consists of source table SAT_SRC$$ componentrepeatedbycomponent SAT$$ comp_group_end comp_group_start AGGR_DAY_GROUP INL_V_GRP consists of aggregated inline_view AGGR_DAY componentrepeatedbycomponent FACT Attribute $ FACT_SNAPSHOT_TIMESTAMP AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] AttributeRepeatedByComponent PIT_DIM_FACT expressedBy PIT_DIM$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_DIM$$.SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] AttributeRepeatedByComponent PIT_DIM_FACT expressedBy PIT_DIM$$.DIM_LVL2_OBJECT_H_KEY expressionRepeatedByColumn PIT_DIM$$.OBJECT_L_H_KEY Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy NOT_EXISTS_IN_FACT_PIT_DAY.LEVEL_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn NOT_EXISTS_IN_FACT_PIT_DAY.LEVEL_SNAPSHOT_TIMESTAMP Aggregated Attribute $ SUM_SRC_MEASURE AttributeRepeatedByComponent SAT_FACT expressedBy SUM(SAT_FACT$$.SRC_MEASURE) expressionRepeatedByColumn SAT_FACT$$.SRC_MEASURE Aggregated Attribute $ AVG_SRC_MEASURE AttributeRepeatedByComponent SAT_FACT expressedBy AVG(SAT_FACT$$.SRC_MEASURE) expressionRepeatedByColumn SAT_FACT$$.SRC_MEASURE consists of source table BRIDGE_SRC componentrepeatedbycomponent BRIDGE consists of inner join JOIN_NOT_EXISTS_IN_FACT componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy 1 = 1 consists of joined inline_view NOT_EXISTS_IN_FACT_PIT_DAY componentrepeatedbycomponent FACT comp_subgroup_start $ PIT_DIM_SUBGROUP componentsubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = DAILY_PIT)] componentsubgrouprepeatedbycomponent PIT_DIM_FACT consists of inner join JOIN_PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy BRIDGE_SRC.OBJECT_H_KEY = PIT_DIM$$.OBJECT_L_H_KEY expressionRepeatedByColumn PIT_DIM$$.OBJECT_L_H_KEY GROUP_2 expressedBy AND NOT_EXISTS_IN_FACT_PIT_DAY.LEVEL_SNAPSHOT_TIMESTAMP = PIT_DIM$$.DAY_SNAPSHOT_TIMESTAMP consists of joined inline_view PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ comp_subgroup_end comp_subgroup_start $ SAT_FACT_SUBGROUP componentsubgrouprepeatedbycomponent SAT_FACT consists of inner join JOIN_SAT_FACT$$ componentrepeatedbycomponent SAT_FACT$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy BRIDGE_SRC.OBJECT_H_KEY = SAT_FACT$$.OBJECT_H_KEY expressionRepeatedByColumn SAT_FACT$$.OBJECT_H_KEY GROUP_2 expressedBy AND NOT_EXISTS_IN_FACT_PIT_DAY.LEVEL_SNAPSHOT_TIMESTAMP = SAT_FACT$$.DAY_TRANS_TIMESTAMP expressionRepeatedByColumn NOT_EXISTS_IN_FACT_PIT_DAY.LEVEL_SNAPSHOT_TIMESTAMP consists of joined inline_view SAT_FACT$$ componentrepeatedbycomponent SAT$$ comp_subgroup_end comp_group_end comp_group_start AGGR_MONTH_GROUP INL_V_GRP consists of aggregated inline_view AGGR_MONTH componentrepeatedbycomponent FACT Attribute $ FACT_SNAPSHOT_TIMESTAMP AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] AttributeRepeatedByComponent PIT_DIM_FACT expressedBy PIT_DIM$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_DIM$$.SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] AttributeRepeatedByComponent PIT_DIM_FACT expressedBy PIT_DIM$$.DIM_LVL2_OBJECT_H_KEY expressionRepeatedByColumn PIT_DIM$$.OBJECT_L_H_KEY Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy NOT_EXISTS_IN_FACT_PIT_MONTH.LEVEL_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn NOT_EXISTS_IN_FACT_PIT_MONTH.LEVEL_SNAPSHOT_TIMESTAMP Aggregated Attribute $ SUM_SRC_MEASURE AttributeRepeatedByComponent SAT_FACT expressedBy SUM(SAT_FACT$$.SRC_MEASURE) expressionRepeatedByColumn SAT_FACT$$.SRC_MEASURE Aggregated Attribute $ AVG_SRC_MEASURE AttributeRepeatedByComponent SAT_FACT expressedBy AVG(SAT_FACT$$.SRC_MEASURE) expressionRepeatedByColumn SAT_FACT$$.SRC_MEASURE consists of source table BRIDGE_SRC componentrepeatedbycomponent BRIDGE consists of inner join JOIN_NOT_EXISTS_IN_FACT componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy 1 = 1 consists of joined inline_view NOT_EXISTS_IN_FACT_PIT_MONTH componentrepeatedbycomponent FACT comp_subgroup_start $ PIT_DIM_SUBGROUP componentsubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = MONTHLY_PIT)] componentsubgrouprepeatedbycomponent PIT_DIM_FACT consists of inner join JOIN_PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy BRIDGE_SRC.OBJECT_H_KEY = PIT_DIM$$.OBJECT_L_H_KEY expressionRepeatedByColumn PIT_DIM$$.OBJECT_L_H_KEY GROUP_2 expressedBy AND NOT_EXISTS_IN_FACT_PIT_MONTH.LEVEL_SNAPSHOT_TIMESTAMP = PIT_DIM$$.MONTH_SNAPSHOT_TIMESTAMP consists of joined inline_view PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ comp_subgroup_end comp_subgroup_start $ SAT_FACT_SUBGROUP componentsubgrouprepeatedbycomponent SAT_FACT consists of inner join JOIN_SAT_FACT$$ componentrepeatedbycomponent SAT_FACT$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy BRIDGE_SRC.OBJECT_H_KEY = SAT_FACT$$.OBJECT_H_KEY expressionRepeatedByColumn SAT_FACT$$.OBJECT_H_KEY GROUP_2 expressedBy AND NOT_EXISTS_IN_FACT_PIT_MONTH.LEVEL_SNAPSHOT_TIMESTAMP = SAT_FACT$$.MONTH_TRANS_TIMESTAMP expressionRepeatedByColumn NOT_EXISTS_IN_FACT_PIT_MONTH.LEVEL_SNAPSHOT_TIMESTAMP consists of joined inline_view SAT_FACT$$ componentrepeatedbycomponent SAT$$ comp_subgroup_end comp_group_end comp_group_start AGGR_BIHOURLY_GROUP INL_V_GRP consists of aggregated inline_view AGGR_BIHOURLY componentrepeatedbycomponent FACT Attribute $ FACT_SNAPSHOT_TIMESTAMP AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] AttributeRepeatedByComponent PIT_DIM_FACT expressedBy PIT_DIM$$.SNAPSHOT_TIMESTAMP expressionRepeatedByColumn PIT_DIM$$.SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY AttributeConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] AttributeRepeatedByComponent PIT_DIM_FACT expressedBy PIT_DIM$$.DIM_LVL2_OBJECT_H_KEY expressionRepeatedByColumn PIT_DIM$$.OBJECT_L_H_KEY Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy NOT_EXISTS_IN_FACT_PIT_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn NOT_EXISTS_IN_FACT_PIT_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP Aggregated Attribute $ SUM_SRC_MEASURE AttributeRepeatedByComponent SAT_FACT expressedBy SUM(SAT_FACT$$.SRC_MEASURE) expressionRepeatedByColumn SAT_FACT$$.SRC_MEASURE Aggregated Attribute $ AVG_SRC_MEASURE AttributeRepeatedByComponent SAT_FACT expressedBy AVG(SAT_FACT$$.SRC_MEASURE) expressionRepeatedByColumn SAT_FACT$$.SRC_MEASURE consists of source table BRIDGE_SRC componentrepeatedbycomponent BRIDGE consists of inner join JOIN_NOT_EXISTS_IN_FACT componentrepeatedbycomponent FACT Artifact GENERAL_EXPRESSION expressedBy 1 = 1 consists of joined inline_view NOT_EXISTS_IN_FACT_PIT_BIHOURLY componentrepeatedbycomponent FACT comp_subgroup_start $ PIT_DIM_SUBGROUP componentsubgroupConditionedBy [(TAB PIT_DIM_FACT$$ : PIT_TEMPLATE_NAME = BI_HOURLY_PIT)] componentsubgrouprepeatedbycomponent PIT_DIM_FACT consists of inner join JOIN_PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy BRIDGE_SRC.OBJECT_H_KEY = PIT_DIM$$.OBJECT_L_H_KEY expressionRepeatedByColumn PIT_DIM$$.OBJECT_L_H_KEY GROUP_2 expressedBy AND NOT_EXISTS_IN_FACT_PIT_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP = PIT_DIM$$.BIHOURLY_SNAPSHOT_TIMESTAMP consists of joined inline_view PIT_DIM$$ componentrepeatedbycomponent PIT_DIM_FACT$$ comp_subgroup_end comp_subgroup_start $ SAT_FACT_SUBGROUP componentsubgrouprepeatedbycomponent SAT_FACT consists of inner join JOIN_SAT_FACT$$ componentrepeatedbycomponent SAT_FACT$$ Artifact GENERAL_EXPRESSION GROUP_1 expressedBy BRIDGE_SRC.OBJECT_H_KEY = SAT_FACT$$.OBJECT_H_KEY expressionRepeatedByColumn SAT_FACT$$.OBJECT_H_KEY GROUP_2 expressedBy AND NOT_EXISTS_IN_FACT_PIT_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP = SAT_FACT$$.HOURLY_TRANS_TIMESTAMP expressionRepeatedByColumn NOT_EXISTS_IN_FACT_PIT_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP consists of joined inline_view SAT_FACT$$ componentrepeatedbycomponent SAT$$ comp_subgroup_end comp_group_end comp_group_start MAIN_GROUP INS_GRP componentGroupRepeatedByComponent FACT comp_subgroup_start DAY componentsubgrouprepeatedbycomponent FACT consists of UNION_ALL SET FACT_TGT componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP expressedBy AGGR_DAY.FACT_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn AGGR_DAY.FACT_SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY$ AttributeRepeatedByComponent PIT_DIM_FACT expressedBy AGGR_DAY.DIM_OBJECT_H_KEY$ expressionRepeatedByColumn AGGR_DAY.DIM_OBJECT_H_KEY$ Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy AGGR_DAY.LEVEL_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn AGGR_DAY.LEVEL_SNAPSHOT_TIMESTAMP Attribute SUM_SRC_MEASURE expressedBy AGGR_DAY.SUM_SRC_MEASURE expressionRepeatedByColumn AGGR_DAY.SUM_SRC_MEASURE Attribute AVG_SRC_MEASURE expressedBy AGGR_DAY.AVG_SRC_MEASURE expressionRepeatedByColumn AGGR_DAY.AVG_SRC_MEASURE consists of source inline_view AGGR_DAY componentrepeatedbycomponent FACT comp_subgroup_end comp_subgroup_start MONTH componentsubgrouprepeatedbycomponent FACT consists of UNION_ALL SET FACT_TGT componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP expressedBy AGGR_MONTH.FACT_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn AGGR_MONTH.FACT_SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY$ AttributeRepeatedByComponent PIT_DIM_FACT expressedBy AGGR_MONTH.DIM_OBJECT_H_KEY$ expressionRepeatedByColumn AGGR_MONTH.DIM_OBJECT_H_KEY$ Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy AGGR_MONTH.LEVEL_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn AGGR_MONTH.LEVEL_SNAPSHOT_TIMESTAMP Attribute SUM_SRC_MEASURE expressedBy AGGR_MONTH.SUM_SRC_MEASURE expressionRepeatedByColumn AGGR_MONTH.SUM_SRC_MEASURE Attribute AVG_SRC_MEASURE expressedBy AGGR_MONTH.AVG_SRC_MEASURE expressionRepeatedByColumn AGGR_MONTH.AVG_SRC_MEASURE consists of source inline_view AGGR_MONTH componentrepeatedbycomponent FACT comp_subgroup_end comp_subgroup_start BIHOURLY componentsubgrouprepeatedbycomponent FACT consists of UNION_ALL SET FACT_TGT componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP expressedBy AGGR_BIHOURLY.FACT_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn AGGR_BIHOURLY.FACT_SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY$ AttributeRepeatedByComponent PIT_DIM_FACT expressedBy AGGR_BIHOURLY.DIM_OBJECT_H_KEY$ expressionRepeatedByColumn AGGR_BIHOURLY.DIM_OBJECT_H_KEY$ Attribute LEVEL_SNAPSHOT_TIMESTAMP expressedBy AGGR_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP expressionRepeatedByColumn AGGR_BIHOURLY.LEVEL_SNAPSHOT_TIMESTAMP Attribute SUM_SRC_MEASURE expressedBy AGGR_BIHOURLY.SUM_SRC_MEASURE expressionRepeatedByColumn AGGR_BIHOURLY.SUM_SRC_MEASURE Attribute AVG_SRC_MEASURE expressedBy AGGR_BIHOURLY.AVG_SRC_MEASURE expressionRepeatedByColumn AGGR_BIHOURLY.AVG_SRC_MEASURE consists of source inline_view AGGR_BIHOURLY componentrepeatedbycomponent FACT comp_subgroup_end comp_group_end ``` ## **4.5: Upload the ETL Fact 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 Click Upload in the editor. ![image](media://004e10bc-cb90-46bf-9036-8827c25a642f) Choose your template (.dvt) file Now click Upload and Save. ## **4.6: Upload the Fact template** For the DDL, 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_ALL SET** to **CREATE_TABLE** **table.** Then remove everything lower than the attribute level. The template should look then like this. ``` Template FACT comp_group_start MAIN_GROUP CREA_GRP componentGroupRepeatedByComponent FACT consists of CREATE_TABLE table FACT_TGT componentrepeatedbycomponent FACT Attribute FACT_SNAPSHOT_TIMESTAMP Attribute $ DIM_OBJECT_H_KEY$ AttributeRepeatedByComponent PIT_DIM_FACT Attribute LEVEL_SNAPSHOT_TIMESTAMP Attribute SUM_SRC_MEASURE Attribute AVG_SRC_MEASURE comp_group_end ``` Upload it into the VaultSpeed Studio by Clicking View on the DDL Template column. ![image](media://a0cc1803-5a8b-4dc7-9221-ab9c989b27e7) And Upload and Save. ![image](media://9c9255a9-9594-46e0-b67c-f2fb4cf5b140)