Portals eNewsletters Web Seminars dataWarehouse.com DM Review Magazine
DM Review | Covering Business Intelligence, Integration & Analytics
   Covering Business Intelligence, Integration & Analytics Advanced Search
advertisement

RESOURCE PORTALS
View all Portals

WEB SEMINARS
Scheduled Events

RESEARCH VAULT
White Paper Library
Research Papers

CAREERZONE
View Job Listings
Post a job

Advertisement

INFORMATION CENTER
DM Review Home
Newsletters
Current Magazine Issue
Magazine Archives
Online Columnists
Ask the Experts
Industry News
Search DM Review

GENERAL RESOURCES
Bookstore
Buyer's Guide
Glossary
Industry Events Calendar
Monthly Product Guides
Software Demo Lab
Vendor Listings

DM REVIEW
About Us
Press Releases
Awards
Advertising/Media Kit
Reprints
Magazine Subscriptions
Editorial Calendar
Contact Us
Customer Service

Business Intelligence:
Balanced Processing to Achieve Optimal Performance for BI Reports and Queries

online columnist Jonathan Wu     Column published in DMReview.com
February 9, 2001
 
  By Jonathan Wu

As a user of a business intelligence (BI) application that provides reporting and querying capabilities, do you ever wonder why some of your reports or queries execute in seconds while others take minutes? Waiting several minutes for a report or query to complete is frustrating for most users. Assuming that the components and technical architecture of the BI/ad hoc query environment are appropriately addressed (Components of an Optimal BI/Ad Hoc Query Environment, December 2000 and Differences Between Web and Non-Web Applications, March 2000, DMReview.com), the primary cause of slow performance can be the design of the report/query, the BI application or the database.

When configuring most BI applications that provide reporting and querying functionality, a reporting subject area needs to be developed and designed. Leading BI application vendors that provide these capabilities - Brio Technology, Business Objects SA and Cognos - refer to these reporting subject areas as a data model, a universe and a catalog, respectively. Reporting subject areas are typically focused on a business function or process (such as accounts payable, fixed assets, human resources or sales) and provide users with the ability to develop their own reports and queries to extract data from a database or result set. The process of designing a subject area depends on the collection of the users' functional requirements. The team that is responsible for configuring the BI application must design the subject area to satisfy the functional requirements and achieve optimal performance. In order to do this, the processing that occurs when using the BI application must be understood . For example, a user accesses a BI application and either develops a query or executes a report. The BI application dynamically generates the structured query language (SQL) statement which is then submitted to the database. The database executes the SQL statement and returns the result set to the BI application. If a report was originally executed and contained calculations and formatting, the returned result set would be further processed by the BI application. Figures 1 and 2 describe the flow of processing.

Figure 1
Figure 1

Steps Processing Location of Processing
1 Creation of SQL statement Client workstation or application server
2 Execution of SQL statement Database server
3 Calculations and formatting Client workstation or application server
Figure 2: The Flow of Processing Table

By understanding the process flow that occurs when these BI applications are used, one can strike a balance at each step in order to achieve the desired objective. If the objective is to provide users with the fastest query/report performance possible, the SQL statements generated and the calculations performed by the BI application must be simple, meaning that complex calculations or extensive data manipulations must be performed and stored at the database level. In this objective, the database schema must be modified or developed and loaded to support the fast data extraction by the BI application. By doing this, the processing of the calculations or data manipulations would shift from the execution of the SQL statement to the load process of the data attributes in the database. This would also create efficiency since the data is calculated or manipulated once. All users could access this information instead of each user having to separately perform this process. In addition, consistent business rules and data transformations can be applied by storing the precalculating and premanipulating data in a database.

Following is a brief case study that highlights balanced processing to achieve optimal performance. In this case study, a BI application has been configured and a sales subject area has been created. The BI application is connected to an online transaction processing (OLTP) database which captures and processes sales transactions. In this environment, a user of the BI application can access the sales subject area and execute a report or develop a query.

Report/Ad Hoc Query

A user has selected and executed the sales report, which is displayed in Figure 3. In this report, product, period and sales information has been extracted from the OLTP database by the BI application and formatted into the presentation of the sales report.

Company XYZ

Sales Report

Fiscal Year 2001
Product 1
January $ 100
Subtotal $ 100
Product 2
January $ 200
Subtotal $ 200
Total Product Sales $ 300
Figure 3: Sales Report

If this report contained numerous calculations, it would be beneficial to move those calculations from the report and either have that calculated data stored in the database or defined as a data element within the reporting subject area. This would shift the processing of the calculation to the database and improve the performance of the report generation.

BI Application Subject Area

Before the sales report can be created or executed, a sales subject area must be designed and developed. Within a subject area there are logical groupings of data elements that allow users to easily locate them. For example, the sales subject area has logical grouping and data elements as shown in Figure 4.

Logical Grouping Data Element SQL Statement
Period

Date

Month

sales_header.date

decode(substr(sales_header.date,1,2), 01, 'January', 02, 'February', 03, 'March', 04, 'April', 05, 'May', 06, 'June', 07, 'July', 08, 'August', 09, 'September', 10, 'October', 11, 'November', 12, 'December')

Product

Product Name

Product Number

Description

List Price

Quantity on Hand

product.product_name

product.product_num

product.description

product.unit_sales_price

product.qty_on_hand

Transaction

Transaction Number

Line Number

Quantity Ordered

Extended Amount

sales_header.salesh_num

sales_line.line_number

sales_line.qty_ordered

sum(sales_line.qty_ordered * product.unit_sales_price)

Figure 4: Logical Grouping and Data Elements of the Sales Subject Area

The data elements used in the sales report from the subject area are:

  • Product Name
  • Month
  • Extended Amount

When a user requests the sales report through the BI application, it will dynamically generate the SQL statement for the sales report from the sales subject area shown in Figure 5.

Figure 5
Figure 5

Database

The sales subject area is configured to access the OLTP database in order to extract the data. The entity-relationship diagram in Figure 6 depicts the entities and the data attributes that are accessed by the sales subject area.

Figure 6
Figure 6

A result set would be produced by the database from the SQL statement generated by the BI application from the sales subject area to support the sales report.

Product Name Month Extended Amount
Product 1 January 100
Product 2 January 200
Figure 7: The Result Set

In this basic case study, the month and extended amount data elements in the sales subject area will have an impact on performance due to the SQL functions used. Specifically with the extended amount data element, if the sum function and calculation were removed, one would have to calculate the detail and total the individual amounts in order to arrive at the correct value. In order to improve performance, the values of these data elements should be processed and stored in data attributes within the database. The entities in the database would have to be modified to support the addition of month and extended amount data attributes. A SQL script would also have to be developed or the OLTP application would have to be modified to populate values for these two data attributes. In addition, appropriate indexes such as an index on the month attribute would have to be created to support rapid data extraction. By doing this, the processing of the month and extended amount data would shift from the execution of the SQL statement to the load process of these data attributes. This would also create efficiency because the calculated or manipulated data is performed once and all users could access this information instead of each user having to perform this process separately. While this is a very basic case study and the performance improvement from shifting the month and extended amount to data attributes within the database may be negligible, it highlights the ability to shift processing to achieve optimal performance.

For BI applications that provide ad hoc query and reporting capabilities, the design of a subject area can have a significant impact on performance. Understanding the processing that occurs when using these BI applications is essential to addressing and considering design modification to achieve optimal performance. While optimal performance is one objective of users of these BI applications, the trade-off is modification and maintenance of the source database and OLTP application.

...............................................................................

For more information on related topics visit the following related portals...
Query & Reporting and Business Intelligence.

Jonathan Wu is a senior principal with Knightsbridge Solutions. He has extensive experience designing, developing and implementing information solutions for reporting, analysis and decision-making purposes. Serving Fortune 500 organizations, Knightsbridge delivers actionable and measurable business results that inform decision making, optimize IT efficiency and improve business performance.  Focusing exclusively on the information management disciplines of data warehousing, data integration, information quality and business intelligence, Knightsbridge delivers practical solutions that reduce time, reduce cost and reduce risk. Wu may be reached at jwu@knightsbridge.com.

Solutions Marketplace
Provided by IndustryBrains

Autotask: The IT Business Solution
Run your tech support, IT projects and more with our web-based business management. Optimizes resources and tracks billable project and service work. Get a demo via the web, then try it free with sample data. Click here for your FREE WHITE PAPER!

Data Quality Tools, Affordable and Accurate
Protect against fraud, waste and excess marketing costs by cleaning your customer database of inaccurate, incomplete or undeliverable addresses. Add on phone check, name parsing and geo-coding as needed. FREE trial of Data Quality dev tools here.

Design Databases with ER/Studio: Free Trial
ER/Studio delivers next-generation data modeling. Multiple, distinct physical models based on a single logical model give you the tools you need to manage complex database environments and critical metadata in an intuitive user interface.

Email Regulatory Compliance
E-Trail Digital Archive is a feature rich, turnkey Electronic Communications Retention, Retrieval and Supervisory system.

Free EII Buyer's Guide
Understand EII - Trends. Tech. Apps. Calculate ROI. Download Now.

Click here to advertise in this space


E-mail This Column E-Mail This Column
Printer Friendly Version Printer-Friendly Version
Related Content Related Content
Request Reprints Request Reprints
Advertisement
advertisement
Site Map Terms of Use Privacy Policy
SourceMedia (c) 2006 DM Review and SourceMedia, Inc. All rights reserved.
SourceMedia is an Investcorp company.
Use, duplication, or sale of this service, or data contained herein, is strictly prohibited.