Skip to main content

Additional Key CMD Functionalities

1. Publishing IDL/Selective Codes

When and IDL or huge volume of customer or contact records loaded into CMD system, we should not publish all of them to not overload the consuming systems. The consuming systems are not capable enough of to process huge volume of data within few seconds. To address this issue, CMD system should have capability to not publish IDL records when it loaded into the system. CMD system should be able to publish them in batches. Below is the solution to be implemented to achieve the same:

  1. Create one attribute, NEED_TO_PUBLISH flag at parent level (customer or contact) level.
  2. When customer/contact loaded by IDL, set this flag NEED_TO_PUBLISH=N
  3. In publish service add another condition where NEED_TO_PUBLISH=Y to publish the customer/contact
  4. Create separate below table RETRIGGER_PUBLISH with PUBLIH_Entity (varchar(20)) and CODE (varchar(20)) attributes
Publish_EntityCode
CustomerIN12345678
ContactIN45677654
  1. Load the codes into RETRIGGER_PUBLISH table which needs to be published again (IDL or need to retrigger again)
  2. Develop separate service (REPUBLISH_CODES) to poll to RETRIGGER_PUBLISH table and publish the codes with below conditions.
  3. Call the publishing service (customer or contact) where Code is present in RETRIGGER_PUBLISH.
  4. Once publish is done successfully, remove the entry for RETRIGGER_PUBLISH table to not to retrigger it repeatedly.
  5. Whenever we want to republish any codes then, insert the code into RETRIGGER_PUBLISH table and the REPUBLISH_CODES service will publish codes automatically.

2. Metrics around publish events

  1. Track the publish events for customer and contact
    • Create a table PUBLISH_METRICS with CODE, ENTITY, EVENT, PUBLISHED_SRC and DATE_TIME fields with no constraints
    • The publishing service needs to make an entry in this table for every publish event
    • The stats reports can be extracted from the below table.
CODEENTITYEVENTPUBLISHED_SRCDATE_TIME
IN12345678CustomerCreatePRTLCurrent Date

3. Publish Complete Snapshot or full data set of CMD Data

There are many consumers who need full data set of CMD data to be loaded when it is integrated with CMD system. In future also any new system can integrate with CMD system to consume the customer, contact, concern and customer facility full data set in the beginning and incremental data after IDL.

The external systems are not allowed to directly connect to CMD system to avoid the performance of CMD system.

After IDL into CMD systems, CMD need to publish all customer, contact, concern and customer facility data into respective EMP topics which will have the complete snapshot with log compaction mode enabled on topics.

MAESTRO team consumes the CMD data from EMP kafka topic and push into RAW layer in Delta Lake format. From Raw layer MAESTRO team will cleanse and convert into relational format and put into cleanse layer.

Below are 2 types of consumers can look for CMD history or incremental data and the source of such data.

  1. Consumer who need data in JSON format. This type of consumer can directly consume from EMP topics.
  2. Consumer who need data in relational format. This type of consumer can consumer from MAESTRO cleanse layer.

4. QueryWS service

QueryWs is the service developed and deployed in CMD application (On-Prem) to be consumed by consumer on need basis. Below are the services exposed:

  1. SearchCarrierOffice
  2. SearchCustomerByLegacyCustomerNumber
  3. SearchCustomerBySCVCustomerNumber
  4. SearchLinkedCustomer
  5. SerachLoaction
  6. SearchOrganization
  7. SearchOrganizationV2
  8. SearchPerson
  9. SearchSalesCode

For every country CMD maintains unstructured address rule in cmd_country_rules table with below fields.

Country_Code

ISO_Country_Code

ZIP_Required

Rebilling_Option

Unstruct_Addr_Rule

Whenever the service provide response based on any of the service mentioned above, the cmd address fields are mapped to unstructuredAddressLine1, unstructuredAddressLine2, unstructuredAddressLine3 & unstructuredAddressLine4 as per below mapping

Field CodeMapping Code CharField Name
ADDRESS_NAMEAAddressName
PO_BOXBPoBox
STREET_NAMECStreet Name
STREET_NUMBERDStreet Number
ADDRESS_LINE2EAddress Line 2
CITY_GEO_IDFCity Geo Id
ADDRESS_LINE3GAddress Line 3
SEARCH_ZIP_CODEHSearch ZipCity Code
STATEIState
SEARCH_CITY_NAMEJSearch City Name
ZIP_CITYKZipCity City
SEARCH_LOCATION_CODELSearch Location Code
COUNTRY_NAMEMGeographyEntity Name
SPACE_Space
NEW_LINE_CHAR/New line Characters
AS_IS_TEXT“Text present between double quotes will be considered as is
  1. Get unstructured address rule from cmd_country_rules table in MDM_INFP_SMDSMD schema
  2. Based on the above rule map the address fields to unstructuredAddressLine1, unstructuredAddressLine2, unstructuredAddressLine3, unstructuredAddressLine4
  3. The response will be consumed in consumer application as they expect

Example: Rule C_D/"POSTFACH"B/E/H_J_M for Country Germany will be divided into 4 parts

C_D - unstructuredAddressLine1

“POSTFACH”B - unstructuredAddressLine2

E - unstructuredAddressLine3

H_J_M - unstructuredAddressLine4

Below is Germany country response message

unstructuredAddressLine1=COLORADO STRASSE 22, unstructuredAddressLine2=POSTFACH PO BOX 35, unstructuredAddressLine3=35, unstructuredAddressLine4=27580 BREMERHAVEN GERMANY

5. Publish codes based on source system

6. Postgres Database backup and Restore

As part of the Azure subscription, the Postgres database service will automatically take backup of database for every minute. So, we need not to take database backup explicitly by any command or script.

In case of DR, we need to restore the database from backup latest save point to new database by following below steps. The database names are taken from SIT environment for explanation

  1. The existing main Postgres Database is cmd-sitwe-pgsql where the CMD data persisted. There are 2 options to restore the PG Database.
  2. Option 1: to restore to new Database
    1. In case of DR or need to restore the database to new database
    2. login to Azure portal and navigate to cmd-sitwe-pgsql
    3. Trigger the restore command and provide new database details like cmd-sitwe-pgsql_bkp1
    4. Change keyvalt settings to reflect the new database name in all relevant configurations
    5. Restart services Azure Postgres service
  3. Option 2: restore to Database with old name itself to not to impact
    1. In case if we need main database name as is to avoid name conflicts in configurations or code execute below steps
    2. login to Azure portal and navigate to cmd-sitwe-pgsql
    3. Trigger the restore command and provide new database details like cmd-sitwe-pgsql_bkp1
    4. Navigate to cmd-sitwe-pgsql and delete main DB cmd-sitwe-pgsql
    5. Navigate to cmd-sitwe-pgsql_bkp1
    6. Trigger the restore command and provide new database details like cmd-sitwe-pgsql
    7. Change keyvalt settings to reflect the main database name in all relevant configurations if applicable
    8. Restart services Azure Postgres service

Below are steps executed to test the backup and restore activities as test scenario.

  1. In SIT environment, the existing main Postgres DB is cmd-sitwe-pgsql
  2. Create table and insert some data using below statements
    1. CREATE TABLE MDM_SMDSMD.DBRestoreTestTbl( testCol1 VARCHAR (50), testCol2 VARCHAR (100), testCol3 VARCHAR (500), testCol4 VARCHAR (5000)); commit;
    2. INSERT INTO MDM_SMDSMD.DBRestoreTestTbl(testCol1, testCol2, testCol3,testCol4) VALUES ('TestCol1Data1','TestCol2Data1','TestCol3Data1','TestCol4Data1');
    3. INSERT INTO MDM_SMDSMD.DBRestoreTestTbl(testCol1, testCol2, testCol3,testCol4) VALUES ('TestCol1Data2','TestCol2Data2','TestCol3Data2','TestCol4Data2');
    4. INSERT INTO MDM_SMDSMD.DBRestoreTestTbl(testCol1, testCol2, testCol3,testCol4) VALUES ('TestCol1Data3','TestCol2Data3','TestCol3Data3','TestCol4Data3'); commit;
  3. Navigate to cmd-sitwe-pgsql database and trigger restore cmd and provide new DB name like cmd-sitwe-pgsql_bkp1 2.1 Change keyvalt settings 2.2 Restart services
  4. Connect to cmd-sitwe-pgsql_bkp1 and verify that all existing tables and data present along with new table we created in step #3
  5. Delete the cmd-sitwe-pgsql_bkp1 database
  6. Delete the temp table created in cmd-sitwe-pgsql DB in step #2
Was this page helpful?