BI Financial DBDIFF Analysis Issue,

BI Financial DBDIFF Analysis Issue,

Core formula: DB --> ETL --> DW vs REF file

 

Cause of error:

1. DB/DW Connection Issue

2. SQL Issue

3. Product Bug

4, REF file not updated

 

Physical requirement analysis:

1. Source DB: EBS, PSFT, Fusion... DB Instance

2. DW: Ignore the storage form of the data source and store the data in the same form.

 

Logic requirement analysis:

1. ETL process: The data stored in Source DB will be extracted, transformed, andloaded to DW.

 

Key points:

1. Each record in DW can be found in Source DB tables. No matter it isoriginal data or derived data.

2. If it is original data, we need to know which table and which columnin Source DB it comes from.

3. If it is derived data, we need to know the associated record with itin Source DB. In another words, how does this data derive from Source DB byETL.

 

Error analysis:

1. DB/DW Connection Issue: (omitted );

2. SQL Issue: (omitted );

3. Product Bug: (in two types)

Type 1: when the number of rows output by the OUT file is equal to the number of rows displayed by the REF file (num (out) = num (ref), but some fields are different

Definition: Whether the field is originaldata or derived data, all the data converted from Source DB to DW through the ETL process, and the converted data is not equal to the data in the DB, however, if the data in the DB is equal to the REF file, this is a Product Bug.

Example: 1. original data:

If the value of a field in DW is 0 and the value of REF file is null, the corresponding field in DB is null through ETLMAPPING. In this case, the core formula is as follows:

Null --> ETL --> 0 vs null. Obviously, this is an ETL error, that is, a ProductBug.

2. derived data:

If the value of a field in DW is 45.2, and the value of this field is 45.3 in REF file, you can see through ETLMAPPING that the value of this field is obtained by adding the values of the two Source databases. e. (20.1 + 25.2). After calculation, the value displayed in the DB is 45.3. In this case, the core formula is as follows:

45.3 --> ETL --> 45.2 vs 45.3. Obviously, this is an ETL error, that is, the ProductBug.

Type 2: when the number of output rows in the OUT file is not the same as that in the REF file, and the number of output rows in the OUTfile is smaller than that in the REF file (num (out) <num (ref), and the output row of the OUT file can be found in the REF file.

Definition:If the ETL Load Plan is not successfully executed, the records converted to DW are missing. This is a Product Bug.

Example:If the number of rows output by DW is 4, and the number of rows displayed by REF file is 8, and the four records output by DW can be found in REFfile, this is in line with num (out) <num (ref). It is a Product Bug because the ETL Load Plan is not successfully executed.

4, REF file not updated: (in two types)

Type 1: when the number of rows output by the OUT file is equal to the number of rows displayed by the REF file (num (out) = num (ref), but some fields are different

Definition: No matter whether the field is originaldata or derived data, all the data converted from Source DB to DW through the ETL process, and the converted data and the data in the DB, etc, however, the data in DB/DW is not equal to the REF file, so this is a problem of REF file not updated.

Example: 1. original data:

If the value of a field in DW is 0 and the value of REF file is null, the corresponding field in DB is 0 through ETLMAPPING. In this case, the core formula is as follows:

0 --> ETL --> 0 vs null. This indicates that ETL is normal, that is, DBUpdated or REF file not updated.

2. derived data:

If the value of a field in DW is 45.2, and the value of this field is 45.3 in REF file, you can see through ETLMAPPING that the value of this field is obtained by adding the values of the two Source databases. e. (20.1 + 25.1). After calculation, the value displayed in the DB is 45.2. In this case, the core formula is as follows:

45.2 --> ETL --> 45.2 vs 45.3. This indicates that there is no problem with ETL, and REF file not updated.

Type 2: when the number of output rows in the OUT file is not the same as that in the REF file, and the number of output rows in the OUTfile is greater than that in the REF file (num (out)> num (ref), and the rows displayed in the REF file can be found in the OUT file (only applicable to INCR. If this type occurs in FULL, it is also summarized into the product bug)

Definition:If the number of rows output by the OUT file is greater than the number of rows displayed by the REF File, the REF update may lag only in the case of increments. Therefore, this is a REF problem, while FULL does not, if yes, it is a Product Bug.

Example:In INCR, if the number of DW output rows is 10, and the number of rows displayed in REF file is 3, and the 3 rows in REF file can be found in 10 OUT files, this is the lag of REF updates.

 

 

Troubleshooting:

1. Without etl mapping, you cannot determine which records of data in DW are obtained from Source DB.