SlideShare Explore You
Loading in …5
Like this presentation? Why not share!
ravi Kumar M , VLSI at Currently seeking new opportunities at StudenDBase 3 months ago
Michael Brunak , Project and Application Portfolio Management at Walt Disney Parks & Resorts Technology at Walt Disney Parks & Resorts Technology 9 months ago
Connie McKissack , Manager IS PMO at Nissan North America at Nissan North America 10 months ago
Prashank Singh , Clarity Consultant at Pemari Technology at Pemari Consulting 11 months ago
No notes for slide
- 1. ca Intellicenter Hands-On Lab: CA PPM Data Warehouse Bryan Temple Session Number ICX07L #CAWorld CA PPM CA Technologies
- 2. Hands On Lab: CA PPM Data Warehouse
- 3. 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
- 4. 4 © 2014 CA. ALL RIGHTS RESERVED. 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 SETUP 2
- 5. 5 © 2014 CA. ALL RIGHTS RESERVED. 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
- 6. 6 © 2014 CA. ALL RIGHTS RESERVED. 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
- 7. 7 © 2014 CA. ALL RIGHTS RESERVED. 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 oLightweight, drag and drop business user reporting capability oOut of the box reports and domains for Investments, Resources, Financials and Timesheets. oPentaho Data Integrator is embedded within CA PPM. The data transformation and load runs as a CA PPM job. oThe Data Warehouse is modeled on a STAR schema, with Dimensions covering the major areas in CA PPM and their associated Facts.
- 8. 8 © 2014 CA. ALL RIGHTS RESERVED. Data Warehouse Overview Reports and portletsrun 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
- 9. 9 © 2014 CA. ALL RIGHTS RESERVED. Data Warehouse Overview 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. Addresses Reporting Challenges
- 10. 10 © 2014 CA. ALL RIGHTS RESERVED. Data Warehouse Overview 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. Addresses Reporting Challenges
- 11. 11 © 2014 CA. ALL RIGHTS RESERVED. 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
- 12. 12 © 2014 CA. ALL RIGHTS RESERVED. Data Warehouse Setup 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
- 13. 13 © 2014 CA. ALL RIGHTS RESERVED. Data Warehouse Setup 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
- 14. 14 © 2014 CA. ALL RIGHTS RESERVED. Data Warehouse Setup 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
- 15. 15 © 2014 CA. ALL RIGHTS RESERVED. Data Warehouse Setup 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
- 16. 16 © 2014 CA. ALL RIGHTS RESERVED. 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
- 17. 17 © 2014 CA. ALL RIGHTS RESERVED. 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
- 18. 18 © 2014 CA. ALL RIGHTS RESERVED. 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
- 19. 19 © 2014 CA. ALL RIGHTS RESERVED. 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 or Varchar(30) The key value of the lookup. If the hiddenkey 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 languagestable [lookup_name] Varchar(255) Descriptivename of the lookup: Example: investment_status sort_order Number Sort order is used tospecify 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
- 20. 20 © 2014 CA. ALL RIGHTS RESERVED. 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_keyand language_codewill 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 languagestable if applicable [lookup_name] … Descriptivename 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
- 21. 21 © 2014 CA. ALL RIGHTS RESERVED. 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.
- 22. 22 © 2014 CA. ALL RIGHTS RESERVED. 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.
- 23. 23 © 2014 CA. ALL RIGHTS RESERVED. Fact Period Aggregation Tables Fact Description FactTable 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
- 24. 24 © 2014 CA. ALL RIGHTS RESERVED. 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 FactTable 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
- 25. 25 © 2014 CA. ALL RIGHTS RESERVED. Example: Investment Period Facts Table •The Investment period facts table contains over 110 different facts. •Investment_keyis a foreign key to the investment table. •Period_keyis a foreign key to the periodic table. •Dw_updated_dateis the last date this record was updated.
- 26. 26 © 2014 CA. ALL RIGHTS RESERVED. 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.
- 27. 27 © 2014 CA. ALL RIGHTS RESERVED. 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_idor user_id) •Inconsistent column names •Multiple joins to the same table for different information •Not intuitive
- 28. 28 © 2014 CA. ALL RIGHTS RESERVED. 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'
- 29. 29 © 2014 CA. ALL RIGHTS RESERVED. 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
- 30. 30 © 2014 CA. ALL RIGHTS RESERVED. Investment Team Facts •Combines the team facts together by period •Calculates costs •Summarizes the periodic team facts
- 31. 31 © 2014 CA. ALL RIGHTS RESERVED. Task Assignment Facts •Combines the assignment facts by period •Calculates costs •Summarizes the periodic assignment facts
- 32. 32 © 2014 CA. ALL RIGHTS RESERVED. Investment Task Facts •Summarizes assignment facts to the task by period •Formulas calculated for consistency •Summarizes task facts •Contains earned value information
- 33. 33 © 2014 CA. ALL RIGHTS RESERVED. Investment Period Facts •Summarizes investment period facts •Formulas calculated for consistency •Comprehensive investment data
- 34. 34 © 2014 CA. ALL RIGHTS RESERVED. Resource Period Facts •Summarizes resource period facts •Formulas calculated for consistency •Comprehensive resource data
- 35. 35 © 2014 CA. ALL RIGHTS RESERVED. Data Warehouse Items Included Change Request Management Issue Management WBS Structure ExchangeRates OBSHierarchy 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 ResourceUser Security CustomAttributes Investment –Ideas Risk Management TSV Values Investment –Other Work Team Allocations Summary InvestmentEarned Value Data Investment–Products Time Entry Current Baseline Data Investment -Projects Time EntryNotes PMO Accelerator Investment –Services Time Sheets DBLINKfor Missing Data Investment User Security Time Sheet Notes New Cost Slices –ETC, Allocations
- 36. 36 © 2014 CA. ALL RIGHTS RESERVED. Data Warehouse Items Under Consideration Additional Objects –Baseline History –Earned Value History –Incidents –Portfolio Management –Resource Skills –Scenarios Snapshots for Trending Slowly Changing Dimensions
- 37. 37 © 2014 CA. ALL RIGHTS RESERVED. For More Information To learn more about Management Cloud, please visit: http://bit.ly/1wEnPhz Insert appropriate screenshot and textoverlayfrom following“More Info Graphics” slide here; ensure it links to correct page Management Cloud
- 38. 38 © 2014 CA. ALL RIGHTS RESERVED. 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 withcustomer 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) affectthe 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 oncurrent information and resource allocations as of November 9, 2014 and is subject to change or withdrawal by CA at any time withoutnotice. 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 whenand 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 Reporting with Jaspersoft
CA Project and Portfolio Management - A Data Warehouse Deep Dive
CA Project & Portfolio Management—Jaspersoft Studio for the Report Developer
Rego University: Advanced Data Model, CA PPM (CA Clarity PPM)
CA Project and Portfolio Management 14.x - Advanced Reporting for the End User
Next Generation Reporting and Analytics—First Glimpse
Managing and Rationalizing the Application Portfolio with CA PPM
LinkedIn Corporation © 2016
Public clipboards featuring this slide
No public clipboards found for this slide
Select another clipboard×
Looks like you’ve clipped this slide to already.
Create a clipboard
You just clipped your first slide!Clipping is a handy way to collect important slides you want to go back to later. Now customize the name of a clipboard to store your clips.