CMD - BBU High-Level Design

1. BBU Portal in CMD Portal

The CMD portal will be used to upload the BBU files as front end UI and also to track the progress of each job. Below are 2 major activities which user will be doing from UI as part of BBU functionality.

1. Upload the BBU template

   1. The users will be able to select the type of bulk update and provide any snow ticket ID for their reference.

   2. They will be given a download button to download an empty excel template based on what they have chosen.

   3. The user needs to modify the excel file and add their preferred records and then drag and drop in the CMD Portal to upload. Subsequently, this will hit a CMD backend service which will do all the processing.

   4. Once the upload is done successfully they will be greeted with a message and will be redirected to the “BBU Audit history” page.

2. BBU Audit history (status of BBU, Download Error file)
   1. There will be a page in the CMD Portal where users will be able to see all the Bulk updates which are made in the past, what is the status of them, and also what was uploaded, when and by whom, will also be shown.
   2. If any errors will be present then the user will get a download button to download the failed records excel file.
   3. The rejected records will be corrected and uploaded as new BBU job.

The above is not the final UI. The presentation & UX might change as per the backend implementation and response later based on business requirement.

2. BBU High-Level Design

Below diagram shows the high level BBU functional and technical design and the integration between CMD portal, BBU Backend service, IngestAPI and the Database.

3. BBU Technical flow diagram

The below flow diagram shows the end to end technical flow of BBU functionality. User will log into portal and and try to upload the data filled template to process the data into system. The templates are predefined with structure and data should be filled as mentioned in the template. If the file structure/format is wrong, validation message will be thrown and processing will be stopped.

If file format is correct, data will be processed in batches with batch size 1k records. While processing 1K record, the IngestAPI BBU service will be called with X number of records to process. The response from the BBU service will be captured and logged into the audit table.

While processing the data, if any systems failure happens the overall BBU job status will be logged as in-complete and user can download the error file.

During any job processing, if any records are failed with validation errors, such validations will be captured and will continue processing with next record. In this case the over BBU job status will be logged as complete. User can download the error file to verify and fix any data issues.

4. Database Tables

As part BBU functionality implementation, we need few database tables to keep track of the job status, records status and the BBU templates information.

Below are 3 tables used in BBU functionality. All these tables are created in MDM_SMDSMD schema.

1. bbu_template_info

This table is used to store all BBU template related information as reference which will help in validating the file or template structure. The number records in this table is equal to number BBU templates.

Attribute Name	Data Type	Sample Values
bbu_template_type	VARCHAR(50) NOT NULL	BLK_UPDATE_NAME_ADDRESSBLK_CUST_TAXNUM_REFNUMBLK_UPDATE_CUST_STATUSBLK_CUST_CONTACTBLK_Contact_Life_Cycle_StatusBLK_CUST_SEGBLK_CUST_CBUBLK_CUST_RELATIONSHIP
template_name	VARCHAR(50) NOT NULL	Name of the template
template_description	VARCHAR(500) NOT NULL	Description of the template and purpose of the template.
template_header	VARCHAR(5000) NOT NULL	Provide all header information in the respective template. This will help in validating the template structure.

2. bbu_history

This table is used to track each and every BBU job status. What is the template uploaded, how many records in the batch and how many are success and how many are failed and overall status of the job.

Attribute Name	Data Type	Sample Values
bbu_id	BIGSERIAL	Auto generated sequence number.
bbu_template_type	VARCHAR(50)	Template type name, referenced from bbu_template_info.bbu_template_type
uploaded_by	VARCHAR(50)	User details who uploaded the template
status	VARCHAR(20)	BBU Job statusUPLOADEDIN-PROGRESSCOMPLETED IN-COMPLETED FAILED
total_records	BIGINT	Total number of records uploaded in a particular BBU job.
failed_records	BIGINT	Total number of records failed to process in a particular BBU job.
job_start_time	timestamptz	What time BBU job started
job_end_time	timestamptz	What time BBU job completed
snow_ticket_id	VARCHAR(50)	SNOW ticket id against with the BBU template is uploaded.
status_details	VARCHAR(200)	Details of the job status, more details in case of failure

3. bbu_error_history

This table is used to track the error details of a record which got failed because of validations. This table tracks all failed records against a BBU job along with all error details.

Attribute Name	Data Type	Sample Values
bbu_error_history_id	BIGSERIAL	Auto generated sequence number.
bbu_id	BIGINT	bbu id under which the record is loaded. This is reference from bbu_history.bbu_id
bbu_template_type	VARCHAR(50)	Template type name, referenced from bbu_template_info.bbu_template_type
bbu_record_sequence_number	BIGINT	This is sequence number from the template
bbu_record	JSONB	This is complete record in JSON format
error_field	VARCHAR(50)	Field on which the validation failed
error_details	JSONB	The actual error/validation message

Tables with sample data is also attached here.