Everything Oracle

Home

Everything Oracle

 

 

 

 

 

 

Create Metadata Catalog and Data Source Schemas

 

Integration Services populates Essbase using data drawn from one or more data sources, flat files and relational databases.  To map this data it needs to store metadata about the data sources in a Metadata Catalog.  This Metadata Catalog must be stored in a relational database.  In this section, we’ll define two schemas in an Oracle database, one to serve as a data source for the Integration Services sample applications, and one to serve as a Metadata Catalog for these applications.

 

Log into SQL*Plus as user “system”.  Create two schemas, “tbc_md” and “tbc”, and grant each “connect” and “resource” privileges as follows:

 

create user tbc_md identified by <password>;
grant connect, resource to tbc_md;
create user tbc identified by <password>;
grant connect, resource to tbc;

 

 

Create ODBC Datasources

 

Integration Services connects to the Metadata Catalog and data sources using ODBC connections.  In this section, we’ll create ODBC connections for the two Oracle schemas that we created in the previous section.

 

Navigate to “Start => Control Panel => Administrative Tools => Data Sources (ODBC)”.  Double-click on the “Data Sources (ODBC)” item.  When the “ODBC Data Source Administrator” window appears, select the “System DSN” tab:

 

Press the “Add” button.  In the “Create New Data Source” window select the “MERANT OEM 5.2 32-BIT Oracle Wire Protocol” driver, and then press “Finish”:

 

In the “ODBC Oracle Wire Protocol Driver Setup” window enter the following information: Data Source Name: “TBC_MD”; Description: “Sample Metadata Catalog”; Host: “localhost”; Port Number: “1521”; SID: “orcl” (or your Oracle database SID if it’s different).  Then press the “Test Connect” button:

 

In the “Logon to Oracle Wire Protocol” window enter the schema name for the Metadata Catalog, “tbc_md”, the corresponding password, and then press the “OK” button:

 

The “Test Connect” window appears showing that a connection has been established:

 

Press the “OK” button to return to the “ODBC Oracle Wire Protocol Driver Setup” window, and then press “OK” to return to the “System DSN” tab.

 

Repeat the process for the data source schema “tbc”, using the appropriate values.  The “ODBC Data Source Administrator” window should now show the two new ODBC connections:

 

Press “OK” to exit.

 

 

Create the Metadata Catalog

 

Navigate to “Start => All Programs => Hyperion => Integration Services => Integration Services Console”.

 

As this is you’re first logon to the Integration Services Console, the “OLAP Metadata Catalog Setup” window will appear.  Enter the following values: Server Name: “<name of integration services host>” (note, unlike in other Hyperion configuration windows, a value of “localhost” is not acceptable); Catalog ODBC DSN: “TBC_MD”; Code Page: “English (Latin1)”; User Name: “tbc_md”; Password: “<password>”.  Deselect the “Show this dialog at Startup” radio button.  Finally, press the “Create” button to create the Metadata Catalog:

 

A pop-up window indicating that the catalog has been created successfully will appear.  Exit the window.

 

If you login to SQL*Plus as “tbc_md” and select “object_type” and “object_name” from “all_objects”, then you can see the objects that constitute the Metadata Catalog:

 

Exit SQL*Plus, and in the “OLAP Metadata Catalog Setup” window press the “Close” button.  The “Login” window will appear (see the next section).

 

 

Logging in to Integration Services

 

When the “Login” window appears, in the “Essbase Integration Services” section enter the following values: Server: “<name of integration services host>”; OLAP Metadata Catalog: “TBC_MD”; Code Page; “ English (Latin1)”; User Name: “tbc_md”; Password: “<password>”.

 

In the “Essbase” section, enter the following values: Server: “<name of integration services host>”; User Name: “sysadmin”; Password: “<password>”:

 

Press the “Set Login Defaults” button to reuse these values on subsequent logins.  In the “Set Login Defaults” window accept the defaults, and press the “Set Default” button:

 

Press “OK” to return to the “Login” window.  Then press the “Shared Services Connection” button.  In the “Shared Services Connection” window enter the following values: Shared Services Server: “localhost”; Port Number: “58080”; User Name: “sysadmin”; Password: “<password>”:

 

Press the “Test Connection” button.  The “Essbase Integration Services Console” window appears showing that a connection has been established:

 

Press “OK” to exit this window.  Press the “Set Login Default” button to save the values you have just entered.  Press “OK” to return to the “Login” window.  Finally, press “OK” again to display the “Welcome” window:

 

Press “Cancel” to exit the window.

 

 

Sample Applications

 

Select “Tools => Create Sample” from the “Integration Services Console” menu.  In the “Sample Application Setup” window enter the following values: Sample ODBC DSN: “TBC”; Password: “<password>”:

 

Press the “Create” button.  A pop-up window will appear, indicating that it may take several minutes to create the tables.  Press the “OK” button (table creation should take 3-4 minutes on a typical desktop machine).  When table creation is complete a pop-up window will appear, indicating that the sample application has been successfully created.  Press the “OK” button and then press “Close” to exit the “Sample Application Setup” window.

 

If you login to SQL*Plus as user “tbc” and select “object_type” and “object_name” from “all_objects”, then you can see the objects that correspond to the sample application:

 

 

 

Everything Oracle

Home

Everything Oracle

 

Copyright © 2008 PWG Consulting, All Rights Reserved