Guideline

ReportNet Usage with Oracle Materialized Views

Product(s): IBM Cognos ReportNet Area of Interest: Modeling

ReportNet Usage with Oracle Materialized Views

Copyright Copyright 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC is an IBM Company. While every attempt has been made to ensure that the information in this document is accurate and complete, some typographical errors or technical inaccuracies may exist. Cognos does not accept responsibility for any kind of loss resulting from the use of information contained in this document. This document shows the publication date. The information contained in this document is subject to change without notice. Any improvements or changes to the information contained in this document will be documented in subsequent editions. This document contains proprietary information of Cognos. All rights are reserved. No part of this document may be copied, photocopied, reproduced, stored in a retrieval system, transmitted in any form or by any means, or translated into another language without the prior written consent of Cognos. Cognos and the Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated) in the United States and/or other countries. IBM and the IBM logo are trademarks of International Business Machines Corporation in the United States, or other countries, or both. All other names are trademarks or registered trademarks of their respective companies. Information about Cognos products can be found at www.cognos.com This document is maintained by the Best Practices, Product and Technology team. You can send comments, suggestions, and additions to [email protected] .

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

Contents
1 2 3 3.1 3.2 3.3 3.4 3.5 4 5 6 INTRODUCTION ............................................................................................ 4 WHEN TO USE MATERIALIZED VIEWS WITH REPORTNET ........................... 4 GENERAL IMPLEMENTATION GUIDELINES FOR MATERIALIZED VIEWS ..... 5 CREATE MODEL AND REPORTS ..................................................................................... 6 SCHEMA AND DIMENSION DEFINITION ............................................................................ 7 MATERIALIZED VIEWS DEFINITION AND CREATION ............................................................ 9 TESTING QUERIES ................................................................................................... 12 SHOW THE PLAN ..................................................................................................... 12 EXAMPLES ................................................................................................... 14 TOOLS ......................................................................................................... 26 TIPS ............................................................................................................ 27

RECOMMENDED READING...................................................................................... 30

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

1 Introduction
Materialized views are used to improve query performance on large databases. These queries often involve operations such as joins or aggregation which are expensive in terms of processing and time. They accomplish this by transparently using pre-computed summarizations and joins of data. These pre-computed summaries would typically be very small compared to the original source data. The query optimizer transparently re-writes query requests so that they use the materialized views as opposed to the detail tables.

2 When to use Materialized Views with ReportNet

The use of an Oracle Materialized Views approach for optimizing query performance for large data warehouses is a widely implemented technique. It represents a set of functionality which offers a more cost efficient approach than the aggregate table approach. Here are three areas where primary benefits would be realized through this approach: Optimized query performance against large data warehouses .Materialized Views are used by the RDBMS query optimizer transparently by rewriting queries to use pre-calculated views, even in situations where the MV only partially fulfills the query requirement. MV can be used by a report at different levels of aggregation, such as year, month week. This is something that is difficult to achieve with custom aggregate aware applications. More queries can be improved with fewer materialized views. Higher percentage of queries benefit, improving not just individual performance but also overall system performance. Materialized views can be designed using a dimensional approach. Reduced development associated with the optimization of your application. The Oracle query re-write mechanisms operate transparently and developing new reports and queries can benefit directly from past efforts and new optimizations do not requires updates to existing reports. Improved Load times and an ability to more effectively manage and reduce the staleness of the data.

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

3 General Implementation Guidelines for Materialized Views

Here are some general guidelines when implementing Materialized as part of the solution. The child side of a parent-child relationship between a dimension and fact table or between tables in snowflake dimension should be described with primary-foreign keys and the child side columns should be declared as not null. Each child must uniquely identify a single parent and ideally be verified against the data using the DBMS_DIMENSION package to ensure integrity. All constraints can be declared to ORACLE as non-enforced constraints if preferred. Large fact tables are often partitioned frequently by the time dimension. Large materialized views may also need to be partitioned. The view should be indexed to support query execution and refresh performance. Ensure to include the appropriate aggregate expressions to solve as many queries as possible. Presume to define sum and count of the same expression Count(*) is mandatory Review the business requirements with regards to refreshing the views. This may enable some views to include fast refresh unfriendly aggregates because less frequent full refreshes would be acceptable. Avoid building views that have wide rows or how cardinality (number of rows) starts to come within 10%1 of the size of the original fact table Ensure to have the ReportNet (CRN) and database administrator (DBA) teams work together at the start of the project as the FM model and core reports/queries are designed. This ensures that queries and view designs can be reviewed and adjusted appropriately.

This is a very general value

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

This document will give an overview of the major activities involved in the implementation of Materialized views in a ReportNet environment. The below figure gives a Flow of these activities.

Create Model & Reports Verify Schema Create Define & Create Test Dimensions Materialized Queries Views

3.1

Create Model and Reports The steps in this process are to create the desired data model and the reports or queries required. Using best practices model the dimensional metadata when creating the star or snowflake model. It is important to have the person(s) responsible for the Framework Manager (FM) model, ORACLE database and reports/queries to work closely together. While a DBA may be able to explain what ORACLE is doing with a given SQL statement they frequently do not know what factors influence ReportNet as it generated it. Because relationships could be defined in multiple places in an FM model it is very important to verify which relationship to review. The SQL generated by the query engine attempts to preserve the query subject names which can aid in isolating which objects are being used by a report/query. If relationships defined are not accurate they could result in impedance with the view.

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

3.2

Schema and Dimension definition The next activity after creating your model and reports is to review the schema and identify the dimensions. There a few guidelines when dimensions are defined and what constraints should exist to maximize query re-writes. The parent-child relationships between a dimension and fact table should be described using PRIMARY and FOREIGN keys. A dimension that is physically implemented as a snowflake should also use PRIMARY and FOREIGN keys. The columns in the FOREIGN KEY should be defined with the NOT NULL constraint. The constraints can be declared and not enforced using the RELY clause. Query re-write must be configured at the instance or session level to TRUSTED or FORCED when RELY is used.

Example of a table definition with constraints

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

CREATE TABLE "EAPPS"."BILLING" ( ... "SALES_ORG_SID" NUMBER(38), "DOCUMENT_CLASS_SID" NUMBER(38), ... "CREATED_DT" DATE, "CHANGED_DT" DATE, CONSTRAINT "BILLING_FK1151013785192867" FOREIGN KEY ("SALES_ORG_SID") REFERENCES "EAPPS"."ORGANIZATION" ("SALES_ORG_SID") RELY NOVALIDATE , ... CHECK ("SALES_ORG_SID" IS NOT NULL) VALIDATE , ... ) Dimension and hierarchy metadata should also be defined in the Oracle Database. These objects are used to perform join-back and rollup along a dimension. The specification of this metadata enables a much richer set of query re-writes and materialized view recommendations by ORACLE. A dimension contains multiple levels and hierarchies that can map against a snow-flake or de-normalized dimension table. This metadata provides the database engine the rollup structure within the dimension which it can use at runtime.

Example of dimension object

CREATE DIMENSION EAPPS.CUSTOMER_DIM LEVEL CITY_CD IS CUSTOMER.CITY LEVEL COUNTRY_CD IS CUSTOMER.COUNTRY_CD LEVEL CUSTOMER_CAT1_CD IS CUSTOMER.CUSTOMER_CAT1_CD LEVEL CUSTOMER_CAT2_CD IS CUSTOMER.CUSTOMER_CAT2_CD LEVEL CUSTOMER_CAT3_CD IS CUSTOMER.CUSTOMER_CAT3_CD LEVEL CUSTOMER_CAT4_CD IS CUSTOMER.CUSTOMER_CAT4_CD LEVEL CUSTOMER_CAT5_CD IS CUSTOMER.CUSTOMER_CAT5_CD LEVEL CUSTOMER_KEY IS CUSTOMER.CUSTOMER_SID LEVEL LEVEL1_CUSTOMER_NO IS CUSTOMER.LEVEL1_CUSTOMER_NO LEVEL LEVEL2_CUSTOMER_NO IS CUSTOMER.LEVEL2_CUSTOMER_NO LEVEL LEVEL3_CUSTOMER_NO IS CUSTOMER.LEVEL3_CUSTOMER_NO

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

LEVEL LEVEL4_CUSTOMER_NO IS CUSTOMER.LEVEL4_CUSTOMER_NO LEVEL REGION_CD IS CUSTOMER.REGION_CD HIERARCHY CATEGORY_HIER ( CUSTOMER_KEY CHILD OF CUSTOMER_CAT5_CD CHILD OF CUSTOMER_CAT4_CD CHILD OF CUSTOMER_CAT3_CD CHILD OF CUSTOMER_CAT2_CD CHILD OF CUSTOMER_CAT1_CD ) HIERARCHY CUSTOMER_NM_HIER ( CUSTOMER_KEY CHILD OF LEVEL4_CUSTOMER_NO CHILD OF LEVEL3_CUSTOMER_NO CHILD OF LEVEL2_CUSTOMER_NO CHILD OF LEVEL1_CUSTOMER_NO ) HIERARCHY GEO_HIER ( CUSTOMER_KEY CHILD OF CITY_CD CHILD OF REGION_CD CHILD OF COUNTRY_CD )

3.3

Materialized Views Definition and creation Once the above two activities have been accomplished successfully the next step is to define the materialized views. A materialized view definition can include aggregation such as SUM, MIN, MAX, AVG, COUNT (*), COUNT(x), COUNT (DISTINCT), VARIANCE or STDDEV, one or more tables joined together and a GROUP BY. The view may also be indexed partitioned. When creating materialized views there a few basic guidelines to follow Create materialized views that satisfy the largest number of queries. If a materialized view is to be used by query rewrite, it must be stored in the same database as the detail tables on which it relies.

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

10

A materialized view definition can include any number of aggregations. It can also include any number of joins. A materialized view can be partitioned, and you can define a materialized view on a partitioned table You can also define one or more indexes on the materialized view.

One approach to defining the view which includes the dimension keys as opposed to projecting for the attributes of the dimensions. This is possible because ORACLE has the metadata to enable it to join-back to the dimension tables at runtime should a query project, filter or group. One reason for using this approach is that it reduces the space occupied by the view and supports a wider set of re-writes such as ad-hoc queries that reference a subset of the dimensions in the star schema.

Example creating a View

CREATE MATERIALIZED VIEW EAPPS.MV_BILLINGS1 TABLESPACE EAPPS BUILD IMMEDIATE REFRESH FORCE ENABLE QUERY REWRITE as select CUSTOMER.CUSTOMER_SID, MATERIAL.MATERIAL_SID, ORGANIZATION.SALES_ORG_SID, ALL_TIME.TIME_SID, count(*) KNT, sum(BILLING.QUANTITY), sum(BILLING.EXTENDED_PRICE_AMT), sum(BILLING.DISCOUNT_AMT), sum(((BILLING.EXTENDED_PRICE_AMT + BILLING.FREIGHT_AMT) + BILLING.TAX_AMT) BILLING.DISCOUNT_AMT), count(BILLING.QUANTITY), count(BILLING.EXTENDED_PRICE_AMT), from CUSTOMER, MATERIAL, ORGANIZATION,

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views ALL_TIME, BILLING where

11

CUSTOMER.CUSTOMER_SID = BILLING.CUSTOMER_SID and MATERIAL.MATERIAL_SID = BILLING.MATERIAL_SID and ORGANIZATION.SALES_ORG_SID = BILLING.SALES_ORG_SID and ALL_TIME.TIME_SID = BILLING.TIME_SID group by CUSTOMER.CUSTOMER_SID, MATERIAL.MATERIAL_SID, ORGANIZATION.SALES_ORG_SID, ALL_TIME.TIME_SID ; For performance reasons, one should define indexes for the dimension columns. CREATE BITMAP INDEX "EAPPS"."MV_BILL_CUST" ON "EAPPS"."MV_BILLINGS1" ("CUSTOMER_SID") TABLESPACE "EAPPS"; CREATE BITMAP INDEX "EAPPS"."MV_BILL_MATL" ON "EAPPS"."MV_BILLINGS1" ("MATERIAL_SID") TABLESPACE "EAPPS"; CREATE BITMAP INDEX "EAPPS"."MV_BILL_ORG" ON "EAPPS"."MV_BILLINGS1" ("SALES_ORG_SID") TABLESPACE "EAPPS"; CREATE BITMAP INDEX "EAPPS"."MV_BILL_TIME" ON "EAPPS"."MV_BILLINGS1" ("TIME_SID") TABLESPACE "EAPPS"; ANALYZE TABLE "EAPPS"."MV_BILLINGS1" ESTIMATE STATISTICS;

A SQL query does not have to exactly match the materialized view definition in order for it to be used, because query rewrite uses a number of different types of rewrite methods which are listed below. Exact Match - where the join conditions and grouping columns in the query and materialized view match exactly Aggregation to All - the materialized view is grouped by product and month, for example, but the query is by products Rollup - query is by quarter, materialized view is at month level, but a dimension exists which defines how to rollup data from month to quarter Join Back - column in materialized view is used to join back to dimension, e.g. materialized view grouped on store_id, but query groups by store name Filtered data - materialized view only contains part of the data, e.g. countries UK and USA and query requests data for the UK only

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views 3.4

12

Testing queries There are several methods in ORACLE to look at queries. The following examples use the SQL*plus interface (command line) that will be found on most machines while tools such as Enterprise Manager, TOAD etc. are less common. Obtaining the native SQL generated by IBM Cognos tools is to enable IPF logging to capture native SQL commands. This or other 3rd party tools that trace the wire protocol can allow the logs to be captured locally to the workstation. ORACLE also offers SQL statement tracing that requires access to the location used by the ORACLE instance to write the traces. Use either approach and ensure to test the queries and reports as interactive HTML or PDF as appropriate. Doing so will avoid the following anomalies as it relates to prompting and the mode of execution. Report Studio has the ability to show native SQL for a query. This feature does not prompt the user prior to generating the SQL which results in optional filters being dropped from the query and default values for the prompts. The rollup processing will default to assuming that the query reflects an interactive (a.k.a. HTML) report. Assuming the native SQL is available the following commands can be used to review a statement.

3.5

Show the plan Assuming that the DBA constructed the plan table for the RDBMS and the RDBMS is 9iR2 or 10G the following skeleton could be used to review a query. truncate table plan_table; explain plan for put-your-sql-here ; set lines 130 set head off select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL')); Assuming the DBA has created the re-write table the following command can be used to ask ORACLE if the query would re-write to a specified view. It is also possible to leave ORACLE to define why any views in the system were considered good or bad candidates.

Note: Any literals in the SQL must use double single quotes to escape them within the string being passed to the procedure.

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

13

truncate table rewrite_table; DECLARE qrytext VARCHAR2(4000); BEGIN qrytext :=' put-your-sql-here '; DBMS_MVIEW.EXPLAIN_REWRITE (qrytext, put-your-mview-name-here); END; / select message from rewrite_table order by sequence; QSM-01033: query rewritten with materialized view, MV_BILLINGS1 ORACLE 10 has improved the parameter interface over 9i, this means that it should be harder to submit a large SQL string and run into buffer overflow errors that seem to occur with 9i. The following code presents a workaround method which includes a workaround to an ORACLE output routine as well2. Create this procedure so it can be used by the following statement when testing a query. create procedure p( p_string in varchar2 ) is l_string long default p_string; begin loop exit when l_string is null; dbms_output.put_line( substr( l_string, 1, 250 ) ); l_string := substr( l_string, 251 ); end loop; end; / set serveroutput on size 1000000 declare Rewrite_Array sys.rewriteArrayType := SYS.RewriteArrayType(); querytxt varchar2(4000) := ' put-your-sql-here ';
2

Courtesy Tom Kyte of ORACLE.

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

14

msg_no number; i number; begin dbms_snapshot.explain_rewrite(querytxt, 'put-your-mvname-here', Rewrite_Array); msg_no := rewrite_array.count; for i in 1..msg_no loop p('MV Name: ' ||Rewrite_Array(i).mv_name); -- p('Query : ' ||Rewrite_Array(1).query_text); p('Message: ' ||Rewrite_Array(i).message); end loop; end;

4 Examples
ReportNet generates a rich set of SQL statements and quickly allows authors to construct complex expressions and aggregations. Often, the resultant SQL does not look like the traditional SQL shown in manuals and documentation. Consider the basic group query in Query Studio. The native SQL generated for this query when run as HTML is:
select "ALL_TIME"."CALENDAR_YEAR" "C0", "ALL_TIME"."CALENDAR_QUARTER" "C1", "CUSTOMER"."CUSTOMER_CAT1_CD" "C2", sum("BILLING"."QUANTITY") "C3" from "EAPPS"."ALL_TIME" "ALL_TIME", "EAPPS"."CUSTOMER" "CUSTOMER", "EAPPS"."BILLING" "BILLING" where "BILLING"."CUSTOMER_SID"="CUSTOMER"."CUSTOMER_SID" and "BILLING"."TIME_SID"="ALL_TIME"."TIME_SID" group by "ALL_TIME"."CALENDAR_YEAR", "ALL_TIME"."CALENDAR_QUARTER", "CUSTOMER"."CUSTOMER_CAT1_CD"

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

15

The equivalent PDF report will use an extended aggregation that is pushed to ORACLE:
select "T0"."C0" "CALENDAR_YEAR", "T0"."C1" "CALENDAR_QUARTER", "T0"."C2" "CUSTOMER_CAT1_CD", "T0"."C3" "QUANTITY", sum("T0"."C3") over () "QUANTITY1", sum("T0"."C3") over (partition by "T0"."C0") "QUANTITY2" from (select "ALL_TIME"."CALENDAR_YEAR" "C0", "ALL_TIME"."CALENDAR_QUARTER" "C1", "CUSTOMER"."CUSTOMER_CAT1_CD" "C2", sum("BILLING"."QUANTITY") "C3" from "EAPPS"."ALL_TIME" "ALL_TIME", "EAPPS"."CUSTOMER" "CUSTOMER", "EAPPS"."BILLING" "BILLING" where "BILLING"."CUSTOMER_SID"="CUSTOMER"."CUSTOMER_SID" and "BILLING"."TIME_SID"="ALL_TIME"."TIME_SID" group by "ALL_TIME"."CALENDAR_YEAR", "ALL_TIME"."CALENDAR_QUARTER", "CUSTOMER"."CUSTOMER_CAT1_CD" ) "T0" order by "CALENDAR_YEAR" asc

Both of these queries will re-write to use the example materialized view.

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

16

Query subjects may be defined with embedded filters (i.e. limit the region codes to the North East) that result in derived tables being generated. Consider the following simple cross tabular report.

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

17

select "Q3"."REGION_NM" "REGION_NM", "Q3"."CALENDAR_YEAR" "CALENDAR_YEAR", "Q3"."QUANTITY" "QUANTITY" from (select "ALL_TIME"."CALENDAR_YEAR" "CALENDAR_YEAR", "CUSTOMER"."REGION_NM" "REGION_NM", sum("BILLING"."QUANTITY") "QUANTITY" from "EAPPS"."ALL_TIME" "ALL_TIME", (select "CUSTOMER"."COUNTRY_CD" "COUNTRY_CD", "CUSTOMER"."REGION_CD" "REGION_CD", "CUSTOMER"."CITY" "CITY", "CUSTOMER"."CUSTOMER_SID" "CUSTOMER_SID", "CUSTOMER"."REGION_NM" "REGION_NM" from "EAPPS"."CUSTOMER" "CUSTOMER"

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

where

18

"CUSTOMER"."REGION_CD" in ('MA', 'ME', 'NH', 'VT', 'AL', 'FL', 'GA', 'KY', 'MS', 'NC', 'SC', 'TN', 'VA', 'WV', 'CT', 'IN', 'MA', 'MD', 'ME', 'MI', 'NH', 'NJ', 'NY', 'OH', 'PA', 'RI', 'VT') ) "CUSTOMER", "EAPPS"."BILLING" "BILLING" where "BILLING"."CUSTOMER_SID"="CUSTOMER"."CUSTOMER_SID" and "BILLING"."TIME_SID"="ALL_TIME"."TIME_SID" group by "ALL_TIME"."CALENDAR_YEAR", "CUSTOMER"."REGION_NM" ) "Q3"

-------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 357 | 14280 | | 8861 (4)| 00:01:47 | | 1 | SORT GROUP BY | | 357 | 14280 | | 8861 (4)| 00:01:47 | |* 2 | HASH JOIN | | 2028K| 77M| | 8684 (2)| 00:01:45 | | 3 | TABLE ACCESS FULL | ALL_TIME | 2920 | 29200 | | 12 (0)| 00:00:01 | |* 4 | HASH JOIN | | 2028K| 58M| 792K| 8652 (2)| 00:01:44 | |* 5 | TABLE ACCESS FULL | CUSTOMER | 26859 | 472K| | 1241 (2)| 00:00:15 | | 6| MAT_VIEW REWRITE ACCESS FULL| MV_BILLINGS1 | 2028K| 23M| | 4685 (1)| 00:00:57 | -------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------1 - SEL$E30F1547 3 - SEL$E30F1547 / ALL_TIME@SEL$2 5 - SEL$E30F1547 / CUSTOMER@SEL$3 6 - SEL$E30F1547 / MV_BILLINGS1@SEL$0F4384E5 Predicate Information (identified by operation id): --------------------------------------------------2 - access("ALL_TIME"."TIME_SID"="MV_BILLINGS1"."TIME_SID") 4 - access("MV_BILLINGS1"."CUSTOMER_SID"="CUSTOMER"."CUSTOMER_SID") 5 - filter("CUSTOMER"."REGION_CD"='AL' OR "CUSTOMER"."REGION_CD"='CT' OR

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

19

"CUSTOMER"."REGION_CD"='FL' OR "CUSTOMER"."REGION_CD"='GA' OR "CUSTOMER"."REGION_CD"='IN' OR "CUSTOMER"."REGION_CD"='KY' OR "CUSTOMER"."REGION_CD"='MA' OR "CUSTOMER"."REGION_CD"='MD' OR "CUSTOMER"."REGION_CD"='ME' OR "CUSTOMER"."REGION_CD"='MI' OR "CUSTOMER"."REGION_CD"='MS' OR "CUSTOMER"."REGION_CD"='NC' OR "CUSTOMER"."REGION_CD"='NH' OR "CUSTOMER"."REGION_CD"='NJ' OR "CUSTOMER"."REGION_CD"='NY' OR "CUSTOMER"."REGION_CD"='OH' OR "CUSTOMER"."REGION_CD"='PA' OR "CUSTOMER"."REGION_CD"='RI' OR "CUSTOMER"."REGION_CD"='SC' OR "CUSTOMER"."REGION_CD"='TN' OR "CUSTOMER"."REGION_CD"='VA' OR "CUSTOMER"."REGION_CD"='VT' OR "CUSTOMER"."REGION_CD"='WV') This scenario introduces additional filters on the dimensions and group filters
select "CUSTOMER"."REGION_NM" "C0", "MATERIAL"."MATERIAL_CAT2_DSC" "C1", "ALL_TIME"."CALENDAR_YEAR" "C2", sum("BILLING"."QUANTITY") "C3" from (select "CUSTOMER"."COUNTRY_CD" "COUNTRY_CD", "CUSTOMER"."REGION_CD" "REGION_CD", "CUSTOMER"."CITY" "CITY", "CUSTOMER"."CUSTOMER_SID" "CUSTOMER_SID", "CUSTOMER"."REGION_NM" "REGION_NM" from "EAPPS"."CUSTOMER" "CUSTOMER" where "CUSTOMER"."REGION_CD" in ('MA', 'ME', 'NH', 'VT', 'AL', 'FL', 'GA', 'KY', 'MS', 'NC', 'SC', 'TN', 'VA', 'WV', 'CT', 'IN', 'MA', 'MD', 'ME', 'MI', 'NH', 'NJ', 'NY', 'OH', 'PA', 'RI', 'VT') ) "CUSTOMER", "EAPPS"."MATERIAL" "MATERIAL", "EAPPS"."ALL_TIME" "ALL_TIME", "EAPPS"."BILLING" "BILLING" where "MATERIAL"."MATERIAL_CAT2_DSC" in ('Bottles', 'Boxes', 'Cans', 'Chemicals') and "BILLING"."CUSTOMER_SID"="CUSTOMER"."CUSTOMER_SID"

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

and "BILLING"."MATERIAL_SID"="MATERIAL"."MATERIAL_SID" and "BILLING"."TIME_SID"="ALL_TIME"."TIME_SID" group by "CUSTOMER"."REGION_NM", "MATERIAL"."MATERIAL_CAT2_DSC", "ALL_TIME"."CALENDAR_YEAR" having sum("BILLING"."QUANTITY") between 500 and 1000

20

Due to the size of the plan only the operation table is shown. -------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |

-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | 1 | TEMP TABLE TRANSFORMATION | 2 | LOAD AS SELECT |* 3 | TABLE ACCESS FULL 472K| | 4 | LOAD AS SELECT |* 5 | TABLE ACCESS FULL 814K| |* 6 | FILTER | | 7 | SORT GROUP BY |* 8 | | 9| |* 10 | HASH JOIN TABLE ACCESS FULL HASH JOIN | | ALL_TIME | | | | | CUSTOMER | | MATERIAL | | | 202 | 11514 | | | | | | | | 26859 | | | | | 52105 |

| | | 202 | 11514 | | 185K| 10M| | 2920 | 29200 | | 185K| 8498K|

| 11 | TABLE ACCESS FULL 52105 | 814K| |* 12 | HASH JOIN

| SYS_TEMP_0FD9D66DB_4C5EFD | | | 185K| 5605K|

| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66DA_4C5EFD | 26859 | 393K| | 14 | MAT_VIEW REWRITE ACCESS BY INDEX ROWID| MV_BILLINGS1 185K| 2893K| | 15 | BITMAP CONVERSION TO ROWIDS | | | | 16 | BITMAP AND | | | | | 17 | BITMAP MERGE | | | | | 18 | BITMAP KEY ITERATION | | | |

| |

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views | 19 | | 13 | |* 20 | | | 21 | | 22 | | 23 | | 13 | |* 24 | | TABLE ACCESS FULL BITMAP INDEX RANGE SCAN

21 1 |

| SYS_TEMP_0FD9D66DA_4C5EFD | | MV_BILL_CUST |

BITMAP MERGE BITMAP KEY ITERATION TABLE ACCESS FULL BITMAP INDEX RANGE SCAN

| | | | | | | | SYS_TEMP_0FD9D66DB_4C5EFD | | MV_BILL_MATL | |

This example introduces a % of group and ranking that were not specified in the materialized view. The native SQL generated by ReportNet is still re-written to leverage the materialized view.

select "T0"."C0" "C0", "T0"."C1" "C1", "T0"."C2" "C2",

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

sum("T0"."C2") over (partition by "T0"."C0") "C3", "T0"."C3" "C4", first_value("T0"."C3") over (partition by "T0"."C0") "C5", "T0"."C4" "C6", first_value("T0"."C4") over (partition by "T0"."C0") "C7" from (select "T0"."C0" "C0", "T0"."C1" "C1", "T0"."C2" "C2", sum("T0"."C2") over () "C3", sum("T0"."C2") over (partition by "T0"."C0") "C4" from (select "CUSTOMER"."REGION_NM" "C0", "ALL_TIME"."CALENDAR_YEAR" "C1", sum("BILLING"."QUANTITY") "C2" from (select

22

"CUSTOMER"."COUNTRY_CD" "COUNTRY_CD", "CUSTOMER"."REGION_CD" "REGION_CD", "CUSTOMER"."CITY" "CITY", "CUSTOMER"."CUSTOMER_SID" "CUSTOMER_SID", "CUSTOMER"."REGION_NM" "REGION_NM" from "EAPPS"."CUSTOMER" "CUSTOMER" where "CUSTOMER"."REGION_CD" in ('MA', 'ME', 'NH', 'VT', 'AL', 'FL', 'GA', 'KY', 'MS', 'NC', 'SC', 'TN', 'VA', 'WV', 'CT', 'IN', 'MA', 'MD', 'ME', 'MI', 'NH', 'NJ', 'NY', 'OH', 'PA', 'RI', 'VT') ) "CUSTOMER", "EAPPS"."ALL_TIME" "ALL_TIME", "EAPPS"."BILLING" "BILLING" where "BILLING"."CUSTOMER_SID"="CUSTOMER"."CUSTOMER_SID" and "BILLING"."TIME_SID"="ALL_TIME"."TIME_SID" group by "CUSTOMER"."REGION_NM", "ALL_TIME"."CALENDAR_YEAR" ) "T0" ) "T0"

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

23

Calculations can be performed against aggregates such as this simple calculation to compute 50% of the total value.
select "ALL_TIME"."CALENDAR_YEAR" "CALENDAR_YEAR", "CUSTOMER"."REGION_NM" "REGION_NM", "ALL_TIME"."CALENDAR_QUARTER" "CALENDAR_QUARTER", sum("BILLING"."QUANTITY") "QUANTITY", sum("BILLING"."QUANTITY")*0.5 "calc1" from "EAPPS"."ALL_TIME" "ALL_TIME", (select "CUSTOMER"."COUNTRY_CD" "COUNTRY_CD", "CUSTOMER"."REGION_CD" "REGION_CD", "CUSTOMER"."CITY" "CITY", "CUSTOMER"."CUSTOMER_SID" "CUSTOMER_SID", "CUSTOMER"."REGION_NM" "REGION_NM" from "EAPPS"."CUSTOMER" "CUSTOMER" where "CUSTOMER"."REGION_CD" in ('MA', 'ME', 'NH', 'VT', 'AL', 'FL', 'GA', 'KY', 'MS', 'NC', 'SC', 'TN', 'VA', 'WV', 'CT', 'IN', 'MA', 'MD', 'ME', 'MI', 'NH', 'NJ', 'NY', 'OH', 'PA', 'RI', 'VT') ) "CUSTOMER", "EAPPS"."BILLING" "BILLING" where "ALL_TIME"."CALENDAR_YEAR"=1998 and "BILLING"."CUSTOMER_SID"="CUSTOMER"."CUSTOMER_SID" and "BILLING"."TIME_SID"="ALL_TIME"."TIME_SID" group by "CUSTOMER"."REGION_NM", "ALL_TIME"."CALENDAR_YEAR", "ALL_TIME"."CALENDAR_QUARTER" order by "CALENDAR_YEAR" asc, "REGION_NM" asc

The model may contain predefined derived measures in the fact table that the user simply drags onto their report. ORACLE will attempt to re-write the query to leverage the materialized view.

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

24

select "T0"."C0" "CALENDAR_YEAR", "T0"."C1" "REGION_NM", 1 "C_____CubeDetailsItem", "T0"."C2" "c4" from (select "ALL_TIME"."CALENDAR_YEAR" "C0", "CUSTOMER"."REGION_NM" "C1", sum("BILLING"."Calculation") "C2" from "EAPPS"."ALL_TIME" "ALL_TIME", (select "CUSTOMER"."COUNTRY_CD" "COUNTRY_CD", "CUSTOMER"."REGION_CD" "REGION_CD", "CUSTOMER"."CITY" "CITY", "CUSTOMER"."CUSTOMER_SID" "CUSTOMER_SID",

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

"CUSTOMER"."REGION_NM" "REGION_NM" from "EAPPS"."CUSTOMER" "CUSTOMER" where

25

"CUSTOMER"."REGION_CD" in ('MA', 'ME', 'NH', 'VT', 'AL', 'FL', 'GA', 'KY', 'MS', 'NC', 'SC', 'TN', 'VA', 'WV', 'CT', 'IN', 'MA', 'MD', 'ME', 'MI', 'NH', 'NJ', 'NY', 'OH', 'PA', 'RI', 'VT') ) "CUSTOMER", (select "BILLING"."CUSTOMER_SID" "CUSTOMER_SID", "BILLING"."TIME_SID" "TIME_SID", (("BILLING"."EXTENDED_PRICE_AMT"+"BILLING"."FREIGHT_AMT")+"BILLING". "TAX_AMT") - "BILLING"."DISCOUNT_AMT" "Calculation" from "EAPPS"."BILLING" "BILLING" ) "BILLING" where "BILLING"."CUSTOMER_SID"="CUSTOMER"."CUSTOMER_SID" and "BILLING"."TIME_SID"="ALL_TIME"."TIME_SID" group by "ALL_TIME"."CALENDAR_YEAR", "CUSTOMER"."REGION_NM" ) "T0" order by "CALENDAR_YEAR" asc, "REGION_NM" asc

-----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 357 | 14637 | 6146 (5)| 00:01:14 | | 1 | SORT GROUP BY | | 357 | 14637 | 6146 (5)| 00:01:14 | |* 2 | HASH JOIN | | 2028K| 79M| 5969 (2)| 00:01:12 | | 3 | TABLE ACCESS FULL | ALL_TIME | 2920 | 29200 | 12 (0)| 00:00:01 | |* 4 | HASH JOIN | | 2028K| 59M| 5937 (2)| 00:01:12 | |* 5 | TABLE ACCESS FULL | CUSTOMER | 4671 | 84078 | 1233 (1)| 00:00:15 | | 6| MAT_VIEW REWRITE ACCESS FULL| MV_BILLINGS1 | 2028K| 25M| 4685 (1)| 00:00:57 |

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

26

-----------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------1 - SEL$0FBF33B2 3 - SEL$0FBF33B2 / ALL_TIME@SEL$2 5 - SEL$0FBF33B2 / CUSTOMER@SEL$3 6 - SEL$0FBF33B2 / MV_BILLINGS1@SEL$0F4384E5 Predicate Information (identified by operation id): --------------------------------------------------2 - access("ALL_TIME"."TIME_SID"="MV_BILLINGS1"."TIME_SID") 4 - access("CUSTOMER"."CUSTOMER_SID"="MV_BILLINGS1"."CUSTOMER_SID") 5 - filter("CUSTOMER"."REGION_CD"='MA' OR "CUSTOMER"."REGION_CD"='ME' OR "CUSTOMER"."REGION_CD"='NH' OR "CUSTOMER"."REGION_CD"='VT') Column Projection Information (identified by operation id): ----------------------------------------------------------1 - "ALL_TIME"."CALENDAR_YEAR"[NUMBER,22], "CUSTOMER"."REGION_NM"[VARCHAR2,30], SUM("MV_BILLINGS1"."SUM(((BILLING.EXTENDED_PRICE_A")[22] 2 - "ALL_TIME"."CALENDAR_YEAR"[NUMBER,22], "CUSTOMER"."REGION_NM"[VARCHAR2,30], "MV_BILLINGS1"."SUM(((BILLING.EXTENDED_PRICE_A"[NUMBER,22] 3 - "ALL_TIME"."TIME_SID"[NUMBER,22], "ALL_TIME"."CALENDAR_YEAR"[NUMBER,22] 4 - "CUSTOMER"."REGION_NM"[VARCHAR2,30], "MV_BILLINGS1"."SUM(((BILLING.EXTENDED_PRICE_A"[NUMBER,22], "MV_BILLINGS1"."TIME_SID"[NUMBER,22] 5 - "CUSTOMER"."CUSTOMER_SID"[NUMBER,22], "CUSTOMER"."REGION_NM"[VARCHAR2,30] 6 - "MV_BILLINGS1"."CUSTOMER_SID"[NUMBER,22], "MV_BILLINGS1"."TIME_SID"[NUMBER,22], "MV_BILLINGS1"."SUM(((BILLING.EXTENDED_PRICE_A"[NUMBER,22]

5 Tools
Oracle9i came with a feature known as the Summary Advisor that recommended a set of materialized views for a given query workload. Consisting of a number of procedures and functions within the DBMS_OLAP package, together with GUI functionality within Oracle Enterprise Manager, Summary Manager was a useful tool for recommending an aggregation strategy for your data warehouse.

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

27

Summaries are only part of the story, however, when looking to improve query response time within your Oracle data warehouse. The effective creation of indexes is just as important and with Oracle Database 10g, Oracle has acknowledged this by providing a new tool, the SQL Access Advisor, that now makes recommendations for both materialized views and indexes. Available through the DBMS_ADVISOR PL/SQL package, the SQL Access Advisor can be used to either tune existing materialized views and indexes, or to recommend them for new schemas that need tuning. An important point to note with the SQL Access Advisor is that whilst it is installed by default as part of the 10g database installation, it is actually part of the Enterprise Manager Tuning Pack, which needs to be licensed separately if using in a production environment. Oracle 10g includes a number of enhancements to materialized views and the query rewrite mechanism. By using new features such as the SQL Access Advisor, DBMS_ADVISOR.TUNE_MVIEW and the new query equivalence mechanism, DBAs will find that summaries are easier to create, are more likely to qualify for query rewrite, and can now be delivered through alternative technologies such as the Oracle 10g OLAP Option.

6 Tips
Always ensure to obtain the native SQL generated by CRN using either configuring the server to log Native Queries or via ORACLE SQL tracing

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

28

Due to the size of the SQL statements errors may occur in tools when pasting in the statement. To avoid this and make the text more readable define an empty query subject which is based on a database query. Simple copy and paste the SQL into the query subject and right-mouse click and select the reformat option.

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

29

IBM Cognos Proprietary Information

ReportNet Usage with Oracle Materialized Views

30

If the customer requires that the application set the session settings instead of setting them at the instance they could: Define ORACLE logon triggers that issue the statements Define connection or session commands for the connection in the portal (Requires 1.1MR2)

Recommended Reading
ORACLE white papers and articles http://www.oracle.com/technology/products/bi/db/10g/dbbi_tech_info_perf.html ORACLE online documentation library (look for the Data Warehousing PDF) http://www.oracle.com/technology/documentation/database10g.html

IBM Cognos Proprietary Information