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
Timeron
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
Drill
Anywhere
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
606 DB2 Cube Views: A Primer
Query performance results
After submitting the SQL to a DB2 database without DB2 Cube Views MQTs
enabled, the database access timerons cost for generating results was of
735,798.81, as shown in Figure 13-12.
Figure 13-12 DB2 explain for question 2: without MQT

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

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial