Install and Configure the Data Warehouse

The Data Warehouse (DWH) is optimized specifically for reporting and analytics. The Data Warehouse is installed on a separate schema from the  database schema, and is populated at scheduled intervals by the Load Data Warehouse job.
ccppmop143
The Data Warehouse (DWH) is optimized specifically for reporting and analytics. The Data Warehouse is installed on a separate schema from the 
Clarity Project and Portfolio Management (PPM)
 database schema, and is populated at scheduled intervals by the Load Data Warehouse job.
The Data Warehouse includes data on the following 
Clarity Project and Portfolio Management (PPM)
 items:
  • Investments, ideas, projects, programs, applications, assets, other work, products, services
  • Teams, tasks, financial plans, hierarchies, risks/issues/change requests, status reports (PMO Accelerator)
  • Timesheets and notes, time entries and notes
  • Financial transactions
  • Resources
  • OBS and portfolio filters
The Data Warehouse consists of a set of dimension tables and fact tables.
Dimension Tables 
store the following data:
  • Fields by which you want to view the data.
  • Major objects and subobjects in 
    Clarity Project and Portfolio Management (PPM)
    .
  • Dimension and localized dimension tables
Fact Tables 
store the following data:
  • Numerical data that you view or aggregate by dimension
  • Summary and period fact tables
The following image shows how data is transferred from the 
Clarity Project and Portfolio Management (PPM)
 database to the Data Warehouse.
Data_Warehouse_Architecture
Data_Warehouse_Architecture
The Data Warehouse has the following characteristics:
  • The database is modeled on the STAR schema and includes the Investment, Resource, Portfolio, Timesheet, and Project objects.
  • Consistent naming conventions, formats, and encoding structures are used throughout.
  • The database is nonvolatile because it is a separate schema from the 
    Clarity Project and Portfolio Management (PPM)
     database.
  • Predefined time slices that can be configured are included.
The following image shows how a system administrator sets up the Data Warehouse.
 
Install the Database Software
See the documentation from Oracle or Microsoft for the appropriate instructions.
Configure the Data Warehouse Database (Oracle)
Complete the following tasks to configure Oracle for use with the Data Warehouse:
Set the Required Data Warehouse Oracle Database Parameters
Set the required parameters in the following table for your new database:
Name
Value
Character Set
AL32UTF8
National Character Set
UTF8/AL16UTF16
nls_date_format
YYYY-MM-DD HH24:MI:SS
nls_sort
BINARY
nls_comp
BINARY
query_rewrite_enabled
true
Cursor_sharing
FORCE
InitNIKU.ORA Parameter File Samples with Recommended Settings
The following initNIKU.ora file uses Automatic Memory Management (AMM). The following samples are the recommended parameter settings, but you can adjust the settings according to your environment. If OLTP and the Data Warehouse schema are on the same database server, consider setting up a different TEMP tablespace for the Data Warehouse.
Windows
*.audit_file_dest='C:\apps\oracle\admin\niku\adump' *.audit_trail='db' *.compatible='11.2.0' *.control_files='C:\apps\oracle\oradata\niku\control01.ctl','C:\apps\oracle\oradata\niku\control02.ctl' *.cursor_sharing='FORCE' *.db_block_size=8192 *.db_domain='' *.db_name='niku' *.diagnostic_dest='C:\apps\oracle' *.job_queue_processes=20 *.memory_max_target=16G *.memory_target=12G *.nls_comp='binary' *.nls_date_format='YYYY-MM-DD HH24:MI:SS' *.nls_sort='binary' *.open_cursors=1000 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=1000 *.session_cached_cursors=1000 *.sga_max_size=0 *.sort_area_size=0  *.star_transformation_enabled='TRUE' *.streams_pool_size=512M
Linux (Non-Windows)
*.compatible='11.2.0' *.control_files='/niku/oracle/oradata/niku/control01.ctl','/niku/oracle/oradata/niku/control02.ctl','/niku/oracle/oradata/niku/control03.ctl' *.core_dump_dest='/niku/oracle/admin/niku/cdump' *.cursor_sharing='FORCE' *.db_block_size=8192 *.db_cache_size=16777216 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='niku' *.diagnostic_dest='/niku/oracle/admin/niku/udump' *.filesystemio_options='SetAll' *** for non ASM instances. *.job_queue_processes=20 *.memory_max_target=16G *.memory_target=12G *.nls_comp='binary' *.nls_date_format='YYYY-MM-DD HH24:MI:SS' *.nls_sort='binary' *.open_cursors=1000 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=1000 *.session_cached_cursors=1000 *.sga_max_size=0 *.sga_target=0 *.sort_area_size=0 *.streams_pool_size=512M  *.star_transformation_enabled='TRUE' *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *** The memory target and tablespace sizes can be adjusted depending upon the customer implementation. If customers are using Linux Huge pages, the DBA can disable AMM and use SGA and PGA instead, until Oracle supports AMM and Linux Huge pages.   SQLNET.ora parameters: NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) SQLNET.INBOUND_CONNECT_TIMEOUT = 0 SQLNET.SEND_TIMEOUT = 600 (Applicable for Oracle 11.2.0.4, Oracle Bug 18841764)
Estimate the Data Warehouse Size
The following view is provided in the 
Clarity Project and Portfolio Management (PPM)
 schema to facilitate estimating the approximate size for the Data Warehouse schema. We recommend that you run this query after setting an Entity and the Timeslice job is run on 
Clarity Project and Portfolio Management (PPM)
 . The estimate is also provided at the end of an upgrade in the postcheck-results.html file.
SELECT ESTIMATED_DWH_SIZE_IN_GB FROM DWH_ESTIMATE_SIZE_V
Create the Required Data Warehouse Oracle Tablespaces (Sample Script)
To create the required tablespaces for the Data Warehouse to work with Oracle, use the following script and adjust the size as appropriate. We recommend that you closely monitor the amount of disk space. The Oracle software and database instance must already be created. You can add data files to each tablespace. See the Oracle documentation to create larger block size tablespaces for the Data Warehouse.
The schema in the backup is PPM_DWH, and the tablespaces are shown in the following example:
  • DWH_PPM_DATA_DIM
  • DWH_PPM_DATA_FACT
  • DWH_PPM_INDX_DIM
  • DWH_PPM_INDX_FACT
CREATE TABLESPACE "DWH_PPM_DATA_DIM" DATAFILE '<ORACLE_BASE>\ORADATA\<SID>\DWH_PPM_DATA_DIM.DBF' SIZE 2G REUSE AUTOEXTEND ON NEXT 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "DWH_PPM_DATA_FACT" DATAFILE '<ORACLE_BASE>\ORADATA\<SID>\DWH_PPM_DATA_FACT.DBF' SIZE 2G REUSE AUTOEXTEND ON NEXT 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "DWH_PPM_INDX_DIM" DATAFILE '<ORACLE_BASE>\ORADATA\<SID>\DWH_PPM_INDX_DIM.DBF' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "DWH_PPM_INDX_FACT" DATAFILE '<ORACLE_BASE>\ORADATA\<SID>\DWH_PPM_INDX_FACT.DBF' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Example: Creating Tablespaces
ORACLE_BASE= c:\apps\oracle SID=NIKU CREATE TABLESPACE "DWH_PPM_DATA_DIM" DATAFILE 'C:\APPS\ORACLE\ORADATA\NIKU\DWH_PPM_DATA_DIM.DBF' SIZE 2G REUSE AUTOEXTEND ON NEXT 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "DWH_PPM_DATA_FACT" DATAFILE 'C:\APPS\ORACLE\ORADATA\NIKU\DWH_PPM_DATA_FACT.DBF' SIZE 2G REUSE AUTOEXTEND ON NEXT 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "DWH_PPM_INDX_DIM" DATAFILE 'C:\APPS\ORACLE\ORADATA\NIKU\DWH_PPM_INDX_DIM.DBF' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "DWH_PPM_INDX_FACT" DATAFILE 'C:\APPS\ORACLE\ORADATA\NIKU\DWH_PPM_INDX_FACT.DBF' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Set the Required Data Warehouse Oracle User Privileges
The following sample script shows the required privileges for a 
Clarity Project and Portfolio Management (PPM)
 schema user. (DEFAULT TABLESPACE DWH_PPM_DATA_DIM):
CREATE USER PPM_DWH IDENTIFIED BY PPM_DWH DEFAULT TABLESPACE DWH_PPM_DATA_DIM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT; GRANT CREATE SESSION TO PPM_DWH; GRANT ALTER SESSION TO PPM_DWH; GRANT CREATE TABLE TO PPM_DWH; GRANT CREATE TRIGGER TO PPM_DWH; GRANT CREATE VIEW TO PPM_DWH; GRANT CREATE MATERIALIZED VIEW TO PPM_DWH; GRANT CREATE SYNONYM TO PPM_DWH; GRANT QUERY REWRITE TO PPM_DWH; GRANT CREATE PROCEDURE TO PPM_DWH; GRANT UNLIMITED TABLESPACE TO PPM_DWH; GRANT CREATE DATABASE LINK TO PPM_DWH; GRANT CREATE JOB TO PPM_DWH; GRANT CONNECT TO PPM_DWH; GRANT RESOURCE TO PPM_DWH;
Import the Data Warehouse Oracle Database Backup Image (Oracle)
The Oracle database backup that is provided with the Data Warehouse contains the complete schema that is needed for a new installation. The schema in the backup is PPM_DWH, and the tablespaces are:
  • DWH_PPM_DATA_DIM (default)
  • DWH_PPM_DATA_FACT
  • DWH_PPM_INDX_DIM
  • DWH_PPM_INDX_FACT
If a schema or tablespace remapping is desired, use the Datapump options. For complete information about database import operations and the available options for using the Oracle Datapump, search the Oracle website.
Follow these steps:
  1. In the 
    Clarity Project and Portfolio Management (PPM)
     installation media, locate the Oracle PPM_DWH database image in the <root>/database folder.
  2. Save the database backup to a location on the database server.
  3. Initiate a sqlplus session as the system user and create an import directory by completing these steps:
    1. To log in to sqlplus, enter the following command:
      > sqlplus system/<system_password>
    2. In sqlplus, run the query to create an import directory.
      SQL> create or replace directory import_directory as '<database backup location>';
    3. Exit sqlplus.
  4. To initiate the import, issue the command:
    > impdp system/<system_password> DIRECTORY=import_directory transform=oid:n DUMPFILE=dwh_oracle_base.db SCHEMAS=ppm_dwh LOGFILE=ppm_dwh_import.log
    A ppm_dwh_import.log is generated in the same location as the database backup. Import errors are expected as there are dependencies between the imported items. Once the import has completed, you can recompile the database to resolve invalid objects.
    If you are using different tablespaces than the ones provided in the backup image, provide tablespace remapping parameters. For information about tablespace remapping parameters, see the Oracle documentation for impdp.
  5. To compile invalid objects from the import, log in to sqlplus as the sys user as 
    sysdba 
    and issue the command:
    SQL> @utlrp.sql
    The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are located in the $ORACLE_HOME/rdbms/admin directory.
     
    Or
    To recompile invalid objects from the 
    Clarity Project and Portfolio Management (PPM)
     Install bin folder, run the following command:
    admin db compile -Ddb.id=Datawarehouse
Configure the Oracle Database Connectivity
To configure the database connectivity to Oracle, complete the following actions:
Configure an Oracle Net Listener Service
During an Oracle installation, the Oracle Net Configuration Assistant is typically launched automatically to create the default listener. If you already have a configured TNS Listener on the server, you can skip this procedure.
Follow these steps:
  1. Start the Oracle Net Configuration Assistant for UNIX or Windows using the appropriate command:
    UNIX
    <oracle home>/bin/netca &
    Windows
    Start Menu, Programs, Oracle,
     Oracle Home, 
    Configuration and Migration Tools, Net Configuration Assistant
  2. To create the TNS listener, use the information in the following table:
Screen
Option
Value/Choice
Welcome
Listener Configuration
Selected
Listener Configuration, Listener
Add
Selected
Listener Configuration, Listener Name
Listener Name
<choose a name>
Configure an Oracle Net Manager Service Name
Follow these steps:
  1. Launch the Oracle Net Manager for UNIX or Windows using the appropriate command:
    UNIX
    <oracle home>/bin/netasst &
    Windows
    Start Menu, Programs, Oracle, 
    Oracle Home, 
    Configuration and Migration Tools, Net Manager
  2. Use the information in the following table to create the Oracle Net Manager service name.
Oracle Net Configuration Assistant Screen
Option
Value
Net Service Name Wizard
Service Name
clarity
Protocol
Protocol
TCP/IP
Protocol Settings
Host Name
Server IP Address or DNS Hostname
Protocol Settings
Port
Server Port
Service
Service Name
clarity
Configure the Data Warehouse Database (Microsoft SQL Server)
After you install Microsoft SQL Server (SQL Server), complete the following tasks to configure the Data Warehouse for use with 
Clarity Project and Portfolio Management (PPM)
:
Set Up a Login Name for 
Clarity Project and Portfolio Management (PPM)
Set up a valid login name for use by 
Clarity Project and Portfolio Management (PPM)
. This login name and password are the values that you specify in CA Clarity System Administration to log in to the database. The login name and password can be any combination. Set the Microsoft SQL Server Database Schema name as 
niku
.
 
This name must be the Default Schema for the login user that you created in the Microsoft SQL Server.
Import the Data Warehouse Database Backup Image
Follow these steps:
  1. In the 
    Clarity Project and Portfolio Management (PPM)
     installation media, locate the MSSQL PPM_DWH database image in the <root>/database folder.
  2. Save the database backup to a temporary location on the database server.
  3. Use the Restore Database tool in Microsoft SQL Server Management Studio to restore the base image.
    To database: ppm_dwh
    From device: c:\<
    temporary location you saved the database image to
    > \dwh_mssql_base.db
  4. Verify the import, and remove the temporary folder and its contents.
  5. Associate the imported database with your organization's SQL server security user by running the following pl/sql as the sa user:
    USE ppm_dwh ALTER USER ppm_dwh WITH LOGIN=<your security user>
  6. To grant the VIEW SERVER STATE to the <
    your security user
    > user, use the following command:
    GRANT VIEW SERVER STATE to <your security user>
Review the Data Warehouse Imported Database Options
Mixed collations settings are not supported. If you change the server-level default from SQL_Latin1_General_CP1_CI_AS to another collation, create a new SQL Server instance with the correct collation SQL_Latin1_General_CP1_CI_AS before creating the 
Clarity Project and Portfolio Management (PPM)
 database.
The import procedure creates the database with the options in the following table. Review the options to verify that they meet your requirements.
Option
Value
ARITHABORT
ANSI NULLS
QUOTED IDENTIFIER
ON
To apply, execute:
ALTER DATABASE <database>
SET ARITHABORT ON
ALTER DATABASE <database>
SET ANSI_NULLS ON
ALTER DATABASE <database>
SET QUOTED_IDENTIFIER ON
Compatibility level
MS SQL 2012: 110
To apply, execute:
EXEC SP_DBCMPTLEVEL <database>, 110
MS SQL 2014: 120
To apply, execute:
EXEC SP_DBCMPTLEVEL <database>, 120
Database Schema Name
ppm_dwh
The login name can be anything, but the default schema name for the login user must be
ppm_dwh
.
Database user roles
db_owner
The database user must be able to alter the Clarity-schema and otherwise own the database.
The following privileges are required:
GRANT ALTER ANY LOGIN TO PPM_DWH
GRANT ALTER ANY LINKED SERVER TO PPM_DWH
READ COMMITTED SNAPSHOT
ON
To apply, execute:
ALTER DATABASE <database>
SET READ_COMMITTED_SNAPSHOT ON
VIEW_SERVER_STATE
Granted to database user.
To apply, execute:
GRANT VIEW SERVER STATE to ppm_dwh
REMOTE_QUERY_TIMEOUT
sp_configure 'remote query timeout',0
reconfigure with override
Set Up a Database Link for the Data Warehouse
Create a Database Link (Oracle) or a Linked Server (MSSQL) to the 
Clarity Project and Portfolio Management (PPM)
 Schema from the Data Warehouse schema as part of the deployment. The link can be used to refer to tables and views on the remote database by appending @dblink to the table or view names. The link gives access to remote tables and views. You can use any SELECT, INSERT, UPDATE, or DELETE statement for the Data Warehouse schema to query against the 
Clarity Project and Portfolio Management (PPM)
 schema. Database links are created to support having the Data Warehouse schema on a dedicated database server or on the same database server as the 
Clarity Project and Portfolio Management (PPM)
 schema.
Clarity Project and Portfolio Management (PPM)
 System Administration (CSA) contains options to enter the information that is related to DBLINKS for both Microsoft SQL Server and Oracle. The default link name is PPMDBLINK, and the name is created during the Save action on the Data Warehouse tab in CSA. The DBLINKs are currently used ONLY during the ETL job that is used to populate the Data Warehouse.
The following user privileges are required to create a database link:
Oracle
CREATE DATABASE LINK TO PPM_DWH
MSSQL
GRANT ALTER ANY LOGIN TO PPM_DWH GRANT ALTER ANY LINKED SERVER TO PPM_DWH
You can revoke the user privileges for Oracle (DATABASE LINK TO PPM_DWH) or MSSQL (ALTER ANY LOGIN TO PPM_DWH and ALTER ANY LINKED SERVER TO PPM_DWH) after you successfully create the DBLINK and run the Load Data Warehouse job. However, if you try to create a new DBLINK and run the Load Data Warehouse job after you revoke these privileges, you receive errors. To resolve the errors, grant the privileges again. Then create the new DBLINK and run the Load Data Warehouse job.
 
More information about DBLINK and Linked Server can be found in the documentation available on the Oracle and Microsoft web sites.
DBLINK is a required parameter for running the Data Warehouse job from 
Clarity Project and Portfolio Management (PPM)
. DBLINK gets created during the SAVE action after the required information is completed on the Data Warehouse tab in CSA.
The default Clarity Database Link is named PPMDBLINK. The link is created using the EZCONNECT method that is available with Oracle. For more information about EZCONNECT, refer to http://www.orafaq.com/wiki/EZCONNECT.
If you create DBLINK using a method other than EZCONNECT, complete the following fields on the Data Warehouse tab in CSA:
  • Custom Database Link. Specifies that the database link is custom. This check box must be selected.
  • Custom Database Link. Specifies the name of your custom database link. CSA gives an error if the custom database link does not exist in the database.
Common procedures can be used to create DBLINK for both Microsoft SQL and Oracle from 
Clarity Project and Portfolio Management (PPM)
. The procedures can be called directly from the Data Warehouse schema if necessary.
ORACLE
CMN_DBLINK_SP(  P_DB_LINK_NAME VARCHAR2, P_CLARITY_DB_HOST_NAME VARCHAR2, P_CLARITY_DB_PORT NUMBER, P_CLARITY_DB_SERVICE_NAME VARCHAR2, P_CLARITY_SCHEMA_NAME VARCHAR2, P_CLARITY_PASSWD VARCHAR2 )
Example: Oracle DBLINK Creation
EXEC CMN_DBLINK_SP ('PPMDBLINK','<hostname>',1521,'CLARITY','NIKU','NIKU');
MSSQL
CMN_DBLINK_SP @P_DB_LINK_NAME VARCHAR(30), @P_CLARITY_DB_HOST_NAME VARCHAR(30), @P_CLARITY_SCHEMA_NAME VARCHAR(30), @P_CLARITY_USER VARCHAR(30), @P_CLARITY_PASSWD VARCHAR(30)
Example: Microsoft SQL DBLINK Creation
CMN_DBLINK_SP 'PPMDBLINK','<hostname>','NIKU','NIKU','NIKU'
The default procedure in the Data Warehouse schema can be used to create the DBLINK for both RAC and Non-RAC databases. The procedure does support both RAC and NON-RAC database and services.
In RAC, the following example creates the DBLINK from the Data Warehouse schema to a 
Clarity Project and Portfolio Management (PPM)
 Schema using the PERF_SERVICE:
EXEC CMN_DBLINK_SP ('PPMDBLINK','hostname',1521,'PERF_SERVICE','niku','niku');
Verify that the EZCONNECT Method is Enabled
The Oracle EZCONNECT method allows you to create an Oracle database link to the 
Clarity Project and Portfolio Management (PPM)
 Schema from the Data Warehouse schema.
Follow these steps:
  1. Navigate to the following folder:
    • $ORACLE_HOME\network\admin
  2. Open the sqlnet.ora file and verify that EZCONNECT is shown in the DIRECTORY_PATH:
    • NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
  3. Add EZCONNECT if it is not shown and save the file.
These steps explain how to troubleshoot if the Data Warehouse database link fails.
Oracle
Follow these steps:
  1. Verify that the following privilege is granted to PPM_DWH user:
    CREATE DATABASE LINK TO PPM_DWH
  2. Verify that EZCONNECT is configured on Oracle.
  3. Verify that sqlnet.ora shows EZCONNECT in the DIRECTORY_PATH:
    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
  4. To verify that PPMDBLINK is working, execute the following query from PPM_DWH schema:
    SELECT count(1) FROM [email protected]
    If the link works, data is returned.
MSSQL
Follow these steps:
  1. Verify that the following privileges are granted to the PPM_DWH schema:
    GRANT ALTER ANY LOGIN TO PPM_DWH GRANT ALTER ANY LINKED SERVER TO PPM_DWH
  2. To verify that PPMDBLINK is working, run the following query from the PPM_DWH schema:
    SELECT count(1) FROM PPMDBLINK.niku.niku.srm_resources
    If the link works, data is returned.
Default Database Link Does Not Work
If for some reason the default database link does not work, a custom database link can be created from the Data Warehouse schema.
Possible Errors:
NSA-0132: Database link could not be created successfully. Please verify the Database and Data Warehouse connection details. ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.
Possible Causes:
  • Verify that EZCONNECT is configured and the sqlnet.ora file shows EZCONNECT in the DIRECTORY_PATH:
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
  • SID and Service_Name are different.
  • Global_names parameter is set to true.
Solution:
Create a custom database link in the Data Warehouse schema and specify the link in CSA. See the Oracle documentation to create database links.
Example:
CREATE DATABASE LINK <LINK_NAME>
CONNECT TO <CLARITY_USER_NAME>
IDENTIFIED BY <CLARITY_USER_PWD>
USING ‘<SERVICE_NAME>’;
If you create DBLINK using a method other than EZCONNECT, complete the following fields on the Data Warehouse tab in CSA:
  • Custom Database Link (check box)
    Specifies that the database link is custom. This check box must be selected.
  • Custom Database Link
  • Specifies the name of your custom database link. CSA gives an error if the custom database link does not exist in the database.
2
Review the Prerequisites
Review the following information before you set up the Data Warehouse:
  • Determine whether you want to install the Data Warehouse on a separate physical server or as a separate instance on the same server. Where you install the Data Warehouse depends on your business needs.
  • Verify that the server times that are set for the 
    Clarity Project and Portfolio Management (PPM)
     app server, product database server, and Data Warehouse database server are all in synch.
  • Only standard 
    Clarity Project and Portfolio Management (PPM)
     objects and attributes are automatically populated in the Data Warehouse by default.
  • The following attribute types are supported: Boolean, String, Large String, Number, Money, Date, Calculated, Formula, Lookup, Multivalued Lookup, URL, and Time-Varying.
  • For any custom objects and custom attributes that you want to include in the Data Warehouse, follow these steps:
    1. Open Administration and from Studio, click Objects.
    2. To include a custom object, click the name, select the
      Include in the Data Warehouse
      check box, and click Save.
    3. To include custom attributes for an object, complete the following steps:
      1. Click the Attributes tab.
      2. Select the custom attributes that you want to include.
        You can identify custom attributes by a check box in the left column of the attributes list.
      3. Click
        Include in Data Warehouse
        .
    4. Save your changes.
    Also look for custom attributes on standard 
    Clarity Project and Portfolio Management (PPM)
     objects.
  • Time slices with the Data Warehouse option selected determine the ranges for the Data Warehouse facts. The default ranges are:
    • Three years back and forward for Months
    • One year back and forward for Weeks
    • One year back and forward for Daily
    • Three years back and forward for Fiscal
    Verify that the ranges work for your business needs. If necessary, you can update the From Date and Number of Periods by opening a time slice and making the changes on the properties page.
    All Monthly time slices should have the same
    From Date
    and
    Number of Periods
    . The same is true for Weekly, Daily, Fiscal, and so on.
  • To help ensure the correct functionality and accuracy of data in 
    Clarity Project and Portfolio Management (PPM)
     and all jobs, including the Load Data Warehouse Job, verify the following:
    • The server time is the same (preferable, down to the second) on the 
      Clarity Project and Portfolio Management (PPM)
       application server, 
      Clarity Project and Portfolio Management (PPM)
       database server, and Data Warehouse database server.
    • The timezone, date, and time is the same on the 
      Clarity Project and Portfolio Management (PPM)
       application server and database servers in the same environment. Do not have any differences.
This synchronization is necessary because the Load Data Warehouse job imports data into the Data Warehouse database based on the last_updated_date field on the object's instances. If the date and time on the servers do not match, data may not be loaded into the Data Warehouse. For other jobs, if the date and time do not match, the job may not start. Or, the job may start later than expected, leading to inaccurate data.
cond
Set the Language and Fiscal Periods for the Data Warehouse
To set the languages and the entity for fiscal periods for the Data Warehouse, use the System Options page in 
Clarity Project and Portfolio Management (PPM)
.
Modifying the fields that are described in the following procedure, requires you to run the
Load Data Warehouse
job with the option Full Load selected. Changes to the field
Entity for Fiscal Periods
result in a resource-intensive operation because all fiscal fields within the Data Warehouse are recalculated.
Follow these steps:
  1. Open Administration and from General Settings, click System Options.
  2. In the Data Warehouse Options section, complete the following fields:
    • Languages
      Specifies the language localizations that are included in the Data Warehouse. Multiple languages result in increased storage requirements on the Data Warehouse server.
    • Entity for Fiscal Periods
      Specifies the entity that is used to determine fiscal periods. The fiscal periods that are associated with the selected entity are used for all Data Warehouse fiscal period data calculations. Setting the entity for the first time creates new Data Warehouse time slice requests. Therefore, the initial run of the Time Slicing job takes significantly longer than normal.
      Initially setting an entity or updating it also updates the fiscal entity and fiscal period type for the Data Warehouse fiscal time slice requests. Do not set or change the entity when a time slice rollover is in progress. If you do, an error message appears. Wait until the rollover completes and then set the entity. To determine which slices requests are in the rollover, check the expiration date on the time slices. A slice request with an expiration date that is blank, earlier than the current date, or the same as the current date is in the rollover. Allow the slice request to complete processing before setting the entity.
      The entity must have Monthly (or 13 period) fiscal time periods set up to cover the time range of the time slice requests. Otherwise, the time slice requests are ignored when the Time Slicing job runs.
  3. Save your changes.
Configure the Data Warehouse Server Credentials
Configure the basic Data Warehouse credentials and configurations in Clarity System Administration (CSA). The Data Warehouse schema can be on the same physical server as the 
Clarity Project and Portfolio Management (PPM)
 database as a separate instance. The Data Warehouse schema can also be on a different physical server depending on the size of the 
Clarity Project and Portfolio Management (PPM)
 database.
The HTTP and HTTPS Entry URL fields completed for the 
Clarity Project and Portfolio Management (PPM)
 server in Clarity System Administration (CSA) cannot be
localhost
when Jaspersoft is integrated with 
Clarity Project and Portfolio Management (PPM)
. When you use Jaspersoft, the URLs must be explicitly entered on the Application subtab of the Properties tab for the 
Clarity Project and Portfolio Management (PPM)
 server.
For example:
HTTP Entry URL: http://<
server name
>:<
port
>
HTTPS Entry URL: https://<
server name
>:<
port
>
Follow these steps:
  1. Open the CA Clarity System Administration application using the following URL:
    http://<
    hostname
    >:8090
    Note
    : If you specified a CA Clarity System Administration port number other than the one indicated, use that value instead in the URL.
  2. Log in using the password that you created during the initial installation.
  3. In the left navigation panel, click Servers and click the name of the server.
  4. On the Properties tab, click the Data Warehouse subtab and complete the fields necessary for your configuration.
    • Vendor
      Specifies the Database system: Oracle (default) or Microsoft SQL Server.
    • Sys Password
      Specifies the system password for this database server. For Oracle 11g only this password is used to start and stop the database. The password is required only if the database is managed as a 
      Clarity Project and Portfolio Management (PPM)
       service.
    • Dimension Table Tablespace
      Used during the schema installation to select a specific tablespace for dimension tables for the Data Warehouse schema. The default is DWH_PPM_DATA_DIM.
    • Fact Table Tablespace
      Used during the schema installation to select a specific tablespace for fact tables for the Data Warehouse schema. The default is DWH_PPM_DATA_FACT.
    • Dimension Index Tablespace
      Used during the schema installation to select a specific tablespace for dimension indices for the Data Warehouse schema. The default is DWH_PPM_INDX_DIM.
    • Fact Index Tablespace
      Used during the schema installation to select a specific tablespace for fact indices for the Data Warehouse schema. The default is DWH_PPM_INDX_FACT.
    • Fetch Size
      Specifies a hint for the JDBC driver as to the number of rows to be fetched from the database when more rows are needed. The number of rows that are specified affects only result sets created using this statement. If the value specified is zero, then the hint is ignored.
    • Status
      Indicates whether the Data Warehouse server is available.
    • Specify URL
      Specifies a different JDBC url than the default. Required when an Oracle RAC is being used.
    • Hostname
      The IP address or hostname of the database server.
    • JDBC URL
      If Specify URL is selected, the JDBC URL to access the database displays.
      This information is primarily useful for connecting to an Oracle RAC cluster.
    • Instance Name (Microsoft SQL Server only)
      Specifies the name of the Microsoft SQL Server instance when it is not the default instance.
    • Port
      Specifies the port that is used for database traffic.
      Default value: 1521 (Oracle) or 1433 (Microsoft SQL Server).
    • Database Name (Microsoft SQL Server)
      Specifies the name of the Microsoft SQL Server database.
    • Service ID (Oracle only)
      Specifies the name of the Oracle database Service ID.
    • Service Name
      Specifies the name of the Oracle database service (Oracle) or the ODBC connection name (Microsoft SQL Server).
    • Login Name
      Specifies the schema login name.
    • Password
      Specifies the schema login password.
    • Custom Database Link
      Specifies whether the database link is the default (ppmdblink) or a custom database link. To indicate a custom link, select the check box.
    • Clarity Database Link
      Specifies the name of a custom database link.
    • ETL Job Timeout
      Specifies the length of time before an ETL job stops when it has not completed. The default setting is 600 minutes.
  5. Save your changes.
Populate the Data Warehouse with 
Clarity Project and Portfolio Management (PPM)
 Data
To populate the Data Warehouse with data from the 
Clarity Project and Portfolio Management (PPM)
 database, run the following jobs in the order provided:
  • Time Slicing
    This job splits time varying data into a relational format.
    The first run of this job can take substantial time.
  • Load Data Warehouse
    This job extracts the data from the 
    Clarity Project and Portfolio Management (PPM)
     database, transforms the data, and loads the data into the Data Warehouse.
    This job is initially disabled. Enable this job before you run it.
    Before you run this job, open the properties for any custom object or attribute that you want to include and select the
    Include in the Data Warehouse
    option. This option must be selected for custom objects or attributes to be populated in the Data Warehouse.
    Note:
    Verify that the DBLINK was created correctly. The Load Data Warehouse job fails if the DBLINK is not valid.
    If you are running this job for the first time to populate the Data Warehouse, select the Full Load parameter. Also, if you had a failed run of the Load Data Warehouse job and you corrected the problem, select Full Load the first time that you run this job after the failure. For subsequent runs, you can run an incremental load by leaving the Full Load parameter unselected.
    The following actions require you to rerun the Load Data Warehouse job after the initial population:
    • Deleting an attribute or clearing the Include in the Data Warehouse option for the attribute in Studio.
    • Changing the data type of an attribute.
    • Adding a language to the Data Warehouse.
    • Changing a timeslice date to include a larger timeframe.
    • Changing a system setting. A Full Load of the Data Warehouse is required. For example, if you change the system Risk Threshold in Administration, Risk Settings, execute the Full Load of the Data Warehouse. Otherwise, a mismatch occurs between the transactional database tables and Data Warehouse tables, which results in a data mismatch.
  • Load Data Warehouse Access Rights
    This job loads the access privileges for resources and investments from the 
    Clarity Project and Portfolio Management (PPM)
     database to the Data Warehouse.
     This job is initially disabled. Enable this job before you run it.
When the Load Data Warehouse Access Rights and Load Data Warehouse jobs run, the jobs write standard messages to the Jobs logs and detailed messages to the Data Warehouse logs. The default Data Warehouse log files are bg-dwh.log and bg-ca.log. To review the detailed Data Warehouse log messages, navigate to the Data Warehouse logs (under the standard Jobs logs) in CSA. You can enable more logging by changing the Kettle Log Level in CSA (Logs, Edit Configuration). Use the detailed messages to monitor job progress and to analyze Data Warehouse load failures.
Follow these steps:
  1. Open Home and from Personal, click Jobs.
  2. Locate the job that you want to run and click the name.
  3. Complete the requested information for the job.
    You can start the job immediately or you can schedule a time. You can also indicate resources or groups you want to notify.
  4. Click Submit.
 
Monitor the Data Warehouse Job
You can monitor the status of the Load Data Warehouse job using the following database view, which is available in the 
Clarity Project and Portfolio Management (PPM)
 Data Warehouse schema:
SELECT TABLE_NAME, TABLE_TYPE, COMPLETED_FLAG, POPULATION_TIME_SEC, DW_LOAD_START_DATE, DW_LOAD_END_DATE FROM DWH_RUN_STATUS_V
If the Completed_Flag is equal to 1, the load is complete. If the Completed_Flag is equal to 0, the load is not complete yet.
The following database view helps you monitor the status of the last load run. This view also provides information about the number of rows that were brought into the Data Warehouse schema and the load time for each table.
SELECT TABLE_NAME, RECORD_COUNT, POPULATION_TIME_SEC FROM DWH_GATHER_METRICS_V WHERE TABLE_NAME NOT IN  ('DWH_CFG_LOG','DWH_CFG_TABLE_AUDIT','DWH_FK_PK_EXCEPTIONS','DWH_META_COLUMNS','DWH_META_INDEXES','DWH_META_TABLES') ORDER BY TABLE_NAME
 
Rebuild the Data Warehouse
If the Load Data Warehouse job fails, the configuration might be corrupt. You might have to rebuild the data warehouse database.
  1. To verify that the application and data warehouse databases match, run the following queries: 
    select * from cmn_install_history order by installed_date desc select * from cmn_dwh_install_history order by installed_date desc
  2. Missing views or tables can also result in a mismatch between the application and data warehouse metadata. To verify the schema, run the following queries: 
    select a.dwh_table_name, a.dwh_column_name,a.attr_type, a.attr_data_type, a.attr_data_size, a.is_deleted, a.js_processedfrom DWH_META_COLUMNS aorder by 1,2select b.dwh_table, b.dwh_column, b.attribute_type, b.attribute_data_typefrom DWH_META_COLUMNS border by 1,2
     
  3. To rebuild the DWH schema, schedule the following steps:
    1. Locate the default initial database file:
        
      dwh_oracle_base.db
      : import using the Oracle datapump utility
       
       dwh_mssql_base.db
      : restore using MS SQL Server Management Studio
    2. Run the following command on the CA PPM application server to have it match the version PPM:
      admin db dwh-upgrade -Dupgrade.phase=maintenance