Customers Create
Their Own Custom Reports Online
BNP Paribas Securities Services initially deployed Sybase Adaptive
Server Enterprise (ASE) to execute online transactions for its
financial customers. But the firm eventually expanded the role of the
database to also support a web-based Business Objects reporting
application that financial customers could use to review the
performance of their portfolios. To create the reports, BNP Paribas
uploaded data to Sybase ASE from various source systems and used IBM
DataStage TX tool for EAI. After compiling the data, Sybase ASE then
uploaded the information to Business Objects for customer viewing.
As more investment customers came to rely on the reports, the
online demand increased dramatically and report generation performance
suffered. BNP Paribas then decided to improve the level of service of
its online reporting by internally developing a separate reporting
application. “We wanted to keep Sybase ASE focused on handling the
online transactions, which is what it is designed for,” says BNP
Paribas Securities Services DBA, Marc Guillard. “We also wanted to add
more flexibility to the reporting service so that we could give our
customers the ability to organize report information according to their
needs, and so they can create a greater variety of reports.”
This business need required an online reporting system with
the ability for end-users to customize reports along any axis of
analysis they desired. Customers also needed the ability to design
report layouts according to how they wanted to view the information.
Additionally, BNP Paribas wanted to improve the robustness of the
system in compiling larger amounts of data, anticipating
growth in the customer base and meeting the goal of delivering
real-time data proactively to customers.
Proof-of-Concept
Cements Sybase IQ Capabilities
When BNP Paribas realized that developing an application internally to
support the reporting mechanism would not generate reports fast enough,
the firm looked towards the leading business intelligence vendors for a
proof-of-concept test. “We decided to test Sybase as well as various
database engines,” Guillard says.
Sybase provided the optimal schema for the POC configuration
and supported the effort with the necessary server hardware. BNP
Paribas put Sybase IQ through extensive testing of ad hoc queries and
as a result, created a model for handling large amounts of data that
can be queried via the Business Objects on the front end.
The testing proved that Sybase IQ would provide the fastest
and most flexible reporting connection between Sybase ASE and Business
Objects. The Sybase IQ performance surpassed the speed the other
vendors. “Sybase IQ also surpassed the speed of our internal
custom application by two or three times,” Guillard said. “And it
worked so well we established Sybase IQ as a business intelligence
architecture standard that the rest of the company can now consider.”
For the deployment of Sybase IQ, BNP Paribas used Sybase
Replication Server Messaging Edition to replicate data from Sybase ASE
into Sybase IQ. The process also leveraged the Sybase PowerDesigner’s
Information Liquidity Model to design the replication process – the
model automatically generated 90 percent of the scripts needed for the
deployment.
Using
Best-of-Breed Databases Leads to Doubling of Ad Hoc Queries
BNP Paribas customers have found the custom reports and filters that
they can now create are much more informative than the static reports.
As an example, customers can now view their portfolios in new ways,
although this functionality is also used in a number of different
applications. “We are also much more efficient with our information
architecture by using best-of-breed databases optimized for
their functions,” Guillard adds. “Sybase ASE now performs what it is
primarily designed to do – OLTP, but we still rely on it for some of
our historical reports. Because Sybase IQ is ideal for turning data
into customizable reports, it makes sense to have separate, but
integrated database environments for transactions and reporting.”
The online reporting system is now providing reports for close
to 10,000 external customers and internal employees who access
approximately 200,000 reports per month. “Since adding Sybase IQ, the
demand for reports has risen dramatically as users realize just how
much more helpful the information is that we can now provide,” Guillard
says. “We receive requests for 10-20,000 more reports per month, and ad
hoc queries have grown from 5,000 to 10,000 per month.”
The Sybase ASE-Sybase IQ combination has also helped BNP
Paribas proactively deliver reports with Replication Server Messaging
Edition. “Rather than having users poll for information, it’s much more
efficient for our system to push data out,” Guillard says. “The
Real-time Events component sends messages to our application server to
run specific processes depending on the data that comes in to Sybase IQ
via Sybase Replication and the reports affected by the data. This
method causes much less stress on our system compared to users polling
the database to find out what has changed.”
Some customers have defined specific reports they want to
receive when the data arrives. Real-time Events lets BNP Paribas
deliver the reports within 15 minutes from Sybase ASE to Sybase IQ and
then to the Business Objects reports. Each time new data is replicated
from ASE to Sybase IQ, the real-time functionality of Replication
Server Messaging Edition sends notifications to the Business Objects
reporting application. End user customers and internal staff now see
near real-time data in all reports and queries.
Plenty of Room
to Grow With Systems That Run on Their Own
Because of the ease of working with Sybase technologies, BNP Paribas
deployed this innovative system in less than four months. “Sybase’s
responsiveness to any issue we had during the deployment also helped
speed things up,” Guillard says. “We now have two integrated systems
capable of handling large amounts of transactions and report requests.
We also have plenty of room to grow in the amount of data we can
process and the historical information we can store.”
The Sybase ASE system now houses three months of data for
short-term reporting, but BNP Paribas may eventually reduce this to one
month of data to further streamline the OLTP system – and accelerate
performance. And with the 50 percent data compression offered by Sybase
IQ, BNP Paribas can now keep up to 10 years of data online for trend
reporting and advanced analytics.
A critical and seemingly complex system may appear to require
a great deal of attention and tuning. But looks can be deceiving, as
Guillard explains, “Both Sybase ASE and Sybase IQ have proven to
require very little system maintenance,” Guillard said, “They
practically run on their own.”
