List of Figures

Figure 1-1. Architecture Diagram for the DIM Adapter for Essbase 3

Figure 2-1. Type 1 Source Definition Information 38

Figure 2-2. Type 2 Source Definition Information 38

Figure 2-3. Type 3 Source Definition Information 39

Figure 2-4. Column Information for a Type 1 Source Definition 39

Figure 2-5. Column Information for a Type 2 Source Definition 40

Figure 2-6. Column Information for a Type 3 Source Definition 40

Figure 3-1. Type 1 Target Definition Information 63

Figure 3-2. Type 2 Target Definition Information 63

Figure 3-3. Type 3 Target Definition Information 64

Figure 3-4. Column Information for a Type 1 Target Definition 64

Figure 3-5. Column Information for a Type 2 Target Definition 65

Figure 3-6. Column Information for a Type 3 Target Definition 65

Figure 6-1. The Edit Tasks Dialog Box for Running Calculation Scripts 98

Figure 6-2. The Edit Report Script Dialog Box 99

Informatica Corporation
http://www.informatica.com
Voice: (650) 385-5000
Fax: (650) 385-5500
NEXT


List of Tables

Table 2-1. Data Storage Probable Values 34

Table 2-2. TwoPassCalc Probable Values 34

Table 2-3. Consolidation Probable Values 34

Table 2-4. TimeBalance Probable Values 35

Table 2-5. TimeBalanceSkip Probable Values 35

Table 2-6. VarReporting Probable Values 36

Table 2-7. CurrCon Skip Probable Values 36

Informatica Corporation
http://www.informatica.com
Voice: (650) 385-5000
Fax: (650) 385-5500
PREV NEXT


Understanding the Hyperion Data Integration Management Adapter for Essbase

This chapter covers the following topics:

  • Understanding the Hyperion Data Integration Management Adapter for Essbase Overview
  • Architectural Overview


Understanding the Hyperion Data Integration Management Adapter for Essbase Overview

The Oracle's Hyperion® Data Integration Management Adapter for Essbase® provides bi-directional connectivity between PowerCenter and the Oracle®Essbase®server for data extraction and loading. It enables you to transfer the data in the Essbase server database into a relational model cube and vice versa. The DIM Adapter for Essbase supports read/write access to Essbase data by making calls to Essbase. It accesses data on the fly to and from Essbase, and does not require additional disk storage for multidimensional data retrieved. It also allows dynamic building of Essbase outlines.


Architectural Overview

Figure 1-1 shows the architecture of the DIM Adapter for Essbase:

Figure 1-1. Architecture Diagram for the DIM Adapter for Essbase

The functionalities of major components marked in the architectural diagram are as follows:

  • Essbase metadata definition (A). Registers the metadata definition for a given Essbase implementation with the Repository server.
  • DIM Adapter for Essbase Client Plug-in (B). Allows you to configure Essbase source and target definitions in the Designer.
  • DIM Adapter for Essbase Reader Plug-in (C). Reads the Essbase information into the Informatica PowerCenter Connect environment.
  • DIM Adapter for Essbase Writer Plug-in (D). Writes data from PowerCenter to the Essbase database.

Components of the DIM Adapter for Essbase

The DIM Adapter for Essbase has these components:

  • Essbase Metadata Definition File
  • DIM Adapter for Essbase Client Plug-in
  • DIM Adapter for Essbase Reader Plug-in
  • DIM Adapter for Essbase Writer Plug-in

Essbase Metadata Definition File

This file represents Essbase schema information in XML format, which is compliant with the Informatica-supplied plugin.dtd. Once this file is registered with the Repository server, the Client plug-in uses the metadata information to allow users to configure Essbase sources and targets.

DIM Adapter for Essbase Client Plug-in

This module allows you to configure Essbase source and target definitions from the Designer. The Client plug-in consists of the Designer Wizard, which provides the GUI for configuring Essbase source and target definitions. Using the Designer Wizard, you can transform the de-normalized data from Essbase to a normalized star schema. You can also manually modify the Essbase outline.

DIM Adapter for Essbase Reader Plug-in

This module allows you to import Essbase sources and the Essbase information into PowerCenter. The PowerCenter Server invokes the Reader plug-in.

DIM Adapter for Essbase Writer Plug-in

This module allows you to export data from PowerCenter to Essbase targets. It helps write data from PowerCenter to an Essbase database and dynamically build the Essbase outline. The PowerCenter Server invokes the Writer plug-in.


Working with Essbase Sources

This chapter includes the following topics:

  • Working with Essbase Sources Overview
  • Importing an Essbase Source Definition
  • Creating Relational Model for Essbase
  • Troubleshooting


Working with Essbase Sources Overview

With DIM Adapter for Essbase, you can create a source definition with columns mapped to dimensions in the Essbase outline. The data stored in the Essbase database can be extracted into the PowerCenter Connect environment.

You can use the Designer to import Essbase source definitions into the repository. When you import Essbase source definition, the Designer displays a table with columns and Essbase datatypes.


Importing an Essbase Source Definition

To import an Essbase source definition:

In the Source Analyzer window, click Sources > Import from Hyperion Essbase.
The Import Hyperion Essbase dialog box appears.

Tip: If Essbase DSNs and tables are already created, they appear under 'Essbase' in the left pane.

Note: You can use an existing DSN, if you have an authentication to connect to it. Every DSN that you create belongs to a particular Essbase server, user name, and password. You may need to create a new DSN in the following cases:

  • You want to connect to a particular Essbase server for which DSN is not available.
  • You want to connect to a particular Essbase server for which DSN is available, but you do not have an authentication (user name and password) to connect to it.

To perform any operation on an existing DSN, select it from the DSN Name list, click Connect, and provide the user name and password for the connection.

Creating a DSN for a Source Definition

DSN is a name given to a set of information that is used to query data. The information includes various elements such as Essbase server name, user name, and password.

To create a DSN for a source definition:

  1. In the Import Hyperion Essbase dialog box, right-click 'Essbase' and select Create DSN.

    -or-

    Click Create DSN.

  2. In DSN Name, enter a name for the DSN.

    Note: The DSN name should follow Informatica conventions. This input is mandatory.

  3. In Description, enter a brief description for the DSN.
  4. In Server Name, enter the name or IP address of the computer on which the Essbase server resides.
  5. In User Name, enter user name.
  6. In Password, enter password.
  7. Click Test Connectivity, to check whether the connection is successful.
  8. Click Save to complete the DSN creation.

    -or-

    Click Cancel to exit from the DSN Creation Wizard.

Note: Multiple source definitions can be created using the Table Creation Wizard by following the above procedure.

Viewing Source DSN Information

You can view information about the DSN associated with a source definition.

To view the DSN information:

  1. In the Import Hyperion Essbase dialog box, select the DSN and click View Details to view the DSN details.

  2. To change the DSN description, click Modify and enter new text in Description.
  3. Click Save.

Creating a Source Definition

Create source definitions to retrieve the multidimensional data in tabular form. You can create three types of source definitions:

  • Accounts dimension member as a column (Type 1). This table type allows you to map Accounts dimension members as source definition columns. You can map any number of Accounts dimension members as columns.
  • Accounts dimension as a column (Type 2). This table type allows you to map Accounts dimension as a column. DIM Adapter for Essbase will create a 'Data' column automatically for this source definition. All the numeric data which is a combination of dimension members will pass through by 'Data' column.
  • Extract Members (Type 3). This table type allows you to map any standard dimension or level under the dimension as a source definition column. The data will be extracted for all descendents under the mapped dimension or level.

Note: The Type 3 source definition is introduced in this release.

Creating a Type 1 Source Definition

The following example explains how to create a source definition called EssbaseTable and select the appropriate application, database, and outline.

To create a Type 1 source definition under a DSN:

  1. Right-click the DSN and select Create Table to start the Table Creation Wizard.

    -or-

    Select the DSN and click Create Table.

    Note: In DSN Name, the name of the DSN appears by default. You cannot edit this value in the dialog box.

  2. In Table Name, enter EssbaseTable.

    Note: Follow Informatica conventions for the table name.

  3. In Description, enter a description for the table.
  4. Select Application, Database, and Database Outline from the respective lists.

    Note: DIM Adapter for Essbase uses this information to fetch data on the fly.
    In Outline Type, the type of the outline appears by default. You cannot change it.

  5. Select the Accounts dimension member as a column (Type 1) option to create Type 1 source definition.
  6. Click Save to create the Type 1 source definition and exit from the Table Creation Wizard.

    -or-

    Click Cancel to exit from the Table Creation Wizard.

    Note: If you do not select any application, database, or outline, then the first application, database, and outline are selected by default. The Accounts dimension member as a column (Type 1) option is selected by default, if the Accounts dimension exists in the selected Essbase outline. This option is unavailable if the Essbase outline does not contain the Accounts dimension. In this case, the Accounts dimension as a column (Type 2) option is selected by default.

    Tip: When you click Save, you are prompted to start creating columns for Type 1 source definition. You can create columns by clicking Yes in the displayed dialog box or by right-clicking the table name in the left pane of the Import Hyperion Essbase dialog box and clicking Create Column.

Note: Multiple source definitions can be created using the Table Creation Wizard by following the above procedure.

Creating Columns in Type 1 Source Definition

After you create a Type 1 source definition, you can create columns.

To create columns in Type 1 source definition:

  1. In the Import Hyperion Essbase dialog box, right-click the table name and click Create Column.

    -or-

    Select the table, EssbaseTable and click Create Column.

    The Dimension - Column Mapping dialog box appears.

    The standard dimensions and the members of the Accounts dimension are selected as columns.

  2. Click Set Filter to set filter expressions and to view filtered list of standard dimensions and Accounts dimension members.
    The Set Outline Filter section appears.

  3. Enter the following information in the Set Outline Filter section:
    • Select the Dimension option and enter filter expression in Filter Expression For Dimensions.
    • Select the Measure option and enter filter expression in Filter Expression for Accounts dimension members.
    • In Level, enter level of the Measure to filter.
    • To show all dimensions and its members, click Show All.

      Note: Filter expression is a regular expression. The '*' character is treated as a string. New filter criteria will override the existing filter criteria.

  4. Click OK to view filtered list of standard dimensions and Accounts dimension members.

  5. Select the standard dimensions or the members of the Accounts dimension to be mapped as columns.
  6. Select a dimension and click [image] to map it to a column.

    The Column Creation Wizard dialog box appears.

  7. In Column Name, enter a name for this column.
  8. In Precision, enter the precision value required.
  9. Select User Defined Attributes to query the database based on the UDA.
    The list of UDAs defined for the leaf members of the dimension appears.
  10. Select the UDAs to be used while querying the Essbase database.

    If Column Name length exceeds 69 characters, it is truncated automatically. This does not lead to any error in data extraction/loading.

    Tip: The UDA option is provided only if UDAs are defined for the leaf-level members of the dimension.

  11. In case of Time dimension to retrieve data for Dynamic Time Series Tag, select the Dynamic Time Series name from the DTS list and leaf member of Time dimension from the Member Name list. Click [image] .

    To remove a Dynamic Time Series Tag, select it from the Dynamic Time Series Tag list and click [image] .

  12. To create another column, repeat Step 5 to Step 10.

    Note: You cannot edit Column Type and Scale in the dialog box. You can edit the precision of text columns.

    Tip: The Dynamic Time Series option appears only for the time dimensions that have DTS members enabled.

  13. Select a member of the Accounts dimension and click [image] to map it to a column.

    Tip: While selecting members of the Accounts dimension, you can use the Ctrl key for multiple selection. While mapping multiple columns simultaneously, the Designer Wizard will not prompt you for the column information, and it will map all the columns with default values. For example, multiple columns will be mapped with a default precision of 15.

    The Column Creation Wizard dialog box appears.

  14. In Column Name, enter a name for this column.

    Note: In Column Type, Scale, and Precision default values appear and you cannot edit them in the dialog box.

  15. Select the Accounts dimension and click [image] , to map the columns based on UDA.
    The list of UDAs defined for the leaf members of the dimension appears.

  16. Select the UDAs to be used from the list while querying the Essbase database.

    Tip: The UDA option is provided only if UDAs are defined for the leaf-level members of the dimension.

  17. Select the UDAs based on which the columns for the Accounts dimension members are to be created.
  18. Repeat the above steps for each Accounts dimension member required.
    After creating the columns, the Dimension - Column Mapping dialog box appears.
  19. You can arrange and sort columns by using the [image] and [image] buttons. Click the column name and click [image] to move the column up or [image] to move the column down. Source definition is arranged in the order you choose for the columns. The data is extracted depending on the source definition and source qualifier in the PowerCenter designer.
  20. To delete a mapped column, select it in the right pane and click [image] .

    Tip: You can use Ctrl or Shift keys for unmapping multiple columns.

  21. Click Save and close the Dimension - Column Mapping dialog box.

    Note: You must map at least one dimension and one Accounts dimension member as column for the Type 1 source definition. The Designer Wizard creates the columns selected in the Dimension - Column Mapping dialog box. It also creates any columns for aliases and attributes associated with the selected dimensions.

Adding Attribute Dimensions to the Type 1 Source Definition

This section explains how to add attribute dimensions to the Type 1 source definition.

To add attribute dimensions to the Type 1 source definition:

You can add attribute dimensions to the Type 1 source definition in the following two ways:

  • Add all attributes
  • Select attributes

To add attribute dimensions to the Type 1 source definition:

Right-click the column name in the left pane of the Import Hyperion Essbase dialog box.

Note: The Add All Attributes and Select Attributes options in the menu are enabled only for the dimensions having attributes associated with them.

Adding All Attributes

You can add columns for all attributes associated with a dimension.

To add all attributes:

Select Add All Attributes to add columns for all the attributes associated with the dimension.

The columns for all the attributes associated with the dimension are added.

Selecting the Attributes

To select attributes:

  1. To add columns for all selected attributes associated with the dimension, select Select Attributes.

    The Select Attributes section appears.

  2. From the attribute list, select the attributes to be added.
  3. Click OK to add the attributes and exit.

    -or-

    Click Cancel to exit attributes selection.

Creating a Type 2 Source Definition

The following example explains how to create Type 2 source definition. A source definition called EssbaseTable is created and the appropriate application, database, and outline are selected.

To create a Type 2 source definition under a DSN:

  1. Right-click the DSN and select Create Table to start the Table Creation Wizard.

    -or-

    Select the DSN and click Create Table.

    Note: In DSN Name, the name of the DSN appears by default. You cannot edit this value in the dialog box.

  2. In Table Name, enter EssbaseTable.

    Note: Follow Informatica conventions for table name.

  3. In Description, enter a description for the table.
  4. Select Application, Database, and Database Outline from the respective lists.

    Note: DIM Adapter for Essbase uses this information to fetch data on the fly.
    In Outline Type, the type of the outline appears by default. You cannot edit this value in the dialog box.

  5. Select the Accounts dimension as a column (Type 2) option to create Type 2 source definition.
  6. Click Save to create the Type 2 source definition and exit from the Table Creation Wizard.

    -or-

    Click Cancel to exit from the Table Creation Wizard.

    Note: If you do not select any application, database, or outline, the first application, database, and outline are selected by default. By default the Accounts dimension member as a column (Type 1) option is selected, if the Accounts dimension exists in the selected Essbase outline. This option is unavailable if the Essbase outline does not contain the Accounts dimension. In this case, the Accounts dimension as a column (Type 2) option is selected by default.

    Tip: After clicking Save to create Type 2 source definition, you are prompted to start creating columns for the Type 2 source definition. You can create columns by clicking Yes in the displayed dialog box or by right-clicking the table name in the left pane of the Import Hyperion Essbase dialog box and clicking Create Column.

Creating Columns in Type 2 Source Definition

To create columns in Type 2 source definition:

  1. In the Import Hyperion Essbase dialog box, right-click the table name and select Create Column.

    -or-

    Select the table, EssbaseTable and click Create Column.

    Note: You must create the columns before using the Report Script. This option is enabled only when the columns are created.

    The Dimension - Column Mapping dialog box appears. To view filtered list of standard dimensions and Accounts dimension members, complete the steps as for Type 1 source definition.

  2. Select the dimension to be mapped and click [image] to map the dimensions to columns.
    The Column Creation Wizard dialog box appears.
    You can select the standard dimension including the Accounts dimension as columns.
  3. In Column Name, enter a name for this column.
  4. In Precision, enter the precision value required.
  5. If you want to query the database based on the UDA, select the User Defined Attribute option.
    The list of UDAs defined for the leaf members of the dimension appear.
  6. Select the UDAs to be used while querying the Essbase database from the list.

    The UDA option is provided only if UDAs are defined for the leaf-level members of the dimension.

  7. In case of Time dimension to retrieve data for Dynamic Time Series Tag, select Dynamic Time Series name from the DTS list and leaf member of Time dimension from the Member Name list. Click [image] .

    To remove a Dynamic Time Series Tag, select one from the Dynamic Time Series Tag list and click [image] .

    The Dynamic Time Series option appears only for Time dimensions having DTS members enabled.

  8. Repeat Step 2 to Step 8 for each dimension required.

    Note: In Column Type, Scale, and Precision, default values appear. Column Type and Scale cannot be edited in the dialog box. Precision is editable only for text columns.

  9. To delete a mapped column, select it in the right pane and click [image] .

    The Dimension - Column Mapping dialog box appears.

    Tip: You can use Ctrl and Shift keys for unmapping multiple columns.

    You can arrange and sort columns by using the [image] and [image] buttons. Click the column name and click [image] to move the column up or [image] to move the column down. Source definition is arranged in the order you choose for the columns. The data is extracted depending on the source definition and source qualifier in the PowerCenter designer.

  10. Click Save and close the Dimension - Column Mapping dialog box.

    Note: You must map at least one dimension as column for the Type 2 source definition.

    Tip: The Designer Wizard creates the columns selected in the Dimension - Column Mapping dialog box. Additionally, a data column and columns for aliases and attributes associated with the selected dimensions, if any, are also created. To add the columns for attributes associated with the dimensions, complete the steps as given in Adding attribute dimensions for Type 1 source definition.

Creating a Type 3 Source Definition

The following example explains how to create a Type 3 source definition called EssbaseTable and select an appropriate application, database, and outline. Type 3 table is typically used where you want to extract the metadata associated with a member such as member name, parent name, and data storage. For example, if you map the Year dimension for Type 3 table, then DIM Adapter for Essbase will extract metadata for all members under the Year dimension.

To create a Type 3 source definition under a DSN:

  1. Right-click the DSN and select Create Table to start the Table Creation Wizard.

    -or-

    Select the DSN and click Create Table.

    Note: In DSN Name, the name of the DSN appears by default. You cannot edit this value in the dialog box.

  2. In Table Name, enter EssbaseTable.

    Note: Follow Informatica conventions for table name.

  3. In Description, enter a description for the table.
  4. Select Application, Database, and Database Outline from the respective lists.

    Note: DIM Adapter for Essbase uses this information to fetch data on the fly.
    In Outline Type, the type of outline appears by default. You cannot edit it.

  5. Select the Extract Members (Type 3 Table) option to create Type 3 source definition.
  6. Click Save to create the Type 3 source definition and exit from the Table Creation Wizard.

    -or-

    Click Cancel to exit from the Table Creation Wizard.

    Note: If you do not select any application, database, or outline, then the first application, database, and outline are selected by default. By default, the Accounts dimension member as a column (Type 1) option is selected, if the Accounts dimension exists in the selected Essbase outline. This option is unavailable, if the Essbase outline does not contain the Accounts dimension. In this case, the Accounts dimension as a column (Type 2) option is selected by default.

    Tip: When you click Save, you are prompted to start creating columns for the Type 3 source definition. You can create columns by clicking Yes in the displayed dialog box or by right-clicking the table name in the left pane of the Import Hyperion Essbase dialog box and clicking Create Column.

Creating Columns in Type 3 Source Definition

After you create a Type 3 source definition, you can create columns.

To create columns in Type 3 source definition:

  1. In the Import Hyperion Essbase dialog box, right-click the table name and select Create Column.

    -or-

    Select the table, EssbaseTable and click Create Column.

    The Dimension - Column Mapping dialog box appears.

    The standard and Accounts dimensions and the members of these dimensions are selected as columns. You can map any 'dimension' or 'level in any dimension' (Except level 0 members) to extract the metadata for the members.

  2. Select the standard or Accounts dimensions or the members of these dimensions to be mapped as columns.

    You cannot map leaf-level members of these dimensions.

  3. Select a dimension and click [image] to map it to a column.

    The Column Creation Wizard dialog box appears.

    You can map only one dimension for the Type 3 source definition. The Designer will create the column selected in the Dimension-Column Mapping dialog box and member properties columns will be added automatically in the source definition by the Designer.

  4. In Column Name, enter a name for this column. The precision for this column is set automatically to 80.
  5. Select Use Mapping Parameter to make use of the mapping parameter for this column. You need to enter the name of the mapping parameter.

    Note: For more information on mapping parameters see, Using Report Script.

  6. Select the filter criteria you want to set for the metadata extraction.

    Following is the description of these filter criterion:

    • <Descendant. Are all members in the branches below a parent. For example, Profit, Inventory, and Ratios are the descendants of Measures. The children of Profit, Inventory, and Ratios are also descendants of Measures. Adapter should extract metadata for all the members under “Measures” if “Measures” is mapped as a column.
    • <IDescendant. Are same as Descendants. Adapter should extract metadata for all the members including “Measures”, if “Measures” is mapped as a column.
    • <Child. Is the member that has a parent above it. For example, Sales and Cost of Goods Sold are children of the parent, Margin. Adapter should extract metadata for Profit, Inventory and Ratios if “Measures” is mapped as a column.
    • <IChild. Is same as Child. Adapter should extract metadata for Profit, Inventory, and Ratios including “Measures”, if “Measures” is mapped as a column.
    • <Level0. Are the members that have no children. For example, Opening Inventory, Additions, and Ending Inventory are level 0 members. Adapter should extract metadata for all the level 0 members under “Measures”. For example, Sales and COGS.
    • <MemberOnly. Adapter should extract metadata only for the member mapped as a column, for example, “Measures”.

      You can unmap the dimension and re-map any other dimension.

  7. Click Save and close the dialog box.

Tip: Adapter will extract the metadata in Parent-Child format. You can create a Rules file based on the Parent-Child reference model to use this metadata for building the same dimension in another outline. Type 3 target definition will help you in this.

Following are the values that will be passing through the columns created for this source:

  • MemberName. Name of the member.
  • ParentName. Name of the parent for the member.
  • Alias. Alias associated with the member.
  • Data Storage. This will output the Data Storage setting.
    Table 2-1 lists the probable values for Data Storage:
Table 2-1.

1 | Store data | S
2 | Dynamic calc and store | V
3 | Dynamic calc | X
4 | Label only | O
5 | Never share | N
6 | Shared Member | NULL
Data Storage Probable Values
  • TwoPassCalc. This will output the TwoPassCalc setting.
    Table 2-2 lists the probable values for TwoPassCalc:
Table 2-2.

1 | TwoPassCalc ON | T
2 | TwoPassCalc OFF | NULL
TwoPassCalc Probable Values
  • Consolidation. This will output the Consolidation setting.
    Table 2-3 lists the probable values for Consolidation:
Table 2-3.

1 | Addition | +
2 | Subtraction | -
3 | Multiplication | *
4 | Division | /
5 | Percentage | %
6 | Ignore | ~
Consolidation Probable Values
  • UDA. A comma separated list of UDAs given by Essbase.
  • Formula. A formula attached to the member.
  • Comment. Comment attached to the member.
  • TimeBalance. Time balance property set for the member.
    Table 2-4 lists the probable values for TimeBalance:
Table 2-4.

1 | None | Null
2 | First-Set the time balance as first when you want the parent value to represent the value of the first member in the branch (often at the beginning of a time period) | F
3 | Last-Set the time balance as last when you want the parent value to represent the value of the last member in the branch (often at the end of a time period) | L
4 | Average-Set the time balance as average when you want the parent value to represent the average of the children values. | A
TimeBalance Probable Values
  • TimeBalanceSkip. Time balance skip option set for the member.
    Table 2-5 lists probable values for TimeBalanceSkip:
Table 2-5. TimeBalanceSkip Probable Values

1 | None | Null
2 | Missing- Skips #MISSING data when calculating the parent value. | M
3 | Zeros- Skips data that equals zero when calculating the parent value. | Z
4 | Both Missing and Zeros- Skips both #MISSING data and data that equals zero when calculating the parent value. | B
  • VarReporting. Variance reporting expense property set for the member.
    Table 2-6 lists probable values for VarReporting:
Table 2-6. VarReporting Probable Values

1 | Non Expense- For non-expense items, such as sales, actual expenses should be higher than budgeted expenses. When actual expenses are less than budget, variance is negative. The @VAR function calculates ACTUAL - BUDGET. For example, if budgeted sales are $100 and actual sales are $110, the variance is 10. By default, members are non-expense. | NULL
2 | Expense- For expense items, actual expenses should be lower than budgeted expenses. When actual expenses are greater than budgeted expenses, variance is negative. The @VAR function calculates BUDGET - ACTUAL. For example, if budgeted expenses are $100 and actual expenses are $110, the variance is - 10 | E
  • CurrCon. Currency conversion type set for the member.
    Table 2-7 lists the probable values for CurrCon:
Table 2-7. CurrCon Skip Probable Values

1 | None | 0
2 | Category | 1
3 | No category | 2
  • CuuConvCategory. Currency conversion category set for the member.
  • AttributeMemberValues. A comma separated list of associated Attribute dimension members.

Note: You cannot use the Report Script and Preview data functionality with Type 3 source definition.

Viewing Source Definition Information

Once you create a source definition, you can view the source definition information.

To view details of the source definition:

  1. On the main window, select the table and click View Details.
    The information appears in the Table Wizard.
  2. To change the table description, click Modify and enter new text in Description.
  3. Click Save.

Type 1 Source Definition Information

Figure 2-1 shows a Type 1 source definition information:

Figure 2-1. Type 1 Source Definition Information

Type 2 Source Definition Information

Figure 2-2 shows a Type 2 source definition information:

Figure 2-2. Type 2 Source Definition Information

Type 3 Source Definition Information

Figure 2-3 shows the Type 3 source definition information:

Figure 2-3. Type 3 Source Definition Information

Viewing Column Information for a Source Definition

You can view column details created by selecting the column on the Import Hyperion Essbase dialog box.

Column Information for a Type 1 Source Definition

Figure 2-4 shows column information for a Type 1 source definition:

Figure 2-4. Column Information for a Type 1 Source Definition

Column Information for a Type 2 Source Definition

Figure 2-5 shows column information for a Type 2 source definition:

Figure 2-5. Column Information for a Type 2 Source Definition

Column Information for a Type 3 Source Definition

Figure 2-6 shows column information for a Type 3 source definition:

Figure 2-6. Column Information for a Type 3 Source Definition

Using Report Script

The Report Script is used to change the default Report Script for retrieving data from Essbase. The following section shows the use of the Report Script for Type 1 and Type 2 tables using the Designer Wizard.

Note: You must create columns before using the Report Script. This functionality was available as “Query Builder” in previous releases. This functionality is not available for Type 3 source definition.

To use the Report Script for Type 1 source definition:

  1. In the Import Hyperion Essbase dialog box, right-click a table name and select Report Script.

    -or-

    After selecting table, click Report Script.

    For Type 1 source definition, the dialog box appears.

    The Edit Report Script dialog box appears. The dialog box is used to change default Essbase Report Script for retrieving Essbase data.

    The Member Selection list shows the standard dimension columns created by you using the Dimension - Column Mapping dialog box.

  2. To change the Member Selection condition, under Column Name, select the column for which you want to change the condition.
  3. From the Member Selection Command list, select a command.
  4. In Command Parameters, enter the command parameters.
  5. To specify the order in which members are sorted while retrieving the data, select a sort order from the Member Sort Order list and a sort option from the Member Sort Option list.
  6. Click Save.
  7. If required, select a command to modify Data Filter by using the Data Range Command list.
  8. In Command Parameters, enter the appropriate range of column values.

Note: Please follow Essbase syntax to specify command parameters for 'Data Range Command': <TOP, <BOTTOM and <RESTRICT.

  9. Click Save.
  10. To get data only for DTS members, select the Get Data for DTS members only option.

    Note: This option is enabled only if there is a column associated with the Time dimension for which DTS members are selected.

Note: To parameterize the query for Essbase source definition, you can select <PARAMETERIZED in Member Selection Command. The value for the Command Parameters should be supplied through Informatica mapping parameters and by using a parameter file. The declaration of the command parameter for <PARAMETERIZED should start with $$. For example, $$VAR.

The following is an example of declaring the value for $$VAR either in the Parameter file or as default value in mapping:

  • Single Value: $$VAR = “Jan”
  • Multiple Value: $$VAR= “Jan” “Feb” “Mar”

Substitution variables can be used in the Report Script as a Command Parameter. The substitution variable should start with '&' sign and must be declared in Essbase. For example: &Sub_Var, where Sub_Var must be declared as a substitution variable in Essbase environment. Substitution variables are used with every selection command except:

  • <PARAMETERIZED
  • <LINK
  • <WITHATTR

While extracting the data, the substitution variable is replaced by the value defined for it in the Essbase environment.

Tip: All filter conditions in the Report Script are reset to default values if you add, modify, or delete columns in the table using the Dimension - Column Mapping dialog box.

  11. Click Show Report Script, to view and check the resulting report script.
    The Report Script dialog box appears.

  12. Click Validate Report Script, to check if the resulting script is correct. If not, go back to the Report Script dialog box and rectify the filter commands and their parameters.
  13. If there is any error with the modified report script as obtained so far, you can click Reset to restore all Member Selection and Data Filter conditions and their parameters to the previously stored valid values.
  14. Click OK, to validate and save the Report Script. If the Report Script is invalid you can either reset and save it or rectify it before saving.

    Note: The Report Script must be valid before you close the Report Script dialog box.

Note: To use Report Script for Type 2 source definition, follow the same procedure as described for Type 1 source definition.

Previewing Data

You can use the Preview Data option to view existing data for the Essbase source definition. The following section explains the use of Preview Data option for Type 1 source definition using the Designer Wizard.

Note: You must create the columns before using Preview Data. This option is enabled only when the columns are created. This functionality is not available for the Type 3 source definition.

To use Preview Data for source definitions:

In the Import Hyperion Essbase dialog box, right-click a table name and select Preview Data. Alternatively, after selecting table, click Preview Data.

For Type 1 source definition, the Import Hyperion Essbase dialog box appears.

The Preview Data dialog box appears. The dialog box displays the data from Essbase source definition.

Note: The data is retrieved from Essbase on the basis of standard dimensions and Accounts dimension members mapped.


Creating Relational Model for Essbase

In an Essbase database, data is stored in multidimensional format. Each dimension is associated with a table in relational database semantics. This OLAP data is extracted in a relational format for which we need to create tables for the dimensions along with a Fact table.

The dimension tables store all the members below the appropriate dimensions and the Fact table actually stores the intersections of these dimensions along with the data across these intersections. This process is no longer manual as DIM Adapter for Essbase has the ability to convert the OLAP source into a relational model for normalization.

The relational model is a star schema model, containing one central Fact table and multiple dimension tables. Upon normalizing the Essbase source definition, DIM Adapter for Essbase creates the Fact table and the appropriate dimension tables. A mapping is automatically created for the normalized Essbase source definition.

Note: This functionality is not available for Type 3 source definition.

To create the relational model:

  1. In the Mapping Designer, click Mappings > Normalize Hyperion Essbase Cube.
    The Normalize Hyperion Essbase Cube dialog box appears.

  2. In Select Source Table, select the source definition to be normalized from the list.
  3. In New Target Fact Table, enter the name for the target Fact table.
    The names of the dimension tables are the same as the corresponding column names in the Essbase source definition.

    Note: In Target Database Type, Oracle appears as the default target database. This cannot be modified.

  4. Click Finish.
    The Normalize Hyperion Essbase Cube activity creates the target definitions in the Designer. It also creates a mapping consisting of a mapplet.
  5. From the left pane, drag the Essbase source definition to the Mapping Designer in the right pane.
  6. Link the Essbase source definition Qualifier to the Input transformation of the mapplet.
  7. Save and validate the mapping.
  8. Create the physical tables for the dimensions and Fact in Oracle (default database) before running the workflow.

    Note: For more information about creating physical tables in Oracle using the Designer, see the PowerCenter documentation.

    The database for the Fact and dimension tables can be changed from the Warehouse Designer.


Troubleshooting

The Designer Wizard fails to create the DSN with the error “Essbase Initialization Failed.”

Make sure the ARBORPATH and PATH environment variables are set properly.


Working with Essbase Targets

This chapter includes the following topics:

  • Working with Essbase Targets Overview
  • Importing an Essbase Target Definition


Working with Essbase Targets Overview

You can use DIM Adapter for Essbase to create a target definition with columns mapped to dimensions in the Essbase outline. The data stored in the source database can be transferred into Essbase database.

You can use the Designer to import target definition into the Repository. When you import Essbase target definition, the Designer displays a target definition with columns and Essbase datatypes.


Importing an Essbase Target Definition

Use the Designer Wizard to import an Essbase target definition.

To import an Essbase target definition:

In the Warehouse Designer, click Targets > Import from Hyperion Essbase.
The Import Hyperion Essbase dialog box appears.

Tip: If any of the DSNs and tables are already created, they appear under the 'Essbase' in the left pane.

Note: You can use an existing DSN if you have an authentication to connect to it. The DSN that you create belongs to a particular Essbase server, user name, and password. You must create a new DSN in the following cases:

  • You want to connect to a particular Essbase server for which DSN is not available.
  • You want to connect to a particular Essbase server for which DSN is available, but you do not have an authentication (user name and password) to connect to the Essbase server.

Note: To perform any operation on the existing DSN, select the DSN from the DSN Name list. Then, enter the user name and password and click Connect.

Creating a DSN for a Target Definition

To create a new DSN for a target definition:

  1. In the Import Hyperion Essbase dialog box, right-click 'Essbase' in the left pane and select Create DSN.

    -or-

    Click Create DSN.

    The Import Hyperion Essbase dialog box appears.

  2. In Server Name, enter the name or IP address of the computer on which the Essbase server resides.
  3. In User Name, enter the login ID of the user.
  4. In Password, enter the password of the user.
  5. Click Test Connectivity, to check whether the connection is successful.
  6. Click Save to complete the DSN creation.

    Note: To create another target definition, click Yes in the displayed dialog box. Alternatively, right-click the DSN name in the left pane of the Import Hyperion Essbase dialog box and select Create Table.

    -or-

    Click Cancel to exit from the DSN Creation Wizard.

    Note: The user must be a valid user for the specified server. The password must correspond to the same user. Multiple DSNs can be created using the DSN Creation Wizard by following the above steps.

Viewing Target DSN Information

To view Target DSN information:

  1. Click View Details in the Import Hyperion Essbase dialog box.
  2. To change DSN description, click Modify.
  3. Enter new description and click Save.

Creating a Target Definition

You need to create target definitions to load the multidimensional data in Essbase. You can create target definitions of one of the following types:

  • Accounts dimension member as a column (Type 1). This table type allows you to map Accounts dimension members as target definition columns. You can map any of Account dimension members as columns.
  • Accounts dimension as a column (Type 2). DIM Adapter for Essbase will automatically create the definition for target Type 2. A 'Data' column will be added to pass the numeric/cell values which are dimension member combinations in the database.
  • Dynamic dimension building (Type 3). This table type allows you to add members to the outline. You can create the columns to pass the member names and metadata associated with the members for adding them to the outline.

Creating a Type 1 Target Definition

The following example explains how to create a target definition, called EssbaseTable and select an appropriate Essbase application, database, and outline.

To create a Type 1 target definition under a DSN:

  1. Right-click the DSN and select Create Table to start the Table Creation Wizard.

    -or-

    Select the DSN and click Create Table.

    Note: In DSN Name, the name of the DSN appears by default. You cannot edit this value in the dialog box.

  2. In Table Name, enter EssbaseTable.
  3. In Description, enter a description for the table.
  4. Select Application, Database, and Database Outline from the respective lists.

    Note: In Outline Type, the type of the outline appears by default. You cannot edit this value in the dialog box.

  5. Select Account dimension member as a column (Type 1) to create the Type 1 target definition.
  6. Click Save to create the Type 1 target definition and exit from the Table Creation Wizard.

    -or-

    Click Cancel to exit from the Table Creation Wizard.

    Note: The first application, database, and outline are selected by default. Accounts dimension member as a column (Type 1) is selected if Accounts dimension exists in the selected Essbase outline. This option is unavailable if the Essbase outline does not contain Accounts dimension. Multiple tables can be created using the Table Creation Wizard by following the above procedure.

    Tip: When you click Save to create the Type 1 target definition, you are prompted to start creating columns for the target definition. You can create columns by clicking Yes in the displayed dialog box or by right-clicking the table name in the left pane of the Import Hyperion Essbase dialog box and clicking Create Column.

Creating Columns for Type 1 Target Definition

After you create a Type 1 target definition, you can create columns.

To create columns in a Type 1 target definition:

  1. In the left pane of the Import Hyperion Essbase dialog box, right-click the table name and select Create Column.
  2. Select the table, EssbaseTable and click Create Column.

    The Dimension-Column Mapping dialog box appears.

  3. In the left pane, select the members of the Accounts dimension to be mapped as columns.
  4. Click [image] to map the Accounts dimension member to a column.

    Tip: You can use Ctrl and Shift keys for unmapping multiple columns.

    Note: To view filtered list of dimensions and Accounts dimension members, follow the steps as in Dimension-Column Mapping for source tables.
    The Column Creation Wizard dialog box appears.

  5. In Column Name, enter a name for this column.

    Note: In Column Type, Scale, and Precision, default values appear and these values cannot be edited in the dialog box.

  6. To delete a mapped column, select it in the right pane and click [image] .

    After mapping the dimensions to columns, the Dimension - Column Mapping dialog box appears.

  7. You can arrange and sort columns by using the [image] and [image] buttons. Click the column name and click [image] to move the column up or [image] to move the column down. Source definition is arranged in the order you choose for the columns. The data is extracted depending on the source definition and source qualifier in the PowerCenter designer.
  8. Click Save.

Note: You must map at least one Accounts dimension member as column for the table. The Designer creates the columns selected in the Dimension-Column Mapping dialog box. Additionally, the columns for all the dimensions in the Essbase outline are also created.

Creating a Type 2 Target Definition

In the following example, a target definition called EssbaseTable is created and the appropriate Essbase application, database, and outline are selected.

To create a Type 2 target definition under a DSN:

  1. Right-click the DSN and select Create Table to start the Table Creation Wizard.

    -or-

    Select the DSN and click Create Table.

    Note: In DSN Name, the name of the DSN appears by default. This value cannot be edited in the dialog box.

  2. In Table Name, enter EssbaseTable that consists of alphanumeric characters.

    Note: Spaces or special characters are not allowed. This input is mandatory.

  3. In Description, enter a description for the table.
  4. Select Application, Database, and Database Outline from the respective lists.

    Note: In Outline Type, the type of the outline appears by default. This value cannot be edited in the dialog box.

  5. Select the Accounts dimension as a column (Type 2) option to create Type 2 target definition.
  6. Click Save to create the Type 2 target definition and exit from the Table Creation Wizard.

    -or-

    Click Cancel to exit from the Table Creation Wizard.

    Note: The first application, database, and outline are selected by default. By default the Accounts dimension member as a column (Type 1) option is selected if the Accounts dimension exists in the selected Essbase outline. This option is unavailable if the Essbase outline does not contain the Accounts dimension. Multiple tables can be created using the Table Creation Wizard by following the above procedure.

    Tip: After clicking Save to create the Type 2 target definition, you are prompted to start creating columns for the target definition. You can create columns by clicking Yes in the displayed dialog box or by right-clicking the table name in the left pane of the Import Hyperion Essbase dialog box and clicking Create Column.

Creating Columns in a Type 2 Target Definition

In a Type 2 target definition, the Designer Wizard creates columns for all the dimensions in the Essbase outline and a 'Data' column to receive the data that is to be loaded into Essbase. Create column is disabled as all the columns are created by the Designer Wizard.

Creating a Type 3 Target Definition

The following example explains how to create a target definition, called EssbaseTable and select the appropriate Essbase application, database, and outline.

To create a Type 3 target definition under a DSN:

  1. Right-click the DSN and select Create Table to start the Table Creation Wizard.

    -or-

    Select the DSN and click Create Table.

    Note: In DSN Name, the name of the DSN appears by default. You cannot edit this value in the dialog box.

  2. In Table Name, enter EssbaseTable that consists of alphanumeric characters.

    Note: You cannot type spaces or special characters. This input is mandatory.

  3. In Description, enter a description for the table.
  4. Select Application, Database and Database Outline from the respective lists.

    Note: In Outline Type, the type of the outline appears by default. This value cannot be edited in the dialog box.

  5. Select Dynamic dimension building (Type 3) to create the Type 3 target definition.
  6. Click Save to create the Type 3 target definition and exit from the Table Creation Wizard.

    -or-

    Click Cancel to exit from the Table Creation Wizard.

    Note: If you do not select any application, database, or outline, the first application, database, and outline are selected by default.

    Multiple tables can be created using the Table Creation Wizard by following the above procedure.

    Tip: After clicking Save to create Type 3 target definition, you are prompted to start creating columns for the target definitions. You can create columns by clicking Yes in the displayed dialog box or by right-clicking the table name in the left pane of the Import Hyperion Essbase dialog box and clicking Create Column.

Creating Columns in a Type 3 Target Definition

This section describes how to create columns in a Type 3 target definition.

To create columns in a Type 3 target definition:

  1. In the left pane of the Import Hyperion Essbase dialog box, right-click the table name and select Create Column.

    -or-

    Select the table EssbaseTable and click Create Column.

    The Dynamic Dimension Building dialog box appears.

  2. To create columns and specify Rules file, enter a column name in Column Name.
  3. Select a Column Type.
  4. Enter precision. You can enter precision only for the text fields. For Numeric, Boolean and Date, the precision is not editable. Following are the default precision values:

    Datatype
    Precision
    Text
    80
    Numeric
    15
    Date
    10
    Boolean
    1
  5. Click [image] to add the column.
    The columns created appear in the Columns Added list.
  6. To remove the columns added to the list, select the column from the Columns Added list and click [image] .
  7. Specify Rule File Selection as follows:
  8. To use a Rules file existing on the Essbase server, select the Server option and then select a Rules file from the available list.
  9. To use the Rules file local to the PowerCenter Server, select the Client option and enter the fully qualified path to the Rules file.

    Note: By default, DIM Adapter for Essbase will specify Rules file separator property as “comma” in the Essbase target. You can change the Rules file separator from the Essbase target definition after importing it. The Rules file separator should match with the actual separator mentioned at the creation of Rules file in the Oracle® Essbase Administration Services.

    Note: . Following are the options:

    • Comma
    • Tab
    • Space
    • Custom character, for example, @, #, etc.
  10. Click Save and close the dialog box.

    Note: You must create at least one column.

Note: The Type 3 table involves Flat file staging. This Flat file staging will use the separator set in the 'RuleSeparator' field from the target definition in the Designer. Set the 'RuleSeparator' field with the separator value that you used as the source file delimiter in the Rules file.

Viewing Target Definition Information

This section describes how to view the target definition information.

To view details of the target definition:

  1. In the left pane, select the table and click View Details.
    The table information is shown in the Table Wizard.
  2. To change the table description, click Modify, and enter the new text in Description.
  3. Click Save.

Type 1 Target Definition Information

Figure 3-1 shows Type 1 target definition information:

Figure 3-1. Type 1 Target Definition Information

Type 2 Target Definition Information

Figure 3-2 shows Type 2 target definition information:

Figure 3-2. Type 2 Target Definition Information

Type 3 Target Definition Information

Figure 3-3 shows Type 3 target definition information:

Figure 3-3. Type 3 Target Definition Information

Viewing Column Information for a Target Definition

You can view details of the columns created by selecting the column on the Designer Wizard.

Column Information for a Type 1 Target Definition

Figure 3-4 shows column information for a Type 1 target definition:

Figure 3-4. Column Information for a Type 1 Target Definition

Column Information for a Type 2 Target Definition

Figure 3-5 shows column information for a Type 2 target definition:

Figure 3-5. Column Information for a Type 2 Target Definition

Column Information for a Type 3 Target Definition

Figure 3-6 shows column information for a Type 3 target definition:

Figure 3-6. Column Information for a Type 3 Target Definition


Working with Administration Console

This chapter includes the following topics:

  • Working with Administration Console Overview
  • Adding an Application
  • Deleting an Application
  • Adding a Database
  • Deleting a Database
  • Modifying the Outline
  • Adding a Dimension
  • Adding a Member
  • Updating a Member
  • Deleting a Member


Working with Administration Console Overview

In this chapter, you will learn how to use the DIM Adapter for Essbase to create/delete an application and database, add/update a standard/attribute dimension, and add/update members and shared members. The Administration console will help you to perform administrative tasks on Essbase.


Adding an Application

This section explains how to add an application in Essbase.

To add an application in Essbase:

  1. In the Source Analyzer window, click Sources > Import from Hyperion Essbase.

    -or -

    Click Import from Hyperion Essbase.
    The Designer Wizard dialog box appears.

  2. Click Administration in the right bottom of the Designer Wizard dialog box.
    The Administration dialog box appears.

  3. Enter the connection information of Essbase.
    The Designer displays all the existing applications and databases present in Essbase.

  4. Right-click Applications and select Create Application to create the application in Essbase.

    The Designer prompts for the application information.

  5. Enter application name in Application.
  6. Select Application Type. This could be either Block Storage or Aggregate Storage.

    Note: The application name should not exceed 8 characters.

  7. Click OK to create the application in Essbase.
    You can see the application created in the Applications list on the Administration dialog box.


Deleting an Application

The following section explains how to delete an application from Essbase.

To delete an application from Essbase:

  1. You can see the list of applications under Applications on the main dialog box.
  2. Right-click the application name and select Delete Application.

    The application and everything that comes under the application, for example, all databases, Rules files, and Calculation scripts is deleted.


Adding a Database

The following section explains how to add a database.

To add a database:

  1. Right-click Databases under Applications and select Create Database.

    The Designer prompts you to enter the database details.

  2. Enter database name in Database.

    Note: The database name should not exceed 8 characters.

  3. Select Database Type. This could be either Normal or Currency type.
  4. Click OK to create the database under the Essbase application.
    The database created appears under the Application's databases list on the Administration dialog box.


Deleting a Database

The following section explains how to delete a database.

To delete a database:

  1. Under Applications, right-click the database name and select Delete Database.

    This deletes the database and everything that comes under that database. For example, it deletes all Rules files, Calculation scripts, and Report scripts.


Modifying the Outline

The option to modify an outline is available to add a standard/attribute dimension in the outline. Using this option, you can also add members at any level in the dimensions. The member can be a standard member or a shared member.

To work with the modify outline functionality, complete the following steps:

  1. Under Applications, select the database and click Modify Outline to modify its outline.

    Note: This option is enabled only when you click the database name.

    The Modify Outline dialog box appears.


Adding a Dimension

You can add standard as well as attribute dimensions in the outline.

Adding a Standard Dimension

To add a standard dimension:

  1. In the Modify Outline dialog box, right-click the outline name and select Add Dimension.

    The Dimension Properties section appears.

  2. Enter the dimension name in Dimension Name and comment, if any, in Comments.
  3. Select Dimension Type as None. Applicable types are None, Time, Accounts, Attribute, Country, and Currency Partition.

    Note: Attribute Type and Base Dimension are enabled only if dimension is of the Attribute type.

  4. Select the TwoPassCalc setting for the dimension from the Two-Pass Calculation list.
  5. Select Data Storage of the dimension.
  6. Select Dimensional Storage. It could be either Sparse or Dense.
  7. After entering all the required information, click OK to create the dimension in the outline.

    Note: To make the changes permanent in the outline, click Save on the Modify Outline dialog box.

Adding an Attribute Dimension

To add an attribute dimension:

  1. In the Modify Outline dialog box, right-click the outline name and select Add Dimension.

    The Dimension Properties section appears.

  2. Enter the dimension name in Dimension Name and comments, if any, in Comments.
  3. Select Dimension Type as Attribute. Applicable types are None, Time, Accounts, Attribute, Country, and Currency Partition.
  4. Select Attribute Type. Applicable types are Numeric, Text, Boolean, and Date.

    Note: For Boolean type, two members, True and False are added automatically under the attribute dimension.

  5. Select Base Dimension for this attribute dimension. All sparse dimensions will be listed out for associating the attribute dimension to the base.
  6. Select the TwoPassCalc setting for the dimension from the Two-Pass Calculation list.

    Note: Data Storage and Dimensional Storage are disabled as they will be applicable for the attribute dimension.

  7. After entering all the required information, click OK to create the dimension in the outline.

    Note: To make the changes permanent in the outline, click Save on the Modify Outline dialog box.


Adding a Member

You can add members under any dimension in the outline.

To add a member, complete the following steps:

  1. In the Modify Outline dialog box, right-click the dimension name and select Add Member.

    The Member Properties section appears.

  2. Enter the member name in Member Name and the comment, if any, in Comments.
  3. Enter alias for the member.
  4. Select the consolidation property from Consolidation. Applicable properties are Addition (+), Subtraction (-), Multiplication (*), Division (/), Percentage (%), and ignore (~).
  5. Select the TwoPassCalc setting from the Two-Pass Calculation list. It can be either True or False.
  6. Select Data Storage. Applicable settings are Store Data, Dynamic Calc and Store, Dynamic Calc, Never Share, Label only, and Shared member.
  7. Click OK to add the member to the outline.

    Note: To make the changes permanent in the outline, click Save on the Modify Outline dialog box.


Updating a Member

You can update members under any dimension in the outline.

To update a member, complete the following steps:

  1. In the Modify Outline dialog box, right-click the member name and select Update Member.

  2. After changing the member properties, click Save to reflect the changes.

    Note: You can update the member properties that you set while creating the member.

    Note: To make the changes permanent in the outline, you must click Save on the Modify Outline dialog box.


Deleting a Member

You can delete members under any dimension in the outline.

To delete a member, complete the following steps:

  1. In the Modify Outline dialog box, right-click the member name and select Delete Member.

    After confirmation, the member will be deleted.

    Note: To make the changes permanent in the outline, click Save on the Modify Outline dialog box.


Creating and Configuring Essbase Workflows

This chapter includes the following topics:

  • Creating and Configuring Essbase Workflows Overview
  • Configuring a Session with an Essbase Mapping
  • Troubleshooting


Creating and Configuring Essbase Workflows Overview

After you create mappings in the Designer, you can create a session and use the session in a workflow to extract, transform, and load data. You can create sessions and workflows in the Workflow Manager.

Before configuring an Essbase workflow, you must complete the following tasks:

  • Configure a PowerCenter Server. Configure a PowerCenter Server to run workflows. For more information about configuring a PowerCenter Server, see the PowerCenter Administrator Guide.
  • Configure source and target connections. To read data from Essbase or write data to Essbase during a workflow, you need to configure an application connection for Essbase source definitions and target definitions in the Workflow Manager.

You can use the connection parameter in the adapter connection settings. The adapter uses the connection object depending on the value mentioned for connection parameter in the parameter file. For details on the connection parameter, see the PowerCenter Workflow Administration Guide.

You can override the default application and database names stored in the table definition by specifying the values for Application and Database in the connection object. Reader and Writer use the values specified in connection object.

By default, the values for Application and Database are NULL, and the adapter uses values from the metadata extension as before. If values are specified in the connection object, the default values from the metadata extension are overridden.


Configuring a Session with an Essbase Mapping

When you configure a session using Essbase source definitions or target definitions, you must set the following session properties:

  • Connection type for Essbase source definitions
  • Connection type for Essbase target definitions

Essbase connection must be set in the session for the Essbase source or target, before you execute the workflow from the Workflow Manager.

Configuring a Session for an Essbase Source Definition

This section explains how to configure a session for Essbase source definitions.

To set the session attributes:

  1. In the Task Developer, right-click the session.
    The Edit Tasks dialog box appears.
  2. Click the Sources tab.
  3. From the Type list, select Application.
  4. From the Value list, select Essbase Connection.

    Note: For information about how to create the Essbase connection object, see Creating and Configuring Essbase Workflows Overview.

  5. Optionally, to specify an Essbase Calculation Script to calculate the database, click the Properties tab of the source and enter the name of the calculation script in Calculation Script Name. Specifying the calculation script name indicates that the calculation script resides on Essbase server. Do not specify calculation file extension “.csc”.

    Note: This functionality is not applicable for source Type 3.

  6. To specify a local calculation script file existing on the PowerCenter Server machine, the Calculation Script Name field must contain the fully qualified path to the file; else it is assumed to exist on the Essbase server. For more information about using calculation scripts, see Calculation Scripts and Retrieval of Aggregated Data.
  7. To run only the calculation script against the Essbase database, select Run Only Calculation Script.

    Note: No data can be extracted, if this option is set.
    This functionality is not applicable for source Type 3.

  8. To suppress the data, click the Properties tab, and select Suppress data. This property has the following parameters:
    • None. No data will be suppressed.
    • Missing. Data having missing values will be suppressed.
    • Zeros. Data having zero values will be suppressed.
    • Missing and Zeros. Data having missing and zero values will be suppressed.

      Note: Multiple pass-through partitions can be created for the session.
      This functionality is not applicable for source Type 3.

  9. To suppress the shared members during data extraction, click Properties and select Suppress shared members.

    Note: This functionality is not applicable for source Type 3.

  10. Through the Partition Query Condition, the query for each partition can be set on the basis of condition set for attribute dimensions, Accounts dimension members or User Defined Attributes. Use the following syntax to specify the condition:
    • Data extraction on the basis of attribute dimension. {ATTR “standard-dimension-name”} attribute-dim-name “operator” value. For example, {ATTR “Market”} Population “>” 200000.
    • Data extraction on the basis of Accounts dimension members. {MSR} Measure-member-name operator value. For example, {MSR} Sales > 10000.
    • Data extraction on the basis of User Defined Attributes (UDA). {UDA “standard-dimension-name”} UDA1 name, UDA2 name. For example, {UDA “Market”} Major_Market.

Note: When the Partition Query Condition is based on a UDA, the UDAs specified in the condition must be applied while mapping the corresponding standard dimension in the table. If the UDA used in the Partition Query Condition is not used while mapping the dimension, that UDA is ignored while extracting data. If all the UDAs mentioned in the Partition Query Condition are not used while mapping the standard dimension then the data is extracted for all UDAs used while mapping the dimension. This is the default behavior. You can specify only one of the query conditions.
This functionality is not applicable for source Type 3.

Configuring a Session for a Relational Model

The source and target connection information is to be set before running the workflow from the Task Developer.

To set the session attributes for the source and target:

  1. In the Task Developer, right-click the session.
    The Edit Tasks dialog box appears.
  2. Click the Sources tab.
  3. From the Type list, select Application.
  4. From the Value list, select an Essbase Connection object.

  5. Click the Targets tab.
  6. For each of the target definitions in the Instances column, select Relational from the Type column list.
  7. In the Connections column, select DB Connection.

    In the Value column, Oracle appears as the default target database.

  8. To set the Target load type for each of the Oracle tables to Normal, select Normal from the Value list.
  9. Click the Properties tab. Select the connection object name for $Target connection value.

Note: Before running the workflow, create the physical tables corresponding to the dimensions and the Fact tables in Oracle, which is the default database.

Configuring a Session for an Essbase Target Definition

Essbase connection is set in the session for the Essbase target running the workflow from the Workflow Manager.

To set the session attributes:

  1. In the Task Developer, right-click the session.
    The Edit Tasks dialog box appears.
  2. Click the Targets tab.
  3. From the Type list, select Application.
  4. From the Value list, select Essbase Connection.

    Note: For information about creating the Essbase connection object, see Creating and Configuring Essbase Workflows Overview.

  5. Optionally, to specify an Essbase Calculation Script to calculate the database, click the Properties tab of the target and enter the name of the calculation script in Calculation Script Name. Specifying the calculation script name indicates that the calculation script resides on Essbase Server. Do not specify calculation file extension “.csc”.

    Note: This functionality is not applicable for target Type 3.

  6. To specify a local calculation script file existing on the PowerCenter Server machine, the Calculation Script Name field must contain the fully qualified path to the file; else it is assumed to exist on the Essbase server. For more information about using calculation scripts, see Calculation Scripts and Retrieval of Aggregated Data.
  7. If you want to run only the calculation script against the Essbase database, select Run Only Calculation Script.

    Note: No data is loaded, if this option is set.
    This functionality is not applicable for target Type 3.

  8. If you want to clear the database before loading the data into Essbase, click the Properties tab of the target and set it. It has the following parameters:
    • None. Clear database will not happen.
    • All. Clears all data blocks.
    • Upper Blocks. Clears all consolidated level blocks.
    • Non-Input Blocks. Clears blocks containing values derived from calculations.

      Note: For ASO application, the Upper Blocks and Non-Input Blocks options will not be applicable.

      Note: The error log file maintains the records that are rejected or that cause the errors while loading the data into Essbase.
      This functionality is not applicable for target Type 3.

  9. Optionally, specify the name and absolute path for the error log file. Each partition has its own error log file. The error log file maintains the records that are rejected or that cause the errors while loading the data into Essbase.

    For target Type 3 (Dynamic Dimension Building), if in case no error file is mentioned, DIM Adapter for Essbase will log all the errors occurred while building dimensions into the file 'builddim.err' created in HASCONNECTORPATH. If absolute path is mentioned in the error log file name, it will be used to log error messages in dynamic dimension building.

    If no name is provided for the error log file, the errors will not be logged. The error log file path must be local to the PowerCenter Server. If the full path for the error log file is not specified, then it is created in HASCONNECTORPATH directory.

Note: For improved performance, you can configure PowerCenter partitioning information in the session. For more information about partitioning, see PowerCenter Workflow Administration Guide.


Troubleshooting

Essbase Reader/Writer library could not be loaded (on UNIX).

If this error occurs, please check the library path settings. If the same problem exists even after setting the correct library path, restart your PowerCenter Server to allow the Server to pick the correct library path.

For any other errors, contact Persistent Technical Support.


Calculation Scripts and Data Retrieval

This chapter covers the following topics:

  • Calculation Scripts and Retrieval of Aggregated Data


Calculation Scripts and Retrieval of Aggregated Data

A calculation script contains a series of calculation commands, equations, and formulas. You can create your own script for calculation other than the predefined outline calculations (formulas associated with the members).

A retrieval of aggregated data provides options to retrieve data from Essbase for different levels.

Running Calculation Scripts

You can use a session attribute wherein to enter the Essbase calculation script to run. The PowerCenter Server can run the calculation script before reading data from Essbase or after writing data to Essbase. If no calculation script is run after loading data, the dynamic calc members of the outline are automatically calculated after data loading according to the default formulas defined in the outline.

Figure 6-1 shows the Edit Tasks dialog box where you enter a calculation script name:

Figure 6-1. The Edit Tasks Dialog Box for Running Calculation Scripts

Note: Calculation script options are not applicable for source and target Type 3 definition.

The calculation script can reside on the server or the client:

  • Server—If the script resides on the Essbase server, enter only the calculation script name. For example, to use a calculation script file called "Calc" that resides on the server (in the Application\Database folder), enter "Calc" as the value for the session property Calculation Script Name.
  • Client—If the script resides on the computer hosting PowerCenter server, enter the absolute path of the script. For example, to use the calculation script file Calc.csc that resides in the c:\Calcfiles folder, enter “c:\Calcfiles\Calc.csc" as the value for the session property Calculation Script Name.

If the option "Run only calculation script" is selected, no data is extracted or loaded. Only the calculation script is fired against the Essbase server.

Retrieving Aggregated Data

If you want to retrieve the data for non-leaf members of the dimension in Essbase, you can change the Report Script through the Edit Report Script dialog box. By default, the data will be retrieved only for the dimension and its immediate children. Using the Edit Report Script dialog box, you can change the column filter command to extract data for all level members. For example, the <DIMBOTTOM command is used to get leaf-level data (default). The <ONSAMELEVELAS or <OFSAMEGEN command can be used to get different level data.

In the Edit Report Script dialog box, you can view a list of commands under the Member Selection command.

Figure 6-2. The Edit Report Script Dialog Box


Data Load Using the Rules File

This chapter covers the following topics:

  • Data Load Using the Rules File


Data Load Using the Rules File

Rules files can be used for loading data into the Essbase server database. A Rules file has logic embedded that determines how the data is loaded into the database. Target type 1 and 2 tables can use a data load Rules file while loading data into the database.

You can specify a data load Rules file in the target definition's metadata extension. You must specify the Rules file name and the Rules file separator. Make sure that the delimiter matches the value specified while preparing the Rules file. The following figure shows where you specify the Rules file and Rules file separator:

Valid values for the Rules file separator are as follows:

  • Comma
  • Tab
  • Space
  • Custom character; for example, @, #, ^

The Rules file can reside on either the server side or the client side:

  • Server—If the Rules file resides on the Essbase server, enter only the Rules file name. For example, to use a Rules file called "LoadRule" that resides on the Essbase server (In the Application\Database folder), enter "LoadRule" as the value for the RuleFile extension name.
  • Client—If the Rules file resides on the computer hosting PowerCenter server, enter the absolute path of the Rules file. For example, to use a Rules file called “LoadRule.rul” that resides in the c:\Rulesfiles folder, enter "c:\Rulesfiles\LoadRule.rul" as the value for the RuleFile extension name.


Datatypes

This chapter includes the following topics:

  • Datatypes


Datatypes

PowerCenter uses the following datatypes in Essbase mappings:

  • Essbase datatypes. Essbase datatypes appear in Essbase source and target definitions.
  • Transformation datatypes. Transformation datatypes are generic datatypes that PowerCenter uses during the transformation process. They appear in all transformations in a mapping.

PowerCenter supports the following Essbase datatypes:

  • Text. Text string.
  • Numeric. Numeric value.
  • Date. Date.
  • Boolean. Boolean value.

Essbase datatypes appear as follows in Essbase source and target definitions:

  • SQL_VARCHAR. String (corresponds to Text).
  • SQL_NUMERIC. Decimal value (corresponds to Numeric).
  • SQL_DATE. Date (corresponds to Date).
  • SQL_BIT. Boolean value (corresponds to Boolean).


Code Pages

This chapter includes the following topics:

  • Using Code Pages


Using Code Pages

When you configure an Essbase application connection in the Workflow Manager for an Oracle® Essbase source or target, the PowerCenter Server automatically selects a code page for the document data, which is the code page of the PowerCenter Server.

For a complete discussion of code page compatibility, see the PowerCenter Administrator Guide.


Glossary

This chapter includes the following topics:

  • Glossary


Glossary

Alias

An alternate name for a dimension, member, or description.

Application

A management structure containing one or more Essbase databases and the related files that control many system variables, such as memory allocation and auto load parameters.

ARBORPATH

An environment variable that specifies the Essbase root directory.

Attribute

A classification of a member in a dimension. You can specify an attribute to select and group members who have the specified attribute associated with them and to perform calculations and application-specific functions.

For example, a Product dimension can have several attributes, such as Size and Flavor. A specific member of the Product dimension can have the Size attribute 8 and the Flavor attribute Cola.

Attribute association

A relationship in an outline where a member in an attribute dimension describes a characteristic of a member of its base dimension.

For example, if product 100-10 has a grape flavor, the product 100-10 has the Flavor attribute association of grape. Thus, the 100-10 member of the Product dimension is associated with the Grape member of the Flavor attribute dimension

Attribute dimension

A type of dimension that enables analysis based on the attributes or qualities of the members of its base dimension.

Base dimension

A standard dimension that is associated with an attribute dimension. To classify a member of a base dimension, you can associate it with a member of an attribute dimension that describes the classification, such as a specific flavor.

For example, assuming products have flavors, the Product dimension is the base dimension for the Flavors attribute dimension.

Calculation

The process of aggregating or of running a calc script on a database.

Calculation script

A text file containing a set of instructions telling Essbase how to calculate a database.

Dimension

A data category used to organize business data for retrieval and consolidation of values. A dimension usually contains a hierarchy of related members grouped within it. For example, a Year dimension often includes members for each time period, such as quarters and months. Other common business dimensions might be Measure, Natural Accounts, Product, and Market.

Dimension build rules

Specifications, similar to data load rules, that Essbase uses to modify an outline. The modification is based on data in an external data source file.

Dynamic calc members

Members that the Essbase server calculates only at retrieval time. The server discards calculated values after the retrieval request is complete.

Dynamic Time Series

A process that is used to perform dynamic period-to-date reporting.

Dynamic Time Series Members

Predefined members that are used to perform Dynamic Time Series reporting.

Hierarchy

A set of multidimensional relationships in an outline, often created in a tree formation. For example, parents, children, and generations represent a hierarchy.

Leaf Member

A member that has no children.

Member

A discrete component within a dimension. For example, a time dimension might include members such as Jan, Feb, and Qtr1.

Member Selection Report Command

A type of Report Writer command that selects a range of members based on outline relationships, such as sibling, generation, and level.

Missing Data

A marker (#MISSING) indicating that data in the labeled location does not exist, contains no meaningful value, or was never entered or loaded. Missing data exists when an account contains data for a previous or a future period but not for the current period.

Online Analytical Processing

Online Analytical Processing (OLAP) is a multidimensional, multi-user, client-server computing environment for users who need to analyze consolidated enterprise data in real time. OLAP systems feature zooming, data pivoting, complex calculations, trend analyses, and modeling.

Outline

The database structure of a multidimensional database, including all dimensions, members, tags, types, consolidations, and mathematical relationships. Data is stored in the database according to the structure defined in the outline.

Standard Dimension

A dimension that is not an attribute dimensions.

Substitution Variable

A variable that acts as a global placeholder for information that changes regularly. The variable and a corresponding string value is set; the value can then be changed at any time. Substitution variables can be used in calculation scripts and report scripts.

Suppress Rows

The option to exclude rows containing missing values.

Time Series Reporting

A process of reporting data based on a date calendar, such as year, quarter, month, or week.

UDA

Formerly called user-defined attribute. A UDA is a term associated with members of an outline to describe a particular characteristic of the members. Users can specify UDAs within calc scripts and reports to return lists of members that have the specified UDA associated with them. UDAs can be applied to dense as well as sparse dimensions.

Informatica Corporation
http://www.informatica.com
Voice: (650) 385-5000
Fax: (650) 385-5500
PREV