Release 4.1 for Windows
A86730-01
 Library |
 Contents |
 Index |
| Oracle Discoverer Administration Edition Administration Guide Release 4.1 for Windows A86730-01 |
|
This chapter consists of the following sections:
This chapter explains summaries and how you specify and maintain them yourself. For details about how Discoverer recommends and creates the best summaries for you, see Chapter 16, "Automated Summary Management".
A summary improves query performance in Discoverer Plus or Discoverer Viewer by using pre-aggregated data created through Discoverer Administration Edition.
The term summary used throughout this guide refers either to summary tables (with pre-8.1.6 databases) or Materialized Views (MVs) ( 8.1.6+ databases). For further details about summary tables and MVs see Section 15.1.1.1, "Summary tables or Materialized Views?".
A query run in Discoverer Plus can be redirected/rewritten to use an appropriate summary rather than having to query the detail tables. A summary is used only when it satisfies the conditions of the query.
Discoverer Plus uses summaries to reduce the time taken to return query results and provides a significant improvement in performance.
Discoverer uses either summary tables or Materialized Views (MVs) depending on which of the following scenarios is true:
|
NOTE:.The SQL generated and the summary table or MV used for a particular query can be viewed through the SQL Inspector/Explain Plan in Discoverer Plus (see Section 15.1.4.3, "Viewing Summary Redirection in Discoverer Plus") |
A Summary Folder is how Discoverer represents the underlying structure of a summary table or MV. Each Summary Folder has one or more Items (i.e.columns from a summary table or MV).
You can view Summary Folders from the Summaries tab in Discoverer Administration Edition (see Figure 15-1, "A Summary Folder as displayed in Discoverer").
The following elements define a Summary Folder:
You set up Summary Folders using the Summary Wizard. To understand how the process works, read the following pages before you launch the Summary Wizard.
The rest of this section describes these concepts in more detail.
A Summary Combination is a single set of axis and measure items in a summary table or MV. Each Combination is a different way of combining two or more of the Items in the Summary Folder. It is very much like a query, in that it defines a specific row and column set.
If a user executes a query with the same Items as those specified in a Combination, the query is run against either a summary table or MV instead of the detail data.
You can define as many Combinations as you require for each Summary Folder.
It is also faster to refresh Summary Folders with multiple Combinations, because the higher level summaries are built from the lower level ones, which is considerably faster than building separate Summary Folders all from the detail data.
For information on how to choose the most appropriate Summary Combinations, see Section 15.2, "Good Summary Folder Design."
You can combine items in a summary folder into Summary Combinations. From a Summary Combination, Discoverer creates one of the following depending on the version of the database:
This section contains the following information about summary tables/Materialized Views:
The following table compares Discoverer summary tables and server MVs.
RDBMS version 8.1.6+ only - When Discoverer Administration Edition is operating against Oracle 8.1.6 (and summaries are needed) it will automatically create server MVs. It will no longer generate and maintain Discoverer summary tables.
Stored as Materialized View - The precomputed summary results are stored as a MV. Additional functionality of the MV can be utilized.
Full or Incremental Refresh:
Full - Refreshes by completely recalculating the result set.
Incremental - Refreshes by incrementally adding the new data that has been inserted into the tables.
Refresh On Demand/Commit:
On Commit - Refresh occurs automatically on the next transaction commit performed against the detail table.
On Demand - Refresh occurs when a user manually executes a refresh.
Refresh Outside Discoverer - Since MVs are stored in the server, those MVs generated within Discoverer are available for refresh by other client applications. For example, a MV created within Discoverer can be refreshed through SQL*Plus using the supplied DBMS_MVIEW package.
Rewrite by Server - The server recognizes when an existing MV can be used to satisfy a given query request and will then transparently rewrite the request to use the MV instead of the detail data.
Defined in terms of Tables and Columns - The Materialized View query is defined in terms of database columns and tables. Whereas, Discoverer Summaries are created from EUL elements - items and folders.
From a Discoverer Administration Edition viewpoint, there are two types of summary folder:
The main differences between these types of summary table are described in Table 15-2.
In both cases, Discoverer knows where summary tables are located and the Items represented in them and so it can use summary redirection to reduce the time taken to perform a query. With Oracle 8.1.6+ the server carries out a summary rewrite to an appropriate MV.
External summary tables are useful:
|
NOTE to users of non-Oracle databases: If you are using a non-Oracle database, Discoverer supports external Summary Folders only. |
External summaries can be registered against tables and views (not the detail tables) but in Oracle 8.1.6+ MVs cannot be created against a view, instead summary redirection is used (see Table 15-3).
| Registering against a table | Registering against a view |
|---|---|
|
Where an external summary is registered against a table, a MV definition is created. |
Where an external summary is registered against a view a MV is not created. Summaries behave in the same manner as pre-8.1.6 Discoverer summary management, that is Discoverer redirection is used (see Section 15.1.4, "Summary Redirection"). |
The data in summary tables and MVs must be maintained to keep all of the summary tables and MVs consistent. If the database changes often, summary tables and MVs need to be refreshed accordingly to keep their data current with the underlying database. With a properly maintained set of summary tables or MVs, query results are accurate, regardless of the table or MV being used.
When you run a query in Discoverer Plus the results will be returned either from the detail tables or from a summary table/MV.
The following conditions regarding database versions also apply:
Both summary redirection and query rewrite are transparent to the user. Both provide exactly the same results as queries that run against the detail tables but return the results in far less time. This depends on whether or not you have set the option of whether to use summaries or not in Discoverer Plus or Discoverer 4i Viewer (this setting can be defined in Discoverer Plus and Discoverer 4i Viewer).
Summary Redirection describes the process of redirecting a query to use a summary table or MV rather than the detail data. Discoverer Plus performs this process automatically against pre-8.1.6 databases. The server performs this process against 8.1.6+ databases (by rewriting the query to a MV).
A query redirected to a summary table or MV can return results in a few seconds. The same query directed against the detail data tables may require a three-or four-table join and an aggregation over thousands or millions of rows, and thus would take considerably longer. Both queries, however, would produce the same results.
The net result is accurate results and quick response times.
For further information about the rules regarding 8.1.6 MV server rewrite scenarios refer to the Oracle8i Data Warehousing Guide Release 2 (8.1.6) Part Number A76994-01.
Discoverer Plus can only use Summary Redirection if all of the following prerequisites are met.
The following prerequisites apply to pre-8.1.6 databases only unless otherwise specified:
In the case of Derived Items (see Chapter 12.1.1.1, "Derived Items" for information about Derived Items), it is not sufficient to include the elements used to create the Derived Items in a Summary Combination; you must include the Derived Items themselves.
In the case of Items in Complex Folders (see Chapter 6.2.2.1, "What is a Complex Folder?" for information about Complex Folder), it is not sufficient to include the Items from the source Folders in a Summary Combination; you must include the Items from the Complex Folders themselves.
This ensures that the result set of data in the summary is the same as that in the detail. The exception in this case is that queries can be defined that use fewer Joins than those specified when the summary table was created, provided that you clear Detail foreign keys can have NULL values on the Join Options dialog box (displayed by clicking Options... on the New Join or Edit Join dialog boxes).
See Section 15.4, "Editing Summary Folder Properties" for more information.
To determine how your database system can benefit from Summary Redirection, use the Summary Wizard (specifying the summaries yourself) to recommend summaries based on previous queries. For a more detailed look at the query statistics option, see the business area named Query Statistics that you received with Discoverer. It includes a workbook for analyzing query usage, the Items most frequently used in queries, the Folders containing the Items, and query execution times (applies to pre-8.1.6 and 8.1.6+ databases).
To view the effect of summary redirection, use the SQL Inspector dialog (choose View | SQL Inspector) in Discoverer Plus.
The SQL Inspector dialog has two tabs, the SQL tab and the Execution Plan tab.
The SQL tab displays the SQL that Discoverer sends to the server.
The Execution Plan tab displays the execution plan chosen by the server for the query request. The execution plan defines the sequence of operations the server performs to execute the statement.
When running Discoverer against an 8.1.6+ database the server controls redirection by rewriting the SQL to use a MV. If a server rewrite occurs, the server execution plan indicates the MV name.
The SQL that Discoverer sends to the server can be viewed from the SQL tab in the SQL Inspector dialog in Discoverer Plus. The SQL rewritten by the server can be viewed on the Execution Plan tab in the SQL Inspector dialog in Discoverer Plus.
In the above figure the SQL remains unchanged.
The MV is used by the RDBMS and is identified in the Execution Plan by the table name EUL4_MV{Identifier}
(not using a MV)
When running against a pre-8.1.6 database Discoverer controls redirection to a summary table. The SQL can be viewed at the SQL tab and the server Execution Plan can be viewed on the Execution Plan tab in the SQL Inspector dialog in Discoverer Plus.
Figure 15-4 shows a crosstab worksheet of items from the Video Analysis folder (created as part of the tutorial (see Chapter 4, "Tutorial") and the resulting SQL statement in the SQL Inspector dialog. The SQL statement shows that the summary table EUL4_SUM100750 is referenced. The table at the bottom of Figure 15-4 is taken from the Combinations | Properties | Mappings page of the Edit Summary dialog box in Discoverer Administration Edition and shows the database column mapping in the EUL4_SUM100750 summary table.
Discoverer Plus automatically chooses the most appropriate summary table to process the query efficiently. This action is completely transparent to the user.
Figure 15-5 shows the same worksheet as before after the user has drilled down from Year to Month. Notice Discoverer Plus has redirected the second part of the query to EUL4_SUM100774 instead of EUL4_SUM100750.
Figure 15-6 shows the same worksheet again, this time the user has drilled down from Region to City. Again, Discoverer Plus automatically chooses the most efficient summary tables for each part of the query.
This example consists of five tables, one of which has almost 70,000 records (see Figure 15-7). The schema and data are taken from the tutorial.
Consider a query requiring the following items:
This would require a five-table join and an aggregation of all matching rows in SALES_FACT (the table with almost 70,000 rows). Producing results to the query could take several minutes depending on the capability of the server.
On the other hand, if the query could be redirected to a single table that already contains the data for Region, Department, Year, and SUM (Dollar_Profit) (see Figure 15-8), then the query would produce an almost instantaneous response.
The sample summary table shown in Figure 15-8 stores the information needed by the query at the month level, and only has to be aggregated to the year level. Discoverer Plus knows it is faster to aggregate from the single table than from the six-table join and full table scan option.
This section applies specifically to Discoverer summary management with a pre-8.1.6 database. For information about operating against an 8.1.6+ database you may find it useful to refer to the Oracle8i Data Warehousing Guide Release 2 (8.1.6) Part Number A76994-01.
Designing useful Summary Folders involves a trade off between the database space used to store the resulting summary tables and the required performance of queries. The key to good Summary Folder design is creating the most appropriate Summary Combinations for the pattern of system usage.
Consider Summary Combinations in two basic ways:
Summary Combinations for popular queries should include all the items and joins used in those queries. Because they may include text items, they may take up considerable database space, but they produce the fastest performance, because no Joins are done.
Summary Combinations for the more ad hoc environment (where queries are far less standardized) are typically based on different combinations of keys in the main fact table.
For example, the columns of the two summary tables shown in Figure 15-9 are mapped to the appropriate Items in the Sales Fact Folder. Although TIME_KEY, PRODUCT_KEY and STORE_KEY are EUL Items hidden from the end user, you can still map the corresponding summary table columns to those Items.
Discoverer will join one of these tables to one or more of the dimension tables (STORE, PRODUCT, or FISCAL DATE) to obtain a quick answer. The requirement is that the target dimension table must be joined to the FACT table by Items defined in the EUL, and that the Summary Folder contains the foreign key items in the FACT folder.
If the user requests Product Category, Month, SUM(Dollar Profit), Discoverer will join EUL4_SUM200801 to PRODUCT and FISCAL DATE to obtain results. Discoverer knows about the foreign and primary keys between SALES_FACT and the two tables, and can apply them to EUL4_SUM200801.
Building Summary Combinations in "layers" is usually an efficient way to work:
This section consists of the following topics:
To be able to create summary folders, the following requirements must be met:
This section describes how to create managed Summary Folders based on Items in the EUL.
This option creates a Managed summary table. It is only available if the Summary Management feature is enabled. See Section 2.2, "Summary Management," for more information.
There are three ways of moving Items and Measures from one list to the other:
To select more than one Item or Measure at once, hold down Ctrl while you click on the Items.
Remember to include:
You can select any Axis Items and math functions, but if the Items are from different Folders, a Join must exist between the Folders.
By default, the first Summary Combination (column 0) is a "catch all" Summary Combination which includes all the Items selected in Summary Wizard: Step 3.
Each Summary Combination is listed in its own numbered column.
For more information, see:
This helps you decide whether the performance gains offered by using the specified Summary Combinations are outweighed by the amount of tablespace they occupy.
|
Note: You can view and edit database storage properties for the selected Summary Combination by clicking Storage Properties.... For more information, see Section 15.9, "Editing Database Storage Properties." |
This creates the Summary Folder in the Business Area and the resulting summary tables or MVs (8.1.6+) in the database. The summary data is generated and the Summary Table/MV marked as Ready to use. For a Summary Folder with multiple Summary Combinations, the Summary Tables/MVs are generated in order with the summary tables/MVs with the largest number of Items generated first.
When the process is complete the new Summary Folder appears on the Summaries page of the work area.
This section describes how to create a new Summary Folder based on query statistics. This saves time and work as you don't have to choose the Items yourself.
This option creates a managed summary table/MV. It is only available if the Summary Management feature is enabled. See Section 2.2, "Summary Management," for more information.
This screen (Figure 15-18) is divided into three sections:
If the search time is significant, a progress bar is displayed.
All the queries that match the threshold values in section 1 are displayed in section 2. You may need to narrow or expand this list further by re-specifying the threshold values.
If a query in the list uses items and measures that are already summarized, a cube icon appears against the query in the left-most column.
To sort the list in section 2 on a column, click the relevant column heading.
This displays the query's Folders, Joins, and Items in section 3.
This displays Summary Wizard: Step 4 (see Figure 15-19, "Selecting the items to include in the Summary Folder") which enables you to select the Items to include in the Summary Folder. By default, the Selected Items list contains the Items and Measures from the query you selected on the previous page.
There are three ways of moving Items and Measures from one list to the other:
To select more than one Item or Measure at once, hold down Ctrl while you click on the Items.
Remember to include:
You can select any Axis Items and math functions, but if the Items are from different Folders, a Join must exist between the Folders.
This displays Summary Wizard: Step 5 which enables you to define all the Summary Combinations you require for your new Summary Folder (see Figure 15-20).
By default, the first Summary Combination (column 0) is a "catch all" Summary Combination which includes all the Items selected in Summary Wizard: Step 4.
Each Summary Combination is listed in its own numbered column.
For more information, see:
This helps you decide whether the performance gains offered by using the specified Summary Combinations are outweighed by the amount of tablespace they occupy.
|
Note: You can view and edit database storage properties for the selected Summary Combination by clicking Storage Properties.... For more information, see Section 15.9, "Editing Database Storage Properties." |
This creates the Summary Folder in the Business Area and the resulting summary tables in the database. The summary data is generated and the summary table marked as Ready to use. For a Summary Folder with multiple Summary Combinations, the summary tables are generated in order with the summary tables with the largest number of Items generated first.
When the process is complete the new Summary Folder appears on the Summaries page of the work area.
This section describes how to create a new Summary Folder based on external summary tables or Views.
For the privileges required to create summary folders see Section 15.3.1, "Prerequisites".
This registers an existing summary table created by another application. When running against an 8.1.6+ database this also creates a Materializeed View
For more information about external summary tables, see Section 15.1.3.2, "Managed versus external summary tables/MVs."
This displays all the database columns found in the external summary table in the Mapped items list.
There are three ways to do this:
|
Note: To remove the mapping between a database column in the external summary table and an Item in the EUL, select the relevant row in the Mapped items list and click the Exclude button (left arrow). |
For more information on the fields on this page of the Summary Wizard, click Help.
This creates the Summary Folder in the Business Area and finishes registering the external summary table.
When the process is complete the new Summary Folder appears on the Summaries page of the work area.
Summary Folder properties are accessible through Summary Properties dialog boxes. This section shows you how to enhance the user's view of the data by editing Summary Folder properties. Figure 15-30 shows an example Summary Properties dialog box.
This section describes how to edit a Summary Folder's properties.
There are four ways to do this:
)
For more information on the fields on this dialog box, click Help.
The following steps show you how to set common properties for more than one Summary Folder at a time:
There are three ways to do this:
)
All properties that are common to each of the selected Summary Folders are displayed. If the data for a field is not common to each of the selected Summary Folders, the field is blank.
For more information on the fields on this dialog box, click Help.
This section describes how to edit an existing Summary Folder.
Summary Folders can be edited whether they are valid or broken. A Summary Folder is considered broken when the summary table, MV or detail tables cannot be used. To find out more about why a Summary Folder is broken, use the View | Validate Folders option. You might want to edit a broken summary to investigate the composition of its Summary Folders and remove any broken folders.
Valid managed summaries are represented by a cube with a clock face (
) valid managed summaries are represented by a cube (
). Broken summaries are represented by a warning triangle (
).
) next to a Business Area to display its available Summaries (
).
There are two ways to do this:
The Edit Summary dialog box is divided into four pages. Each page corresponds to a page of the Summary Wizard:
For more information on the fields on this dialog box, click Help.
) following an edit, any modifications made in Discoverer are not written to the summary table or MV. Where a summary is valid (
or
) following an edit, any modifications made in Discoverer are also written to the summary table/MV.
This section describes what happens when a Summary Folder is refreshed, how to manually refresh one or more Summary Folders and why you need to refresh after you import/export across database versions (i.e. from pre-8.1.6 to 8.1.6+).
With an 8.1.6+ database the server's own refresh mechanism is used (this can be an incremental refresh) depending on your refresh settings.
Whenever a Summary Folder is refreshed, the following actions are performed by Discoverer:
To select more than one Summary Folder, hold down the Ctrl button while you click each Summary Folder that you want to select.)
There are two ways to do this:
This opens the Refresh Summaries dialog box (see Figure 15-32).
Perform incremental refresh (Oracle 8.1.6+ only)
During a summary refresh with pre-Oracle 8.1.6 databases, a full table scan is carried out whereas with Oracle 8.1.6+ the option exists to carry out an incremental refresh (which is faster).
For further information on the conditions required for incremental refresh please refer to Oracle8i Data Warehousing Guide Release 2 (8.1.6) Part Number A76994-01.
|
Note: Sometimes it is useful to refresh a summary after some external event has completed, such as the loading of data into a warehouse. You can use the Command Line option to refresh a summary from a batch command file to do this. See Appendix D.1 for more information. |
When you export a Business Area with Summary Folders from a pre-8.1.6 database and then import it into an 8.1.6 database, MVs need to be created for these Summary Folders. For the server to create the MVs, you must refresh the Summary Folders in Discoverer.
When you export a Business Area with Summary Folders from a 8.1.6+ database and then import it into a pre-8.1.6 database, Discoverer needs to be able to convert these Summary Folders to summary tables instead of to MVs. For Discoverer to do this, you must refresh the Summary Folders.
This section describes how to display the status of managed summary tables.
There are two ways to do this:
The summary table's status is shown in the Status Bar at the bottom of the dialog box.
Additional diagnostic information can be achieved when in the Summaries tab by using the View | Validate Folders option. This will display errors where applicable (see Chapter 6.9, "Validating Folders").
For more information on the possible status messages, click Help.
This section describes how to delete Summary Folders.
To select more than one Summary Folder at once, hold down Ctrl while you click on the Summary Folders.
There are three ways to do this:
This opens the Confirm Delete dialog box.
This section describes how to edit the various database storage properties associated with Managed summary tables. Editing database storage properties is an advanced topic that gives you control of how Summary Combinations are stored in the database.
There are two places where you can do this:
This displays the Database Storage Properties dialog box (see Figure 15-34).
The Database Storage Properties dialog is divided into a number of tabs:
For information on the fields on this dialog box, click Help.
Oracle 8.1.6+ databases support incremental refresh, when available, enabling you to work with large data warehouses/databases. Parallelism is also supported for the refresh operation.
For further information on the conditions required for incremental refresh please refer to Oracle8i Data Warehousing Guide Release 2 (8.1.6) Part Number A76994-01.
For further details click Help
|
 Copyright © 2000 Oracle Corporation. All Rights Reserved. |
|
)