Scenario: Extending Dimension Entities

If you want to extend dimension entities for the KCRT_CONTACTS table, follow these steps. The USER_ID and ENABLE_FLAG fields are to be added.

Defining Content Packs

To define content packs, follow these steps:

  1. Create two content pack folders: CUSTOMIZATION_PPM.cp and CUSTOMIZATION_TARGET.cp.
  2. Create folders under these two content packs. For detailed structures, see Content Pack Structure .
  3. Under CUSTOMIZATION_TARGET.cp, do the following:

    1. Under the dwmetadata\entities directory, create and define the target entity JSON file CUSTOMIZATION_CONTACTS.json by copying the following to the file:

      {
      	"metadata_layout_version":"1.0",
      	"version":"1.0",
      	"content_pack":"CUSTOMIZATION_TARGET",
      	"entity_name":"CUSTOMIZATION_CONTACTS",
      	"entity_business_name":"CUSTOMIZATION CONTACTS",
      	"entity_description":"CUSTOMIZATION CONTACTS",
      	"dimension":{
      		"dimension_business_name":"CUSTOMIZATION CONTACTS",
      		"is_conformed":"true",
      		"dimension_type":"Primary",
      		"storage_strategy":{
      		         "segmented_by":"default",
      				 "partition_by":"na"
      		}
      	},
      	"schema":[
      		{
      			"attribute":"USER_ID",
      			"attribute_name":"USER_ID",
      			"attribute_business_name":"USER ID",
      			"attribute_description":"USER ID",
      			"attribute_type":"dimension",
      			"scd":"scd1",
      			"target_data_type":"int",
      			"size":"na",
      			"is_required":"false"
      		},
      		{
      			"attribute":"ENABLED_FLAG",
      			"attribute_name":"ENABLED_FLAG",
      			"attribute_business_name":"ENABLED FLAG",
      			"attribute_description":"ENABLED FLAG",
      			"attribute_type":"dimension",
      			"scd":"scd2",
      			"target_data_type":"varchar",
      			"size":"1",
      			"is_required":"false"
      		}
      	],
          "dimension_associated_dimension":[
      		{
      			"lookup_entity_name":"Contacts",
      			"role_entity_name":"CUSTOMIZATION_CONTACTS_EXTEND",
      			"role_entity_business_name":"Contacts",
      			"description":"Contacts"
      		}	
      	]
      }
      
          "dimension_associated_dimension":[
      		{
      			"lookup_entity_name":"Contacts",
      			"role_entity_name":"Extends_Contacts",
      			"role_entity_business_name":"Contacts",
      			"description":"Contacts"
      		}	
      	]
      }

      CUSTOMIZATION_CONTACTS.json defines:

      • Two fields in the target table: USER_ID and ENABLED_FLAG
      • The target entity that is to be extended: Contacts

      After you complete this step, the target entity JSON file is defined.

    2. Create and define cp.json by copying the following to the file:

      {
      	"metadata_layout_version": "1.0",
      	"content_pack_name": "CUSTOMIZATION_TARGET",
      	"version": "1.0",
      	"description": "Project Management & Portfolio Management Content Pack, Shared Entities.",
      	"require": {
      		"platform": ">=1.0.0"
      	},
      	"target_entities": [
      		{
      			"name": "CUSTOMIZATION_CONTACTS"
      		}
      	]
      }
      

      cp.json defines:

      • The content pack name: CUSTOMIZATION_TARGET

      • The target entity as defined in CUSTOMIZATION_CONTACTS.json: CUSTOMIZATION_CONTACTS

      After you complete this step, the cp.json file is defined.

  4. In CUSTOMIZATION_PPM.cp, do the following:

    1. Under the dwmetadata\entities directory, create and define the source entity JSON file CUSTOMIZATION_KCRT_CONTACTS.json:

      {
          "metadata_layout_version": "1.0",
          "version": "1.0",
          "content_pack": "CUSTOMIZATION_PPM",
          "source_product": "PPM",
          "entity_name": "CUSTOMIZATION_KCRT_CONTACTS",
          "entity_business_name": "PPM contacts table with customized field",
          "entity_description": "Contacts info with customized field",
          "schema": [
              {
                  "attribute": "CONTACT_ID",
                  "attribute_name": "CONTACT_ID",
                  "attribute_business_name": "Contact ID",
                  "attribute_description": "Contact ID",
                  "sql_data_type":"INT",
                  "size": "na",
                  "is_bk": "true",
                  "is_cdc": "false",
                  "is_required": "true",
                  "column_sequence": "1"
              },
      		{
                  "attribute": "USER_ID",
                  "attribute_name": "USER_ID",
                  "attribute_business_name": "User Id",
                  "attribute_description": "User ID of the contact",
                  "sql_data_type":"INT",
                  "size": "na",
                  "is_bk": "false",
                  "is_cdc": "false",
                  "is_required": "false",
                  "column_sequence": "2"
              },
              {
                  "attribute": "ENABLED_FLAG",
                  "attribute_name": "ENABLED_FLAG",
                  "attribute_business_name": "ENABLED FLAG",
                  "attribute_description": "ENABLED FLAG",
                  "sql_data_type":"VARCHAR",
                  "size": "1",
                  "is_bk": "false",
                  "is_cdc": "false",
                  "is_required": "false",
                  "column_sequence": "3"
              }
          ]
      }
      

      CUSTOMIZATION_KCRT_CONTACTS.json defines:

      • Three fields in the source table: CONTACT_ID and ENABLED_FLAG fields that are extended; USER_ID that is the primary key of the source table
      • The source entity name: CUSTOMIZATION_KCRT_CONTACTS

      After you complete this step, the source entity JSON file is defined.

    2. Under dwmetadata\streams, create and define the stream entity JSON file CUSTOMIZATION_CONTACTS_STREAM.json:

      {
      	"metadata_layout_version":"1.0",
      	"version":"1.0",
      	"content_pack":"CUSTOMIZATION_PPM",
      	"source_product":"PPM",
      	"stream_name":"CUSTOMIZATION_CONTACTS_STREAM",
      	"source_entities_includes":[
      		{
      			"source_entity_include":"CUSTOMIZATION_KCRT_CONTACTS"
      		}
      	],
      	"target_entities_includes":[
      		{
      			"target_entity_include":"CUSTOMIZATION_CONTACTS"
      		}	
      	],
      	"transforms":[
      		{
      		"sql": "drop table if exists PPM_CUSTOMIZATION_CONTACTS_${MD_SOURCE_INSTANCE_ID}_SSI CASCADE; 
      --==--==--
      create table PPM_CUSTOMIZATION_CONTACTS_${MD_SOURCE_INSTANCE_ID}_SSI ( 
                      MD_BUSINESS_KEY			varchar(1000),
                      CUSTOMIZATION_CONTACTS_EXTEND_BUSINESS_KEY     varchar(1000),
                      CUSTOMIZATION_CONTACTS_EXTEND_ENTERPRISE_KEY     INT,
                      USER_ID   INT,
      				ENABLED_FLAG      varchar(1),
      
                      MD_BATCH_ID                 INT,
                      MD_PROCESS_ID               INT,
                      MD_SOURCE_INSTANCE_ID       INT,	
                      MD_FLAG                varchar(10)
      ); 
      --==--==--
      insert into PPM_CUSTOMIZATION_CONTACTS_${MD_SOURCE_INSTANCE_ID}_SSI (
                      MD_BUSINESS_KEY,
                      CUSTOMIZATION_CONTACTS_EXTEND_BUSINESS_KEY,
                      USER_ID,
      				ENABLED_FLAG,
                      MD_BATCH_ID ,
                      MD_PROCESS_ID ,
                      MD_SOURCE_INSTANCE_ID ,
                      MD_FLAG                       
      ) 
      select          
                      tab.MD_BUSINESS_KEY,
                      tab.CUSTOMIZATION_CONTACTS_EXTEND_BUSINESS_KEY,
                      tab.USER_ID,
      				tab.ENABLED_FLAG,				
                      ${MD_BATCH_ID} AS MD_BATCH_ID ,
                      ${MD_PROCESS_ID} AS MD_PROCESS_ID ,
                      ${MD_SOURCE_INSTANCE_ID} AS MD_SOURCE_INSTANCE_ID ,
                      tab.MD_FLAG 
      from (
                      select
                                      t1.md_source_instance_id || ':' || t1.md_business_key as MD_BUSINESS_KEY , 
      	t1.md_source_instance_id || ':' || t1.CONTACT_ID as CUSTOMIZATION_CONTACTS_EXTEND_BUSINESS_KEY, 
                                      USER_ID,
                                      ENABLED_FLAG,
      						        
                                      decode(t1.md_flag,'NEW','NEW','DEL','DEL','UPD') as md_flag,  
                                      row_number() over( partition by t1.md_source_instance_id || ':' || t1.md_business_key) multi_flag 
                      from CUSTOMIZATION_CONTACTS_STREAM_CUSTOMIZATION_KCRT_CONTACTS_${MD_SOURCE_INSTANCE_ID}_EXT t1
                      where t1.md_pf_flag = 'D') tab
      where tab.multi_flag = 1; 
      --==--==--
      	SELECT ANALYZE_STATISTICS('PPM_CUSTOMIZATION_CONTACTS_${MD_SOURCE_INSTANCE_ID}_SSI');
      --==--==--
      "
      		}
      	]
      } 
      

      CUSTOMIZATION_CONTACTS_STREAM.json defines:

      • Source entities that provide data
      • Target entities that accept data
      • SQLs mainly to transform data from source entities to target entities

      After you complete this step, the stream entity JSON file is defined.

    3. Under extmetadata, create and define the extractor entity JSON file CUSTOMIZATION_KCRT_CONTACTS_EXT.json:

      {
      	"metadata_layout_version":"1.0",
      	"version":"1.0",
      	"source_product":"PPM",
      	"content_pack": "CUSTOMIZATION_PPM",
      	"entity_name":"CUSTOMIZATION_KCRT_CONTACTS_EXT",
      	"source_entity_name":"KCRT_CONTACTS",
      	"extractor" : "OracleDBExtractor",
      	"extraction":[
      		{
      		"extraction_view" : "SELECT contact_id, user_id, ENABLED_FLAG FROM KCRT_CONTACTS",
      		"source_product_version" : "9.30"
      		}
      	]
      }
      

      CUSTOMIZATION_KCRT_CONTACTS_EXT.json defines:

      • The extractor entity name: CUSTOMIZATION_KCRT_CONTACTS_EXT
      • source_entity_name as the source table name

      • extraction_view: SQLs for extracting data

      After you complete this step, the extractor entity JSON file is defined.

    4. Create and define cp.json:

      {
      	"metadata_layout_version": "1.0",
      	"content_pack_name": "CUSTOMIZATION_PPM",
      	"source_product": "PPM",
      	"version": "1.0",
      	"description": "Project Management & Portfolio Management Content Pack, Extend Entities.",
      	"require": {
      	"platform": ">=1.0.0",
      		"cp": [
      			{
      				"name": "CUSTOMIZATION_TARGET",
      				"version": ">=1.0.0"
      			}
      		]
      	},
      	"streams": [
      		{
      			"name": "CUSTOMIZATION_CONTACTS_STREAM"
      		}
      	],
      	"source_entities": [
      		{
      			"name": "CUSTOMIZATION_KCRT_CONTACTS"
      		}
      	],
      	"extraction_entities": [
      		{
      			"name": "CUSTOMIZATION_KCRT_CONTACTS_EXT"
      		}
      	]
      }
      

      cp.json defines:

      • The content pack name: CUSTOMIZATION_PPM
      • Source entities: Defined in CUSTOMIZATION_KCRT_CONTACTS.json

      • Stream entities: Defined in CUSTOMIZATION_CONTACTS_STREAM.json
      • Extract entities: Defined in CUSTOMIZATION_KCRT_CONTACTS_EXT.json

Deploying Content Packs

To deploy content packs, follow these steps:

  1. Place CUSTOMIZATION_PPM.cp and CUSTOMIZATION_TARGET.cp under <VDW_HOME>/Content.

  2. Run the ContentManager.sh script under the <VDW_HOME>/bin directory to deploy CUSTOMIZATION_TARGET.cp:

    sh ContentManager.sh --instruction install --cpname CUSTOMIZATION_TARGET;

    You can find the following message from ContentManager.log under <VDW_HOME>/logs if the content pack is deployed successfully:

    The content of package CUSTOMIZATION_TARGET was successfully installed

    The DIM_ CUSTOMIZATION_CONTACTS table is generated in the Vertica database.

  3. Run the ContentManager.sh script under the <VDW_HOME>/bin directory to deploy CUSTOMIZATION_PPM.cp:

    sh ContentManager.sh --instruction install --cpname CUSTOMIZATION_PPM;

    You can find the following message from ContentManager.log under <VDW_HOME>/logs if the content pack is deployed successfully:

    The content of package CUSTOMIZATION_PPM was successfully installed
  4. Run the ExtractorEngine.sh script under the <VDW_HOME>/bin directory to extract data from the PPM database to flat files:

    sh ExtractorEngine.sh --streamname CUSTOMIZATION_CONTACTS_STREAM --instancename <PPM_Instance_Name>

    You can find the following message from ExtractorEngine.log under <VDW_HOME>/logs if the content pack is deployed successfully:

    Extractor was successfully executed. The BATCH ID is: <Batch_ID>.

    <PPM_Instance_Name> is the PPM instance name you specified when installing the Vertica for PPM Content Pack.

  5. Run the FlowEngine.sh script under the <VDW_HOME>/bin directory to process ETL:

    sh FlowEngine.sh --batch <Batch_ID> --streamname CUSTOMIZATION_CONTACTS_STREAM --instancename <PPM_Instance_Name>

    You can find the following message from FlowEngine.log under <VDW_HOME>/logs if the content pack is deployed successfully:

    ETL process was executed successfully

    <Batch_ID> used in this command is the batch ID that was generated in Step 4.

  6. Connect to the Vertica database and check whether the data has been loaded successfully:

    select * from <Target_Schema>.DIM_CUSTOMIZATION_CONTACTS

    <Target_Schema> is the name for the schema that contains target data and tables for reporting.