DB2 Cube Views: A Primer

Chapter 12. Accessing DB2 dimensional data using BusinessObjects 571

12.4 Reports and queries examples

To demonstrate how the MQTs built using DB2 Cube Views Optimization Advisor

improved BusinessObjects queries performance we used a simple business

scenario:

򐂰 Query1: Who are the most profitable consumers?

򐂰 Query 2: What do they buy?

To check if the report query is optimized by DB2 Cube Views through the

Optimization Advisor, we used the following method:

1. In BusinessObjects, launch SQL Viewer from the Query Panel (see

Figure 12-30), and copy the SQL statement.

Figure 12-30 Get the SQL statement from SQL Viewer

2. In DB2 Control Center, launch Explain SQL Panel, and paste the SQL

statement.

572 DB2 Cube Views: A Primer

Figure 12-31 Launch the explain

3. In DB2 Control Center, analyze the access plan graph from DB2 Explain to

check if MQTs is used.

4. Check the response time under BusinessObjects Data Manager (see

Figure 12-32).

Note: Depending on the type of BusinessObjects reports that you want to

create, you can run Optimization Advisor from DB2 OLAP Center to define

MQTs (Materialized Query Tables) that can be created. This allows your

report query to be optimized by MQTs with a shorter response time.

Chapter 12. Accessing DB2 dimensional data using BusinessObjects 573

Figure 12-32 Check the response time

The following examples show reports created on top of the universe that has

been previously built with the BusinessObjects Universal Metadata Bridge. It can

be seen that query response times are improved by MQTs.

We will present for each example:

򐂰 The report

򐂰 The SQL generated by BusinessOjects

򐂰 The query performance

򐂰 The data access result using the access plan graph from DB2 Explain

12.4.1 Query 1

Query 1 addresses the business question:

What are the top five most profitable consumer groups?

The report

The report is shown in Figure 12-33.

574 DB2 Cube Views: A Primer

Figure 12-33 Report 1

The SQL

The SQL is shown in Example 12-1.

Example 12-1 SQL 1

SQL

SELECT

STAR.CONSUMER.AGE_RANGE_DESC,

STAR.CONSUMER.GENDER_DESC,

SUM(STAR.CONSUMER_SALES.TRXN_SALE_AMT -

STAR.CONSUMER_SALES.TRXN_COST_AMT)

FROM

STAR.CONSUMER,

STAR.CONSUMER_SALES

WHERE

( STAR.CONSUMER_SALES.CONSUMER_KEY= STAR.CONSUMER.IDENT_KEY )

GROUP BY

STAR.CONSUMER.AGE_RANGE_DESC,

STAR.CONSUMER.GENDER_DESC

Chapter 12. Accessing DB2 dimensional data using BusinessObjects 575

The query performance

Table 12-1 shows the results.

Table 12-1 Query performance result

The data access

Without MQTs, the data access used is described in Figure 12-34.

Figure 12-34 Without MQTS: DB2 explain result

The Access Plan Graph of the query shows that tablespace scans have been

used because no MQTs can be used for query rewrites.

The measured response time for the refresh of the report is also long: 12

seconds.

Time to refresh report 1: timeron

Without MQT 148,969

With MQT 3,986