B
Installing a Dedicated Clickstream Database

The information in this appendix is intended for use by System Administrators, Database Administrators (DBAs), or individuals who have installed Oracle9i Application Server. In this appendix, you'll learn how to configure an Oracle9i Enterprise Edition database for use with Oracle9iAS Clickstream Intelligence via the Oracle9iAS Database Installation Wizard.

Note:

Throughout this appendix and the Oracle9iAS Clickstream Intelligence documentation set, the terms "dedicated (Clickstream) database" and "standalone database" are used interchangeably with "customer database." All terms refer to a separate, Oracle9i Enterprise Edition database that is used for collection of Clickstream data exclusively. The term customer database is preferred terminology, as it is used throughout the Oracle9i Application Server documentation set.

This appendix contains the following sections:

Dedicated Clickstream Database Installation Summary: Provides a summary of all steps required to start using Clickstream Intelligence with a cusomer database.
Hardware Specifications: Suggests the optimal hardware configuration for installation of a dedicated Clickstream database.
Software Specifications: Describes the software required to install a customer database.
Configuring the Database: Describes configuration of the database for use with Oracle9iAS Clickstream Intelligence.
Running the Database Installation Wizard: Describes the parameters required for installation of the Clickstream Intelligence database schemas. Also includes information about automatic or manual creation of tablespaces.

Dedicated Clickstream Database Installation Summary

To begin using Clickstream Intelligence with a dedicated customer database, complete the following tasks (in the order specified).

Install Oracle9i Enterprise Edition database and set the database password.
Install the appropriate Patchset for your operating system. To do this, go to the following URL:
```
http://metalink.oracle.com
```
Following successful login with a valid username and password, click Patches from the menu on the left side of your screen. Provide the following Patch Download query data. Fields not listed below require no modification; leave the default values in the fields provided.
- Product Family: Oracle Server
- Release: 9.0.1.3
- Platform: (select the same platform as your Oracle9i database)
- Language: (select the language that matches the one you selected during Oracle9i database installation.)
- Limit Search To: All Product Patches
Click the Submit button. From the list of patches displayed, click the link for one of the patches listed below. Download the patch as specified in the ReadMe file.
- (UNIX) Oracle 9.0.1.3 Patch Set for Oracle Data Server. ID: 1502843; Patchset: 2271678.
- (Windows) Oracle9i 9.0.1.3.1 Patch 3 for Windows NT and Windows 2000. ID: 1670440; Patchset: 2367681.
Launch the Database Installation Wizard. For details, see "Launching the Database Installation Wizard".
Stop and then re-start all application server processes to ensure that Apache server recognizes and uses the modifications you made to the click-app.xml file via the Database Installation Wizard. Use the following commands:
```
     ORACLE_HOME/opmn/bin/opmnctl stopall 

     ORACLE_HOME/opmn/bin/opmnctl startall 
```
In the expressions above, the ORACLE_HOME environment variable represents the directory in which Oracle9iAS is installed.
Create an End User Layer (EUL) with the Oracle9i Discoverer Administrator "EUL Manager dialog."

The EUL should be created by the DBA who installed the dedicated Clickstream database. To learn how to create an EUL, see the Oracle9i Discoverer Administrator Administration Guide.
Use the Oracle9i Discoverer Administrator Import Wizard to populate the EUL with the Clickstream EUL objects contained in the clickstream_intelligence_eul_full.eex file. This file is located in the following directory:
```
CLICK_HOME/install/analytics/discoverer/LANG 
```
In the expression above, CLICK_HOME is the directory in which Oracle9i Clickstream Intelligence is installed, and LANG is the code corresponding to the language selected during Oracle9iAS installation.

For information about populating the EUL with Clickstream metadata, see the Oracle9i Discoverer Administrator Administration Guide.
Access the Clickstream Intelligence Home Page. To do this, launch an Internet browser window and enter the following URL:
```
http://HOST:PORT/click/
```
The HOST is the name of the computer on which Clickstream Intelligence is installed, and the PORT is the logical channel on which the host listens for requests.
From the Clickstream Intelligence Home page, click the link for the Runtime Administrator. Log on to the Runtime Administrator with the username clkrt and the password you defined with the Clickstream Database Installation Wizard.
Using the Runtime Administrator, specify the types of data you want to acquire, process, and store in the customer database. To learn more, see the Oracle9iAS Clickstream Intelligence Administrator's Guide.
Load Web server log file data into the dedicated Clickstream database. For information about this and other database processes, see Chapter 4, "Loading the Clickstream Database".
Enable access to Clickstream Analytics by creating a "Private" database connection. To do this, access the Clickstream Intelligence Home page and click the Analytics-Discoverer Viewer link. The Connect to Discoverer Viewer page is displayed.
On the Connect to Discoverer Viewer page, provide the Connection Name, Connection Description, and Locale (the language used to view Clickstream Analytics).

Specify the Database Connection Details below, and then click Apply to return to the Connect to Discoverer Viewer page.
- User Name: enter clkana as your user name.
- Password: enter the clkana schema password you defined via the Clickstream Database Installation Wizard.
- Database: enter the customer database SID.
To access Clickstream Analytics, click the link for the name of the private connection you just created.

For additional information on creating a private connection and creating public connections for multiple Clickstream Analytics users, see the Oracle9i Discoverer Administrator's Administration Guide.

Hardware Specifications

It is recommended that you install the Oracle9iAS database on a dedicated server for the following reasons:

The operations or queries performed on a Oracle9iAS Clickstream Intelligence database are CPU-intensive.
A typical Oracle9iAS Clickstream Intelligence database instance uses a significantly large amount of memory when processing some querys.
The storage requirements of Oracle9iAS grow quickly over time and may reach terabytes in size.

The minimum hardware requirements for Oracle9i Enterprise Edition database can be found in the Oracle9i Database Installation Guide.

Software Specifications

An Oracle9i Enterprise Edition database is required for use with Oracle9iAS because Oracle9iAS Clickstream Intelligence requires specific features that are only available in that database. Those features are listed below:

Partitioning: Partitioning improves database performance by breaking large tables and indexes into components of a much smaller size. This enables users to perform operations, querys, or other actions on an individual partition rather than the entire table. Additionally, partitioning reduces the number of data management problems that can occur within a large tablespace.
Bitmapped indexes: Bitmapped indexes use bitmaps instead of a list of rowids to quickly identify key values
Parallel processing: Parallel processing improves query performance by enabling the database to use multiple processes to execute a single SQL statement.

For more information about installing an Oracle9i Enterprise Edition database, see the Oracle9i Database Installation Guide.

Configuring the Database

To configure Oracle9i Enterprise Edition database for Clickstream Intelligence, you must complete the following tasks:

Create tablespaces in the database
Modify the database initialization parameter file
Partition the database (optional, but recommended)

The Database Installation Wizard enables you to complete the tasks above. With the Database Installation Wizard you can automatically or manually create tablespaces, and install the Clickstream Intelligence database schemas. If you do not run the Database Installation Wizard, the Clickstream Intelligence database schemas will not be installed.

Information regarding customer database configuration is contained in the following sections of this appendix:

Tablespaces - Provides a high-level discussion of tablespaces and describes the tablespaces required by Clickstream Intelligence.
Creating Tablespaces With the Database Installation Wizard - Explains how to use the Database Installation Wizard for creation of required tablespaces.
Manually Creating Tablespaces - Explains how to create tablespaces manually, and provides sizing formulas and examples.
Partitioning - Describes the various aspects of partitioning required by Clickstream Intelligence.

Tablespaces

Tablespaces are logical containers within an Oracle database that enable data storage and access. Tablespaces are comprised of physical data files (as well as database schema objects such as indexes and tables). Data files define a tablespace's capacity - adding files to a tablespace, for example, increases its data storage capacity. A database can contain one or more tablespaces, and tablespaces can contain one or more data files.

Tablespace size is typically specified by the Database Administrator (DBA) when the database is installed. The size of the data files within a tablespace can automatically extend (as needed), or can be set to a finite value. For more information about tablespaces, see Oracle9i Database Concepts.

Oracle9iAS Clickstream Intelligence requires the following tablespaces:

Analytics Tablespace (CANA)

Provides storage for the Discoverer End User Layer (EUL), as well as the Discoverer workbooks and worksheets that comprise Clickstream Analytics.
Warehouse Audit Tablespace (CAUDT)

Provides storage for historical audit records and details about previously-run processes and jobs.
Warehouse Data Tablespace (CDATA)

Provides storage for all dimensional data and serves as the default tablespace for fact records if fact table partitions are not created.
Warehouse Index Tablespace (CINDX)

Provides storage for dimension indexes, and serves as the default tablespace for fact table bitmap indexes if fact table partitions are not created.
Warehouse Stage Tablespace (CSTGE)

Provides storage for incoming data as it is transformed and loaded into the warehouse.
Warehouse Summary Layer Tablespace (CSUMM)

Provides storage for both partitioned and non-partitioned summary layer tables and materialized views.
Temporary Tablespace (TEMP)

Provides temporary storage for the database.

There are two ways to create the tablespaces listed above. You can use the Oracle9iAS Database Installation Wizard, or you can create the tablespaces yourself. The following sections describe these options:

The tablespace size information provided in the "Manually Creating Tablespaces" section is based on a formula that uses the average number of hits a Web site receives per day, the amount of Web log data those hits generate, and how long the Web log data is stored in the database before it is archived, to determine optimal size. Consider your own Web site and your own storage needs as you determine the appropriate tablespace sizes for the Clickstream database.

Creating Tablespaces With the Database Installation Wizard

The Database Installation Wizard assists you with the creation and sizing of the tablespaces described in the Tablespaces section. if you want to create tablespaces with the assistance of the Database Installation Wizard, select Typical on the Tablespace Option page. Based on the information you specify on the Database Sizing Parameters page, the Database Installation Wizard automatically calculates the number and size of the tablespaces needed in the database. Tablespace information provided by the Database Installation Wizard is influenced by the following factors:

The average number of page requests served by all Web sites that will store data in the database.
The maximum number of page requests served by all Web sites that will store data in the database.
The number of days data will be stored in the database. (It is recommended that you plan ahead when determining this number.)
The frequency with which data will be loaded into the database.

For more information about the Database Installation Wizard, see "Running the Database Installation Wizard" in this appendix.

Manually Creating Tablespaces

To manually create and size the required tablespaces for the Clickstream database, you must still run the Database Installation Wizard. When you reach the Tablespace Option page, however, you must select Custom instead of Typical. By selecting Custom, you inform the Database Installation Wizard that it should search for pre-existing tablespaces in the database.

It is required that you create the following tablespaces within your dedicated Clickstream database:

To size your tablespaces appropriately, use theTablespace Sizing Formula provided below. For examples of tablespace size calculations via the Tablespace Sizing Formula, see the "Calculated Tablespace Values" section. For more information about creating tablespaces, see the Oracle9i Database Administrator's Guide.

Tablespace Sizing Formula

Tablespace Size = C1 * (Average Number of Hits Per Day) * (Number of Days to Store Historical Data) + C2

The components of the formula above explained in the text that follows. If you use the Tablespace Sizing Formula to calculate tablespace size(s), you should be familiar with the log format(s) of data stored in the tablespace, as well as the volume of Web traffic associated with your site.

C1: This numerical constant is dependent on log format. It is the average amount of space used by each hit in each tablespace after Web log data is loaded into the customer database. (Example values for this constant can be found in the "Calculated Tablespace Values" section.)
Average Number of Hits Per Day: This variable is equal to the average number of hits per day served by all Web servers. As the number of hits to your Web site increases, tablespace size requirements also increase.

Note:

When using the Tablespace Sizing Formula to calculate the size of the Warehouse Stage Tablespace, substitute (Maximum Number of Hits Per Day) in place of the value you would typically enter for (Average Number of Hits Per Day).

Number of Days to Store Historical Data: This variable is equal to the number of days of historical data stored in the tablespace before archiving. Storing data in the database for long periods of time increases the size of the tablespace, while storing data in the database for shorter time periods decreases tablespace size requirements.

Note:

When using the formula to calculate the Warehouse Stage Tablespace size, substitute (Number of Days Per Load) in place of (Number of Days to Store Historical Data).

C2: This constant is equal to the minimum space requirement for the tablespace. Hypothetical values for this constant are contained in the examples provided in the "Calculated Tablespace Values" section of this appendix.

Calculated Tablespace Values

This section provides example tablespace values, calculated via the Tablespace Sizing Formula. All of the calculated example values below assume the following scenario for a hypothetical Web site:

The Web site receives an average of 500,000 hits per day. (This data is stored in the database.)
Data is stored in the database for 90 days before it is archived.

You can use the examples below as a guide when calculating tablespace sizes for your own dedicated Clickstream database. To calculate tablespace sizes for your Clickstream database, substitute your own numerical values for the number of hits per day and the number of days data is stored in the database.

Note:

The Analytics Tablespace (CANA) and Temporary Tablespace (TEMP) do not adhere to the Tablespace Sizing Formula.

Analytics Tablespace: When creating the Analytics tablespace, You must use the value provided below.

Temporary Tablespace: There is no one correct size for the Temporary tablespace. We recommend a minimum size based on the assumptions above. Your needs may be larger or smaller. Plan accordingly.

Analytics Tablespace (CANA):

100 MB (Required)

Warehouse Data Tablespace (CDATA):

19,975 MB

Size = 0.45 KB per hit * (500,000 hits per day)* (90 days) + 200 MB = 19,975 MB 
(19.50 GB)

Warehouse Index Tablespace (CINDX):

8,989 MB

Size = 0.20 KB per hit * (500,000 hits per day) * (90 days) + 200 MB = 8,989 MB 
(8.78 GB)

Warehouse Summary Layer Tablespace (CSUMM):

11,186 MB

Size = 0.10 KB per hit * (500,000 hits per day) * (90 days) + 200 MB = 4,595 MB 
(4.49 GB)

Warehouse Staging Tablespace (CSTG):

600 MB

Size = 0.60 KB per hit * (500,000 hits per day) * (1 day) + 300 MB = 600 MB 
(0.59 GB)

Temporary Tablespace (TEMP):

Greater than 350 MB Recommended

Initialization Parameters File

An Oracle initialization parameter file contains a list of database parameters and a value for each parameter. This file is used to start the database and determine the names and locations of the database control files, in addition to other functions. For more information about Oracle initialization parameter files, see the Oracle9i Database Reference. Modifying the default parameter values in the Oracle9i Enterprise Edition database initialization parameter file optimizes the performance of the Clickstream database by adjusting certain parameter values to better acommodate your storage or processing needs.

The "Initialization Parameter Details" section below provides the parameters and values for an initialization parameter file used by an Oracle9i database. The examples that are calculated below assume the following:

The database stores data for a Web site averaging 500,000 hits per day.
Data is loaded into the database daily.
Data is stored for 180 days.

Use the information below as an example to assist you with determining how to set the values in your database initialization parameter file.

Initialization Parameter Details

The information in this section is displayed in the following format:

Parameter Type

Example Parameter: Value

For more information about the functions of the parameters listed below, see the Oracle9i Database Reference.

Note:

Required parameter values appear in italic and must correspond to the values specified below.

Cache and I/O

db_block_size: 16384

db_cache_size: 256MB
File Configuration

db_files: 1000
Cursors and Library Cache

open_cursors: 1000
Job Queues

job_queue_processes: 16
Miscellaneous

compatible: 9.0.1
Pools Parameter

java_pool_size: 50MB

large_pool_size: 256MB

shared_pool_size: 128MB
Optimizer

optimizer_features_enable: 9.0.1

optimizer_max_permutations: 40000

optimizer_mode: Choose

query_rewrite_enabled: True

query_rewrite_integrity: Trusted

star_transformation_enabled: True
Parallel Queries

parallel_automatic_tuning: True
Processes and Sessions

processes: 200
Redo Log and Recovery

fast_start_mttr_target: 300
Resource Manager

resource_manager_plan: system_plan
Sort, Hash Joins, Bitmap Indexes

pga_aggregate_target: 512MB

workarea_size_policy: Auto
System Managed Undo and Rollback Segments

undo_management: Auto
Transactions

dml_locks: 1000

Partitioning

Partitioning a database enables you to quickly access data in large tables and indices by dividing those objects into smaller, more manageable pieces called partitions. Partitioning improves the data management, processing, and querying capabilities of the database by making data easier to access. While there are several ways to partition the tables in a database, Clickstream Intelligence requires range partitioning.

Range partitioning enables the storage of data in specific partitions that are based on a partition key. Within Oracle9iAS, that partition key is a date. Range partitioning is most effective for Web log data because it can be easily defined and sorted by date. Range partitioning the database tables and indexes into equal segments also improves the database's management of objects. For more information on the benefits of range partitioning, see Oracle9i Database Concepts.

Oracle9iAS provides the PL/SQL package called CLK_MNT_PARTITIONS to create range partitions in the Fact and Mview (materialized view) tables. To optimize performance, it is recommended that you use the CLK_MNT_PARTITIONS package to equipartition all Fact and Mview tables. The CLK_MNT_PARTITIONS package also provides additional procedures that enable you to more effectively control partitioning in the database. The following procedures are available:

Range Partitioning the Database

Launch SQL*Plus.
Select the appropriate partitioning method from the choices below.
Enter the code string associated with the partitioning method you chose into SQL*Plus, replacing the code variables with your own data.

CREATE_ALL_PARTITIONS

This procedure creates partitions on every partitioned object in the CLKRT schema. The advantage of using this procedure is that it enables you to simultaneously equipartition all partitioned database objects within the CLKRT schema. Equipartitioning the CLKRT schema improves the refresh performance of all materialized view objects.

The syntax, an example, and the parameters of a call used to invoke the CREATE_ALL_PARTITIONS procedure appear below:

Syntax

procedure create_all_partitions(
   p_fact_tablespace         IN     VARCHAR2,
   p_mview_tablespace        IN     VARCHAR2,
   p_first_date              IN     DATE,
   p_date_less_than          IN     DATE,
   p_days_per_part           IN     NUMBER,
   p_fact_index_tablespace   IN     VARCHAR2 default null,
   p_mview_index_tablespace  IN     VARCHAR2 default null
);

Example

begin
   clk_mnt_partitions.create_all_partitions(
      p_fact_tablespace=>'CDATA',
      p_mview_tablespace=>'CSUMM',
      p_first_date=>to_date('01-NOV-2001','DD-MON-YYYY'),
      p_date_less_than=>to_date('30-NOV-2001','DD_MON_YYYY'),
      p_days_per_part=>1, 
      p_fact_index_tablespace =>'CFACTINDX',
      p_mview_index_tablespace=>'CMVIEWINDX'
   );
end;

Parameters

p_fact_tablespace: The name of the tablespace in which the created fact partitions are placed. The default value is CDATA.
p_mview_tablespace: The name of the tablespace in which the created materialized view partitions are placed. The default value is CSUMM.
p_first_date: The beginning date in the date range for which you want to create partitions.
p_date_less_than: The ending date in the date range for which you want to create partitions.
p_days_per_part: The number of days contained in each partition.
p_fact_index_tablespace: The name of the tablespace in which the fact index partitions are placed. If a parameter is not supplied, the fact index partitions are placed in the same tablespace as fact data. Placing these partitions in the same tablespace as fact data can create storage problems if your data tablespace has a large extent size.
p_mview_index_tablespace: The name of the tablespace in which the materialized view index partitions are placed. If a parameter is not supplied, thet materialized view index partitions are placed in the same tablespace as materialized view data. Placing these partitions in the same tablespace as materialized view data can create storage problems if your data tablespace has a large extent size.

CREATE_ALL_FACT_PARTITIONS

The CREATE_ALL_FACT_PARTITIONS procedure creates partitions for every fact object within the database. Call this procedure if you want all fact objects partitioned equally. This procedure enables you to partition fact objects differently from the materialized view objects.

Partitioning fact objects and materialized view objects differently is not recommended.

The syntax, an example, and the parameters of the call used to invoke the CREATE_ALL_FACT_PARTITIONS procedure appear below:

Procedure

procedure create_all_fact_partitions(
   p_fact_tablespace         IN     VARCHAR2,
   p_first_date              IN     DATE,
   p_date_less_than          IN     DATE,
   p_days_per_part           IN     NUMBER,
   p_fact_index_tablespace   IN     VARCHAR2 default null
);

Example

begin
   clk_mnt_partitions.create_all_fact_partitions(
      p_fact_tablespace=>'CDATA',
      p_first_date=>to_date('01-NOV-2001','DD-MON-YYYY'),
      p_date_less_than=>to_date('30-NOV-2001','DD_MON_YYYY'),
      p_days_per_part=>1,
      p_fact_index_tablespace=>'CFACTINDX'
   );
end;

Parameters

p_fact_tablespace: The name of the tablespace in which the created fact partitions are placed.
p_first_date: The beginning date in the date range for which you want to create partitions.
p_date_less_than: The ending date in the date range for which you want to create partitions.
p_days_per_part: The number of days contained in each partition.
p_fact_index_tablespace: The name of the tablespace in which the fact index partitions are placed. If a parameter is not supplied, the fact index partitions are placed in the same tablespace as fact data. Placing these partitions in the same tablespace as fact data can create storage problems if your data tablespace has a large extent size.

CREATE_ALL_MVIEW_PARTITIONS

The CREATE_ALL_MVIEW_PARTITIONS procedure creates partitions for every materialized view object within the database.

Partitioning materialized view objects and fact objects differently is not recommended.

The syntax, an example, and the parameters of the call used to invoke the CREATE_ALL_MVIEW_PARTITIONS procedure appear below:

Syntax

procedure create_all_mview_partitions(
   p_mview_tablespace        IN     VARCHAR2,
   p_first_date              IN     DATE,
   p_date_less_than          IN     DATE,
   p_days_per_part           IN     NUMBER,
   p_mview_index_tablespace  IN     VARCHAR2 default null
);

Example

begin
   clk_mnt_partitions.create_all_mview_partitions(
      p_mview_tablespace=>'CSUMM',
      p_first_date=>to_date('01-NOV-2001','DD-MON-YYYY'),
      p_date_less_than=>to_date('30-NOV-2001','DD_MON_YYYY'),
      p_days_per_part=>1,
      p_mview_index_tablespace=>'CMVIEWINDX'
   );
end;

Parameters

p_mview_tablespace: The name of the tablespace in which the created materialized view partitions are placed. The default value is CSUMM.
p_first_date: The beginning date in the date range for which you want to create partitions.
p_date_less_than: The ending date in the date range for which you want to create partitions.
p_days_per_part: The number of days contained in each partition.
p_mview_index_tablespace: The name of the tablespace in which the materialized view index partitions are placed. If a parameter is not supplied, thet materialized view index partitions are placed in the same tablespace as materialized view data. Placing these partitions in the same tablespace as materialized view data can create storage problems if your data tablespace has a large extent size.

CREATE_FACT_PARTITIONS

The CREATE_FACT_PARTITIONS procedure creates partitions on a specific fact table object. Call this procedure if you only want to partition a subset of all fact table objects within the database. This procedure provides finer control over creating partitions for specific facts.

The syntax, an example, and the parameters of the call used to invoke the CREATE_FACT_PARTITIONS procedure appear below:

Syntax

procedure create_fact_partitions(
   p_fact_name               IN     VARCHAR2,
   p_fact_tablespace         IN     VARCHAR2,
   p_first_date              IN     DATE,
   p_date_less_than          IN     DATE,
   p_days_per_part           IN     NUMBER,
   p_fact_index_tablespace   IN     VARCHAR2 default null
);

Procedure

begin
   clk_mnt_partitions.create_fact_partitions(
      p_fact_name=>'CLK_IMPRESSION_FACT',
      p_fact_tablespace=>'CDATA',
      p_first_date=>to_date('01-NOV-2001','DD-MON-YYYY'),
      p_date_less_than=>to_date('30-NOV-2001','DD_MON_YYYY'),
      p_days_per_part=>1,
      p_fact_index_tablespace=>'CFACTINDX'
   );
end;

Parameters

p_fact_name: The name of the fact object you want to partition.
p_fact_tablespace: The name of the tablespace in which the created fact partitions are placed. The default value is CDATA.
p_first_date: The beginning date in the date range for which you want to create partitions.
p_date_less_than: The ending date in the date range for which you want to create partitions.
p_days_per_part: The number of days contained in each partition.
p_fact_index_tablespace: The name of the tablespace in which the fact index partitions are placed. If a parameter is not supplied, the fact index partitions are placed in the same tablespace as fact data. Placing these partitions in the same tablespace as fact data can create storage problems if your data tablespace has a large extent size.

CREATE_MVIEW_PARTITIONS

The CREATE_MVIEW_PARTITIONS procedure creates partitions on a specific materialized view object. Call this procedure if you only want to partition a subset of all materialized view objects within the database. This procedure provides finer control over creating partitions for specific materialized views.

The syntax, an example, and the parameters of the call used to invoke the CREATE_MVIEW_PARTITIONS procedure appear below:

Syntax

procedure create_mview_partitions(
   p_mview_name              IN     VARCHAR2,
   p_mview_tablespace        IN     VARCHAR2,
   p_first_date              IN     DATE,
   p_date_less_than          IN     DATE,
   p_days_per_part           IN     NUMBER,
   p_mview_index_tablespace  IN     VARCHAR2 default null
);

Example

begin
   clk_mnt_partitions.create_mview_partitions(
      p_mview_tablespace=>'CSUMM',
      p_mview_name=>'CLK_SL_SESSION_MV',
      p_first_date=>to_date('01-NOV-2001','DD-MON-YYYY'),
      p_date_less_than=>to_date('30-NOV-2001','DD_MON_YYYY'),
      p_days_per_part=>1,
      p_mview_index_tablespace=>'CMVIEWINDX'
   );
end;

Parameters

p_mview_name: The name of the materialized view object you want to partition.
p_mview_tablespace: The name of the tablespace in which the created materialized view partitions are placed. The default value is CSUMM.
p_first_date: The beginning date in the date range for which you want to create partitions.
p_date_less_than: The ending date in the date range for which you want to create partitions.
p_days_per_part: The number of days contained in each partition.
p_mview_index_tablespace: The name of the tablespace in which the materialized view index partitions are placed. If a parameter is not supplied, thet materialized view index partitions are placed in the same tablespace as materialized view data. Placing these partitions in the same tablespace as materialized view data can create storage problems if your data tablespace has a large extent size.

Running the Database Installation Wizard

This section explains how to use the Database Installation Wizard to install the Clickstream Intelligence database schemas. In order to use Clickstream Intelligence with a separate Oracle9i Enterprise Edition database, you must use the Database Installation Wizard to create two database schemas: clkana (for Clickstream Analytics) and clkrt (for the Runtime Administrator). Both schemas provide all necessary database objects to run Oracle9iAS Clickstream Intelligence. The Database Installation Wizard also modifies the database connection information contained in the click-app.xml file. After running the Database Installation Wizard, the Clickstream Intelligence Runtime Administrator will connect to the customer database you just installed (and not the metadata repository installed with Oracle9i Application Server.

Note:

Your Clickstream database, or customer database, must already be running prior to using the Database Installation Wizard.

Launching the Database Installation Wizard

How to Launch the Database Installation Wizard

Connect to the Oracle9i Application Server host.

At the command prompt, enter the appropriate command:

(UNIX)  ORACLE_HOME/click/bin/clkinstall --click-home=ORACLE_HOME/click

(Windows)  ORACLE_HOME/click/bin/clkinstall.bat --click-home="ORACLE_HOME/click"

In the expression above, ORACLE_HOME represents the location in which the customer database was installed.

The Welcome Page

The Welcome page provides information about the steps needed to install information into a Clickstream Intelligence database. To proceed, click Next.

The following function buttons appear on all Database Installation Wizard pages.

Cancel: Quits the installation and closes the Wizard.
Help: Accesses a help file that explains the functionality of each page.
Back: Enables you to view the previous page.
Next: Enables you to view the next page.

The Install/Deinstall Page

The Install/Deinstall page enables you to either create a Oracle9iAS Clickstream Intelligence schema within the database or drop pre-existing schemas.

Select a schema option from the choices below and click Next.

Create a new Oracle9iAS Clickstream Intelligence schema within the database: This option creates the dedicated Clickstream database instance.
Delete pre-existing Clickstream Intelligence schemas from the database: This option permanently removes all Clickstream Intelligence schemas from the database.

The SYS Logon Page

The SYS Logon page enables you to set all parameters needed for the DBA to gain access to the database.

Enter the appropriate password, host name, port, and Oracle SID information and click Next.

Sys Password: The password that will grant a DBA access to the database.
Host Name: The name of the computer on which the database resides (for example, DEDHOST)
Port Number: The port number the database uses to listen (for example, 1521)
Oracle SID: The unique system identifier for the database (for example, dw80pk)

The Runtime Administrator Schema Page

The Runtime Administrator Schema page enables you to set and confirm the password used by a System Administrator to access data stored in the Runtime Administrator schema (CLKRT) created by the Wizard for this database. The CLKRT schema defines within the database all the information necessary to create and manage the Oracle9iAS Runtime Administrator.

Enter the password associated with the Runtime Administrator schema and click Next. There are two fields you must fill in on this page:

Password: Create a password. This is the password that the System Administrator will use to access or configure the Runtime Administrator.
Confirm Password: The same password as entered in the Password field.

The Analytics Schema Page

The Analytics Schema page enables you to set and confirm the password used by a System Administrator and end users to access data stored in the Analytics schema (CLKANA). The CLKANA schema defines within the database all the information necessary to create and manage Oracle9iAS Clickstream Intelligence Analytics (Clickstream Analytics).

Enter the password associated with the Viewer schema and click Next. There are two fields you must fill in on this page:

Password: Create a password. This is the password that users of Oracle9iAS will use to access Clickstream Analytics.
Confirm Password: The same password as entered in the Password field.

The Language Selection Page

The Language Selection page enables you to select the language used for both the Runtime Administrator and Clickstream Analytics.

Select a language from the Runtime Administrator drop-down list.
Select a language from the Clickstream Analytics drop-down list.
Click Next.

The Tablespace Option Page

Use the Tablespace Option page to select the way in which you will set up the tablespaces required when using Oracle9iAS. After making a selection, click Next.

Select Typical if you want use the Database Installation Wizard to set up tablespace parameters for your Clickstream database. If you select Typical, the Database Sizing Parameters page appears next to help you size and name the tablespaces.
Select Custom if you have already installed an Oracle9i Enterprise Edition database and created tablespaces within it. When you select Custom, the Database Installation Wizard immediately displays the Tablespaces Selection page.

Note:

Remember that if you select Custom, you can go to the "The Tablespaces Selection Page" section of this chapter for further instructions. You do not need to read the following sections:

The Database Sizing Parameters Page
The Suggested Tablespace Size Page
The Datafile Directory Page
The Temporary Tablespace Page

The Database Sizing Parameters Page

This page appears if you selected Typical on the Tablespace Option page. Use the Database Sizing Parameters page to configure the tablespaces in your Clickstream database. The Database Installation Wizard utilizes a mathematical formula (as described in the "Tablespace Sizing Formula" section of this appendix) to determine the appropriate size for all tablespaces. When you have provided information for the required fields (described below), click Next.

What is the Average Daily Number of Page Requests Served by All Your Web Sites: Enter the average number of requests served by your Web site per day. The number you provide enables size estimatation for the Warehouse Data tablespace, the Warehouse Index tablespace, and the Warehouse Summary Layer tablespace.
What is the Maximum Daily Number of Page Requests Served by All Your Web Sites: Enter the maximum number of requests served by your Web site per day. The value you provide enables the size of the Warehouse Stage tablespace to be estimated by the Database Installation Wizard.
How Many Days of Web Site Data Will be Stored in the Clickstream Database: Enter the number of days Web log data should be stored in the database before it is archived. The number you supply enables size estimation for the Warehouse Data tablespace, the Warehouse Index tablespace, and the Warehouse Summary Layer tablespace.
How Often Will You Load Web Site Data into the Clickstream Database: Enter the number of days the Clickstream Loader should wait before loading new data into the database. If you want to load data more than once a day, enter the appropriate decimal fraction, such as .5. The number you supply enables the Database Installation Wizard to estimate the size of the Warehouse Stage tablespace.

The Suggested Tablespace Size Page

The Suggested Tablespace Size page enables you to review all tablespaces that will be created by the Database Installation Wizard. To adjust any of the calculated tablespace sizes, enter a new value in the appropriate text field. After you've reviewed all tablespace values, click Next.

Note::

This page appears only if you selected Typical on the Tablespace Option page.

The Database Installation Wizard calculates size values for the following tablespaces:

Analytics tablespace (CANA): Provides storage for the Discoverer End User Layer (EUL), as well as Discoverer workbooks and worksheets.
Warehouse Audit tablespace (CAUDT): Provides storage for historical audit records and details about previously-run processes and jobs.
Warehouse Data tablespace (CDATA): Provides storage for all dimensional data and serves as the default tablespace for fact records (if fact table partitions are not created).
Warehouse Index tablespace (CINDX): Provides storage for dimension indexes and serves as the default tablespace for fact table bitmap indexes (if fact table partitions are not created).
Warehouse Stage tablespace (CSTGE): Provides storage for incoming data as it is transformed and loaded into the database.
Warehouse Summary Layer tablespace (CSUMM): Provides storage for non-partitioned summary layer tables and materialized views, and serves as the default tablespace for partitioned summary layer tables and materialized views.

The Datafile Directory Page

The Datafile Directory page enables you to select the directory in which you want the Database Installation Wizard to create data files for the customer database. To designate a storage location, enter the path for an existing directory on the database host and click Next.

The Temporary Tablespace Page

The Temporary Tablespace page enables you to create a temporary tablespace within the database. to do this, select a temporary tablespace from the drop-down list, specify the requested parameters, and click Next.

Note::

This page appears only if you selected Typical on the Tablespace Option page.

The Tablespaces Selection Page

The Tablespaces Selection page enables you to assign roles to the tablespaces you created in the database. To do this, use the drop-down menu under each tablespace option to select the appropriate tablespaces. When you are finished, click Next.

Note::

This page appears only if you selected Custom from the Tablespace Option page. The tablespaces that appear are identical to those listed in the "The Suggested Tablespace Size Page" section of this appendix.

The Summary Page

The Summary page enables you to review all the settings before installation of the Clickstream Intelligence schemas. These settings include:

System
DBA User
New Runtime Administrator Schema
New Viewer Schema
Warehouse Tablespace Configurations

Review the summary, and then click Finish to begin the installation. If you want to modify settings, click Back to return to the appropriate page(s).

Installation takes about 40 minutes. During this time, the progress bar moves quite slowly - this is normal behaviour. Do not stop the installation due to a lack of feedback or status information. After the Database Installation Wizard completes, you can load data into the database. For more information, see Chapter 4, "Loading the Clickstream Database".

Database Installation Log

To view details related to customer database installation, reference the install-click.log file located in the following directory:

(UNIX) ORACLE_HOME/click/log
(NT) ORACLE_HOME\click\log

The variable ORACLE_HOME refers to to the directory in which Oracle9iAS was installed.

B Installing a Dedicated Clickstream Database

Dedicated Clickstream Database Installation Summary

Hardware Specifications

Software Specifications

Configuring the Database

Tablespaces

Creating Tablespaces With the Database Installation Wizard

Manually Creating Tablespaces

Tablespace Sizing Formula

Calculated Tablespace Values

Analytics Tablespace (CANA):

Warehouse Data Tablespace (CDATA):

Warehouse Index Tablespace (CINDX):

Warehouse Summary Layer Tablespace (CSUMM):

Warehouse Staging Tablespace (CSTG):

Temporary Tablespace (TEMP):

Initialization Parameters File

Initialization Parameter Details

Partitioning

Range Partitioning the Database

CREATE_ALL_PARTITIONS

Syntax

Example

Parameters

CREATE_ALL_FACT_PARTITIONS

Procedure

Example

Parameters

CREATE_ALL_MVIEW_PARTITIONS

Syntax

Example

Parameters

CREATE_FACT_PARTITIONS

Syntax

Procedure

Parameters

CREATE_MVIEW_PARTITIONS

Syntax

Example

Parameters

Running the Database Installation Wizard

Launching the Database Installation Wizard

How to Launch the Database Installation Wizard

The Welcome Page

The Install/Deinstall Page

The SYS Logon Page

The Runtime Administrator Schema Page

The Analytics Schema Page

The Language Selection Page

The Tablespace Option Page

The Database Sizing Parameters Page

The Suggested Tablespace Size Page

The Datafile Directory Page

The Temporary Tablespace Page

The Tablespaces Selection Page

The Summary Page

Database Installation Log

B
Installing a Dedicated Clickstream Database