Transformation of data management for financial reporting
Transformation of data management for financial reporting
Embedded Files
Investment banks trade currencies on the futures exchange markets for their clients. At the end of each day of trading, the banks produce client reports which show the cumulative Profit and Loss (P/L) for each account. This would be relatively straightforward if all trades were entered on time and with no errors but the banks find they have to issue corrected reports to take into account late trades, ghost trades, and data corrections in rates and quantities. When a corrected report is issued the bank also has to reconcile the original report with the corrected report. This reconciliation activity is very costly and difficult, often involving transfer of data from the transactional systems to expensive data warehouses.
Digital transformation requires that the present systems which deliver this business service are replaced/updated with simpler technical solutions which have a lower cost of development and much reduced ongoing costs. Using a data platform with Time Travel capabilities delivers an estimated 80% saving. Here is why.
The Ld8a Time Travel solution
The Ld8a Time Travel solution
With a Time Travel capability the solution becomes much more straightforward. This scenario shows how 4 simple tables (see above) can produce the required client report. The schema shown represents the extent of what the application developer needs to consider i.e. only the business data. Note that the schema does not have any dates or additional tables for history and audit. This is because time has been separated (or factored away from) the business data and the infrastructure handles the data implication for all event types (eg late trades, corrections). Additionally, the modelling stage of the project is reduced to only requiring a “current view” design. The Ld8a environment then simplifies and extends the capability for the actual reporting in a number of ways:-
the necessary sql to generate the report is simpler
the sql for the client report can be executed for any point in time in the day
the data is stored in such a way that there is no requirement for complex sub queries
reporting does not require large scans of data to determine relevant records for a report. Eg if only 200 records are relevant out of 200 million records, only the necessary 200 records are accessed.
no requirement for an additional, specialised reporting data platform such as a data warehouse
In summary, significant cost savings are realised by using the Ld8a platform in all steps of the digital delivery :-
design is easier
implementation is simpler
data entry is more straightforward
the reporting capability is extended (and made easier)
and from a compute aspect the requirements for storage and compute power are reduced
This is real business transformation.
Summary technical steps for the creation of a Time Travel environment
Summary technical steps for the creation of a Time Travel environment
Connect to the private user area in the Ld8a Sandpit environment
Connect to the _SRC directory within the private user area
Enter and execute code to create current view data model
Log out of _SRC area and log into _USR area
Generation of the Time Travel database environment in the _USR area
Inserting data
Updating data
Deleting data
Reporting
Detailed steps for creation of a Time Travel environment
Detailed steps for creation of a Time Travel environment
Connect to the private user area in the Ld8a Sandpit environment
Using the connection details provided as part of the Ld8a onboarding process, establish a connection via the assigned IP address and named session.
2. Connect to the _SRC directory within the private user area
To load a current view schema the user logs into the _SRC area of their assigned environment.
3. Enter and execute code to create a current view data model
Having entered the _SRC area the user enters and executes code to create the required current view environment. The following code has previously been entered and executed for the “Futures Trading” system
CREATE TABLE CLIENTS
(ID NUMBER(7,0) NOT NULL
,NAME VARCHAR2(30) NOT NULL)
/
CREATE TABLE SECURITIES
(CODE VARCHAR2(12) NOT NULL
,DESCRIPTION VARCHAR2(30) NOT NULL
,MMEOD_PRICE NUMBER(38,9) NOT NULL)
/
CREATE TABLE POSITIONS
(ID NUMBER(7,0) NOT NULL
,CNT_ID NUMBER(7,0) NOT NULL
,SCT_CODE VARCHAR2(12) NOT NULL
)
/
CREATE TABLE TRADES
(ID NUMBER(7,0) NOT NULL
,PSN_ID NUMBER(7,0) NOT NULL
,QTY NUMBER(7) NOT NULL
,EXCHANGE_RATE NUMBER(38,7) NOT NULL
)
/
ALTER TABLE CLIENTS
ADD (CONSTRAINT CNT_PK PRIMARY KEY(ID))
/
ALTER TABLE SECURITIES
ADD (CONSTRAINT SCT_PK PRIMARY KEY (CODE))
/
ALTER TABLE POSITIONS
ADD (CONSTRAINT PSN_PK PRIMARY KEY (ID))
/
ALTER TABLE TRADES
ADD (CONSTRAINT TRD_PK PRIMARY KEY (ID))
/
ALTER TABLE POSITIONS ADD (CONSTRAINT PSN_SCT_FK FOREIGN KEY (SCT_CODE) REFERENCES SECURITIES (CODE))
/
ALTER TABLE POSITIONS ADD (CONSTRAINT PSN_CNT_FK FOREIGN KEY (CNT_ID) REFERENCES CLIENTS (ID))
/
ALTER TABLE TRADES ADD (CONSTRAINT TRD_PSN_FK FOREIGN KEY (PSN_ID) REFERENCES POSITIONS (ID))
/
grant select on CLIENTS to fd_tpl;
grant select on SECURITIES to fd_tpl;
grant select on POSITIONS to fd_tpl;
grant select on TRADES to fd_tpl;
grant select on CLIENTS to fd_usr;
grant select on SECURITIES to fd_usr;
grant select on POSITIONS to fd_usr;
grant select on TRADES to fd_usr;
4. Log out of _SRC area and log into _USR area
The _SRC area holds the current view data model for the required system. The _USR holds the Time Travel generated environment which is accessed for all data entry and reporting.
5. Generate the Time Travel environment in the _USR area
The generation of the Time Travel environment is done by the simple execution of command “exec gen;” as below.
6. Inserting data
To insert data, the required “business time” or “valid time” is specified before the insert command is executed. In the code section below the “valid start time” has been set using the exec dbms_systime.set_valid_time() function with the time being set to a value held by the variable :vtstartclient. Note how the setting of the time is a separate command to the insert command.
7. Updating data
As with inserting data updating data requires “business time” or “valid time” to be specified before the update command is executed. In the code section below the “valid start time” has been set using the exec dbms_systime.set_valid_time() function with the time being set to a value held by the variable :vttrade2. Note how the setting of the time is a separate command to the update command.
8. Deleting data
As with inserting data and updating data, deleting data requires the “business time” or “valid time” to be specified before the delete command is executed. In the code section below the “valid start time” has been set using the exec dbms_systime.set_valid_time() function with the time being set to a value held by the variable :vttrade2. Note how the setting of the time is a separate command to the update command.
Note that deleting data is from a point in “valid time”. Also note that after a deletion the existence of the data is still available for reporting against earlier points in “transaction time” (ie when the data was in existence).
9. Reporting
The points to note about reporting are:-
the select statements are straightforward to write and do not contain clauses for “time”
as with data inserts and updates, the required point in time is set prior to the execution of the select statement
the same select statement can be used for any point in time
both “valid time” and “transaction time” can be set for all reporting
time series reporting can easily be created
In the following sample reports, data is being drawn from 3 tables (trades, securities and positions) with the data values as of a specific “transaction times” and specific “valid times”. The purpose of the following screen outputs is to show that the sql to produce the reports is straightforward and can be pointed to provide an output for any points in time.
Importantly, the “end of day report” becomes “any time of day” report as BTDaaS supports access to the business data for any second or micro second in time!
Same report below but with different “valid time” and “transaction time” set
Insulate from
Insulate from
Complexity
Complexity
The normal approach for solutions providing compliance and end of day reporting involves complex, resource intensive solution development. This is because tackling the challenges of data in the context of time puts developers in the weeds of coding. The ideal alternative, which is now a reality, is to insulate the developer from this through an infrastructure which autonomously generates the code which would otherwise have to be written.
Elevate to
Elevate to
Current View
Current View
The ideal situation for a developer is to work at a level where the main focus is on the business logic. The value of focusing on business logic is that it delivers improvements to competitive advantage and intellectual property. By elevating the developer out of the weeds of "technical challenges", the developer is now able to best maximise their talents in the areas which add most business value.
Page updated
Google Sites
Report abuse