Setting Up and Configuring Ascential DataStage and MetaStage for EPM

This chapter provides an overview of Ascential DataStage and Metastage setup, and discusses how to:

• Set up DataStage projects

• Configure environmental parameters

• Import *.dsx files

• Create and populate the MetaStage directory (optional)

Understanding Ascential DataStage and MetaStage Setup and Configuration

This section provides an overview of DataStage and MetaStage setup and configuration.

Setting Up DataStage for EPM

In order for Ascential DataStage to work properly with EPM, you must perform the following setup and configuration steps:

• Create projects: Projects are used to import your *.dsx file metadata, which include ETL jobs.

• Setup and configure environmental parameters: Every ETL job uses environmental parameters and you must configure the default parameters.

• Import *.dsx files into projects: You must specify a project to hold your DataStage metadata, including ETL jobs.

Setting Up MetaStage for EPM (Optional)

In order for Ascential MetaStage to work properly with EPM, you must perform the following setup and configuration steps:

• Create a new MetaStage Directory: The MetaStage Directory holds your MetaStage objects.

• Populate the MetaStage Directory with MetaStage objects: You must populate the MetaStage Directory with the appropriate MetaStage objects.

Note. Installing and configuring MetaStage for EPM is optional. You only need to perform these tasks if you plan to use the EPM Metadata Console feature.

Setting Up DataStage Projects

This section discusses considerations regarding the design of your DataStage projects and how to create a project.

Project Structure Considerations

Before you create your DataStage projects, you should consider how many projects you need to create. The number of projects you need depend on the following factors:

• The PeopleSoft source transaction system that you are using.

• The license codes for the Data Marts and/or EPM Applications you have purchased, or plan to implement.

• Disk space management based on storing hash files, log files, and *.dsx files.

• Common jobs necessary to all products (you may wish to create a separate project for the common jobs if you are implementing more than one data mart).

Based on the preceding information, you have the following options:

• Create one project per PeopleSoft source (Enterprise and Enterprise One sources ).

  For example, you can create a project called HCM_EPM90 for bringing data from an Enterprise – HCM source database or you can create a project called E1_EPM90 for bringing data from an Enterprise One source database.

• Create one project for both PeopleSoft sources (Enterprise and Enterprise One).

  For example, you can create a project called E_E1_EPM90 for bringing data from both Enterprise and Enterprise One source transaction databases.

• Create one project per functional warehouse (such as HCM or SCM warehouses).

  For example, you can create a project called CRM_EPM90 to handle all CRM-related Data Marts or EPM applications.

• Create one project for all the functional warehouses.

  For example, you can create a project called EPM90_ALL which will manage ETL jobs relating to all of the functional warehouses.

Because you need to create projects based on the preceding requirements, ensure that you have understood your long-term needs and requirements before creating suitable projects for importing the jobs.

Note. Certain operating systems, such as Unix, have a limit to the number of objects that can be created under one project or directory. Please check your operating system specifications before proceeding with project creation.

Note. There is no naming convention for projects.

Creating Projects

Use the following steps to create DataStage projects:

1. In the DataStage Administrator client, select Start, Programs, Ascential DataStage, DataStage Administrator.

2. In the Attach to DataStage box, enter the following information:

   • Host system: Enter the name of the server machine (for example, Windows, Unix, or Linux) on which the DataStage Server has been installed.

     You can enter localhost if the client and server are installed on the same machine.

   • User name: Enter the user name that is required to log onto the machine on which the server is installed.

     The user name is the Windows login (if the server is running on a Windows platform) or the Administrator user that was used during the install (if the server is running on a Unix platform).

   • Password: Enter the password that is required to log onto the machine on which the server is installed.

   • Omit: Do not select this check box, even if the server is installed on a Windows machine.

3. Click OK.

4. Click the Projects tab to view a list of available projects on the server.

5. Click the Add… button.

   The Add Project box displays.

6. Enter the project name in the Name field . You can specify your own naming convention in this field.

   Remember the project name is case-sensitive and you cannot change the project name after you have imported the dsx files. If you want to update or change the project, you only have the option to create a new project and import the dsx files again. DataStage Administrator automatically appends the project to the default location for a new project.

   Click the Browse button to select another location if the default location is not desirable (due to disk space constraints). Do not select the Create protected project check box as this would cause the project to be read-only.

7. Click OK to create the project on the server.

   This creates an empty project on the DataStage server. The updated list of projects displays after the copy is finished.

8. Repeat steps five through seven to create any additional projects.

9. When you are finished, click Close.

Step 4 - DataStage Administration box with Projects tab selected

Step 5 - Add Project box

See Also

Ascential DataStage Administrator Guide

Configuring Environmental Parameters

This section discusses how to copy the DSPARAMS file and add values for environmental variables. You must configure default environmental parameters for each project you are using, as every job uses a subset of the environmental variables.

Copying the DSPARAMS File

Copying the DSPARAMS file enables you to bypass entering the parameters manually. Use the following steps to copy the DSPARAMS file:

1. Locate the DSPARAMS file using the following path on the installation CD:

   <PSHOME>\SRC\ETL.

2. In DataStage Administrator, navigate to the projects tab to determine the project home directory.

   The project home directory path can be found under Project Pathname field.

3. Select your project name.

4. In Windows Explorer, use the project home directory path to navigate to your DSPARAMS file.

5. Rename the DSPARAMS file located in the project home directory folder to DSPARAMS_old and paste the copied file.

   Now, you can see a DSPARAMS file under this path.

6. Repeat steps one through five for each project you have created.

Step 2 - DataStage Administration box with Projects tab selected.

Adding Values for Environmental Variables

You must add suitable values for each environmental parameter you plan to use. For example, if you are only implementing a functional warehouse, you need not update OWE related parameters.

Use the following steps to add values for environmental variables:

1. In DataStage Administrator, select the Projects tab of the DataStage Administrator box to view a list of available projects on the server.

2. Select the project you would like to configure.

3. Click the Properties button.

   The Project Properties window displays.

4. Click the Environment… button.

   The Environment Variables box displays.

5. In the navigation frame named Categories, select User Defined.

   A dialog box displays prompting you to specify the name of the variable and default value.

6. Enter the default values based on the warehouse setup.

   Ensure you do not include any trailing spaces in the values.

Note. Ensure that you perform a detailed analysis of the various environmental variables that impact your requirements with respect to the project created and the corresponding values. Use defaults wherever required. If you have not provided the correct values for the chosen environmental variables, the related jobs may abort or not work as required.

Step 5 - Project Properties box:

Step 6 - Environment Variables box:

Step 7 - Environment Variables box with user defined selected:

Step 8 - Environment Variables box with sample values:

See Environmental Parameters Information.

See Parameter and Source Data Files Information.

See DataStage Dynamic Relational Stage Technical Bulletin (drsstage.pdf), delivered with the Ascential installation CD.

Importing *.DSX Files

After you have created your projects and configured the project-level environmental parameters, you can import the *.dsx files into the projects. PeopleSoft prepackaged *.dsx files contain server jobs, job sequencers, and other metadata . The prepackaged jobs may belong to any of the following categories:

• Source to OWS

• Source to MDW (for CRM online marketing applications only)

• OWS to OWE

• OWS to MDW

• OWE to MDW

This section discusses preparations for importing *.dsx files and how to import each type of *.dsx file.

See Ascential DataStage Manager Guide for more information on how to import files using the Unix command-line import.

Preparing to Import *.DSX Files

Before you begin importing your *.dsx files, you should be thoroughly familiar with the DSX Files Import Description document located in the appendix of this guide.

See DSX File Import Descriptions.

The DSX Files Import Description document contains the following information:

• The first column provides information on the order in which *.dsx files should be imported.

• The second column indicates whether the *.dsx file corresponds to a specific warehouse/SKU or generic file.

• The third column describes the type of jobs the *.dsx file contains, such as OWS, OWE, and setup.

• The fourth column describes the source system associated with the *.dsx file (such as Enterprise or Enterprise One).

• The fifth column provides the name of the *.dsx file.

• The sixth column provides the location of the *.dsx file on the installation CD.

• The seventh column provides a detailed description of the *.dsx file.

In order to import the *.dsx files, you must be able to link to the Windows File Server from the Ascential DataStage Client.

Note. The *.dsx files are delivered as zip files and must be unzipped before importing.

Note. The *.dsx files can only be installed on a Microsoft Windows client operating system. If you have installed EPM on UNIX, you must access the files from the Windows file server.

Steps to Import a *.DSX File

Use the following steps to import a *.dsx file:

1. In DataStage Manager client, enter the server name or IP address in the Host System field of the Attach to Project box.

   You must also enter a user name and password, and select the project for which you are importing the *.dsx file.

2. Select Import, DataStage Components from the menu.

   The DataStage Repository Importwindow displays.

3. Browse to the *.dsx file you are importing by clicking the Import from file browse button.

   Do not select the Perform usage analysis check box unless you want to perform usage analysis. Selecting this check box increases the time it takes to import the *.dsx files into a project.

4. Select Import All and click OK to import the file.

   Once the import process is complete, the Import Progress window closes.

Step 2 - DataStage Repository Import box:

Importing *.DSX Files for EPM Applications Only

If you are planning to implement only some of the EPM Application(s), the following sections provide a guide to *.dsx files you need.

If your EPM system supports only an Enterprise source, you should import:

• COMMON_UTILITIES_E_E1.dsx

• COMMON_ E.dsx

• Any one or all of the following dsx files, depending upon the number of PeopleSoft source transaction systems that you may have:

  • WHR_OWS_E.dsx (For an HCM and LM Enterprise Source Database)

  • WCR_OWS_E.dsx (For an CRM Enterprise Source Database)

  • WSC_OWS_E.dsx (For an SCM Enterprise Source Database)

  • WFN_OWS_E.dsx (For an FMS Enterprise Source Database)

  • OWE_E.dsx

If your EPM system supports only an Enterprise One source, you should import:

• COMMON_UTILITIES_E_E1.dsx

• COMMON_E1.dsx

• OWS_E1.dsx (For an EnterpriseOne Source)

• OWE_E1.dsx

If your EPM system supports both Enterprise and Enterprise One sources, you should import:

• COMMON_UTILITIES_E_E1.dsx

• COMMON_ E.dsx

• COMMON_E1.dsx

• Any one or more or all of the following dsx files, depending upon the number of source transaction systems that you may have:

  • WHR_OWS_E.dsx (For an HCM and LM Enterprise Source Database)

  • WCR_OWS_E.dsx (For an CRM Enterprise Source Database)

  • WSC_OWS_E.dsx (For an SCM Enterprise Source Database)

  • WFN_OWS_E.dsx (For an FMS Enterprise Source Database)

  • OWS_E1.dsx

  • OWE_E.dsx

  • OWE_E1.dsx

Importing *.DSX Files for Functional Warehouses Only

If you are planning to implement only functional warehouses, this section provides a guide to *.dsx files you need.

If your EPM system supports only an Enterprise source, you should import:

• Common_Utilities_E_E1.dsx

• Common_ E.dsx

If your EPM system supports only an Enterprise One source, you should import:

• Common_Utilities_E_E1.dsx

• Common_E1.dsx

• OWS_E1.dsx (For an Enterprise One source)

If your EPM system supports both Enterprise and Enterprise One sources, you should import:

• Common_Utilities_E_E1.dsx

• Common_ E.dsx

• Common_E1.dsx

• OWS_E1.dsx (For an Enterprise OnesSource)

Each functional warehouse (HCM Warehouse or FMS Warehouse, for example) requires a unique list of *.dsx files to import.

See DSX File Import Descriptions.

Creating and Populating the MetaStage Directory (Optional)

This section discusses how to create and populate a MetaStage directory.

Creating the MetaStage Directory

After you have installed MetaStage and the PeopleSoft MetaBrokers, and configured the database on which the metadata is stored, you must create a new MetaStage Directory using Directory Administrator.

Note. Before launching the Directory Administrator you may need to configure the user environment variable for ClassPath if one is being used (under Start, Settings, Control Panel, System). When you installed Ascential MetaStage, an entry was added to the system environment variable for ClassPath. For example, C:\WINNT\java\classes\metastage.zip. You must manually add this entry to the user variable, otherwise MetaStage will not launch. No action is required is there is no user environment variable for ClassPath.

Creating a New MetaStage Directory

Use the following steps to create a new MetaStage directory:

1. In Directory Administrator, click New to create a new directory.

2. In the Select Data Source box, select the Machine Data Source tab.

3. On the Machine Data Source box click New to start the Create New Data Source wizard.

4. Select System Data Sourceand click Next.

   The wizard displays a list of available ODBC drivers.

5. Select the appropriate driver for the database hosting the new directory, and click Next.

   The wizard displays details of the data source you have selected. Appropriate drivers are:

   • SQL Server for a SQL Server directory.

   • Oracle ODBC Driver for an Oracle directory.

   • IBM DB2 ODBC Driver for a DB2 directory.

6. Click Finish.

   The Setup box for the selected data source appears.

If you selected MicroSoft SQL Server, the Create a New Data Source to SQL Server wizard appears. This wizard guides you through the process of setting up the data source. Use the following steps to complete the setup of the MetaStage Directory:

1. Enter data for the following fields:

   • Name: Specify the data source name used to connect to the data source.

   • Server: Specify the name of the SQL Server.

     If you are configuring the directory on the same machine that hosts it, choose (local).

2. Specify that MetaStage users should enter a login ID and password for SQL Server when logging in.

   When the wizard asks you how you want to supply authentication details, do not select the default. Select With SQL Server Authentication using login ID and password entered by the user.

3. When you reach the last wizard screen, click Finish.

   The ODBC Microsoft SQL Server Setup dialog box appears with details of the settings for the new data source.

4. You can now supply a user name and password to connect to the SQL Server data source that you set up.

   The directory is then created and populated with a basic MetaStage schema.

If you selected MicroSoft SQL Server, the Create a New Data Source to SQL Server wizard appears. Use the following steps to complete the setup of the MetaStage Directory:

1. Enter data for the following fields (you can accept the default values for all others):

   • Data Source Name: Enter the name of the data source.

   • Description: Enter a description of the data source.

   • Service Name: Enter the name of the connection created in the Oracle Net8 Assistant.

   • UserID: Enter the Oracle ID that owns the directory.

2. Click OK.

3. Click OK in the Select Data Source dialog box.

   The Oracle ODBC Driver Connect dialog box appears, requesting connection details for the database.

4. Supply the password for connecting to the database.

5. Click OK.

   The TableSpace for Oracle MetaStage Directory dialog box appears.

6. In the TableSpace for Oracle MetaStage Directory dialog box, do one of the following:

   • To specify the tablespace, follow the procedure in "Specifying the Tablespace for an Oracle Database" on page 4-8 of the MetaStage Administrator’s Guide.

   • To connect to the database without specifying a tablespace, click OK. The Directory Administrator connects to the database, creates the directory, and populates it with a basic MetaStage schema.

If you selected IBM DB2 ODBC Driver, the IBM DB2 ODBC Driver - Add dialog box appears. Use the following steps to complete the setup of the MetaStage Directory:

1. Enter data for the following fields:

   • Data source name: Enter the name of the data source.

   • Database alias: Enter the DB2 database to which you want to connect.

   • Description: Enter a description of the data source.

2. Click OK.

   The Connect to DB2 Database dialog box appears requesting connection details for the database.

3. Supply the following information:

   • User ID: Enter the name to use for connecting to the database.

   • Password: Enter the password for connecting to the database.

   • Connection mode: Accept the default setting of Share.

4. Click OK.

   The TableSpace for DB2 MetaStage Directory dialog box appears.

5. In the TableSpace for DB2 MetaStage Directory dialog box, do one of the following:

   • To specify the tablespace, follow the procedure in "Specifying the Tablespace for a DB2 Database" on page 4-11 of the MetaStage Administrator’s Guide.

   • To connect to the database without specifying a tablespace, click OK. The Directory Administrator connects to the database, creates the directory, and populates it with a basic MetaStage schema.

Populating the MetaStage Directory

After you have successfully defined the data source that hosts the MetaStage directory and connected to it, the Directory Administration populates the directory with basic MetaStage objects. The Directory Administrator dialog box displays the progress of the population. After the directory has been created close it