Skip Headers
Oracle® Business Intelligence Discoverer Administration Guide
10g Release 2 (10.1.2.1)
B13916-04
  Go To Documentation Library
Library		 Go To Product List
Product		 Go To Table Of Contents
Contents		 Go To Index
Index
Previous
Previous 		Next
Next 		 

14 Managing summary folders

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

About folders and summary folders in Discoverer

Discoverer uses two kinds of folders:

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):

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).

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 Oracle9i (or later) Enterprise Edition databases. For more information, see "What is different between mapping external summary tables and views to EUL items, with Oracle Enterprise Edition databases?".

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:

Notes

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

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 should you wish to control the specification of default values during summary folder creation/maintenance.

How does ASM work?

ASM determines how to create summary folders by:

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. This means that ASM can use folders from all business areas in the current EUL.

What are the prerequisites for creating summary folders with ASM?

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

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:

The version of Discoverer and/or database version under which the database user was created? Does the 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.

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

There are a number of ways that you can run ASM. 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".

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").

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 (e.g. 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, "Oracle Business Intelligence Discoverer command line interface").

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:

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

    Description of Figure 14-1 follows
    Description of "Figure 14-1 Summary Wizard dialog"

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

    Figure 14-2 Summary Wizard: Step 2 dialog

    Description of Figure 14-2 follows
    Description of "Figure 14-2 Summary Wizard: Step 2 dialog"

    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 allows 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 exactly the same point in the process at which the Stop button was clicked.

    Figure 14-3 Summary Wizard: Step 2 dialog

    Description of Figure 14-3 follows
    Description of "Figure 14-3 Summary Wizard: Step 2 dialog"

    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

    Description of Figure 14-4 follows
    Description of "Figure 14-4 Summary Wizard: Step 3 dialog"

    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

    Description of Figure 14-5 follows
    Description of "Figure 14-5 Summary Wizard: Step 3"

  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

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

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, "Oracle Business Intelligence Discoverer command line interface". For more information about ASM commands and command modifiers see and "/asm".

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. This enables the system to maintain itself.

Before you can run ASM from a batch file you need to do two things:

  1. Create a text file in a text editor (e.g. 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 (e.g. 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.

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:

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 Oracle9i databases and 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;

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 Oracle9i databases and 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>;

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 Oracle9i (and later) databases:

  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 e.g. USERS

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.

How to set the start time for summary processing

The summary management processes run within the database on the server, and are controlled by parameters in the initialization file of the Oracle DBMS - the INIT<SID>.ORA file.

Limiting the number of processing requests that can run simultaneously

The parameter job_queue_processes specifies the number of concurrent processes to use to process DBMS_JOB. That is, it controls the number of processing requests that can be handled simultaneously. The default value is zero which means processing requests will NOT be created. You should set it to a minimum of 2, or more if you have any other applications that use DBMS_JOB.

You need more than one job queue process, because if one job fails for any reason, it may keep getting re-submitted and thus prevent everything else in the queue from being completed. If you wish to have 10 simultaneous processing requests handled then you will need to set this to 10.

For more information about these parameters, see "What Oracle database features support workbook scheduling?"

To enable the above parameter:

  1. Locate the INIT<SID>.ORA file.

    The INIT<SID>.ORA file is held in <ORACLE_HOME>\database. The default name of the file is INITORCL.ORA, where ORCL is the <SID> name.

  2. Enter a line into the file. For example: 

    job_queue_processes = 2

Notes

  • The summary management and workbook scheduling features both use this scheduling capability within the Oracle DBMS. The number of concurrent requests affects both features.

  Previous
Previous 		Next
Next
  Go To Documentation Library
Library		 Go To Product List
Product		 Go To Table Of Contents
Contents		 Go To Index
Index