BI Financial Dbdiff Analysis Issue

Core formula:----and 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 Requirements Analysis:

1, Source Db:ebs, psft, Fusion ... DB Instance

2, DW: Ignores the storage form of the data source and stores the data in the same form.

Logic Requirements Analysis:

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

Key points:

1, each record in DW can is found in Source DB tables. No matter it isoriginal data or derived data.

2, If It is original data, we need the 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, what does this data derive from Source DB Byetl.

Error Analysis:

1, Db/dw Connection Issue: (slightly);

2, SQL Issue: (slightly);

3, Product Bug: (Divided into two types)

Type one: the number of rows that are output by the out file is equal to that of the REF file (num (out) ==num (REF)), but some fields are different

definition : Regardless of whether the field is originaldata or derived data, the source DB is converted into the DW by the ETL process, and the converted data is not equal to the data in the DB, but the data in db is equal to the ref file. So this is a product Bug.

example : 1. Original data:

If one of the field values in the DW is 0, and the ref file shows null, the corresponding fields in the DB are seen by etlmapping Null. This situation is represented by the core formula:

--and ETL--0 vs Null. Obviously, this is an ETL error, that is productbug.

2. Derived data:

If the value of a field in the DW is 45.2 and the ref file is displayed as 45.3, the value of this field is etlmapping by the addition of the value in two source db, i.e. (20.1+25.2), in the calculation, the value shown in DB is 45.3. This situation is represented by the core formula:

45.3-to-ETL-to 45.2 vs 45.3. Obviously, this is also an ETL error, that is productbug.

Type two: the number of rows that are output by the out file and ref file are not equal, and the number of rows that the outfile output is less than the number of rows displayed by the ref file (num (out) <num (ref)), and out The rows of the file output can be found in ref file

Definition: If the ETL Load Plan fails to execute successfully, there will be a missing record in the conversion to the DW, which is a product Bug.

Example: if the DW outputs a number of rows of 4, and the ref file displays 8 rows, and the 4 records that are output in the DW can be found in the Reffile 8 records, then this is the num (out) <num (ref), which is the ETL Load Plan did not execute successfully and is therefore a product Bug.

4, REF File not updated: (divided into two types)

Type one: the number of rows that are output by the out file is equal to that of the REF file (num (out) ==num (REF)), but some fields are different

definition : Whether the field is originaldata or derived data, which is converted into DW by the source DB through the ETL process, and the converted data and the data in the DB want to wait, but the data in DB/DW is not equal to the ref file , then this is a problem with ref file not updated.

example : 1. Original data:

If a field in the DW has a value of 0, and the ref file shows null, the corresponding field in the DB is shown as 0 by etlmapping. This situation is represented by the core formula:

0--and ETL--0 vs Null. This shows that ETL is no problem, that is, dbupdated or REF file not updated.

2. Derived data:

If the value of a field in the DW is 45.2 and the ref file is displayed as 45.3, the value of this field is etlmapping by the addition of the value in two source db, i.e. (20.1+25.1), in the calculation, the value shown in DB is 45.2. This situation is represented by the core formula:

45.2-to-ETL-to 45.2 vs 45.3. This indicates that ETL is not a problem, REF file not updated.

    type two: when the number of rows output by the out file is not the same as that of the ref file, and the number of rows that the outfile outputs is greater than the number of rows displayed by the ref file (num (out) >num (ref)), and the rows displayed by ref file can be found incr full product bug )

    definition: out file The number of rows output is greater than Span style= "Color:black" > REF File The number of rows displayed, which can occur only in the case of increments ref updated hysteresis, therefore is ref full product Bug

Example: in INCR, if the DW outputs a number of rows, and the REF File displays a number of 3rows, and the 3 bars in REF file can be Out file ten found in the bar, then it is REF the lag problem of the update.

Analyze the problematic points:

1, without ETL MAPPING, it is not possible to determine which data in the DW is obtained from which records in Source DB.

BI Financial Dbdiff Analysis Issue