Processing Sequence
When interfacing data directly into the FACT/Dim tables, the DIM tables must be processed ahead of the FACT table with the DIM Companies table processed first. This ensures that any bank supplied DIMs changes are in place before the FACT table is processed which may result in default DIM records being created.
Common Processing
Each CSV file can be a maximum size of 20mb, this physical size may be revised after performance testing has completed. A maximum physical size is required to prevent performance issues, this limitation may mean that file content will need to be split.
If a CSV file field is presented that is larger than the target database column size then the CSV file field will be trimmed and the leftmost characters used to fill the column.
Due to the complexities of consolidation a single company limitation has been imposed in the initial release of the system. This means that data can only be provided for a single company in all CSV files.
Default Values
None of the FACT/DIM tables support the use of NULL as column values, this is to prevent data integrity issues. If columns are written to tables and no value is supplied then a default value of ‘***’ is written to the column. This then allows grouping to occur using that column value to accumulate totals.
Care must be taken when interfacing directly to the FACT/DIM tables to ensure that null values are eliminated and transformed into ‘***’ before writing the data to the table.
Where data is added automatically to DIM tables to maintain data integrity then a key code of ‘**’ or ‘***’ is used with a description of ‘** autogenerated’ with the batch reference.
This processing is built into the interface of CSV files to staging tables to transformation into the FACT/DIM tables.
FACT/DIM Relationships
The FACT Measures table is linked to the DIM lookup tables using keyfields. If data is provided in the FACT Measures that does not have a lookup decode then the system will automatically add a default entry to the appropriate lookup table. The description of these defaults can then be modified using the Web Client and then presented to the system as part of the next interface.
Company Table Validation
The initial release of the system supports a single company, therefore, the company table can only have a single record.
If an entry is presented for a company and there is no existing company then that company will be created as long as there is not already a company record. If there is then the CSV file will be rejected.
If the CSV file is presented as empty then the CSV file will not be processed.
Cost Centre Table Validation
If an entry is presented for a company and there is no existing company then that company will be created as long as there is not already a company record. If there is then the CSV file will be rejected.
Company code and Cost Centre code fields must be provided or the CSV file will be rejected. All other fields are optional and will default.
If the CSV file is presented as empty then the cost centre table will be cleared.
Branches Table Validation
If an entry is presented for a company and there is no existing company then that company will be created as long as there is not already a company record. If there is then the CSV file will be rejected.
Company code and Branch code fields must be provided or the CSV file will be rejected. All other fields are optional and will default.
If the CSV file is presented as empty then the CSV file will be rejected.
Currency Table Validation
If an entry is presented for a company and there is no existing company then that company will be created as long as there is not already a company record. If there is then the CSV file will be rejected.
Company code and Currency code fields must be provided or the CSV file will be rejected. All other fields are optional and will default.
If the CSV file is presented as empty then the CSV file will be rejected.
COA Table Validation
If an entry is presented for a company and there is no existing company then that company will be created as long as there is not already a company record. If there is then the CSV file will be rejected.
Company code, Bank code and Bank BI code fields must be provided or the CSV file will be rejected. All other fields are optional and will default.
If an entry is presented for a Bank Code that does not provide a valid Bank BI Code then the value will be defaulted to #99999999 (unclassified)
If the CSV file is presented as empty then the CSV file will be rejected.
Measures Table Validation
If an entry is presented for a company and there is no existing company then that company will be created as long as there is not already a company record. If there is then the CSV file will be rejected.
If an entry is presented for a branch and there is no existing branch then that branch will be created.
If an entry is presented for a cost centre and there is no existing cost centre then that cost centre will be created.
If an entry is presented for a currency and there is no existing currency then that currency will be created.
If the CSV file is presented as empty then the CSV file will be rejected.
The Balance in local currency and in actual currency are mandatory fields, zero balance values are allowed.
If the Average Balance in local currency and in actual currency are empty then the values will be defaulted from the Balance fields, zero balance values are allowed.
If the Risk Weighted Balance in local currency and in actual currency are empty then the values will be defaulted from the Balance fields, zero balance values are allowed.
The interface from the banking system can present Measures data in sets. Existing data is first dropped from the system tables where it already exists in the interface table using the following key columns:
This means that sets of data can be interfaced in a phased manner instead of presenting all data at the same time. It also means that when data is presented in a set that all of that data set must be presented. For example,
If data is interfaced in a set that contains:
Then all existing data that matches that criteria will be deleted before the set is interfaced. Later F2 level data could then be applied without dropping the F1 level data already applied.
1st Level data must be presented before 2nd Level data and only one Level type per CSV file is permitted. So F1/C1/B1/E1 data must be interfaced before the detailed F2/C2/B2/E2 data can be interfaced.
Balance Source is validated as being FINANCE/CUSTOMER/BUSINESS/EMPLOYEE.
Level is validated as being F1/C1/B1/E1 or F2/C2/B2/E2.
Report Type is validated where Level is F1 or F2 then BS/PL, where Level is C1 then C1, C2 then C2, B1 then B1, B2 then B2, E1 then E1, E2 then E2.
Balance Type is validated as being ACTUAL//ADJUSTMENT/BUDGET/PEER1/PEER2/PEER3/PEER4/PEER5/FORECAST/PLAN/IFRS
For more information about BankBI please visit our website at bankbi.com
Comments
0 comments
Please sign in to leave a comment.