Continue in 2 seconds

We are doing an OLTP project for our client, but they want OLAP capabilities for reporting.

By
  • Michael Jennings, Clay Rehm, Les Barbusinski
Published
  • November 05 2002, 1:00am EST
More in

Q:

We are doing an OLTP project for our client, but they want OLAP capabilities for reporting. Since these are such opposite functions, we had a tough time in design. Also we have two OLAP tool options: MS OLAP, which comes free with SQL2000, or Brio. I want your advice about the design. Should we try to develop a design that supports both OLTP and OLAP? Do you have any suggestions for choosing between Brio Query and MS-OLAP?

A:

Michael Jennings’ Answer: Combining OLTP and OLAP traffic to a single database structure would typically be a mistake. Assuming that your OLTP application is running some segment of your company’s business, you risk impacting its performance and ability to quickly process business transactions by merging OLTP transaction processing with OLAP analytic functionality. In order for transactions to be processed efficiently, the data store would have to be in third normal form. This construct works fine for transaction processing but is inefficient for OLAP request due to the excessive amount of joins that will be required to quickly answer business questions. Both OLTP transactions and OLAP analytics will be competing for the same disk I/O which will degrade performance. End users running OLAP requests will experience ever-changing result sets of information in their queries as OLTP transactions are processed throughout the day unless your design accounts for this. Aggregation, calculations, derived data, and multi-pass processing will have to be performed during at run time of an OLAP query further degrading performance. In most cases, operational reporting against an OLTP system is performed in a secondary data store separate from where transaction processing occurs in order to avoid the performance impact of operational reporting. Your company may decide to go down this path initially to save money but will quickly see the need to create a secondary data store for OLAP in order to be able to analysis strategic information in a efficient manner.

Clay Rehm’s Answer: Does your client really need OLAP capabilities or do they need standard prebuilt reports? Do they want to understand the underlying data, data structures and relationships that are needed to write their own queries? Do they want to actually write their own queries? These are questions to ask and get answers to before making any design decision.

Whether to choose Brio or MS-OLAP – that depends… do you already own both? If not, why are those tools your only options? Both tools will work – but depending on your specific requirements and how they may be used by your clients, one or both tools may not work!

The OLTP functionality must be designed for OLTP, which is meant for quick updates. OLAP databases are designed for easy and quick query and retrieval.

Instead of building a full blown data warehouse, you could develop reporting tables that are populated from the OLTP tables on a regular time frame depending on the latency requirements. Another option is to use database views that join OLTP tables together. Views give the impression of a table yet there are no ETL processes that have to be built and run to populate the data. This virtual look at the data is easier for users to understand and to obtain current data. The other benefit of database views is they are quick to develop and implement, and help to prove if the structure is what the users need. It also helps in the performance testing to determine if a view will be a long- term solution or a short-term solution depending on the response time.

Les Barbusinski’s Answer: As you know, OLAP and OLTP data structures are diametrically opposed to one another, so trying to design a one-size-fits-all database won’t work. What will work is layering your design by creating data mart structures (e.g., star schemas) that are separate from the highly denormalized OLTP structures. This approach will provide the optimum performance for each application.

As for contrasting Brio with MS-OLAP, there are too many variables to provide you with a simple answer. I would suggest, however, that you build a simple prototype with both tools – a prototype that provides some of the typical reporting requirements for your application. This will give you the best "feel" for choosing the tool that’s right for your shop.

Chuck Kelley’s Answer: First design the OLTP side to run the business of your client and then design and build a second database for OLAP processing. As for choosing Brio vs. MS OLAP, that will depend on what your clients needs are. Do an analysis of what capabilities are needed by the user community. Second, see which product the user community prefers to solve their problems. That will drive the product that is required.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access