hands-on lab: ca ppm data warehouse

ca Intellicenter

Hands-On Lab: CA PPM Data Warehouse

Bryan Temple

Session Number ICX07L #CAWorld

CA PPMCA Technologies

Hands On Lab: CA PPM Data Warehouse

3 © 2014 CA. ALL RIGHTS RESERVED.

Abstract

Having the right data at your fingertips is critical for making decisions in the new world of software-driven business. Facilitating access to project and resource data is a key focus area for CA Project & Portfolio Management (CA PPM). Review the presentation from this hands-on lab to learn the details of the new CA PPM data warehouse.

Bryan Temple

CA Technologies

Sr. Engineering Services Architect


Agenda

DATA WAREHOUSE OVERVIEW AND ARCHITECTURE

LOADING THE DATA WAREHOUSE

DATA WAREHOUSE DEMO

DATA WAREHOUSE STANDARDS

DATA WAREHOUSE DIMENSIONS AND FACTS

DATA WAREHOUSE ROADMAP

1

3

4

5

6

7

DATA WAREHOUSE SETUP2


Subject Oriented

Modeled on the STAR schema and includes the following master objects: Investment (All Types), Resource, Portfolio and Timesheet

Integrated

Consistent naming conventions, formats and encoding structures

Non-Volatile

Separate schema optimized for business decision making and analytics

Time Variant

Predefined, yet configurable, time slices

– 1 year back/forward for weekly

– 3 years back/forward for monthly

Data Warehouse

Data Warehouse Overview



Dimensions are the descriptive fields on an object (Examples: Investment ID, Investment Name, Investment Manager, etc.).

Facts are the metrics on an object (Examples: Total Cost, Actual Hours, etc.).

Star Schema is a type of database design. A simple Star would have a fact table with a few direct links to dimension tables.

A Snowflake is a dimension table that can be indirectly linked to a fact table.

Common Terms


CA PPM Application

CA PPM Reporting Architecture

Load Data Warehouse job

(embedded Pentaho Data Integration)

Data Warehouse

Jaspersoft Reports, Ad Hoc Views &

Domains

CA PPM Database

o Lightweight, drag and drop business user reporting capability

o Out of the box reports and domains for Investments, Resources, Financials and Timesheets.

o Pentaho Data Integrator is embedded within CA PPM. The data transformation and load runs as a CA PPM job.

o The Data Warehouse is modeled on a STAR schema, with Dimensions covering the major areas in CA PPM and their associated Facts.



Reports and portlets run against transactional data.– The data warehouse schema resides on another database server taking the stress

off the transactional CA PPM database.

Relational database makes queries very complex.– The data warehouse carries keys and descriptive values in the dimension tables so

fewer joins are required. Facts are combined into summary and period tables.

Finding the data with 1000+ tables– With the exception of configuration and meta tables, the data warehouse tables

are ‘user friendly’ to report against.

Table name inconsistencies– Similar tables are grouped together by the table prefix and the names are very

descriptive.

Addresses Reporting Challenges



Time slice requests

– Specific time slice requests are set up to populate the data warehouse. Defaults are set but can be modified.

Column naming

– Columns are consistently named across tables.

Resource ID versus user ID

– In the CA PPM database, manager points to the user ID and resource points to the resource ID, or code, which makes it inconsistent. In the data warehouse, resource columns (manager_key, resource_key, etc.) are always the resource_key.

Date/time storage

– In the CA PPM database, the finish/end dates do not always match those displayed in CA PPM. Database functions in queries must be leveraged to determine the correct date. In the data warehouse, the finish/end dates always match CA PPM.




Code versus ID– In the CA PPM financial tables, codes are used instead of IDs. The data warehouse

always uses the numeric key of the dynamic lookups.

Database tuning– Since the data warehouse is separate from the CA PPM database, the database

can be tuned differently for optimal performance.

Studio attributes are not available in Business Objects Universes without customization.– The data warehouse is extendable without customization. A flag has been added

to Studio objects and attributes that control whether the data warehouse load job automatically adds custom objects and attributes.



Data Warehouse Setup

The CSA data warehouse properties allow you to configure the basic data warehouse credentials and settings. This database can be on the same physical server, a different instance on the same server, or on a different server. This depends on the size of the CA PPM database.

CSA Configuration



Administration/General Settings/System Options:– The languages selected determine which localizations are included in the data warehouse

(more languages means more disk).

– The entity chosen determines which fiscal periods are used when aggregating data.

– The entity selected does not restrict the investment data included in the data warehouse to that entity.

System Options



Time slices with the Data Warehouse flag checked determine the ranges for the facts in the data warehouse.

Defaults– Months: 3 years back and forward– Weeks: 1 year back and forward – Daily: 1 year back and forward– Fiscal: 3 years back and forward

Verify these ranges work for your company. If not, you can update the From Date and Periods in the time slice request.

All monthly time slices should have the same From Date and Number of Periods. (The same applies for Weekly, etc.).

Time Slice Requests



Custom objects can be included in the data warehouse via Studio. Simply check the box for ‘Include in the Data Warehouse.’

The attributes of the object also need to be selected manually for inclusion in the data warehouse.

Custom Objects



Custom attributes can be included in the data warehouse via Studio. Simply check the box for ‘Include in the Data Warehouse.’

Boolean, String, Number, Money, Date, Calculated, Formula, Lookup and Multi-Valued Lookup attributes are supported. Calendar and Fiscal TSVs are supported for relevant attributes.

Custom Attributes


Loading the Data Warehouse

Two jobs exist in CA PPM for loading the data warehouse. These jobs are independent of one another.

Load the Data Warehouse Security Privileges: Loads the security for investments and resources. The table is truncated and rebuilt each time.

Load the Data Warehouse: This is the core job that analyzes the meta data, creates new objects and attributes (if needed), loads the dimensions, lookups and facts.

Parameter:

Data Warehouse Full Reload – If checked, this will truncate and rebuild the data warehouse. Otherwise, only incremental changes are processed.

ETL Jobs


Loading the Data Warehouse

Reports and Jobs

Load the Data Warehouse Security Privileges.– Loads the investment/resource security for the system users

– Separate job – the security job is not incremental, the table gets truncated and rebuilt.

Load the Data Warehouse.– Loads the complete data warehouse

– ETL job steps: Runs scripts the data warehouse is dependent upon: calendar population, WBS hierarchy, investment

hierarchy

Builds the meta data that determines the data warehouse structure

Checks/corrects any data warehouse structure changes

Loads/updates the lookup tables

Loads/updates the dimension tables

Loads/updates the fact tables

ETL Jobs


Data Warehouse Table Prefix Standards

DWH_CFG - Configuration tables used to supply the data warehouse log and audit information

DWH_CMN - Common database objects used across most areas

DWH_CMP - Company database objects

DWH_FIN - Financial management database objects

DWH_INV - Investment management database objects

DWH_LKP - Lookup database objects

DWH_META - Meta data tables that help determine the data warehouse structure

DWH_ODF - Custom database objects

DWH_PFM - Portfolio management database objects

DWH_RES - Resource management database objects

DWH_RIM - Risk, issue and change management database objects

DWH_TME - Time management database objects

DWH_X - Internal database objects used to help populate the fact tables


Data Warehouse Static Lookup Standards

Static Lookups in CA PPM can be confusing because they are stored in one table and you need to qualify them by the lookup_type. In the data warehouse, each lookup is its own table. The lookup values are stored in the different languages chosen for the data warehouse. If, for example, the data warehouse is stored in English and Spanish, two records exist for each lookup value.

Column Data Type Description

[lookup_name]_key Number orVarchar(30)

The key value of the lookup. If the hidden key in CA PPM is lookup_enum, then the key in the data warehouse will be populated with the lookup_enum. Same for lookup_code. Example: investment_status_key

language_code_key Number ID from the CA PPM languages table

language_code Varchar(30) Unique language code from the CA PPM languages table

[lookup_name] Varchar(255) Descriptive name of the lookup: Example: investment_status

sort_order Number Sort order is used to specify a specific order in which the user wants to see the values

is_active Number Is the current lookup value active

clarity_updated_date Date Last time the record was updated in CA PPM

dw_updated_date Date Last time the record was updated in the data warehouse


Data Warehouse Dynamic Lookup Standards

Dynamic Lookups in CA PPM are determined by NSQL statements. In the data warehouse, a table exists for each dynamic lookup that is used. Each table structure can be different depending on the lookup. If the lookup is language dependent, then language_code_key and language_code will be stored. Otherwise, there will be one record per value.

Column Data Type Description

[lookup_name]_key … The key value of the dynamic lookup. Depends on the NSQL’s hidden value

language_code_key Number ID from the CA PPM languages table if applicable

language_code Varchar(30) Unique language code from the CA PPM languages table if applicable

[lookup_name] … Descriptive name of the lookup: Example: investment_status

… … Miscellaneous columns specific to the lookup

clarity_updated_date Date Last time the record was updated in CA PPM

dw_updated_date Date Last time the record was updated in the data warehouse

Basic Dynamic Lookup Structure


Data Warehouse Dimension Standards

Dimension Language Tables– If the dimension has language dependent lookups, a table ending with ‘_ln’ carries the language

dependent descriptions.

Below is a simple example using ‘Investment_status’. The key is carried in the investment table while the language dependent description is carried in the investment language table.


Data Warehouse Fact Standards

• Fact table names end with ‘_facts’.

• Fact tables with ‘_period_’ in the name store facts by defined periods.

• Fact tables with ‘_summary_’ in the name store summarized facts.

• The fact table keys all have referential integrity.

• Calculated facts are stored in the tables to help with consistency.

• Summary rollups exist in the data warehouse.• Assignments roll up to tasks, tasks roll up to investments.

• Data warehouse time slice requests aggregate the data into weekly, monthly and fiscal periods.

• Fiscal aggregation is new to the data warehouse.


Fact Period Aggregation Tables

Fact Description Fact Table Aggregation

Financial Transaction Facts dwh_fin_transaction_facts Daily

Time Entry Facts dwh_tme_entry_facts Daily

Financial Benefit Facts dwh_fin_benefit_period_facts Fiscal Period

Financial Plan Facts dwh_fin_plan_period_facts Fiscal Period

Task Assignment Facts dwh_inv_assign_period_facts Fiscal Period, Weekly, Monthly

Investment Task Facts dwh_inv_task_period_facts Fiscal Period, Weekly, Monthly

Investment Team Facts dwh_inv_team_period_facts Fiscal Period, Weekly, Monthly

Investment facts dwh_inv_period_facts Fiscal Period, Weekly, Monthly

Resource Facts dwh_res_period_facts Fiscal Period, Weekly, Monthly


Fact Summary Tables and Internal Fact Tables

• Summary tables exist for many of the facts.• If matching summary numbers to period facts, qualify the period facts by a period type.

• Internal Fact Tables start with a ‘dwh_x_’. These tables are used to populate the period and summary fact tables in the most efficient way. They are not for user consumption.

Fact Description Fact Table

Financial benefit facts dwh_fin_benefit_summary_facts

Financial plan facts dwh_fin_plan_summary_facts

Task assignment facts dwh_inv_assign_summary_facts

Investment task facts dwh_inv_task_summary_facts

Investment team facts dwh_inv_team_summary_facts

Investment facts dwh_inv_summary_facts


Example: Investment Period Facts Table

• The Investment period facts table contains over 110 different facts.• Investment_key is a foreign key to the investment table.• Period_key is a foreign key to the periodic table.• Dw_updated_date is the last date this record was updated.


Example: Investment Team Table

DWH_INV_TEAM

DWH_INV_TEAM_LN

Table contains language translations.

If the data warehouse is set up for English and Spanish, there would be two records for every one record in dwh_inv_team.


Example: Old Team Query (CA PPM Database)

SELECT m.full_name investment_manager,

i.name investment_name,

r.full_name resource_name,

rr.full_name role_name,

tl.name booking_status,

t2.name request_status,

s1.slice_date period_start_date,

NVL(s1.slice,0) alloc_hours,

NVL(s2.slice,0) alloc_cost

FROM inv_investments i

INNER JOIN prTeam t ON i.id = t.prProjectID

LEFT OUTER JOIN srm_resources m ON i.manager_id = m.user_id

LEFT OUTER JOIN srm_resources r ON t.prResourceID = r.id

LEFT OUTER JOIN srm_resources rr ON t.prRoleID = rr.id

LEFT OUTER JOIN cmn_lookups_v tl ON t.prBooking = tl.lookup_enum

AND tl.lookup_type = 'BOOKING_STATUS_LIST'

AND tl.language_code = 'en'

LEFT OUTER JOIN cmn_lookups_v t2 ON t.prBooking = t2.lookup_enum

AND t2.lookup_type = 'REQUEST_STATUS_LIST'

AND t2.language_code = 'en'

LEFT OUTER JOIN prj_blb_slices s1 ON t.prID = s1.prj_object_id

AND s1.slice_request_id IN (SELECT id

FROM prj_blb_slicerequests

WHERE request_name = 'MONTHLYRESOURCEALLOCCURVE')

LEFT OUTER JOIN prj_blb_slices s2 ON t.prID = s1.prj_object_id

AND s1.slice_request_id IN (SELECT id

FROM prj_blb_slicerequests

WHERE request_name = 'team::alloccost_curve::dwh_month')

AND s1.slice_date = s2.slice_date

WHERE s1.slice_date BETWEEN TO_DATE('01/01/2014','MM/DD/YYYY') AND TO_DATE('12/31/2014','MM/DD/YYYY')

• Need to know lookup types• Inconsistent joins between tables

(resource_id or user_id)• Inconsistent column names• Multiple joins to the same table for

different information• Not intuitive


Example: New Team Query (Data Warehouse)

• No need to join to lookup tables• Consistent joins between tables

(always resource_id)• Consistent column names• Intuitive

SELECT i.investment_manager,

i.investment_name,

t.resource_name,

t.role_name,

tl.booking_status,

tl.request_status,

p.period_start_date,

tf.alloc_hours,

tf.alloc_cost

FROM dwh_inv_team t

INNER JOIN dwh_inv_team_ln tl ON t.team_key = tl.team_key

INNER JOIN dwh_inv_investment i ON t.investment_key = i.investment_key

INNER JOIN dwh_inv_team_period_facts tf ON t.team_key = tf.team_key

INNER JOIN dwh_cmn_period p ON tf.period_key = p.period_key

WHERE SYSDATE BETWEEN p.year_start_date AND p.year_end_date

AND p.period_type_key = 'MONTHLY'

AND tl.language_code = 'en'


Financial Plan Facts

• Combines the periodic plan facts• Calculates forecast facts• Numerous slices used to produce

these facts

• Summarizes the periodic plan facts• Calculates forecast facts


Investment Team Facts

• Combines the team facts together by period

• Calculates costs

• Summarizes the periodic team facts


Task Assignment Facts

• Combines the assignment facts by period

• Calculates costs

• Summarizes the periodic assignment facts


Investment Task Facts

• Summarizes assignment facts to the task by period

• Formulas calculated for consistency

• Summarizes task facts• Contains earned value information


Investment Period Facts

• Summarizes investment period facts


• Comprehensive investment data


Resource Period Facts

• Summarizes resource period facts


• Comprehensive resource data


Data Warehouse Items Included

Change Request Management Issue Management WBS Structure

Exchange Rates OBS Hierarchy WIP Financial Transactions

Financial Benefit Plans Portfolio (High Level) Facts by Weekly/Monthly/Fiscal Period

Financial Budget/Cost Plans Resource Assignments Summary Facts

Investment – Applications Resources All Associated Lookups

Investment – Assets Resource User Security Custom Attributes

Investment – Ideas Risk Management TSV Values

Investment – Other Work Team Allocations Summary Investment Earned Value Data

Investment – Products Time Entry Current Baseline Data

Investment - Projects Time Entry Notes PMO Accelerator

Investment – Services Time Sheets DBLINK for Missing Data

Investment User Security Time Sheet Notes New Cost Slices – ETC, Allocations


Data Warehouse Items Under Consideration

Additional Objects– Baseline History

– Earned Value History

– Incidents

– Portfolio Management

– Resource Skills

– Scenarios

Snapshots for Trending

Slowly Changing Dimensions


For More Information

To learn more about Management Cloud,

please visit:

http://bit.ly/1wEnPhz

Insert appropriate screenshot and text overlayfrom following “More Info Graphics” slide here;

ensure it links to correct pageManagement Cloud






For Informational Purposes Only

© 2014 CA. All rights reserved. All trademarks referenced herein belong to their respective companies. The presentation provided at CA

World 2014 is intended for information purposes only and does not form any type of warranty. Some of the specific slides with customer

references relate to customer's specific use and experience of CA products and solutions so actual results may vary.

Certain information in this presentation may outline CA’s general product direction. This presentation shall not serve to (i) affect the rights

and/or obligations of CA or its licensees under any existing or future license agreement or services agreement relating to any CA software

product; or (ii) amend any product documentation or specifications for any CA software product. This presentation is based on current

information and resource allocations as of November 9, 2014 and is subject to change or withdrawal by CA at any time without notice. The

development, release and timing of any features or functionality described in this presentation remain at CA’s sole discretion.

Notwithstanding anything in this presentation to the contrary, upon the general availability of any future CA product release referenced in

this presentation, CA may make such release available to new licensees in the form of a regularly scheduled major product release. Such

release may be made available to licensees of the product who are active subscribers to CA maintenance and support, on a when and if-

available basis. The information in this presentation is not deemed to be incorporated into any contract.

Terms of this Presentation

hands-on lab: ca ppm data warehouse

Technology