Tutorial Scenario Resolution - Part 4: Information Marts

In this part of the tutorial, we will focus on the creation of information marts. The main objective is to reproduce the following structure.

Steps:

1. Downlaod the file containing the queries:

2. Create New Queries in the appropriate source system

3. Create a State Machine to generate the im

im.turoial.sql

Resolution

1. Open the file
   dim_contracts:

   CODE

   DROP TABLE IF EXISTS im.dim_contracts;
   CREATE TABLE im.dim_contracts AS
   SELECT
   	hc.bk AS contract_id,
   	info.status AS contract_status, 
   	info.discount AS contract_discount,
   	info.closing_date AS contract_closing_date,
   	snapshot_dates.snapshot_date AS snapshot_date
   FROM dv.h_contract hc
   CROSS JOIN ref.snapshot_dates snapshot_dates
   INNER JOIN dv.sh_contract_ctx ctx ON ctx.hk = hc.hk
   INNER JOIN dv.vsh_contract_crm_info info ON info.hk = hc.hk
   WHERE ctx.src = 's01'
       AND ctx.load_dts <= snapshot_dates.snapshot_date AND ctx.load_end_dts > snapshot_dates.snapshot_date
       AND info.load_dts <= snapshot_dates.snapshot_date AND info.load_end_dts > snapshot_dates.snapshot_date;

   fields name are important, depending on how you named each entities, you could face issue with the generation of the query.

2. Create IM in the appropriate sources:

   1. CRM:

      1. dim_cars

      2. dim_contracts

      3. dim_customers

      4. fact_contract_details

   2. HR

      1. dim_employees

      2. dim_shops

3. Create a state machine to generate the whole im

   1. From States> State Machines, create a State Machine with the following query (language = JSON)
      

      JSON

      {
        "StartAt": "im-dim_cars",
        "TimeoutSeconds": 6000,
        "States": {
          "im-dim_cars": {
            "Type": "Task",
            "Resource": "ExecuteDataVaultQuery",
            "ResultPath": null,
            "Parameters": {
              "EnvironmentName": "main",
              "ProjectName": "Cars",
              "QueryType": "Reader",
              "QueryName": "SourceSystems/s01/dim_cars",
              "Type": "NonQuery",
              "CommandTimeout": 10
            },
            "Next": "im-dim_customers"
          },
          "im-dim_customers": {
            "Type": "Task",
            "Resource": "ExecuteDataVaultQuery",
            "ResultPath": null,
            "TimeoutSeconds": 10,
            "Parameters": {
              "EnvironmentName": "main",
              "ProjectName": "Cars",
              "QueryType": "Reader",
              "QueryName": "SourceSystems/s01/dim_customers",
              "Type": "NonQuery",
              "CommandTimeout": 10
            },
            "Next": "im-dim_contracts"
          },
          "im-dim_contracts": {
            "Type": "Task",
            "Resource": "ExecuteDataVaultQuery",
            "ResultPath": null,
            "TimeoutSeconds": 10,
            "Parameters": {
              "EnvironmentName": "main",
              "ProjectName": "Cars",
              "QueryType": "Reader",
              "QueryName": "SourceSystems/s01/dim_contracts",
              "Type": "NonQuery",
              "CommandTimeout": 10
            },
            "Next": "im-fact_contracts_details"
          },
          "im-fact_contracts_details": {
            "Type": "Task",
            "Resource": "ExecuteDataVaultQuery",
            "ResultPath": null,
            "TimeoutSeconds": 10,
            "Parameters": {
              "EnvironmentName": "main",
              "ProjectName": "Cars",
              "QueryType": "Reader",
              "QueryName": "SourceSystems/s01/fact_contract_details",
              "Type": "NonQuery",
              "CommandTimeout": 10
            },
            "Next": "im-dim_shops"
          },
          "im-dim_shops": {
            "Type": "Task",
            "Resource": "ExecuteDataVaultQuery",
            "ResultPath": null,
            "TimeoutSeconds": 10,
            "Parameters": {
              "EnvironmentName": "main",
              "ProjectName": "Cars",
              "QueryType": "Reader",
              "QueryName": "SourceSystems/s02/dim_shops",
              "Type": "NonQuery",
              "CommandTimeout": 10
            },
            "Next": "im-dim_employees"
          },
          "im-dim_employees": {
            "Type": "Task",
            "Resource": "ExecuteDataVaultQuery",
            "ResultPath": null,
            "TimeoutSeconds": 10,
            "Parameters": {
              "EnvironmentName": "main",
              "ProjectName": "Cars",
              "QueryType": "Reader",
              "QueryName": "SourceSystems/s02/dim_employees",
              "Type": "NonQuery",
              "CommandTimeout": 10
            },
            "End": true
          }
        }
      }

Fields:

• "StartAt": //im-”name of the table you want to generate first”

• “Next”: //starts a new query execution

• "TimeoutSeconds": //time before the execution is automatically stopped

• "States": // define the steps the State Machine must execute

  • "im-dim_cars": { // name of the table
    "Type": "Task",
    "Resource": "ExecuteDataVaultQuery", // name of activity
    "ResultPath": null,
    "Parameters": {
    "EnvironmentName": "main", // name of environment
    "ProjectName": "Cars", // name of the project
    "QueryType": "Reader",
    "QueryName": "SourceSystems/s02/dim_employees", // path to the query, you will find this information by editing the im query, it will be the “new name” of the query (in Metavault)
    

    

    
    "Type": "NonQuery",
    "CommandTimeout": 10