Generating reports using summarized data¶
Using the OB_REPORTING_SUMMARIZED_DB
datasource (openbank_ob_reporting_summarizeddb
database) you can generate the
following reports according to the OBIE templates:
Template 1 - Performance and Availability (P&A) (OBIE)¶
ID | Field Name | Table to refer | How to capture data |
---|---|---|---|
1 | Report Date | The respective table you retrieve the date from | Get the year-month-date values from the table |
2 | ASPSP Brand ID | N/A | A fixed value for a bank |
3 | Endpoint ID | ENDPOINT_DETAILS |
Download the SQL script from here to create the ENDPOINT_DETAILS in your MSSQL database. A fixed value. Get the resource from the respective table and map that with the ENDPOINT_ID table. |
4 | Core/Non Core Hours | Either Core or Non-Core | This column should be either CORE or NON-CORE |
5 | Up Time | N/A | Need to get from an external tool |
6 | Planned Downtime | N/A | Value needs to be decided by the bank |
7 | Unplanned Downtime | N/A | Need to get from an external tool |
8 | Median TTLB Response Time | PA_CORE or PA_NON_CORE |
|
9 | Median TTFB Response Time | PA_CORE or PA_NON_CORE |
|
10 | Median Response Payload Size | PA_CORE or PA_NON_CORE |
|
11 | Max Payment Initiations Per Second (PIPS) | PAYMENT_INITIATION_DETAILS |
Get the payment initiations per second per day |
12 | Average TTLB Response Time | PA_CORE or PA_NON_CORE |
Get the AVG_TTLB value from the relevant table per day per time per endpoint |
13 | Average TTFB Response Time | PA_CORE or PA_NON_CORE |
Get the AVG_TTFB value from the relevant table per day per time per endpoint |
14 | Total Number of API calls | PA_CORE or PA_NON_CORE |
Get the TOTAL_API_CALLS value from the relevant table per day per time per endpoint. |
15 | Total TTLB Response Time (Time to Last Byte) | PA_CORE or PA_NON_CORE |
Get the TOTAL_TTLB value from the relevant table per day per time per endpoint |
16 | Total TTFB Response Time (Time to First Byte) | PA_CORE or PA_NON_CORE |
Get the TOTAL_TTFB value from the relevant table per day per time per endpoint |
17 | Total ResponsePayload Size | PA_CORE or PA_NON_CORE |
Get the TOTAL_RESPONSE_PAYLOAD_SIZE value from the relevant table per day per time per endpoint |
18 | Version | PA_CORE or PA_NON_CORE |
Get the API_SPEC_VERSION value from the relevant table per day per time per endpoint |
Template 2 - PSU Authentication & Interaction¶
ID | Field Name | Table to refer | How to capture data |
---|---|---|---|
1 | ReportDate | The respective table you get the date from | Get the year-month-date values from the table |
2 | ASPSP Brand ID | N/A | A fixed value for a bank |
3 | API Type | PSU_AUTHENTICATION_INTERACTION |
Get the REQUEST_TYPE value |
4 | Time C PSU Authentication & Interaction (ms) (OPTIONAL) | PSU_AUTHENTICATION_INTERACTION |
Get the TOTAL_INTERACTION_TIME value |
5 | PSU Authentications & Interactions Volumes (OPTIONAL) | PSU_AUTHENTICATION_INTERACTION |
Get the FLOW_COUNT value |
6 | Version | PSU_AUTHENTICATION_INTERACTION |
Get the API_SPEC_VERSION value |
Template 3 - Response Outliers (OBIE)¶
ID | Field Name | Table to refer | How to capture data |
---|---|---|---|
1 | Report Date | The respective table you get the date from | Get the year-month-date values from the table |
2 | Time | The respective table that you get the time from | Get the hour-minute-second values from the table |
3 | ASPSP Brand ID | N/A | A fixed value for a bank |
4 | Endpoint ID | ENDPOINT_DETAILS |
A fixed value. Get the resource from the respective table and map that with ENDPOINT_ID table |
5 | TTLB Response Time | RESPONSE_OUTLIER |
Get the TTLB value per day per time per endpoint |
6 | TTFB Response Time | RESPONSE_OUTLIER |
Get the TTFB value per day per time per endpoint |
7 | TPP Application ID | RESPONSE_OUTLIER |
Get APP_SOFTWARE_ID value per day per time per endpoint |
8 | Response Payload Size | RESPONSE_OUTLIER |
Get RESPONSE_PAYLOAD_SIZE value per day per time per endpoint |
9 | Version | RESPONSE_OUTLIER |
Get API_SPEC_VERSION value per day per time per endpoint |
Template 4 - Auth Efficacy (OBIE) (WIP)¶
ID | Field Name | Table to refer | How to capture data |
---|---|---|---|
1 | Month | The respective table you get the month from | Get the year-month values from the table |
2 | ASPSP Brand ID | N/A | A fixed value for a bank |
3 | Authentication Type | The respective tables | AUTHENTICATION_APPROACH from each table |
4 | API Type | The respective tables | REQUEST_TYPE from each table |
5 | API Request TPP Channel | The respective tables | PSU_CHANNEL from each table |
6 | ASPSP Authentication Channel | N/A | The value should be web |
7 | Consents Requiring Authentication | CONSENT_REQ_AUTHENTICATION |
Get the TOTAL_CONSENT_REQ_AUTHENTICATION value from the column |
8 | Authentications Attempted by PSUs | AUTHENTICATION_ATTEMPTED |
Get the TOTAL_ATTEMPTS_TO_AUTHENTICATE value from the column |
9 | Authentications Abandoned by PSUs | Get this data by deducting the value of TOTAL_ATTEMPTS_TO_AUTHENTICATE from the value of TOTAL_CONSENT_REQ_AUTHENTICATION |
|
10 | Authentications Succeeded | AUTHENTICATION_SUCCESSFUL |
Get the SUCCESSFUL_AUTHENTICATIONS value from the column |
11 | Authentications Failed | Get this data by deducting the value of SUCCESSFUL_ AUTHENTICATIONS from the value of TOTAL_ATTEMPTS_TO_AUTHENTICATE |
|
12 | Confirmations Required | CONFIRMATION_DETAILS |
Get the AuthorisationRequired count per month |
13 | Confirmations Accepted by PSUs | CONFIRMATION_DETAILS |
Get the Authorised count per month |
14 | Confirmations Rejected by PSUs | CONFIRMATION_DETAILS |
Get the Rejected count per month |
Template 5 - PSU Adoption (OBIE)¶
ID | Field Name | Table to refer | How to capture data |
---|---|---|---|
1 | ReportMonth | The respective table you get the month from | Get the year-month values from the table |
2 | ASPSP Brand ID | N/A | A fixed value for a bank |
3 | Retail/Business PSUs | PSU_DETAILS , FIRST_TIME_USERS |
The retail and business PSU details are available in the core banking system. The provided raw data tables include the PSU_ID s that can be used |
4 | PSUs used AIS Services for the first time | FIRST_TIME_USERS |
The retail and business PSU details are available in the core banking system. The provided raw data tables include the PSU_ID s that used the AIS services for the first time. You can use the PSU_ID and get the PSU count that used the services for the first time in a particular month |
5 | PSUs used PIS Services for the first time | FIRST_TIME_USERS |
The retail and business PSU details are available in the core banking system. The provided raw data tables include the PSU_ID s that used the PIS services for the first time. You can use the PSU_ID and get the PSU count that used the services for the first time in a particular month |
6 | Total PSUs used AIS Services | PSU_DETAILS |
The retail and business PSU details are available in the core banking system. The provided raw data tables include the PSU_ID s that used the AIS services for the first time. You can use the PSU_ID and get the total PSU count that used the services for the first time in a particular month |
7 | Total PSUs used PIS Services | PSU_DETAILS |
The retail and business PSU details are available in the core banking system. The provided raw data tables include the PSU_ID s that used the PIS services for the first time. You can use the PSU_ID and get the total PSU count that used the services for the first time in a particular month |
8 | Unique PSUs used both AIS and PIS Services for the first time | FIRST_TIME_USERS |
The retail and business PSU details are available in the core banking system. The provided raw data tables include the PSU_ID s that used both AIS and PIS services for the first time. You can use the PSU_ID and get the PSU count that used the services for the first time in a particular month |
9 | Total unique PSUs used both AIS and PIS Services | PSU_DETAILS |
The retail and business PSU details are available in the core banking system. The provided raw data tables include the PSU_ID s that used both AIS and PIS services for the first time. You can use the PSU_ID and get the total PSU count that used the services for the first time in a particular month |
10 | Total new PSUs for Online Banking | FIRST_TIME_USERS |
The retail and business PSU details are available in the core banking system. The provided raw data tables include the PSU_ID s that used Online Banking for the first time. You can use the PSU_ID and get the total PSU count that used Online Banking for the first time in a particular month |
11 | Total new PSUs for Mobile Banking | FIRST_TIME_USERS |
The retail and business PSU details are available in the core banking system. The provided raw data tables include the PSU_ID s that used Mobile Banking for the first time. You can use the PSU_ID and get the total PSU count that used Mobile Banking for the first time in a particular month |
12 | Total number of PSUs used Online Banking | PSU_DETAILS |
The retail and business PSU details are available in the core banking system. The provided raw data tables include the PSU_ID s that used Online Banking. You can use the PSU_ID and get the total PSU count that used the Online Banking in a particular month |
13 | Total number of PSUs used Mobile Banking | PSU_DETAILS |
The retail and business PSU details are available in the core banking system. The provided raw data tables include the PSU_ID s that used Mobile Banking. You can use the PSU_ID and get the total PSU count that used the Mobile Banking in a particular month |
14 | Version | PSU_DETAILS |
Get API_SPEC_VERSION value |
Template 6 - Enhanced PSU Adoption (OBIE)¶
ID | Field Name | Table to refer | How to capture data |
---|---|---|---|
1 | ReportMonth | ||
2 | ASPSP Brand ID | ||
3 | Retail/Business PSUs | ||
4 | API Service | ||
5 | Active PSUs | ||
6 | Active SME Businesses | ||
7 | Version |
Template 7 - PSU Consent Adoption (OBIE)¶
Info
The fields Long-lived consents - Consents Requiring Reauthentication
and Long-lived consents - Total Refreshed consents
are only available as a WSO2 Update from WSO2 Open Banking Business Intelligence UK Toolkit Level 1.0.0.1 onwards.
For more information on updating, see Getting WSO2 Updates.
ID | Field Name | Table to refer | How to capture data |
---|---|---|---|
1 | ReportMonth | The respective table you get the month from | Get the year-month values from the table |
2 | ASPSP Brand ID | N/A | A fixed value for a bank |
3 | Retail/Business PSUs | N/A | The retail and business PSU details are available in the core banking system |
4 | API Service | REQUEST_TYPE from any table |
|
5 | One-off Consents | ONE_OFF_CONSENT_ADOPTION |
CONSENT_COUNT per REQUEST_TYPE |
6 | Long-lived consents - Outstanding consents BoP | LONG_LIVED_CONSENT_ADOPTION |
|
7 | Long-lived consents - Revoked consents | LONG_LIVED_CONSENT_ADOPTION |
|
8 | Long-lived consents - Expired consents | LONG_LIVED_CONSENT_ADOPTION |
|
9 | Long-lived consents - Consents Requiring Reauthentication | CONSENT_REQ_REAUTHENTICATION |
This is the total number of unique Long-lived consents, which required 90 days PSU re-authentication. See the note below this table to learn how to capture data. |
10 | Long-lived consents - Renewed consents | LONG_LIVED_CONSENT_ADOPTION |
|
11 | Long-lived consents - New consents | LONG_LIVED_CONSENT_ADOPTION |
|
12 | Long-lived consents - Outstanding consents EoP | LONG_LIVED_CONSENT_ADOPTION |
|
13 | Long-lived consents - Total Refreshed consents | REFRSHED_CONSENT |
This is the total number of unique Long-lived consents that have been refreshed (Re-authenticated). See the note below this table to learn how to capture data. |
14 | Version | LONG_LIVED_CONSENT_ADOPTION |
To capture Long-lived consents - Consents Requiring Re-authentication
:
- Join
ACCOUNTS_RAW_DATA
andAPI_INVOCATION_RAW_DATA
tables. - Get the total count of
CONSENT_ID
asTOTAL_CONSENT_REQ_REAUTH
. - Consider the following conditions:
CONSENT_TYPE
='long-lived'
AUTHORISATION_STATUS
='Authorised'
RE_AUTHORISATION
==false
EXPIRATION_DATE_TIME
>REPORTING_DATE_TIME
REPORTING_DATE_TIME
- 90 days>AUTHORIZED_TIME_DATE
To capture Long-lived consents - Total Refreshed consents
:
- Join
ACCOUNTS_RAW_DATA
andAPI_INVOCATION_RAW_DATA
tables. - Get the total count of
CONSENT_ID
asTOTAL_CONSENT_REQ_REAUTH
. - Consider the following conditions:
CONSENT_TYPE
='long-lived'
AUTHORISATION_STATUS
='Authorised'
RE_AUTHORISATION
==true
EXPIRATION_DATE_TIME
>REPORTING_DATE_TIME
REAUTHORIZED_DATE_TIME
>AUTHORIZED_TIME_DATE
+ 90 days
Template 8 - Payments Adoption (OBIE)¶
ID | Field Name | Table to refer | How to capture data |
---|---|---|---|
1 | ReportMonth | The respective table you get the month from | Get the year-month values from the table |
2 | ASPSP Brand ID | N/A | A fixed value for a bank |
3 | Total AISPs Registered (at 1st of month) | Zero for the starting month and for the following months the cumulative monthly number of AISPs registered of the last month | |
4 | AISP Additions | AISP_VOLUMES |
Get data from the AISP_COUNT column where STATUS ='CREATED' |
5 | Successful single Domestic Payments | BACS_REQUEST_STATUS or CHAPS_REQUEST_STATUS |
Select the REQUEST_COUNT from the relevant payment type table where PAYMENT_TYPE = /domestic-payment and (AUTHORISATION_STATUS = AcceptedSettlementInProgress or AUTHORISATION_STATUS = AcceptedSettlementCompleted ) |
6 | Single Domestic Payments failed for Business Reasons | FAILED_BACS_REQUESTS or FAILED_CHAPS_REQUESTS |
Get the sum of REQUEST_COUNT from the relevant payment type table where STATUS_CODE LIKE '%4%' |
7 | Single Domestic Payments failed for Technical Reasons | FAILED_BACS_REQUESTS or FAILED_CHAPS_REQUESTS |
Get the REQUEST_COUNT from the relevant payment type table where STATUS_CODE = 500 |
8 | Single Domestic Payments Rejected | BACS_REQUEST_STATUS or CHAPS_REQUEST_STATUS |
Select the REQUEST_COUNT from the relevant payment type table where (PAYMENT_TYPE = /domestic-payment-consents or PAYMENT_TYPE = /domestic-payments ) and AUTHORISATION_STATUS = Rejected |
9 | Total payments included in Bulk/Batch files | FILE_PAYMENT |
Get the TOTAL_PAYMENTS for the relevant payment type where LOCAL_INSTRUMENT = Payment Type |
10 | Successful International payments involving currency conversion | BACS_INTERNATIONAL or CHAPSS_INTERNATIONAL |
The currency conversion details are available in the core banking system. The given tables provide all the successful international-payment details with the consent id for each payment type |
11 | Version | Get API_SPEC_VERSION value |
Template 9 - TPP Volumes (OBIE)¶
ID | Field Name | Table to refer | How to capture data |
---|---|---|---|
1 | ReportMonth | The respective table you get the month from | Get the year-month values from the table |
2 | ASPSP Brand ID | N/A | A fixed value for a bank |
3 | Total AISPs Registered (at 1st of month) | Zero for the starting month and for the following months the cumulative monthly number of AISPs registered of the last month | |
4 | AISP Additions | AISP_VOLUMES |
Get data from the AISP_COUNT column where STATUS =CREATED |
5 | AISP Deregistrations | AISP_VOLUMES |
Get data from the AISP_COUNT column where STATUS =DELETED |
6 | Cumulative Monthly number of AISPs | Use the following formula: (Total no of AISPs registered (at 1st of the month) + AISP Additions - AISP deregistrations) | |
7 | Total PISPs Registered (at 1st of month) | Zero for the starting month and for the following months the cumulative monthly number of PISPs registered of last month. | |
8 | PISP Additions | PISP_VOLUMES |
Get data from the PISP_COUNT column where STATUS =CREATED . |
9 | PISP Deregistrations | PISP_VOLUMES |
Get data from the PISP_COUNT column where STATUS =DELETED . |
10 | Cumulative Monthly number of PISPs | Use the following formula: (Total no of PISPs registered (at 1st of month) + PISP Additions - PISP reregistrations) |
Template 10 - Daily Volumes (OBIE)¶
ID | Field Name | Table to refer | How to capture data |
---|---|---|---|
1 | ReportDate | The respective table you get the data from | Get the year-month-date values from the table |
2 | ASPSP Brand ID | N/A | A fixed value for a bank |
3 | Endpoint ID | ENDPOINT_DETAILS |
Download the SQL script from here to create the ENDPOINT_DETAILS in your MSSQL database. This value is a fixed value. you can get the resource from the respective table and map that with ENDPOINT_ID table |
4 | PCA API Calls | ACCOUNT_RAW_DATA_FOR_PCA_BCA , PAYMENT_RAW_DATA_FOR_PCA_BCA , FUNDS_RAW_DATA_FOR_PCA_BCA |
The PCA/BCA details are available in the core banking system. The provided raw data tables include the ACCOUNT_ID of accounts that can be used to identify whether it's a PCA or BCA |
5 | BCA API Calls | ACCOUNT_RAW_DATA_FOR_PCA_BCA , PAYMENT_RAW_DATA_FOR_PCA_BCA , FUNDS_RAW_DATA_FOR_PCA_BCA |
The PCA/BCA details are available in the core banking system. The provided raw data tables include the ACCOUNT_ID of accounts that can be used to identify whether it's a PCA or BCA |
6 | Successful API Calls (200, 201 or 204 codes) | STATUS_CODE_SUMMERY |
The table contains the volume of API calls per status code per endpoint and per date. Filter the STATUS_CODE values of 200, 201 and 204 per endpoint per day and get the sum of those |
7 | Failed API Calls Business Reasons (4xx Codes) | STATUS_CODE_SUMMERY |
The table contains the volume of API calls per status code per endpoint and per date. Filter the |
8 | Failed API Calls Technical Reasons (5xx Codes) | STATUS_CODE_SUMMERY and AUTHORISATION_SUMMERY |
The table contains the volume of API calls per status code per endpoint and per date. Filter the STATUS_CODE values of 5xx per endpoint per day and get the sum of those |
9 | API Calls Rejected Status | STATUS_CODE_SUMMERY |
Get the sum of the above 2 values (Failed API Calls Business Reasons (4xx Codes) and Failed API Calls Technical Reasons (5xx Codes)) and add the value of Rejected statuses per endpoint per day from the AUTHORIZATION_SUMMERY table |
10 | TPPs Calling APIs | DIFFERENT_TPPS |
The count of the DISTINCT_TPPS per elected resource per day. |
11 | API Calls Not Authorised by PSU | AUTHORISATION_SUMMERY |
The count of the Rejected status per endpoint per day. |
12 | API Calls Authorised but Not Consumed | AUTHORISATION_SUMMERY |
Get the count of Authorised status per endpoint per day and deduct the count of AcceptedStatementInProcess status per endpoint per day. |
13 | Multi Auth API Calls Successful | MULTI_AUTH_DETAILS |
Get the count of Authorised statuses against /domestic-payment-consent per day |
14 | Multi Auth API Calls Failed | MULTI_AUTH_DETAILS |
Get the count of Rejected statuses against /domestic-payment-consent per day |
15 | Version | Get API_SPEC_VERSION value |
Template 11 - Additional Metrics (OBIE)¶
- Additional Metrics - Payment Status
- Additional Metrics - Payment SCA Exemptions
- Additional Metrics - Revocation Notifications from ASPSPs
- Additional Metrics - AIS Corporate Account Access (OPTIONAL)
- Additional Metrics - Payment Refunds
- Additional Metrics - 90 Days Re-authentication