Skip Headers

Oracle Discoverer Administrator Administration Guide 10g (9.0.4) Part Number B10270-01 |
Home | Contents | Index

# 14
Creating summary folders manually

## Creating summary folders manually

This chapter explains how you create summary folders manually using Discoverer Administrator (i.e. instead of using Automated Summary Management), and contains the following topics:

- "What is manual summary folder creation?"

- "What are the prerequisites for creating summary folders manually in Discoverer?"

- "What are summary combinations?"

- "What to consider when defining summary combinations?"

- "Guidelines for setting up summary combinations"

- "When will an expression use a summary folder?"

- "How to create summary folders based on items in the EUL"

- "How to create summary folders based on users' queries"

- "How to create summary folders based on external summary tables"

- "How to manually refresh a summary folder"

- "How to edit the properties of summary folders"

- "How to edit summary folders"

- "How to edit database storage properties of summary combinations for a summary folder"

- "How to delete summary folders"

- "How to view the status of the summary tables/materialized views for a summary folder"

## What is manual summary folder creation?

Manual summary folder creation is the process of creating summary folders yourself, instead of using Discoverer's Automated Summary Management (ASM) functionality (for more information, see Chapter 13, "Managing summary folders"). It is recommended that you use ASM to create summary folders. However, you might choose to create summary folders yourself, if you want to:

- manually select the combination of EUL items (e.g. when you know the items to use in the summary folder)

- use query performance statistics to choose from a selection of summary folder suggestions (based on queries that have been run)

- use a summary table or view created and/or maintained by an external application (e.g. an external summary table)

You choose whether to create summary folders using ASM or manually in the first step of the Summary Wizard. To create summary folders manually you select the option I want to specify the summaries myself and complete one of the following tasks:

- "How to create summary folders based on items in the EUL"

- "How to create summary folders based on users' queries"

- "How to create summary folders based on external summary tables"

For information about summary folders and how Discoverer creates and maintains them automatically see Chapter 13, "Managing summary folders".

## What are the prerequisites for creating summary folders manually in Discoverer?

To create summary folders, the following requirements must be met:

- The database must support PL/SQL.

- The DBMS_JOB package must be installed for scheduling summary folder creation and refresh. For more information see Chapter 13, "How to confirm that DBMS_JOB is installed for summary management".

- The User ID that you intend to use to create the summary folders must have the following database privileges:

  - CREATE TABLE

  - CREATE VIEW

  - CREATE PROCEDURE

  - SELECT ON V_$PARAMETER

  - CREATE/DROP/ALTER ANY MATERIALIZED VIEW (Oracle 8.1.7 (or later) Enterprise Edition databases)

  - GLOBAL QUERY REWRITE (Oracle 8.1.7 (or later) Enterprise Edition databases)

  For more information, see Chapter 13, "How to use SQL*Plus to grant the privileges required to create summary folders".

- The User ID that you intend to use to create the summary folders must have sufficient quota in their default tablespace. For more information, see Chapter 13, "How to determine and reset tablespace quotas".

#### Notes

- If a database user was created in Discoverer 3.1 against an Oracle 8.1.7 (or later) Enterprise Edition database, it must have additional privileges granted to enable them to create summary folders (for more information, see "What are the prerequisites for creating summary folders with ASM?").

- If you want Discoverer to use external summary tables, the EUL owner:

  - must have SELECT WITH GRANT access to the external summary tables

  - must have been granted SELECT WITH GRANT access explicitly (i.e. not via a database role)

## What are summary combinations?

Summary combinations are groupings of items that make up a summary folder. A summary combination maps directly to a materialized view or summary table in the database. Discoverer creates materialized views or summary tables based on the summary combinations you create. Each summary combination defines a different way of combining two or more items in a summary folder. If a Discoverer Plus user executes a query with a combination of items that closely matches those specified in a particular combination, the query will be run against a summary table or materialized view instead of the detail data, resulting in improved response times.

For example, you might create a summary folder against two folder items, Product Key and Product Type.

You might want the summary folder to be used only when the Product Key and Product Type items are used together.

For this example you would not add any summary combinations but accept the default combination, as follows:

Combinations | Combination 1 (default)
--- | ---
Product Key | selected
Product Type | selected

Alternatively, you might want the summary folder to be used for any of the following combinations:

- Product Key and Product Type (Combination 1)

- Product Key (Combination 2)

- Product Type (Combination 3)

For this example you would add two summary combinations and select the check box next to the items that you want to use as follows:

Combinations | Combination 1 (default) | Combination 2 | Combination 3
--- | --- | --- | ---
Product Key | selected | selected | not selected
Product Type | selected | not selected | selected

You can define as many combinations as you need for each summary folder using the Summary Wizard.

#### Figure 14-1 The relationship between summary combinations, summary folders and summary tables/materialized views

Text description of the illustration sumcomb.gif

The diagram above illustrates that for every summary combination, Discoverer creates a materialised view or summary table in the database.

If you add multiple summary combinations, Discoverer (where possible) builds the higher level combinations (i.e. combinations with a greater number of items) using the lower level combinations (i.e. combinations with fewer items). This improves performance because:

- the detail tables do not have to be queried so often

- summary folders are refreshed more quickly

For example, a summary folder might contain two summary combinations:

- month, region and revenue

- year, region and revenue.

Discoverer aggregates the data for the revenue by month and region by accessing the detail data directly. Discoverer then uses data aggregated for the first combination to aggregate the data for the second combination, saving both processing and CPU overhead.

Note: Summary combinations are only available when Discoverer manages the refresh of a summary folder.

## What to consider when defining summary combinations?

There are three things to consider when defining summary combinations:

- how much database space is required to store summary data?

- how quickly do you want a query to run?

- how often is a query run?

The key to good summary folder design is to create the most appropriate summary combinations for the pattern of system usage.

Typically you will want queries that are run most often to run the most quickly, even if this requires more database space. Similarly, it is usually desirable for queries that run less frequently to use less database space, even if this means the queries run more slowly.

When creating summary combinations, look for:

- summary combinations for popular and frequent queries

  If you are creating a summary combination for a popular query, include all the items and joins used in the query. Note that such a summary combination might require considerable database space.

- summary combinations that meet ad hoc, less frequent queries

  Summary combinations for a more ad hoc environment (where queries are far less predictable) are typically based on different combinations of keys in the main fact table.

For example, in the summary tables below, the columns EUL_SUM200801 and EUL_SUM200802 are mapped to appropriate items in the Sales Fact folder.

#### Figure 14-2 Sample summary tables and joins to fact and dimension tables

Text description of the illustration sumtab4.gif

Key to the figure above:

1. join from the Sales Fact table to Product table using the Product Key

2. join from the Sales Fact table to Fiscal Date table, using the Time Key

3. join from the Sales Fact table to Store table, using the Store Key

4. join from the summary table EUL_SUM200801 to the Product table using the Product Key

5. join from the summary table EUL_SUM200801 to the Fiscal Date table using the Time Key

6. join from the summary table EUL_SUM200802 to the Fiscal Date table using the Time Key

7. join from the summary table EUL_SUM200802 to the Store table using the Store Key

8. EUL_SUM200801 and EUL_SUM200802 represent summary tables that Discoverer creates in the database

Depending on the query, Discoverer will join a summary table to one or more of the dimension tables (i.e. Store, Product, or Fiscal Date). The dimension table must be joined to the fact table by items defined in the EUL, and the summary table must contain the foreign key items in the fact folder.

For example, if a user requests Product Category, Month and SUM(Dollar Profit), Discoverer will join EUL_SUM200801 to Product and Fiscal Date to obtain results. Discoverer knows about the foreign and primary keys between the Sales Fact table and Product and Fiscal Date, and can apply them to EUL_SUM200801.

## Guidelines for setting up summary combinations

We suggest that you build summary combinations in stages. Concentrate first on frequent queries, then on less frequent queries, and finally create a catch all summary combination. Guidelines for setting up summary combinations are as follows:

Query coverage | Number of items | Notes
--- | --- | ---
Frequent queries | 3-4 | Create many summary combinations that combine only three or four axis items (dimension values). This minimizes space and maximizes performance gain.
Less frequent queries | 5-7 | Create less summary combinations that combine, for example five to seven axis items. More space is required but large performance gains are still realized. The more items you create in a summary combination the wider the variety of queries that is served.
A catch all | all | When you choose to specify summaries yourself, create one summary combination that contains all of the items in a summary folder (the total number must be less than the number of items in the source folder(s)). Although the speed of the query results is slower, performance is still better than if the detailed tables are queried. Discoverer Administrator creates this summary combination automatically

Include the following in summary combinations:

- data point items

  Additional data point items take up little extra room in the summary tables.

- aggregates (with the possible exception of STDEV and VARIANCE, which are rarely used)

  Multiple aggregates do not require much space and can improve performance significantly. Remember that AVG requires the inclusion of SUM and COUNT, which Discoverer uses to calculate the average.

#### Notes

- You do not have to include items at all levels of a hierarchy in a summary combination. If you include items at the bottom level, queries that use items higher up in the hierarchy can still use the summary table. However, for this to work the summary folder must contain a foreign key to the folder that contains the hierarchy. Creating a summary combination for all levels in a hierarchy achieves only a marginal performance gain. For example, the hierarchy from Year to Quarter only saves a 4:1 aggregation because quarters are added up to years.

## When will an expression use a summary folder?

An expression will use a summary folder except under specific conditions. For this reason it is useful to be able to identify when an expression will use a summary folder. The following examples show when an expression will use a summary folder, if that summary folder contains the item SUM(Salary) and SUM(Comm):

Expression | Use summary table | Notes
--- | --- | ---
SUM(Salary + Comm) | No | This expression will not use a summary folder even though SUM(Salary) and SUM(Comm) are available as summarized items. This occurs because SUM(Salary + Comm) is not relationally equal to SUM(Salary) + SUM(Comm). Because the results could be wrong, the expression will not be used.
SUM(Salary) * 12 | Yes | You can query expressions such as this, and as SUM(Salary) is summarized it will use the summary folder.
NVL(SUM(Comm),0) | Yes | Expressions that use SQL functions such as NVL(SUM(Comm),0) will use a summary because SUM(Comm) is available in a summary.
SUM(NVL(Comm,0)) | No | An expression such as SUM(NVL(Comm,0)) will not use a summary, because no direct match exists between a part of the expression and the available summarized item, SUM(Comm).

In essence, an expression will only use a summary folder when the expression or parts of it and the summarized expression are relationally equal.

## How to create summary folders based on items in the EUL

Use this option to manually select combinations of EUL items that you want to include in a summary folder.

You might select this option if, for example:

- you have switched off the Collect Query Statistics privilege (for more information, see Chapter 6, "How to specify the tasks a user or role (responsibility) can perform")

- you want to manually select the combinations of EUL items that make up a summary folder

To create summary folders based on items in the EUL.

1. Select Insert | Summary to display "Summary Wizard: Step 1 dialog".

   #### Figure 14-3 Summary Wizard: Step 1 dialog

   Text description of the illustration swz1.gif
2. Select the I want to specify the summaries myself radio button and click Next to display "Summary Wizard: Step 2 dialog".

   #### Figure 14-4 Summary Wizard: Step 2 dialog

   Text description of the illustration swz2eul.gif
3. Select From items in the End User Layer to create a summary folder.

This option is only available if the summary folders feature is enabled. For more information, see Chapter 13, "How to configure the database for summary folders".

1. Click Next to display the "Summary Wizard: Step 3".

   #### Figure 14-5 Summary Wizard: Step 3 dialog

   Text description of the illustration swz3eul.gif

1. Move the items that you want to include in the new summary folder from the Available items list to the Selected items list.

   You can select more than one item at a time by holding down the Ctrl key and clicking another item.

   Remember to include:

   - all aggregate functions for each data point item - for more information, see "Guidelines for setting up summary combinations".

   - items from complex folders (where necessary) - for more information, see Chapter 15, "What are the conditions for query rewrite by Discoverer?".

   - derived items (where necessary) - for more information, see Chapter 15, "What are the conditions for query rewrite by Discoverer?".

   Note: You can select any items and math functions. However, if you select items from different folders, a join must already exist between the folders.

1. Click Next to display the "Summary Wizard: Step 4" to define the summary combinations in the new summary folder.

   #### Figure 14-6 Summary Wizard: Step 4 dialog

   Text description of the illustration swz4eul.gif

The Summary Wizard: Step 4 dialog enables you to define the summary combinations in the new summary folder.

By default, all the items you selected in the Summary Wizard: Step 3 are included in the first summary combination (in column 0).

1. Click Add Combination to add a new summary combination.

   The summary combination appears in a new numbered column.

2. Select the relevant check boxes to include items in the summary combination.

3. Add as many summary combinations as necessary

   For more information, see "What are summary combinations?"

   Hints:

   - If you are not sure whether the performance gains offered by using the specified summary combinations are outweighed by the amount of tablespace they occupy, click Estimate Space.

   - You can view and edit database storage properties for a selected summary combination by clicking Properties. For more information, see "How to edit database storage properties of summary combinations for a summary folder".

   - To remove an unwanted summary combination, select the relevant column number and click Remove Combination.

4. Click Next to display the "Summary Wizard: Step 5" to specify a refresh schedule for the summary folder.

   #### Figure 14-7 Summary Wizard: Step 5 dialog

   Text description of the illustration swz5eul.gif
5. Select the Automatically refresh this summary folder check box to specify the date, time and frequency that Discoverer will automatically refresh this summary folder.

Hint: Do not select this check box if the data is static and will not change, or if you want to refresh the summary folder manually. For more information about manually refreshing summary folders, see "How to manually refresh a summary folder".

1. Set the date and time you want Discoverer to start the first refresh.

2. Set the Repeat every fields for the refresh interval you want.

   The refresh period you specify here is the period of time that will elapse before Discoverer refreshes and updates the data. This refresh pattern will continue until you change the settings.

3. Click Next to display "Summary Wizard: Step 6".

   #### Figure 14-8 Summary Wizard: Step 6 dialog

   Text description of the illustration swz6eul.gif

1. Specify a name and description for the summary folder.

2. Specify when you want to build this summary folder as follows:

   - select the Build immediately radio button if you have smaller summary tables, or if you want to build the summary tables immediately.

   - Select the Build at radio button if you have larger summary tables that are best built at off-peak hours. Enter the time and date you want to build the summary tables.

3. Click Finish.

When Discoverer builds the summary folder, it creates:

- a summary folder and summary tables (Oracle Standard Edition databases)

- a summary folder and materialized views (Oracle 8.16 databases or later)

The build process generates the summary data and marks the summary table/materialized view as Ready to use.

When the process completes Discoverer displays the new summary folder in the "Workarea: Summaries tab".

## How to create summary folders based on users' queries

Query performance statistics are automatically generated when the Collect Query Statistics privilege is switched on for users (for more information, see Chapter 6, "How to specify the tasks a user or role (responsibility) can perform").

You can create a new summary folder based on query performance statistics rather than choosing the items yourself. Discoverer suggests the summary folder items for you based on the query performance statistics that are generated from Discoverer end user queries.

You might select this option if you know that you want to create one or more summary folders based upon specific queries and you do not necessarily want Discoverer to create any other summary folders.

To create summary folders based on query performance statistics:

1. Choose Insert | Summary to display "Summary Wizard: Step 1 dialog".

   #### Figure 14-9 Summary Wizard: Step 1 dialog

   Text description of the illustration swz1slf.gif
2. Select the I want to specify the summaries myself radio button and click Next to display "Summary Wizard: Step 2 dialog".

   #### Figure 14-10 Summary Wizard: Step 2 dialog

   Text description of the illustration swz2qp.gif
3. Select the Using query performance statistics option.

   This option creates a summary table or materialized view. It is only available if the summary management feature is enabled. For more information see Chapter 13, "How to configure the database for summary folders".

4. Click Next to display the "Summary Wizard: Step 3".

   #### Figure 14-11 Summary Wizard: Step 3 dialog

   Text description of the illustration swz3qp.gif

The Summary Wizard: Step 3 dialog is divided into three sections as follows:

Section number | Purpose
--- | ---
Section 1 | Specify search criteria to obtain statistics on query performance. This section enables you to specify search criteria to obtain statistics on query performance.
Section 2 | Select a query to be summarized. This section shows queries that meet the search criteria.
Section 3 | Verify items to be summarized. This section shows a selected query's folders, joins and items.

1. Specify the values in Section 1 that you want to use to search for previously run queries.

2. Click Search.

   If the search time is significant, Discoverer displays a progress bar.

   All the queries that match the threshold values in Section 1 are displayed in Section 2. To narrow or expand this list further, re-specify the threshold values.

   If a query in the list uses items that are already summarized, a cube icon appears beside the query.

   To sort the display order of items in a column in Section 2, click the relevant column heading.

3. Select the row that represents the query you want to summarize from the list in Section 2.

   The query's folders, joins, and items appear in Section 3.

4. Click Next to display the "Summary Wizard: Step 4".

   #### Figure 14-12 Summary Wizard: Step 4 dialog

   Text description of the illustration swz4qp.gif

The Summary Wizard: Step 4 dialog enables you to select items to include in the summary folder. By default, the Selected items list contains the items from the query you selected on the previous page of the Summary Wizard.

1. Move the items that you want to include in the new summary folder from the Available items list to the Selected items list.

   You can select more than one item at a time by holding down the Ctrl key and clicking another item.

   Remember to include:

   - all aggregate functions for each data point item (for more information, see "Guidelines for setting up summary combinations")

   - items from complex folders, where necessary (for more information, see Chapter 15, "What are the conditions for query rewrite by Discoverer?")

   - derived items, where necessary (for more information, see Chapter 15, "What are the conditions for query rewrite by Discoverer?")

   Note: You can select any items and math functions. However, if you select items from different folders, a join must already exist between the folders.

2. Click Next to display the "Summary Wizard: Step 5".

   #### Figure 14-13 Summary Wizard: Step 5 dialog

   Text description of the illustration swz5qp.gif

The Summary Wizard: Step 5 dialog enables you to define the summary combinations in the new summary folder.

By default, all the items you selected in Summary Wizard: Step 4 are included in the first summary combination (in column 0).

1. Click Add Combination to add a new summary combination.

   The summary combination appears in the new numbered column.

2. Select the relevant check boxes to include items in the new summary combination.

3. Add as many summary combination as necessary.

   For more information, see "What are summary combinations?".

4. Click Estimate Space to help you decide whether the performance gains offered by using the specified summary combinations are outweighed by the amount of tablespace they occupy.

5. Click OK.

   Note: You can view and edit database storage properties for a selected Summary Combination by clicking Properties. For more information, see "How to edit database storage properties of summary combinations for a summary folder".

   Hint: To remove an unwanted summary combination, select the relevant column number and click Remove Combination.

6. Click Next to display the "Summary Wizard: Step 6" to specify a refresh schedule for the summary folder.

   #### Figure 14-14 Summary Wizard: Step 6 dialog

   Text description of the illustration swz6qp.gif
7. Select the Automatically refresh this summary folder check box to specify the date, time and frequency of automatic refresh for this summary.

Hint: Do not select the check box if the data is static and will not change, or if you want to refresh the summary folder manually. For more information about manually refreshing summary folders, see "How to manually refresh a summary folder".

1. Set the Date and Time you want Discoverer to start the first refresh.

2. Set the Repeat every fields for the refresh interval you want.

   The refresh period you specify here is the period of time that will elapse before Discoverer refreshes and updates the data. This refresh pattern will continue until you change the specification.

3. Click Next to display the "Summary Wizard: Step 7".

   #### Figure 14-15 Summary Wizard: Step 7 dialog

   Text description of the illustration swz7qp.gif
4. Specify a name and description for the summary folder.

5. Specify when you want to build this summary folder.

6. Click Finish.

When Discoverer builds the summary folder, it creates:

- a summary folder and summary tables (Oracle Standard Edition databases)

- a summary folder and materialized views (Oracle 8.1.7 or later Enterprise Edition databases)

The build process generates the summary data and marks the summary table/materialized view as 'Ready to use'.

When the process is complete Discoverer displays the new summary folder in the "Workarea: Summaries tab".

#### Notes

The business area named Query Statistics that is supplied with Discoverer includes a workbook that analyzes the following:

- query usage

- the items most frequently used in queries

- the folders containing the items

- query execution times

## How to create summary folders based on external summary tables

This section describes how to create a new summary folder based on summary tables created by an application other than Discoverer. Summary tables created by an application outside Discoverer are called external summary tables. Note that this task also applies to creating summary folders based on external views.

To create summary folders based on external summary tables:

1. Choose Insert | Summary to display "Summary Wizard: Step 1 dialog".

   #### Figure 14-16 Summary Wizard: Step 1 dialog

   Text description of the illustration swz1slf.gif
2. Select the radio button I want to specify the summaries myself and click Next to display "Summary Wizard: Step 2 dialog".

   #### Figure 14-17 Summary Wizard: Step 2 dialog

   Text description of the illustration swz2ext.gif
3. Select the Registering an external summary table radio button.

   The result of completing this task is that the database will create a materialized view from the summary table created by the external application.

   Note that Discoverer will continue to create summary tables under the following conditions:

   - If you run Discoverer against an Oracle Standard Edition database.

     For more information, see Chapter 15, "What is different about summary folders that are based on external summary tables?".

   - If you map an external view to EUL items against an Oracle 8.1.7 (or later) Enterprise Edition database.

     For more information, see Chapter 15, "What is different between mapping external summary tables and views to EUL items, with Oracle 8.1.7 (or later) Enterprise Edition databases?".

4. Click Next to display the "Summary Wizard: Step 3 dialog".

   #### Figure 14-18 Summary Wizard: Step 3 dialog

   Text description of the illustration swz3ext.gif
5. Click Select to display the "Choose user or table/view dialog".

   #### Figure 14-19 Choose table or view dialog

   Text description of the illustration swz3eta.gif
6. Select the database (from the drop down list) that contains the external summary table that you want to register.

   Note: When connected to an Oracle 8.1.7 (or later) Enterprise Edition database, Oracle prevents a user from registering an external summary over a database link. This is because the database does not allow a materialized view to be created over a database link. However, this may be achieved by creating a view in the database that the EUL is in and referencing the external summary in the view. This view may then be registered within Discoverer as an external summary.

7. Select the external summary table that you want to register with Discoverer Administrator.

8. Click OK to display all the database columns found in the external summary table in the mapped items list.

   #### Figure 14-20 Summary Wizard: Step 3 dialog

   Text description of the illustration swz3etm.gif

   You now map each database column in the external summary table to a corresponding item in the EUL.

1. For each database column, drag the corresponding item from the Available items list onto the database column in the Mapped items list

   Hint: If several items from the same folder correspond to columns in the external summary table, you can drag and drop the folder onto an item in the Mapped items list. Discoverer Administrator attempts to map the correct items to the database columns using the item names. Note that you will have to map items to any columns for which Discoverer Administrator cannot identify corresponding items.

   Hint: 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 left arrow button.

2. Click Next to display the "Summary Wizard: Step 4" to specify a refresh schedule for the summary folder.

   #### Figure 14-21 Summary Wizard: Step 4 dialog

   Text description of the illustration sw48i1e.gif
3. Select the Manage the refresh of this summary check box if you want Discoverer to manage the refresh of this external summary table.

4. Select the Automatically refresh this summary folder check box to specify the date, time and frequency of automatic refresh for this summary.

Hint: Do not select the check box if the data is static and will not change, or if you want to refresh the summary folder manually. For more information about manually refreshing summary folders, see "How to manually refresh a summary folder".

1. Set the Date and Time you want Discoverer to start the first refresh.

2. Set the Repeat every fields for the refresh interval you want.

   The refresh period you specify here is the period of time that will elapse before Discoverer refreshes and updates the data. This refresh pattern will continue until you change the specification.

3. Click Next to display "Summary Wizard: Step 5".

   #### Figure 14-22 Summary Wizard: Step 5 dialog

   Text description of the illustration swz5ext.gif
4. Specify a name and description for the summary folder

5. Click Finish to create the summary folder in the business area and register the external summary table with Discoverer.

When the process is complete, Discoverer displays the new summary folder in the "Workarea: Summaries tab".

#### Notes

- Summary combinations are only available when Discoverer manages the refresh of a summary folder. When Discoverer uses external summary tables, summary combinations are not available.

## How to manually refresh a summary folder

This section describes how you can refresh a summary folder. You might need to refresh a summary folder if the database has changed.

To manually refresh a summary folder:

1. Select the summary folder that you want to refresh in the "Workarea: Summaries tab".

2. Choose Tools | Refresh Summaries to display the "Refresh Summaries dialog".

   Note: Discoverer does not display the Refresh Summaries dialog for externally managed summary folders.

   #### Figure 14-23 Refresh Summaries dialog

   Text description of the illustration sumfref.gif
3. Specify when you want to refresh the selected summary folder as follows:

   - select the Refresh the summary immediately radio button if you want to refresh the summary folder immediately

   - select Schedule the refresh at a later time radio button if you want to refresh the summary folder at a specified time

4. If you selected Schedule the refresh at a later time, specify the date and time when you want the refresh to begin.

5. To perform an incremental refresh, select the check box.

   You might want to perform an incremental refresh when the detail data in the database tables has changed little since the last refresh. Performing an incremental refresh saves time, as it updates only those changes committed to the database since the last refresh.

   For further information on the conditions required for incremental refresh, see Oracle9i Data Warehousing Guide.

#### Notes

- Sometimes you will want to refresh a summary folder when a process outside Discoverer has completed (e.g. the loading of data into a data warehouse). You can refresh a summary folder from a batch command file using the Discoverer's command line interface option (for more information, see Chapter 21, "/refresh_summary").

For more information about refreshing summary folders, see:

- Chapter 15, "About refresh options for Oracle 8.1.7 (or later) Enterprise Edition databases"

- Chapter 15, "About refreshing summary folders following import between Oracle databases"

## How to edit the properties of summary folders

This section describes you how to edit the properties of a summary folder.

To edit the properties of a summary folder:

1. Select one or more summary folders in the "Workarea: Summaries tab"

2. Choose Edit | Properties to display the "Summary Properties dialog".

   #### Figure 14-24 Summary Properties dialog

   Text description of the illustration sump1.gif

   Hint: You can select more than one summary folder at a time by holding down the Ctrl key and clicking another summary folder. If all the selected folders have the same value for a particular property, that value is shown. If the selected folders have different values for a particular property, no value is shown for the property. Any changes you make here are applied to all selected summary folders.

1. Make your changes as required.

2. Click OK to save the changes you have made.

## How to edit summary folders

You might want to edit a summary folder to:

- change its name or description

- view or edit its properties

- refresh it

- alter its composition (e.g. change items or combinations)

- view or modify database storage properties

- investigate the composition of a broken summary folder and remove broken folders

#### Notes

- You can edit summary folders, regardless of whether they are valid or broken.

To edit a summary folder:

1. Display the "Workarea: Summaries tab".

2. Select the + symbol next to a business area to display its available summary folders.

3. (optional) Choose View|Validate Summaries to display diagnostic summary folder information, if required.

4. Click the summary folder and choose Edit | Edit...

   #### Figure 14-25 Edit Summary dialog: General tab

   Text description of the illustration sumedg.gif
5. Edit the summary folder as required.

   The Edit Summary dialog is divided into four tabs. Each tab corresponds to a step of the Summary Wizard as follows:

   - "Edit Summary dialog: Choose items tab" - use this tab to specify the items in a summary folder.

   - "Edit Summary dialog: Combinations tab" - use this tab to edit, add, or delete summary combinations. You can also use this tab to view and configure database storage properties of materialized views or summary tables (depending on database version).

     Note: The Combinations tab is not displayed for summary folders that are based upon external summary tables.

   - "Edit Summary dialog: Refresh tab" - use this tab to set the refresh and refresh interval of the summary folder.

   - "Edit Summary dialog: General tab" - use this tab to edit the name, description and to view the build time of the summary folder.

6. Click OK.

#### Notes

- If a summary folder is broken, you can view the status of folders within the summary folder by choosing View | Validate Summaries. If Discoverer detects a problem with a folder within a summary folder, an error message is displayed beside the folder.

- If a summary folder remains broken following an edit, any modifications made in Discoverer are not written to the materialized view or summary table. Where a summary folder is valid following an edit, any modifications made in Discoverer are also written to the materialized view/summary table.

## How to edit database storage properties of summary combinations for a summary folder

You can use Discoverer Administrator to control how summary combinations are stored in the database by editing the database storage properties.

Note: Database storage properties are not available for summary folders that are based on external summary tables. For more information, see Chapter 15, "What is different about summary folders that are based on external summary tables?".

To edit the database storage properties of summary combinations for a summary folder:

1. Select a summary folder from the "Workarea: Summaries tab".

2. Choose Edit | Edit... to display the Edit Summary dialog.

3. Display the "Edit Summary dialog: Combinations tab".

4. Select the summary combination with database storage properties that you want to edit.

5. Click Properties to display the Database Storage Properties dialog (see "Database Storage Properties dialog: List of tabs").

6. Display the appropriate tab and edit the properties as required.

For more information about the Database Storage Properties dialog tabs, see:

- "Database Storage Properties dialog: Properties tab"

- "Database Storage Properties dialog: Mappings tab"

- "Database Storage Properties dialog: Tablespaces tab"

- "Database Storage Properties dialog: Materialized Views tab"

## How to delete summary folders

This section describes how you can delete a summary folder. When you delete a summary folder, the underlying summary tables or materialized views are also deleted.

To delete a summary folder:

1. Select the summary folder that you want to delete from the "Workarea: Summaries tab".

   Hint: You can select more than one summary folder at a time by holding down the Ctrl key and clicking another summary folder.

2. Choose Edit | Delete to display the "Confirm Delete dialog".

   You can review the objects that might be affected by deleting this summary folder.

3. (optional) To see the potential impact of the deletion:

   1. Click Impact to display the "Impact dialog".

      #### Figure 14-26 Impact dialog

      Text description of the illustration impacta.gif

      The Impact dialog enables you to review the other EUL objects that might be affected when you delete a summary folder.

      Note: The Impact dialog does not show the impact on workbooks saved to the file system (i.e. in .dis files).

   2. (optional) Select a row to view text at the bottom of the list, indicating what affect the current action will have on the EUL object.

   3. When you have finished reviewing the impact of deleting the summary folder, click Close to close the Impact dialog.

1. If you still want to delete the selected summary folder click Yes.

   Discoverer Administrator deletes the selected summary folder from the EUL and drops the summary table/materialized view from the database.

## How to view the status of the summary tables/materialized views for a summary folder

This section describes how you can view the status of the summary table/materialized view for a summary folder.

To view the status of the summary table/materialized view for a summary folder:

1. Select the summary folder that contains the summary table or materialized view you are interested in from the "Workarea: Summaries tab".

2. Choose Edit | Edit... to display the Edit Summary dialog

3. Display the "Edit Summary dialog: Combinations tab".

4. Select the numbered column heading for the summary combination that corresponds to the summary table materialized view you are interested in.

   Discoverer displays the status of the summary table/materialized view in the status bar at the bottom of the dialog.

5. When you have finished viewing the status of the summary table/materialized view (represented by the summary combination), click OK to close the Edit Summary dialog.

#### Notes

- The status is not available for summary folders that are based upon external summary tables.

- To see additional diagnostic information in the "Workarea: Summaries tab" use the View | Validate Folders option. This displays any errors that exist within the folders of a summary folder.

  For more information about validating folders and summary status messages, see:

  - "Summary Properties dialog"

  - Chapter 5, "How to validate folders in a business area"

| Copyright © 1996, 2003 Oracle Corporation. All Rights Reserved. |
Home | Contents | Index