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

Get DB2 Cube Views: A Primer now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.