Setting Up the PMF Data Mart in Your RDBMS

In this section:

How to:

Reference:

After completing the PMF install program, the PMF application is prepared for use on your Reporting Server.

As previously mentioned in this documentation, PMF also requires a connection to a physical data mart, which must reside in an RDBMS. PMF uses this data mart as the central place where it stores data for Measures and Dimensions, the information that controls these, and other critical data, such as Feedback, Tasks, the specifications for Alerts, and Dashboard preferences.


Top of page

x
General Procedure for Setting Up a PMF RDBMS

To set up the PMF data mart, you need to:

  1. Set up, or gain access to, an existing RDBMS resource on which you can set up a PMF data mart. PMF supports hosting on MySQL, MS SQL Server, Oracle, Teradata, Information Builders HyperStage, or IBM DB2. If you need the data mart to be different from one of these, contact your Information Builders support representative.
  2. Have security credentials for the RDBMS that are at a sufficient level to allow the following options:
    • Create, alter, and drop of tables, views, and procedures.
    • For Oracle, create and drop public synonyms, triggers, and sequences. You also have the ability to enable and disable triggers. Note that the drop public synonym must be explicitly granted.
    • Select, update, and delete for all tables, and select on all views.

    Test and confirm the credentials you have given these rights, before attempting to use the credentials to create the PMF Data mart in your RDBMS.

  3. If necessary, have your DBA review the provided, packaged PMF SQL DDL code that creates and sets up the PMF data mart.
  4. From your SQL tool of choice, either run the provided, packaged PMF files yourself or have your DBA run them and save the SQL output files from the process, at full error reporting levels so it will be possible to see any error messages.
  5. Review the SQL output files from the process and confirm all ran properly with no displayed errors.

    Note: For SQL Server or MySQL, the PMF installer will correctly configure the profile for the default tenant. For all others, this is a manual step.

  6. Make sure connectivity software (for example, the database connection client) for your Reporting server has been properly set up. Make sure that your tenant profile is properly configured for PMF.
  7. Make sure the WebFOCUS RDBMS connection called pmf_system has been properly configured for connection to the RDBMS.
  8. Log into PMF as an administrator and resynch the PMF metadata from the newly connected data mart. If necessary, enter any required RDBMS parameters.
  9. If desired, perform a snapshot restore of demonstration data for PMF.

Top of page

x
Reference: Settings for Data Mart Build and Upgrade

The following system settings are required by certain supported RDBMSs to enable various PMF functions. These settings will vary from one RDBMS to another.

Setting

Controls

PMF_DB_OWNER

The configured owner or schema of the database that contains the PMF data mart.

PMF_DB_TABLESPACE

The defined and configured tablespace for the database.

PMF_DB_INDEXSPACE

The defined and configured index space for the database.

During installation, the PMF installer prompts for none, one, or all of these setting depending on the database. After the install is completed, PMF updates the PMF settings table with the confirmed values that you provided.

Important:



x
Procedure: How to Build a SQL Server Data Mart Using DDL

If you ran the PMF installer and specified SQL server, PMF is already configured with a SQL Server database named pmf_base.

To create a new SQL Server data mart for PMF, perform the following steps:

  1. Start your SQL tool for MS SQL Server. Most typically, DB users use Microsoft® provided SQL Server Management Studio. Make sure to log in to this tool using the Owner ID that has all proper rights to create, read, and drop tables, views and procedures.
  2. Check your MS SQL Server owner name for the data mart. PMF currently requires the default DBO.
  3. Create the new database in your MS SQL Server environment using SQL Server Management Studio.
  4. In the SQL Server Management Studio console, run the provided SQL DDL called sqlmss.sql. This script is located on the WebFOCUS Reporting Server in /ibi/apps/pmfdbms/Create_Database. The DDL script creates and populates the new data mart with the minimum amount of data required to start PMF.
  5. Personally review the SQL output in SQL Server Management Studio from the DDL process and confirm all ran properly with no displayed errors. If there were errors, double-check that your DB Owner name has the proper rights to create, read, and drop tables, views and procedures, and that the physical connection from SQL Server Management Studio to your RDBMS is correct.
  6. Double-check that you have any proper DB Client software installed on your WebFOCUS Reporting Server, as needed, and that the physical connection to the RDBMS server and database has been set up in that client.
  7. Double-check that an adapter connection called pmf_system has been set up for MS SQL Server in the WebFOCUS Reporting Server console for the Reporting Server(s) used for PMF. The connection should be in the tenant profile.
  8. Log into PMF as an administrator. You should be prompted to resynch PMF. Perform the resynch. If there are any issues, check to make sure you followed every aspect of this procedure. If it was followed perfectly and there are still issues, see Troubleshooting Common Issues.
  9. If needed, to provide sample data for PMF, or to restore your previous PMF data from another installation of PMF, restore a snapshot into the data mart. For more information, see How to Restore a Snapshot.

Top of page

x
Procedure: How to Build an Oracle Data Mart Using DDL

Note the following before performing the procedure:

To create a new Oracle data mart for PMF, perform the following steps:

  1. Create the new database in your Oracle environmental using your standard toolset.
  2. Create table spaces for your tables and indexes. Depending on your requirements, you can use the same tablespace for both your tables and indexes. You can also use separate tablespaces for each.
  3. Create your Oracle owner name for the data mart. We recommend using PMF for simplicity, but you can use a different name if required for your standards.
  4. Set up the Oracle SID.
  5. Edit the sqlora.sql file located in the \ibi\apps\pmfdbms\Create_Database directory and set the proper values for the three environmental variables. These values must be identical to those used in steps 2 and 3. It is recommended that you use upper standard ANSI characters only.

    For details about variable settings, see Settings for Data Mart Build and Upgrade.

  6. Start your Oracle interactive or GUI tool. For Oracle 11 and higher, we recommend using Oracle Developer. For earlier releases, you can use SQL*Plus.
  7. In the Oracle session, run sqlora.sql.

    The DDL script creates and populates the new data mart with the minimum amount of data required to start PMF.

  8. Finalize the connection setup for the adaptor needed in your User Profile on the WebFOCUS Reporting Server. This will be tenant specific if you have a Multi-Tenant environment. It is recommended that you use the sample tenant profile delivered with PMF.

    The sample profile is located in pmf_base.prf in the /apps/pmfdemo folder. You must change line 5 to read:

    -SET &RDBMS_PRF=’SQLORA’;

    You must also adjust the APP MAP commands on lines 9-17 to match the physical location of the folders on your server machine.

  9. If you are creating a brand new data mart, it will prompt you for the Schema, Tablespace, and Indexspace values, as shown in the following image.

    PMF automatic upgrade

    Once the values are entered, the Oracle system settings will be verified for legitimacy in your database. You cannot continue with the installation or upgrade if any of these values are not legitimate.

Note:


Top of page

x
Procedure: How to Create a New DB2/UDB Data Mart

Establishing and maintaining a DB2/UDB database is a complex task that requires knowledge of DB2/UDB and of any site specific standards. The following procedure should be performed by an experienced database administrator.

  1. You will need to attach your RDBMS query tool to your DB2/UDB system with an ID that has all necessary privileges to perform the following steps. This includes, but is not limited to, create database, schema, tables, views, indexes, procedures, and so on. Make sure that such an ID is available.
  2. Create the new database and schema in your DB2/UDB environmental using your standard toolset.

    It is recommended that you use PMF for the schema name but a different name can be used if required for your standards.

  3. Create table spaces for your tables and indexes using your standard toolset.

    It is recommended that you use PMF_DATAMART_DATA for the tablespace for the tables and PMF_DATAMART_INDEX for the tablespace for your indexes. Different names can be used if required for your standards.

    Note: Sample code to create the database, schema, and table spaces is in the supplied script db2_env_defaults.sql which is located in the apps/pmfdbms/Create_Database folder.

  4. Run the supplied script db2.sql, which is also in the apps/pmfdbms/Create_Database folder.

    If you are not using the recommended schema or tablespace names, you will have to first edit the db2.sql file and substitute the proper names where ever they are used. View the output listing and verify that all statements completed successfully.

    NOTE: It is recommended that you use the standard DB2 GUI toolkit - DB2 Control Center and/or DB2 Command Editor - to interact with your DB2 database. Alternatively, you may run the supplied sample scripts after making any necessary changes in command line mode. Here is what a typical DB2 command line session looks like:

    C:\>db2 connect to pmf user db2admin
    Enter current password for db2admin:
       Database Connection Information
    Database server        = DB2/NT64 9.7.0
    SQL authorization ID   = DB2ADMIN
    Local database alias   = PMF
    C:\> db2 -vf C:\ibi\apps\pmfdbms\Create_Database\db2.sql   
    -z C:\ibi\apps\pmfdbms\Create_Database\db2.out +o

    The –z parameter along with the +o command effectively routes the session output to the file db2.out for inspection.

  5. Using the WebFOCUS Server Administration console, create and test your pmf_system connection to the database you just created.
  6. Using the WebFOCUS Server Administration console, open your Server Tenant Profile. If you are in single tenant mode (the default) the profile is pmf_base.prf. If you are in multi-tenant mode, see Installing PMF for Multi-Tenancy for details on how to identify your profile.

    Perform steps a and b:

    1. Ensure that the pmf_system connection is the default DB2 connection.
    2. Enter this code after your DB2 connection string:
      -SET &&PMF_DB_OWNER_FOR_UPGRADE = ‘<schema_name>’;

      where:

      schema_name

      Is the name you chose in step 2.

    3. Finalize the connection setup for the adaptor needed in your User Profile on the WebFOCUS Reporting Server. This will be tenant specific if you have a Multi-Tenant environment. It is recommended that you use the sample tenant profile delivered with PMF.

      The sample profile is located in pmf_base.prf in the /apps/pmfdemo folder. You must change line 5 to read:

      -SET &RDBMS_PRF=’DB2’;

      You must also adjust the APP MAP commands on lines 9-17 to match the physical location of the folders on your server machine.

  7. Login to your PMF application using the supplied PMF administrator ID pmfadmin. A form similar to the following image prompts you for the Schema, Tablespace for tables, and Table Space for Indexes (for example, Indexspace).

    PMF automatic upgrade

    Enter the same values you used when running the db2.sql script in the previous step. You must enter proper values, otherwise PMF will not allow further processing

  8. Remove the -SET statement from your tenant profile (used in step 6) to avoid interfering with other applications.

Top of page

x
Procedure: How to Create a New DB2/zOS Data Mart

The procedures for creating a DB2/zOS database are similar to those in the previous procedure for DB2/UDB, but note the following:

  1. The DB and tablespace creation must be run directly on the z/OS system. Sample SQL to do this is located in zosv8_env_defaults.sql in /apps/pmfdbms/Create_Database supplied with PMF.
  2. The DDL must be run to create the stored procedures directly on the mainframe. The SQL to do this is located in zosv8_procedures.sql in /apps/pmfdbms/Create_Database supplied with PMF. The DB Owner ID needs select access to the SYSIBM.SYSTABLES catalog table.
  3. The DB Owner ID must have full capabilities on objects owned by itself, for example, DBADM.
  4. The DB Owner ID must have permissions to run the two stored procedures. Also, if the procedures are prepared as external SQL procedures (this is the only option in DB2 z/OS V8), then the id must also have access to the packages associated with the procedures. Refer to the CALL statement section in the DB2 z/OS SQL Reference for information on the authorization requirements.
  5. To create and populate the PMF data mart, you must run the supplied script (zosv8.sql) from a DB2 client. The minimum client configuration is DB2 Connect.
    • If you are using DB2 Connect release 8.x, make sure you have applied FixPak 18 (also known as Version 8.2 FixPak 11) from IBM. You may obtain this at http://www-01.ibm.com/support/docview.wss?rs=71&uid=swg24024075.
    • If you connect to the z/OS database from the client using an ID other than the owner ID, then that ID must have OS privileges to impersonate that actual owner ID.
  6. If you create the pmf_system connection using the DB Owner for authentication inside of WebFOCUS, you do not need to make any changes (temporary or otherwise) to edasprof.
  7. If the WebFOCUS connection is not using the DB Owner to connect, you must apply z/OS permissions (for example, RACF) on the connect ID in order to:
    • Be able to use the attach ID.
    • Be able to use the stored procedures.
  8. You may need to edit the lsched.acx metadata files. lsdched is a cluster join used by PMF in the PMF interface to the Report Caster. The issue is that there can be multiple ReportCaster databases on any given LPAR and there may be no way to point to a database without editing ldsched.acx to qualify the tables.

Top of page

x
Procedure: How to Create a PMF Data Mart in MySQL

Note the following before performing the procedure:

To create a new data mart for PMF, perform the following steps:

  1. Start MySQL Workbench and connect to the RDBMS server.
  2. In the MySQL Workbench session, create your MySQL schema name for the data mart. We recommend using pmf for simplicity, but you can use a different name if required for your standards, or if required for Multi-Tenancy.

    Important: Use lowercase standard ANSI characters to name this schema.

  3. Create the new empty database with the defined schema name as the owner (recommended).
  4. Check over the DDL for creating the default databases and MySQL owner name (schema) can be found in the sqlmysql.sql file, which is located in the approot/pmfdbms/Create_Database folder in the standard PMF installation. Make any edits necessary. For example, you might need to change the schema name variable to the actual setting for your schema.
  5. Once changes are made, run the DDL SQL to create the database structure for the MySQL component. Ensure that no errors occur. If you do encounter errors, make sure your schema has DBO level access, or at minimum can create and destroy tables, views and triggers for the database you created. This script creates and populates the new data mart with the minimum amount of data required to start using PMF.
  6. Finalize the connection setup for the adaptor needed in your User Profile on the WebFOCUS Reporting Server. This will be tenant specific if you have a Multi-Tenant environment. It is recommended that you use the sample tenant profile delivered with PMF.

    The sample profile is located in pmf_base.prf in the /apps/pmfdemo folder. You must change line 5 to read:

    -SET &RDBMS_PRF=’SQLMYSQL’;

    You must also adjust the APP MAP commands on lines 9-17 to match the physical location of the folders on your server machine.

  7. Using the WebFOCUS Server Administration console, create and test your pmf_system connection to the database you just created.
  8. Login to your PMF application using the supplied PMF administrator ID pmfadmin. A form prompts you for the schema.

    Enter the schema name you created in the prior steps, along with the table and index tablespace names.


Top of page

x
Procedure: How to Create a PMF Data Mart in Teradata

Note the following before performing the procedure:

To create a new data mart for PMF, perform the following steps:

  1. Create the new database in your Teradata environmental using your standard toolset.
  2. Start your Teradata console.
  3. Create your Teradata owner name for the data mart. We recommend using PMF for simplicity, but you can use a different name if required for your standards.

    Important: It is recommended that you use upper standard ANSI characters to name this only.

  4. Open and edit the sqldbc.sql file located in the approot/pmfdbms/Create_Database folder on your system, and set the proper values for schema name variables. These values must be identical to those used in step 3. It is recommended that you use upper standard ANSI characters only.
  5. In the Teradata console session, run [ddl name].
  6. After completing the edits, run the DDL SQL script. This script creates and populates the new data mart with the minimum amount of data required to start using PMF.
  7. Finalize the connection setup for the adaptors needed in your User Profile on the WebFOCUS Reporting Server. This will be tenant specific if you have a Multi-Tenant environment. The typical configuration for the standard shipped PMF tenant in HyperStage split cube mode will look like this:
    -SET &&PM_TNT_NAME_PRF = 'pmf_base' ;
    -SET &RDBMS_PRF      = 'sqldbc' ;
    -SET &RDBMS_CUBE_PRF = 'sqlhyp' ;
    -SET &PMF_DB_SPLIT_MODE_PRF = 'OFF' ;
     
    APP MAP pmfdata              "C:\ibi\apps\pmf_tenants\pmfdata"
    APP MAP pmfdata_tenant      
    "C:\ibi\apps\pmf_tenants\pmf_base\pmfdata_tenant"
     
    APP MAP pmfdb_system        
    "C:\ibi\apps\pmfdbms\pmfdb_system\pmfdb_system_&RDBMS_PRF"
    APP MAP pmfdb_system_tenant 
    "C:\ibi\apps\pmf_tenants\pmf_base\pmfdb_system\pmfdb_system_&RDBMS_PRF"
     
    APP MAP pmfdb_cube          
    "C:\ibi\apps\pmfdbms\pmfdb_cube\pmfdb_cube_&RDBMS_CUBE_PRF"
    APP MAP pmfdb_cube_tenant   
    "C:\ibi\apps\pmf_tenants\pmf_base\pmfdb_cube\pmfdb_cube_&RDBMS_CUBE_PRF"
    APP MAP pmf_custom_tenant   
    "C:\ibi\apps\pmf_tenants\pmf_base\pmfcustom"
     
    -SET &APP_PATH_START = 'pmf_custom_tenant pmf_custom' ;
    -SET &APP_PATH_CUBE  = IF (&PMF_DB_SPLIT_MODE_PRF EQ 'OFF') THEN ' '
    ELSE 'pmfdb_cube_tenant pmfdb_cube' ;
    -SET &APP_PATH_END   = 'pmfdata_tenant pmfdata pmfdb_system_tenant
    pmfdb_system mainstreet' ;
    APP PREPENDPATH &APP_PATH_START &APP_PATH_CUBE &APP_PATH_END
     
    -*  Followed by ENGINE statements
    SQL SQLDBC SET TRANSACTION BTET
    ENGINE SQLDBC SET CONNECTION_ATTRIBUTES [connection attributes]

Top of page

x
Procedure: How to Create a PMF Data Mart in Information Builders HyperStage

Note the following before performing the procedure:

To create a new data mart for PMF, perform the following steps:

  1. Start MySQL Workbench and connect to the RDBMS server.
  2. In the MySQL Workbench session, create your MySQL schema name for the data mart. We recommend using PMF for simplicity, but you can use a different name if required for your standards, or if required for Multi-Tenancy.

    Important: It is recommended that you only use uppercase standard ANSI characters to name this schema.

  3. Create the new empty database with the defined schema name as the owner (recommended).
  4. Check over the DDL for creating the default databases and MySQL owner name (schema) can be found in the sqlmysql.sql file, which is located in the approot/pmfdbms/Create_Database folder in the standard PMF installation. Make any edits necessary. For example, you might need to change the schema name variable to the actual setting for your schema.
  5. Once changes are made, run the DDL SQL to create the database structure for the MySQL component. Ensure that no errors occur. If you do encounter errors, make sure your schema has DBO level access, or at minimum can create and destroy tables, views and triggers for the database you created.
  6. Configure the adaptor for PMF to point to the MySQL database instance. Test and burn in PMF to ensure that it works with MySQL standalone. If you encounter any errors, debug and fix them before proceeding.
  7. Start the HyperStage console, and connect to your HyperStage server.
  8. Open and edit the sqlhyp_cube.sql file located in the approot/pmfdbms/Create_Database folder on your system and set the proper values for the three environmental variables. It is recommended that you use upper standard ANSI characters only.
  9. After completing the edits, run the DDL SQL script. This script creates and populates the new data mart with the minimum amount of data required to start using PMF.
  10. Finalize the connection setup for the adaptors needed in your User Profile on the WebFOCUS Reporting Server. This will be tenant specific if you have a Multi-Tenant environment. The typical configuration for the standard shipped PMF tenant in HyperStage split cube mode will look like this:
    -SET &&PM_TNT_NAME_PRF = 'pmf_base' ;
    -SET &RDBMS_PRF      = 'sqlmysql' ;
    -SET &RDBMS_CUBE_PRF = 'sqlhyp' ;
    -SET &PMF_DB_SPLIT_MODE_PRF = 'ON' ;
      
    APP MAP pmfdata              "C:\ibi\apps\pmf_tenants\pmfdata"
    APP MAP pmfdata_tenant      
    "C:\ibi\apps\pmf_tenants\pmf_base\pmfdata_tenant"
      
    APP MAP pmfdb_system        
    "C:\ibi\apps\pmfdbms\pmfdb_system\pmfdb_system_&RDBMS_PRF"
    APP MAP pmfdb_system_tenant 
    "C:\ibi\apps\pmf_tenants\pmf_base\pmfdb_system\pmfdb_system_&RDBMS_PRF"
      
    APP MAP pmfdb_cube          
    "C:\ibi\apps\pmfdbms\pmfdb_cube\pmfdb_cube_&RDBMS_CUBE_PRF"
    APP MAP pmfdb_cube_tenant   
    "C:\ibi\apps\pmf_tenants\pmf_base\pmfdb_cube\pmfdb_cube_&RDBMS_CUBE_PRF"
      
    APP MAP pmf_custom_tenant   
    "C:\ibi\apps\pmf_tenants\pmf_base\pmfcustom"
      
    -SET &APP_PATH_START = 'pmf_custom_tenant pmf_custom' ;
    -SET &APP_PATH_CUBE  = IF (&PMF_DB_SPLIT_MODE_PRF EQ 'OFF') THEN ' '
    ELSE 'pmfdb_cube_tenant pmfdb_cube' ;
    -SET &APP_PATH_END   = 'pmfdata_tenant pmfdata pmfdb_system_tenant
    pmfdb_system mainstreet' ;
      
    APP PREPENDPATH &APP_PATH_START &APP_PATH_CUBE &APP_PATH_END
     
    -*  Followed by ENGINE statements
    ENGINE SQLMYSQL SET CONNECTION_ATTRIBUTES [connection attributes]
    ENGINE SQLHYP SET CONNECTION_ATTRIBUTES [connection attributes]

WebFOCUS