||View Job Listings|
||Post a job|
Balanced Processing to Achieve Optimal Performance for BI Reports and Queries
||Column published in DMReview.com
February 9, 2001
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 2: The Flow of Processing Table
| 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 |
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.
Figure 3: Sales Report
| Company XYZ |
| Fiscal Year 2001 |
| Product 1 || || || |
| || January || $ 100 || |
| || Subtotal || || $ 100 |
| Product 2 || || || |
| || January || $ 200 || |
| || Subtotal || || $ 200 |
| Total Product Sales || || $ 300 |
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.
Figure 4: Logical Grouping and Data Elements of the Sales Subject Area
| Logical Grouping || Data Element || SQL Statement |
| Period || |
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 || |
Quantity on Hand
| Transaction || |
sum(sales_line.qty_ordered * product.unit_sales_price)
The data elements used in the sales report from the subject area are:
- Product Name
- 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.
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.
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.
Figure 7: The Result Set
| Product Name || Month || Extended Amount |
| Product 1 || January || 100 |
| Product 2 || January || 200 |
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
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 email@example.com.
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|