Managing Data Warehouse Schemas for Oracle Databases

Creating, upgrading or dropping an entire schema. This option uses the Data Warehouse Configuration Wizard to do a mass update of the schema by creating, upgrading, or dropping all tables at once. This option also enables you to create delete triggers on a transactional database for Siebel sources. See "Creating, Upgrading or Dropping an Entire Schema for Oracle Databases" for instructions.

Note: Before you perform the procedures in this section, make sure you have done the following:

Created an ODBC connection to the Oracle Business Analytics Warehouse database.

Created an SSE role for the Oracle Business Analytics Warehouse and associated the database user with the role.

Creating, Upgrading or Dropping an Entire Schema for Oracle Databases

This procedure uses the Data Warehouse Configuration Wizard to create, upgrade, or drop all tables in the schema. You can also follow this procedure to create delete triggers on a transactional database for Siebel sources.

To create, upgrade or drop the data warehouse schema

From the DAC menu bar, select Tools, ETL Management, Configure.

In the Sources dialog, select Oracle as the target and source database platform.

Click OK to display the Data Warehouse Configuration Wizard.

Select the appropriate option to generate a SQL script to create, upgrade, or drop data warehouse tables. For Siebel sources you can also select the option to create delete triggers in the transactional data warehouse.

Note: If you select the option to upgrade the data warehouse schema, the SQL scripts that DAC generates depend on what data warehouse objects need to be upgraded. See "About the Upgrade Schema SQL Scripts" for more information.

Click Next.

The Data Warehouse tab is active.

Enter the following information:

Field

Description

Container

The name of the source system containers for which you want to create, upgrade or drop the data warehouse tables. Separate multiple names with commas.

If you leave this field blank, DAC performs the action specified for all source system containers.

If there are tables that are common to multiple containers, then only one table will be created. If columns are different for the same table across containers, DAC will create a table that has all the columns in the same table.

When you specify a container name, you must enter the name exactly as it appears in the container drop-down list.

Is Unicode

Select this check box if you need a Unicode data warehouse.

Note: The database must be set to support the creation of a Unicode schema.

Execute

Select this option of you want DAC to execute the SQL script automatically after it is generated.

If you do not select this option, you can manually execute the script at a later time. DAC stores the SQL scripts in <Domain_Home>\dac\conf\sqlgen\sql\oracle.

Note: If you are upgrading the data warehouse schema, depending on what objects need to be updated, DAC may generate a SQL script named upgrade-questionable.sql. DAC will not automatically execute this script. You must execute it manually after you have reviewed and corrected it as necessary. For more information about the upgrade scripts, see "About the Upgrade Schema SQL Scripts".

Physical Data Source

Select the appropriate target data source.

Change default parameter file

The default location for the default_parameter.properties file is displayed. This file stores parameter names and values for the parameterization of default values for not null table columns.

You can select a different properties file by clicking Change default parameter file, and navigating to the appropriate file.

The Run Status tab displays information about the process, as follows:

If a 'Success' message is displayed, the data warehouse tables have been created. To review log information about the process, see the following log files:

<Domain_Home>\dac\log\config\generate_ctl.log - A log of the schema definition process, including details of any conflicts between containers.

<Domain_Home>\dac\log\config\createtables.log - A log of the DDL Import Utility process.

If a 'Failure' message is displayed, the data warehouse tables have not been created. Use the log information in <Domain_Home>\dac\log\config\generate_ctl.log to diagnose the error. The createtables.log is not generated.

Creating, Upgrading or Dropping Subsets of Tables in the Schema for Oracle Databases

To create, upgrade or drop subsets of tables, you use the Generate DW Table Scripts right-click menu command to generate SQL scripts in the Oracle database format.

You can access the right-click menu command from the tabs described in Table 9-1, depending on the context for the schema update:

Note: Depending on what objects need to be updated, DAC may generate a SQL script named upgrade-questionable.sql. DAC will not automatically execute this script. You must execute it manually after you have reviewed and corrected it as necessary. For more information about the upgrade scripts, see "About the Upgrade Schema SQL Scripts".

Drop Tables

Select this option to generate a SQL script to drop tables from the data warehouse schema.

Unicode

Select this check box if you need a Unicode data warehouse.

Note: The database must be set to support the creation of a Unicode schema.

Execute

Select this option of you want DAC to execute the SQL script automatically after it is generated.

If you do not select this option, you can manually execute the script at a later time.

The Run Status tab displays information about the process, as follows:

If a 'Success' message is displayed, the data warehouse tables have been created. To review log information about the process, see the following log files:

<Domain_Home>\dac\log\config\generate_ctl.log - A log of the schema definition process, including details of any conflicts between containers.

<Domain_Home>\dac\log\config\createtables.log - A log of the DDL Import Utility process.

If a 'Failure' message is displayed, the data warehouse tables have not been created. Use the log information in <Domain_Home>\dac\log\config\generate_ctl.log to diagnose the error. The createtables.log is not generated.

Advanced Usage of the Schema Creation and Upgrade Process for Oracle Databases

If you are an advanced DAC user, you can customize the schema creation and upgrade process. This section provides information you need to know before customizing the SQL scripts.

About the Create Schema SQL Script

When you use DAC to create a new data warehouse schema, DAC generates the create.sql file. This file contains SQL syntax for creating a schema for the source system container you are working with.

After the create.sql file is generated, DAC saves it in the <Domain_Home>\dac\conf\sqlgen\sql\<database type> directory. During the schema creation process, you can choose whether to execute this script upon generation or save the script to execute at a later time.

About the Upgrade Schema SQL Scripts

When you use DAC to upgrade the data warehouse schema, DAC generates one or both of the following SQL scripts, depending on what data warehouse objects need to be upgraded.

upgrade-regular.sql

DAC generates the upgrade-regular.sql file when it determines the following data warehouse schema modifications are needed:

Add a new table

Add a new column

Increase a column length

Modify a column from NOT NULL to NULL

Drop a nullable column default value

Modify a column default value

DAC saves the upgrade-regular.sql script in the <Domain_Home>\dac\conf\sqlgen\sql\oracle directory. During the schema upgrade process, you can choose whether to execute this script upon generation, or save the script to execute at a later time.

upgrade-questionable.sql

DAC generates the upgrade-questionable.sql file when it determines the following data warehouse modifications are needed.

Modify a column data type

Decrease a column length

Modify a column precision

Modify a column from NULL to NOT NULL

Because of the nature of these changes, the SQL scripts may fail when executed. Therefore, a DBA must review the upgrade-questionable.sql file before it is executed, and make any necessary corrections to the SQL statements. DAC saves this file in the directory <Domain_Home>\dac\conf\sqlgen\sql\oracle directory. You cannot choose to have this script executed upon generation. You must execute it manually after it has been reviewed and corrected as necessary.

Schema Characteristics You Need to Consider

This section provides information about the most important schema characteristics specific to DAC that you need to consider.

Data Types

Data types are specified as part of the column definition. The following data types are supported:

CHAR. The CHAR data type specifies a fixed-length character string. The maximum length is enforced by the destination database. When you create a CHAR column, you must specify the maximum number of bytes or characters of data the column can hold in the Length field (in the Columns subtab of the Tables tab in DAC). If you try to insert a value that is shorter than the specified length, trailing spaces are added to the column value at run time.

VARCHAR. The VARCHAR data type specifies a variable-length character string. The maximum length is enforced by the destination database. When you create a VARCHAR column, you must specify the maximum number of bytes or characters of data it can hold in the Length field (in the Columns subtab of the Tables tab in DAC).

DATE. The DATE data type specifies a valid date. The valid date is enforced by the destination database.

NUMBER. The NUMBER data type stores positive and negative fixed and floating-point numbers. The maximum length for this value is enforced by the destination database. When you create a number column, you must specify a Length and Precision in the Columns subtab of the Tables tab in DAC. The Length value is the maximum number of bytes or characters of data the column can hold. The Precision value is the total number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit.

TIMESTAMP. The TIMESTAMP data type stores the date and timestamp. The maximum length for this value is enforced by the destination database.

NULL, NOT NULL and Default Values

A column can have a NULL value if, in DAC, the Nullable check box is selected in the Columns subtab of the Tables tab. If the Nullable check box is not selected, the column is declared NOT NULL. NOT NULL columns must contain a value. If a NOT NULL column does not have an explicit value, it will get the default value if one is specified in the Default Value field in the Columns subtab. If a NOT NULL column does not have an explicit value and a default value is not specified, then it will get the value specified in the defaults.properties file only when this column is an existing column that is being upgraded.

For the upgrade process, the defaults.properties file enables you to specify missing default values for data warehouse tables with NOT NULL columns for which default values are not specified in DAC. This file also specifies the equivalents of CURRENT TIMESTAMP based on database type. This file is located in the <DAC_Config_Location>\CustomSQLs\Schema_Templates directory.

In the defaults.properties file, the default values specified are grouped by database type. The entries for the Oracle database type look similar to the following:

For the default values you enter in DAC, you need to enclose in single quotes the values for CHAR, VARCHAR, DATE, and TIMESTAMP data types.

Column Ordering

In DAC, the column order is specified in the Position field of the Columns subtab in the Tables tab. Each column must have a unique position, and the column position specified in DAC must match the column position specified in the Informatica repository.

Unicode Considerations

When generating SQL scripts to create or upgrade the schema, you can indicate in DAC whether the schema should be created as Unicode. When you create a schema as Unicode, you can still define specific table columns as non-Unicode in DAC by going to the Columns subtab in the Tables tab.

Index Creation

Indexes are not created during the schema creation process. Indexes are created at runtime during the first full load.

Error Handling

Errors should be handled using an iterative process. Figure 9-1 illustrates the general process for handling errors during the schema creation and upgrade processes. The first step is to generate the SQL scripts for creating or upgrading the data warehouse schema. Next, you can review the SQL scripts before executing and also review the SQL generation log file, which is located in the directory <Domain_Home>\dac\log\config. You then execute the scripts, and check the SQL execution log file for errors. This file is located in the directory <Domain_Home>\dac\log\config.

If errors occurred during the script execution, you need to interpret the error messages and determine the best way to alter the metadata in DAC. Once you have made the necessary changes to the metadata, you can then re-execute the scripts, check the log files, and make any additional changes to the metadata. You need to repeat this process until no errors appear in the execution log file.

Figure 9-1 Error Handling Iterative Process

Creating or Upgrading the Schema When You Have Multiple Source System Containers

If your environment uses multiple source system containers, when you upgrade the schema, the upgrade-questionable.sql file will log discrepancies among the containers, such as discrepancies with data types. The process of finding errors will be iterative. You can execute the scripts, review them to find errors, and fix the errors before executing the scripts.

For the upgrade scripts to execute successfully, the containers must be synchronized. If you have made changes in one container, you need to replicate the changes in the other containers. You can do so by referencing the new or changed object or by recreating the new or changed object across all containers. For information about how to reference repository objects, see "About Object Ownership in DAC".

Customizing the Schema XML Templates

The schema creation and upgrade processes are controlled by XML templates, which contain database-dependent SQL syntax that specifies how data warehouse objects are created or upgraded.

The XML templates are located in the directory <DAC_Config_Location>\CustomSQLs\Schema_Templates directory.

About createschema_template_designation.xml

The createschema_template_designation.xml file designates which schema creation template or templates will be used to create the data warehouse schema. By default, the preconfigured createschema_<database type>.xml file is designated as the template for creating all data warehouse tables. If necessary, you can modify the createschema_<database type>.xml file or you can create your own schema creation templates in XML format.

The createschema_template_designation.xml file enables you to specify schema creation templates to do the following:

Specify a single schema creation template for creating all the data warehouse tables or all tables other than those created by the override templates.

The XML example below shows the structure of the createschema_template_designation.xml file. The tag <ORACLE> indicates this block of XML applies to the Oracle database type. The XML elements available for customizing are TABLE_NAMES, TABLE_TYPES, and ALL_OTHER_TABLES. Templates specified in the TABLE_NAMES and TABLE_TYPES tags will override the template specified in the ALL_OTHER_TABLES tag.

As an example, if you wanted to specify a particular, custom schema creation template for creating fact and dimension tables, called createschema_dimension.xml, and use the default template (createschema_oracle.xml) for creating all other tables, the XML block would look like the following:

About createschema_<database type>.xml

The createschema_<database type>.xml file contains the database-dependent SQL syntax used to create the schema. It also specifies the tablsespace. You can customize this file to specify tablespaces for specific tables by table name and table type. You can also add additional SQL at the end of the file.

However, you should not change the SQL syntax in the file.

The code example below shows the XML elements available for customizing the tablespace. The tablespace specified in the TABLE_NAMES and TABLE_TYPES tags will override the tablespace specified in the ALL_OTHER_TABLES tag.

Customizing the createschema_<database type>.xml File

You can customize the createschema_oracle.xml file by specifying tablespaces for tables by table name and table type. You can also add additional SQL statements to the end of the file. You should not change any of the SQL syntax in the file.

To customize the createschema_oracle.xml file

Go to the directory <DAC_Config_Location>\CustomSQLs\Schema_Templates.

Open the createschema_oracle.xml file in a text editor.

To designate a tablespace for a specific table name:

Find the TABLE_NAMES XML element.

For example:

<TABLESPACE NAME = "">
<TABLE_NAMES></TABLE_NAMES>
</TABLESPACE>

Enter the tablespace name as the value for the TABLESPACE NAME attribute. The value must be enclosed within quotes.

Enter the table name as text content for the TABLE_NAMES element.

To designate a tablespace for a table type:

Find the TABLE_TYPES XML element.

For example:

<TABLESPACE NAME = "">
<TABLE_TYPES></TABLE_TYPES>
</TABLESPACE>

Enter the tablespace name as the value for the in the TABLESPACE NAME attribute. The value must be enclosed within quotes.

Enter the table type as text content for the TABLE_TYPES element.

To designate a tablespace for all tables or for all tables other than those created by templates specified in the steps above:

Find the XML element for the ALL_OTHER_TABLES element.

For example:

<TABLESPACE NAME = "">
<ALL OTHER TABLES/>
</TABLESPACE>

Enter the tablespace name as the value for the in the TABLESPACE NAME attribute. The value must be enclosed within quotes.

(Optional) Add additional SQL statements as the text content of the element <SUPPLEMENT> towards the end of the file.

Save and close the file.

About the Upgrade XML Template

The upgradeschema_oracle.xml file contains the database-dependent SQL syntax used to upgrade the data warehouse schema with changes that were made to tables in DAC. This file is located in the <DAC_Config_Location>\CustomSQLs\Schema_Templates directory. You should not change any of the SQL syntax in this file.

Managing Data Warehouse Schemas for Non-Oracle Databases

This section contains instructions for creating, upgrading, and dropping data warehouse tables when the Oracle Business Analytics Warehouse is on a SQL Server, DB2, DB2-390, or Teradata database.

You can also follow this procedure to create delete triggers in the transactional database for Siebel sources.

Note: Before you perform the procedure in this section, make sure you have done the following:

Created an ODBC connection to the Oracle Business Analytics Warehouse database.

Created an SSE role for the Oracle Business Analytics Warehouse and associated the database user with the role.

To create, upgrade or drop the data warehouse schema

From the DAC menu bar, select Tools, ETL Management, Configure.

In the Sources dialog, select the database platform for the target data warehouse and source transactional database.

Click OK to display the Data Warehouse Configuration Wizard.

Select the appropriate option to create, upgrade, or drop data warehouse tables. For Siebel sources you can also select the option to create delete triggers in the transactional data warehouse. Then, click Next.

The Data Warehouse tab is active.

Enter the appropriate information for the database in which you want to store the data warehouse tables. The information that you need to enter is dependent on the type of target database.

Field

Description

Container

The name of the source system containers for which you want to create, upgrade or drop the data warehouse tables. Separate multiple names with commas.

If you leave this field blank, DAC performs the action specified for all source system containers.

If there are tables that are common to multiple containers, then only one table will be created. If columns are different for the same table across containers, DAC will create a table that has all the columns in the same table.

If you only want to deploy a subset of the source business applications for which you imported seed data earlier, then use this field to specify a container name. When you specify a container name, you must enter the name exactly as it appears in the container drop-down list.

Table Owner

Valid database owner, username, or account that you set up to hold the data warehouse.

Password

Valid database user password for the database owner, username, or account that you specified in the Table Owner field.

ODBC Data Source

Data Source Name (DSN) for the Oracle Business Analytics Warehouse.

You must specify the name of the ODBC connection that you created for the data warehouse.

Tablespace

(Optional) Tablespace where data warehouse tables are created.

Is Unicode

Specifies whether the data warehouse database is Unicode. The database must be set to support the creation of a Unicode schema.

Change default parameter file

(For Teradata databases only.) The default location for the default_parameter.properties file is displayed. This file stores parameter names and values for the parameterization of default values for not null table columns.

You can select a different properties file by clicking Change default parameter file, and navigating to the appropriate file.

The Run Status tab displays information about the process, as follows:

If a 'Success' message is displayed, the data warehouse tables have been created. To review log information about the process, see the following log files:

<Domain_Home>\dac\log\config\generate_ctl.log - A log of the schema definition process, including details of any conflicts between containers.

<Domain_Home>\dac\log\config\createtables.log - A log of the DDL Import Utility process.

If a 'Failure' message is displayed, the data warehouse tables have not been created. Use the log information in <Domain_Home>\dac\log\config\generate_ctl.log to diagnose the error. The createtables.log is not generated.

For Teradata databases, DAC generates a SQL script for the specified action. The script is saved in the <Domain_Home>\dac\conf\sqlgen\sql\teradata directory. You need to manually execute the script in the data warehouse to complete the specified process.

Parameterizing Default Values for Table Columns

Before creating or upgrading a data warehouse schema, you can parameterize values for not null table columns. This feature can be useful for defining default values for table columns that are shared across multiple tables, such as DATASOURCE_NUM_ID.

Note: When you upgrade a schema and use a parameter to define a table column default value, the actual value of the parameter will be used to compare with the default value in the target data warehouse.

Follow these instructions to define a parameter and value before you create or upgrade the data warehouse schema.

To parameterize a default value for table columns

Go to the directory dac\CustomSQLs\Schema_Templates, and open the file default_parameters.properties.

The file that is installed with Oracle BI Applications will be empty.

Note: Alternatively, you can create your own properties file and save it in a directory of your choosing.

Enter a parameter and value in the following format:

@DAC_<parameter_name>=<parameter_value>

Note the following conditions:

If the value is a string, it must be enclosed within single quotation marks.

For example:

@DAC_param1='mydefault'

Null values are not allowed. A null value will throw an error.

To define a parameter value as an empty string, you must use single quotes. For example:

@DAC_param2=''

Otherwise, the value will be considered null and will throw an error.

When creating or upgrading a schema for multiple containers, if a parameter is used in one container and an actual value is defined for the other, the process will compare the actual value and the parameterized value. If the values are not the same, an exception will be thrown.