Citigroup

Historical Data Is Not Obsolete Data
Citigroup Global Markets recently needed to completely review its data retention policy in Germany due to the changing regulatory environment and an increased focus on data retention. All departments were asked to analyze their processes and determine what data they were required to store for supervisory purposes and the respective retention periods, as well as the levels of access they needed.

As retention periods lengthen and data archiving requirements intensify, the volumes involved eventually become seemingly limitless. As long as the stored files and documents were “simply lying around“ somewhere, this was not a major problem. Increasingly, however, historical data must be made available rapidly for online access. This refers not only to invoices, contracts, human resources documents, etc., but also to files relating to daily business—e-mails, transaction data from trading systems, communication between participants in securities trading, and much more. These processes create massive amounts of data from which specific information must be selected and made available quickly.

“That is why Citigroup needed a completely new approach to archiving,” said Szafran Athey, first vice president and head of CitiTech Frankfurt, a unit of Citigroup that provides IT services. “The approaches that were commonly used and adequate in the past, such as paper-based archiving, database excerpts, and sequential files on tapes, were no longer adequate in view of the growing volume of data, the lengthening retention periods and the demand for quick retrieval, if nothing else than for reasons of cost.”

Citigroup was also faced with the challenge of quickly locating and editing a growing volume of data, but they could not store it in the operational database due to performance reasons. Seamlessly integrating the data warehouse with the transactional database became a necessary step.

Online historical archive with Sybase IQ
Citigroup decided to replace the conventional static archive with a dynamic one. The management chose Sybase IQ as the tool to accomplish this.

“The technology used here enables short response times even when querying very large data volumes, plus the data is stored very efficiently due to high compression rates,” Athey said. “Citigroup in Germany now stores trading data for four years; this amounts to 13.2 million deals. Each deal comprises 388 bytes of real data. In the classic SQL database, this data grows to 405 bytes due to the index overhead. In contrast, the Sybase IQ database stores this information in 218 bytes, which represents a compression rate of 43 percent. Our goal is to make ten years’ worth of trading data available online via IQ.

Athey added: “The decisive factor is that Sybase IQ has the same application interface as a relational database, even though it stores the data completely differently. This means that the data can be accessed using regular SQL. Each application can therefore access current, as well as historical data seamlessly. Users can query archived data in real time, upload it back to the transactional database (in this case ASE), if needed (for example, if an error is discovered), edit the data, and archive it again. The fact that the transaction is amended in the transactional database ensures that there is a full audit trail of the amendment. Historical data is not obsolete any longer.”

Additional savings result from the fact that end users in the departments can now research historical data themselves, whereas in the past IT specialists were always required to do so. The process of archiving is also now fully automated, data is moved from the ASE database to the IQ data warehouse on a daily basis if it meets complex archive criteria based on the age of the transaction, the last time the record was amended, settlement date, etc.

New level of analysis quality now possible
The online historical archive was implemented principally in response to external regulatory requirements. “Now that it is in place, the archive also opens up new prospects within the company,” Athey said. “One example is standard online reports. If they were forgotten or not prepared for other reasons in the past, users could no longer generate these reports at a later date once the data was archived. Today this is no longer a problem. A large number of new analyses have become possible that no one would have thought to produce in the past due to the time and cost involved. In principle, there are almost no limits any longer on the systematic analysis of past activities—regardless of whether this involves statistics, trend analyses for marketing and CRM or process optimization—now that historical data can be analyzed in real time.”

Even dreams, such as a “tick database“ in which all changes in the price of a security during a particular trading day are recorded, have become a reality.

“Due to the enormous volume of data that is generated in such a case, we currently only record daily closing prices,” Athey said. “However, this type of historical database would theoretically allow us to record all the price updates of a security not just for a day but also for years. This could add value to the business and our customers.”

External requirements are also expected to increase. Supervisory authorities know that they can now request companies to implement procedures and processes that used to be impossible which are now feasible thanks to technical advances. Online historical archives will become a must.