Introduction

The stored data that the platform operates on is maintained in individual databases linked to each platform service. Beyond the complexity of having multiple data sources, the data in these databases is in a format designed and optimized for operational platform use, not reporting. This makes it very difficult for a Developer wanting to extract data for use by their application or for a Programme Manager to extract data related to the use and performance of owned programmes.

The data warehouse is a service that maintains a version of platform data in a single database optimized for business use by both the application and by other roles in the OPE. This service listens to events broadcast by other platform services (Extract), transforms the event data into a more report- and business-friendly format (Transform) and inserts this data into the data warehouse database (Load).

The data warehouse can be accessed via standard SQL mechanisms. Additionally, users can also make use of the Reporting Framework to access data in more organized manner. The reporting framework offers two different ways of accessing the data warehouse: a pull mechanism via the Data Warehouse API and a push mechanism via the Data Warehouse broadcast events.

Data Warehouse Design

The data warehouse is part of the OPE framework, and is made available (along with the other OPE services) on each partition for each programme manager. All applications developed on that same partition share the same data warehouse. The data warehouse is a repository for business data used to facilitate data querying and reporting, without disrupting operational processes. The data warehouse is designed to satisfy business requirements, and the data model segregates data into quantifiable business processes, with several descriptive attributes for each process. This enables a significant amount of data manipulation that would otherwise be too complex in an operational environment. This model can be used in business intelligence efforts to support data-driven management decisions.

Data Warehouse Overview and Main Components

Figure 1

As per the above figure, the data warehouse service gets data in real time via the events broadcast by the relevant platform paylets and services. The event data is passed through an ETL process (Extract, Transform, Load) which collates the data and transforms it into the formats required by the Basic Data Store tables and the Business Views. Once transformed, the data is pushed to the data warehouse database.

The resulting data can either be accessed directly through standard SQL methods or else can be accessed more conveniently either via API access or by listening to event notifications.

Basic Data Store

The basic data store of the data warehouse includes a number of data tables optimized for business use (for example through de-normalization). The entities are still quite close to the platform paylets and services that the original data comes from. The figure below identifies the data entities and the relations between them. The following sections go through the details of each entity.

Figure 2

API access to the Data Warehouse is still under development. Instead, we provide direct MySQL database querying which works as follows:

  • Registering on the dev-portal automatically creates a MySQL user with SELECT rights to the Data Warehouse for that user. Both the Username and the Password of this user are the tenantId, which is the first numeric value before the | in the ProgrammeKey*.
  • This user will be created with an expired password, so you will need to log in to the server with a MySQL client that supports this.
  • Connect with the following details:
    • URL: dwh.stg.openpayments.cloud
    • Port: 3306
    • Username: <tenantId>
    • Initial Password: <tenantId>
  • After connecting with the initial password, set it to a new one with the MySQL command:
    • SET PASSWORD FOR '<tenantId>'@'%' = PASSWORD('<newPass>'); (naturally after changing the <tenantId> and <newPass> placeholders).
  • You should now have full READ access to your own Data Warehouse, which is the table named <tenantId>_collector.
  • If you run into any issues during this process and need help, do not hesitate to contact us on Slack.

* See What is my ProgrammeKey? section in FAQs if you don't know what your ProgrammeKey is.

Corporate Identity Table

This data table maintains the information related to corporate identities in the platform, including the programmes that the corporate identities belong to and the applications that the programmes are a deployment of.

Field Description
Corporate Id Identifier of the Corporate Identity in IPS. This is the primary key for this table.
Corporate Name Business Name.
Corporate State State of this identity. One of {ACTIVE}.
Corporate Creation Date Date when identity was registered.
Programme Id Identifier of the Programme to which this corporate identity belongs.
Programme Code Name of the Programme formatted to enable searching/filtering.
Programme Name Name of Programme.
Programme State State of Programme. One of {PENDING_CREATION, ACTIVE, PENDING_APPROVAL, APPROVED, REJECTED, PUSHING_TO_PRODUCTION}
Programme Activation Date Date when Programme is set to ACTIVE.
Application Id Identifier of the Application that the Programme is a deployment of.
Application Name Name of the Application.
Application Last Approved Major Version Application major version number.
Application Owner Id Identifier of the application developer.
Application Owner Type Type of owner, at the time DEVELOPER.
Application Creation Date Date when the Application was registered in the Application Store.

Table 1 – Corporate Identity table

Managed Account Tables

Two tables maintain the information related to managed accounts, including a daily snapshot of the managed account balances: Managed Account and Managed Account Balances.

Field Description
Managed Account Id Identifier of the Managed Account in IPS.
Programme Id Identifier of the Programme to which this managed account belongs.
Profile Name Name of the profile that this managed account instance is tied to.
Owner Id The corporate identity owning the account.
Corporate Name Name of the corporate identity owning this managed account.
Creation Date Date when this managed account was created.
Friendly Name Friendly name for this managed account.
State State of this managed account. One of {CREATED, DESTROYED}.
Destroy Date Date when this managed account was destroyed.
Issuing Provider Issuer tied to this managed account for treasury purposes.
Currency Currency of this managed account.

Table 2 – Managed Account table

Field Description
Managed Account Id Identifier of the Managed Account in IPS.
Actual Balance IPS local balance for the managed account.
Available Balance IPS cached view of the remote balance for the managed account.
Reserved Balance Portion of the balance of the managed account held in suspense waiting for an action to be confirmed. Reservation can be confirmed (debited) or cancelled/expired (funds released).
Balance As At Date/Time The table contains a daily entry of the balances for each managed account (updated during the same day upon updates and registered daily if there is no activity). This field identifies the date and time of this particular balance entry.
Is Last Balance Convenience field identifying the latest updated balance entry for a particular managed account (only True for one entry for each managed account).

Table 3 – Managed Account Balances table

Managed Card Tables

Two tables maintain the information related to managed cards, including a daily snapshot of the managed card balances: Managed Card and Managed Card Balances.

Field Description
Managed Card Id Identifier of the Managed Card in IPS.
Programme Id Identifier of the Programme to which this managed card belongs.
Profile Name Name of the profile that this managed card instance is tied to.
Owner Id The corporate identity owning the card.
Corporate Nam Name of the corporate identity owning this managed card.
Creation Date Date when this managed card was created.
Friendly Name Friendly name for this managed card.
Card Level Classification Whether this is a Consumer or Corporate card.
State State of this managed account. One of {PRE_ACTIVE, ACTIVE, PRE_DESTROYED, DESTROYED}
Card Type Identifies whether this is a Plastic or a Virtual card.
Renewal Type Determine the behaviour upon expiry of the card. One of {AUTO_RENEW, ASK, NO_RENEW}.
Destroy Type One of {NOT_DESTROYED, SYSTEM, OWNER, LIMITED_SPENDS, OPERATOR}.
Destroy Date Date when this managed card was destroyed.
Card Brand Brand of card i.e. Visa, MasterCard or others.
Issuing Provider Issuer for this card.
Processing Provider Issuing Processor for this card.
First Six Digits First six digits of card number (card BIN).
Last Four Digits Last four digits of card number.
Max Number of Loads Maximum number of loads allowed on card (typically identifies whether this is a single-load card or an unlimited-loads card)
Max Number of Spends Maximum number of spends allowed on the card (typically identifies whether this is a single-spend card – card destroyed upon receiving first authorization – or an unlimited-spends card)
Currency Currency of this managed card.

Table 4 – Managed Card table

Field Description
Managed Card Id Identifier of the Managed Card in IPS.
Actual Balance IPS local balance for the managed card.
Available Balance IPS cached view of the remote balance for the managed card.
Reserved Balance Portion of the balance of the managed card held in suspense waiting for an action to be confirmed. Reservation can be confirmed (debited) or cancelled/expired (funds released).
Balance As At Date/Time The table contains a daily entry of the balances for each managed card (updated during the same day upon updates and registered daily if there is no activity). This field identifies the date and time of this particular balance entry.
Is Last Balance Convenience field identifying the latest updated balance entry for a particular managed card (only True for one entry for each managed card).

Table 5 – Managed Card Balances table

Authorization and Settlement Details Tables

Managed Card Authorization data and Settlement data is held in two individual tables: Authorization Details and Settlement Details.

Field Description
Authorization Details Id Identifier of the auth record in IPS.
Provider Auth Id Provider’s identifier of the auth.
Provider Transaction Id Provider’s identifier of the auth transaction.
Related Provider Auth Id In case of a related auth, provider’s identifier of that auth.
Related Provider Transaction Id In case of a related auth, provider’s transaction identifier of that auth.
Instrument Id Identifier of the instrument (managed card) on which this auth was received.
Authorization Type Type of auth. One of {PURCHASE, EXPIRY, DECLINED, MERCHANT_VOID, REVERSAL}.
Transaction Type Type of transaction. One of {SALE_OR_PURCHASE, MAIL_OR_TELEPHONE_ORDER}.
Transaction Date Date and time of the IPS transaction resulting from this auth.
Provider Processing Date Date and time when the provider processed this auth.
Auth Expiry Date Date and time when the auth expires (and is reversed) if not settled.
Transaction Currency Original currency of the purchase authorized on the managed card.
Transaction Amount Original amount of the purchase authorized on the managed card.
Billing Currency Card currency, in which the purchase authorised is billed.
Billing Amount Amount authorised on the managed card, converted to billing currency.
Billing Conversion Rate Conversion rate employed in conversion between the billing and transaction currencies.
Commission Currency Currency of the commission amount.
Commission Amount Amount indicating the charges applicable to the auth.
Padding Currency Currency of the padding amount
Padding Amount Amount reserved in addition to the auth amount to protect from forex rate fluctuations between the auth and the clearing date.
Merchant Id Identifier of the merchant where the purchase was performed.
Merchant Name Name of the merchant where the purchase was performed.
Merchant Category Merchant Category Code of the merchant where the purchase was performed.
Auth Code Authorization Code assigned to this auth.
Acquirer Reference Number Used to reference a transaction when it goes from the merchant bank (acquirer’s bank) through to the cardholder bank (issuer’s bank).
Retrieval Reference Number Document reference number identifying the original source information (ATM acquirer).
Terminal Code Code identifier of the terminal where the purchase was performed.
Terminal Country Country of the terminal where the purchase was performed.
Cardholder Presence Determines if the transaction was performed with the physical presence of the cardholder.
Is Real Time Flag indicating whether the auth was supplied by file or by a realtime feed.

Table 6 – Authorization Details table

Field Description
Settlement Details Id Identifier of the settlement record in IPS.
Provider Settlement Id Provider’s identifier of the settlement.
Provider Transaction Id Provider’s identifier of the settlement transaction.
Related Provider Settlement Id In case of a related settlement, provider’s identifier of that settlement.
Related Provider Auth Id In case of a related settlement, provider’s identifier of that related auth.
Related Provider Auth Transaction Id In case of a related settlement, provider’s transaction identifier of the related auth.
Instrument Id Identifier of the instrument (managed card) on which this settlement is occurring.
Provider Processing Date Date and time when the provider processed this settlement.
Transaction Date Date and time of the IPS transaction resulting from this settlement.
Transaction Currency Original currency of the purchase settled on the managed card.
Transaction Amount Original amount of the purchase settled on the managed card.
Billing Currency Card currency, in which the purchase is billed.
Billing Amount Purchase amount on the managed card, converted to billing currency.
Billing Conversion Rate Conversion rate employed in conversion between the billing and transaction currencies.
Merchant Id Identifier of the merchant where the purchase was performed.
Merchant Transaction Type Transaction type for merchant. One of {SALE_PURCHASE, MAIL_OR_TELEPHONE_ORDER, PURCHASE_REFUND_REVERSAL, ORIGINAL_CREDIT_TRANSACTION_REVERSAL, PURCHASE_REFUND, ORIGINAL_CREDIT_TRANSACTION}.
Merchant Name Name of the merchant where the purchase was performed.
Merchant Category Code Merchant Category Code of the merchant where the purchase was performed.
Auth Code Authorization Code assigned to this settlement.
Reason Code A reply flag given as a response.
Acquirer Reference Number Used to reference a transaction when it goes from the merchant bank (acquirer’s bank) through to the cardholder’s bank (issuer’s bank)
Retrieval Reference Number Document reference number identifying the original source information (ATM acquirer).
Terminal Code Code identifier of the terminal where the purchase was performed.
Terminal Country Country of the terminal where the purchase was performed.
Cardholder Presence Determines if the transaction was performed with the physical presence of the cardholder.
File Name The name of the settlement file containing this settlement entry.

Table 7 – Settlement Details table

Instrument Log and Transaction Fees Tables

The Instrument Log contains the movements on individual instruments as a result of the transactions performed. The Transaction Fees table identifies transactions related to those transactions.

Field Description
Transaction Id Identifier of the transaction record in IPS that this instrument log is a part of.
Transaction Type Type of the transaction. One of {TRANSFER, CREATE_MANAGED_CARD, CREATE_MANAGED_ACCOUNT, CREATE_EXTERNAL_ACCOUNT, AUTHORISATION, SETTLEMENT, DEPOSIT, WITHDRAWAL}.
Related Transaction Id In case of a related transaction, the IPS identifier of that transaction.
Related Transaction Type In case of a related transaction, the type of that transaction. One of {TRANSFER, CREATE_MANAGED_CARD, CREATE_MANAGED_ACCOUNT, CREATE_EXTERNAL_ACCOUNT, AUTHORISATION, SETTLEMENT, DEPOSIT, WITHDRAWAL}.
Transaction Batch Id An identifier of one or a set of instrument instructions, together with transaction details, grouped as a transaction batch.
Transaction Batch Type Nature of the instruction/s grouped in this batch. One of {ADJUSTMENT, QUERY, RESERVATION, SYSTEM}.
Transaction Batch State State of the transaction batch. One of {IN_PROGRESS, COMPLETED, FAILED, COMPENSATION_IN_PROGRESS}.
Transaction Batch Creation Date Date when instruction/s were created.
Transaction Batch Processed Date Date when adjustment/s actually occurred.
Related Transaction Batch Id In case of a related transaction batch, the IPS identifier of that batch.
Related Transaction Batch Type In case of a related transaction batch, the type of that batch. One of {ADJUSTMENT, QUERY, RESERVATION, SYSTEM}.
Instrument Id Identifier of the instrument in IPS that this instrument log entry relates to.
Instrument Type Type of the instrument. One of {MANAGED_CARD, MANAGED_ACCOUNT, EXTERNAL_ACCOUNT}.
Corporate Id Identifier of the corporate identity in IPS that owns the instrument.
Corporate Name Name of the corporate identity in IPS that owns the instrument.
Instruction Type Type of the instrument movement instruction. One of {CREATE, TAKE, PUT, PUT_ACTUAL, TAKE_ACTUAL, TAKE_ACTUAL_ALLOW_NEGATIVE, REVERSE_TAKE, REVERSE_PUT, BLOCK, UNBLOCK, DESTROY, BALANCE_QUERY}.
Transaction Currency Currency of the transaction adjustment.
Transaction Adjustment Amount of the transaction adjustment.
Instrument Currency Currency of the instrument adjustment.
Instrument Adjustment Amount of the instrument adjustment.
Event Published Date Date and time of the published event generated by the instrument adjustment.

Table 8 – Instrument Log table

Field Description
Transaction Id Identifier of the transaction record in IPS that this transaction fee entry relates to.
Transaction Batch Id An identifier of one or a set of instrument instructions, together with transaction details, grouped as a transaction batch. This identifier can be found in the Instrument Log table to fetch further transaction details.
Fee Instrument Id Identifier of the instrument in IPS that the fee relates to.
Fee Instrument Type Type of the instrument from which the fee was deducted. One of {MANAGED_CARD, MANAGED_ACCOUNT, EXTERNAL_ACCOUNT}.
Fee Category Category for this fee. One of {SOURCE, DESTINATION}.
Fee Instrument Currency Currency of the instrument adjustment.
Fee Instrument Adjustment Amount of the instrument adjustment.
Fee Currency Currency of the fee adjustment.
Fee Amount Amount of the fee adjustment.

Table 9 – Transaction Fees table

Business Views

The business views include two views built on top of the basic data store tables. The views represent a next level of business abstraction to make it easier to application developers and programme managers to retrieve the required information.

Two views have been generated identifying the activity on managed accounts and managed cards.

Managed Account Activity View

This data view presents a business view of the activity occurring on managed accounts.

Field Description
Corporate Name Business Name.
Programme Id Identifier of the Programme owning the managed account.
Managed Account Id Identifier of the Managed Account in IPS.
Transaction Id Identifier of the transaction record in IPS.
Transaction Type Type of the transaction. One of {TRANSFER, CREATE_MANAGED_ACCOUNT, DEPOSIT, WITHDRAWAL}.
Transaction Batch State State of the transaction batch. One of {IN_PROGRESS, COMPLETED, FAILED, COMPENSATION_IN_PROGRESS}.
Transaction Info Type of the instrument movement instruction. One of {CREATE, TAKE, PUT, PUT_ACTUAL, TAKE_ACTUAL, TAKE_ACTUAL_ALLOW_NEGATIVE, REVERSE_TAKE, REVERSE_PUT, BLOCK, UNBLOCK, DESTROY, BALANCE_QUERY}.
Transaction Batch Creation Date Date when instruction/s were created.
Transaction Batch Processed Date Date when adjustment/s actually took place.
Transaction Currency Currency of the transaction adjustment.
Transaction Adjustment Amount of the transaction adjustment.
Source Instrument Type Type of the source instrument. One of {MANAGED_CARD, MANAGED_ACCOUNT, EXTERNAL_ACCOUNT}.
Source Instrument Friendly Name Friendly name for the source instrument.
Destination Instrument Type Type of the destination instrument. One of {MANAGED_CARD, MANAGED_ACCOUNT, EXTERNAL_ACCOUNT}.
Destination Instrument Friendly Name Friendly name for the destination instrument.
Participation Type Identifies whether the managed account is the source or the destination of this transaction.
Actual Balance Before Transaction Managed Account actual balance before transaction.
Actual Balance Adjusted Amount Managed Account adjustment amount in account currency.
Actual Balance After Transaction Managed Account actual balance after transaction.
Original Transaction Currency Original currency of the transaction (can be source or destination amount).
Original Transaction Amount Original amount of the transaction (can be source or destination currency).
Participant Currency Currency of the managed account.
Participant Amoun t Managed Account adjustment amount in account currency.
Exchange Rate Exchange rate between source and participant currency.
Non Forex Fee Transaction fee excluding forex markup component.
Forex Flag Whether forex applies to this transaction.
Forex Fee Forex markup component.
Direction Whether this is an original transaction or the reversal of one.

Table 10 – Managed Account Activity view

Managed Card Activity View

This data view presents a business view on the activity occurring on managed cards.

Field Description
Corporate Name Business Name.
Programme Id Identifier of the Programme to which this managed card belongs.
Managed Card Id Identifier of the Managed Card in IPS.
First Six Digits First six digits of card number (card BIN).
Last Four Digits Last four digits of card number.
Card Brand Brand of card i.e. Visa, MasterCard or others.
Profile Name Name of the profile that this managed card instance is tied to.
Transaction Id Identifier of the transaction record in IPS.
Transaction Type Type of the transaction. One of {TRANSFER, CREATE_MANAGED_CARD, AUTHORISATION, SETTLEMENT, DEPOSIT, WITHDRAWAL}.
Transaction Batch State State of the transaction batch. One of {IN_PROGRESS, COMPLETED, FAILED, COMPENSATION_IN_PROGRESS}.
Transaction Info In the case of an authorization transaction, this refers to the type of auth. One of {PURCHASE, EXPIRY, DECLINED, MERCHANT_VOID, REVERSAL}. In the case of a settlement transaction, this refers to the transaction type for merchant. One of {SALE_PURCHASE, MAIL_OR_TELEPHONE_ORDER, PURCHASE_REFUND_REVERSAL, ORIGINAL_CREDIT_TRANSACTION_REVERSAL, PURCHASE_REFUND, ORIGINAL_CREDIT_TRANSACTION}. In the case of any other transaction type, this refers to the type of the instrument movement instruction. One of {CREATE, TAKE, PUT, PUT_ACTUAL, TAKE_ACTUAL, TAKE_ACTUAL_ALLOW_NEGATIVE, REVERSE_TAKE, REVERSE_PUT, BLOCK, UNBLOCK, DESTROY, BALANCE_QUERY}.
Transaction Batch Creation Date Date when instruction/s were created.
Transaction Batch Processed Date Date when adjustment/s actually took place.
Transaction Currency Currency of the transaction adjustment.
Transaction Adjustment Amount of the transaction adjustment.
Source Instrument Type Type of the source instrument. One of {MANAGED_CARD, MANAGED_ACCOUNT, EXTERNAL_ACCOUNT}.
Source Instrument Friendly Name Friendly name for the source instrument.
Destination Instrument Type Type of the destination instrument. One of {MANAGED_CARD, MANAGED_ACCOUNT, EXTERNAL_ACCOUNT}.
Destination Instrument Friendly Name Friendly name for the destination instrument.
Participant Sink Identifies whether the managed card is the source or the destination of this transaction.
Participant Instrument Type Type of the instrument. One of {MANAGED_CARD, MANAGED_ACCOUNT, EXTERNAL_ACCOUNT}.
Participant Instrument Friendly Name Friendly name for this managed card.
Actual Balance Before Transaction Managed Card actual balance before transaction.
Actual Balance Adjusted Amount Managed Card adjustment amount in account currency.
Actual Balance After Transaction Managed Card actual balance after transaction.
Original Transaction Currency Original currency of the transaction (can be source or destination amount).
Original Transaction Amount Original amount of the transaction (can be source or destination currency).
Participant Currency Currency of the managed card.
Participant Amount Managed Card adjustment amount in card currency.
Exchange Rate Exchange rate between source and participant currency.
Non Forex Fee Transaction fee excluding forex markup component.
Forex Flag Whether forex applies to this transaction.
Forex Fee Forex markup component.
Interchange Currency Transaction Fee Currency for the Interchange fee type.
Interchange Amount Transaction Fee Amount for the Interchange fee type.
Provider Processing Date Date and time when the provider processed this settlement.
Merchant Name Name of the merchant where the purchase was performed.
Terminal Country Country of the terminal where the purchase was performed.
Merchant Category Code Merchant Category Code of the merchant where the purchase was performed.
Auth Code Authorization Code assigned to this auth.
Acquirer Reference Number Used to reference a transaction when it goes from the merchant bank (acquirer’s bank) through to the cardholder’s bank (issuer’s bank)
Direction Whether this is an original transaction or the reversal of one.

Table 11 - Managed Card Activity view

Report Framework Design

The main function of the data warehouse is to store the data. The data can be accessed directly through SQL connections using native SQL querying. The report framework provides additional data access mechanisms to allow the application developer and the programme manager to get access to the data warehouse data. Two methods are provided. API Access to the data warehouse data allows the pulling of information from the tables and views (documented earlier) and is easier to integrate with an external application than direct SQL Access. In this case it is at the discretion of the application to make the API calls at the required time and frequency. The second method offered by the report framework uses data warehouse notifications to push out data warehouse data updates. In this case, the application will be registered as a listener to these events and can react to incoming notifications as required by the application. Note that at the time of writing the signatures for the API access and the notifications are not yet finalised and are therefore not yet documented.