602 DB2 Cube Views: A Primer
Query performance results
After submitting the SQL of the Regional Department Sales Contribution report
directly into the DB2 Explain SQL Dialog, we were able to obtain the database
cost, which was 735,263.62 timerons with a very complex construction
generation, as shown in Figure 13-8.
Figure 13-8 DB2 explain for Question 1: without MQT
Chapter 13. Accessing DB2 dimensional data using MicroStrategy 603
When submitting the same SQL from Regional Department Sales Contribution
report into a DB2 Database with Cube Views and MQTs enabled the Database
was able to determine the usage of MQT tables dynamically and used them as
fact tables for the resolution of the results.
The database cost for solving this SQL was 3,779.44 timerons with a more
simplified construction complexity. The results are shown in Figure 13-9.
Figure 13-9 DB2 explain for question 1: with MQT
Table 13-2 summarizes the data access path costs issued from DB2 explain
when using DB2 Cube Views MQTs.
Table 13-2 Query performance result
Without MQT 735,263.62
With MQT 3,779.44
604 DB2 Cube Views: A Primer
13.5.3 Question 2: campaign contributions to sales
The business issue to solve is to assess how each of the campaigns in your
region contributed to the rest of the region in terms of sales.
The business issue mentioned above has been resolved using the
functionality in MicroStrategy which allows the user to drill anywhere in
the project’s browsing hierarchies. In this case, the user has drilled to the
Campaign attribute from Region in the 01 – Regional Department Sales
Contribution report, as shown in Figure 13-10.
Figure 13-10 Drilling to campaign
Chapter 13. Accessing DB2 dimensional data using MicroStrategy 605
Once the user has drilled to Campaign attribute, the MicroStrategy Intelligence
Server will generate the corresponding SQL to bring back to the user a report
with data at the Campaign level, as shown in Figure 13-11.
Figure 13-11 Question 2: report grid