Capital Markets Forex Trades Reporting
A few years ago I consulted for an origanization to design and build a foreign exchange trade reporting environment.
One of the aspects of the project was to document the technical components. This is a web write up of the core parts of that technical document.
The systems:
The trading system is a Calypso ‘Bank in a Box’ environment.
The account balances system is Misys Equation based on IBM AS400.
There are two CRM systems – SalesLogix and Microsoft Dynamics.
Phase 1
Review existing report methods and query extract processes from Calypso.
Review the existing Cognos reports through the Framework Manager.
Cognos and the existing report methods and queries are used to start to build a picture of the end user requirements and will be replaced by phase 1 and by extension phase 2.
Build a Business Objects Universe connecting to the Calypso application production database. Reporting is limited and carefully controlled in this phase, so as not to affect system performance.
The Calypso Business Objects Universe
Phase 2
Book time with representatives from the front office traders, the back office accountants and the settlements department to gain allowed a clearer understanding of their requirements .
No data aggregation is required, there isn’t a need to follow the design process for snapshot fact tables.
The database will be purged nightly with a full reload of transaction (trade level) grain data.
To help design and build the reporting database, a data warehouse bus matrix of business processes and shared dimensions is created for the core data tables.
SQL Server Integration Services (SSIS)
Build SSIS control flows to manage extraction and load of source data to staging and transformation to production.
The database tables
Build a Business Objects universe over the database
The Trades Reporting Database Business Objects Universe
A sample Business Objects report summarised at product type, sales person and client.
Note: The report contains synthetic data.