14 Managing Summary Folders

This chapter explains summary folders and how you manage them using Discoverer Administrator, and contains the following topics:

14.1 About folders and summary folders in Discoverer

Discoverer uses two kinds of folders:

  • folders - represent data

    Discoverer folders are classified as simple, complex, or custom. For more information, see Chapter 6, "Creating and Maintaining Folders".

  • summary folders - represent data that has been queried and the results saved for reuse

    Discoverer uses summary folders to improve query response times for end users.

14.2 What are summary folders?

Summary folders are a representation of queried data (created in Discoverer Administrator) that has been saved for reuse. The data is stored in the database in one of the following (depending on the edition of Oracle):

  • materialized views

    Discoverer uses materialized views to store summarized data in Oracle Enterprise Edition databases.

  • tables

    Discoverer uses tables to store summarized data in Oracle Standard Edition databases.

    The tables created by Discoverer to store summarized data are called Discoverer summary tables. For more information about Discoverer summary tables, see "What are Discoverer summary tables?".

Summary folders improve the response time of a query because the query accesses pre-aggregated and pre-joined data rather than querying against the detail database tables.

You can also direct Discoverer to use summary folders based on tables containing summary data that have been created by another application. These tables are known as external summary tables. You can specify that Discoverer refreshes external summary tables.

You can create summary folders in one of two ways:

The recommended method for creating summary folders in Discoverer is to use Automated Summary Management (ASM).

14.3 What are Discoverer summary tables?

Discoverer summary tables are database tables created by Discoverer Administrator that contain summarized data. Discoverer summary tables contain pre-aggregated and pre-joined data, and can improve query performance in Discoverer Plus and Discoverer Viewer.

Discoverer automatically recognizes when a summary table is available and rewrites the query to use the summary table. For more information, see "What is query rewrite?".

For example, when a query is run for the first time in Discoverer Plus it retrieves data from the detail tables. This may require a multiple table join and aggregation over thousands or millions of rows, which could take some time to complete. If Discoverer has created a suitable summary table, the same query retrieves data from the summary table and will return results in a few seconds. Both queries will produce the same results.

For more information about Discoverer and rewriting queries to summary tables with Oracle Standard Edition databases, see "Example illustrating the advantages of rewriting a query to use a summary table".

Note: Circumstances sometimes prohibit the creation of materialized views and result in the creation of Discoverer summary tables when running against Oracle databases (Enterprise Edition release 9.2.0.7 or later). For more information, see "What is different between mapping external summary tables and views to EUL items, with Oracle Enterprise Edition databases?".

14.4 What are materialized views?

Materialized views are database objects that contain the results of a query created by an Oracle Enterprise Edition database. The query results contain pre-aggregated and pre-joined data and can improve query performance in Discoverer Plus and Discoverer Viewer.

Oracle Enterprise Edition databases automatically recognize when a materialized view can be used to satisfy a query request. The database rewrites the query to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables or views.

Materialized views have the following characteristics:

  • Materialized views consume storage space.

    Unlike ordinary views, materialized views contain data resulting from a query against one or more detail tables.

  • Materialized views must be refreshed when data changes.

    The Oracle database maintains data in materialized views by refreshing the materialized views after changes are made to the detail tables.

    You can configure Discoverer:

    • to set the Refresh Type to incremental (that is, fast refresh, where only the changes made to the detail tables are refreshed) or full (that is, complete, where the database executes the defining subquery of the materialized view)

    • to set the Refresh to On demand (manually maintained by the Discoverer manager) or On commit (whenever a database transaction commits its changes to the detail tables), where the materialized view is in the same database as the detail tables

  • Materialized views are transparent to Discoverer users.

    Query rewrite transforms a SQL statement expressed in terms of the detail table into a statement accessing one or more materialized views that are defined on the detail tables. For more information, see "What is query rewrite?".

Notes

For more information about the differences between Discoverer summary tables and materialized views, see:

14.5 What is Automated Summary Management (ASM)

Automated Summary Management (ASM) is a Discoverer facility that creates and manages summary folders for you.

ASM simplifies the process of summary folder creation and maintenance by offering you a fully automated solution to summary folder management. When run periodically, ASM can automatically refine the summary folders it creates using query statistics gathered by Discoverer from user queries. ASM also provides guidance if you want to control the specification of default values during summary folder creation/maintenance.

14.6 How does ASM work?

ASM determines how to create summary folders by:

  • analyzing your database tables

  • using query statistics (when available)

  • using default values known as the ASM policy (for more information, see "What is the ASM policy?")

ASM automatically creates and maintains the best set of summary folders based on the above combination.

ASM converts your summary policy into a list of recommended summary folder definitions. You can either implement these recommendations without further intervention (the actual set of summary tables dynamically adjusts with system usage) or execution can be stalled until you have specifically sanctioned one or more of the recommendations.

ASM requires table space to create summary folders but you can adjust how much space in the Summary Wizard (for more information, see "How to run ASM using the Summary Wizard").

Note: ASM works on the EUL as a whole, not just the currently selected business areas. So ASM can use folders from all business areas in the current EUL.

14.7 What are the prerequisites for creating summary folders with ASM?

To create summary folders with ASM, 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 "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 (for more information, see "How to use SQL*Plus to grant the privileges required to create summary folders"):

    • CREATE TABLE

    • CREATE VIEW

    • CREATE PROCEDURE

    • SELECT ON V_$PARAMETER

    • CREATE/DROP/ALTER ANY MATERIALIZED VIEW (Oracle Enterprise Edition databases)

    • GLOBAL QUERY REWRITE (Oracle Enterprise Edition databases)

    • ANALYZE ANY

  • The User ID that you intend to use to create summary folders must have sufficient quota in the tablespace for the summary tables to be created (for more information, see "How to determine and reset tablespace quotas").

  • You must connect to Discoverer Administrator as the EUL owner.

  • If you are using an Oracle Enterprise Edition database, your folders must not contain SET operators (for example, UNION, UNION ALL, MINUS, INTERSECT). If ASM finds a folder that contains a SET operator, summary data will not be created for that folder.

Note: For more information about Oracle SET operators, see the Oracle Database SQL Reference Guide.

Note: A database user created in Discoverer may need additional privileges to enable them to create summary folders. The conditions that determine when this is true are detailed in the following table, that illustrates when a database user created in Discoverer requires additional privileges to create summary folders:

Version of Discoverer and database version under which the database user was created Does database user require further privileges to create summary folders? What action is required?
Discoverer 3.1. Yes, the database user requires further privileges to create summary folders. You must explicitly grant the required privileges to the database user (for more information about how to grant these privileges, see "How to use SQL*Plus to grant the privileges required to create summary folders").
Discoverer 4i against an Oracle Standard Edition database). Yes, the database user requires further privileges to create summary folders. Same action as above.
Discoverer 4i against an Oracle Enterprise Edition database. No, the database user requires no further privileges to create summary folders. No action is required.

14.8 What are the different ways to run ASM and when do you use them?

The following table shows the different ways that you can run ASM and when to use them:

How to run ASM? Why run ASM this way? For information see
Using the Summary Wizard. Discoverer leads you through the whole process. "How to run ASM using the Summary Wizard".
Using the Load Wizard after bulk load. Enables you to create summary folders during the load of a business area without having to access ASM independently. "How to run ASM after bulk load using the Load Wizard".
Using the command-line interface. Enables you to create summary folders without having to start Discoverer. "How to run ASM using the command-line interface".
Using a batch file and the Operating System scheduler. Automates the scheduling of ASM summary folder creation through the command line. "How to run ASM using a batch file and the operating system scheduler".

14.9 What is the ASM policy?

The ASM policy is a set of user defined constraints and options that enable you to control how ASM behaves and what summary folders it produces.

The ASM policy is divided into space options and advanced settings. In many cases you will only need to set the space options.

For more information about space options and advanced settings, see:

Discoverer provides default settings that ensure suitable summary folders are created and maintained without your intervention.

The minimum information required for an ASM policy is a tablespace name and an allocated amount of disc space. The tablespace defaults to the user tablespace, and a default amount of disc space is used. Both of these values can be changed if required (for more information, see "Summary Wizard (ASM): Step 3 Allocate Space dialog").

14.10 Why must you refresh summary data?

You must refresh summary data regularly to keep summary tables and materialized views consistent with the database. If the database changes often, summary tables and materialized views need to be refreshed accordingly to keep their data current with the underlying database.

For information about refreshing summary folders see:

Note: Sometimes it is useful to refresh a summary folder after a change has occurred (for example, the loading of data into a data warehouse). You can use the Discoverer command-line interface facility to automatically refresh a summary folder (for more information, see Chapter 22, "Discoverer Command-line Interface").

14.11 What happens when a summary folder is refreshed?

When a summary folder is refreshed (when running against Oracle Enterprise Edition databases), the database 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:

  • The summary folder is marked as unavailable.

  • The materialized view or summary table associated with summary folder is reconciled against its EUL metadata and if necessary the definition is updated.

  • All existing summary data that is about to be refreshed is deleted.

  • The summary data is regenerated and inserted into the corresponding materialized views or summary tables.

  • The summary folder is marked as available.

14.12 How to run ASM using the Summary Wizard

You need certain database privileges to run ASM (for more information about the privileges required to run ASM, see "What are the prerequisites for creating summary folders with ASM?").

To see a list of headings that point to more information about the Summary Wizard dialogs, see "Summary Wizard (ASM): List of dialogs".

To start the summary wizard:

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

    Figure 14-1 Summary Wizard dialog

    Surrounding text describes Figure 14-1 .

  2. Select the Have Discoverer recommend and create the best summaries option and click Next to display the "Summary Wizard: Step 2 dialog".

    Figure 14-2 Summary Wizard: Step 2 dialog

    Surrounding text describes Figure 14-2 .

    Note: To determine which summary folders to create Discoverer analyzes every folder that is to be involved in the summary folder process. Depending on the number and size of folders to be analyzed this could take some time to complete. Discoverer enables you to start and stop this process at your own pace.

  3. Click the Start button to begin the analysis:

    Your progress will be displayed during the analyze process.

  4. (optional) Click the Stop button to pause the analysis.

    You might want to pause the analysis to make changes to the default settings (using the Advanced Settings button) before resuming table analysis.

  5. (optional) Click the Continue button to resume the analysis.

    The analysis always resumes from the same point in the process at which the Stop button was clicked.

    Figure 14-3 Summary Wizard: Step 2 dialog

    Surrounding text describes Figure 14-3 .

    Note: If some folders cannot be analyzed Discoverer displays the "Not Analyzed dialog".

  6. When analysis is complete click Next to allocate space for summaries.

    Figure 14-4 Summary Wizard: Step 3 dialog

    Surrounding text describes Figure 14-4 .

    The graph plots the expected performance gain from allocating a certain amount of space for summary folders.

    The information displayed here is calculated during folder analysis (see previous step). If you make changes using any of the tabs in the Change Default Settings dialog Discoverer might recalculate the graph.

    For more information about settings see "Summary Wizard (ASM): Change default settings: List of dialog tabs".

  7. Select the tablespace in which to store summary data in the Tablespace to use for summaries list box.

    Note: We recommend you place summary data in a separate tablespace specifically intended for it. If such a tablespace does not exist, we strongly recommend you do not use the SYSTEM or TEMP tablespaces. For more information, ask your database administrator.

  8. Specify the amount of space to allocate for summary data in one of the following ways:

    • click the graph area and drag the pointer/red line to select the space you want to allocate for summaries

    • specify a figure in the Create summaries to fill field

    Note: The value beneath the Tablespace to use for summaries field can be less than the value specified in the Create summaries to fill field. This does not matter if you have set your tablespace to autoextend. With your tablespace set to autoextend the extra space needed will be added automatically to the database. If your tablespace is not set to autoextend the value Space (KB) must be less than the available space.

    Figure 14-5 Summary Wizard: Step 3

    Surrounding text describes Figure 14-5 .

  9. (optional) To view the summary folders that ASM will create/remove for you, click the Recommended Summaries button.

    For more information about recommended summaries, see the "Recommended Summaries dialog".

  10. (optional) To display the Change default settings dialog, click the Advanced Settings button.

    For more information about advanced settings, see "Summary Wizard (ASM): Change default settings: List of dialog tabs".

  11. Click Finish to generate summary folders based on the current settings.

    These settings include those made at the Recommended Summaries dialog or the Change Default Settings dialog.

Notes

14.13 How to run ASM after bulk load using the Load Wizard

When you use the Load Wizard to load a business area into the current EUL, you can choose whether to create a set of summary folders for this new business area. If you take this option then suitable summaries will be created after bulk load.

To run ASM after bulk load using the Load Wizard:

  1. Choose Insert | Business Area | From Database to start the Load Wizard (for more information about using the Load Wizard, see "What is the Load Wizard?").

  2. In step 4 of the Load Wizard click the option Summaries based on folders that are created.

    For more information, see "Load Wizard: Step 4 dialog".

Notes

  • The summary folders created during bulk load of a business area are derived from analyzing the tables and using the default summary policy.

    For more information about the ASM policy, see "What is the ASM policy?"

  • When database tables have just been loaded in the bulk load process, query statistics will not be available. Where Discoverer has been in use for a while, query statistics will have been gathered by Discoverer. Where query statistics are available they will be used by ASM to create more suitable summary folders.

  • You do not need to make any changes to summary policy settings for a bulk load ASM process. The default settings which are used should be adequate. If changes are made however, these settings then become the defaults.

14.14 How to run ASM using the command-line interface

To run ASM using the command-line interface:

  1. From the Start menu choose Run.

  2. Type in the appropriate command.

    For more information about the command-line interface, see Chapter 22, "Discoverer Command-line Interface". For more information about ASM commands and command modifiers see and "/asm".

14.15 How to run ASM using a batch file and the operating system scheduler

You can run ASM through the batch file/scheduler facility provided by your operating system. This way you specify when you want ASM to run including the scheduled intervals when you want to repeat the process.

By using the command-line syntax within a batch file, the process can be run automatically overnight or on a weekend.

Before you can run ASM from a batch file, you must do two things:

  1. Create a text file in a text editor (for example, Notepad).

  2. Using the correct command-line syntax, enter the command-line details as text into the batch file.

    For more information about ASM command-line syntax, see "/asm".

  3. Save the file as a .bat file (for example, asmsched.bat).

  4. Use the correct operating system commands to schedule the batch file.

    To learn how to schedule a batch file see your operating system documentation or help.

14.16 How to configure the database for summary folders

The summary management feature in Discoverer uses native features in the Oracle database management system (DBMS). This feature uses the same highly scalable and reliable processing procedures as the workbook scheduling capability and the setup for both features is similar. These procedures use standard packages in the DBMS called DBMS_JOB.

To enable the processing procedures for summary management in Discoverer you can check the following tasks:

14.16.1 How to confirm that DBMS_JOB is installed for summary management

To confirm that DBMS_JOB is installed for summary management:

  1. Start SQL*Plus (if it is not already running) and connect as the database administrator.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> CONNECT dba_user/dba_pw@database;

    Where dba_user is the database administrator and dba_pw is the database administrator password.

  2. Type the following at the command prompt:

    SQL> select * from all_objects where object_name='DBMS_JOB' and object_type = 'PACKAGE';

    If the statement returns no rows, use your database administrator to create the necessary packages.

To install DBMS_JOB and create the necessary packages for summary management, for Oracle databases (version 9.2.0.7 or later):

  1. Start SQL*Plus (if it is not already running) and connect as the SYS user, or a user to which the sysdba privilege has been granted.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> CONNECT sys/sys_pw@database AS SYSDBA;

    Where sys is the SYS user and sys_pw is the SYS user password.

  2. Type the following at the command prompt:

    SQL> start <ORACLE_HOME>/rdbms/admin/dbmsjob.sql;
SQL> start <ORACLE_HOME>/rdbms/admin/prvtjob.plb;

14.16.2 How to use SQL*Plus to grant the privileges required to create summary folders

You can use SQL*Plus to grant the privileges required to create summary folders in the following ways:

Notes

  • The <user> below is the database user for the person using Discoverer Administrator.

How to use SQL*Plus to manually grant the privileges required to create summary folders

To use SQL*Plus to manually grant the privileges required to create summary folders, for Oracle databases (version 9.2.0.7 or later):

  1. Start SQL*Plus (if it is not already running) and connect as the SYS user, or a user to which the sysdba privilege has been granted.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> CONNECT sys/sys_pw@database AS SYSDBA;

    Where sys is the SYS user and sys_pw is the SYS user password.

  2. Type the following at the command prompt:

    SQL> grant CREATE TABLE to <user>; 
SQL> grant CREATE VIEW to <user>; 
SQL> grant CREATE PROCEDURE to <user>; 
SQL> grant CREATE ANY MATERIALIZED VIEW to <user>; 
SQL> grant DROP ANY MATERIALIZED VIEW to <user>; 
SQL> grant ALTER ANY MATERIALIZED VIEW to <user>; 
SQL> grant GLOBAL QUERY REWRITE to <user> with admin option; 
SQL> grant ANALYZE ANY to <user>; 
SQL> grant SELECT ON V_$PARAMETER to <user>;

    Note: To grant SELECT on v_$parameter you must log in as the SYS user. If you are unable to login as the SYS user or are unsure about the SYS user name and password, see your database administrator.

How to use SQL*Plus and the eulasm.sql script to grant the privileges required to create summary folders

To use SQL*Plus and the eulasm.sql script to grant the privileges required to create summary folders:

  1. Start SQL*Plus (if it is not already running) and connect as the database administrator.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> CONNECT dba_user/dba_pw@database;

    Where dba_user is the database administrator and dba_pw is the database administrator password.

  2. Type the following at the command prompt:

    SQL> @'<ORACLE_HOME>\discoverer\util\eulasm.sql'

  3. When the script prompts you, enter the database user to which the script is to grant the privileges.

    ENTER value for username: <username>

    Note: To grant SELECT on v_$parameter you must log in as the SYS user. If you are unable to login as the SYS user or are unsure about the SYS user name and password, see your database administrator.

  4. Start SQL*Plus (if it is not already running) and connect as the SYS user, or a user to which the sysdba privilege has been granted.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> CONNECT sys/sys_pw@database AS SYSDBA;

    Where sys is the SYS user and sys_pw is the SYS user password.

  5. Type the following at the command prompt:

    SQL> grant SELECT ON V_$PARAMETER to <user>;

14.16.3 How to determine and reset tablespace quotas

A database user must have enough quota in their default tablespace to create summary tables. The following tasks enable you to determine and tablespace quotas, if necessary.

To determine tablespace quotas, for Oracle databases (version 9.2.0.7 or later):

  1. Start SQL*Plus (if it is not already running) and connect as the SYS user, or a user to which the sysdba privilege has been granted.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> CONNECT sys/sys_pw@database AS SYSDBA;

    Where sys is the SYS user and sys_pw is the SYS user password.

  2. Type the following at the command prompt:

    SQL> select * from dba_ts_quotas where username = <user>;

    where <user> is the userid of the person using Discoverer Administrator.

To reset tablespace quotas:

  1. Reset the tablespace quotas by executing the following SQL statement:

    SQL> alter user <user> quota <n> on <tablespace>;

    where <user> is the userid of the person using Discoverer Administrator

    where <n> is the quota in K(ilobytes) or M(egabytes) or Unlimited

    where <tablespace> is the default tablespace name for example, USERS

14.16.4 How to check object/schema name

A user must not have an object in their schema with the same name as their user name. This task shows you how to look for any objects in your schema that have the same name as your database user name.

To check whether there are any objects in your schema with the same name as your user name, do the following:

  1. Start SQL*Plus (if it is not already running) and connect as the database user.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> CONNECT jchan/tiger@database;

    Where jchan is the database user and tiger is the database user password.

  2. Type the following at the command prompt:

    SQL> select object_name from user_objects where object_name = <user>;

where <user> is the same as the login used to connect to SQL*Plus.