Parameter Management

About Parameter Management

This section describes how the DAC handles parameters and how you can define and manage parameters at the source system and task levels.

 It contains the following topics:

■ Overview of Parameters

■ Preconfigured Parameters

■ How DAC Handles Parameters at Runtime

■ Nesting Parameters within Other Parameters

■ Defining a Text Type Parameter

■ Defining a Database Specific Text Type Parameter

■ Defining a Timestamp Type Parameter

■ Defining a SQL Type Parameter

Overview of Parameters

1)   The ETL logic in Oracle Business Intelligence Applications uses parameters in the Informatica mappings and sessions.

2)    You define and manage parameters using the DAC parameter management feature.

3)   A parameter can apply to all tasks under a source system container (referred to as a source system parameter) or it can apply to a particular task (referred to as a task level parameter).

4)   Parameters set up at the task level have priority over parameters set up at the source system level.

5)   In the DAC, there are two types of parameters:

static and runtime.

The value of static parameters remains constant for all ETL runs.

6)   Examples of static parameters include language codes and currencies.

7)   The value of runtime parameters is dynamic, and the DAC updates this value for each ETL run.

8)    Examples of dynamic parameters include last refresh dates and last WID sequence numbers.

Parameter Data Types

Parameters can have one of the following data types.

About Parameter Management

Customizing DAC Objects and Designing Subject Areas 8-9

Text

The value for the parameter is defined as text.

 You can use the Text data type for both static and runtime parameters.

DB Specific Text

1)   The value for the parameter is defined as database-specific text.

2)   This parameter should be used only if you have a heterogeneous database environment, and the parameter value needs to be different for the different database types.

3)   The DAC evaluates the string based on the source or target database type.

4)   If you do not specify database-specific text, the DAC returns the default value.

Timestamp

The value for the parameter is defined as a timestamp.

1)    You can use the Timestamp data type for both static and runtime parameters.

2)   A static timestamp can be any time that is constant.

3)    A runtime timestamp parameter is a variable for which the value is supplied by the DAC at runtime.

4)   You can define the timestamp in one of multiple formats or define a custom format.

5)   You can also use SQL to fetch any value that can be fired against the specified logical database connection.

6)   The DAC executes the SQL against a data source that maps to the specified logical connection and then formats the resulting value in the specified format.

7)   A SQL specified for a given timestamp parameter can include nested DAC parameters. For information about nested

parameters, see "Nesting Parameters within Other Parameters".

SQL

The DAC fetches the value for the parameter from a database using SQL.

Preconfigured Parameters

1)   Oracle Business Intelligence Applications ships with preconfigured parameters.

2)    Some of these preconfigured parameters are held in text files named parameterfileDW.txt and parameterfileOLTP.txt, which are stored in the folder\Oraclebi\DAC\Informatica\parameters\input.

3)   Other preconfigured parameters are held in the DAC.

4)   The parameters held in the DAC are specific to the different source system containers.

5)   You can add new parameters in the DAC or change the existing parameters held in the DAC.

6)   However, Oracle recommends that you do not change the parameters held in the parameter text files.

7)   You can override the parameters in the text files by creating new parameters in the DAC.

8)   If you do make changes to the parameter text files, however, make sure the files remain in the folder \OracleBI\DAC\Informatica\parameters\input.

How DAC Handles Parameters at Runtime

1)   During an ETL execution, the DAC reads and evaluates the parameters held in the text files parameterfileDW.txt and parameterfileOLTP.txt along with the parameters held in the DAC.

2)   The DAC then creates an individual parameter file for each session.

3)   This file contains the evaluated name-value pairs for all parameters, both static and runtime.

4)   The naming convention for this parameter file is ..txt.

5)   The DAC copies this file to a location specified in the DAC system property InformaticaParameterFileLocation.

About Parameter Management

8-10 Oracle Business Intelligence Data Warehouse Administration Console User’s Guide

Note: The Informatica Server must be configured to read parameter

files from the location specified in the DAC system property

InformaticaParameterFileLocation. For instructions on setting this

property, see Oracle Business Intelligence Applications Fusion Edition

Installation and Configuration Guide.

Nesting Parameters within Other Parameters

1)   You can nest any parameter definition within another parameter definition.

2)   For example, you could nest a runtime text parameter that returns the current run ID within a where clause of a SQL parameter, or you could use database specific text inside another parameter of the text or SQL data types.

3)   Parameters that are nested within other parameters must use the prefix @DAC_.

4)   An example of a text parameter that returns the current run ID placed in a where clause of a SQL parameter would look similar to the following:

5)    

SELECT VALUE FROM PARAM_TEST

WHERE ROW_ID=`@DAC_p1'

Note: Avoid circular nesting, such as parameter A nested within

parameter B, which is nested within parameter A. In such situations,

the DAC randomly picks one of the parameters in the circle and

evaluates it as an empty string.

Defining a Text Type Parameter

Follow this procedure to define a parameter using the Text data type. This procedure applies to parameters defined at both the source system and task levels.

To define a text type parameter:

1. Do one of the following:

■ To define a source system parameter, from the Views menu, select Design, and

then select Source System Parameters.

■ To define a task level parameter, from the Views menu, select Design, then

select Tasks, and then click the Parameters subtab.

2. Click New.

3. Enter a parameter name.

4. Select the Text data type.

5. Click in the Value field to open the Enter Parameter Value dialog.

6. Select one of the following options:

■ Static. This option specifies a value that remains constant for all ETL runs.

■ Runtime. This option specifies a value will be updated by the DAC before

each ETL run.

7. If you selected the Static option, enter a text value in the text window, and click

OK.

8. If you selected the Runtime option, select a DAC Variable from the list, and click

OK.

Note: The Informatica Server must be configured to read parameter

files from the location specified in the DAC system property

InformaticaParameterFileLocation. For instructions on setting this

property, see Oracle Business Intelligence Applications Fusion Edition

Installation and Configuration Guide.

Note: Avoid circular nesting, such as parameter A nested within

parameter B, which is nested within parameter A. In such situations,

the DAC randomly picks one of the parameters in the circle and

evaluates it as an empty string.

About Parameter Management

Customizing DAC Objects and Designing Subject Areas 8-11

9. (Optional) To inactivate the parameter, select Inactive.

10. Click Save.

Defining a Database Specific Text Type Parameter

Follow this procedure to define a parameter using the DB Specific Text data type. This

procedure applies to parameters defined at both the source system and task levels.

To define a database specific text type parameter:

1. Do one of the following:

■ To define a source system parameter, from the Views menu, select Design, and

then select Source System Parameters.

■ To define a task level parameter, from the Views menu, select Design, then

select Tasks, and then click the Parameters subtab.

2. Click New.

3. Enter a parameter name.

4. Select the DB Specific Text data type.

5. Click in the Value field to open the Enter Parameter Value dialog.

6. Select one of the following Connection Type options:

■ @DAC_SOURCE_DBTYPE. This option specifies a source database

connection.

■ @DAC_TARGET_DBTYPE. This option specifies a target database connection.

7. To define a parameter specific to all database types:

a. Click in the Default field to open the Default text box.

b. Enter the parameter definition, and click OK.

8. To define a parameter specific to a particular database type:

a. Click in the appropriate database type field to open the text box.

b. Enter the parameter definition, and click OK.

9. Click OK to close the Enter Parameter Value dialog.

10. (Optional) To inactivate the parameter, select Inactive.

11. Click Save.

Defining a Timestamp Type Parameter

Follow this procedure to define a parameter using the Timestamp data type. This

procedure applies to parameters defined at both the source system and task levels.

To define a Timestamp type parameter:

1. Do one of the following:

■ To define a source system parameter, from the Views menu, select Design, and

then select Source System Parameters.

■ To define a task level parameter, from the Views menu, select Design, then

select Tasks, and then click the Parameters subtab.

2. Click New.

About Parameter Management

8-12 Oracle Business Intelligence Data Warehouse Administration Console User’s Guide

3. Enter a parameter name.

4. Select the Timestamp data type.

5. Click in the Value field to open the Enter Parameter Value dialog.

6. Select one of the following options:

■ Static. This option specifies a value that remains constant for all ETL runs.

■ Runtime. This option specifies the value will be updated by the DAC before

each ETL run.

■ SQL. This option

7. If you selected the Static option:

a. Click in the Date field to open the Date dialog.

b. Enter a data and time, click OK.

8. If you selected the Runtime option:

a. Click in the Value field to open the Enter Parameter Value dialog.

b. Select a Variable from the list.

c. From the Function list, select a format to which the DAC will convert the date.

If you select Custom, enter a custom date format.

If you select SQL Syntax or SQL Syntax (Date Only), select a Connection Type.

9. If you selected the SQL option:

a. Click in the SQL field to open the Enter Parameter Value dialog.

b. Select a Logical Data Source from the list.

c. Enter the parameter definition and click OK.

10. Click OK to close the Enter Parameter Value dialog.

11. (Optional) To inactivate the parameter, select Inactive.

12. Click Save.

Defining a SQL Type Parameter

Follow this procedure to define a parameter using the text data type. This procedure

applies to parameters defined at both the source system and task levels.

To define a SQL type parameter:

1. Do one of the following:

■ To define a source system parameter, from the Views menu, select Design, and

then select Source System Parameters.

■ To define a task level parameter, from the Views menu, select Design, then

select Tasks, and then click the Parameters subtab.

2. Click New.

3. Enter a parameter name.

4. Select the SQL data type.

5. Click in the Value field to open the Enter Parameter Value dialog.

6. Select a Logical Data Source.

Working with Configuration Tags

Customizing DAC Objects and Designing Subject Areas 8-13

7. Enter a SQL statement, and click OK.

8. (Optional) To inactivate the parameter, select Inactive.

9. Click Save.