barclaysimage

Barclays Capital is an investment bank with global offices and contacts in Americas, Asia Pacific, Europe, Middle East and Africa. Cody Middlebrook as a Business Intelligence Architect, was head hunted from Brisbane, Australia and commissioned to undertake three projects for Barclays during the period 2007-2010. The first project was in London during 2008-2009. The second project was also in London during 2008-2009. The third project was in New York City during 2008-2010. The projects revolved around the development and implementation of Business Intelligence in Trading. In an interview, Cody Middlebrook discusses with Rajen Jani about the three projects of Barclays Capital.

FIRST PROJECT

Scenario:
Barclays Capital had an existing platform for fixed income, derivates and FX Data. They wanted it to be more efficient and show real-time data much faster. Additionally, reports had to be formed in real-time, thereby giving more technological benefit to the traders.

Solution:
Cody devised a solution with comprehensive Data Mining, Integration Services data transformation and SSRS reports. The SSRS API was interrogated for report manipulation. Data Mining was used on Trade Products to find Trading Patterns and prediction for Fixed Income, Derivatives and FX. He effectively developed and implemented a Build and Testing Platform in MSBuild with C# custom tasks for Building, Testing and Deploying Cubes, SSIS Packages and Reports. He successfully developed a BI calculation engine for middle office. The Calculation engine was written in C# and .NET 3 Workflow Foundation. This calculated Interpolated and Extrapolated Trade data into a Data warehouse for Reporting via an SSAS Cube.

Interview:
RJ: You came from Brisbane, so how did you find London?
CM: London was like a second home for me, I had lived there before for 10 years.
RJ: Ok. Coming to your first project with Barclays, did you use C# for developing the solution?
CM: Yes. With my BI and C# skills, they wanted me to take their existing C++ Excel plugs that calculated Normdist functions, re-write them in C#, and move the calculation tool away from Excel. They wanted to create a new workflow tool, where the trader could build their own workflow calculation. With C# coding, this power could be obtained in the reporting.
RJ: How did you increase the reporting speed?
CM: For a faster reporting tool, I built cubes. Cubes are used to hold pre-calculated data or aggregated data. This speeds up the process of extracting data for reporting. So, I built SSAS Cubes, SSIS Packages and Reports. When traders want data, they don’t want to wait.
RJ: Yes. Real-time data needs to be fast. How was your experience in building this solution?
CM: This experience prompted to me build a BI Automated Build and Test Framework.
RJ: Did you later use this framework?
CM: Yes. I have used this framework with other financial companies.
RJ: Ok. Lets move on to your next project with Barclays.

Benefits:
Barclays Capital, by utilizing the skills of Cody Middlebrook, benefited in serving their customers more effectively.
1. For the middle office a BI calculation engine was built.
2. A Data Warehouse was built wherein trade data was Interpolated and Extrapolated, resulting in the formation of real-time Reports.
3. Barclays Capital improved their services rendered to traders of Fixed Income, Derivatives and FX.

SECOND PROJECT

Scenario:
Barclays Capital wanted to obtain real-time data with 1-minute latency to the traders. The data from the trading systems had to be obtained, saved, aggregated and reported, all in real-time and with a maximum latency of 1-minute. Barclays needed to take a growing legacy Trade reporting application into a warehouse and provide a simple reporting solution, using Cubes and Business Objects.

Solution:
The Oracle driven Trader Flash Trading Data was migrated with SSIS. For front and middle office, BI reports were generated. Using AMO, an automated Cube sliding window written in C# and .NET, the current trading data could be accessed at real-time via the ROLAP partitions. All existing reports, written in Reporting Services were rebuilt, using the latest Business Objects XI 2.0 and was later merged to 3.0. The Business Objects API were accessed via C#, which triggered scheduled BO Reports. Using ASP, C#, and .NET, a site was built, where traders could access any menu items on the site, which gave them access to their required real-time report.

Interview:
RJ: After this first successful project, you again came to London during 2008-2009, to work on another project of Barclays?
CM: Yes. This was the first trial to get real-time data with 1-minute latency to the traders. This trial would later on drive my third project with Barclays in New York City.
RJ: In this project, you had to deal with obtaining data and aggregating them in real-time?
CM: All within 1-minute. By the time the data was obtained and aggregated, the traders wanted it in the reports.
RJ: And you had to deal with real-time data of financial products like Stock, Commodities, Derivatives and Forex?
CM: All of them. From obtaining data from trading platforms, to assimilating, to reporting, all within 1-minute.
RJ: So you used Flash data?
CM: Flash data mainly for this trial. At that time, Microsoft had no real reporting tool contender, SSRS and Excel.
RJ: How did you use the Flash data?
CM: I decided to use Business Objects XI3.0 and the SSAS Cubes as the data source. This would be the first version of Business Objects that supported SSAS cubes. And it came with its headaches. We had to overcome a load of Version 1 issues. We worked directly with SAP to solve these issues.
RJ: So, the issues were solved with great difficulty. Can you recollect any issue?
CM: For example, when a cube was used as a Symantec layer between a SSAS cube, all attributes and measures became text.
RJ: This gave problems in sorting?
CM: Yes. This meant all dates could not be sorted correctly.
RJ: So you found a bug in the release of BO IX3.0.
CM: Well, we informed SAP and worked with them to solve it. We had to wait for them to patch the release of BO IX3.0.
RJ: Till then, the dates remained unsorted?
CM: Yes. It was a nightmare. All dates were exposed as 20120213 year month day.
RJ: You had developed a BI Automated Build in the earlier project. Did you use it in this project?
CM: Yes. The BI Automation, I believe was the first of its kind.
RJ: Ok. Let’s move on to your third project at Barclays Capital.

Benefits:
Barclays Capital was benefited by using the skills of Cody Middlebrook.
1. It successfully migrated the Oracle driven Trader Flash Trading data with SSIS.
2. For the front and middle office, BI reports were developed.
3. Cubes and Business Objects gave a simple reporting solution and traders could easily obtain their desired report in financial products like Stocks, Commodities, Derivates, and Forex, with a latency of maximum 1-minute.

THIRD PROJECT

Scenario:
In 2008, the financial services firm Lehman Brothers filed for bankruptcy and their core business, was acquired by Barclays. Cody Middlebrook was commissioned by Barclays to help Barclays set up a fast trading platform at their new acquired office of Lehman Brothers at New York City.

Solution
Cody migrated Lehman’s complex trading data with SSIS. For the middle office and traders, Cody built a SharePoint 2010 Server with Windows Server 2008 and SQL Server 2008. The SharePoint 2010 Server accessed and managed the SQL Reporting Services Custom Reports. For the front and middle office Reports, a Global Management Reporting System was built. Business Objects Universes that connected to the BW and SSAS Trading Cubes were developed. For calculating rolling Formal and Flash trading figures in real-time, a complex calculation engine was built using C#. Real-time partitions and a dimensional warehouse was developed. To assist in achieving real-time figures, a complex MDX Cube was built for complex and fast calculations. SSRS Reports were developed that were managed in SharePoint and PerformancePoint dashboards. Complex cube security was implemented.

Interview:
RJ: Barclays sent you to New York for this third project.
CM: Yes. I was sent to New York to assist in the Lehman’s merger with Barclays.
RJ: How did you migrate Lehman’s data?
CM: I worked with SAP again to get their data into a MIS Training and reporting system real-time.
RJ: So you developed Business Objects Universes that connected to the BW and SSAS Trading Cubes?
CM: Yes. At the time SAP were finding their feet with SSAS as a data source in the Universe, so I worked directly with SAP to path away for future SAP BO release, which would solve ongoing SAP BO and MS Cube issues.
RJ: What was the latency?
CM: 5-minute latency. 10,000 records a second.
RJ: That’s impressive. Did you face any issues or difficulties?
CM: Loads.
RJ: Can you recollect some example?
CM: There were several difficulties. Like the first issue was the hardware at the time. Then there were problems with data storage and cube processing. A major issue was the month-to-date rolling and year-to-date rolling calculations.
RJ: With multiple cities, the problem must have multiplied.
CM: Not only multiple cities, but also multiple countries. Even different bank holiday rules in different countries had to be taken into consideration. If you slice the cube on London and New York and look at the YTD measure, then the measure needs to take into consideration two sets of bank holidays upto date. This slows down the calculations. Also as the year gets closer to the end there is an increase in data, which leads to more calculation. This slows down the reporting tool to a halt.
RJ: Did you use Microsoft BI tools to solve this?
CM: The case finished with it, cannot be done with Microsoft BI tools and the project moved to a memory database. This was how Google stored their data at the time and was new to the database world.
RJ: In the first project with Barclays, you developed your BI automated build. Did you use it in this project?
CM: Yes I did, but in a modified way.
RJ: How does this BI Automation actually work?
CM: Well, when developers write code, they generally check their code into a source control system. This system can take their code, compile it and then execute it. Tests are run against the code. The BI Build works like this. There was no real platform for BI Builds at that time. They had not really thought about BI.
RJ: How did you develop this BI Automation?
CM: I developed in C# and MS Build, a platform for continuous integration and automated cube testing. So, when a developer made a change to the BI solution, the BI Build would run.
RJ: What would the BI Build run and do?
CM: On a staging server, it would run several tasks. First it would get the latest code from the Source Control. Then it would build the solution. Create the database, deploy the SSIS package, and run the test data into the warehouse. Thereafter, it would deploy the cube, run MDX tests against the cube. It would deploy reports and their security policies. It would also report any dependency breaks to the team. All of this cannot be done in Team Foundation Server with the MS Build framework I worked with 7 years ago.
RJ: So you created a unique build framework of your own. That’s very innovative. Have you thought of getting it patented?
CM: Well, I use this build framework in more projects of my new clients. However, now it has been modified to work with loads of new source control products.
RJ: Great. I am sure you will develop it much further as years go by. Well, I wish I could continue this interview, but then it has to end. Thank you Cody for giving your time for this interview and sharing your thoughts.
CM: Thank you Rajen, it was great talking with you.

Benefits:
Many benefits accrued to Barclays Capital by utilizing the skills of Cody Middlebrook.
1. The complex trading data of Lehman’s was migrated with SSIS.
2. For traders and middle office, a SharePoint 2010 Server was built on Windows Server 2008 and SQL Server 2008. Due to this SQL Reporting Services (SSRS) Custom Reports could be accessed and managed.
3. Development of Business Objects Universes that connected to the BW and SSAS Trading Cubes.
4. A C# complex calculation engine for calculating rolling Formal and Flash trading figures real-time, was built.
5. Development of dimensional warehouse and real time partitions.
6. Complex MDX Cube calculations to assist in achieving real-time figures, was built.
7. Development of SSRS Reports that were managed in SharePoint and PerformancePoint dashboards.
8. Implementation of complex cube security with AMO stored procedures and dimensional security, to prevent sensitive trading data being viewed.
9. Development of  Business Objects XI 3.0 pre-canned reports. It also gave the platform for user ad-hoc reporting using the Cube.

For images, technical drawings and further information:
Cody Middlebrook
cody@bicg.com.au

One Comment

  1. Great case study on the difficulties of BI in investment banking.

Leave a Comment

Your email address will not be published. Required fields are marked *