Starting Essbase Integration Services Console

Related Topics

This topic provides instructions for starting Essbase Integration Services Console, assuming you have already started Essbase Integration Server according to the instructions in the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide.

To start Integration Services Console, use any of the following methods:

  • From the Windows Start menu, select Programs > Oracle EPM System > Essbase > Integration Services > Console.

  • Double-click the shortcut for Essbase Integration Services Console, [image], if you have placed one on your Windows desktop.

  • On the command line, type the startup file name (startOlapbldr.bat).

For example, if Integration Services Console is installed in the products\Essbase\eis\console\bin directory, type the following path:

Oracle\Middleware\EPMSystem11R1\products\Essbase\eis\console\bin\startOlapbldr.bat

Note: The default client directory, which is specified during installation, is Oracle\Middleware\EPMSystem11R1\products\Essbase\eis\console. Your application may have a different installation directory. Consult the person who installed Integration Services at your site if you are not sure of the directory specification.

You must connect to an Essbase Integration Server and OLAP Metadata Catalog, and then connect to either a relational or flat file data source. Then you can create or modify an OLAP model.


Connecting to an Essbase Integration Server and OLAP Metadata Catalog

Related Topics

Overview

Integration Server manages access to OLAP Metadata Catalogs and to data sources. An OLAP Metadata Catalog stores information that defines the data source and the structure of OLAP model and metaoutline elements. To create or modify an OLAP model or a metaoutline, you first connect to Integration Server and OLAP Metadata Catalog, and select the code page of the language that you want to use during the current Integration Services Console session.

Note: The system administrator for Integration Services must provide you with a user name and password to an OLAP Metadata Catalog before you can connect to the server.

To connect to Integration Server and OLAP Metadata Catalog:

1 Select Connections > OLAP Metadata Catalog > Connect to display the Login dialog box, from the OLAP Model main window or the OLAP Metaoutline main window,

Note: The Login dialog box is displayed automatically when you start Essbase Integration Services Console. You can redisplay the Login dialog box at any time while working in the OLAP Model main window or the OLAP Metaoutline main window by using the procedures described in this topic.

2 In the Server drop-down list box under Essbase Integration Services, type or select the name or IP address of an Integration Server computer; for example, cypress.

If the port number on which Integration Server communicates with the console has been set to a value other than the default, you must type the server name or IP address and type the nonstandard port number, separated by a colon; for example,

cypress:3390

3 In the OLAP Metadata Catalog drop-down list box, type or select the name of an OLAP Metadata Catalog; for example, TBC_MD for the sample TBC (The Beverage Company) database.

If you are using Oracle Call Interface (OCI) direct connection, be sure to prefix the Net Service Name, found in the tnsnames.ora file, or the Schema Name if you are using onames, with Oracle by typing Oracle: and then the Net Service Name or Schema Name.

4 In the Code page drop-down list box, select the code page of the language you want to use during the current Integration Services Console session.

The code page is a portion of the locale, which identifies the local language and cultural conventions such as the formatting of currency and dates, and the sort order of data.

5 In the User Name drop-down list box under Essbase Integration Services, type or select your user name.

6 In the Password text box under Essbase Integration Services, type your password.

Note: Use the same user name and password that you use to connect directly to the database server and to access the database that contains the OLAP Metadata Catalog.

7 Click OK, or enter information for connecting to an Essbase Server.

Note: You can run Integration Server and Essbase Server on the same server computer.


Connecting to an Essbase Server

Related Topics

Essbase Server creates and manages Essbase databases. You do not have to connect Essbase Integration Services Console to an Essbase Server to create an OLAP model or metaoutline, but you must connect to an Essbase Server to load members or data into an Essbase database.

In the Login dialog box, you can specify the information for connecting to an Essbase Server, but Integration Services Console does not connect to the Essbase Server until you attempt to view a sample Essbase outline or to load members or data.

Note: The system administrator for the Essbase Server must provide you with a user name and a password before you can connect.

To set connection information for an Essbase Server:

1 In the OLAP Model main window or OLAP Metaoutline main window, select Connections > OLAP Metadata Catalog > Connect to display the Login dialog box.

Note: The Login dialog box is displayed automatically when you start Integration Services Console. You can redisplay the Login dialog box at any time while working in the OLAP Model main window or the OLAP Metaoutline main window by using the procedures described in this topic.

2 In the Server text box under Essbase, type or select the name of a computer running Essbase Server; for example, sequoia.

Note: If you are using Integration Services Console on a client computer that is outside the firewall for your network, and you require access to an Essbase Integration Server and Essbase Server located inside the firewall for your network, be sure to use a name or an IP address for the Essbase Server that is accessible from both sides of the firewall.

3 In the User Name drop-down list box under Essbase, type or select your Essbase user name.

4 In the Password text box under Essbase, type your Essbase password.

The user name and password for the Essbase Server can be different from the user name and password for the Integration Server connection.

5 Click OK.

Note: You can run Essbase Server and Integration Server on the same server.


Connecting to Data Sources

Related Topics

Overview

A data source is an external data repository—typically a large relational database or a collection of flat files—whose data you want to analyze by using Essbase. You must connect Essbase Integration Services to all data sources that you plan to use in creating OLAP models and metaoutlines, and in loading data into an Essbase database.

Note: You can connect to more than one relational data source or flat file data souce to create or edit OLAP models.

To connect to a data source:

1 In the Integration Services Console Welcome dialog box, click the appropriate icon to create a new OLAP model or metaoutline.

Alternatively, select the Existing or Recent tab and double-click an OLAP model or metaoutline to open it for editing.

The Data Source dialog box is displayed.

2 In the Data Source drop-down list box, type or select the data source to be used; for example, TBC in the sample application.

Any external data sources that you want to use must be configured on the computer that is running Integration Server. If the data source that you need is not visible in the scroll list, contact the database administrator or system administrator. For more information about troubleshooting server and data source connections, see the Essbase Integration Services System Administrator’s Guide.

Note: If you are using Oracle Call Interface (OCI) direct connection, be sure to prefix the Net Service Name, found in the tnsnames.ora file, or the Schema Name if you are using onames, with Oracle by typing Oracle: and then the Net Service Name or Schema Name.

3 In the Code Page drop-down list box, select the code page of the language you want to use during the current Integration Services Console session, and click OK.

The code page is a portion of the locale, which identifies the local language and cultural conventions such as the formatting of currency and dates and the sort order of data.

4 In the User Name drop-down list box, type or select your user name.

Note: If you are using a flat file data source, no user name is required.

5 In the Password text box, type your password.

Note: If you are using a flat file data source, no password is required.

The left frame of the OLAP Model main window initially displays information about the first data source to which you are connected. Data source information is displayed hierarchically by data source name and owner name.

To connect to additional data sources:

1 Select Connections > Add Data Sources > Connect.

The Data Source dialog box is displayed. You can connect to any number of available data sources without closing this dialog box.

2 In the Data Source drop-down list box, type or select the additional data source to which you want to connect.

Note: If you are using Oracle Call Interface (OCI) direct connection, be sure to prefix the data source name with Oracle by typing Oracle: before the data source.

3 In the Code Page drop-down list box, select the code page of the language you want to use during the current Integration Services Console session and click Connect.

The code page is a portion of the locale, which identifies the local language and cultural conventions such as the formatting of currency and dates and the sort order of data.

4 In the User Name drop-down list box, type or select your user name.

Note: If you are using a flat file data source, no user name is required.

5 In the Password text box, type your password.

Note: If you are using a flat file data source, no password is required.

6 Repeat steps 2 through 5 for each data source to which you want to connect.

As you connect to additional data sources, the left frame of the OLAP Model main window displays information about all data sources to which you are connected.

7 When you have finished connecting to all appropriate data sources, click Close.


Reconnecting When Connections Are Lost

Related Topics

Overview

If a connection is lost while you are working on an OLAP model or metaoutline, you can reconnect to Essbase Integration Server and OLAP Metadata Catalog without losing any unsaved work and without having to restart the console.

To reconnect to Essbase Integration Server and OLAP Metadata Catalog:

1 Start the server following the instructions in "Starting the Server on Windows Systems" or "Starting the Server on UNIX Systems" in the Essbase Integration Services System Administrator's Guide.

2 In the Integration Services Console, select Connections > OLAP Metadata Catalog > Reconnect.

Note: The connection is made automatically; no messages are displayed, and you do not need to reenter information in the Login dialog box.


Correcting Connection Problems

If you have problems connecting from Essbase Integration Services Console to Essbase Integration Server, OLAP Metadata Catalog, Essbase Server, or the data source, use the following checklist to help diagnose the problem:

  • Was the server temporarily down or not available? Try again to connect.

  • Did you enter the correct server name? Your network configuration may require a fully-qualified internet host name; for example, myserver.mycompany.com.

  • Did you enter the correct user name and password? Passwords are usually case-sensitive. Make sure that the Caps Lock key is not activated.

  • Do you have a network connection to the server? Check your network connection by using the ping command. See Microsoft Windows online help for more information about using ping.

If the above steps do not resolve the problem, contact your database administrator or system administrator. For more information about troubleshooting server connections, see the Essbase Integration Services System Administrator’s Guide.


Creating an OLAP Metadata Catalog Automatically

Related Topics

Overview

Whether your data resides in a relational database or flat file, you must create an OLAP Metadata Catalog in a relational database. Integration Server uses the OLAP Metadata Catalog to store information about OLAP models and metaoutlines that you create.

After you complete installation of Essbase Integration Services and start the console, the program automatically displays the OLAP Metadata Catalog Setup dialog box.

To create an OLAP Metadata Catalog automatically:

1 From the Essbase Integration Server drop-down list in the OLAP Metadata Catalog Setup dialog box, type or select the name of the server computer on which you have installed Integration Server.

If the port number on which Integration Server communicates with the console has been set to a value other than the default, you must type the server name or IP address and type the nonstandard port number, separated by a colon; for example,

cypress:3390

2 From the Catalog ODBC DSN drop-down list, select the ODBC data source name for the OLAP Metadata Catalog that you are creating.

If you are using Oracle Call Interface (OCI) direct connection, be sure to prefix the Net Service Name, found in the tnsnames.ora file, or the Schema Name if you are using onames, with Oracle. Do this by typing Oracle: and then the Net Service Name or Schema Name. See the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide for more information.

Note: Before you can create the catalog, you must have created the database in which you will store the OLAP Metadata Catalog, assigned the appropriate user permissions, and configured the ODBC or OCI connection.

3 From the Code Page drop-down list, select the code page that corresponds to the code page of the source database that will be used as the OLAP Metadata Catalog.

The code page is a portion of the locale, which identifies the local language and cultural conventions such as the formatting of currency and dates and the sort order of data.

Note: If you want to create a Unicode OLAP Metadata Catalog, you must manually create it by running the scripts provided, as described in the chapter on OLAP Metadata Catalog setup in the Essbase Integration Services System Administrator’s Guide.

4 In the User Name text box, type the name of the user to whom you have assigned access to the database where the OLAP Metadata Catalog will be stored.

5 In the Password text box, type the password for the user to whom you have assigned permission to access the database where the OLAP Metadata Catalog will be stored.

6 Select the Show this dialog at Startup check box to display the OLAP Metadata Catalog Setup dialog box automatically each time you start up Essbase Integration Services Console.

If you leave the Show this dialog at Startup check box unchecked, you can access the OLAP Metadata Catalog Setup dialog box at any time by selecting Tools > Create Catalog from either the OLAP Model or OLAP Metaoutline main window.

7 Click Create.

Integration Services creates the OLAP Metadata Catalog.

8 Click Close to exit the OLAP Metadata Catalog Setup dialog box.

If you are migrating from a previous release of Integration Services, the automatic-creation process for OLAP Metadata Catalog upgrades your existing OLAP Metadata Catalog to the current version.


Creating the Sample Application Automatically

Related Topics

Overview

After you have completed OLAP Metadata Catalog creation as part of the Essbase Integration Services installation process, you can choose to create automatically the standard portion of the sample application, The Beverage Company (TBC), on a relational data source.

Notes:

  • If you plan to use the Hybrid Analysis portion of the sample application, you must follow the setup procedures in the Essbase Integration Services System Administrator’s Guide.

  • If you plan to use the Unicode portion of the sample application, you must follow the setup procedures in the Essbase Integration Services System Administrator’s Guide.

To set up the standard sample application automatically:

1 Select Tools > Create Sample.

2 In the Sample ODBC DSN text box of the Sample Application Setup dialog box, type or select the data source name for creating tables in the source database.

If you are using Oracle Call Interface (OCI) direct connection, be sure to prefix the Net Service Name, found in the tnsnames.ora file, or the Schema Name if you are using onames, with Oracle. Do this by typing Oracle: and then the Net Service Name or Schema Name. See the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide for more information.

3 The User Name text box displays the name, TBC (lowercase tbc for Informix). You must use this name to create the TBC sample application database.

4 In the Password text box, type the password for the user to whom you have assigned access to the TBC sample application database.

5 Click Create to create the sample application.

Integration Services creates the necessary tables, loads the TBC sample data into the tables, and loads the sample OLAP model and metaoutline data into the OLAP Metadata Catalog.

6 Click Close to exit the Sample Application Setup dialog box.

Circumstances to Consider

  • If Integration Services detects that you are not currently connected to the OLAP Metadata Catalog where you want to load the sample OLAP model and metaoutline data, the Login dialog box is displayed. You can then enter the information necessary to connect. After you close the Login dialog box and are returned to the Sample Application Setup dialog box, click OK again to initiate setup of the sample application.

  • If Integration Services detects all the following components—an existing sample data source, sample OLAP model, and sample OLAP metaoutline—in the OLAP Metadata Catalog, you are prompted with the message that tables already exist in the database. Click OK to return to the Sample Application Setup dialog box to reenter information, or click Delete to delete the existing sample application data. If you click Delete, the sample OLAP models and metaoutlines in the current OLAP Metadata Catalog are deleted and replaced with the sample OLAP model and metaoutline for the current release.

  • If Integration Services detects that any or all of the following components—an existing sample data source or sample OLAP model or sample OLAP metaoutline—are missing, the missing components are created without any prompts or messages.

After you have completed creating The Beverage Company (TBC) sample application, you are ready to begin working with the console to create your own sample OLAP models and metaoutlines.


Understanding the OLAP Model Workflow

The following figure illustrates the workflow for creating an OLAP model. For relational data source or flat file users, this workflow includes creating and working with an OLAP model; creating a fact table, an accounts dimension, and a time dimension; creating and working with dimensions; editing and transforming the columns of dimension tables; joining dimension tables; and creating and working with hierarchies.

Before you start working with an OLAP model, make sure that the OLAP Metadata Catalog in which your OLAP model will be stored, the computer on which Essbase Integration Server is running, and the data sources are accessible. These items can be on server computers on a network; they do not need to be on your local computer to be available to Essbase Integration Services Console.


Displaying Tables, Views, Synonyms, and User-Defined Tables for Relational Data Sources

When you access one or more relational data sources either to create or to edit an OLAP model, information on tables, views, synonyms, and user-defined tables is presented in the left frame.

When you first access the OLAP Model main window to create or

to edit an OLAP model, the information in the
left frame is presented
in a tree in collapsed form, as shown in the illustration at left.

You can expand any of the items in the left frame of the OLAP Model main window. In the illustration below, the view of the tables and the view of the user-defined tables in the primary relational data sources are expanded in the tree.

When items are expanded, the left frame displays information about the data sources to which the OLAP model is connected.

The data source from which the fact table was created is the primary data source. The primary data source is displayed first in the tree followed by any applicable secondary data sources.

For each data source, information is displayed hierarchically by owner name (by database name for Teradata users).

For each owner name (or database name with Teradata), data is further sorted and organized by tables, views, synonyms, and user-defined tables.


Displaying Tables for Flat File Data Sources

Related Topics

When you access one or more flat file data sources either to create or to edit an OLAP model, information on tables is presented in the left frame.

Note: User-defined tables are not supported for flat file data sources.

When you first access the OLAP Model main window to create or

to edit an OLAP model, the information in the
left frame is presented
in a tree in collapsed form, as shown in the figure

at left. There is no concept of a specific owner with a flat file data source; therefore, the generic label PUBLIC is used for the owner name.

You can expand the items in the left frame of the OLAP Model main window. In the following figure, two flat file data sources are being accessed. The view of the tables in the primary data source are expanded in the tree.

When items are expanded, the left frame displays information about the data sources to which the OLAP model is connected.

The data source from which the fact table was created is the primary data source. The primary data source is displayed first in the tree followed

by any applicable secondary data sources.

For each data source, information is displayed hierarchically by owner name (always PUBLIC for flat files).

For each owner name, data is further sorted and organized by tables and columns.


Displaying Column Names

The tables in the data source are displayed in an expanded view in the left frame of the OLAP Model main window, as shown in Displaying Tables, Views, Synonyms, and User-Defined Tables for Relational Data Sources , and Displaying Tables for Flat File Data Sources. Column names can also be viewed in the left frame of the OLAP Model main window.

When working with data sources, as you create a fact table or a dimension table, you can display the names of the columns that the source table contains by clicking the plus sign, [image], to the left of the table name. The columns are displayed in logical

You can use this information to ensure that you have selected the correct table or view from the data source.

You can view the data contained in the columns of the table or view by right-clicking the table name, view name, or any column name and selecting View Table Data. You can also select the table, and then select View > View Table Data to view the same information.

Note: When you display columns in the left frame, Essbase Integration Server displays only certain ODBC data types. For a complete list, see About Column Data Types .

To display the column names of a source table:

1 Expand the data source table by clicking the plus sign, [image], in front of the table name.

The table expands to include an alphabetical list of columns. An icon identifies each column as a column, [image], or as a primary key, [image]. You can expand as many tables as you want and scroll through each table to display column and key names.

2 Optional for relational data sources. In the left frame, for the appropriate data source, expand views or synonyms by clicking the plus sign, [image], to the left of the Views or Synonyms items in the tree.

  • A view is a logical table created by combining columns from one or more tables in the relational data source (see the Essbase Integration Services Data Preparation Guide).

  • Synonyms (aliases) are displayed only for RDBMSs that support synonyms.

You can display tables, views, synonyms, or any combination of tables, views, or synonyms in the left frame. This online help refers to tables. Whenever tables in the left frame are discussed, synonyms and views are implied.

3 Click the minus sign, [image], in front of the table to collapse it.

Displaying Column Names in the Right Frame

To display column names after dragging a table to the right frame, use one of the following methods:

  • Drag the corner of the dimension table or fact table down until the table is large enough to display its columns.

  • Right-click the dimension table or fact table and select View Columns.

To collapse the column list, right-click and select View Columns again.

  • Select the dimension table or fact table and select View > View Columns.

To collapse the column list, select View > View Columns again.

  • To display all columns of all dimension tables (including the fact table) currently in the right frame, click in a blank space in the right frame and select View > View All Columns.

A scrollable list of column names is displayed for each dimension table.

For illustrations and explanations of the icons that identify columns, see Icons in Essbase Integration Services Console.


Viewing Table Data

Related Topics

To display the contents of a data source table that is listed in the left frame of the OLAP Model main window:

1 Select the data source table, right-click, and select View Table Data.

The first 100 rows of the selected source table or view are displayed in the View Table Data dialog box.

2 To view the next 100 rows, click Next.

3 To display the previous 100 rows, click Previous.

4 To display all rows in the table, click Show All.

Retrieval may take some time, depending on the number of rows that the table contains.

5 When you are finished viewing table data, click Close.


Creating User-Defined Tables

Related Topics

Overview

Example

You can create a user-defined table to establish a virtual view of the relational schema of your data source. This enables the schema to function correctly when you are creating OLAP models.

Note: Essbase Integration Services does not support user-defined tables created in Sybase or Informix.

To create a user-defined table:

1 Open the OLAP model to which you want to add a user-defined table.

2 Select the User-Defined Tables icon.

3 Right-click and select Add User-Defined Tables to display the User-Defined Table Definition dialog box.

Alternatively, you can select Edit > Add User-Defined Tables.

4 In the Table Name text box, type the name of the user-defined table that you want to create.

5 In the Table Definition text box, type the SQL statement to create the user-defined table using standard SQL statements, beginning with the SELECT statement portion of the standard CREATE VIEW statement.

For example:

Note: The SQL statement you enter defines the structure of the user-defined table and can include any number of source columns from any number of data source tables, as allowed by your specific RDBMS. It becomes the basis for a logical table in the data source.

6 Click OK to create the user-defined table.

Note: You will see a warning if the table is created in such a way that might affect its usability; for example, if modifications are made to column types.


Editing User-Defined Tables

Related Topics

Overview

Example

Follow the procedure below to edit the SQL definition of a user-defined table.

Notes: Essbase Integration Services does not support user-defined tables created in Sybase or Informix.

To edit a user-defined table:

1 Open the OLAP model that contains the user-defined table that you want to edit.

2 Expand the User-Defined Tables icon.

3 Right-click the user-defined table you want to edit and select Edit Definition to display the User-Defined Table Definition dialog box.

4 In the Table Definition text box, edit the SQL statement to create the user-defined table using standard SQL statements, beginning with the SELECT statement portion of the standard CREATE VIEW statement.

For example:

Note: The SQL statement you enter defines the structure of the user-defined table and can include any number of source columns from any number of data source tables, as allowed by your specific RDBMS. It becomes the basis for a logical table in the data source.

5 Click OK to save the changes to the user-defined table.

Note: You will see a warning if the table is created in such a way that might affect its usability; for example, if modifications are made to column types.


Changing the Primary Data Source That Contains User-Defined Tables

Related Topics

Overview

Example

When working with an OLAP model, you can access an alternative data source as the primary data source for the model, even if the original primary data source contained user-defined tables. As long as the tables and columns that the OLAP model accessed in the original data source are present in the alternative data source, Essbase Integration Server will verify this model as valid.

To change the primary data source that an OLAP model accesses:

1 If you have not already done so, connect to an Essbase Integration Server and an OLAP Metadata Catalog.

2 Optional: Connect to an Essbase Server.

3 Connect to the alternative data source.

Note: If the tables and columns referenced by the user-defined table are not present in the alternative data source, an error message is displayed.

In the left frame of the OLAP model, the alternative primary data source name is shown next to the [image] icon.

4 Expand the User-Defined Table icon ( [image]) under the primary data source name to view the tables.

The user-defined tables that were created with the original primary data source are now displayed under the alternative primary data source in the following format:

<original_primary_data_source_name>.user-defined_table_name

As shown below, a user-defined table, MySales, created with TBC as the primary data source is displayed as TBC.MySales when tbc2 is used as an alternative primary data source:

Note: If the alternative data source to which you are connected contains the same tables and columns used in the user-defined table, a plus sign ( [image]) is displayed next to the user-defined table. The absence of a [image] sign indicates that the tables and columns referenced in the user-defined table are not present in the alternative data source.

5 Edit the OLAP model, as necessary.

6 Select File > Verify.

7 When you are finished working with the OLAP model, save and close.


Deleting User-Defined Tables

Related Topics

To delete a user-defined table:

1 Open the OLAP model that contains the user-defined table that you want to delete.

2 Expand the User-Defined Tables icon.

3 Right-click the user-defined table you want to delete and select Remove.

4 Click Yes at the prompt to confirm that you want to delete the user-defined table.


Creating or Editing OLAP Models for Relational or Flat File Sources

Related Topics

Overview

Example

To create or edit OLAP models, be sure that Essbase Integration Server is running, then start Integration Services Console and connect to Integration Server, OLAP Metadata Catalog, and a data source.

For more information about starting Integration Server, see the Essbase Integration Services System Administrator's Guide. If you have problems connecting, see Correcting Connection Problems.

If you just started Integration Services Console, the New tab of the Welcome dialog box that you use to choose whether to create or edit an OLAP model opens automatically.

If you are already running Integration Services Console and are connected to OLAP Metadata Catalog, select File > New or File > Open to open the Welcome dialog box.

To create a new OLAP model:

1 In the New tab of the Welcome dialog box, select the Model icon, [image].

2 In the Data Source dialog box, log in to the appropriate data source.

3 Click Open.

You are now ready to add the fact table and dimensions. See the topics listed under Creating the Fact Table, Accounts Dimension, and Time Dimension and Creating and Working with Dimensions to continue the process of creating an OLAP model.

To edit an OLAP model:

1 In the Welcome dialog box, select the Existing tab or the Recent tab.

2 Select the OLAP model name.

For more information about the OLAP model, look in the Details group box.

3 If you want to place write-lock privileges on the OLAP model, select Exclusive Access.

Exclusive Access enables you to modify and save the OLAP model. If Exclusive Access is not selectable, another user has write-lock privileges and you have read-only privileges or no privileges.

4 Click Open.

5 If necessary, log on to the appropriate data source.


Validating OLAP Models Manually

When you save an OLAP model, Essbase Integration Server automatically validates it and writes it to OLAP Metadata Catalog. If you prefer, you can manually validate an OLAP model before saving it. You can save an OLAP model that does not pass validation, but you cannot use an invalid OLAP model to create a metaoutline.

To validate an OLAP model manually:

  • Select File > Verify.

If the OLAP model is valid, a dialog box informs you that the OLAP model contains no errors.

If the OLAP model is invalid, the console displays the OLAP Model Validation Errors dialog box, detailing the errors in the OLAP model.

If the OLAP model is invalid, check for the following conditions:

  • The OLAP model contains a fact table. See About the Fact Table - Overview, About the Time Dimension - Overview , and About the Accounts Dimension - Overview.

  • The OLAP model contains at least one measures value; for example, sales amounts, quantities sold, or commissions. (Measures values can be included in the fact table and do not necessarily need to be in an accounts dimension. If the business is a non-financial application, for example, you do not need to create an accounts dimension.) See About the Time Dimension - Overview and About the Accounts Dimension - Overview .

  • The OLAP model contains at least one dimension.

  • Each dimension table in the OLAP model is joined to the fact table or to another dimension table.

  • If the OLAP model contains a single dimension, the dimension contains at least one column.

  • Dimensions in the OLAP model that are made up of two or more physical tables have joins defined. See About Joins .

  • The OLAP model contains no dimensions with duplicate names.

  • The OLAP model contains no aggregate storage errors.

To save a model that does not pass validation, see Saving an OLAP Model Manually


Saving OLAP Models

Related Topics

Overview

If the OLAP model that you want to save has been previously saved and you do not want to give the OLAP model a different name, select File > Save. Essbase Integration Server writes the changes to the OLAP Metadata Catalog to which you are connected.

If you have not previously saved the OLAP model or if you want to save the OLAP model to a different name, you must use the Save New OLAP Model dialog box.

To use the Save New OLAP Model dialog box to save an OLAP model:

1 Select one of the following menu options:

  • If you are saving an OLAP model for the first time, select File > Save.

  • If you are saving a previously saved OLAP model to a new name, select File > Save As .

The Save New OLAP Model dialog box is displayed.

Note: If you save an existing model, the Save New OLAP Model dialog box is not displayed

2 In the OLAP Model Name text box, enter the name of the OLAP model.

3 From the Security drop-down list box, select one of the following options to determine who can access the OLAP model:

Option Permitted | Activity
Allow read-write access for other users. | Other users can read and write to the OLAP model. This setting is the default.
Allow read access for other users. | Other users can read the OLAP model but not write to the OLAP model; that is, another user cannot save changes to the OLAP model.
Disallow all access for other users. | Other users can neither read nor write to the OLAP model. Only you can read or write to the OLAP model.

Note: To change the permissions on an OLAP model after you create it, you must use the tools provided with the source database that contains the relevant OLAP Metadata Catalog or use File > Save As to save the OLAP model to a different name. See the Essbase Integration Services System Administrator’s Guide.

4 If you want to describe the OLAP model, in the Description text box, enter a brief description.

The description can include up to 255 characters. Essbase Integration Services Console displays the description in the following places:

  • The Save New OLAP Model dialog box when you select File > Save As.

  • The OLAP Model Properties dialog box (see Changing Read/Write Access).

  • The Existing tab of the Welcome dialog box (see Working with an OLAP Model).

5 Click OK.

If the OLAP model is invalid, Integration Services Console displays an error message. You can save the OLAP model without correcting the error, but you cannot use an invalid OLAP model to create a metaoutline.

Note: If you attempt to save an OLAP model that another user currently has open, a dialog box informs you that the OLAP model is currently locked or open. You must wait to save until all other users close the OLAP model.


Saving an OLAP Model Under a Different Name

Related Topics

Overview

You can save an OLAP model under a different name while keeping or modifying any existing metaoutlines associated with the model.

To save an OLAP model under a different name:

1 Open an existing model and make the appropriate changes, if any.

2 Select File > Save Model As to display the Save Model As dialog box.

3 In the Model drop-down list box, select the name of the model that you want to save under a different name.

4 In the Save text box, type the new model name.

5 If you want to save the model without saving its associated metaoutlines, check the Save Model Only check box.

If you do not check this box, any changes made to the OLAP model will be reflected in the associated metaoutlines.

6 Click the OK button to save the model under the new name.


Renaming an OLAP Model or Metaoutline

Related Topics

To rename an OLAP model or metaoutline:

1 Open an existing model or metaoutline.

2 Select File > Rename Model/Metaoutline to display the Rename Model/Metaoutline dialog box.

3 In the Type drop-down list box, select the type to be renamed: OLAP model or metaoutline.

4 In the Name drop-down list box, select the name of the model or metaoutline to be renamed.

5 In the New Name text box, type the new name for the model or metaoutline.

6 Click the OK button to rename the model or metaoutline.


Changing Read/Write Access

At any time after you save an OLAP model, you can change the access rights that you originally assigned by using the OLAP Model Properties dialog box.

To change read/write access to an OLAP model:

1 In a blank space of the right frame, right-click and select Properties.

Essbase Integration Services Console displays the General tab of the OLAP Model Properties dialog box.

2 In the Security drop-down list box, change the user access defined for the model.

For more information about security settings, see the table in Saving an OLAP Model Manually.

3 Click OK.


Deleting an OLAP Model

When you delete an OLAP model, Essbase Integration Services removes the OLAP model from OLAP Metadata Catalog.

To delete one or more OLAP models:

1 Make sure that you are connected to the OLAP Metadata Catalog in which the OLAP model is stored.

If you are connected, the lower-right corner of the OLAP Model main window contains the appropriate OLAP Metadata Catalog name (for example, TBC_MD in the sample TBC database).

If you are not connected, select Connections > OLAP Metadata Catalog > Connect and log on to the correct OLAP Metadata Catalog.

2 Select File > Delete to display the Delete an OLAP Model/Metaoutline dialog box.

3 Select the OLAP model to delete and click Delete.

Note: To select multiple OLAP models, select one OLAP model, hold down the Ctrl key, and select each additional OLAP model. Integration Services Console displays a dialog box that prompts you to confirm that you want to delete the OLAP model.

Tip: Use caution when deleting. You cannot undo the deletion of an OLAP model.

4 Click Yes.

Essbase Integration Server deletes the selected model from OLAP Metadata Catalog.

Note: If you attempt to delete an OLAP model that you or another user currently has open, a dialog box informs you that the OLAP model is currently locked or open. If you have it open, close the OLAP model and select Connections > OLAP Metadata Catalog > Delete Locks. If other users have the OLAP model open, you must wait until they close it.


Using Flat Files as Data Sources

Related Topics

In Integration Services, you can use a text file (often referred to as a flat file) as a data source; however, the following conditions may affect your use of the product:

  • The Open Database Connectivity (ODBC) driver for text files cannot extract month names from date fields in flat files. For example, it cannot extract "January" from the date field "01/30/04." The ODBC driver does, however, support the numerical representation. During a member load, you can apply a transformation so that the numerical value is converted into the name of the month.

  • If the data source is a flat file, the default Date Hierarchies provided by Integration Services does not function with month names. If you wish to use the Date Hierarchy functionality, define your month as a numerical value.

  • If the data source consists of several flat files that resemble or consist of tables, the process of importing data will be similar to importing relational data. If the data source is one large, contiguous flat file with several different columns, it is likely that you will need to cleanse your data beforehand. You may also need to define your joins explicitly for optimal performance. See the Essbase Integration Services Data Preparation Guide for information on cleansing data and defining joins.

  • Integration Services supports data source table and column names that contain spaces.

  • User-defined tables are not supported for flat file data sources.


About OLAP Models and Flat File Data Sources

Related Topics

A flat file data source may be set up in one of two ways:

  • Multiple text files, one file for each table in the source database

  • One text file containing all tables in the source database

The way the flat file data source is set up affects the way you create an OLAP model, as described in the following sections.

Working with Multiple Flat Files

If a data source consists of multiple flat files, the flat files are displayed as tables when you expand the TABLES icon in the left frame of the OLAP Model main window. For example, if there are 10 flat files defined in the data source, you see 10 tables listed under the TABLES icon.

When working with a multiple flat file data source, you create the model similarly to the way you create an OLAP model when the data source is a relational database. You drag tables to right frame of the OLAP Model main window to create a fact table, a time and an accounts dimension, and standard dimensions as you require. The following steps outline the process:

1 Manually create a fact table.

Create the fact table by dragging one of the source tables to the right frame of the OLAP Model main window. You are prompted to create a time and an accounts dimension as you create the fact table, but you can choose to create them manually later.

See the topics listed under "Creating the Fact Table, Accounts Dimension, and Time Dimension" for information on manually creating the time and accounts dimensions.

2 Manually create the dimensions that you require.

3 Manually create hierarchies.

All other OLAP model tasks, such as editing columns, adding columns to dimensions, or transforming columns, are generally accomplished in the same manner as when an OLAP model is connected to a relational data source.

Be sure to review join information in the OLAP model and define and edit joins as necessary. See the topics listed under "Joining Dimension Tables" for information on defining and editing joins.

Working with a Single Flat File

If a data source consists of a single flat file, this flat file is displayed as a single table when you expand the TABLES icon in the left frame of the OLAP Model main window. That is, if there a single flat file defined in the data source, you will see one table listed under the TABLES icon.

When working with a data source consisting of a single flat file, you create the model by repeatedly dragging the same table to the right frame to create the fact table, a time and an accounts dimension, and standard dimensions as you require. The following steps outline the process:

1 Manually create a fact table.

Create the fact table by dragging the single flat file to the right frame of the OLAP Model main window. You are prompted to create a time and an accounts dimension as you create the fact table, but you can choose to manually create them later.

See the topics listed under "Creating the Fact Table, Accounts Dimension, and Time Dimension" for information on manually creating the time and accounts dimensions.

2 Manually create the dimensions that you require.

Again, drag the single flat file to the right frame of the OLAP Model main window for each standard dimension that you want to create.

You then edit the dimensions by adding or deleting columns so the fact table and all dimensions contain the columns as appropriate for your OLAP model.

3 Manually create hierarchies.

All other OLAP model tasks, such as editing columns, adding columns to dimensions, and transforming columns, are generally accomplished in the same manner as when an OLAP model is connected to a relational data source.

Be sure to review join information in the OLAP model and define and edit joins as necessary. See the topics listed under "Joining Dimension Tables" for information on defining and editing joins.


Creating the Fact Table Automatically

Related Topics

Overview

If you are accessing a relational data source, you can create the fact table and the related Time and Accounts dimensions automatically using Essbase Integration Services Console.

Note: If you are accessing a flat file data source, do not use this functionality.

To create the fact table automatically:

1 In the left frame of the OLAP Model main window, select the data source from which you want the fact table to be created, then select Tools > Create Fact Table, or click the Create Fact Table toolbar button, [image] , to access the Create Fact Table dialog box.

Note: You can also access this dialog box by using the "autodetect" link in the Intelligent Help (Fact Table process button, Create Fact Table Automatically task).

2 In the Create Fact Table hierarchical view, click the plus (+) sign to expand Existing Fact Tables and Fact Table Candidates views.

The Existing Fact Tables view shows any existing fact tables in the OLAP Metadata Catalog that are present in the current data source. The Fact Table Candidates view shows the fact tables detected in the current data source to which you are connected.

3 Select the table or tables on which to base your fact table.

Tip: To select only one table to use as the fact table, click the table name. To select more than one table to use for the fact table, press and hold down the Ctrl key and click your fact table choices.

4 Select the Create a Time dimension from the fact table check box to create the Time dimension automatically. Clear this check box if you prefer to create the Time dimension manually at a later time.

Note: If you are planning to enable Hybrid Analysis in a subsequent metaoutline based on the OLAP model you are now building, you must create a time dimension for Hybrid Analysis to work correctly. However, creating the time dimension from a timestamp column in the fact table can result in missing data due to ragged hierarchies.

5 Select the Create an Accounts dimension from the fact table check box to create the Accounts dimension automatically. Clear this check box if you prefer to create the Accounts dimension manually at a later time.

6 Click OK to create the fact table and, if applicable, related Time and Accounts dimensions.


Creating the Fact Table Manually

Related Topics

Overview

If you are accessing a relational or flat file data source, the first table that you drag to the right frame of the OLAP Model main window becomes the fact table. When you create the fact table, you also choose whether to create the time and accounts dimensions automatically.

Caution: If the data source stores accounts measures values and time values in the same columns, you must use a non-standard method to create the OLAP model accounts dimension. This situation is common in some applications, such as general ledger and legacy systems. See Switching the Time and Accounts Dimensions in the OLAP Model.

To create a fact table:

1 Drag the appropriate source table of the appropriate data source from the left frame to the right frame.

If you are not sure which data source or table to use, view the data stored in each table for each data source by selecting a table and then selecting View > View Table Data.

The fact table uses the name of the source table that you select to create it. To rename the fact table, see Renaming Dimensions and the Fact Table.

2 Decide whether to create the time dimension first and then the accounts dimension, by responding to the following prompts:

  • Would you like a Time dimension using a value from the fact table? Click Yes or No.

  • Would you like to add an Accounts dimension? Click Yes or No.

    • Creating the Time Dimension Automatically

    Creating the Accounts Dimension Automatically

    If the source table that you select to create the fact table contains at least one column with a time value (a shipping date, for example) and you want to use that column to create a time dimension, you can choose to create the time dimension automatically.

    If you select this option, the time dimension initially contains the same columns as the fact table. However, any columns that you later add to the time dimension are not added automatically to the fact table; likewise, any columns that you later add to the fact table are not added automatically to the time dimension.

    If the source table that you select to create the fact table contains at least one column with a measures value (units sold, for example) and you want to use that column to create an accounts dimension, you can choose to create the accounts dimension automatically.

    The accounts dimension inherits all the fact table columns. In an OLAP model, the fact table and the accounts dimension are always mirror images of each other. The accounts dimension reflects changes made in the fact table; likewise, the fact table reflects changes made in the accounts dimension.

    After you respond to the time dimension and accounts dimension prompts, the fact table is displayed in the right frame.

    If you create an accounts dimension automatically, an object is displayed in the right frame. The object is identified by the "Accounts" label and the icon, [image]. A join line connects the fact table and the accounts dimension.

    If you create a time dimension automatically, an object is displayed in the right frame. The object is identified by the "Time" label and the icon, [image]. A join line connects the fact table and the time dimension.

    3 After you create the fact table, proceed with the following options:

    • To add tables from the data source to the fact table, see Adding Tables to the Fact Table.

    • To add time and accounts dimensions manually, if you opted not to create time and accounts dimensions automatically along with the fact table, see Creating the Accounts Dimension Manually and Creating a Time Dimension Manually.

    • To create standard dimensions, see the topics listed in "Creating and Working with Dimensions."


Creating the Accounts Dimension Manually

Related Topics

Overview

If you need an accounts dimension and did not create it automatically when you created the fact table, you can create it manually.

To add an accounts dimension manually:

1 Right-click the fact table, and select Add Accounts Dimension.

An accounts dimension is displayed in the right frame. The dimension is identified by the "Accounts" label and the icon, [image]. A join line connects the accounts dimension to the fact table.

2 If needed, add one or more source tables to the accounts dimension.

You must add source tables directly to the accounts dimension; you cannot create a dimension branch.

3 If needed, add one or more user-defined columns to the accounts dimension.

Any columns that you add to the accounts dimension are also added to the fact table. For instructions on how to add new columns to the accounts dimension, see Creating User-Defined Columns.

Note: You cannot enable the accounts dimension or its members for Hybrid Analysis.


Creating a Time Dimension Manually

Overview

You can create a time dimension either automatically or manually. The procedure for automatically creating a time dimension is described in Creating the Fact Table Automatically. After automatically creating the time dimension, you can edit the columns it contains.

If you do not create a time dimension automatically when you create the fact table, you can add the time dimension manually. You create the time dimension manually in one of two ways:

  • Based on the fact table, whereby the time dimension table inherits all of the columns of the fact table. See Creating a Time Dimension Manually Based on the Fact Table.

  • Based on another table in an external data source, such as a user-defined calendar. Follow the procedure that you use for creating standard dimensions, with some exceptions that are noted in the procedure, Creating a Time Dimension Manually Based on the Fact Table. See Designing a User-Defined Calendar for information on


Creating a Time Dimension Based on the Fact Table Manually

Overview

To add a time dimension that is based on the fact table:

1 Right-click the fact table, and select Add Time Dimension.

A time dimension is displayed in the right frame. The dimension is identified by the "Time" label and the icon, [image]. A broken join line connects the time dimension to the fact table. The time dimension table includes all of the columns in the fact table. The broken join line indicates that you need to select time-related columns in both tables to define the join relationship between the two tables.

2 Define the join between the two tables, as described in Joining Dimension Tables Directly to the Fact Table.

3 If needed, add one or more source tables to the time dimension.

You must add source tables directly to the time dimension; you cannot create a dimension branch.

For more information, see Adding Source Tables Directly to Dimension Tables.

4 If needed, add one or more user-defined columns to the time dimension.

Add new columns to the time dimension to use the columns as levels in a time hierarchy. If you want monthly and quarterly aggregations in the Essbase database, you need a monthly and a quarterly column in the time dimension of the OLAP model.

If the time dimension does not include the needed columns, you must add new, user-defined columns. Add a column for each level of aggregation that the OLAP model does not currently include. You derive the values of the new columns from date columns currently existing in the data; for example, a transaction or sales date.

For instructions on how to add new columns to the time dimension table, see Creating User-Defined Columns.


Designing a User-Defined Calendar

Related Topics

Assume that a user-defined calendar is located in a table in the source database that you are using to create an OLAP model. This table requires both of the following:

  • A column for each potential time period (member) in the time dimension.

  • A time-related key column that maps to the lowest-level, time-related column associated with the measures data. See the Essbase Integration Services Data Preparation Guide.

The table below is an example of a user-defined calendar.

TIMESTAMP | YEAR | QTR | MONTH | WK_OF YEAR | DAY_OF YEAR | DAY_OF WEEK | SEASON
Jul 1 2001 12:00AM | 2001 | 1 | 1 | 1 | 1 | Thurs | Summer
Jul 2 2001 12:00AM | 2001 | 1 | 1 | 1 | 2 | Fri | Summer
Jul 5 2001 12:00AM | 2001 | 1 | 1 | 2 | 5 | Mon | Summer
Jul 6 2001 12:00AM | 2001 | 1 | 1 | 2 | 6 | Tues | Summer
Jul 7 2001 12:00AM | 2001 | 1 | 1 | 2 | 7 | Wed | Summer
Jul 8 2001 12:00AM | 2001 | 1 | 1 | 2 | 8 | Thurs | Summer
Jul 9 2001 12:00AM | 2001 | 1 | 1 | 2 | 9 | Fri | Summer
Sep 1 2001 12:00AM | 2001 | 1 | 3 | 10 | 63 | Wed | Autumn
Sep 2 2001 12:00AM | 2001 | 1 | 3 | 10 | 64 | Thurs | Autumn
Sep 3 2001 12:00AM | 2001 | 1 | 3 | 10 | 65 | Fri | Autumn
Sep 6 2001 12:00AM | 2001 | 1 | 3 | 11 | 66 | Sat | Autumn

The example table illustrates several points to consider when creating a user-defined calendar:

  • The first column, TIMESTAMP, is the key column in the user-defined calendar table because it defines the lowest level of time analysis that the source data can provide to the fact table. The TIMESTAMP column is joined to a time-related column of the fact table. As joined columns, the columns must have the same data type; in this example, both columns must be datetime.

  • In order for the measures data (such as sales) to map to the time members, each value in the date column of the source data for the fact table must match a value in the key column of the user-defined calendar table.

The TIMESTAMP column values include the time rounded in minutes to 12:00 AM; for example, Jul 2 2000 12:00AM. TIMESTAMP is the join column from the user-defined table to the fact table. Therefore, not only must the join column in the fact table have a datetime data type, but also each time value must be rounded to 12:00AM. For example, if a sale occurred on July 2, 2000, at 8:30 AM, you must prepare the transaction date in the source data so that the fact table and the user-defined calendar show the same value, Jul 2 2000 12:00AM.

  • Representing a fiscal calendar starting with July 1, the calendar in example table shows the July 1 timestamp value mapping to a 1 value for the quarter, month, week, and day of the year.

  • The example table shows only a few rows for July and September. Actually, a table must contain a row for every potential date in the source data. For example, the table above does not include rows for the dates that are Saturdays and Sundays. Unless you are certain that no data is posted with a Saturday or Sunday date, you should include rows for those days.

  • The example table includes two columns related to the day: the day of the year and the day of the week. If you want to analyze whether sales are better on Mondays than on Wednesdays, you need a column in the user-defined calendar and a corresponding member in the time dimension for the day of the week.

  • The example table includes a SEASON column. Although autumn traditionally starts in mid-September, the example shows autumn starting on September first.

Set the values in the calendar to correspond to the needs of your company. A user-defined calendar is as unique as the business requirements of your company.


Switching the Time and Accounts Dimensions in the OLAP Model

To switch time and accounts dimensions in the OLAP model:

1 Create and name the fact table using the procedures in Creating the Fact Table Automatically or Creating the Fact Table Manually, with the following exceptions:

  • To the prompt for creating the time dimension automatically, click No.

  • To the prompt for creating the accounts dimension automatically, click Yes.

  • Right-click the Accounts dimension table, and select Properties to display the Dimension Properties dialog box. In the dialog box, change the name of the Accounts dimension to Time, and click OK.

  • Right-click the newly created Time dimension table and select View Columns to display the column names.

  • Right-click the Accounts table in the column view, select Properties to display the Table Properties dialog box, change the name of the Accounts dimension to Time, and click Close.

2 In the OLAP Model main window, drag the source table that you used for the fact table to the right frame to create a new dimension.

3 When a dialog box prompts you to rename the table (because that name already exists), click Yes.

Essbase Integration Services Console displays the Create New Table dialog box

4 In the Name text box, type Accounts and click OK to close the dialog box.

5 In the OLAP Model main window, use the join tool to join the new dimension to the fact table.

Click the join tool (or right-click in a blank space of the right frame and select Add Joins Mode) to activate Add Joins mode, and drag the tool between the two tables.

Integration Services Console displays the Create New Dimension dialog box with the dimension name Accounts in the Name text box.

6 From the Dimension Type drop-down list, select Default, and click OK to close the dialog box.

7 Define the joins completely, using all necessary keys.

8 Create any other dimensions that you need (see Creating Dimensions Automatically or Creating Dimensions Manually.

9 Save the OLAP model.

Complete the OLAP model before using it to create a metaoutline.


Setting Dimension Properties in the Metaoutline

To set properties for the time and accounts dimensions in the metaoutline:

1 From the newly created OLAP model, create a metaoutline.

2 After you add the time dimension to the metaoutline, right-click the time dimension and select Properties.

Essbase Integration Services Console displays the General tab of the Dimension Properties dialog box.

  1. Select the Member Info tab.

  2. From the Dimension Type options, select Time.

  3. Complete information on the other tabs as needed, and click OK to close the dialog box.

3 As needed, right-click a member level name within the time dimension, select the Properties option, and complete information on the tabs.

4 Click OK to close the dialog box.

5 Right-click the accounts dimension and select Properties.

Integration Services Console displays the General tab of the Dimension Properties dialog box.

  1. Select the Member Info tab.

  2. From the Dimension type options, select Accounts.

  3. Complete information on the other tabs as needed, and click OK to close the dialog box.

6 As needed, right-click a member level name within the accounts dimension, select the Properties option, and specify on each tab the information required to define the selected member level.

7 Click OK to close the dialog box.

8 Continue with the workflow as described in the Understanding Metaoutline Workflow.


Creating Dimensions Automatically

Related Topics

Overview

You can create standard dimensions automatically using Essbase Integration Services Console.

Note: If you are accessing a flat file data source, do not use this functionality.

To create standard dimensions automatically:

1 Select Tools > Create Dimensions or click the Create Dimensions toolbar button, [image] , to initiate the dimension creation process.

Note: You can also access this function by using the "autodetect" link in the Intelligent Help (Dimension process button, Create Standard Dimensions Automatically task).

A message box is displayed listing the names of the standard dimensions that will be created.

2 Click OK to close the message box.

The standard dimension creation process is complete.

You can also click Cancel to stop the dimension creation process.


Creating Dimensions Manually

This topic describes how to create a dimension that joins directly with the fact table. To create a fact table, see Creating the Fact Table Automatically or Creating the Fact Table Manually.

Tip: You can create more than one dimension from a single data source table. Each dimension must have a unique name. You should hide all columns that do not contain values that you want to be reported for the dimension (see Hiding or Showing Columns ).

To create dimensions:

1 Drag the appropriate source table from the left frame to the right frame.

If you are not sure which table to use, view the data stored in the table by selecting a table, right-clicking, and then selecting View Table Data.

  • If a predefined primary-foreign key relationship exists between the table that you are adding and the fact table, Essbase Integration Services joins the dimension table to the fact table. Because the dimension table joins directly to the fact table, the dimension table is also a dimension.

  • If a predefined primary-foreign key relationship does not exist between the table that you are adding and the fact table, Integration Services does not join the dimension table to the fact table. To create the dimension, join the dimension table to the fact table manually. See Joining Dimension Tables Directly to the Fact Table

Integration Services Console displays the Create New Dimension dialog box. By default, the new dimension inherits the name of the source table that you select to create it.

Note: When you drag a source table from the left frame to the right frame, and that source table already exists in the OLAP model, the console displays the Create New Table dialog box where you provide a new table name.

2 If you want to change the dimension name, in the Name text box, enter a new name.

3 From the Dimension Type drop-down list, select Default.

4 Click OK.


Adding Source Tables to Create Dimension Branches

Overview

To add a table and its columns to form a dimension branch:

1 Drag the source table from the left frame to the right frame.

2 Join the table to the dimension table as described in Joining Dimension Tables to Create or Expand a Dimension Branch.


Adding Source Tables Directly to Dimension Tables

Overview

To add a source table to a dimension table:

1 Drag the source table from the left frame to the right frame, position it directly over the dimension table to which you want to add it, and release the mouse button.

A dialog box prompts you to make sure that you want to add the columns to the existing dimension table.

2 To add all columns of the new dimension table to the existing dimension table, click Yes.

Essbase Integration Services Console displays the Physical Joins tab of the Table Properties dialog box.

The left Table drop-down list contains the name of the table that you just added. The right Table drop-down list contains the names of all tables currently included within the dimension table, including the name of the newly added table. The left and right Column lists contain the names of the columns associated with the two selected tables. If a predefined primary-foreign key relationship exists between the tables, the join columns are displayed under Column 1 and Column 2 at the bottom of the dialog box.

3 If the column joins listed in the join information box are correct and no joins are missing, proceed to step 5.

4 If you need to join additional columns or if the column joins listed in the join information are not correct, complete the following steps:

  1. In the right Table list, select the table with which the newly added table has a relationship.

  2. In the left and right Column lists, select from each table the columns that you want to join; for example, FAMILYID.

Tip: If you double-click a column in one list, the column with the same name is selected in the other list.

  1. To join the selected columns, click Add.

The columns that you select are displayed in the box at the bottom of the dialog box.

  2. As needed, repeat steps a through c to join additional columns from the selected table.

5 When you finish specifying join information for the selected table, click OK to return to the right frame of the OLAP Model main window.

If you select the original dimension table to which you have added the new dimension table and then right-click and select View Columns, all of the columns that the new table contains are displayed in the original dimension table.

Now is a good time to hide or delete unwanted columns that the dimension table received from the newly added table, and to mark columns as Drill-Through .

Notes: Deleted columns are permanently removed from the OLAP model. If you later decide to include the deleted columns, you must re-create the OLAP model.


Adding Tables to the Fact Table

Overview

To add tables to the fact table:

1 Drag the source table from the left frame to the right frame, position it directly over the fact table, and release the mouse button.

A dialog box prompts you to make sure that you want to add the columns to the fact table.

2 To add the new table to the fact table, click Yes.

Essbase Integration Services Console displays the Physical Joins tab in the Table Properties dialog box.

3 Join the new table to the fact table as described in Joining Dimension Tables Directly to the Fact Table.

Now is a good time to hide or delete unwanted columns that the fact table received from the newly added table. See Hiding and Showing Columns and Deleting Columns.

Note: Deleted columns are permanently removed from the OLAP model. If you later decide to include the deleted columns, you must re-create the OLAP model.


Viewing Dimension Table and Fact Table Information

The right frame of the OLAP Model main window contains the fact table and the dimension tables that the OLAP model contains. This topic describes how to view information about the fact table and related dimension tables.

To view properties of a dimension table or of the fact table:

1 Select the dimension table or the fact table, and then select Edit > Properties > Table.

Essbase Integration Services Console displays the General tab of the Table Properties dialog box. The General tab describes the dimension table—its name, its data source, its dimension, its level, and the names of its source tables. The fact table is level 0. Dimension tables joined to the fact table are level 1. In a dimension branch, dimension tables joined to level 1 dimension tables are level 2; dimension tables joined to level 2 tables are level 3, and so on.

2 If you are viewing the table properties for the fact table, to view the dimensions defined in the OLAP model, select the Dimensions tab.


Renaming Dimensions and the Fact Table

When you create an OLAP model fact table or a dimension, the source table name is used by default. This topic describes how to change the name of a dimension or the name of the fact table. For information on how to change a dimension table name, see Renaming Dimension Tables.

To rename a dimension or to rename the fact table:

1 In the right frame of the OLAP Model main window, select the dimension or the fact table, right-click, and select Rename.

2 Type the new name for the dimension or the fact table; for example, REGION.

Follow the rules described in Naming Conventions

3 Press Enter to record the new name.

Essbase Integration Services Console displays the new name in the right frame.


Renaming Dimension Tables

When you create a dimension table, the source table name becomes the dimension table name by default. This topic describes how to change the name of a dimension table. For information on how to change the name of a dimension or of the fact table, see Renaming Dimensions and the Fact Table.

To rename a dimension table:

1 In the right frame of the OLAP Model main window, select the dimension table name.

If the dimension table is not visible, drag the corner of the dimension box down until the dimension table is visible.

2 Click the dimension table name.

3 Type the new name.

4 Press Enter to record the new name.

Essbase Integration Services Console displays the new dimension table name.


Moving Tables

You can rearrange dimension tables and the fact table in the right frame of the OLAP Model main window. For example, you can move a table to allow room to display all columns of the table in the right frame. You can also move tables to display the tables more clearly in the right frame of the OLAP Model main window.

To move a table:

1 Make sure that the join tool is in Move mode, not in Add Joins mode.

In Move mode, the join tool is not indented, [image].

In Add Joins mode, the join tool is indented, [image].

If the join tool is in Add Joins mode, click the join tool to change it to Move mode.

2 Select the dimension table and drag it to the new location.

If the dimension table that you move is joined to another dimension table, the join line also moves.


Deleting Dimension Tables or the Fact Table

Remember these rules when deleting dimension tables or the fact table:

  • If you delete a table, its columns are not available for metaoutlines.

  • Deleting a table in the OLAP model does not affect the tables of the relational data source.

  • When you delete a table, you also delete the joins associated with it.

  • If you delete a table that is the primary dimension table of a dimension branch, the other dimension tables of the branch remain in the OLAP model as unjoined dimension tables. When you validate or save an OLAP model with unjoined tables, you receive an error message and you cannot use the model to create a metaoutline.

  • You cannot delete tables that are used in hierarchies or metaoutlines.

  • If you delete the fact table, the accounts dimension is deleted.

  • If you delete the fact table and you created the time dimension from the fact table, the time dimension is deleted.

  • If you delete the fact table, the next source table that you drag to the right frame is used to create a new fact table.

To delete a dimension table or the fact table:

1 In the right frame of the OLAP Model main window, right-click the dimension table or the fact table, and select Delete.

Essbase Integration Services Console displays a delete confirmation dialog box.

2 Click Yes to confirm the deletion.

The dimension table or fact table is removed from the OLAP model.


Viewing Column Properties

In the OLAP Model main window, you can view the name and properties of any column at any time.

To display the names and properties of columns in a dimension table or in the fact table:

1 To display a scrollable list of column names, as shown below, right-click the dimension and select View Columns.

See Icons in Essbase Integration Services Console for a complete list of column properties icons.

2 If you want to turn off the display of all graphics next to columns for all dimensions, be sure that no dimensions or dimension tables are selected, and then deselect View > Model Icons > All.

You can also choose to turn off the display of individual column graphics; for example, to turn off the display of all primary key graphics in the OLAP model, deselect View > Model Icons > Primary Key.

Note: The graphics display choices you make apply to all dimensions and columns in the OLAP model.

3 To close the column list, right-click and select View Columns.


Renaming Columns

You can rename any column in a dimension table or in the fact table. Renaming a column in the OLAP model does not affect the data source.

To rename a column:

1 In the right frame of the OLAP Model main window, select the column.

If the column is not visible, drag down the corner of the dimension table containing the column until the column is visible.

2 Select the column again so that the column name is highlighted, as shown below:

3 Type the new column name.

Follow the rules described in Naming Conventions.

4 Press Enter to record the new name.

Essbase Integration Services Console displays the new name in the selected table.


Hiding or Showing Columns

Typically, data source tables contain some columns that you do not want to include in an Essbase database. Hiding a column removes it from an OLAP model in the sense that the column is not available to a metaoutline as a member level, a measure, an alias, or a drill-through column.

You can join dimension tables on hidden columns. You can also show a previously hidden column if you change your mind and want to make the column available to a metaoutline. Hiding columns does not affect the data source.

Note: In an OLAP model, you cannot hide a column that is used in an associated metaoutline.

To hide or show a column:

1 Choose one of the following options:

  • Select the fact table or dimension table, then select Edit > Properties > Table.

  • Drag the corner of a dimension table until its columns are visible, right-click the table name within the column view, and select Properties.

Essbase Integration Services Console displays the General tab of the Table Properties dialog box.

2 Select the Columns tab.

The Columns tab displays all columns available in the OLAP model. For each hidden column, the Hidden field contains a Y (Yes).

3 Choose one of the following settings:

  • To hide one or more available columns (columns without a Y in the Hidden field), select each appropriate column, and click Hide.

  • To show one or more hidden columns (columns with a Y in the Hidden field), select each appropriate column, and click Hide to reverse the setting.

To select multiple columns, see Working with Multiple Columns .

If you select a combination of columns (some with and some without the Y in the Hidden field) and then click Hide, all selected columns are hidden.

If you select a column with a Y in the Drill-Through field, the Y is removed from the Drill-Through field and placed in the Hidden field because columns cannot be both hidden and drill-through.


Working with Multiple Columns

You can apply a procedure to an individual column or to multiple columns. If you want to apply a procedure to multiple columns, use the Table Properties dialog box to select the members.

To select multiple, contiguous columns in the Table Properties dialog box:

1 Click the name of the first column.

2 Shift-click the name of the last column.

The first, last, and all columns in between are selected.

To select multiple, noncontiguous columns in the Table Properties dialog box:

1 Click the name of the first column.

2 Hold down Ctrl and click the individual names of the other columns.


Deleting Columns

You can delete unwanted columns from dimension tables and from the fact table. Deleting columns is different from hiding columns; deleted columns are permanently removed from the OLAP model. If you later decide to include the deleted columns, you must re-create the OLAP model.

Deleting columns from an OLAP model does not affect the data source.

Note: You cannot delete columns that are currently used in joins, hierarchies, or associated metaoutlines.

To delete a column from a dimension table or from the fact table:

1 Select the dimension table or the fact table, and then select Edit > Properties > Table.

Essbase Integration Services Console displays the Table Properties dialog box.

Tip: You can also delete a column by selecting it in the OLAP Model main window, right-clicking, and selecting Delete.

2 Select the Columns tab.

3 Select the column to delete.

To select multiple columns, see Working with Multiple Columns.

4 Click Delete.

The console displays a delete confirmation dialog box.

Note: Use caution when deleting. You cannot undo the deletion of a column.

5 Click Yes to confirm the deletion.

The selected column name is removed from the list of names in the Columns tab.


Allowing Null Values for Columns when Relational Storage is Enabled

Related Topics

Overview

Example

To apply the Allow Null Value property to those columns in the OLAP model that possibly contain NULL values:

1 In the right frame of the OLAP Model main window, select the appropriate column.

If the column is not visible, drag down the corner of the dimension table containing the column until the column is visible.

2 Right-click the column name and select Properties.

The General tab of the Column Properties dialog box is displayed.

3 Select the Allow Null Value check box and click OK.

4 Repeat steps 1 through 3 for each column to which you want to apply the Allow Null Value property.

Note: The Allow Null Value property should only be used when you plan to enable relational storage (Advanced Relational Access or Hybrid Analysis) in the corresponding metaoutline.


Using a Column to Create Time Hierarchies

You can create predefined time hierarchies automatically from a single column.

For example, you can select a column containing a timestamp, such as Dec 29 2001 0733AM and use the values of the timestamp to create a hierarchy that includes the year (2001), quarter (Qtr_4) and month (December).

To transform a date column into a hierarchy:

1 Select the dimension table that contains the column that you want to use to create a hierarchy and then select Edit > Properties > Table to display the Table Properties dialog box.

2 In the Table Properties dialog box, select the Columns tab.

Essbase Integration Services Console displays the Columns tab of the Table Properties dialog box.

3 Select the appropriate date or time column, and click Date-Hierarchy.

The console displays the Date Hierarchy dialog box.

4 Select the hierarchy to create from the selected column; for example, Year, Quarter, Month.

If you attempt to create a hierarchy that includes a column that already exists within the OLAP model, the console displays an error message. You cannot create a hierarchy with a duplicate column name, whether the duplicate column is contained in the source data or in an OLAP model fact table or in a dimension table (including user-defined columns).

5 To return to the OLAP Model main window, click OK in the Date Hierarchy dialog box, and then click Close in the Table Properties dialog box.


Defining Drill-Through Columns

Overview

To change the drill-through property of a column:

1 Choose one of the following methods to display the General tab of the Table Properties dialog box:

  • Select the dimension table, and select Edit > Properties > Table.

  • Drag the corner of a dimension table until its columns are visible, right-click the table name within the column view, and select Properties.

2 Select the Columns tab.

The Columns tab displays all columns of the OLAP model. Each drill-through column displays a Y (Yes) in the Drill-Through field. All numeric columns in the accounts dimension table and the fact table show NA under Drill-Through. An NA indicates that a column is not available as a drill-through column.

3 Select one or more columns for which you want to add or remove the drill-through property.

To select multiple columns, see Working with Multiple Columns.

4 Click Drill-Through.

  • If you select only columns that do not have a Y (Yes) in the Drill-Through field, each selected column receives a Y.

  • If you select only columns that have a Y in the Drill-Through field, the Y of each selected column is removed.

  • If you select a combination of columns that do and do not have a Y in the Drill-Through field, each selected column receives or retains a Y (as determined by its previous status).

  • If you select a column that has a Y in the Hidden field, the Y is removed from the Hidden field and placed in the Drill-Through field because columns cannot be both hidden and drill-through.


Creating User-Defined Columns

Related Topics

Overview

To add a user-defined column to a dimension table:

1 Select the table that contains the source column on which you want to base a new column.

2 Choose one of the following methods to display the General tab of the Table Properties dialog box:

  • Select the fact table or dimension table and then select Edit > Properties > Table.

  • Drag the corner of a dimension table until its columns are visible, right-click the table name within the column view, and select Properties.

3 In the Table Properties dialog box, select the Columns tab.

The console displays the Columns tab of the Table Properties dialog box.

4 Click Add.

The console displays the Add New Column to Table dialog box.

5 Enter the name of the user-defined column; for example, California.

Follow the rules described in Naming Conventions .

6 From the Physical Table list, select the source table that contains the column on which you are basing the user-defined column; for example, TBC.REGION.

7 From the Physical Column list, select the source column that contains the data values from which you want to derive the values of the new column; for example, REGIONID.

8 Click OK.

The console displays the General tab of the Column Properties dialog box.

The General tab displays the name that you just entered, the names of the source (physical) table and column, and the data type of the column. If the user-defined column is in the accounts dimension and the column has a numeric data type, the dialog box will also contain an Aggregation drop-down list. Aggregation options are methods for consolidating column data.

9 If the user-defined column belongs to the accounts dimension table, from the Aggregation drop-down list, select an aggregation option.

  • <NONE>: prevents a column from being available as measures to the metaoutline. Typically, <NONE> is the aggregation option specified for a join column.

  • SUM: adds the data for each reporting level. SUM is the default selection.

  • AVG, MIN, or MAX: reports, respectively, the average, minimum, or maximum value computed from the data in the column.

Hybrid Analysis: With a Hybrid Analysis-enabled metaoutline, you should consider the following when working with the column functions AVG, MIN, or MAX.

As an example, if the column function MIN is associated with a user-defined member Min_Cogs belonging to the Measures dimension in the metaoutline, the following results can be expected:

  • The column function MIN will be used when loading data into Min_Cogs cells in an Essbase database.

  • The column function is not preserved when loading members into an Essbase database because the member Min_Cogs in the Essbase outline does not have a MIN function associated with it. Calculation of all aggregate values for Min_Cogs cells in Essbase only use relationships defined by hierarchies, if any, or a formula attached to Min_Cogs in the outline.

  • When drilling back into Hybrid Analysis, for all retrievals of cells associated with Min_Cogs, the column function MIN is used when generating SQL.

  • COUNT: reports, for each reporting level, a count of the source data cells that are not NULL.

10 To define the transformation rule for the user-defined column, select the Transformation Rule tab.

Essbase Integration Services Console displays the Transformation Rule tab appropriate to the data type of the column with which you are working. For more information about transformations for each data type, see one or more of the following subtopics:

  • Defining Transformations that Use Substrings to Create String Columns

  • Defining Transformations that Use Concatenations to Create String Columns

  • Defining Transformations for Numeric Columns

  • Defining Transformations for Datetime Columns

  • Defining Pass-Through Transformations


Defining Transformations that Use Substrings to Create String Columns

Overview

To define a transformation rule that uses a substring to create a string column:

1 Select the appropriate dimension table and then select Edit > Properties > Table.

2 In the Table Properties dialog box, select the Columns tab.

3 Double-click the column that you want to transform to display the Column Properties dialog box.

4 In the Column Properties dialog box, select the Transformation Rule tab.

The Function list displays the options that you can use to derive the new column from the source data.

For numeric and string columns in the time dimension, the dialog box contains a Date-part drop-down list. Use the options in the Date-part list to use a column to define a Dynamic Time Series member and to report to-date totals, such as week-to-date totals, in the Essbase database.

5 If the column is a time-related column of the time dimension, in the Date-part drop-down list, select the part of the date that you want the column to represent.

For example, if you want the substring to contain the year, select Years.

6 For string columns, in the Function list, select Substring.

Two text boxes, Start and Length, are displayed to the right of the Function list.

7 In the Start text box, enter a number that identifies the position in the source column at which you want to start the substring.

For example, to start at the first position of the product code string, type 1.

8 In the Length text box, enter a number that identifies how many characters from the source column are needed in the new column.

For example, to use the first six positions of the product code, type 6.

9 Click OK.


Defining Transformations that Use Concatenations to Create String Columns

Overview

To define a transformation rule that uses a concatenation to create a string column:

1 Select the appropriate dimension table and then select Edit > Properties > Table.

2 In the Table Properties dialog box, select the Columns tab.

3 Double-click the column that you want to transform to display the Column Properties dialog box.

4 In the Column Properties dialog box, select the Transformation Rule tab.

The Function list displays the options that you can use to derive the new column from the source data.

For numeric and string columns in the time dimension, the dialog box contains a Date-part drop-down list. Use the options in the Date-part list to use the column to define a Dynamic Time Series member in the metaoutline and thus to report to-date totals, such as week-to-date totals, from the Essbase database.

5 If the column is a time-related column of the time dimension, in the Date-part drop-down list, select the part of the date that you want the column to represent.

For example, if you want the column to contain the year, select Years.

6 In the Function list, select Concatenate.

Two list boxes, Column Names and Concatenate Columns, are displayed to the right of the Function list box.

Using the sample data as an example, the Column Names list may display the name of the source table (TBC.PRODUCT) as a column name prefix and the names of the columns that you can include in the concatenated column (PKGTYPE and SKU). The Concatenate Columns list initially displays the source column you selected to transform (in this case, CAFFEINATED).

7 In the Column Names list, select the name of the next column to include in the concatenated column (TBC.PRODUCT.PKGTYPE in this example), and click the [image] button.

The new column is placed at the end of the Concatenate Columns list.

Tip: To move more than one column at a time, select multiple columns. As the columns move to the Concatenate Columns list, they retain the same sequence of the Column Names list.

8 To include additional columns, repeat step 7 for each column.

9 As necessary, complete and repeat one or both of the following actions:

  • To remove a column from the Concatenate Columns list, select the column and click the [image] button. The selected column returns to the Column Names list.

  • To change the sequence of the column names in the Concatenate Columns list, select a column name and then click the [image] button or the [image] button until the column is in the desired location.

10 When the Concatenate Columns list displays all columns to be concatenated and they are sequenced in the correct order, click OK.


Defining Transformations for Numeric Columns

Overview

To define a transformation rule for a numeric column (a column with a numeric data type):

1 Select the appropriate dimension table and then select Edit > Properties > Table.

2 In the Table Properties dialog box, select the Columns tab.

3 Double-click the column that you want to transform to display the Column Properties dialog box.

4 In the Column Properties dialog box, click the Transformation Rule tab.

The Operator list displays the types of operations available. The Column drop-down list displays the names of all other numeric columns in the dimension table. You can use any one of the numeric columns as the second operand in the calculation.

For numeric and string columns in the time dimension, the dialog box contains a Date-part drop-down list. Use the options in the Date-part list to use a column to define a Dynamic Time Series member in a metaoutline and thus to report to-date totals, such as week-to-date totals, in the Essbase database.

5 If the column is a time-related column of the time dimension, in the Date-part drop-down list, select the part of the date that you want the column to represent.

For example, if you want the column to contain the year, select Years.

6 In the Operator list, select an operator; for example, minus (-). By default, the source column is the first operand in the calculation.

Note: In the list of available operators, the % operator is the modulus function. The % operator returns the remainder of a division calculation; the % operator does not represent percentage.

7 In the Column drop-down list, select a column to be the other operand; for example, TBC.SALESFACT.COGS.

The calculation is displayed in the box below the Operator box; for example:

TBC.SALESFACT.SALES - TBC.SALESFACT.COGS

8 As necessary, complete either or both of the following actions:

  • To change the operator, from the Operator list, select a different operator. The calculation reflects the change.

  • To reverse the sequence of the operands, select Invert Parameters. The operands switch their positions; for example,

TBC.SALESFACT.SALES - TBC.SALESFACT.COGS

changes to

TBC.SALESFACT.COGS - TBC.SALESFACT.SALES

9 When the calculation is correct, click OK.


Defining Transformations for Datetime Columns

Overview

To define a transformation rule for a datetime column:

1 Select the appropriate dimension table and then select Edit > Properties > Table.

2 In the Table Properties dialog box, select the Columns tab.

3 Double-click the column that you want to transform to display the Column Properties dialog box.

4 In the Column Properties dialog box, select the Transformation Rule tab.

5 In the Category list, select the time category for the column.

Select the part of the date that you want the new column to contain. For example, if you select the Quarters category, Essbase Integration Server inserts the quarter of the year into the new column. If the value in the source column is 04/21/2000, which is in the second quarter, Integration Server inserts in the new column the value 2.

To copy the full date into the column, select <None>.

6 In the Function list, select how to represent the date.

For example, if you select Months from the Category list, the Function list displays three possible formats: MM, MMM, and MONTH. If the date value in the source column is April 15, 2000, and you select MM, Integration Server inserts in the new column the value 04. Select MMM for a three-character abbreviation; for example, APR. Select MONTH for a full name; for example, April. The text box at the bottom of the Transformation Rule tab describes the selected function.

Note: If the data source is a flat file, the default Date Hierarchies provided by Integration Services do not function with month names. If you want to use the Date Hierarchy functionality, define months as numerical values.

7 Click OK.


Defining Pass-Through Transformations

To define a pass-through transformation:

1 Select the appropriate dimension table and then select Edit > Properties > Table.

2 In the Table Properties dialog box, select the Columns tab.

3 Double-click the column that you want to transform to display the Column Properties dialog box.

4 In the Column Properties dialog box, select the Transformation Rule tab.

5 Click Pass-through (Database Specific).

The Transformation Rule tab changes to display a text box for specifying the pass-through transformation.

6 In the text box, enter the command or the information needed to perform the pass-through function.

You can enter anything that you can validly insert in the SELECT clause of an SQL statement in the source RDBMS. Make sure that the text that you enter is not enclosed in quotation marks.

Note: For column names, always enter the user ID, the dimension table name, and the column name. Place periods between the elements; for example, TBC.SALESFACT.SALES.

In the statement that you enter, do not type the word SELECT; enter only the text that you want to add to the SELECT clause.

The performance of the pass-through function produces the value of the new column. Here are two examples of using the pass-through feature:

  • As shown in below, to derive the variance of the SALES column, type

VARIANCE(TBC.SALESFACT.SALES)

  • As shown below, to derive the month portion of a date that is stored in a numeric column (if the source RDBMS supports the % operator as a modulus operator), type

(TBC.TIME.TRANSDATE%10000/100)

Note: For some RDBMSs, use the MOD or MODULUS operators instead of the % operator.

7 Click OK.


Editing Column Transformation Rules

Column transformation rules can be changed. The rules that are available for use vary, depending upon the data type of the selected column. For more information on data types, see About Column Data Types.

To change the transformation rule for a column:

1 Select the appropriate dimension table, right-click, and select View Columns.

2 Select the column to edit, right-click, and select Properties.

In the Column Properties dialog box, select the Transformation Rule tab and make the appropriate changes.

For more information, see one of the following topics:

  • Defining Transformations that Use Substrings to Create String Columns

  • Defining Transformations that Use Concatenations to Create String Columns

  • Defining Transformations for Numeric Columns

  • Defining Transformations for Datetime Columns

  • Defining Pass-Through Transformations


Selecting and Deselecting Columns as Attribute-Enabled

When selecting or deselecting columns to be attribute-enabled, keep in mind that you cannot deselect a column as attribute-enabled if such action would invalidate an existing attribute dimension that includes the selected column.

Note: When selecting and deselecting columns as attribute-enabled, relational fields used to create Level 0 members of attribute dimensions must never contain NULL values.

To select or deselect a column as attribute-enabled:

1 In the right frame of the OLAP Model main window, right-click the table containing the column that you want to use, and select Properties from the pop-up menu.

If you are not sure which table a column is in, select View > View All Columns.

Essbase Integration Services Console displays the General tab of the Table Properties dialog box.

2 Select the Columns tab.

Integration Services Console displays the Columns tab of the Table Properties dialog box.

If a column has previously been attribute-enabled, the Attribute field contains a Y (Yes).

3 Select the name of the column that you want to be attribute-enabled or the column where you want to remove the attribute-enabled status.

4 Click Attribute.

If you select the column as attribute-enabled, the Attribute field contains a Y (Yes).

If you deselect the column as attribute-enabled, the Attribute field no longer contains a Y (Yes).


Joining Dimension Tables Directly to the Fact Table

When you join a dimension table to the fact table, the joined table is called the primary dimension table. The primary dimension table becomes a dimension to form a dimension branch. In a dimension branch, only the primary dimension joins directly to the fact table.

To join a dimension table to the fact table:

1 Click the join tool, [image], (in the upper right of the OLAP Model main window) to enter Add Joins mode, and draw a line between the dimension table and the fact table.

Tip: Another way to switch to Add Joins mode is to right-click in a blank space of the right frame and select Add Joins Mode.

Essbase Integration Services Console displays the Create New Dimension dialog box.

Note: If the predefined primary-foreign relationship exists between the fact table and the table that you drag to the right frame, Integration Services creates a join automatically and displays the Create New Dimension dialog box.

2 Specify the dimension name and dimension type and click OK.

For more information, see Creating Dimensions Manually and Creating Dimensions Automatically .

Integration Services Console displays the Edit Join Info dialog box.

Above the left list box is the name of the fact table. Above the right list box is the name of the table that you joined to the fact table.

The list boxes contain the columns of their respective tables. If a predefined primary-foreign key relationship exists between the tables that you are joining, the Current Join Information list box contains one entry for each column join between the two tables.

For information about deleting joins, see Deleting Joins Between Dimension Tables or Deleting Joins Within Dimension Tables .

3 From each table list, select a join column, and click Add.

The columns that you select are displayed in the Current Join Information box.

Tip: Double-click a column in one table to select its corresponding column in the other table.

4 For each column join that you want to add to the join definition, repeat Step 3.

5 When the Current Join Information box lists all required column joins, click Close.

A solid line connects the two dimensions.

Note: If you plan to create a recursive hierarchy from a newly-created dimension, the table that you use to create the dimension must have a self-join defined (see Defining a Recursive Table).

If you close the dialog box without defining a column join, the join line displays as a broken line. The OLAP model is invalid. You can save the OLAP model, but you cannot use it to create a metaoutline until all joins are defined. If you see a broken line, create a valid join. See Viewing Joins Information, Joining Dimension Tables Directly to the Fact Table, or Joining Dimension Tables to Create or Expand a Dimension Branch.


Joining Dimension Tables to Create or Expand a Dimension Branch

You can join a dimension table to a primary dimension table, thus forming a dimension branch (see About Dimension Branches). You can also join a dimension table to any table of a dimension branch, thus expanding the branch.

To join dimension tables to create or expand a dimension branch:

1 Click the join tool, [image], (in the upper right of the OLAP Model main window) to enter Add Joins mode, and drag the tool between the two tables.

Tip: Another way to switch to Add Joins mode is to right-click in a blank space of the right frame and select Add Joins Mode.

Essbase Integration Services Console displays the Edit Join Info dialog box for the join.

At the top of the dialog box are the names of the original dimension table and the table that you are joining to the original table. The list boxes contain the columns of their respective tables. If a predefined primary-foreign key relationship exists between the tables that you are joining, the Current Join Information list box contains one entry for each column join.

2 From each table list, select a column to join, and click Add.

Tip: Double-click a column in one table to select its corresponding column in the other table.

The columns that you select are displayed in the Current Join Information list box.

3 For each column join that you want to add to the join definition, repeat step 2.

You can create column joins in any sequence.

4 When the Current Join Information list displays all required column joins, click Close.

In the right frame of the OLAP Model main window, a solid join line connects the two joined tables.

Note: If you plan to create a recursive hierarchy from a newly-created dimension, the table that you use to create the dimension must have a self-join defined (see Defining a Recursive Table).

If you close the dialog box without defining a column join, the join line displays as a broken line. An OLAP model that contains unjoined tables is invalid. You can save the OLAP model, but you cannot use it to create a metaoutline until all joins are defined. If you see a broken line, create a valid join. See Viewing Joins Information.


Combining Table Columns to Join Dimension Tables

You can add a source table to a dimension by dragging the source table directly onto an existing dimension table. When you use this process, the columns from the second table are combined with the columns of the existing dimension table. When you drop a dimension table directly onto the fact table or onto another dimension table, you must join the two tables. For more information, see Adding Source Tables to Create Dimension Branches.

To add and join a dimension table to another dimension table or to the fact table:

1 In the left frame of the OLAP Model main window, select a source table.

2 Drag the selected source table to the right frame, and drop it on the dimension table to which you want to join it.

When you add a source table to an existing dimension table or to the fact table, Integration Services Console displays the Physical Joins tab of the Table Properties dialog box.

Two drop-down lists at the top of the dialog box contain the names of the joined tables. The Column lists display the names of the columns of their respective tables. The join information box below lists the column joins.

3 If you want to find and correct columns that are incorrectly joined and shown at the bottom of the dialog box or if you want to join columns that are not already joined, complete steps a. and b., as appropriate; otherwise, proceed to step 4.

  1. If the tables to be joined are not already displayed, from the two drop-down Table lists, select the appropriate tables.

  2. From each Column list, select a column to join, and click Add.

The columns that you join are displayed in the join information box at the bottom of the dialog box.

Tip: Double-click a column in one table to select its corresponding column in the other table.

If a primary-foreign key relationship exists between one or more columns of the selected tables, the join information box contains one entry for each column join.

4 Repeat Step 3 for each column join at the bottom of the dialog box that you want to add to the join definition.

You can create column joins in any sequence.

5 Click OK.


Viewing Join Information

To view join information for joins between tables, right-click the join line between two dimension tables or between the fact table and a dimension table. Essbase Integration Services Console displays the Edit Join Information dialog box. (A different dialog box is displayed if you click the dimension table rather than the join line.)

When the Edit Join Information dialog box opens, you can review the following join property information:

  • Names of joined tables

  • Names of columns within the joined tables

  • Names of joined columns

If you want to add column joins between tables, follow the same procedure as described in Step 2 of Joining Dimension Tables to Create or Expand a Dimension Branch

If you want to delete column joins, see Deleting Joins Between Dimension Tables and Deleting Joins Within Dimension Tables.


Deleting Joins Between Dimension Tables

Deleting a join between two dimension tables or between a dimension table and the fact table results in an unjoined dimension table or dimension branch. An unjoined dimension table or dimension branch makes an OLAP model invalid. You cannot create a metaoutline from an OLAP model that contains a dimension table that is not joined either directly to the fact table or to a dimension table that joins, in series, to the fact table.

To delete a join between two dimension tables or between a dimension table and the fact table:

1 Right-click the join line and select Delete.

Essbase Integration Services Console displays a delete confirmation dialog box.

To delete a join between two dimension tables or between a dimension table and the fact table:

2 Click Yes to confirm the deletion.

The join line disappears.

Tip: You can also delete joins by double-clicking the join line between dimension tables and selecting joins to delete in the Edit Join Information dialog box.


Deleting Joins Within Dimension Tables

To delete joins within a dimension table:

1 Select the appropriate dimension, right-click, and select View Columns.

2 In the column view, double-click the dimension table name.

Essbase Integration Services Console displays the General tab of the Table Properties dialog box.

3 Select the Physical Joins tab.

4 Click the column join that you want to delete; for example, SKU, and click Delete.

In the OLAP model, the column join is removed from the table.

Notes:

  • If you delete all column joins, an OLAP model becomes invalid. To delete a table from within a dimension table, delete all the columns from the dimension table.


Defining a Recursive Table

A recursive table describes the hierarchical relationship of an Essbase dimension in just two columns. One column functions as a parent column and the other column functions as a child column. Information in one row relates to information in another row.

For example, assume that a payroll database includes an EMPLOYEES table that contains a child column, EMPLOYEE_ID, and a parent column, REPORTS_TO_ID. As shown in the table below, the data value (100101) is included in two rows, one row in the child column and another row in the parent column.

To define a recursive table, you need to join the table to itself. In databases, this process is called a self-join. See Joining a Table to Itself.

For more information about recursive tables, see the Essbase Integration Services Data Preparation Guide.


Preparing Recursive Tables to Be Used with Aliases and User-Defined Attributes

A recursive table can contain additional information that relates to the parent column or child column values. In the table below, the additional information provided in the FULL_NAME column and the TITLE column relates to the employees named in the child column, EMPLOYEE_ID.

Essbase Integration Server can use the values of the columns that provide the additional information such as aliases or as user-defined attributes (UDAs). A UDA is a word or phrase that describes a member level.

In a metaoutline, you can assign an alias column to a parent or child column. Integration Server then substitutes the value of the alias column for the value of the parent or child column. Aliases improve the readability of Essbase outlines or reports. For example, if you use the product description column (PRODUCT_DESC) as an alias for the product code column (PRODUCT_ID), users see the description, Root Beer, instead of the product code, 200-10, on their reports.

In a metaoutline, you can create a UDA for a member level. For example, you might create a UDA called Under Forecast for regions whose sales are below the forecast.

Whether you can define aliases or create UDAs for columns in a recursive table depends on the nature of the data and on how you define the joins for the recursive table. To use aliases and UDAs, you must define joins as follows:

  • The information of an alias or a UDA column must be related either to the value in the parent column or to the value in the child column. (You cannot apply an alias or a UDA column to both the parent column and the child column.)

  • In most cases, the data in the column for which you are defining the alias or UDA must be fully defined. For example, in the figure above, the child column is fully defined and the parent column is not.

For more information about fully defined columns in recursive tables, see the Essbase Integration Services Data Preparation Guide.

In some cases, the parent and child columns and the columns with additional information are not in the same table. For example, in the graphic below, the EMPLOYEE RELATIONSHIPS table and the EMPLOYEE DETAILS table show such a differentiation.

Defining a Recursive Table Associated with Additional Information describes how to define a recursive table that can be used with aliases or UDAs. In the table in Defining a Recursive Table Associated with Additional Information, the Data value characteristics column includes only those situations for which Integration Server can work with aliases or UDAs.

For more information about aliases and UDAs, see the Essbase Integration Services Data Preparation Guide.


Defining a Recursive Table Associated with Additional Information

The table below describes how to define a recursive table that can be used with aliases or UDAs. In this table, the Data value characteristics column includes only those situations for which Essbase Integration Server can work with aliases or UDAs.

Data Value Characteristics

When the alias or UDA columns and the parent and child columns are in one recursive table

When the alias or UDA
columns and the parent and
child columns are in separate
tables

The additional information applies to the parent and the parent column is fully defined.

1 Define the recursive table with a self-join.

2 Hide the child column.

1 Define the recursive table with a self-join.

2 Join the foreign key column of the table containing the alias or UDA column to the parent column of the recursive table.

3 Hide the child column.

The additional information applies to the child column and the child column is fully defined.

1 Define the recursive table with a self-join.

2 Hide the parent column.

1 Define the recursive table with a self-join.

2 Join the foreign key column of the table containing the alias or UDA column to the child column of the recursive table.

3 Hide the parent column.

The additional information applies to either the parent column or to the child column and neither column is fully defined.

You cannot use an alias or a UDA in this situation.

1 Define the recursive table with a self-join.

2 Join the foreign key column of the table containing the alias or UDA column to the parent column of the recursive table.

3 Join the foreign key column of the table containing the alias or UDA column to the child column or the recursive table.

  • Do not hide the parent column.

  • Do not hide the child column.


Joining a Table to Itself

To join a table to itself:

1 In the right frame of the OLAP Model main window, select the dimension table to be joined to itself, and select Edit > Properties > Tables.

Essbase Integration Services Console displays the Table Properties dialog box.

2 Select the Physical Joins tab.

On the Physical Joins tab, two Table drop-down lists display the name of the physical table that you are joining to itself. Two Column lists display the columns in the table.

3 In the Column list on the left, select the name of the column representing the lower level of the relationship.

4 In the Column list on the right, select the name of the column representing the higher level of the relationship.

5 Click Add.

The two join columns are displayed in the Recursion dialog box.

If the wrong columns are displayed in the Parent Column and Child Column list boxes, click Swap to switch the columns.

If you make a mistake, click Swap to switch the columns again.

6 Click OK.

Integration Services Console displays the Physical Joins tab of the Table Properties dialog box, with the table and column names in the Current Join Information box.

7 Click Close.

Notes:

  • In most cases, either the Parent Column or the Child Column is the significant column in the table. After defining a self-join for a recursive table, hide the column that is not significant (see Hiding or Showing Columns.

Tip: Be sure that the joins are correctly defined. Otherwise, the Essbase outline built from the table may not load correctly.


Creating Hierarchies Automatically

Related Topics

Overview

Relational data source users can create hierarchies automatically.

Note: If you are accessing a flat file data source, do not use this functionality. For a flat file data source, you create hierarchies manually .

To create hierarchies automatically using Essbase Integration Services Console:

1 Select Tools > Create Hierarchies or click the Create/Get Hierarchies toolbar button, [image] , to access the Create Hierarchies dialog box.

Note: You can also access this function by using the "autodetect" link in the Intelligent Help (Hierarchy process button, Create Standard Hierarchy Automatically task).

2 Click the Select All button to select all possible detected hierarchies.

Alternatively, to select only one hierarchy for hierarchy creation, click the hierarchy name. To select more than one but not all hierarchies for hierarchy creation, press and hold down the Ctrl key and click your hierarchy creation choices.

3 Click the Create button to launch the hierarchy creation process.

A message box prompts you when all selected hierarchies have been successfully created.

4 Click OK to clear the message box.


Creating Hierarchies Manually

Relational and flat file data source users can create hierarchies manually.

To create a hierarchy:

1 Double-click the dimension for which you want to create a hierarchy; for example, MARKET.

Essbase Integration Services Console displays the Dimension Properties dialog box.

2 In the Dimension Properties dialog box, select the Hierarchies tab.

The box lists any hierarchies that have been defined for the selected dimension.

3 To open the Edit Hierarchy dialog box, select a dimension and click Add.

The box on the left displays the dimension name above the name of the dimension table. The hierarchy graphic in the right box contains the default name for the hierarchy.

4 In the Name text box, enter the name for the hierarchy.

The hierarchy name that you enter replaces the default name (Hierarchy1).

5 To display the columns of the selected dimension, in the left box, click the plus sign, [image] , in front of the table.

To view the data in a column, in the left box, select a column and click View Table Data. The data values for all columns in the corresponding table in the data source are displayed.

For more information, see Viewing Table Data.

6 To place a column at the top of the hierarchy, in the left box, double-click the column name.

The name of the selected column is displayed in the right box immediately below the name of the hierarchy. For example, double-click the REGION column to place it at the top of the hierarchy. Column names in a hierarchy are prefaced with the name of the dimension table to which they belong; for example, REGION.REGION.

7 To place a column at the next level in the hierarchy, double-click the column.

The column is displayed in the right box immediately below the previously selected column.

8 To view or perform additional operations on a completed hierarchy, proceed with any of the following options:

  • To create a filter or sort on the selected column, click Filter (see About Hierarchy Filters or Sorting Data in Hierarchies).

  • To transform the data in the selected column, click Transform (see Transforming Data in Hierarchies - Overview).

  • To view the hierarchy as it will appear in the Essbase database, click View Sample. For more information, see Previewing the Essbase Outline.

9 Click OK twice to return to the OLAP Model main window.


Deleting Hierarchies

Relational and flat file data source users can delete any existing hierarchy.

To delete a hierarchy:

1 In the OLAP model, double-click the dimension that contains the hierarchy.

2 When the Dimension Properties dialog box opens, select the Hierarchies tab.

3 Select the name of the hierarchy you want to delete.

4 Click Delete to delete the selected hierarchy.


Editing Hierarchies

Relational and flat file data source users can edit any existing hierarchy.

To edit a hierarchy:

1 In the OLAP model, double-click the dimension that contains the hierarchy.

2 When the Dimension Properties dialog box opens, select the Hierarchies tab.

3 Select the name of the hierarchy you want to edit.

4 In the Hierarchies tab of the Dimension Properties dialog box, click Edit.

Essbase Integration Services Console displays the Edit Hierarchy dialog box.

The hierarchy icon in the right box contains the name of the hierarchy that you are editing; for example, Market.

5 To display the columns of a dimension table in the left box, click the plus sign, [image] , in front of the table to expand the view.

6 To view the data in a column, in the left box, select the column and click View Table Data.

Integration Services Console displays the View Table Data dialog box showing the first 100 rows of data in the selected column.

For more information, see Viewing Table Data.

7 To make changes to an existing hierarchy, proceed with any of the following options:

  • To delete a column from the hierarchy, right click the column in the hierarchy and select Delete.

  • To add a column or to move a column to some level above the lowest level in the hierarchy, delete the columns located above the level where you want to add or move a column and then add the columns in the order that you want them listed.

  • To create or edit a filter or sort on the selected column, click Filter (see About Hierarchy Filters or Sorting Data in Hierarchies).

  • To create or edit data transformations on the selected column, click Transform (see Transforming Data in Hierarchies - Overview ).

  • To view the hierarchy as it will appear in the Essbase database, click View Sample.

8 Click OK to close the dialog box.


Creating Filters in Hierarchies

You create filters on columns in hierarchies to determine what data values are returned. When transforming data values, Essbase Integration Server performs filters before it performs sorts and transformations.

To create a filter for a column in a hierarchy:

1 In the Dimension Properties dialog box, select the Hierarchies tab.

  1. Select the hierarchy to filter and click Edit.

The Edit Hierarchy dialog box is displayed.

  1. In the right-hand box, click the plus sign, [image] , in front of a hierarchy name.

The columns in the hierarchy are displayed.

2 From the hierarchy, select the column to filter and click Filter.

Integration Services Console displays the Edit Filter dialog box. Use the dialog box to specify conditions and operators for the selected conditions. A condition that you apply to a filter is like an SQL WHERE clause.

If you enter a condition value directly into the Filters text box, you must use the syntax rules of the SQL for the data source that you use.

The Column drop-down box displays the selected column. Notice that the Add button is not selectable; you cannot add a filter until you have selected the condition to use to create the filter, as described in steps 3 and 4.

3 Click the browse button, [image].

Integration Services Console displays the Select Values dialog box. The Select Values dialog box lists the values that you can select.

4 Select one or more values to include in the filter.

The number of values (conditions) that you can select depends on the type of operator that you want to apply. You can select more than one value (condition) when applying the in or not in operator. You can select only a single value (condition) when applying any other operator. Available operators are described in the table below.

Operator | Description
in | Use to select one or more values; for example, Region in United States or Europe selects regions in both the United States and Europe.
not in | Use to eliminate one or more values (see in operator).
like | Selects values with similar characters. For example, to select all products that begin with P without specifying all characters in the name, set the condition Like "P%" (the wildcard character may vary with the RDBMS).
not like | Selects values without similar characters (see Like operator).
= | Equal to.
<> or ! = | Not equal to.
> | Greater than.
>= | Greater than or equal to.
< | Less than.
<= | Less than or equal to.

5 Click OK.

The values (conditions) that you selected are displayed in the Condition text box of the Edit Filter dialog box.

The Add button of the Edit Filter dialog box is now selectable.

6 From the Operator drop-down list, select an operator; for example, in or = (equal to).

The available Operator options are described in step 4.

7 Click Add.

The condition and the operator that you selected are displayed in the Filters list box.

8 To add another condition to the filter, in the Add Type box, click And or Or to determine how the filters are combined, and repeat steps 3 through 7.

You can group And and Or clauses by typing them directly into the Filters text box. Be sure to use complete SQL WHERE clauses with the valid SQL syntax for the RDBMS that you use.

9 To view the data that will be returned by the filter, click Preview Results. For more information, see Previewing the Essbase Outline.

10 To apply the filter to the column and return to the Edit Hierarchy dialog box, click OK.


Transforming Data in a Hierarchy

Related Topics

After you create a dimension hierarchy, you can change the way the data values of the hierarchy are represented when reported by Essbase. For example, perhaps the date values of the OLAP model hierarchy are numeric and you want the corresponding Essbase members to be alphabetic.

If the data values of source columns contain leading or trailing spaces, you can omit the spaces from the corresponding values reported in Essbase. You can change capitalization or add a parent value prefix or suffix to help you identify in a report the dimension to which a value belongs. You can also ensure that source data, as well as any transformations set on hierarchies in an OLAP model or individual members in a metaoutline, are assigned valid Essbase names before load operations are performed.

In a metaoutline, you can use a transformation to generate unique member names required by Essbase to report data values accurately. For example, if you have duplicate month column names for two or more years, you can add a suffix to the month column names to include the year so that Essbase reports the correct values for each month in each year.

Note: Not all transformations and transformation rules apply if you plan to enable Hybrid Analysis. See Transformation Support in Hybrid Analysis and Limitations on Transformation Rules with Hybrid Analysis for more information.

For detailed information on using transformations to change member names, see Transforming Member Names.


Sorting Data in Hierarchies

Overview

To define a sort order for column values:

1 Open the Hierarchies tab of the Dimension Properties dialog box.

  1. Select a hierarchy and click Edit.

The Edit Hierarchy dialog box is displayed.

  1. To expand the view, in the right box, click the plus sign, [image] , in front of the hierarchy.

2 To display the Edit Filter dialog box, select the column for which you want to sort values, and then click Filter.

3 Select the Ordering tab.

4 From the Sort Order drop-down list, select Ascending, Descending, or None.

5 Click OK three times to return to the OLAP Model main window.

To change the way data values are represented when reported by Essbase, see Replacing Data in a Hierarchy .


Replacing Data in a Hierarchy

Related Topics

Overview

You can change column data by replacing current values with new values, changing capitalization, and removing leading and trailing spaces.

To transform data values in a hierarchy:

1 Double-click the dimension that contains the hierarchy with which you want to work.

2 In the Dimension Properties dialog box, select the Hierarchies tab, then select the appropriate hierarchy, and click Edit.

3 In the Edit Hierarchy dialog box, select from the hierarchy the column for which you want to transform data values (for example, MARKET), and click Transform.

Essbase Integration Services Console displays the Replace tab of the Edit Transformations dialog box.

4 If you want to replace text, perform the following tasks:

  1. In the Search for text box, enter the original value and, in the Replace with text box, enter the text to replace the value.

  2. Specify the conditions for making the replacement by checking one or more of the following options:

  • Case Sensitive (C). Replaces only text strings that match the capitalization of the string in the Replace text box.

  • Match Whole Word (W). Replaces the text string only when it occurs as a whole word. For example, when replacing the 10 in the string 100 10 1 with an A, if you set the Match Whole Word option, the string changes to 100 A 1.

  • Replace All Occurrences (A). Replaces every occurrence of the text string. For example, if you replace all occurrences of 10 in the string 100 10 1 with an A, the string changes to A0 A 1.

  1. Click Add to List to add the transformation to the value specified.

The entries move to the Replace box.

5 If you want to change the capitalization of a data value, select one of the following options:

  • Original. Does not change the case. This setting is the default.

  • Upper Case. Changes the value to uppercase; for example, january or January to JANUARY.

  • Lower Case. Changes the value to lowercase; for example, JANUARY or January to january.

  • Capitalize First Letters. Changes the first letter of the value and the first letter following a space or an underscore to uppercase; for example, qtr1_january changes to Qtr1_January.

6 If you want to replace spaces, select one or both of the following options:

  • Drop Leading/Trailing Spaces. Omits spaces before or after the data value.

  • Convert Spaces to Underscores. Converts spaces that exist in the original data value to underscores ( _ ).

7 If you want to view the data as it will appear in the Essbase outline, click Preview Results.

For more information, see Previewing the Essbase Outline.

8 Click OK three times to return to the OLAP Model main window.


Converting Names in a Hierarchy to Valid Essbase Names

You can ensure that source data and any transformations you set within a hierarchy are, as appropriate, automatically converted to valid Essbase names before load operations are performed.

To convert source data and transformations within a column in a hierarchy to valid Essbase names:

1 Double-click the dimension that contains the hierarchy to which you want to apply valid Essbase names.

2 In the Dimension Properties dialog box, select the Hierarchies tab, then select the appropriate hierarchy, and click Edit.

3 In the Edit Hierarchy dialog box, select from the hierarchy the column that contains the source data or transformed names for which you want to ensure valid Essbase names (for example, MARKET), and click Transform.

Essbase Integration Services Console displays the Replace tab of the Edit Transformations dialog box.

4 Select the Convert to Valid Essbase Names check box.

By default, the Convert to Valid Essbase Names box is not selected.

When this option is selected, prior to performing a load operation, Essbase Integration Server checks whether source data as well as any transformed member names within a hierarchy conform to Essbase naming rules, as described in Naming Conventions. If a transformed member name is found to be invalid, Essbase Integration Server applies the following conversion rules, as needed, and automatically makes the conversion to a valid Essbase name.

  • If the first character of a member name is a restricted character, replace it as follows:

    • *@*_ for @ | *(*_ for (
*\*_ for \ | *)*_ for )
*{*_ for { | *.*_ for .
*}*_ for } | *+*_ for +
*,*_ for , | *‘*_ for ‘
*-*_ for - | *_*_ for _
*=*_ for = | *|*_ for |
*<*_ for < | *dq*_ for "

    • Replace all instances of double quotation marks (") within a member name (except as a trailing character) with _dq_.

    • Replace trailing double quotation marks (") with dq_.

    • If a name length exceeds 80 characters, truncate the name and add **_ to the beginning of the name.

    A name is truncated to accommodate the addition of two asterisks and an underscore (**_) to the name and to accommodate any extra characters added to the member name by other conversions. After a load operation, in the Essbase outline, **_ at the beginning of a member name denotes that the name has been truncated.

    5 If you want to view the data as it will appear in the Essbase outline, click Preview Results.

    For more information, see Previewing the Essbase Outline.

    6 Click OK three times to return to the OLAP Model main window.


Prefixing and Suffixing Column Values

Related Topics

Overview

You can use a prefix or suffix to generate unique member names from the columns of a hierarchy. Essbase requires unique member names to report data values accurately. For example, if you use the same month names for multiple years, you can add a year suffix to the month column names of each hierarchy to ensure that Essbase reports the correct value for each month of each year.

Note: The default setting for columns in a time hierarchy is parent name.

To define prefixes or suffixes for data values in a hierarchy:

1 Double-click the dimension that contains the hierarchy with which you want to work.

2 To display the Edit Hierarchy dialog box, select the Hierarchies tab, then select the appropriate hierarchy, and click Edit.

3 In the Edit Hierarchy dialog box, select the hierarchy column that you want to transform (for example, MARKET.CITY), and click Transform.

The Edit Transformations dialog box is displayed.

4 Select the Prefix/Suffix tab.

Essbase Integration Services Console displays the Prefix/Suffix tab.

5 To add a prefix or suffix to a data value, select the value to prefix or suffix, as listed in the table below:

Prefix/Suffix | Definition
None | Uses the column value as the full name. Nothing is attached.
Parent Name | Attaches the immediate parent; for example, Florida_Miami.
Grandparent Name | Attaches the parent and the parent of the parent; for example, Central_Florida_Miami.
All Ancestors’ Names | Attaches all parents; for example, Market_Central_Florida_Miami.
Dimension Name | Attaches the dimension name; for example, Market_Miami.
Custom Prefix Custom Suffix | Attaches your own prefix or suffix. Type the prefix or suffix in the text box; for example, SKU_.

6 To insert a character between the prefix or suffix and the member value, in the Prefix Separator or Suffix Separator box, select the character to insert.

For example, to prefix the value for Year to the quarter members and insert an underscore between the prefix and the quarter member, select Parent Name and Underscore. This procedure creates member names like 2000_Quarter4.

7 Click Preview Results to view the data as it will appear in the Essbase outline.

The Preview Transformation Results dialog box is displayed.

For more information, see Previewing the Essbase Outline.

8 Click Close to close the Preview Transformation Results dialog box.

9 Click OK three times to close all open dialog boxes and return to the main window.


Previewing the Essbase Outline

You can preview the Essbase outline that will be created from a hierarchy. The preview includes the results of filtering and transformation operations. For example, you can create a filter and then view the column values that will be returned by the filter.

To preview the Essbase outline:

1 Choose one of the following options:

  • If you are creating a filter in the Edit Filter dialog box, click Preview Results.

  • If you are creating a transformation in the Edit Transformations dialog box, click Preview Results.

  • If you are creating a hierarchy in the Edit Hierarchy dialog box, click View Sample.

Essbase Integration Services Console displays the Preview Transformation Results dialog box. You can click the plus sign, [image] , in front of each member to expand and view the full member hierarchy within the sample outline.

The top list box displays the members that will show up in the Essbase outline.

The Sample Build SQL list box displays the SQL that Essbase Integration Server uses to retrieve the members from the data source.

2 When you finish viewing the members and the SQL, click Close.


Enabling Regional Options for Multiple Languages

Related Topics

When you enable the appropriate languages in the Windows, Integration Services Console can display OLAP model and metaoutline elements in the languages of the source database.

Integration Services Console uses the settings you configure in the Regional Options dialog box in Windows 2000 and the Regional and Language Options dialog box in Windows XP to display languages.

To enable the Regional Options for languages in Windows 2000:

1 In the Windows taskbar, select Start > Settings > Control Panel and double-click Regional Options.

The General tab of the Regional Options dialog box is displayed.

2 In the Your locale (location) drop-down list, select the appropriate locale.

In most cases, you set the locale to match your location.

3 In the Language settings for the system frame, select the languages that you want to display on the Integration Services Console computer.

For example, you may select Cyrillic to display Russian characters or Simplified Chinese and Traditional Chinese to display Chinese characters.

4 If the languages that you need do not appear in the Language settings for the system frame, or if you want to attach a specific code page to a language, click the Advanced button and perform the following tasks:

  1. In the Advanced Regional Options dialog box, select the appropriate code page conversion tables.

Select the code page tables that correspond to the languages that you want to display on the Integration Services Console computer.

  2. Click OK to close the Advanced Regional Options dialog box.

5 In the Regional Options dialog box, click Apply to apply the changes you made.

6 Click OK to close the Regional Options dialog box.

For more information on Windows Regional Options, refer to the Windows 2000 documentation.

To enable the Regional and Language Options in Windows XP:

1 In the Windows taskbar, select Start > Control Panel and double-click Regional and Language Options.

The Regional Options tab of the Regional and Language Options dialog box is displayed.

2 In the Standards and formats frame, from the drop-down list, select the appropriate language to use for formatting items such as numbers, currencies, time, and dates.

In most caes, you set this language to match your location.

3 Click the Languages tab and click the Details button.

The Text Services and Input Languages dialog box is displayed.

4 In the Installed services frame, click Add.

5 In the Input Languages list, select the language that you want to add and select the check box for the type of text service you want to install.

For example, you may select Cyrillic to display Russian characters or Simplified Chinese and Traditional Chinese to display Chinese characters.

6 If the languages that you need do not appear in the Input Languages frame, or if you want to add a specific code page to a language, click the Advanced tab and perform the following tasks:

  1. In the Code page conversion tables frame, select the appropriate code page conversion tables.

Select the code page tables that correspond to the languages that you want to display on the Integration Services Console computer.

  3. Click Apply.

7 Click OK to close the Regional Language and Options dialog box.

For more information on Windows Regional Language and Options, refer to the Windows XP documentation.


Updating an OLAP Model and Metaoutline After RDBMS Changes

Related Topics

Overview

Before you begin this procedure, consider making backup copies of the OLAP models and metaoutlines you plan to work with. You can use the "Save Model As " function to quickly save an OLAP model and its associated metaoutines.

To update an OLAP model and the associated metaoutlines based upon changes to the underlying RDBMS:

1 Open the OLAP model that is affected by changes that have been made to the RDBMS.

2 Connect to the relational data source that contains the updated RDBMS.

3 In the left frame of the OLAP Model main window, expand the data source icon that is associated with the RDBMS that has been changed.

See Displaying Tables, Views, and Synonyms for more information on the items listed in the left frame of the OLAP Model main window.

4 Under the appropriate data source icon, expand the TABLES icon.

Any new or modified RDBMS tables are displayed under the TABLES icon. Expand the appropriate table name to view any new columns that were added

To add a new table to the OLAP model, proceed to step 4. To add a new column to an existing dimension table, proceed to step 5.

5 If you want to add a new RDBMS table to your OLAP model, add the new table in one of the following ways:

  • To add a table to create a new dimension, see Adding Source Tables to Create Dimension Branches

  • To add a table to an existing dimension table, see Adding Source Tables Directly to Dimension Tables

6 If you want to add a new RDBMS column to your OLAP model, add the new column by following the procedure described in Creating User-Defined Columns.

Note: When completing the Add New Column to Table dialog box, be sure to do the following:

  • Select the new or modified source table in the Physical Table drop-down list box.

  • Select the new source column in the Physical Column drop-down list box.

7 Verify that the OLAP model is valid by select File > Verify.

8 Save the OLAP model.

9 Open the appropriate metaoutline.

Any new dimensions are displayed in the left frame of the OLAP Metaoutline main window. Expand the appropriate dimension to view any new members (columns) you added.

10 Add the new dimensions or members to the metaoutline as needed.

If you need help, follow the procedures listed in Procedures > OLAP Metaoutline Tasks > Creating Dimensions, Member Levels, and Measures.

11 When you are finished making changes, select File > Verify to validate the metaoutline.

12 Save and close the metaoutline.

13 Repeat steps 9 through 12 for any metaoutlines that you want to update.

You must perform member and data loads using the updated metaoutlines in order to view changes in associated Essbase outlines or spreadsheets.


New Command

File > New Command

Use the New command to create an OLAP model and determine the data source on which to base it. Use the Save command to name the new OLAP model.


Open Command

File > Open Command

Related Topics

Use the Open command to display the Welcome dialog box, Existing tab. This dialog box displays all the OLAP models stored in the OLAP Metadata Catalog to which you are connected.


File > Delete Command

Related Topics

You can use three different Delete commands.

  • The Delete command located on the File menu deletes an OLAP model. It displays the Delete an OLAP Model/Metaoutline dialog box. When you delete an OLAP model, Essbase Integration Server removes the OLAP model from OLAP Metadata Catalog. A dialog box opens, prompting you to confirm that you want to delete the OLAP model.

Note: When an OLAP model is deleted, its associated metaoutlines are automatically deleted. If there are metaoutlines associated with a model you intend to delete, you are prompted to confirm that you want to delete the OLAP model and all its associated metaoutlines.

Caution: You cannot undo the deletion of an OLAP model.

  • The Delete Command located on the right-click menu when selected from the dimension table deletes a dimension table from the OLAP model.

When you delete a dimension table, its columns are not available for any metaoutlines that you subsequentially create.

You cannot delete a dimension table that is referenced by a metaoutline stored in the OLAP Metadata Catalog or one that is in the process of being created and is not yet saved. When you attempt to delete a dimension table that is referenced by a metaoutline, a validation check is performed and a message is returned to the console informing you that the dimension table is currently in use by a hierarchy or a metaoutline.

Deleting a table in the OLAP model does not affect the tables of the data source.

Note: When you delete a dimension table, you also delete the joins associated with it. If the dimension table you delete is the primary dimension table of a dimension branch, the other dimension tables in the branch remain in the OLAP model as unjoined dimension tables.

A dialog box opens, prompting you to confirm that you want to remove the dimension table from the OLAP model.

  • The Delete command located on the right-click menu when selected from a join line deletes the join line. This action deletes any unjoined columns between the two dimension tables that were originally joined.


File > Close Command

Related Topics

Use the Close command to close the OLAP model. If you try to close an OLAP model without saving it, you will be prompted to save or discard your changes.

Click Yes to save the OLAP model or No to close the model without saving any changes you have made. If the model you are closing is a new model that has never been saved, it will be lost if you click No to the Save changes? message.


File > Save Command

Related Topics

Use the Save command when you save an OLAP model, Essbase Integration Server automatically performs a validation check and writes it to OLAP Metadata Catalog.

You cannot save an OLAP model if other users are reading it. Save it later or save the model with a different name using File > Save As.


File > Save As Command

Related Topics

Use the Save As command to save an OLAP model if you want to:

  • Save the OLAP model with a new name. (The original OLAP model retains the original name)

  • Make a copy of the OLAP model to use in creating a new model.

Note: You cannot copy an OLAP model using your operating system because the OLAP model resides in the OLAP Metadata Catalog. Instead, you need to save the OLAP model to a different name.


File > Save Model As Command

Related Topics

You use the Save Model As command if you want to perform any of the following tasks:

  • Save the OLAP model without losing its modified metaoutlines

  • Save the OLAP model without saving its associated metaoutlines

  • Save the OLAP model with a new name while keeping the original model and metaoutlines


File > Rename Model/Metaoutline

Related Topics

You use the Rename Model/Metaoutline command to rename an OLAP model or metaoutline.


File > XML Import/Export Command

Use the XML Import/ Export command to import data into and export data from the OLAP Metadata Catalog. You can modify your own cubes and multidimensional databases as well as OLAP models and metaoutlines using an XML-based editor and then import the data into the OLAP Metadata Catalog.


File > Verify Command

Related Topics

Use the Verify command to validate an OLAP model. When you validate an OLAP model, Essbase Integration Services checks to ensure that the following conditions are met:

  • The OLAP model contains a fact table.

  • The OLAP model contains at least one measures value; for example, sales amounts, quantities sold, or commissions.

  • The OLAP model contains at least one dimension.

  • If the OLAP model contains a single dimension, the dimension contains at least one column.

  • Dimensions in the OLAP model that are made up of two or more source tables have joins defined.

  • The OLAP model contains no dimensions that have joins defined between two tables, but have no joins defined between columns in the two tables.

  • The OLAP model contains no dimensions with duplicate names.

If the model is valid, the console displays a message telling you that there are no errors in the model.

If Integration Services encounters errors when validating the OLAP model, the OLAP Model Validation Errors dialog box opens.


File > Print Command

Use the Print command to print the OLAP model contained in the right frame of the OLAP Model main window. Use the printed copy as a hard-copy reference or to retain on file. The data source tables and views information in the left frame of the window does not print when you print the OLAP model.


File > Exit Command

Related Topics

Use the Exit command to exit from Essbase Integration Services Console. If you try to close an OLAP model without saving it, you are prompted to save or discard your changes.

Click Yes to save the OLAP model or No to close the model without saving your. If the model you are closing is a new model that has never been saved, it will be lost if you click No to the Save changes? message.


Edit > Rename Command

Use the Rename command to rename the selected dimension.

To access the Rename command, right-click the title bar of a dimension table.

Note: This is command is not available for the fact table.


Edit > Add Joins Mode

Use the Edit > Add Joins Mode command to place the Join tool in Add Joins mode instead of Move mode. To return to Move mode, deselect Edit > Add Joins again.


Edit > Add Accounts Dimension Command

Related Topics

Use the Add Accounts Dimension command to manually create an accounts dimension. Creating an accounts dimension may be necessary if the source table or view that you used to create the fact table does not include a column that stores measures data, such as Actual Sales or Units Sold.

To access the Add Accounts Dimension command, right-click the title bar of the fact table.


Edit > Add Time Dimension Command

Related Topics

Use the Add Time Dimension command to manually create a time dimension based on the fact table, whereby the time dimension table inherits all of the columns in the fact table.

To access the Add Time Dimension command, right-click the title bar of the fact table.


Edit > Add User-Defined Table Command

Use the Add User-Defined Table command to create a user-defined table for a virtual view of your source database schema. You can then create and edit OLAP models and metaoutlines without altering the schema.

To access this command, select the User-Defined Tables icon in the left frame of the OLAP Model main window and then select Edit > Add User-Defined Table.

Alternatively, right-click the User-Defined Tables icon in the left frame of the OLAP Model main window and select Add User-Defined Table.


Edit > Properties Command

Related Topics

You use the Properties command to set properties for the following objects in the right frame of the OLAP Model main window:

  • OLAP models. Click in a blank space in the OLAP model. Use the Edit > Properties > Model command (or right-click and select Properties) to set properties for the current OLAP model. This command displays the OLAP Model Properties dialog box.

  • Dimensions. Select the title bar of a dimension. Use the Edit > Properties > Dimension command (or right-click and select Properties) to set properties for the selected dimension. You can also view, add, or edit hierarchies using this dialog box. This command displays the Dimension Properties dialog box.

  • Dimension tables. Select a dimension table in the right frame and use the Edit > Properties > Table command (or right-click and select Properties) to set properties for the selected dimension table. This command displays the Table Properties dialog box.

  • Columns. Select a column, and use the Edit > Properties > Column command (or right-click and select Properties) to set properties for the selected column. This command displays the Column Properties dialog box.

  • Joins. Select a join line, and use the Edit > Properties > Join command (or right-click and select Properties) to display or change the properties for the selected join. This command displays the Edit Join Info dialog box.

You also use the Properties command to view the properties of a data source. Right-click on a data source name in the left frame of the OLAP Model main window to display the Data Source Properties dialog box. The Data Source Properties dialog box provides the following information:

  • Data Source. The data source name that you selected in the left frame of the OLAP Model main window.

  • User Name. The user name that was used to connect to the selected data source

  • Data Source Type. The data source type is either Primary or Secondary.

  • Connect Status. The connection status is either Connected or Not Connected.

  • In Use Status. The in use status is either "in use" or "not in use."

  • Dimensions in use. A list of dimensions that are used in the OLAP model.


Toolbar Command

View > Toolbar Command

Related Topics

Use the Toolbar command to view the toolbar used for executing commonly used functions. To hide the toolbar, deselect View > Toolbar. The check mark next to Toolbar in the menu is removed and the toolbar is hidden. To show the toolbar again, select View > Toolbar.

The toolbar is a floating toolbar that is not attached to the edge of the program window. You can move it by selecting it and dragging it to a new location. You can also change the shape and size of the toolbar.


Status Bar Command

View > Status Bar Command

Related Topics

Use the Status Bar command to show you the database, the OLAP Metadata Catalog to which you are connected, and other status information. To hide the status bar, select View > Status Bar. The check mark next to Status Bar in the menu is removed and the status bar is hidden. To show the status bar again, select View > Status Bar.


View > View Table Data Command

Use the View > View Table Data command to view the data values in the selected source table, user-defined table. This command opens the View Table Data dialog box.

Also, you can right-click on a source table or user-defined table and select View Table Data to open the View Table Data dialog box.


View > View Columns Command

Related Topics

Use the View > View Columns command to view a list of column names directly below the selected dimension table or fact table, without opening a dialog box.


View > View All Columns Command

Related Topics

Use the View > View All Columns command to view a list of all columns of all dimension tables in the OLAP model.


View > Enable Join Tool Tips Command

Use the View > Enable Join Tool Tips command to view the joins between two tables when you move the cursor over the join line in the right frame of the OLAP Model main window.


View > Model Icons Commands

Use the View > Model Icons commands to turn off the display of icons next to columns in the OLAP model. By default, icons are displayed next to all columns in the OLAP model.

Use the following commands to deselect the display of icons in the current OLAP model:

  • All: Select View > Model Icons > All to turn off the display of all column icons.

  • Primary Key: Select View > Model Icons > Primary Key to turn off the display of the primary key icon next to all primary key columns.

  • Join: Select View > Model Icons > Join to turn off the display of the join icon next to all join columns.

  • Filter: Select View > Model Icons > Filter to turn off the display of the filter icon next to all columns for which a filter is defined.

  • Hierarchy Transformation: Select View > Model Icons > Hierarchy Transformation to turn off the display of the hierarchy transformation icon next to all columns for which a hierarchy transformation is defined.

  • Column Transformation: Select View > Model Icons > Column Transformation to turn off the display of the column transformation icon next to all columns for which a column transformation is defined.

  • Hidden Column: Select View > Model Icons > Hidden Column to turn off the display of the hidden icon next to all hidden columns.

  • Drill-Through: Select View > Model Icons > Drill-Through to turn off the display of the drill-through icon next to all drill-through columns.

  • Attribute Enabled: Select View > Model Icons > Attribute Enabled to turn off the display of attribute-enabled icon next to each attribute-enabled column.


Connections > OLAP Metadata Catalog > Connect Command

Related Topics

Use the Connections > OLAP Metadata Catalog > Connect command to connect to OLAP Metadata Catalog. OLAP Metadata Catalog is a single SQL relational database containing the following elements:

  • Metadata describing the nature, source, location, and type of data to be retrieved from the data source

  • Metadata describing the information required to generate an Essbase outline

  • OLAP models and metaoutlines

OLAP Metadata Catalog must be a data source that is configured for open database connectivity (ODBC). If you do not know how to create an ODBC data source, see the Essbase Integration Services System Administrator's Guide or the ODBC user documentation.

You can also access this command when you select a data source that was previously not connected in the right frame of the OLAP Model main window, right-click and choose Connect from the popup menu.


Connections > OLAP Metadata Catalog > Disconnect Command

Related Topics

Use the Connections > OLAP Metadata Catalog > Disconnect command to disconnect from OLAP Metadata Catalog. You can also disconnect by selecting a data source in the right-frame of the OLAP Model main window and choosing Disconnect from the popup menu.

Note: Any OLAP models or metaoutlines you have open when you select Connections > OLAP Metadata Catalog > Disconnect will automatically be closed.


Connections > OLAP Metadata Catalog > Reconnect Command

Related Topics

If connection is lost while you are working on an OLAP model or metaoutline, use the Connections > OLAP Metadata Catalog > Reconnect command to reconnect to Integration Server and OLAP Metadata Catalog without losing any unsaved work and without having to restart the console.


Connections > Add Data Sources Command

Related Topics

Use the Connections > Add Data Sources command to connect to additional data sources.

When you open a new OLAP model, only one data source is displayed in the left frame of the OLAP Model main window. This data source is, by default, a secondary data source. You can connect to other secondary data sources by selecting Connections > Add Data Sources and completing the Data Source dialog box.

Note: When you create an OLAP model using multiple data sources, the data source that contains the table you used for the fact table is the primary data source.


Tools > Create Catalog Command

Related Topics

Overview

Use the Tools > Create Catalog command to access the OLAP Metadata Catalog Setup dialog box to create new OLAP Metadata Catalogs.

By default, the Show this dialog at Startup check box in the OLAP Metadata Catalog Setup dialog box is selected whether you are installing Essbase Integration Services for the first time or upgrading from a previous version. New users will not be able to access Integration Services until at least one catalog is created. Existing users will not be able to access Integration Services until at least one catalog is upgraded.

If the Show this dialog at Startup check box is selected, the OLAP Metadata Catalog Setup dialog box is displayed automatically every time you start Integration Services Console. If the Show this dialog at Startup check box is cleared, you can access the OLAP Metadata Catalog Setup dialog box at any time by using the Tools > Create Catalog command.

Note: If you are already connected to an OLAP Metadata Catalog, you cannot access the OLAP Metadata Catalog Setup dialog box. You must first disconnect from the current OLAP Metadata Catalog by using the Connections > OLAP Metadata Catalog > Disconnect command. Then you can use the access the Tools > Create Catalog command to access the OLAP Metadata Catalog Setup dialog box.


Tools > Create Sample Command

Related Topics

Overview

Use the Create Sample command after you have created the sample OLAP Metadata Catalog as part of the Essbase Integration Services installation process. Use the Tools > Create Sample command to create the sample TBC (The Beverage Company) application automatically.

If you are upgrading from a previous release of Integration Services, use this command to automatically update your existing sample application to the current release.


Tools > Create Fact Table Command

Related Topics

Overview

Use the Tools > Create Fact Table command to access the Create Fact Table dialog box, where you create the OLAP model fact table and the related Time and Accounts dimensions automatically.

Note: You can also access the Create Fact Table dialog box by clicking the Create Fact Table toolbar button, [image] , or by using the "autodetect" link in the Intelligent Help (Fact Table process button, Create Fact Table Automatically task).


Tools > Create Dimensions Command

Related Topics

Overview

Use the Tools > Create Dimensions command to initiate the OLAP model standard dimension automatic creation process.

Note: You can also create dimensions automatically by clicking the Create Dimensions toolbar button, [image] , or by using the "autodetect" link in the Intelligent Help (Dimension process button, Create Standard Dimensions Automatically task).

A message box is displayed listing the names of the standard dimensions created. Click OK and the dimension creation process is complete,

You can also click Cancel to stop the dimension creation process.


Tools > Create Hierarchies Command

Related Topics

Overview

Use the Tools > Create Hierarchies command to access the Create Hierarchies dialog box, where you automatically create the OLAP model standard hierarchies.

Note: You can also access the Create Hierarchies dialog box by clicking the Create/Get Hierarchies toolbar button, [image] , or by using the "autodetect" link in the Intelligent Help (Hierarchy process button, Create Standard Hierarchy Automatically task).


Tools > Intelligent Help Command

Overview

Use the Tools > Intelligent Help command to display the Intelligent Help when you are working in an OLAP model. When displayed, a check mark appears next to the Intelligent Help menu item.

To hide the Intelligent Help while working in an OLAP model, select Tools > Intelligent Help. When hidden, a check mark does not appear next to the Intelligent Help menu item.

Note: You can also display or hide the Intelligent Help by clicking the Intelligent Help toolbar button, [image] .


Tools > View Log File

Overview

Use the Tools > View Log File command to retrieve Essbase Integration Server log file data and display it in the Server Log File window. The data retrieved depends on the logging level you set when you start the server. See the Essbase Integration Services Systems Administrator’s Guide for information on the server log file and setting the logging level.

Note: You can copy the log file data from the Server Log File window and paste it to any text editor, such as Notepad, to print it out for further review and analysis.

Note: If you open the log file directly (by double-clicking on the file name), you must open it in UTF-8 format for the text to display correctly.


Remove Command

Use the Remove command to remove any of the following items:

  • A selected data source

Right click a data source in the left frame of the OLAP Model main window and select Remove from the pop-up menu.

  • A selected user-defined table

Right-click a user-defined table in the left frame of the OLAP Model main window and select Remove from the pop-up menu.


Edit Definition Command

Use the Edit Definition command to modify the name or the SQL definition of a selected user-defined table. Right-click a user-defined table in the left frame of the OLAP Model main window and select Edit Definition from the pop-up menu.


About Adding Tables to the Accounts Dimension

When adding source tables to the accounts dimension, remember the following:

  • The source table that you are adding must have at least one measures data column; for example, COGS (cost of goods sold).

  • The columns are added to the fact table. Likewise, when you add a table to the fact table, the columns it contains are added to the accounts dimension.

  • In the accounts dimension, you can hide or delete non-measures columns after you add a table (see Hiding or Showing Columns and Deleting Columns).

  • Columns that are hidden or deleted in the accounts dimension do not appear in the fact table; likewise, columns that are hidden or deleted in the fact table do not appear in the accounts dimension.

Note: Deleted columns are permanently removed from the OLAP model. If you later decide to include the deleted columns, you must re-create the OLAP model.

For instructions on how to add tables to the accounts dimension, see Adding Source Tables Directly to Dimension Tables.


About Adding Tables to the Fact Table - Overview

About Adding Tables to the Fact Table – Overview

After you create a fact table, you can add other tables from the relational data source to the fact table. For example, assume that you created the fact table from a sales table in the relational data source. If you want to track inventory and keep inventory data values in a separate table, you need to add to the fact table the necessary columns from the relational data source inventory table.

When adding source tables to the fact table, remember the following:

  • When you add a source table to the fact table, the source table columns are added to the accounts dimension. Likewise, when you add a table to the accounts dimension, the source table columns are added to the fact table.

  • You can hide or delete columns in the fact table after you add a table (see Hiding or Showing Columns "and Deleting Columns).

  • Columns that are hidden or deleted in the fact table do not appear in the accounts dimension; likewise, columns that are hidden or deleted in the accounts dimension do not appear in the fact table.

  • When you add a source table to the time dimension, the source table columns are not added to the fact table. Likewise, when you add a source table to the fact table, the source table columns are not added to the time dimension.

To ensure that you select the correct relational source tables to add to the fact table, view the column names within the tables as described in Displaying Column Names. If you decide to hide columns after adding a table, check column names after completing the process to make sure that you made the correct selections.

Note: Deleted columns are permanently removed from the OLAP model. If you later decide to include those columns, you must re-create the OLAP model.


About Adding Tables to the Time Dimension

When adding source tables to the time dimension, remember the following:

  • The table that you add must have at least one column with date or time information; for example, a shipping date. The column can be any data type.

  • The columns are not added to the fact table. Likewise, when you add a table to the fact table, the columns are not added to the time dimension.

For instructions on how to add tables to the time dimension, see Adding Source Tables Directly to Dimension Tables.


About Attributes

You can use attributes to retrieve and analyze data not only from the perspective of dimensions, but also in terms of characteristics, or attributes, of those dimensions. For example, you can analyze product profitability based on size or packaging, and you can make more effective conclusions by incorporating market attributes, such as the population size of each market region, into your analysis.

Such an analysis can tell you that decaffeinated drinks sold in cans in small (less than 6,000,000-population) markets are less profitable than you had anticipated. For more details, you can filter your analysis by specific attribute criteria, including minimum or maximum sales and profits of different products in similar market segments.

Here are a few ways analysis by attribute provides depth and perspective, supporting better-informed decisions:

  • You can select, aggregate, and report on data based on common features (attributes).

  • By defining attributes as having a text, numeric, Boolean, or date type, you can filter (select) data using type-related functions such as AND, OR, NOT, <, >, and = comparisons.

  • You can use the numeric attribute type to group statistical values by attribute ranges; for example, population groupings such as <500,000, 500,000–1,000,000, and >1,000,000.

  • You can view sums, counts, minimum or maximum values, and average values of attribute data.

  • You can perform calculations using numeric attribute values in calc scripts and member formulas.

  • You can drill down through data to find out more detailed information, or drill up to see a summary overview of data.


About Column Data Types

Related Topics

When you drag a source table to the right frame to create a fact table or a dimension table for an OLAP model, Integration Server assigns a data type to each column of the table based on the Open Database Connectivity (ODBC) data type of the column in the source table. Some relational database management systems (RDBMS) use different names for these data types. Some RDBMSs do not list all data types; see the SQL documentation for the RDBMS that you are using.

An OLAP model refers to columns as having numeric, string, Boolean, or datetime data types.

The following table shows commonly used names for these data types.

ODBC Data Type | Integration Services Data Type
CHAR | String and Boolean. Integration Server handles columns that include up to 80 characters. For a column exceeding 80 characters, add a string column that is based on the CHAR column and use a substring transformation rule to extract 80 or fewer characters.
VARCHAR | String and Boolean. Integration Server handles columns that include up to 80 characters.
DECIMAL | Numeric and Boolean
NUMERIC | Numeric and Boolean
TINYINT | Numeric and Boolean
SMALLINT | Numeric and Boolean
BIGINT | Numeric and Boolean
INTEGER | Numeric and Boolean
REAL | Numeric and Boolean
FLOAT | Numeric and Boolean
DOUBLE | Numeric and Boolean
DATE | DateTime
TIME | DateTime
TIMESTAMP | DateTime
NCHAR | String
NVARCHAR | String

Integration Server does not support columns with the following data types:

  • LONGVARCHAR

  • BIT

  • BINARY

  • VARBINARY

  • LONGVARBINARY

Note: Do not use source database table or column names that contain or require around them special characters. Integration Server does not handle source database table or column names that contain or require around them special characters, such as quotation marks ( " ) or ampersands (&). For example, a table name in all lowercase letters would require quotation marks around it in Oracle, and this is not supported by Integration Services.

If supported by your RDBMS, source table and column names may contain blank spaces within them, but may not contain blank spaces around them.

For other database table and column naming restrictions, see the Limits and Guidelines appendix in the Essbase Integration Services System Administrator’s Guide.


About Dimension Branches

A dimension branch contains a dimension table known as the primary dimension table. The primary dimension table joins directly to the fact table and to one or more related dimension tables. In the figure below, the PRODUCT and FAMILY dimension tables form a dimension branch for the Product dimension.

Notice that both dimension tables are located in the Product dimension and that both dimension tables display the name Product in their title bars.

The hierarchical structure of dimension branches is similar to the consolidation levels reported in Essbase. The fact table in an OLAP model is considered level 0 (zero). A dimension table joined directly to the fact table is level 1. A dimension table joined to another dimension table that is joined directly to the fact table is level 2. A dimension table joined to a level 2 table is level 3, and so on.

Level numbers are assigned to dimension tables and branches to provide a hierarchical structure to work with as you create a metaoutline. Viewing a hierarchical structure in an OLAP model helps you to define the correct consolidation levels you require. Level numbers do not carry over to the metaoutline or to the subsequent Essbase outline.


About Hierarchies

Related Topics

In Essbase, hierarchies determine how data is consolidated. For example, many businesses summarize their data monthly, roll up the monthly data to get quarterly figures, and then roll up the quarterly data to get annual figures. Some businesses may also summarize data by zip code, and then by city, state, and country. Any dimension can be used to consolidate data for reporting purposes. For example, a hierarchy for a MARKET dimension in an OLAP model might look like the hierarchy shown below. In this hierarchy, REGION.REGION is a child of the dimension MARKET, and MARKET.STATE is a child of REGION.REGION. The structure of the hierarchy carries over to the Essbase outline, where you can report on sales for individual cities, consolidate city figures to report on sales for states, and consolidate state figures to report on regional sales.

Hierarchies provide named structures that contain:

  • The hierarchical structure itself—a level-by-level sequence for consolidating data. For example, sales totals by STATE roll up to sales totals by REGION. If you had CITY as a child of STATE in your hierarchy, sales totals by CITY could roll up to sales totals by STATE.

  • The data filters that are placed on selected columns within the hierarchy—a way to select specific categories of information. For example, you can filter the REGION column to include only information on sales in the USA.

  • The organizational sequence of the data—the sort sequence for a column. For example, you can sort the MONTH column in descending sequence to see the most recent totals first.

  • Transformations—a way to control column data values, which become Essbase member names. For example, if you had CITY as a child of STATE in your hierarchy, you could ensure unique member names by prefixing each STATE value with an appropriate value from the REGION column. You could then differentiate between REGALBANY and GAALBANY.

After a hierarchy is created in an OLAP model, it can be dragged directly onto a metaoutline. By creating hierarchies in OLAP models, you can customize an OLAP model for each user group. For example, assume that you use hierarchical filters in the OLAP model that you provide to users from a specific corporate division. With such an OLAP model, the users can more easily create metaoutlines that contain only the data relevant to their specific division. For information about how to use hierarchies to create metaoutlines, see Creating Dimensions Automatically Using Existing Hierarchies.


About Hierarchy Filters

You can apply filters to the columns in a hierarchy to restrict the data values that are reported. For example, you can create a filter for each regional office in the United States so that regional managers can view sales totals only for their respective regions. At headquarters, you can view sales for each region and for the total USA.

A filter contains the following components:

In an OLAP model, you can define a filter only at the column level of a hierarchy. Any filter that you define on a parent value applies to the children of that parent value; for example, if you define a REGION column filter that selects values from the Central region, values from the cities (children) of the Central region are automatically selected.

The filters that you define in an OLAP model are used to create related metaoutlines and are then used to create related Essbase outlines.

In a metaoutline, you can define filters for member levels in dimension hierarchies, for member levels created from columns contained in an OLAP model, or for measures contained in an OLAP model.

Note: If you make changes to a filter in a hierarchy in an OLAP model, the changes do not carry through to metaoutlines previously created from that OLAP model.

You can also define the sequence in which you want to see information. To define the sort order of a column, see Sorting Data in Hierarchies.


About Joins

A join establishes a relationship between the data of one table and the data of another table. The join is based on column values common to each table. The two tables can be one fact table and a dimension table, two dimension tables, two source tables consolidated within a dimension table, or a table joined to itself. Join relationships provide information that Essbase Integration Server uses to retrieve data from external data source tables. Integration Server then uses the data to build Essbase outlines and load Essbase databases.

Note: Tasks that you perform on the joins of an OLAP model do not affect the data source.

Joins between two tables are formed through related columns called join columns. The join columns of the two tables represent the same entity. For example, assume that two tables contain information about products. One table stores information about product suppliers. The other table stores information about the number of product units in the warehouse. Because both tables have a column that contains product numbers, you can join the tables by using the product number columns as join columns.

When a data value of a join column matches the data value of the corresponding join column, the information contained in the associated rows of both tables is related. For example, in the table below, the data values in join columns PRODUCTID and PRODNO match, so all information in the KC-100 row of the PRODUCTS table applies to the KC-100 row of the ORDERS table. Thus, you know that the supplier, Beverage Distrib. supplies the product Kool Cola at 1.10 per case.

Note: The names of the joined columns do not have to match.

If the dimension table automatically joins to the fact table, and you want to join the dimension table to a dimension table in a dimension branch, delete the join line between the dimension table and the fact table. You can now join the dimension table to whatever table you select, as described in Joining Dimension Tables to Create or Expand a Dimension Branch.

Why You May Need to Define Multiple Joins

You may need more than one pair of join columns to define a join relationship. For example, if a product number, as used by two suppliers, indicates two different products, you need to create two pairs of join columns, as shown below. Each pair of join columns is called a column join. One column join includes the product number columns of both tables. The other column join includes the SUPPLIERID columns of both tables. The second column join ensures unique joins.

In this example, for product KC-100 and supplier SUP440, the data of the two rows is related. If more than one supplier supplies Kool Cola, the join on SUPPLIERID ensures that the price and supplier name retrieved from the PRODUCTS table are correct for each supplier.


About Ragged Hierarchies

Related Topics

Ragged (asymmetrical) hierarchies occur when branches within a dimension have different numbers of levels. The Sample hierarchy below illustrates a ragged hierarchy where the East branch contains four levels, and the West branch contains five levels:

Essbase Integration Server can handle tables that create ragged hierarchies.


About Recursive Table Filters

You can most easily define a filter for a column of a recursive table in the process of defining a metaoutline. However, one advantage of an OLAP model is the fact that you can select and reuse its predefined dimensions and hierarchies in more than one metaoutline. If you plan to use a filter in more than one metaoutline, you may want to consider creating the filter in an OLAP model. Remember that, to create a filter on a column of a recursive table in a reusable OLAP model, you must first create a hierarchy.

You can define filters in an OLAP model only through hierarchies. When you create a hierarchy from a recursive table, filter on only the parent column or the child column. For more information on creating filters, see Creating Filters in Hierarchies

CAUTION: If you place both the parent and child columns of a hierarchy into an OLAP model, you lose the recursive tie between the rows of the table. Consequently, the table is no longer recursive.

To understand what to expect when you filter from a recursive table, consider the following example based on data shown in the table below.

Row Number | GEO_CHILD | GEO_PARENT
1 | USA | <NULL>
2 | East | USA
3 | Maine | East
4 | Bangor | Maine
5 | 01010 | Bangor
6 | New York | East
7 | Buffalo | New York
8 | 30092 | Buffalo

When you filter on a value in a parent column, Essbase Integration Server retrieves only records that include the filter value as the parent. For example, on the GEO_PARENT column, filtering for the value East retrieves the two rows that include East as the parent, rows 3 and 6, which contain Maine and New York in the GEO_CHILD column. No other rows are retrieved or reported on in the Essbase database.

When you filter on a value in a child column, Essbase Integration Server retrieves only records that include the filter value as the child. For example, on the GEO_CHILD column, filtering for the value East retrieves only row 2, the row that includes East in GEO_CHILD and USA in GEO_PARENT.

If you filter on the parent column, hide the child column. If you filter on the child column, hide the parent column.


About Recursive Tables

Related Topics

A recursive table (parent and child table) contains information in one row that is related to information in another row. For example, assume that you have a recursive table named GEOGRAPHY that contains the columns GEO_CHILD and GEO_PARENT. The fact that the table is recursive indicates that, within each row, the child geographical area is part of the parent geographical area. Any value can serve both as parent and child, a parent in one row and a child in another row. For example, in the table below, zip code 01010 is within Bangor, which is within Maine, which is within East. A <NULL> parent value defines the corresponding child value as the highest level in the branch, in this case, USA.

GEO_CHILD | GEO_PARENT
USA | <NULL>
East | USA
Maine | East
Bangor | Maine
01010 | Bangor

Whenever Essbase Integration Server finds a <NULL> parent, the child value is considered the parent of a new branch. Thus, the data from the table above generates the hierarchy shown below:


About Shared Members in Recursive Tables

Related Topics

When Essbase Integration Server finds a child with two parents, one parent in each of two records, it creates a shared member. In Essbase, a shared member is a member that shares storage space with another member of the same name. The use of shared members prevents Essbase from making extra calculations on members that are in more than one location in the outline.

For example, the table shown below generates an Essbase outline hierarchy where 100-20 (Diet Cola) is a shared member of Diet because it is already a child of 100 (Colas), and 200-20 is a shared member of Diet because it is already a child of 200 (Fruit Drinks).

Child Column | Child Description | Parent Column
100-10 | Regular Cola | 100
100-20 | Diet Cola | 100
100-30 | Caffeine-free Cola | 100
200-10 | Regular Fruit-Orange | 200
200-20 | Diet Fruit-Orange | 200
200-30 | Regular Fruit Lime | 200
100 | Colas | <NULL>
200 | Fruit Drinks | <NULL>
Diet | Diet Drinks | <NULL>
100-20 |  | Diet
200-20 |  | Diet

The outline hierarchy based on the table above includes the shared members 100-20 and 200-20 under Diet, as shown in the following outline segment:

If a source database contains recursive tables, you need to join each recursive table to itself (self-join) to access any related information and to consolidate the data properly. See Defining a Recursive Table.

Note: If you are working with a metaoutline that supports duplicate member names, the child column in a recursive hierarchy is treated as the key column by default. For generational hierarchies, you must provide a member key on columns that could generate shared members, as described in Specifying Key Columns for Duplicate Members.


About the Accounts Dimension - Overview

About the Accounts Dimension – Overview

The accounts dimension consists of columns that contain measures. A measure is a numeric data value that you track over time, such as sales, discounts, or commissions. In Essbase, accounts is a dimension type that makes accounting intelligence available during online analytical processing.

An OLAP model is not complete without at least one column from the data source that contains a measure. If you add more than a single measure to the fact table, you must create an accounts dimension in the OLAP model.

If you have a non-financial application or, for any reason, have only a single measure to be analyzed and reported, you do not need to create an accounts dimension. First you add the appropriate measure to the fact table, then you use the measure from the fact table to add a single measure to the metaoutline that you create from the OLAP model.

Caution: If the source database stores measure values and time values in the same columns, you must use a non-standard method to create the OLAP model accounts dimension. This situation is common in some applications, such as general ledger and legacy systems. See Switching the Time and Accounts Dimensions in the OLAP Model.


About the Fact Table - Overview

About the Fact Table – Overview

The fact table is made up of one or more source tables and contains a compound key that uniquely identifies the records that it contains. The compound key consists of primary and foreign keys and links to each dimension table of the OLAP model. The dimension tables contain columns of related information from the source tables.

For example, you might store product ID and geographic information in two or more tables in the source database. Source tables can be used to create Product and Market dimensions in the OLAP model. You join the dimensions to the fact table, either manually or through automatic joins. When you create a new dimension, Essbase Integration Server creates an automatic join whenever it detects a natural join relationship. A natural join exists between two tables when a primary key of one table is identified as a foreign key in the other table.

Bear in mind that Integration Server does not create additional structures for the relational data that you use when creating an OLAP model. Rather, it reorganizes the existing data into a format that you can use to create a metaoutline.

Why a Fact Table Is Required

Every OLAP model that you create must contain a fact table. The fact table contains the values that are later aggregated and reported by Essbase. The first source table that you drag to the right frame automatically becomes the fact table. Because of its relationship to each dimension that you define, the fact table becomes the hub (the center) of the OLAP model. After you create the fact table, you can add other source tables to it.

How the Fact Table and the Accounts Dimension Are Related

Any columns that you add to the fact table are added automatically to the accounts dimension. Likewise, columns that you add to the accounts dimension are added automatically to the fact table. All measures values that you want reported are referenced through the fact table and through the accounts dimension, which is an exact copy of the fact table.

If, in the relational data source, you have only one column containing measures, you can store the column in the fact table, and you do not need to create an accounts dimension. For more information about the accounts dimension, see About the Accounts Dimension.


About the Time Dimension - Overview

About the Time Dimension – Overview

In an Essbase database, you use the dimension tagged as time to describe how often you collect and update data. You can use time dimension members, such as Year and Quarter, to report yearly and quarterly totals for members of other dimensions; for example, total sales of Diet Cola in Boston for the first quarter.

In an OLAP model, you create a dimension tagged as time to provide a time structure from which to create a specific time dimension in an associated metaoutline. You cannot create a time dimension in a metaoutline if you did not create, in the associated OLAP model, a dimension that contains a time value.

It is not mandatory to include a time dimension in an OLAP model. However, if an application requires analysis to be performed on a time basis, such as examining trends over time or making seasonal sales comparisons, you should include a time dimension. You can create only one time dimension in an OLAP model, but you can create as many time dimension hierarchies, within that single time dimension, as are necessary to meet reporting requirements.

In an OLAP model, the time dimension includes the following elements:

  • One or more time-related columns joined to the fact table.

  • A time-related column for every time dimension member that you want to report on in Essbase; for example, a separate column for year, for quarter, and for month. You use these time-related columns in an associated metaoutline to define members of the time dimension.

  • As needed, one or more hierarchies that define how to roll up member data in the time dimension. For example, a time hierarchy can include Month, Quarter, and Year members whereby monthly totals roll up to quarterly totals and quarterly totals roll up to yearly totals. Creating hierarchies in an OLAP model provides a multilevel structure that you can view when creating dimensions in metaoutlines.

You may have specific reporting and analysis requirements that are based on specialized calendar years, such as in a merchant calendar. For more information on how to prepare the relational data source to meet such requirements, see "Creating Time and Accounts Dimensions" in the Essbase Integration Services Data Preparation Guide.


Adding Source Tables Directly to Dimension Tables - Overview

Adding Source Tables Directly to Dimension Tables – Overview

When you add a new table directly to a dimension table, you combine the two tables. Combining tables offers both advantages and disadvantages.

In both the OLAP model and the metaoutline, the original sources of columns are not as clear as they would be if the added table were part of a dimension branch; however, the OLAP model contains fewer tables. In the metaoutline, you can add columns in any order (that is, you are not required to duplicate the OLAP model hierarchy). For example, in the graphic below, the order of the ADDITIONS and OPENINGINVENTORY columns in the OLAP model are different from the order of the ADDITIONS and OPENINGINVENTORY columns of the metaoutline.

In an OLAP model

 In a metaoutline

When you add a new source table directly to an existing dimension table, you must join columns between the two tables. If a column name is used in both tables, the name of the added table is prefixed to the duplicate name from the added table. To add tables to the fact table, the time dimension, and the accounts dimension, you must combine tables; you cannot create dimension branches.


Adding Source Tables to Create Dimension Branches - Overview

Adding Source Tables to Create Dimension Branches – Overview

The use of dimension branches offers some advantages, as shown in the illustration below. In both the OLAP model and the metaoutline, it is easy to tell to which source table each column belongs. In the metaoutline, it is easy to add columns in the required order—the hierarchical order defined by the dimension branch of the OLAP model. For example, in the metaoutline below, the columns of the FAMILY table must be added before and positioned above the columns of the PRODUCT table.

In an OLAP model | In a metaoutline

When you join a source table to an existing dimension table to create a dimension branch, all columns of the table are added to the dimension. Each dimension table in the dimension branch displays the original dimension table name.

You cannot create dimension branches for the fact table, for the time dimension, or for the accounts dimension. To use multiple tables for the time and accounts dimensions and for the fact table, add the tables directly (see Adding Source Tables Directly to Dimension Tables).


Attribute Types

Attribute dimensions have a text, numeric, Boolean, or date type that enables different functions for grouping, selecting, or calculating data.

The default attribute type is text. Text attributes enable the functionality of the basic attribute member selection and attribute calculations. This includes AND, OR, NOT, <, >, and = comparisons.

The names of level 0 members of numeric attribute dimensions are numeric values. You can perform AND, OR, NOT, <, >, and = comparisons on numeric attributes, and you can include the names (values) of numeric attribute dimension members in calculations.

You can also associate numeric attributes with ranges of base dimension values; for example, you can create market population groupings such as states with populations of 12,000,000 or less in one group and states with a population greater than 12,000,000 in another group.

All Boolean attribute dimensions in a database contain only two members. The member names must match the settings for the database; for example, True and False.

You can use date attributes to specify the date format, either month-day-year or day-month-year, and to sequence information accordingly. You can perform AND, OR, NOT, <, >, and = comparisons on date attributes, and you can compare dates in a calculation.


Changing the Primary Data Source That Contains User-Defined Tables-Example

Changing the Primary Data Source That Contains User-Defined Tables – Example

Suppose you create an OLAP model in your test environment while connected to TBC as the primary relational data source and logged on as user TBC. Now add a user-defined table called "MySales." In the left frame of the OLAP Model main window, the primary data source would show "TBC" and the user-defined table "TBC.MySales" would be listed under the User-Defined Tables icon, as shown here:

Later, in the production environment, you want to access the same OLAP model but connect to an alternative primary data source (for example, tbc2). In the left frame of the OLAP model, the primary data source shows "tbc2." The user-defined table, however, is still listed as "TBC.MySales" under the User-Defined Tables icon, as shown here:


Changing the Primary Data Source That Contains User-Defined Tables - Overview

Changing the Primary Data Source That Contains User-Defined Tables – Overview

When working with an OLAP model that employs user-defined tables, you can access an alternative data source as the primary data source for the model. Essbase Integration Server will verify this OLAP model as valid if the primary data source contains the same tables and columns referenced in the user-defined tables as well as the source tables, and as long as there are no other problems with the model.

The ability to change the primary data source attached to an OLAP model enables you to move models that contain user-defined tables easily from one environment to another. For example, an OLAP model containing user-defined tables created in a testing environment and accessing a testing environment data source can be moved to a production environment and access a differently-named production data source without altering or re-creating the definitions of the user-defined tables. As long as the alternative data source contains the tables and columns referenced in the user-defined table definition, the tables will work properly in the OLAP model.


Connecting to Integration Server and OLAP Metadata Catalog - Overview

To use Essbase Integration Services Console to create and modify OLAP models and metaoutlines, you must first connect the console to Essbase Integration Server and to the OLAP Metadata Catalog where you want to store the OLAP models and metaoutlines that you work with.

Note: Relational database users and flat files users must store their OLAP Metadata Catalogs in a relational database.

When making the server and catalog connection, you must also select the code page of the language you want to use while running the console. If you want to load members and data, you must connect to Essbase.

After you have connected to the Integration Server, OLAP Metadata Catalog, and Essbase Server, and selected a code page, you use the Data Source dialog box to connect to one or more data sources.

Servers and data sources are typically located on computers in your network.


Connecting to Data Sources - Overview

Connecting to Data Sources – Overview

When you first access Essbase Integration Services Console to create or to edit an OLAP model or metaoutline, you specify the appropriate OLAP Metadata Catalog in the Login dialog box.

After you have connected to the appropriate OLAP Metadata Catalog, the Welcome dialog box is displayed. When you select to create a new OLAP model or metaoutline, or when you select an existing OLAP model or metaoutline, the Data Source dialog box is displayed for you to specify the database that contains the required data source. You also use the Data Source dialog box to access additional data sources that you may need to build OLAP models and metaoutlines (use the Connections > Add Data Sources command).


Creating User-Defined Tables - Example

Creating User-Defined Tables – Example

In Essbase Integration Services Console, you create a user-defined table, which is similar to an RDBMS view, by typing SQL syntax in the Table Definition text box of the User-Defined Table Definition dialog box. The following examples show the difference between the SQL statement you use in an RDBMS and the SQL statement you use in Integration Services.

Using tables from the TBC database, the following example illustrates an SQL statement to create a view in an RDBMS:

CREATE VIEW
View_Prod_Proddim
(Caffeinated, FamilyID, Ounces, PackageType,
ProductID, SKU, Family) as
SELECT
a.caffeinated,
a.familyid, a.ounces, a.pkgtype,
a.productid, a.sku, b.family
FROM product a, productdim b
WHERE a.sku=b.sku

Using the example above, in Integration Services, when you create a user-defined table, the SQL syntax does not include the CREATE VIEW portion of the SQL statement. Your SQL table definition syntax begins with the portion of the statement starting from SELECT. For example:

SELECT
a.caffeinated,
a.familyid, a.ounces, a.pkgtype,
a.productid, a.sku, b.family
FROM product a, productdim b
WHERE a.sku=b.sku


Creating User-Defined Tables - Overview

Creating User-Defined Tables – Overview

If your database schema is not appropriately structured for Essbase Integration Services, the schema will not function correctly during the creation of OLAP models unless a virtual view of the schema is generated. You can generate this virtual view by creating your own user-defined tables, which function like an RDBMS view, enabling you to create and edit OLAP models without altering your relational schema.

Essbase Integration Server verifies the SQL commands you use to create a user-defined table and enables you to establish a virtual view of the database schema.

Notes:

  • You can create user-defined tables for relational data sources.

  • Essbase Integration Services does not support user-defined tables created in Sybase or Informix.


Defining Transformations for Numeric Columns - Overview

Defining Transformations for Numeric Columns – Overview

Numeric column transformations are the most common transformations for columns of the accounts dimension table. A numeric column transformation is the result of a calculation that involves a source column and a numeric column that is located in the same dimension.

For example, you can add, subtract, multiply, or divide the value of a numeric source column from the accounts dimension table by the value of another numeric column from the accounts dimension table to derive the value that you want to report.

For example, assume that you want to define a transformation rule for a new column named NETPRICE. NETPRICE can be derived from the SALES and DISCOUNTS columns of the TBC sample fact table called TBC.FACT_TABLE.

For the transformation rule, Essbase Integration Server uses the source column, in this case, TBC.FACT_TABLE.SALES, as the first operand in the calculation.

You specify the subtract operator (-) and the other operand, the TBC.FACT_TABLE.DISCOUNTS column. Thus, the calculation for NETPRICE is as follows:

TBC.FACT_TABLE.SALES - TBC.FACT_TABLE.DISCOUNTS

As a result, for a row with a value of 15.80 for SALES and 3.50 for DISCOUNTS, the value of NETPRICE is 12.30.

To define the transformation rule for a user-defined column, see Creating User-Defined Columns.

To use a pass-through transformation, see Defining Pass-Through Transformations .


Defining Transformations that Use Concatenations to Create String Columns - Overview

Defining Transformations that Use Concatenations to Create String Columns – Overview

You can define the data in a string column by concatenating one or more string columns, provided that all columns are from the same dimension. For example, assume that you want to create a single column, COLA_ID_DESCRIPTION, that contains values from both the PRODUCT_ID and the PRODUCT_DESC columns. When the value of the PRODUCT_ID column is 100-10 and the value of the PRODUCT_DESC column is KoolKola, the value of the concatenated column becomes 100-10-KoolKola.

To define a transformation rule for a user-defined column, see Creating User-Defined Columns

To use a pass-through transformation, see Defining Pass-Through Transformations.


Defining Transformations that Use Substrings to Create String Columns - Overview

Defining Transformations that Use Substrings to Create String Columns – Overview

You can define a string column as a substring of another column, provided that the two columns are in the same dimension. For example, if only the six characters at the beginning of the PRODCODE column are meaningful to your analysis, you can create a SHORTPROD column that contains only the first six characters. As a result, when the value of PRODCODE is 100 10 C001 S0003, the value of SHORTPROD is 100 10.

To define a transformation rule for a user-defined column, see Creating User-Defined Columns

To use a pass-through transformation, see Defining Pass-Through Transformations.


Defining Transformations for Datetime Columns - Overview

Defining Transformations for Datetime Columns – Overview

For a time-related column, select a transformation rule that maps the content of the new column to the appropriate portion of a date, such as the year or month. For more information about columns in the time dimension, see "Creating Time and Accounts Dimensions" in the Essbase Integration Services Data Preparation Guide.

Typically, time-related columns have a datetime data type. Time-related data can also be stored with a string data type, with or without formatting. For example, a string column can contain the date formatted with slashes, 03/24/2000, or without slashes, 03242000. Time-related data can also be stored with a numeric data type, without formatting; for example, 20000324.

In most cases, you work with columns with a datetime data type in the time dimension table and in the fact table. If you include a column with a datetime data type in another type of dimension, you can use the column as a join column or as a column to be used in drill-through spreadsheet reports (see About Joins and Defining Drill-Through Columns.

If your fiscal year starts at any month other than January, you must use a user-defined calendar to tie measures data to time-related columns such as month or quarter. For information, see Designing a User-Defined Calendar.

To define the transformation rule for a user-defined column, see Creating User-Defined Columns.

To use a pass-through transformation, see Defining Pass-Through Transformations.


Determining If a User-Defined Calendar Is Needed

Related Topics

Essbase Integration Server can map a date in a source column to a day, week, month, quarter, or year time period in the time dimension. If you need to analyze data by other time periods or if you use a calendar different from the standard Gregorian calendar, create a user-defined calendar in the source data. A user-defined calendar is a source table in a relational database that maps a business calendar to the date information associated with the measures data. If you answer "Yes" to any of the following questions, create a relational source table containing a user-defined calendar to perform the required mapping.

  • Does the fiscal year start on a date other than January 1?

  • Does the time dimension include a member for a period that has no matching column in the source data for the fact table? For example, do you want to measure by sales season, but no sales season column exists in the source data for the fact table?

  • Do you measure business cycles in periods other than the standard Gregorian calendar months? For example, instead of the standard 12 months to the year, is your year measured by 13 four-week periods?


Differences Between Attribute Dimensions and Standard Dimensions

In general, attribute dimensions and their members are similar to standard dimensions and members:

  • You can provide aliases and member comments for attribute dimensions.

  • Attribute dimensions can include hierarchies.

  • You can name generations and levels.

  • You can perform spreadsheet operations on attribute dimensions and members.


How Joins are Displayed in an OLAP Model

A join line between two tables indicates a join relationship. As shown in below, a solid join line indicates a fully-defined join; that is, the join columns of both dimension tables are specified (see About Joins). A broken line indicates that the dimension tables are joined but that the join columns are not specified.

Note: If every dimension table is not joined either to another dimension table or to the fact table, the OLAP model is invalid. You can save an invalid OLAP model, but you cannot use an invalid OLAP model to create a metaoutline.

Any dimension table that you join to the fact table becomes a dimension in the OLAP model. Any dimension table that you join to another dimension table becomes part of the dimension of that dimension table. For more information, see Adding Source Tables to Create Dimension Branches.

To see the properties of a join, select the join line, right-click, and select Properties. To see the join columns in a join, move the cursor over the join line. A tool tip displays the names of the table and join columns. To hide the tool tip, deselect View > Enable Join Tool Tips.

Note: If a join relationship exists between tables in the external data source, the columns join automatically when you add a new source table to an existing dimension table in the OLAP model.


Reconnecting When Connections Are Lost - Overview

Reconnecting When Connections Are Lost – Overview

Essbase Integration Services prevents the loss of unsaved changes to an OLAP model or metaoutline if you are disconnected from Integration Server and OLAP Metadata Catalog. This protection allows you to reconnect without losing any unsaved work. Should a connection be lost, Integration Services enables you to reconnect to the Integration Server and OLAP Metadata Catalog.


Saving OLAP Models - Overview

Saving OLAP Models – Overview

You must have a valid OLAP model to create a metaoutline. You then use the metaoutline to create an outline and load data into an Essbase database.

Essbase Integration Services automatically performs a validation check of the OLAP model each time you save it. If you prefer, you can manually validate an OLAP model before saving it. You can save an OLAP model that does not pass validation, but you cannot use it to create a metaoutline.


Sorting Data in Hierarchies - Overview

Sorting Data in Hierarchies – Overview

You can specify a sequence for sorting the values of any column. You can select ascending, descending, or none (no sort order). The default sort order is none. If you select ascending or descending, data is retrieved faster than if you select none for no sort order. If you make a change to a sort in an OLAP model hierarchy, the changes do not carry through to metaoutlines previously created from that OLAP model.

Note: While retrieving data, Essbase Integration Server sorts columns after it filters and before it performs transformations. For example, if the MONTH column contains numeric values for the month (01, 02, 03, and so on) and you specify a transformation that substitutes month abbreviations, (Jan, Feb, Mar, and so on), Essbase Integration Server sorts columns by their pre-transformation numeric values. April totals are displayed after March totals because 04 sorts after 03.


Transforming Data in Hierarchies - Overview

Transforming Data in Hierarchies – Overview

After you create a dimension hierarchy, you can change the way the data values of the hierarchy are represented when reported by Essbase. For example, perhaps the date values of the OLAP model hierarchy are numeric and you want the corresponding Essbase members to be alphabetic.

A transformation that you define on a hierarchy in an OLAP model is used to create first a metaoutline and then an Essbase outline. If you change a transformation in an OLAP model, the change does not carry through to metaoutlines previously created from the OLAP model. Essbase Integration Server performs filters and sorts before transformations.

For more information about transforming data in hierarchies, see:

  • Replacing Data

  • Prefixing and Suffixing Column Values


Understanding Exclusive Access

You can save an OLAP model even if multiple users are reading it by selecting Exclusive Access in the Existing tab or the Recent tab of the Welcome dialog box. Opening an existing OLAP model with Exclusive Access guarantees that no other user can modify the OLAP model by placing write-lock privileges on the model. This option allows you to do both of the following:

  • You can save an existing OLAP model.

  • You can modify and save an existing OLAP model. If you open a read-only OLAP model and no other user has Exclusive Access, you can save the OLAP model to the same name.

If Exclusive Access is not selectable, another user has write-lock privileges, and you have read-only privileges.

If you want to modify and save an OLAP model but have read-only privileges, use File > Save As to save the OLAP model to a different name. See Saving an OLAP Model Manually. Also see the Essbase Integration Services System Administrator’s Guide for information on unlocking OLAP models.

Note: You cannot save an OLAP model if other users are reading it. Save it later or use File > Save As to save the model with a different name.


Updating an OLAP Model or Metaoutline After RDBMS Changes-Overview

Updating an OLAP Model or Metaoutline After RDBMS Changes—Overview

Sometimes new tables or columns are added to your relational database management system. These new tables or columns are not automatically reflected in existing OLAP models and metaoutlines. If you want to use new tables or columns in existing OLAP models and metaoutlines, you must add them. The method for adding new tables or columns to existing OLAP models and metaoutlines is similar to the methods you already use to edit them.

In summary, when you first open an OLAP model after an RDBMS change, you will see the new tables or columns in the left frame of the OLAP Model main window. You drag and drop those tables or columns that you want to add to your existing OLAP model. Next, edit the associated metaoutline(s). In the OLAP Metaoutline main window, drag and drop the new OLAP model elements that you want to use (dimensions, members, or hierarchies) from the left frame of the OLAP Metaoutline main window to the right.

Note: Be sure to validate updated OLAP models and metaoutlines by using the File > Verify command.


Dynamic Time Series

You can calculate period-to-date values dynamically in the Essbase database. When you create a metaoutline you can enable Dynamic Time Series members in the Time dimension by selecting the Dynamic Time Series tab of the Dimension Properties dialog box. For example, you can calculate the quarter-to-date values dynamically by setting the QUARTER member of the TIME dimension as Dynamic Time Series.

Note: You cannot associate Dynamic Time Series members with level 0 members of the time dimension. They may be associated with any other level.


Save New OLAP Model Dialog Box

Use the Save New OLAP Model dialog box to save an OLAP model in the appropriate OLAP Metadata Catalog. When you save an OLAP model, Essbase Integration Services automatically performs a validation check.

To display this dialog box, create a new OLAP model and select File > Save.

Dialog Box Items

OLAP Model Name text box

Displays the name of the OLAP model you are saving. If you are saving the OLAP model for the first time, Integration Services gives it a default name, for example, OLAP_Model1. Type over this name to change it. See Naming Conventions .

Existing OLAP Models list box

Displays the OLAP models stored in the OLAP Metadata Catalog to which you are connected.

Security drop-down list box

Select who can access the OLAP model. Options are:

  • Allow read/write access for other users: Gives other users permission both to read and to write to the OLAP model. This setting is the default.

  • Allow read access for other users: Gives other users permission to read the OLAP model but not to write to it; that is, another user cannot save changes to the OLAP model.

  • Disallow all access for other users: Other users can neither read nor write to the OLAP model. Only you can read it or write to it.

Description text box

Type a description of the OLAP model. The description can contain up to 255 characters. This description is displayed in the Delete an OLAP Model/Metaoutline and the OLAP Model Properties – General tab dialog boxes.

Related Topics


Select Values From Dialog Box

Use the Select Values From dialog box to display all the current values for the selected column. Essbase Integration Server retrieves these values directly from the data source. If the data source contains many values, Integration Server confirms that you want to view them all.

Note: Integration Server does not display sample outlines if they contain more than 32,767 members.

To display this dialog box from an OLAP model, right-click a dimension and select the Hierarchies tab. Select a hierarchy to filter and click Edit, or click Add to create a new hierarchy. In the Edit Hierarchy dialog box, click the [image] in front of a hierarchy name in the right list box, select a column, and click Filter. The Filters tab is displayed by default. Select a column in the Column drop-down list box, an operator in the Operator drop-down list box, and then click [image] to display the Select Values From dialog box.

Related Procedures

Creating Hierarchies Manually

Creating Filters in Hierarchies

Dialog Box Items

Value list box

Displays all current values for the column specified in the Column drop-down list box. To select multiple, non-contiguous values, hold down the Ctrl key and click each value. To select a contiguous range of values, hold down the Shift key and click each value

You can select one or more values to be eliminated during data retrieval if you have selected in or not in as the operator on the Edit Filter dialog box.

Related Topics


Add New Column to Table Dialog Box

Use the Add New Column to Table dialog box to add columns to existing dimension tables. You must base the new column on an existing column in the same dimension table.

To display this dialog box, in an OLAP model, right-click the table name in a dimension and select Properties. Click the Columns tab, select an existing column on which to base the new column, and click Add.

Related Procedures

Creating User-Defined Columns

Dialog Box Items

Name text box | Type the name of the new user-defined column. See Naming Conventions .
Physical Table drop-down list box | Select the source table that contains the column on which you want to base the new column.
Physical Column drop-down list box | Select the source column that contains the data values from which you want to derive the values of the new column.
Type list box | Displays the data type for the new column.


Adding Tables or Views to the Time Dimension

Related Topics

When you create a dimension tagged as time in the OLAP model, the dimension may not contain all the time-related columns you need. Other tables or views in the data source database, such as an operations calendar, may contain time-related columns you want to include in the time dimension. To add columns from other source tables, add the table that contains the time-related columns to the time dimension table and hide or delete any unwanted columns.

When adding source tables and views to the time dimension, the following rules apply:

  • To add a table or view to the time dimension, at least one of the tables or views you are adding must have at least one column with date or time information; for example, shipping date.

  • When you add a table or view to the time dimension, the columns you add are not added to the fact table. Likewise, when you add a table or view to the fact table, the columns you add are not added to the time dimension.


Allowing Null Values for Columns when Relational Storage is Enabled - Example

Allowing Null Values for Columns when Relational Storage is Enabled – Example

A simple example of using the Allow Null Value check box can be seen in a Market dimension where the markets are United States, France, and Germany. For this example, the following table is the Market table from the data source database:

Market table

Country | Region | State | City
US | West | CA | San Jose
US | West | OR | Portland
US | East | NY | Albany
France | Aquitaine |  | Bordeaux
Germany |  | Bavaria | Munich

In this Market table, France has no State value, and Germany has no Region value. If relational storage or Hybrid Analysis is not enabled, Integration Services performs NULL promotion during member load as follows:

  • Promotes Bordeaux to Generation 4

  • Promotes Bavaria to Generation 3 and promotes Munich to Generation 4

If the Market dimension is enabled for relational storage (or if Region and State are enabled for Hybrid Analysis), select the Allow Null Value check box for each of these columns to handle NULL promotion.


Allowing Null Values for Columns when Relational Storage is Enabled - Overview

Allowing Null Values for Columns when Relational Storage is Enabled – Overview

When loading members into an Essbase database, Integration Services uses NULL promotion to handle null values from a data source. NULL promotion means that when a row contains a NULL value, its members are added at the next higher level in the outline.

If you are enabling a metaoutline for relational storage and your data source possibly contains null values, you can enable NULL promotion functionality by selecting the "Allow Null Value" check box in the Column Properties dialog box, General tab. When this check box is selected, relationally stored members are retrieved with NULL promotion taken into account.

"Allow Null Value" is a column property. You apply this property to those columns in the OLAP model that contain NULL values.


Attribute Dimensions

Related Topics

You can use the Essbase Integration Services attribute feature to retrieve and analyze data in Essbase in terms of the individual characteristics, or attributes, of dimensions. For example, you can analyze product profitability based on size or packaging. You can select, aggregate, and report on data based on common features, and you can choose from five consolidation methods: sums, counts, averages, minimums, and maximums.

Attribute types are text, numeric, Boolean, and date. In OLAP models, a standard dimension is any dimension that is not an attribute dimension. When an attribute dimension is associated with a standard dimension, the standard dimension is the base dimension for that attribute dimension. You can associate attributes only with a sparse, standard dimension. You can associate attributes with columns that will later be enabled for Hybrid Analysis in a metaoutline. For other attribute dimension creation guidelines, see Rules for Defining Attribute Dimensions. In metaoutlines, attribute dimensions are positioned below the base dimensions with which they are associated.

Attribute dimensions and members are dynamically calculated, and attribute data is not stored in the Essbase database.

In the sample relational data source (TBC), products have attributes that are characteristics of the products; for example, products have an attribute that describes their packaging. In the metaoutline, the product and its characteristic are seen as two dimensions, the Products dimension, and the PKGTYPE attribute dimension that is associated with it.

After you associate a base dimension with an attribute dimension, you associate members of the base dimension with members of the associated attribute dimension. For example, the Market dimension member Los Angeles could be associated with the 6000000 member of the Population attribute dimension. That makes 6000000 an attribute of Los Angeles.


attribute

A classification of a member in a dimension. You can specify an attribute
to select and group members with a specified attribute 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.


Column Properties Dialog Box

Use the Column Properties dialog box to display general information about columns. You can change the name of a column, change the methods for consolidating column data, and define transformation rules on columns. The dialog box contains the following tabs:

General Tab

Transformation Rule Tab

To display this dialog box, in an OLAP model, right-click a column name in a dimension and select Properties.

Related Topics


Column Properties Dialog Box - Transformation Rule Tab

Column Properties Dialog Box – Transformation Rule Tab

Select the Transformation Rule tab of the Column Properties dialog box to define how the column derives its values from the source column. The contents of this dialog box vary depending on the type of column that you are transforming or the function you have selected to perform the transformation. You can perform functions on the following columns using the Transformation Rule tab:

  • Columns with a DateTime data type

  • Columns with a Numeric data type

  • Columns with a String data type on which you are performing a Substring function

  • Columns with a String data type on which you are performing a Concatenate function

  • Columns on which you are performing a Pass-through function

To display this dialog box, in an OLAP model, right-click a column name in a dimension and select Properties and then select the Transformation Rule tab.

Select one of the following tabs for more help:

Related Topics


Column Properties Dialog Box - General Tab

Column Properties Dialog Box – General Tab

Select the General tab of the Column Properties dialog box to rename a column or to view its properties.

If the column is in the accounts dimension and the column has a numeric data type, the dialog box also contains an Aggregation drop-down list.

To display this dialog box, in an OLAP model, right-click a column name in a dimension and select Properties. The General tab is displayed by default.

Related Procedures

Renaming Columns

Select one of the following tabs for more help:

Dialog Box Items

Name text box

To rename the column, type the new column name in the Name text box.

See Naming Conventions .

Physical Table text box

Displays the name of the source table that contains the column on which the new column is based.

Physical Column text box

Displays the name of the source column on which the column is based.

Type drop-down list box

Displays the data type for the column.

Aggregation drop-down list box

Select the function to use to aggregate the items in the column:

  • SUM: adds the data for each reporting level. SUM is the default selection.

  • <NONE>: prevents the values in the column from being available as a measure to the metaoutline. Typically, a join column has <NONE> as its aggregation option.

  • AVG: reports the average value computed from the data in the columns consolidated to the level being viewed.

  • MIN: reports the minimum value computed from the data in the columns consolidated to the level being viewed.

  • MAX: reports the maximum value computed from the data in the columns consolidated to the level being viewed.

  • COUNT: reports the number of the source data cells in that column that are not NULL, consolidated to the level being viewed.

This list box is available only for Numeric columns in the fact table and the accounts dimension table.

Note: The aggregate function is defined at the measures dimension. If a measures dimension contains an aggregate function not supported in Integration Services, the aggregate function is mapped to <NONE>.

Allow Null Value check box

Select this check box to allow null values to be loaded during an outline build.

When a metaoutline is enabled for relational storage or members are enabled for Hybrid Analysis, and you select this check box, relationally stored members are retrieved with NULL promotion taken into account.

When there are no members tagged for relational storage (Advanced Relational Access or Hybrid Analysis), this check box is ignored.

Related Topics


Create New Dimension Dialog Box

Use the Create New Dimension dialog box to name a new dimension and define the dimension type as accounts, time, or standard. Standard is the default.

To display this dialog box, drag a table from the left frame of the OLAP Model main window into the OLAP model in the right frame.

Dialog Box Items

Name text box | Type the name of the new dimension. If you want to rename the table you are using to create the dimension, use the Table Properties dialog box. See Naming Conventions .
Dimension Type drop-down list box | Select the dimension type, such as accounts, time, or standard (standard is the default). You can have only one dimension tagged as accounts and one dimension tagged as time for each OLAP model.
Current Dimensions list box | Displays all dimensions currently defined in the OLAP model.

Related Topics


Create New Table Dialog Box

In some instances, you may be required to drag a data source table a second time into an OLAP model to create a dimension. For example, some applications, such as general ledger applications, store time values in columns and measures values in rows. For these databases, you must switch the time and accounts dimensions in the OLAP model and then set the dimension types correctly in the associated metaoutline. To do this, you must drag a table a second time from the left frame into the OLAP model, and then rename the table using the Create New Table dialog box.

To display this dialog box, drag a table a second time from the left frame into the OLAP model.

Related Procedures

Creating Dimensions Manually

Switching the Time and Accounts Dimensions in the OLAP Model

Dialog Box Items

Name text box | Type the new name of the table.
Current Tables list box | Displays the table names currently used in the OLAP model.


Creating an OLAP Metadata Catalog Automatically - Overview

Creating an OLAP Metadata Catalog Automatically – Overview

When you first start the Essbase Integration Services Console after installing Integration Services, the system displays the OLAP Metadata Catalog Setup dialog box. Whether your data resides in a relational database or flat file, you must create an OLAP Metadata Catalog in a relational database.

For first-time installations, you can click a button in this dialog box to create the OLAP Metadata Catalog automatically. For existing users, if the system detects a previous version of the catalog, your existing catalog is automatically updated to the current version.

Note: If you want to create a Unicode OLAP Metadata Catalog, you must manually create it by running the scripts provided, as described in the chapter on OLAP Metadata Catalog setup in the Essbase Integration Services System Administrator’s Guide.


Creating Dimensions Automatically - Overview

Creating Dimensions Automatically – Overview

After you have created the fact table, you can choose to have related standard dimensions created automatically.

A quick-access link to the automatic detection option for creating the related standard dimensions is provided in the Intelligent Help. In addition, a Tools menu option (Tools > Create Dimensions) is available from the OLAP Model main window.

For automatic detection of standard dimensions, the system begins by looking for all tables joined to the fact table with primary-foreign key relationships. Next, it searches for all tables joined to the previously detected tables and adds them to the OLAP model schema. In a database where no primary-foreign key relationships are defined, the system begins by searching for all tables joined to the fact table and then uses the column name and type to determine join keys. If two tables have the same column names and types, the system assumes that the two tables are joined on that column. To reduce the possibility of creating invalid dimensions, the system does not search for join keys in any tables that are not joined to the fact table.

Selecting the standard dimension auto-detect option initiates the creation process. A message box is displayed listing the names of the standard dimensions that will be created. Click OK to create the suggested standard dimensions.

You can also click Cancel to stop the dimension creation process.


Creating Hierarchies Automatically - Overview

Creating Hierarchies Automatically – Overview

After you have created the fact table and the related standard dimensions, you can choose to create hierarchies automatically.

A quick-access link to the automatic detection option for creating hierarchies is provided in the inline text of the Intelligent Help. In addition, a Tools menu option (Tools > Create Hierarchies) is available from the OLAP Model main window.

When you select the automatic hierarchy detection option, the system searches for hierarchies that exist in the OLAP Metadata Catalog. It then displays the detected hierarchies in a hierarchical tree format that includes the dimension name, the hierarchy names, and the associated member names. You can choose to create a single hierarchy or multiple hierarchies.

Note: If you are accessing a flat file data source, do not use this functionality. For a flat file data source, you create hierarchies manually .


Creating the Fact Table Automatically - Overview

Creating the Fact Table Automatically – Overview

After you click the OLAP Model icon for first-time model creation and log in to the appropriate data source, you have the option of creating the OLAP model fact table and the associated Time and Accounts dimensions automatically.

A quick-access link to the automatic detection option for creating a fact table and related Time and Accounts dimensions is provided in the inline text of the Intelligent Help. In addition, a Tools menu option (Tools > Create Fact Table) is available from the OLAP Model main window.

When you select the fact table auto-detect option, the system displays the Create Fact Table dialog box. This dialog box shows the fact table candidates detected in the current data source to which you are connected, along with any existing fact tables in the OLAP Metadata Catalog that are present in the current data source. You can select from either a list of fact tables in existing OLAP models or from a list of potential fact table candidates to define the fact table to use.

Check boxes in the Create Fact Table dialog box enable you to create the Time and Accounts dimensions automatically. If you prefer, you can create the Time and Accounts dimensions manually at a later time.


Creating the Sample Application Automatically - Overview

Creating the Sample Application Automatically – Overview

After the OLAP Metadata Catalog has been automatically created or updated from a previous version, you can access the Sample Application Setup dialog box to create The Beverage Company (TBC) sample application automatically on a relational data source. The sample application includes the sample data (TBC), sample OLAP model (TBC Model), and sample metaoutline (TBC Metaoutline).

Notes:

  • If you plan to use the Hybrid Analysis portion of the sample application, you must follow the setup procedures in the Essbase Integration Services System Administrator’s Guide.

  • If you plan to use the Unicode portion of the sample application, you must follow the setup procedures in the Essbase Integration Services System Administrator’s Guide.


Creating User-Defined Columns - Overview

Creating User-Defined Columns – Overview

User-defined columns are columns that you create, based on existing columns in the source database. You may want to add user-defined columns to dimension tables for several reasons. For example, assume that you want to split out a specific portion of the PRODCODE column. To accomplish this, create a SHORTPROD column from the first six characters of the PRODCODE column. As a result, when the value of PRODCODE is 100-10-C001-S0003, the value of SHORTPROD is 100-10.

Another reason to add user-defined columns is to use the columns as levels in a time hierarchy. If you want monthly and quarterly aggregations in the Essbase database, you need monthly and quarterly columns in the dimension table for the time dimension. You can add the columns as user-defined columns and derive their values from a single transaction date column contained in the same dimension table.

You must base a user-defined column on existing columns in a dimension table. A user-defined column assumes a numeric, string, or datetime data type, based on the data type of the source column, as discussed in About Column Data Types. You cannot change the data type of a column.

Adding user-defined columns to a dimension table includes two major steps:

  • Selecting an existing column as the source for the new column and naming the new column

  • Applying transformation rules that determine how the data values of the user-defined column are derived from the source column


Data Source Dialog Box

Use the Data Source dialog box to connect to one or more appropriate data sources.

The Data Source dialog box is displayed each time you create or modify an OLAP model or metaoutline. To create or edit OLAP models and metaoutlines, you connect to the appropriate data sources and to the OLAP Metadata Catalog where you want to store the OLAP models and metaoutlines. You also select the code page of the language you want to use during the current Integration Services Console session

When an OLAP model is based on multiple data sources, the data source from which the fact table is created becomes the primary data source. Any additional data sources to which the OLAP model is connected are secondary data sources.

Note: When you save an OLAP model, the primary and secondary data source information is set to the values used in the current connection. Also, you can choose to save password information for secondary data sources in the OLAP Model Properties dialog box.

Related Procedures

Connecting to Data Sources

Changing the Primary Data Source for Models That Contain User-Defined Tables

Dialog Box Items

Data Source drop-down list box

Select from the available data sources in the Data Source drop-down list or enter a data source name. If you do not know the name of the data source, contact the system administrator.

If you are using the Oracle Call Interface (OCI) direct connection, be sure to prefix the Net Service Name, found in the tnsnames.ora file, or the Schema Name if you are using onames, with Oracle by typing Oracle: and then the Net Service Name or Schema Name. For example:

Oracle:TBC

See the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide for more information.

Code Page drop-down list box

Select the code page of the language you want to use during the current Integration Services Console session

The code page is a portion of the locale, which identifies the local language and cultural conventions such as the formatting of currency and dates and the sort order of data.

User Name drop-down list box

Select from the available user names in the User Name list or enter your user name. If you do not know your user name, contact the system administrator.

Note: If you are using a flat file data source, no user name is required.

Password text box

Type your password. If you do not know your password, contact the system administrator.

Note: If you are using a flat file data source, no password is required.

The following button is displayed when you first connect to a data source to create or edit an OLAP model or metaoutline:

OK button

Click to confirm connection to the selected data source.

The following buttons are displayed when you connect to additional data sources while creating or editing an OLAP model or metaoutline:

Connect button

Click to connect to an additional selected data source.

Note: If you have selected the Save secondary data sources’ password check box in the OLAP Model Properties dialog box, the secondary data sources will automatically be connected upon opening the OLAP model or metaoutline.

Close button

Click when finished connecting to all the appropriate data sources.


Date Hierarchy Dialog Box

Use the Date Hierarchy dialog box to create pre-defined time hierarchies automatically from a single column in the data source. For example, you can select a column containing a timestamp, such as 200012290733 and use the values of the timestamp to create a hierarchy that includes the year (2000), quarter (Qtr4), and month (December).

To display this dialog box, right-click the dimension table that contains the column you want to use to create a date hierarchy and select Properties, and then select the Columns tab. Select the appropriate date or time column in the Columns list box and click Date-Hierarchy.

Dialog Box Items

Hierarchy list box

After selecting a date column in the Table Properties dialog box on which to base a time hierarchy, select the hierarchy to create (for example, Year, Quarter, Month).

Note: If the data source is a flat file, the default Date Hierarchies provided by Integration Services do not function with month names. If you want to use the Date Hierarchy functionality, define months as numerical values.


Defining Drill-Through Columns - Overview

Defining Drill-Through Columns – Overview

Drill-Through columns are not available as member levels, aliases, or measures in metaoutlines. Therefore, drill-through columns are not available to Essbase databases. However, drill-through columns are available to Essbase database users as additional information in reports.

For example, if you set the PRODDESC column as Drill-Through, PRODDESC is not available to the Essbase database. However, when spreadsheet client users drill through to a report that contains the PRODDESC column, they see the product descriptions as a part of the detail. For more information about drill-through reports, see About Drill-Through.

Use the following guidelines when creating Drill-Through columns:

  • You cannot define columns in the accounts dimension table and the fact table as Drill-Through.

  • You cannot define a column as Drill-Through if the column is used in an associated metaoutline.

  • You cannot define hidden columns as Drill-Through; however, they will not remain hidden once you define them as Drill-Through).

  • The metaoutline in which you are designing a drill-through report cannot contain alternate recursive hierarchies. For example, you cannot insert the same member from a recursive hierarchy into a dimension more than once.

Do not define columns as Drill-Through if you want to use the columns as aliases, member levels, or measures in a metaoutline.


Delete Catalog Locks Dialog Box

Use the Delete Catalog Locks dialog box to clear locks on OLAP models and metaoutlines. This dialog box deletes only your locks; it does not delete locks held by other users.

To display this dialog box, select Connections > OLAP Metadata Catalog > Delete Locks. You do not need to have an OLAP model or metaoutline open to access this dialog box.

Dialog Box Items

Model Name drop-down list box | Select the name of the OLAP model on which the locks are stored.
Model Read Locks option | Select to delete read locks on the OLAP model. Use this setting to read the OLAP model.
Model Write Locks option | Select to delete write locks on the OLAP model. Use this setting to write to the OLAP model.
Metaoutline Name drop-down list box | Select the name of the metaoutline on which the locks are stored.
Metaoutline Read Locks option | Select to delete read locks on the metaoutine. Use this setting to read the metaoutline.
Metaoutline Write Locks option | Select to delete write locks on the metaoutline. Use this setting to write to the metaoutline.


Create Fact Table Dialog Box

Use the Create Fact Table dialog box to automate fact table creation when creating a new OLAP model.

Note: If you are using a flat file data source, this functionality should not be used.

To display this dialog, you must first create a new OLAP model (File > New) and then select Tools > Create Fact Table.

Related Procedures

Creating the Fact Table Automatically

Creating Dimensions Automatically

Creating Hierarchies Automatically

Dialog Box Items

Create Fact Table hierarchical view

Contains two lists of possible fact tables.

Existing Fact Tables: Expand to show any existing fact tables that are present in the current data source and already stored in the OLAP Metadata Catalog.

Tip: To select only one table to use as the fact table, click the table name. To select more than one table to use for the fact table, press and hold down the Ctrl key and click your fact table choices.

Fact Table Candidates: Expand to show the fact tables detected in the current data source to which you are connected.

Create a Time dimension check box

Select to create the Time dimension automatically.

Clear to create the Time dimension manually at a later time.

Create an Accounts dimension check box

Select to create the Accounts dimension automatically.

Clear to create the Accounts dimension manually at a later time.


Create Hierarchies Dialog Box

Use the Create Hierarchies dialog box to automate hierarchy creation when creating a new OLAP model.

To display this dialog, you must first create a new OLAP model (File > New), create the fact table and dimensions, and then select Tools > Create Hierarchies.

Related Procedures

Creating Hierarchies Automatically

Creating the Fact Table Automatically

Creating Dimensions Automatically

Dialog Box Items

Create Hierarchies hierarchical view | Contains the detected hierarchies in a hierarchical format that includes the dimension names, the hierarchy names, and the associated member names. Hierarchy names are depicted by the Hierarchy icon, [image] .
Select All button | Click to select all detected hierarchies for hierarchy creation. Tip: To select only one hierarchy for hierarchy creation, click the hierarchy name. To select more than one but not all hierarchies for hierarchy creation, press and hold down the Ctrl key and click your hierarchy creation choices.
Create button | Click to complete the hierarchy creation process.
Close button | Click to close this dialog box without creating hierarchies.


Dimension Properties Dialog Box

Use the Dimension Properties dialog box to rename a dimension, set its properties, and define its hierarchies. The dialog box contains the following tabs:

General Tab

Hierarchies Tab

To display this dialog box, right-click on a dimension in the OLAP model and select Properties.

Related Topics


Dimension Properties Dialog Box - Hierarchies Tab

Dimension Properties Dialog Box – Hierarchies Tab

Select the Hierarchies tab of the Dimension Properties dialog box to view a list of hierarchies, add new hierarchies, and edit or delete existing hierarchies.

To display this dialog box, right-click on a dimension in the OLAP model and select Properties and then select the Hierarchies tab.

Select one of the following tabs for more help:

Dialog Box Items

Hierarchies list box | Displays the defined hierarchies.
Edit button | Click to edit the selected hierarchy and open the Edit Hierarchy dialog box.
Add button | Click to add a new hierarchy and open the Edit Hierarchy dialog box.
Delete button | Click to delete the selected hierarchy.

Related Topics


Dimension Properties Dialog Box - General Tab

Dimension Properties Dialog Box – General Tab

Select the General tab of the Dimension Properties dialog box to rename a dimension or to view its properties.

To display this dialog box, right-click on a dimension in the OLAP model and select Properties. The General tab is displayed by default.

Select one of the following tabs for more help:

Dialog Box Items

Name text box | Type the new dimension name in the Name text box. See Naming Conventions.
Type text box | Displays the type of the selected dimension: time, accounts, or standard (default).
Data Source drop-down list box | Select to change the data source of a specific dimension. Note: When changing to another data source, the data source to which you are changing must contain the same physical tables and columns as the original data source.

Related Topics


Edit Filter Dialog Box

Use the Edit Filter dialog box to set filters on columns and sort the data values in a column. The dialog box contains the following tabs:

Filters Tab

Ordering Tab

Related Topics


Edit Filter Dialog Box - Filters Tab

Edit Filter Dialog Box – Filters Tab

Select the Filters tab of the Edit Filters dialog box to define filters on columns. Filters determine what data to retrieve from the data source.

To display this dialog box, right-click on a dimension in the OLAP model and select Properties and then select the Hierarchies tab. Choose one of the following options:

  • Select an existing hierarchy and click Edit, select a column to filter from the hierarchy in the Hierarchy list box, and click Filter.

  • Click Add to create a new hierarchy, select and double-click columns to move them to the Hierarchy list box, then select a column in to filter from the Hierarchy list box and click Filter.

Select one of the following tabs for more help:

Dialog Box Items

Column drop-down list box

Select the column to which the condition applies.

Operator drop-down list box

Select one of the following filter operators:

in: Use to select one or more values; for example, Region in United States or Europe selects regions in both the United States and Europe.

not in: Use to eliminate one or more values; not in is the opposite of in; for example Region not in United States selects all regions except for the regions that are part of the United States.

is: Use to select columns that contain not null or null values. To select all columns that contain data, select not null. To select all columns that co not contain data (database value NULL), select null.

like: Use to select values with similar characters. For example, to select all products that begin with P without specifying all characters in the name, set the condition Like "P%" (the wildcard character may vary with the RDBMS).

not like: Use to select values without similar characters; not like is the opposite of like.

=: Equal to

< >: Not equal to

<: Less than

<=: Less than or equal to

>: Greater than

>=: Greater than or equal to

Condition text box

Type the condition (value) or click the [image] button to open the Select Values From dialog box that displays all the current values for the selected column.

Integration Server retrieves these values directly from the external data source. If the external data source contains many values, Integration Server confirms that you want to view them all.

Add button

Click to add the condition and operator to the Filters list.

Add Type options

Use the following guidelines to define more than one condition on a filter:

To determine how to combine multiple conditions, select And or Or in the Add Type box or type AND or OR in the Filters box. The default value is Or, which means that the filter is applied if any condition is met. If you select And, the filter is applied only if all conditions are met.

To determine the order in which the data source executes multiple conditions, type parentheses around the conditions in the Filters box. The manner in which you define ordering depends on the data source. Consult the documentation for the data source you are using.

Verify button

Click to verify that the SQL syntax in the Filters list box is correct.

Filters list box

Displays the conditions defined on the filter. You can type conditions into the Filters list box. If you do, you must use the SQL syntax supported by the data source you are using.

Preview Results button

Click to view the data that will be returned by the filter.

Related Topics


Edit Filter Dialog Box - Ordering Tab

Edit Filter Dialog Box – Ordering Tab

Select the Ordering tab of the Edit Filters dialog box to sort based on columns within a dimension.

You can define more than one sort to create a hierarchy of sort criteria and further refine the criteria to sort member names alphanumerically within a column. For example, you can sort all states in the GEOGRAPHY column from A to Z so that a United States GEOGRAPHY column starts with Alabama and ends with Wyoming.

The source database performs the sort. Some sorts may not work in the way that you expect. For example, if you sort alphabetically by month and the data values are the names of the months, you may get April, August, December, and so on.

To display this dialog box, right-click on a dimension in the OLAP model and select Properties and then select the Hierarchies tab. Choose one of the following options:

  • Select an existing hierarchy and click Edit, select a column to filter from the hierarchy in the Hierarchy list box, and click Sort Order.

  • Click Add to create a new hierarchy, select and double-click columns to move them to the Hierarchy list box, then select a column in to filter from the Hierarchy list box and click Sort Order.

Select one of the following tabs for more help:

Dialog Box Items

Column drop-down list box

Select one column from the list of all logical columns from the model.

Sort Order drop-down list box

Select one of the following options to apply to the selection made in the Column drop-down list box:

  • Ascending (0 to 9, A to Z)

  • Descending (10 to 0, Z to A)

Add button

Click to add a sort condition to the Column and Sort Order list box.

Column and Sort Order list box

Displays all sorts added in this session. Also displays any previously-created sorts.

Delete button

Click to delete the selected row or rows from the Column and Sort Order list box.

Up button

Click to move the selected row up in sequence in the Column and Sort Order list box.

Down button

Click to move the selected row down in sequence in the Column and Sort Order list box.

OK button

Click to validate changes made in this session.

Cancel button

Click to ignore all changes. Sort conditions revert to previous state.

Related Topics


Edit Hierarchy Dialog Box

Use the Edit Hierarchy dialog box to edit existing hierarchies or to create a new hierarchy in the selected dimension.

To display this dialog box, right-click on a dimension in the OLAP model and select Properties and then select the Hierarchies tab and choose one of the following options:

  • To edit an existing hierarchy, select one and click Edit.

  • To create a new hierarchy, click Add.

Note: This dialog box is resizable. Essbase Integration Services windows and dialog boxes that contain the symbol, [image] , in the lower right corner are resizable.

Dialog Box Items

Name text box

Type the name of the new hierarchy or view the name of the existing hierarchy. The name also is displayed in the Hierarchy list box. See Naming Conventions .

Dimension list box

Displays the dimension name and the name of the table used to create the dimension. Click the plus sign, [image] , in front of a table name to display all of the columns in that table. Select and double-click columns to move them to the Hierarchy list box.

Note: Select and double-click table columns in the exact order you want them to appear in the hierarchy.

Click the minus sign, [image] , to close the list of columns.

Hierarchy list box

Displays the hierarchy that you define from the columns in the Dimension list box. Select columns in the hierarchy to filter or transform them.

Filter button

Click to set filters on columns or to sort the data values in a column alphanumerically. The Edit Filter dialog box, Filters tab opens

Sort Order button

Click to define a sort based on columns within a dimension, as well as columns in the database that are not used as part of the OLAP model or metaoutline. The Edit Filter dialog box, Ordering tab opens.

Transform button

Click to change column data values in the hierarchy. The Edit Transformations dialog box, Replace tab opens.

View Table Data button

Enabled when you select a column name in the Dimension list box. Click View Table Data to view the data values for all columns in its corresponding table in the data source.

View Sample button

Click to view the data as it will appear in the Essbase outline.

Related Topics


Edit Join Info Dialog Box

Use the Edit Join Info dialog box to edit column joins. Note that the Table list boxes display the columns in each table that you are joining. The names of the tables you are joining appear at the top of each list box.

Ensure that joins are logical; for example, join a measures column such as SALES with another measures column. Essbase Integration Server does not check the logic of the joined columns that contain the same data values.

To display this dialog box, choose one of the following options:

  • To edit an existing join, double-click on the join line between two dimension tables or between the fact table and a dimension table.

  • To add a join where none exists, click the join tool, [image], and drag the tool between two dimension tables or between the fact table and a dimension table.

Dialog Box Items

Table list boxes | From each table, select the column on which you want to join. Tip: If you double-click a column in one of the table list boxes, the column with the same name (if one exists) is highlighted in the other list box.
Current Join Information list box | Displays the columns that are joined for the two tables. You can join on sets of multiple columns.
Close button | Click to close the dialog box.
Add button | Click to add the columns selected in the Table list boxes. The columns you add are displayed in the Current Join Information list box.
Delete button | Click to delete the selected join. The columns are no longer displayed in the Current Join Information list box.

Related Topics


Edit Transformations Dialog Box

Use the Edit OLAP Model Transformations dialog box to change column data or generate unique member names from the columns of a hierarchy. This dialog box contains the following tabs:

Edit Transformations Dialog Box - Replace Tab

Edit Transformations Dialog Box - Prefix/Suffix Tab

To display this dialog box, right-click on a dimension in the OLAP model and select Properties and then select the Hierarchies tab. Select an existing hierarchy and click Edit, select a column to filter from the hierarchy in the Hierarchy list box, click Transform and then select the Prefix/Suffix tab.

You can also click Add to create a new hierarchy, select and double-click columns to move them to the Hierarchy list box, then select a column in to filter from the Hierarchy list box and click Transform and then select the Prefix/Suffix tab.


Edit Transformations Dialog Box - Prefix/Suffix Tab

Edit Transformations Dialog Box – Prefix/Suffix Tab

Select the Prefix/Suffix tab of the Edit Transformations dialog box to generate unique member names from the columns of a hierarchy.

To display this dialog box, right-click on a dimension in the OLAP model and select Properties and then select the Hierarchies tab. Select an existing hierarchy and click Edit, select a column to filter from the hierarchy in the Hierarchy list box, click Transform and then select the Prefix/Suffix tab.

You can also click Add to create a new hierarchy, select and double-click columns to move them to the Hierarchy list box, then select a column in to filter from the Hierarchy list box and click Transform and then select the Prefix/Suffix tab.

Select one of the following tabs for more help:

Dialog Box Items

Prefix options | Generate the member name as follows: None uses the column value as the full name. Nothing is attached. Parent Name attaches the immediate parent. Grandparent Name attaches the parent and the parent of the parent. All Ancestors’ Names attaches all parents. Dimension Name attaches the dimension name.
Custom Prefix text box | Type your own prefix.
Prefix Separator options | To insert a character between the prefix and the member value, select the character to insert.
Preview Prefix text box | Displays the format of the column transformation, including the Prefix Separator if you select one. The value displayed in the Preview Prefix text box changes as you select different options in Prefix and Prefix Separator.
Suffix options | Generate the member name as follows: None uses the column value as the full name. Nothing is attached. Parent Name attaches the immediate parent. Grandparent Name attaches the parent and the parent of the parent. All Ancestors’ Names attaches all parents. Dimension Name attaches the dimension name.
Custom Suffix text box | Type your own suffix.
Suffix Separator options | To insert a character between the suffix and the member value, select the character to insert.
Preview Suffix text box | Displays the format of the column transformation, including the Suffix Separator if you select one. The value displayed in the Preview Suffix text box changes as you select different options in Suffix and Suffix Separator.
Preview Results button | Click to view the data as it will appear in the Essbase outline.

Related Topics


Edit Transformations Dialog Box - ReplaceTab

Edit Transformations Dialog Box – Replace Tab

Select the Replace tab of the Edit Transformations dialog box to change column data by replacing current values with new values, changing capitalization, and removing leading and trailing spaces. You can also use the dialog box to ensure that source data and any transformations that you set within a specified hierarchy are converted to valid Essbase names prior to load operations.

To display this dialog box, right-click on a dimension in the OLAP model, select Properties, and then select the Hierarchies tab. Select an existing hierarchy and click Edit. From the hierarchy in the Hierarchy list box, select a column. Click Transform. The Replace tab is displayed by default.

Also, from the Hierarchies tab of the Dimension Properties dialog box, you can click Add to create a new hierarchy. Once you have created a new hierarchy, you can click Transform to access the Replace tab of the Edit Transformations dialog box.

Related Procedures

Replacing Data in a Hierarchy

Converting Names in a Hierarchy to Valid Essbase Names

Select one of the following tabs for more help:

Dialog Box Items

Search For text box

You can do one of the following:

  • Type the text string that you want to replace; for example, New York.

  • Choose to replace null values by selecting <NULL> from the drop-down list.

Replace with text box

Enter the new text that you want to use to replace the original text.

Case Sensitive check box

Select to replace only text strings that match the capitalization of the string in the Search for text box.

Match Whole Word check box

Select to replace the text string only when it occurs as a whole word.

Replace All Occurrences check box

Select to replace every occurrence of the text string. If you do not select this check box, Essbase Integration Server changes only the first occurrence.

Case options

Select one of the following options:

Original does not change the case.

Upper Case changes the value to upper case.

Lower Case changes the value to lower case.

Capitalize First Letters changes the first letter of the value and the first letter following a space or an underscore to uppercase.

Drop Leading/
Trailing Spaces check box

Select to omit spaces before and after the data value.

Convert Spaces to Underscores check box

Select to convert spaces that exist in the original data value to underscores.

Convert to Valid Essbase Names check box

Sometimes, source data may be invalid in terms of Essbase naming conventions or you may set transformation rules that result in an invalid Essbase member name.

Select this check box to convert, when necessary, member names within a specified hierarchy to valid Essbase names.

Add to List button

Click to add the replacement operation that you have created to the Replace list box.

Edit Details button

After selecting a specific transformation in the Replace list box, and entering changes in the Search for or Replace with text box or changing any replacement options, click Edit Details. Any changes you made to the transformation criteria are reflected in the Replace list box.

Delete button

To delete a specified transformation operation, select the operation and click Delete.

Replace List box

Corresponds with the replacement operations that you have created by listing the text strings that you want to change. Select a text string in the Search column to revise or delete the transformation criteria.

Preview Results button

Click to view the data as it will appear in the Essbase outline.

Related Topics


Intelligent Help - Overview

Intelligent Help – Overview

When you access the OLAP Model or OLAP Metaoutline main window to create or modify an OLAP model or a metaoutline, the Integration Services Console provides Intelligent Help to guide you through the process. Intelligent Help is displayed in a separate window and provides numbered procedures, along with links to automatic-detection options and frequently used functions. The Intelligent Help window is a dockable, relocatable window that you can move and resize as you prefer.


Introducing Transformation Rules

Related Topics

You use transformation rules to define how the data in user-defined columns is derived. Essbase Integration Services provides transformations that are common to most database management systems (DBMSs). These transformations enable you to use any of the following methods to derive data values in a user-defined column:

  • Duplicate values in an existing column

  • Use a substring of an existing String column

  • Concatenate two or more String columns from the same dimension table into a single column

  • Add, subtract, multiply, or divide the value of the source column by the value of another Numeric column in the dimension table

  • Obtain Year, Quarter, Month, Week, and Day values from a date from a DateTime column


OLAP Model Validation Errors Dialog Box

The OLAP Model Validation Errors dialog box opens if, upon a save or save as operation, Essbase Integration Services finds errors in the OLAP model. You can save an OLAP model that contains validation errors, but you cannot use it to build a metaoutline.

Dialog Box Items

Errors and Warnings list box

When you validate an OLAP model, Integration Services checks to ensure that the following conditions are met:

  • The OLAP model contains a fact table.

  • The OLAP model contains at least one measures value; for example, sales amounts, quantities sold, or commissions.

If you use only a single measure, you can include it in the fact table; you do not necessarily need to create a dimension tagged as accounts. If you use more than one measure in the OLAP model, you must create a dimension tagged as accounts.

  • The OLAP model includes a fact table that has no self-joins (no columns joined within a single table).

  • The OLAP Model contains dimensions that are made up of two or more source tables which have joins defined.

  • The OLAP model contains at least one dimension and that dimension contains at least one column.

  • The OLAP model contains no dimensions with duplicate names.

  • The OLAP model contains no dimensions with tables that have no joins defined between the columns in the two tables.

Each dimension in the OLAP model is joined to the fact table or another dimension table.

Related Topics


Naming Conventions - OLAP Model

Naming Conventions – OLAP Model

Related Topics

When naming an OLAP model, or when naming the tables, dimensions, and columns it contains, follow these rules:

  • Maximum column name length: 255 characters.

If the column in the data source that contains the member names has values with more than 80 characters, you must use the SUBSTRING function in the OLAP model to extract the 80 or fewer characters to use in the member name.

Note: Source table and column names may contain blank spaces, if supported by your RDBMS.

  • Names are not case-sensitive unless case-sensitivity is enabled. See "Setting Outline Properties" in Oracle Essbase Administration Services Online Help.

  • Do not use the following characters at the beginning of a name:

@ at signs | () parentheses
\ backslashes | . periods
{ } braces | + plus signs
, commas | ' single quotation marks
- dashes, hyphens, or minus | _ underscores
= equal signs | | vertical bars
< less than signs |

  • You cannot, in most cases, use the ampersand character (&).

Note: For information about using an ampersand (&) in dimension table names or column names, click the Help button in the Substitution Variables dialog box in Essbase Administration Services Console.

  • For relational data sources: Do not use spaces at the beginning or end of a name. (Essbase ignores spaces at the beginning or end of a name.)

  • For flat file data sources: Do not use spaces anywhere in a name.

  • Do not use the following characters anywhere in a name:

    • @ asterisks | + plus signs
[] brackets | ? question marks (exception: allowed in column names)
: colons | " double quotation marks
, commas | ; semicolons
= equal signs | ‘ single quotation marks
> greater than signs | tabs
< less than signs | | vertical bars
. periods |

    Notes:

    • Do not use source database table or column names that require identifiers, such as quotation marks, around the name. For example, a table name in all lowercase letters would require quotation marks around it in Oracle, and this is not supported by Integration Services.

    • Brackets ([]) are supported in block storage outlines in Essbase, but are not recommended because their use causes errors when converting to an aggregate storage outline.

    • Do not use any of the following as names:

    • $$$UNIVERSE$$$

    • #MISSING or #MI

    • Other

    • All calculation script commands, operators, and keywords. (See the Oracle Essbase Technical Reference for details.)

    • Report script commands. (See the Oracle Essbase Technical Reference for details.)

    • Function names and function arguments

    • Names that are already in the database: names of other dimensions, members, generation names, level names, aliases, and alias combinations in the database. For more information about aliases, generation names, and level names, see the Oracle Essbase Database Administrator’s Guide.


OLAP Metadata Catalog Setup Dialog Box

Use the OLAP Metadata Catalog Setup dialog box to create an OLAP Metadata Catalog automatically.

Note: If you have an older release of OLAP Metadata Catalog, you also use this dialog box to upgrade it to the current release.

To display this dialog box, select Tools > Create Catalog. If you are currently connected to an OLAP Metadata Catalog, you will be prompted to disconnect from it before using the Tools > Create Catalog command.

Related Procedures

Creating an OLAP Metadata Catalog Automatically

Dialog Box Items

Server drop-down list box

Type or select the name of the computer on which you have installed Integration Server.

If the port number on which Integration Server communicates with the console has been set to a value other than the default, you must type the server name or IP address and type the nonstandard port number, separated by a colon; for example,

cypress:3390

Catalog ODBC DSN drop-down list box

Select the ODBC data source name for the OLAP Metadata Catalog that you are creating.

If you are using the Oracle Call Interface (OCI) direct connection, be sure to prefix the Net Service Name, found in the tnsnames.ora file, or the Schema Name if you are using onames, with Oracle by typing Oracle: and then the Net Service Name or Schema Name. For example:

Oracle:TBC

Code Page drop-down list box

Select the code page that corresponds to the code page of the source database that will be used as the OLAP Metadata Catalog.

The code page is a portion of the locale, which identifies the local language and cultural conventions such as the formatting of currency and dates and the sort order of data.

Note: If you want to create a Unicode OLAP Metadata Catalog, you must manually create it by running the scripts provided, as described in the chapter on OLAP Metadata Catalog setup in the Essbase Integration Services System Administrator's Guide. This release does not support automatic creation of a Unicode catalog.

User Name drop-down list box

Select or type the name of the user to whom you have assigned access to the database where the OLAP Metadata Catalog will be stored. This user must have authority to create tables in this database.

Password text box

Type the password for the user to whom you have assigned access to the database where the OLAP Metadata Catalog will be stored.

Create button

Click to create the OLAP Metadata Catalog automatically. A message is displayed when creation of the catalog is successful.

Delete button

Click to delete a catalog.

Close button

Click to close this dialog box without creating an OLAP Metadata Catalog.

Show this dialog at Startup check box

Select this check box to display the OLAP Metadata Catalog Setup dialog box automatically each time you start Integration Services Console.


OLAP Model Properties Dialog Box

At any time after you save an OLAP model, you can change the description and access rights that you originally assigned by using the OLAP Model Properties dialog box. The dialog box contains the following tab:

General Tab

To display this dialog box, open an OLAP model and select Edit > Properties > Model.

Related Topics


OLAP Model Properties Dialog Box - General Tab

OLAP Model Properties Dialog Box – General Tab

Select the General tab of the OLAP Model Properties dialog box to change the access rights you originally assigned to the OLAP model.

To display this dialog box, open an OLAP model and select Edit > Properties > Model.

Related Procedures

Saving an OLAP Model Manually

Validating OLAP Models Manually

Dialog Box Items

Model Name text box

Displays the name of the current OLAP model.

Owner Name text box

Displays the name of the user who created the OLAP model.

Security drop-down list box

Select one of the options below to define who can access the OLAP model:

  • Allow read/write access for other users: Gives other users permission both to read to and write to the OLAP model. This setting is the default.

  • Allow read access for other
    users    : Gives other users permission to read the OLAP model but not to write to it.

  • Disallow all access for other users: Other users can neither read nor write to the OLAP model. Only you can read it or write to it.

Description text box

Type over the existing information to change the description of an OLAP model. The description can contain up to 255 characters. It is also displayed in the Save New OLAP Model dialog box and the Delete an OLAP Model/Metaoutline dialog box.

Save secondary data sources’ password check box

Select to save password information for secondary data sources.

Related Topics


Overview Dialog Box

Use the Overview dialog box to display conceptual information about OLAP models and metaoutlines. Click Overview in the Welcome dialog box to display overview information.

Note: You can only access the Overview dialog box from the Welcome dialog box.

Dialog Box Items

Model button | Click to display information describing the components of an OLAP model.
Metaoutline button | Click to display information describing the components of a metaoutline.


Recursion Dialog Box

Use the Recursion dialog box to set the parent and child column in a self-join.

To display this dialog box, in an OLAP model, right-click a dimension table to join to itself and select Properties and then select the Physical Joins tab. Select a column in the left list box and a column in the right list box and click Add.

Dialog Box Items

Parent Column text box | Displays the name of the column to use as the parent in the recursive join. To define the current child column as parent and the current parent column as child, click Swap.
Child Column text box | Displays the name of the column to use as the child column in the recursive join. To define the current child column as parent and the current parent column as child, click Swap.
Swap button | Click to reverse the parent and child columns in a recursive join.

Related Topics


Rename Model/Metaoutline Dialog Box

You use the Rename Model/Metaoutline dialog box to rename an OLAP model or metaoutline.

To display this dialog box, select File > Rename Model/Metaoutline. You do not need to open an OLAP model or metaoutline.

Related Procedures

Renaming an OLAP Model or Metaoutline

Dialog Box Items

Type drop-down list box | Select the type to be renamed: OLAP Model or Metaoutline.
Name drop-down list box | Select the name of the OLAP model or metaoutline to be renamed.
New Name text box | Type the new name for the OLAP model or metaoutline.
OK button | Click to rename the OLAP model or metaoutline under the new name.
Cancel button | Click to cancel the Rename Model/Metaoutline operation.


Rules for Defining Attribute Dimensions

Related Topics

When creating attribute dimensions in an OLAP model, follow these guidelines:

  • You can associate attributes only with a sparse, standard dimension.

  • You can associate attributes with columns that will later be enabled for Hybrid Analysis in a metaoutline.

  • You can tag a column as attribute-enabled if there is a recursive hierarchy defined upon the column. But this column can only associate by level, not by memberset.

  • You can add a recursive hierarchy when one of the columns is attribute-enabled.

  • You cannot deselect a column as attribute-enabled if such action would invalidate an existing attribute dimension that includes the selected column.

  • You cannot tag a hidden column as attribute-enabled.

  • Database columns used to create Level 0 members of attribute dimensions in a metaoutline must never contain NULL values.

  • You cannot associate an attribute dimension with a base dimension level used to create a ragged hierarchy with null promotion, where the base dimension column contains nulls.


Sample Application Setup Dialog Box

Use the Sample Application dialog box to automate the setup of the sample TBC (The Beverage Company) application.

To display this dialog box, select Tools > Create Sample. If you have any OLAP models or metaoutlines open, you will be prompted to close them.

Related Procedures

Creating the Sample Application Automatically

Dialog Box Items

Sample ODBC DSN text box

Select or type the ODBC data source name for creating or deleting tables in the source database.

If you are using the Oracle Call Interface (OCI) direct connection, be sure to prefix the Net Service Name, found in the tnsnames.ora file, or the Schema Name if you are using onames, with Oracle by typing Oracle: and then the Net Service Name or Schema Name. For example:

Oracle:TBC

See the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide for more information.

User Name text box

Displays the default user name, TBC (tbc for Informix), required for creating the sample application. This user must have authority to create tables in the database where the OLAP Metadata Catalog is stored.

Password text box

Type the password for the user to whom you have assigned access to the TBC sample application database.

Create button

Click to create the sample application.

Delete button

Click to delete the sample application.

Close button

Click to exit this dialog box.


Save Model As Dialog Box

You use the Save Model As dialog box to save an OLAP model and its associated metaoutlines under a different name.

To display this dialog box, select File > Save Model As. If you already have an OLAP model open and want to save it under a different name, you must close the model in order to use the Save Model As function.

Related Procedures

Saving an OLAP Model As

Renaming an OLAP Model or Metaoutline

Dialog Box Items

Model drop-down list box | Select the OLAP model that you want to save under a different name.
Save text box | Type the name to be used for the new OLAP model.
Save Model Only check box | Select to only save the OLAP model without saving any of the associated metaoutlines. If you do not check this box, all associated metaoutlines will be saved along with the new OLAP model. The new metaoutlines keep their original names and a 10-digit number is appended to each. You can rename the associated metaoutlines by using the Rename Model/Metaoutline dialog box.
OK button | Click to save the OLAP model and, if applicable, the associated metaoutlines under the new name.
Cancel button | Click to cancel the Save Model As operation.


Saving an OLAP Model Under a Different Name - Overview

Saving an OLAP Model Under a Different Name – Overview

You can save an OLAP model under a different name while keeping or modifying any existing metaoutlines associated with the model. The Save Model As command enables you to perform the following tasks:

  • Make changes to an OLAP model without losing its modified metaoutlines

  • Make changes to an OLAP model without saving its associated metaoutlines

  • Make changes to an OLAP model while keeping the original model metaoutlines


Synonyms

Related Topics

Synonyms are accessed using the tree structure in the left frame of the OLAP Model main window. Expand SYNONYMS to view the tables and columns as they are displayed in the data source. Synonyms are displayed only for database management systems (DBMSs) that support synonyms or aliases.

A plus sign, [image], is always displayed next to the SYNONYMS icon.

To expand SYNONYMS, click the plus sign.

Notes

  • If the selected data source does not contain synonyms or aliases, the plus sign, [image], next to the SYNONYMS icon changes to a minus sign, [image].

  • Click the plus sign to expand the VIEWS icon to see if views are available for the selected data source. If the selected data source does not contain views, the plus sign, [image] next to the VIEWS icon changes to a minus sign, [image]. Views are logical tables created by combining columns from one or more tables in the data source.


Tables

Related Topics

The physical tables of the data source are accessed using the tree structure in the left frame of the OLAP Model main window. Expand TABLES to view the tables and columns of the data source.

A plus sign, [image], is always displayed next to the TABLES icon.

To expand TABLES, click the plus sign.

Notes

  • Click the [image] next to VIEWS to see views for the selected data source. Views are logical tables created by combining columns from one or more tables in the data source. If the selected data source does not contain views, the plus sign, [image], next to the VIEWS icon changes to a minus sign, [image].

  • If the database management system (DBMS) supports synonyms or aliases, click the [image] to see SYNONYMS for the selected data source. If the selected data source does not contain synonyms, the plus sign, [image], next to the SYNONYMS icon changes to a minus sign, [image].

  • Teradata: Tables are categorized by database names, not owner names. The database name must be included with the table name in any SQL statements that you create; for example, when you override standard SQL (see Overriding Standard SQL).


Views

Related Topics

You access views using the tree structure in the left frame of the OLAP Model main window. Expand VIEWS to see the logical tables created by combining columns from one or more tables in the data source.

A plus sign, [image], is always displayed next to the VIEWS icon.

To expand VIEWS, click the plus sign.

Notes

  • If the selected data source does not contain views, the plus sign, [image], next to the VIEWS icon changes to a minus sign [image].

  • If the database management system (DBMS) supports synonyms or aliases, you can expand SYNONYMS to view synonyms in the data source.


Table Properties Dialog Box

Use the Table Properties dialog box to set properties for the selected table. The dialog box contains the following tabs:

General Tab

Columns Tab

Physical Joins Tab

Dimensions Tab (available only for the fact table)

To display this dialog box, right-click a dimension table name and select Properties. The General tab is displayed by default.

Select one of the following tabs for more help:

Related Topics


Table Properties Dialog Box - Columns Tab

Table Properties Dialog Box – Columns Tab

Select the Columns tab of the Table Properties dialog box to edit column properties, add or delete columns, hide columns in metaoutlines, select columns for drill-through, or select columns to be attribute-enabled. If the column contains date or time information, the Date Hierarchy button is enabled.

To display this dialog box, right-click a dimension table name in an OLAP model and select Properties and then select the Columns tab.

Select one of the following tabs for more help:

Dialog Box Items

Columns list box

Displays all the columns you can work with in the OLAP model.

Name column

Displays the OLAP model column name. The graphic preceding each OLAP model column name indicates its data type:

indicates numeric columns in the fact and accounts dimension tables. You can use these columns as measures in metaoutlines.

indicates numeric columns in tables other than the fact and accounts tables.

indicates string columns, which contain alphanumeric data.

indicates datetime columns, which contain date and time data.

Table Name column

Displays the name of the database table that is the source for each column.

Column Name column

Displays the name of the column in the data source on which the OLAP model column is based.

Hidden column

Displays a Y (for Yes) to indicate that a column is hidden to metaoutlines. A blank indicates that the column is available to metaoutlines.

Drill-Through column

Displays a Y to indicate that a column is available only for drill-through reports, or displays an NA (for Not Applicable) to indicate that there is no restriction on the column, meaning that the column is available for both drill-through as well as for creating the metaoutline. All numeric columns in the fact table and accounts dimension table are marked as NA.

Note that columns tagged as Y will not be visible under their table names in the left frame of the metaoutline. Columns tagged as Y are intended to be available only for drill-through and not for creating the metaoutline. Even though the column is not visible in the left frame of the OLAP Metaoutline main window, you will still be able to include this column in the column list when creating a drill-through report.

Also note that columns tagged as NA (for Not Applicable) will be visible in the left frame of the OLAP Metaoutline main window and are available for selection in the drill-through report.

Attribute column

Displays a Y to indicate that a column is attribute-enabled.

Edit button

Click to edit the selected column and open the Column Properties dialog box.

Add button

Click to add a new column and open the Add New Column to Table dialog box.

Delete button

Click to delete the selected column.

Date Hierarchy button

Click to open the Date Hierarchy dialog box where you create a date hierarchy from a single table column.

Note: This button is only enabled when you select a DateTime column.

Hide button

Click to hide the selected column. A Y is displayed in the Hidden column. To show the column, click Hide again and the Y is removed from the Hidden column.

Hidden columns do not appear in metaoutlines.

Tip: You can use hidden columns in joins.

Drill-Through button

Click to set the selected column as drill-through only. A Y is displayed in the Drill-Through column of the dialog box. To remove the Drill-Through property from the column, click Drill-Through again and the Y is removed.

Drill-through columns are available only when creating drill-through reports.

Attribute button

To select or deselect a column as attribute-enabled, highlight the name of the OLAP model column and click the Attribute button.

Related Topics


Table Properties Dialog Box - Dimensions Tab

Table Properties Dialog Box – Dimensions Tab

Select the Dimensions tab of the Table Properties dialog box to display and edit the dimensions defined in the OLAP model. This tab is only available from the fact table.

To display this dialog box, right-click the table name in the fact table of an OLAP model and select Properties and then select the Dimensions tab.

Select one of the following tabs for more help:

Dialog Box Items

Dimensions list box | Displays the dimensions defined in the OLAP model.
Edit button | Select a dimension and click to open the General tab of the Dimension Properties dialog box.

Related Topics


Table Properties Dialog Box - General Tab

Table Properties Dialog Box – General Tab

Select the General tab of the Table Properties dialog box to rename a table or to view its properties.

To display this dialog box, right-click a dimension table name in an OLAP model and select Properties. The General tab is displayed by default.

Select one of the following tabs for more help:

Dialog Box Items

Name text box | Type the new table name. To name the dimension you are creating with the table, use the Create New Dimension dialog box. See Naming Conventions.
Data Source text box | Displays the data source name from which this table was created.
Dimension text box | Displays the dimension in which the table is used.
Level text box | Displays the distance of each dimension table from the fact table. The fact table is a dimension at level 0. A table joined directly to the fact table is a dimension at level 1, a table joined to another table (a dimension branch) that is joined directly to the fact table is at level 2, and so on.
List of Physical Table(s) list box | Displays the physical tables that make up the selected dimension table. A dimension table can contain one or more physical tables.

Related Topics


Table Properties Dialog Box - Physical Joins Tab

Table Properties Dialog Box – Physical Joins Tab

Select the Physical Joins tab of the Table Properties dialog box to perform any of the following types of join operations:

  • Add a source table to a dimension table.

  • Add a dimension table to the fact table.

  • Join tables within a dimension.

  • Delete a join within a dimension table.

  • Join a table to itself.

To display this dialog box, right-click the table name in an OLAP model and select Properties and then select the Physical Joins tab.

Select one of the following tabs for more help:

Dialog Box Items

Table drop-down list boxes | Select the tables that you want to join. Select one table from each drop-down list box.
Child and Parent list boxes | Select the column that you want to join on from the child and parent table and click Add. Select one column from each list box.
Join Information list box | Displays the columns that are joined for the two tables. You can join on multiple columns.
Add button | Click to add the columns selected in the Column list boxes. The columns that you select to join are displayed in the Join Information list box.
Delete button | Click to delete the selected join. The names of the columns are no longer displayed in the Join Information list box.
Recursion button | Click to define parent and child columns in the Recursion dialog box. This button is only selectable if you have created a recursive join by joining a table to itself.

Related Topics


Transforming a Column Using a Pass-Through Function

Use pass-through transformation rules to provide a statement that Integration Server "passes through" as a part of the SQL SELECT statement that returns values for the column.

Note: When you need to extract data values for a column in ways other than those provided in Essbase Integration Services, it is recommended that you use SQL Override instead of the pass-through function.

For example, suppose a new column, SALESVAR, is based on the SALES column. You want the value of the new column to equal the variance of the values in the SALES column. Integration Services does not provide a variance transformation rule. However, Integration Services Console provides the pass-through transformation rule so that you can take advantage of a variance function if the database management system (DBMS) you are using supports that function.

To display this dialog box, in an OLAP model, right-click a column name in a dimension and select Properties and then select the Transformation Rule tab.

Select one of the following tabs for more help:

Dialog Box Items

Pass-Through check box

Select to define a pass-through transformation.

Date-part drop-down list box

Select the Date-part option for the column.

From these options, select the part of the date the column contains. For example, if you select the Month category, Integration Server inserts the month of the year into the new column. If the value in the source column is 04/22/2001, Integration Server inserts a 4 as the value in the column.

If the column is a copy of the full date, select <None>.

Note: This drop-down list box is available only if you are transforming a DateTime column.

Pass-through
text box

Type the command or information needed to perform the pass-through function. The result of the function becomes the value of the new column.

For column names, always enter the dimension table name and the column name with a period between them; for example, SALESFACT.SALES. For some RDBMSs, you must also prefix the column name with the user name; for example, TBC.SALESFACT.SALES. You must use the SQL syntax supported by the RDBMS.

For example, to derive the variance of the SALES column, type: VARIANCE(TBC.SALESFACT.SALES)

Note: Do not type the word SELECT itself; type only the text that you want to add to the SELECT clause.

Related Topics


Transforming a DateTime Column

Transform columns with a DateTime data type to map the contents of a new column to the appropriate portion of a date, such as year or month.

To display this dialog box, in an OLAP model, right-click a column name in a dimension and select Properties and then select the Transformation Rule tab.

Select one of the following tabs for more help:

Dialog Box Items

Pass-Through check box

Select to define a pass-through transformation. See Transforming a Column Using a Pass-Through Function.

Date-part drop-down list box

Select the Date-part option for the column. If this list box is not selectable, then you must select a Date-part option in the Category list box. Options are:

  • <None>

  • Years

  • Quarters

  • Months (options available for both numeric and character representations of month)

  • Weeks

  • Days

From these options you select the part of the date the column contains. For example, if you select the Months category, Essbase Integration Server inserts the month of the year into the new column. If the value in the source column is 04/22/2001, Integration Server inserts 4 as the value in the new column.

If the new column is a copy of the full date, select <None>.

Tip: To use Dynamic Time Series in a metaoutline, you must set the Date-part for the column containing time data in the OLAP model to tag the corresponding values as Dynamic Time Series in the metaoutline.

Category list box

Select the Date-part option for the column.

From these options, select the part of the date the new column contains. For example, if you select the Months category, Integration Server inserts the month of the year into the new column. If the value in the source column is 04/22/2001, Integration Server inserts 4 as the value in the new column.

If the new column is a copy of the full date, select <None>.

Function list box

Select how to represent the date. For example, if you select Month in the Category list box, the Function list box displays three possible formats: MM, MMM, and Month. Click on a format option, and a description of that option is displayed in the area under the Category and Function list boxes.

If you select MM and the value in the source column is April, Integration Server displays 04 as the value in the new column.

Description text box

Describes the selection in the Category list box.

Related Topics


Transforming a Numeric Column

Transform a column with a numeric data type to define a calculation that includes the source column; an operator such as add, subtract, multiply, or divide; and another column from the same dimension. The result of the calculation becomes the value of the new column. The source column is the first parameter in the calculation.

To display this dialog box, in an OLAP model, right-click a column name in a dimension and select Properties and then select the Transformation Rule tab.

Select one of the following tabs for more help:

Dialog Box Items

Pass-Through check box | Select to define a pass-through transformation. See Transforming a Column Using a Pass-Through Function.
Date-Part drop-down list box | Select the Date-part option for the column. From these options, select the part of the date the column contains. For example, if you select the Month category, Integration Server inserts the month of the year into the new column. If the value in the source column is 04/22/1999, Integration Server inserts a 4 as the value in the column. If the column is a copy of the full date, select <None>. Note: This drop-down list box is only available if you are transforming a DateTime column.
Operator list box | Select the operator to use in the transformation. You can select: + (addition operator) - (subtraction operator) * (multiplication operator) / (division operator), or % (modulus operator, not percent) Tip: The % operator returns the remainder of a division calculation; the % operator does not stand for percentage.
Column drop-down list box | Select the column to use as the other operand; for example, TBC.SALESFACT.OPENINGINVENTORY.
Invert Parameters check box | Select to reverse the sequence of the operands. The operands switch their positions; for example, TBC.SALESFACT.SALES - TBC.SALESFACT.OPENINGINVENTORY changes to TBC.SALESFACT.OPENINGINVENTORY - TBC.SALESFACT.SALES
Calculation box | Displays the numeric calculation; for example, TBC.SALESFACT.SALES - TBC.SALESFACT.OPENINGINVENTORY

Related Topics


Transforming a String Column Using the Substring Function

Transform a string column using the Substring function when you need to extract a substring from a column. For example, if only the six characters at the beginning of the PRODCODE column are meaningful for your analysis, create a SHORTPROD column containing those six characters. As a result, when the value of PRODCODE is 100-10-C001-S0003, the value of SHORTPROD is 100-10.

To display this dialog box, in an OLAP model, right-click a column name in a dimension and select Properties and then select the Transformation Rule tab.

Select one of the following tabs for more help:

Dialog Box Items

Pass-Through check box

Select to define a pass-through transformation. See Transforming a Column Using a Pass-Through Function .

Date-part drop-down list box

Select the Date-part option for the column.

From these options, select the part of the date the column contains. For example, if you select the Month category, Integration Server inserts the month of the year into the new column. If the value in the source column is 04/22/2001, Integration Server inserts 4 as the value in the column.

If the column is a copy of the full date, select <None>.

Note: This drop-down list box is available only if you are transforming a DateTime column.

Function list box

Select the function to use to derive the new column from the source data. Valid options are:

  • Substring (to select a string from inside another string)

  • Concatenate (to combine two strings)

  • None

Related Topics


Transforming a String Column Using the Concatenate Function

Transform a string column using the concatenate function when you need to combine two or more string columns from the same dimension.

For example, you can create a single column, SUPPLIER-ADDRESS, that contains the values of both the SUPPLIER and the ADDRESS columns.

To display this dialog box, in an OLAP model, right-click a column name in a dimension and select Properties and then select the Transformation Rule tab.

Select one of the following tabs for more help:

Dialog Box Items

Pass-Through check box

Select to define a pass-through transformation. See Transforming a Column Using a Pass-Through Function .

Function list box

Select the function to use to derive the new column from the source data. Valid options are:

  • Substring (to select a string from inside another string)

  • Concatenate (to combine two strings)

  • None

Column names list box

Select the columns to concatenate and click the [image] button.

Concatenate Columns list box

Displays the columns, in sequence, to include in the concatenated column.

Click [image] to remove columns from the Concatenate Columns list box.

Click [image] to move a column up in the Concatenate Columns list box.

Click [image] to move a column down in the Concatenate Columns list box.

Column Separator drop-down list box

Select a separator for strings in the concatenated column:

None. No separator, strings run together.

Space (‘_’). Inserts a blank space between strings.

Dash (‘-‘). Inserts a dash between strings.

Underscore (‘_’). Inserts an underscore between strings.

Related Topics


User-Defined Table Definition Dialog Box

Use this dialog box to define one or more user-defined tables in order to create a virtual "view" of the data from your database.

If your database schema is not properly structured for use with Essbase Integration Services, user-defined tables enable you to build OLAP models and metaoutlines without making changes to the database schema.

To access this dialog box, select the User-Defined Tables icon in the left frame of the OLAP Model main window, right-click, and select Add User-Defined Tables. Alternatively, you can select the User-Defined Tables icon and then select Edit > Add User-Defined Tables.

Note: This dialog box is resizable. Integration Services windows and dialog boxes that contain the symbol, [image] , in the lower right corner are resizable.

Related Procedures

Creating User-Defined Tables

Dialog Box Items

Table Name text box | Type the name of the user-defined table that you want to create.
Table Definition text box | Type the SQL statement to create the user-defined table using the SQL syntax required by the data source. This statement defines the names of the tables in the virtual view and can include any number of source column names. It becomes the basis for a logical table in the data source.


Using Intelligent Help

Overview

Use Intelligent Help to guide you through the process of creating an OLAP model or metaoutline. Intelligent Help provides quick-access links that automatically detect the fact table, dimensions, and hierarchies in your data source to create OLAP models and metaoutlines quickly and easily. Intelligent Help also provides you assistance in completing tasks related to OLAP model and metaoutline creation and maintenance.

You can click the "autodetect" links within the Intelligent Help to automate the following tasks:

  • Create a fact table for an OLAP model

  • Create dimensions for an OLAP model

  • Create hierarchies for an OLAP model

  • Get existing dimensions and hierarchies for a metaoutline

To display or hide Intelligent Help

1 To display Intelligent Help when you are working with an OLAP model or metaoutline, select Tools > Intelligent Help.

When displayed, a check mark appears next to the Intelligent Help menu item.

2 To hide the Intelligent Help while working with an OLAP model or metaoutline, deselect Tools > Intelligent Help.

When hidden, a check mark does not appear next to the Intelligent Help menu item.

Note: You can also display or hide the Intelligent Help by clicking the Intelligent Help toolbar button, [image] .

To move the Intelligent Help window:

When you open Essbase Integration Services Console for the first time, the Intelligent Help window is docked, or attached, to the OLAP Model or Metaoutline main window directly below the Toolbar. To move the Intelligent Help window from this position:

1 Click the [image] button in the upper-left corner of the Intelligent Help window.

The window becomes a separate window with a title bar reading Model Intelligent Help or Metaoutline Intelligent Help, as appropriate.

2 Click in the title bar area of the Intelligent Help window and drag it to the position you want on your screen.

You can position it at the bottom or even outside of the console window.

3 To dock the Intelligent Help window back into the console main window, click in the Intelligent Help title bar area and move the window either to the top or bottom of the console window.

The Intelligent Help is reattached, or docked.

To resize the Intelligent Help window:

When the Intelligent Help window is not docked to the main console window, you can resize it as needed.

1 Move the cursor to an edge or corner of the Intelligent Help window until the cursor becomes a resize arrow.

2 Drag the resize arrow till the window is the size you want. You can drag in all directions: vertical, horizontal, and diagonal.

To use Intelligent Help to assist you in completing a task:

  • Click a process button in the Intelligent Help toolbar above for task information related to that process topic. For example, for information on editing a fact table, click the Fact Table process button and select the Edit Fact Table task.


View Log File - Overview

For assistance in analyzing and debugging data and member loads, you can use the Tools > View Log file command to retrieve log file data for display in the Server Log File window after a data or member load is completed. A date and time stamp is provided for each activity that has occurred during the load.

The server retrieves the last one MB of log file data at any time.


View Table Data Dialog Box

Use the View Table Data dialog box to view the data values in the selected table in the data source.

To display this dialog box, select a table name in the left frame of the OLAP Model main window and then select View > View Table Data.

Note: This dialog box is resizable. Essbase Integration Services windows and dialog boxes that contain the symbol, [image] , in the lower right corner are resizable.

Related Procedures

Viewing Table Data

Dialog Box Items

Source text box | Displays the name of the data source.
Table text box | Displays the name of the source table.
View Table Data list box | Displays the first 100 rows of the selected source table.
Rows text field | Displays the number of rows currently displayed in the View Table Data list box.
Previous button | Displays the previous 100 rows in the source table.
Next button | Displays the next 100 rows in the source table.
Show All button | Retrieves all rows in the source table. Depending on the number of rows in the source table, the retrieval may take considerable time.


Welcome Dialog Box - Existing Tab

Welcome Dialog Box – Existing Tab

Use the Existing tab of the Welcome dialog box to view or modify an existing OLAP model or metaoutline.

To display this dialog box, select File > Open.

Select one of the following tabs for more help:

Dialog Box Items

Selection list box | Displays all OLAP models and metaoutlines that are stored in the OLAP Metadata Catalog. Click the plus sign (+) in front of an OLAP model to view the associated metaoutlines. Click the minus sign (-) in front of an OLAP model to collapse the view.
Primary Data Source text box | Displays the name of the primary data source with which the selected OLAP model or metaoutline is associated. This information is read-only.
Description text box | Displays an optional description of the primary data source for the selected OLAP model or metaoutline. This information is read-only.
Dimensions text box | Displays the dimensions contained in the selected OLAP model or metaoutline. This information is read-only.
Exclusive Access check box | Select to open the selected OLAP model or metaoutline with an exclusive Read/Write access lock. When you select this option, other users can neither read nor write to the OLAP model or metaoutline until you have saved and closed it. At the time you save an OLAP model or metaoutline, you can assign user access privileges.
Open button | Click to open the selected OLAP model or metaoutline option. You must first enter the appropriate information in the Data Source dialog box. You can also double-click the selected option to open it.
Overview button | Click to display conceptual information about OLAP models and metaoutlines.


Welcome Dialog Box - New Tab

Welcome Dialog Box – New Tab

Use the New tab of the Welcome dialog box to create a new OLAP model or metaoutline.

To display this dialog box, select File > New.

Select one of the following tabs for more help:

Dialog Box Items

OLAP Model option | Select to create a new OLAP model. You must first enter the appropriate information in the Data Source dialog box.
OLAP Metaoutline option | Select to create a new OLAP metaoutline. You must first enter the appropriate information in the Data Source dialog box.
Overview button | Click to display conceptual information about OLAP models and metaoutlines.
Open button | Click to open the selected OLAP model or metaoutline option. Then type the appropriate information in the Data Source dialog box. You can also double-click the selected option to open it.


Welcome Dialog Box - Recent Tab

Welcome Dialog Box – Recent Tab

Use the Recent tab of the Welcome dialog box to access the last ten OLAP models or metaoutlines created or modified.

To display this dialog box, select File > Open and then click the Recent tab.

Select one of the following tabs for more help:

Dialog Box Items

Selection list box | Displays the last ten OLAP models and OLAP metaoutlines created or modified.
Open button | Click to open the selected OLAP model or metaoutline option. You must first enter the appropriate information in the Data Source dialog box. You can also double-click the selected option to open it.
Primary Data Source text box | Displays the name of the primary data source with which the selected OLAP model or metaoutline is associated. This information is read-only.
Description text box | Displays an optional description of the primary data source for the selected OLAP model or metaoutline. The information is read-only.
Dimensions text box | Displays the dimensions contained in the selected OLAP model or OLAP metaoutline. This information is read-only.
Exclusive Access check box | Select to open the selected OLAP model or metaoutline with an exclusive Read/Write access lock. When you select this option, other users can neither read nor write to the OLAP model or metaoutline that you open until you have saved and closed it. At the time you save an OLAP model or metaoutline, you can assign user access privileges.
Open button | Click to open the selected OLAP model or metaoutline. You must first enter the appropriate information in the Data Source dialog box. You can also double-click the selected option to open it.
Overview button | Click to display conceptual information about OLAP models and metaoutlines.


Overview of Essbase Integration Services

Related Topics

Essbase Integration Services transfers data from a data source to an Essbase database quickly and easily. To perform the transfer, you must determine which data to transfer and consolidate the selected data into a form that is useful for decision-support users. Then you must identify the tables, rows, or columns that contain the data and determine how they map to the structure of the OLAP multidimensional database.

Integration Services provides graphical tools to help you perform the following tasks:

  • Create an OLAP model from tables, views, and columns in the source database. An OLAP model is a logical star schema consisting of a fact table that is surrounded by related dimension tables.

  • Use the OLAP model to create a metaoutline, an outline template that contains the structure and the rules required to generate an Essbase outline.

  • Use the metaoutline to create and populate an Essbase database.


Overview of OLAP Models

Related Topics

An OLAP model contains a star schema. OLAP models are based on the idea that values in a soruce database can be categorized as either facts or dimensions of facts. Facts are the numeric, variable values in the database, such as sales figures and the number of units sold. Associated with facts are related data values that provide additional information, such as store locations and product IDs of units sold. An OLAP model contains a fact table, one or more dimension tables, one or more dimension branches, and optional time and accounts dimensions. You can also tag columns in an OLAP model to be attribute-enabled. These columns become attributes such as color or size, in the metaoutline and provide an additional layer of reporting in Essbase.

Unlike other integration products, an OLAP model you create with Essbase Integration Services Console is a logical model, not a physical star schema. The OLAP model is a logical representation of the data values that you select from the source database tables and that you want to report in Essbase.

You use an OLAP model to create one or more metaoutlines. A metaoutline contains the basic structure required to build an outline and load data into Essbase. You can use one OLAP model as the basis for another OLAP model by saving the original OLAP model under a different name and editing it as needed to meet reporting requirements. You can create any number of OLAP models for use in building metaoutlines. However, each metaoutline is based on one specific OLAP model. OLAP models have the following features:

  • They are reusable. You can use the same OLAP model as the basis for more than one metaoutline.

  • They provide a layer of abstraction that insulates the database outline from changes in the source database.

  • They enable you to create hierarchies to structure and summarize the data from the source database. You can use these hierarchies in multiple metaoutlines.