Database Setup

About this task

This Database is used by CDM to store all the tables across different database schemas like LDZ, RDV, BDV, 360.

Procedure

  1. Access the server having Oracle database.
  2. Log in to the Oracle database server using the environment-specific credentials.
  3. From the UDS installation package (HCL_Unica_CDM_<25.1.1>.zip), extract the ZIP file and navigate to the ddls folder.
  4. Execute the SQL scripts in this folder as per instructions provided below:
    Important: In an editor, open the file Step1_user_schema_creation.sql and search for the string CREATE USER. In all instances CREATE USER, replace the <password> value with actual passwords for the system user IDs. Ensure that all occurrences of <password> is replaced with the actual password for corresponding user ID.
  5. Connect to the Oracle database with credentials having DBA access.
    1. Upon connecting to the Oracle database, execute the first DD i.e. HCL_Unica_CDM_<25.1.1>.zip/ddls/Step1_user_schema_creation.sql.
      If the DDL execution is successful, log out of the DB connection. This creates all Oracle users/schemas (canonical, dev_raw, dev_ldz, dev_rdv, dev_bdv, cs_360_db, cdp_metadata, etc.) and grants base privileges so later scripts can create objects in each layer.
    Schema name Description on usage
    canonical This layer is the standardized "canonical" source layout used to land inbound data before it moves into RAW/LDZ.
    dev_raw The tables in this schema hold the raw, history-preserving copies of source data with load timestamps and arrival dates.
    dev_ldz The tables in this schema store the standardized, current/operational layer (LDZ) copies of key entities used for CDC and downstream transformations.
    dev_rdv This schema contains tables in Raw Data Vault format that store history, normalized core data as Hub, Link and Satellite tables
    dev_bdv This schema consists of snapshot tables for campaign/UA entities (attributes, campaigns, offers, etc.) that serve as the Business Data Vault / campaign metadata layer.
    cs_360_db This schema contains the core cs_360_db.customer_360 table (and related 360 structures) that will hold the consolidated customer 360 view exposed to consumers.
    cdp_metadata This schema contains standard reference data tables used across ETL code
    imp_cdp_db This schema contains dimensional views that reshape RDV/BDV data into star-schema friendly dimensions

    The users created in this step will be used to execute the next steps as listed below:

    Schema name to execute the SQL SQL Script Description / Outcome
    canonical HCL_Unica_CDM_<25.1.1>.zip/ddls/Step2_canonical_source.sql This creates canonical schema tables (e.g. canonical.ldz_*) that define the standardized “canonical” source layout used to land inbound data before it moves into RAW/LDZ.
    dev_raw HCL_Unica_CDM_<25.1.1>.zip/ddls/Step3_raw_ddl.sql This script creates dev_raw tables (LDZ_* style) that hold the raw, history-preserving copies of source data with load timestamps and arrival dates.
    dev_ldz HCL_Unica_CDM_<25.1.1>.zip/ddls/Step4_ldz_ddl.sql This creates dev_ldz tables that store the standardized, current/operational layer (LDZ) copies of key entities used for CDC and downstream transformations.
    dev_rdv HCL_Unica_CDM_<25.1.1>.zip/ddls/Step5_rdv_ddl.sql This creates Raw Data Vault tables (audience map and associated DV-style entities) in dev_rdv that store history, normalized core data.
    dev_bdv HCL_Unica_CDM_<25.1.1>.zip/ddls/Step6_bdv_ddls.sql This creates dev_bdv snapshot tables for campaign/UA entities (attributes, campaigns, offers, etc.) that serve as the Business Data Vault / campaign metadata layer.
    cs_360_db HCL_Unica_CDM_<25.1.1>.zip/ddls/Step7_ddl_360.sql This creates the core cs_360_db.customer_360 table (and related 360 structures) that will hold the consolidated customer 360 view exposed to consumers.
    cdp_metadata HCL_Unica_CDM_<25.1.1>.zip/ddls/Step8_date_control.sql

    HCL_Unica_CDM_<25.1.1>.zip/ddls/Step9_reference_data.sql

    		 The Step8 script creates ETL date control and holiday tables in cdp_metadata and seeds initial rows, used by jobs to determine business dates and run windows.

    The Step9 script creates a REF_DATA_MAPPING table in cdp_metadata and inserts sample mappings so raw values can be standardized (e.g. channel codes) during processing.
    canonical_etl_db_user HCL_Unica_CDM_<25.1.1>.zip/ddls/Step10_cross_schema_table_access.sql This script uses PL/SQL loops to grant SELECT (and sometimes DML) on all tables across schemas (DEV_RAW, DEV_RDV, DEV_BDV, etc.) to other schemas so they can build views and procedures without manual grants.
    dev_bdv HCL_Unica_CDM_<25.1.1>.zip/ddls/Step11_bdv_camp_views.sql This creates views in dev_bdv that join UA campaign/offer tables and pivot their attributes into JSON (e.g. VW_BDV_CAMPAIGN_ATTRIBUTES, VW_BDV_OFFER_ATTRIBUTES), making campaign metadata easier to consume.
    dev_raw HCL_Unica_CDM_<25.1.1>.zip/ddls/Step12_campaign_raw_views.sql This creates simplified views in dev_raw over UA raw tables (campaigns, offers, contact history, etc.) to expose them in a consistent, report-friendly format.
    im_cdp_db
    • HCL_Unica_CDM_<25.1.1>.zip/ddls/Step13a_dimensional_views_updated.sql
    • HCL_Unica_CDM_<25.1.1>.zip/ddls/Step13b_fact_party_device_relationship.sql
    • HCL_Unica_CDM_<25.1.1>.zip/ddls/Step13c_updated_dim_account.sql
    • HCL_Unica_CDM_<25.1.1>.zip/ddls/Step13d_updated_dim_party.sql
    • HCL_Unica_CDM_<25.1.1>.zip/ddls/Step13e_updated_fact_relationship_views_oracle.sql
    • HCL_Unica_CDM_<25.1.1>.zip/ddls/Step13f_updated_multi_active_dimensional_and_bridge_views.sql
    • HCL_Unica_CDM_<25.1.1>.zip/ddls/Step13g_updated_transaction_facts.sql
    		 These create below artifacts in im_cdp_db schema:
    • core dimensional views (e.g. dim_offer, dim_campaign, dim_channel, etc.) that reshape RDV/BDV data into star-schema friendly dimensions.
    • FACT_PARTY_DEVICE_RELATIONSHIP, a fact view that links parties to devices and surfaces relationship attributes for analytics.
    • dim_account, an account dimension view exposing account attributes (status, balances, product, dates, etc.) keyed off the account hub.
    • dim_party, a party/person dimension view with demographic details (name, gender, marital status, income range, etc.) based on party hub and satellites.
    • relationship fact views (e.g. fact_account_party) that tie accounts to parties and roles, capturing relationship effective dates and attributes.
    • multi-active dimension / bridge views (e.g. dim_party_identity and other bridge structures) so multiple concurrent attributes (like IDs, contact points) can be analyzed correctly over time.
    • transaction fact views (e.g. fact_base_transactions) that join account-party relationships with transaction satellites to expose a unified transaction fact grain.
    canonical_etl_db_user HCL_Unica_CDM_<25.1.1>.zip/ddls/Step14_cross_view_access.sql This script uses PL/SQL loops to grant SELECT on all views in IM_CDP_DB and DEV_BDV to consuming schemas, ensuring cross-schema read access to the dimensional and BDV views.
    cs_360_db
    • HCL_Unica_CDM_<25.1.1>.zip/ddls/Step15_c_360_views.sql
    • HCL_Unica_CDM_<25.1.1>.zip/ddls/Step16a_camp_360.sql
    • HCL_Unica_CDM_<25.1.1>.zip/ddls/Step16b_cust_360_proc.sql
    		 These scripts create below artefacts in cs_360_db:
    • 360 summary views (e.g. VW_CAMPAIGN_360) that aggregate campaign performance and engagement metrics from dimensional/fact views into a business-friendly format.
    • stored procedure GENERATE_CAMPAIGN_360_DATA which uses ETL date control to determine run windows and populates/refreshes the campaign 360 tables incrementally or in full.
    • stored procedure GENERATE_CUSTOMER_360_DATA which uses ETL date control and last run timestamps to compute and load the customer_360 table (incremental or full), counting processed parties and handling validation.
    canonical_etl_db_user HCL_Unica_CDM_<25.1.1>.zip/ddls/Step17_Role_level_access_with_triggers.sql This creates read/write roles for each schema (RAW, RDV, BDV, IM_CDP_DB, CS_360_DB, CDP_METADATA), bulk-grants table/view privileges to those roles, assigns roles to users, and defines schema-level triggers that automatically grant SELECT on newly created views to the appropriate read-only roles.
    Important: VERIFICATION STEP
    1. Connect to Oracle database with the user ID cs_360_db .
    2. Upon connection, execute the following SQL commands in the HCL_Unica_CDM_<25.1.1>.zip/ddls/ValidateProcedure.txt: 
      ALTER PROCEDURE CS_360_DB.GENERATE_CUSTOMER_360_DATA COMPILE; ALTER PROCEDURE CS_360_DB.GENERATE_CAMPAIGN_360_DATA COMPILE;
SELECT object_name, object_type, status FROM user_objects WHERE object_name = UPPER('GENERATE_CAMPAIGN_360_DATA') and object_type = 'PROCEDURE';
    3. If the earlier commands returns the status as INVALID, execute the following commands to debug: 
      SELECT line, position, text FROM user_errors WHERE name = UPPER('GENERATE_CAMPAIGN_360_DATA') ORDER BY line;
SELECT object_name, object_type, status FROM user_objects WHERE object_name = UPPER('GENERATE_CUSTOMER_360_DATA') and object_type = 'PROCEDURE';
    4. If the earlier commands returns the status as INVALID, execute the following command to debug: 
      SELECT line, position, text FROM user_errors WHERE name = UPPER('GENERATE_CUSTOMER_360_DATA') ORDER BY line;

      HCL_Unica_CDM_<25.1.1>.zip/ddls/Step10_cross_schema_table_access.sql

      HCL_Unica_CDM_<25.1.1>.zip/ddls/Step14_cross_view_access.sql

    5. If the status is INVALID execute below scripts as mentioned above with respective user. 
      HCL_Unica_CDM_<25.1.1>.zip/ddls/Step10_cross_schema_table_access.sql
HCL_Unica_CDM_<25.1.1>.zip/ddls/Step14_cross_view_access.sql
    If the DDL execution is successful, log out of the DB connection.

Results

The validation of stored procedures required in this UDS installation is now complete. This also completes all the Database Setup activities required for this UDS installation.