This was built for a City of London company which provides analysis for investment banks and pension funds on their stock market and commodity trading. This analysis was achieved by comparing their own trading with their peer group or the market.
There were several challenges:
1) Each bank and fund manager provided data in their own formats, with varying degrees of detail. Dates and currency were in different formats, different identifiers were used for shares and commodities and brokers could be referred to differently. A complex and powerful system was built to import and harmonise the diverse data. Tools such as SSIS were used to do this.
2) we needed to build a picture of global trading to do this. For each share we created minute by minute history of trading. The data received for this came continuously like a fire hydrant from the world’s trading centres, running very nearly 24/7.
3) Finally this all had to be combined into a single data warehouse which was available as a web reporting system to clients. 50+ million rows of financial data had to be available for clients in dynamic reports which brought its own challenges to achieve high performance. Complex algorithms were used for the calculations, which had to take account of currency fluctuations and other variables.