Post-Source Configuration Validations

After the completion of source configuration, we need to ensure that all activities are completed successfully. This section covers scenarios for all components to be validated after the source configuration. Validate each scenario in this section before loading data into the target data warehouse.

Database Component

# Validation Script or Steps to Validate
H01 Validate that database link to source database exist in target database.

Run the following query in target database as a MDADM user and validate that one record is displayed:

select * from DBA_DB_LINKS where db_link = 'CCB1_LINK';
H02 Validate that new context exist in the product instance metadata table.

Run the following query in target database as a MDADM user and validate that one row is displayed:

select * from b1_prod_instance where context_cd='CCB1';
H03 Validate all attributes for newly added context in the product instance metadata table.

Run the following query in target database as a MDADM user and validate the following:

select * from b1_prod_instance where context_cd='CCB1';
  1. Source application link present in Drillback_Url should be accessible.
  2. Language code is present (Default:ENG).
  3. Time zone code is present.
H04 Validate that the Time zone details of Source Database are populated into the Replication schema of the Target DB. Run the query SELECT * FROM <REP_SCHEMA_NAME>.B1_SRC_TZ_DETAILS; in the target database and validate the time zone details of source product. The output should be the time zone of the source Database.
H05 Validate that ETL jobs for Dimension/Fact/MV are exists in the configuration metadata.

Run the following query in target database as a MDADM user and validate that no rows are displayed:

select distinct o.target_object_name from b1_object_map o,b1_prod_instance i where o.prod_flg=i.prod_flg and i.context_cd='CCB1' and (o.target_object_name like 'CD%' or o.target_object_name like 'CF%' or o.target_object_name like '%MV%') and not exists(select 1 from b1_config_vw v where context_cd='CCB1' and o.target_object_name=v.entity_name);
H06 Validate that source tables marked for replication are exists in the replication schema.

Run the following query in target database as a MDADM user and validate that no results are displayed:

select s.* from b1_source_table s,b1_prod_instance i where s.prod_flg=i.prod_flg and context_cd='CCB1' and base_replicate_flg = 'Y' and not exists(select 1 from dba_tables t where s.tbl_name=t.table_name);
H07 Validate that primary key exist for each table in the replication schema.

Run the following query in target database as a MDADM user and validate that no results are displayed:

select s.tbl_name from b1_source_table s,b1_prod_instance i where s.prod_flg=i.prod_flg and i.context_cd='CCB1' and s.base_replicate_flg='Y' and not exists(select * from all_constraints c where c.constraint_type='P' and owner='CCB1REP' and s.tbl_name=c.table_name);
H08 Validate that index on slicing timestamp column exist for each table in the replication schema.

Run the following query in target database as a MDADM user and validate that no results are displayed:

select s.tbl_name from b1_source_table s,b1_prod_instance i where s.prod_flg=i.prod_flg and i.context_cd='CCB1' and s.base_replicate_flg='Y' and not exists(select 1 from all_ind_columns ind where index_owner='CCB1REP' and s.tbl_name=ind.table_name and ind.column_name='JRN_SLICING_TS');
H09 Validate that GoldenGate extract processes exist in source.

Run the following query in the target database as a MDADM user and validate the following:

Select s.prod_flg,max(group_nbr)+1 "Extract_Process_Count" , max(group_nbr)+1 "Distribution_Process_Count" from b1_source_table s ,b1_prod_instance i where s.prod_flg=i.prod_flg and i.context_cd='CCB1' and s.base_replicate_flg='Y' group by s.prod_flg;
H10 Validate that GoldenGate replicat processes exist in target.

Run the following query in target database as a MDADM user and validate that Expected_Process_Count is same as actual process count.

Select s.prod_flg,max(group_nbr)+1 "Expected_Process_Count" ,(Select count(group_name) from b1_checkpoint c,b1_prod_instance i where substr(c.group_name,1,4)=i.context_cd and context_cd='CCB1') "Actual_Process_Count" from b1_source_table s ,b1_prod_instance i where s.prod_flg=i.prod_flg and i.context_cd='CCB1' and s.base_replicate_flg='Y' group by s.prod_flg;
H11 Validate that all replication views exist in the replication schema.

Run the following query in target database as a MDADM user and validate the following:

select owner,count(1) from all_views where owner in('CCB1REP','DWADM') and view_name like 'B1%VW' and view_name not like '%UDD%' group by owner;
  1. In the replication schema for CCB, view count is 97.
  2. In the replication schema for MDM, view count is 38.
  3. In the replication schema for MWM, view count is 28.
  4. In the replication schema for WAM, view count is 48.
  5. In the replication schema for NMS, view count is 77.
  6. n the target DWADM schema, view count is 12 (only if NMS source configured).

ETL Component

# Validation Script or Steps to Validate
H12 Validate that all GoldenGate extract and distribution processes exist in source.

To validate GoldenGate processes in source:

  1. Log in to the Source Administration Server URL of GoldenGate Microservices.
  2. Verify the status of Extracts on Home screen.
  3. Log in to the Source Distribution Server URL of GoldenGate Microservices.
  4. Verify the status of Distribution process on Home screen.
H13 Validate that all GoldenGate replicat processes are exists in target .

To validate GoldenGate processes in Target:

  1. Log in to the Administration Server URL of GoldenGate Microservices in Target server.
  2. Verify the status of Replicats on Home screen.
H14 Validate that all Param files are moved from Target Application Server to Source and Target GoldenGate Microservices Servers.

To validate the Param files presence in Source GoldenGate Microservices server:

  1. Navigate to <Source Oracle GoldenGate Microservice Deployment Home>/etc/conf/ogg path on Source GoldenGate Microservices Server.
  2. Verify EXTRACT param files.

To validate the Param files presence in Target GoldenGate Microservices server:

  1. Navigate to <Target Oracle GoldenGate Microservice Deployment Home>/etc/conf/ogg path on Target GoldenGate Microservices Server.
  2. Verify REPLICAT param files.
H15 Validate that Schema Trandata is added to Source GoldenGate Microservices.

To validate if Schema Trandata is added:

  1. Log in to Source GoldenGate Microservices Administration Server URL.
  2. Navigate to Configuration > Credentials.
  3. Check if Schema Trandata is added.
H16 Validate that GoldenGate Microservices processes are started in both Source and Target Servers.

To validate GoldenGate processes in source:

  1. Log in to the Source Administration Server URL of GoldenGate Microservices.
  2. Verify the status of Extracts on Home screen.
  3. Validate that all Extract and Distribution processes created are in running state.

To validate GoldenGate processes in Target:

  1. Log in to the Target Administration Server URL of GoldenGate Microservices.
  2. Verify the status of Replicats on Home screen.
  3. Validate that all Replicat processes created are in running state.
H17 Validate that all ETL Configuration jobs are executed successfully.

Run the following query in target database as a MDADM user and validate that no results are displayed:

select * from oua_work.snp_session es where sess_status='E' and not exists(Select 1 from oua_work.snp_session ds Where sess_status='D' and es.sess_name=ds.sess_name);

Administration Tool Component

# Validation Script or Steps to Validate
H18 Validate that configured context exist under ETL Configuration→ Product Instance page.

Login to Oracle Utilities Analytics Administration Tool and validate the following:

  1. Click Product Instance in the ETL configuration section.
  2. Validate that one record is present for the configured source on the Product Instance page.
H19 Validate that jobs can be enabled or disabled for the configured context under ETL Configuration→ Job Configuration page.

Login to Oracle Utilities Analytics Administration Tool and validate the following:

  1. Click Product Instance in the ETL configuration section.
  2. Click the Enable Jobs button.
  3. Validate that Enable Jobs window opens.
  4. Select the Source Product, Instance Number, Job Type and click the Enable Jobs button.
  5. Validate that the Entity Active Flag is set to Y for all jobs of selected type.
  6. Make sure you disable all jobs if enabled during this validation process.

Network Management System Parameters and Buckets

If the Network Management System source is configured with Oracle Utilities Analytics Warehouse, the following parameters and buckets also need to be configured from the Administration Tool, and the replication view needs to be generated after this configuration. Any modification to these parameters or bucket configurations will require reset and reload of target dimensions and facts. Also, all replication views need to be regenerated.

# Validation Script or Steps to Validate
I01

Country

Country code where the addresses are located.

Run the following query in target database as a MDADM user and validate that one record is displayed with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_COUNTRY' and instance_num=1;
I02

Interruptions Count for CEMI

Number of interruptions that you should have experienced to calculate the CEMI index. This is required by the City and Control Zone Outage facts (CF_CITY_OUTG, and CF_CTRL_ZONE_OUTG)

Run the following query in target database as a MDADM user and validate that one record is displayed with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_INTR_CNT_CEMI' and instance_num=1;
I03

Momentary Outage Duration

Duration in minutes to be used for determining if an outage is a momentary outage or a sustained outage. This is required by the Recent and Restored Customer Outage facts (CF_CUST_RECENT_OUTG, and CF_CUST_RST_OUTG)

Run the following query in target database as a MDADM user and validate that one record is displayed with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_MOM_OUTG_DUR' and instance_num=1;
I04

Number of Retention Days

Number of days in which restored outage data is retained in the Recent Call, Recent Crew, Recent Job and Recent Customer Outage facts (CF_RECENT_CALL, CF_RECENT_CREW, CF_RECENT_JOB, CF_RECENT_CUST_OUTG).

Run the following query in target database as a MDADM user and validate that one record is displayed with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_RET_DAYS' and instance_num=1;
I05

Priority Indicator Bit

Nth bit, which indicates the priority of the Complaint registered in the Incidents table. This is required by the Recent and Restored Call facts (CF_RECENT_CALL, and CF_RST_CALL).

Run the following query in target database as a MDADM user and validate that one record is displayed with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_PRTY_BIT' and instance_num=1;
I06

Switch Plan Aborted Step Status Key(s)

This is parameter value is used to identify the aborted steps status of a Switch Plan. This is used by the Switch Plan and switch plan state facts (CF_SW_PLAN, and CF_SW_PLAN_STATE).

Run the following query in target database as a MDADM user and validate that at least one record is displayed with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_SWP_AB_ST' and instance_num=1;
I07

Switch Plan Begin State Key(s)

This is parameter value is used to identify the initial states of a Switch Plan. This is used by the Switch Plan and switch plan state facts (CF_SW_PLAN, and CF_SW_PLAN_STATE).

Run the following query in target database as a MDADM user and validate that at least one record is displayed with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_SWP_BG_ST' and instance_num=1;
I08

Switch Plan End State Key(s)

This is parameter value is used to identify the final states of a Switch Plan. This is used by the Switch Plan and switch plan state facts (CF_SW_PLAN, and CF_SW_PLAN_STATE).

Run the following query in target database as a MDADM user and validate that at least one record is displayed with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_SWP_ED_ST' and instance_num=1;
I09 Switch Plan Failed Step Status Key(s) This is parameter value is used to identify the failed steps states of a Switch Plan. This is used by the Switch Plan and switch plan state facts (CF_SW_PLAN, and CF_SW_PLAN_STATE).

Run the following query in target database as a MDADM user and validate that at least one record is displayed with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_SWP_FL_ST' and instance_num=1;
I10

Switch Sheet Planned Category

This parameter value is used to identify the Planned Category code for Switch Sheets.

Run the following query in target database as a MDADM user and validate that at least one record is displayed with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_SW_SHT_PLAN_CAT' and instance_num=1;
I11

Event Status

This is parameter contains a list of excluded Event Status values. Those event status values specified in the parameters are excluded from the extract.

This is not a mandatory parameter. If this parameter is configured, the following query should display at least one record with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_EVENT_STATUS' and instance_num=1;
I12

Segmentation Event Status

This is parameter contains a list of Event Status values based on which data will be loaded into Switch Plan Segmentation Fact (CF_SW_PLAN_SEG).

Run the following query in target database as a MDADM user and validate at least one record is displayed with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_EVENT_STATUS_SEG' and instance_num=1;
I13

Account Type

This is parameter contains a list of excluded Account Type values. Those Account Type values specified in the parameters are excluded from the extract.

This is not a mandatory parameter. If this parameter is configured the following query should display at least one record with not null parameter value.

Select * from b1_job_parm where prod_flg='NMS' and parm_cd='B1_ACCOUNT_TYPE' and instance_num=1;
I14

Outage Duration Buckets

Outage duration bucket configuration defines the bucket ranges used for classifying each outage based on how long it took before the outage was addressed and closed. The configured data is extracted into the Outage Duration dimension (CD_OUTG_DUR).

Run the following query in target database as an MDADM user and validate the following:

select * from b1_range_lookup where prod_flg='NMS' and range_cd = 'N1-OutageDuration';
  1. At least one bucket has the start range as 0.
  2. At least one bucket has the end range as 99999.
  3. No gaps or overlaps are allowed within the bucket ranges.
I15

Estimated Restoration Duration Buckets

Estimated restoration duration bucket configuration defines the bucket ranges used for classifying outages based on its estimated restoration time. The configured data is extracted into the Estimated Restoration Duration dimension (CD_EST_RST_DUR).

Run the following query in target database as an MDADM user and validate the following:

select * from b1_range_lookup where prod_flg='NMS' and range_cd = 'N1-EstRstDuration';
  1. One bucket should have the start range as 0.
  2. One bucket should have the end range as 99999.
  3. No gaps or overlaps are allowed within the bucket ranges.
I16

Duration Deviation Buckets

Duration deviation bucket configuration defines the bucket ranges used for classifying an outage based on how much its actual restoration time deviated from its estimated restoration time. The configured data is extracted into the Duration Deviation dimension (CD_DURATION_DEVIATION ).

Run the following query in target database as an MDADM user and validate the following:

select * from b1_range_lookup where prod_flg='NMS' and range_cd = 'N1-DurationDeviation';
  1. One bucket should have the start range as 0.
  2. One bucket should have the end range as 99999.
  3. No gaps or overlaps are allowed within the bucket ranges.
I17

Customers Out Buckets

Customers Out bucket configuration defines the bucket range for classifying the number of customers that were out during an outage event. The configured data is extracted into the Customers Out bucket dimension (CD_CUST_OUT).

Run the following query in target database as an MDADM user and validate the following:

select * from b1_range_lookup where prod_flg='NMS' and range_cd = 'N1-CustomersOut';
  1. One bucket should have the start range as 0.
  2. One bucket should have the end range as 99999.
  3. No gaps or overlaps are allowed within the bucket ranges.