DB2 UDB's High-Function Business Intelligence in e-business

Chapter 2. DB2 UDB’s materialized views 69

2.8.9 Step 9: Load the materialized views with production data

This step involves loading the materialized views with production data (via

REFRESH TABLE statements), and making them available for DB2 optimization,

after executing runstats against the materialized views and their corresponding

indexes.

2.8.10 Generalizing local predicates application example

Sapient is our sample application that explores data cubes with a star schema,

by slicing and dicing multidimensional data. Any multidimensional data can be

analyzed by this tool.

Sapient consists of a report view, and navigational controls.

򐂰 The report view allows for the viewing of the results of data queries on a data

cube. Reports may be summary tables, trend line graphs or pie charts, etc.

򐂰 An important part of the navigational controls are the dimensions and metrics

selection boxes. The dimension selection box allows the selection and drill

down on each dimension. This includes drilling down a dimension hierarchy

or cross drilling from one dimension to another. The metric selection box

allows for the selection of the metrics that are computable for the given data

cube. Additional navigation buttons allow forward and backward navigation to

view previous reports, and the drill button to initiate the query to drill a

hierarchy or cross drill a dimension.

Important: The aforementioned steps describe a process that requires skilled

professionals using trial and error techniques in order to design effective

materialized views, and drop them when they are no longer beneficial. The

process is both time consuming and error prone.

Attention: There are plans to provide an Design Advisor in future to assist

DBAs define materialized views that deliver maximum performance benefits

with minimal overheads.

70 High-Function Business Intelligence in e-business

We use the Medline data star schema shown in Figure 2-12 as the target of the

Sapient application.

Figure 2-12 Sapient star schema

The Sapient application provides an interactive interface as shown in Figure 2-13

for accessing the contents of this star schema, and generates appropriate SQL

queries to deliver the desired result.

TIME

Time_ID

Month

Year

COUNTRY

Country_ID

Country_Name

PHARM ACTION

PA_ID

PA_Name

INSTITUTION

Inst_ID

Inst_Name

DOCUMENT

Doc_ID

Title

Abstract

AUTHOR

Auth-ID

Auth-Name

PUBLICATION

Pub_Month

Pub_Name

Pub_Type

LANGUAGE

Lang_ID

Lang_Name

Fact1

Medline Data Model

Medline Data Model

MESH

Mesh_ID

Root_Level

First_Level

Second_Level

Third_Level

Fourth_Level

Fifth_Level

Sixth_Level

Seventh_Level

Eighth_Level

Ninth_Level

Qualifier

Tree_Number

Item_Name

Scope_Note

Chapter 2. DB2 UDB’s materialized views 71

Figure 2-13 Sapient graphical user interface

For the purposes of our scenario, we assume that the performance of the

generated queries are unsatisfactory, and would like to consider designing

materialized views to improve their performance. Example 2-37 shows a list of

the “problem” queries listed in priority order.

Example 2-37 “Problem” queries listed in priority order

Query 1:

SELECT MESH.SECOND_LEVEL, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS

FROM FACT1_SUBSET, MESH

WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND MESH.ROOT_LEVEL='Anatomy'

AND MESH.FIRST_LEVEL='Body Regions'

GROUP BY MESH.SECOND_LEVEL

Query 2:

SELECT MESH.FIRST_LEVEL, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS

FROM FACT1_SUBSET, MESH

WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND

MESH.ROOT_LEVEL='Chemicals and Drugs'

GROUP BY MESH.FIRST_LEVEL

Query 3:

72 High-Function Business Intelligence in e-business

SELECT MESH.ROOT_LEVEL, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS

FROM FACT1_SUBSET, MESH

WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID

GROUP BY MESH.ROOT_LEVEL

Query 4:

SELECT AUTHOR.AUTHOR_NAME, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS

FROM FACT1_SUBSET, MESH, AUTHOR

WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND

FACT1_SUBSET.AUTHOR_ID=AUTHOR.AUTHOR_ID AND

MESH.ROOT_LEVEL='Anatomy' AND MESH.FIRST_LEVEL='Animal Structures'

GROUP BY AUTHOR.AUTHOR_NAME

Query 5:

SELECT AUTHOR.AUTHOR_NAME, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS

FROM FACT1_SUBSET, MESH, AUTHOR

WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND

FACT1_SUBSET.AUTHOR_ID=AUTHOR.AUTHOR_ID AND

MESH.ROOT_LEVEL='Anatomy' AND MESH.FIRST_LEVEL IN ('Body Regions','Cells')

GROUP BY AUTHOR.AUTHOR_NAME

Query 6:

WITH DT AS

(

SELECT COUNTRY_NAME, COUNT(*) AS COUNT, YEAR, MONTH,

ROOT_LEVEL, FIRST_LEVEL

FROM FACT1_SUBSET, MESH, TIME, COUNTRY

WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND

FACT1_SUBSET.TIME_ID=TIME.TIME_ID AND

FACT1_SUBSET.COUNTRY_ID=COUNTRY.COUNTRY_ID

GROUP BY ROLLUP(YEAR, MONTH), ROLLUP(ROOT_LEVEL, FIRST_LEVEL), COUNTRY_NAME

)

SELECT *

FROM DT

ORDER BY COUNTRY_NAME, YEAR, MONTH, ROOT_LEVEL, FIRST_LEVEL

Query 7:

WITH DT AS

(

SELECT COUNTRY_NAME, COUNT(*) AS COUNT, YEAR, ROOT_LEVEL,

FROM FACT1_SUBSET, MESH, TIME, COUNTRY

WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND

FACT1_SUBSET.TIME_ID=TIME.TIME_ID AND

FACT1_SUBSET.COUNTRY_ID=COUNTRY.COUNTRY_ID

GROUP BY ROLLUP(YEAR), ROLLUP(ROOT_LEVEL), COUNTRY_NAME

)

SELECT *

FROM DT

ORDER BY COUNTRY_NAME, YEAR, ROOT_LEVEL