Advanced Data Model Maximizing Queries Using Tables www
Advanced Data Model Maximizing Queries Using Tables www. regoconsulting. com Phone: 1 -888 -813 -0444
Summary ● ● ● High Level Overview Core Tables Time Slices Datamart Database ○ Projects ● Database Details ○ Common Columns ○ Table Naming Convention ○ Object Types ○ ○ ○ Resources Ideas Projects Baselines Master / Sub Portfolios ● Open Discussion! ● Where to find documentation ● Object Details ○ Ideas 2 www. regoconsulting. com Phone: 1 -888 -813 -0444
10, 000 Foot View ● There are 3 main areas where data is stored ● Core Tables ○ These are the production tables used for the day to day functions ○ They include • Investment , Resource, Timesheet Information ○ Data updated in real time (Live Tables) ● Time Slice Tables ○ Houses summarized data by Daily, Weekly, Bi-Weekly, Monthly, Bi-Monthly, Quarterly, Semi-Annually, Yearly views ○ These tables are populated via a job process – Time Slice ○ Time Slices are critical to define how much data is summarized ● Data. Mart Tables ○ Provides Summary and Rollup Data ○ Data. Mart is populated via several job processes – Rate Matrix Extraction, Data. Mart Extraction and Datamart Rollup 3 www. regoconsulting. com Phone: 1 -888 -813 -0444
Core Tables ● Investments ○ INV_INVESTMENTS – Main investment table that links to all of the related investment table. ● Resources ○ SRM_RESOURCES is the basic resource/role table that links to all resource related tables. ● Timesheet ○ Stores timesheet information and links to the resource, time entry and time period tables ○ PRTimesheet ○ Pr. Time. Entry 4 www. regoconsulting. com Phone: 1 -888 -813 -0444
Time Slice Tables ● Time and Hour metrics are stored as blobs and are unreadable in the day to day production tables. ● Time Slice tables open a window to this data for viewing. ● These views allow Clarity to group data into Weeks, Months, Quarters etc… ● This grouping allows for more efficient queries. ● Need to tell it what slice you are going after. ● Keep your daily slices to a minimum. ● Resetting Slices. 5 www. regoconsulting. com Phone: 1 -888 -813 -0444
www. regoconsulting. com Phone: 1 -888 -813 -0444 Feb-16 Jan-16 Dec-15 Nov-15 Oct-15 Sep-15 Aug-15 Jul-15 Jun-15 May-15 Apr-15 Mar-15 Feb-15 Qtr Jan-15 Dec-14 Slice Range Qtr Nov-14 Oct-14 Sep-14 Aug-14 Jul-14 Jun-14 Qtr May-14 Apr-14 Mar-14 Feb-14 Jan-14 Time Slice Tables 6
www. regoconsulting. com Phone: 1 -888 -813 -0444 Feb-16 Jan-16 Dec-15 Nov-15 Oct-15 Sep-15 Aug-15 Jul-15 Jun-15 May-15 Qtr Apr-15 Mar-15 Slice Range Qtr Feb-15 Jan-15 Dec-14 Nov-14 Oct-14 Sep-14 Qtr Aug-14 Jul-14 Jun-14 May-14 Apr-14 Mar-14 Feb-14 Jan-14 Time Slice Tables Rollover 7
Types of Slices ● There are five DAILY out of the box slices. ● These slices are used to populate the datamart and some reporting tables. # 1 2 3 10 11 Slice Name DAILYRESOURCEAVAILCURVE DAILYRESOURCEACTCURVE DAILYRESOURCEESTCURVE DAILYRESOURCEALLOCCURVE DAILYRESOURCEBASECURVE Portions obtained from CA documentation www. regoconsulting. com 8 Phone: 1 -888 -813 -0444
Types of Slices ● There are four MONTHLY out of the box slices. ● These slices are not used to populate the datamart but are used in reporting. # 4 5 6 7 Slice Name MONTHLYRESOURCEACTCURVE MONTHLYRESOURCEESTCURVE MONTHLYRESOURCEALLOCCURVE MONTHLYRESOURCEAVAILCURVE Portions obtained from CA documentation www. regoconsulting. com 9 Phone: 1 -888 -813 -0444
Datamart Tables • Time bucketed PM Information • Weekly, monthly, quarterly and yearly time bucketed information at the OBS level • Summary information about projects • Project, resource and task information on a daily basis • Resource information • Datamart data is tied to the out of the box daily slices. • Datamart historical information is limited to the time slices. • Pre-Upgrade steps will clear the datamart tables. 10 www. regoconsulting. com Phone: 1 -888 -813 -0444
Common Columns ID CREATED_DATE CREATED_BY LAST_UPDATED_DATE LAST_UPDATED_BY Primary Key Date/time record was created User that created the record. Foreign key to CMN_SEC_USERS. ID Date/time record was last updated User that updated the record. Foreign key to CMN_SEC_USERS. ID 11 www. regoconsulting. com Phone: 1 -888 -813 -0444
DB Object Prefix Naming Convention 12 www. regoconsulting. com Phone: 1 -888 -813 -0444
DB Object Suffix Naming Convention 13 www. regoconsulting. com Phone: 1 -888 -813 -0444
Documentation ● CA Bookshelf ○ Contains Entity Diagram and Technical Reference guide for 13. x versions ○ One bookshelf for users. ○ One bookshelf for admins. ○ Also contains data model changes. 14 www. regoconsulting. com Phone: 1 -888 -813 -0444
Resources ● CMN_SEC_USERS ○ User / Logon ● SRM_RESOURCES ○ Resource Information ● PRJ_RESOURCES ○ Open for Time settings ● RSM_SKILLS ○ List of Skills ● RSM_SKILL_ASSOCIATIONS ○ Skill to Resource link Portions obtained from CA documentation www. regoconsulting. com 15 Phone: 1 -888 -813 -0444
Ideas ● INV_INVESTMENTS ○ ODF_OBJECT_CODE = ‘idea’ ● INV_IDEAS ○ Idea object attributes ● ODF_CA_IDEA ○ Custom Idea attributes ● PRTask ○ Hidden Idea Task Portions obtained from CA documentation www. regoconsulting. com 16 Phone: 1 -888 -813 -0444
Projects ● INV_INVESTMENTS ○ Main table ● INV_PROJECTS ○ Template, program ● PAC_MNT_PROJECTS ○ Financial Settings ● ODF_CA_PROJECT ○ Custom Project Attributes ● PRTeam ○ Team Members ● PRTask ○ Task Information ● PRAssignment ○ Assignment Information Portions obtained from CA documentation www. regoconsulting. com 17 Phone: 1 -888 -813 -0444
Baselines ● PRJ_BASELINES ○ ○ List of all baselines Flag for current Link to investment Project / Application type ● PRJ_BASELINE_DETAILS ○ Task / Assignment / Project level Portions obtained from CA documentation www. regoconsulting. com 18 Phone: 1 -888 -813 -0444
PRJ_BASELINES ● PRJ_BASELINES ○ Master list of all baselines in the system. ○ Updated for 13. 2 and above. ○ ○ ○ PROJECT_ID = Investment ID OBJECT_TYPE = Investment Type NAME = Baseline Name CODE = Baseline Code IS_CURRENT = Current BL Flag ○ ID = Baseline internal ID 19 www. regoconsulting. com Phone: 1 -888 -813 -0444
PRJ_BASELINE_DETAILS ● PRJ_BASELINE_DETAILS ○ Details of the baseline. ○ OBJECT_TYPE • • Project / Application Task Team Assignment ○ BASELINE_ID = ID from PRJ_BASELINE table. ○ OBJECT_ID = Instance ID of object_type ○ USAGE_SUM = Baselined Effort (Act + Remaining Effort) in seconds. ○ COST_SUM = Baselined Cost ○ DURATION = Effort duration 20 www. regoconsulting. com Phone: 1 -888 -813 -0444
Exercise #1 – Baseline Details Select INV. CODE , INV. NAME , PBD. START_DATE , PBD. FINISH_DATE , PBD. USAGE_SUM / 3600 , PBD. COST_SUM , PBD. DURATION FROM INV_INVESTMENTS INV Inner Join PRJ_BASELINES PB ON PB. PROJECT_ID = INV. ID Inner Join PRJ_BASELINE_DETAILS PBD ON PBD. BASELINE_ID = PB. ID Where PBD. OBJECT_TYPE = 'PROJECT' and PB. is_current = 1 21 www. regoconsulting. com Phone: 1 -888 -813 -0444
Master / Sub ● INV_HIERARCHIES_FLAT ○ This denormalized table stores data based on INV_HIERARCHIES. The flattened table contains parent_id and child_id entries for all descendants of a given investment parent_id that has a hierarchy. The link_source_id contains the ID of the immediate parent of the child. This table enables rapid retrieval of all descendants within a hierarchy. By examining the link_source_id, the original hierarchical order can also be retrieved. ● Can return Program/Master/Sub relationships. Portions obtained from CA documentation www. regoconsulting. com 22 Phone: 1 -888 -813 -0444
Master / Sub ● INV_HIERARCHIES_FLAT ○ Join to the INV_INVESTMENTS on the ID = PARENT_ID or CHILD_ID fields from the hierarchy table. ○ Same table is used for multiple purposes. ○ Filter for Program! • INV_PROJECTS. IS_PROGRAM ○ Reference the Investments twice • Once for Master. • Once for Sub. Portions obtained from CA documentation www. regoconsulting. com 23 Phone: 1 -888 -813 -0444
Exercise #2 – Master / Sub Select MAS. NAME Master. Name, MAS. CODE Master. Code, SUB. NAME Sub. Name, SUB. CODE Sub. Code FROM INV_HIERARCHIES IH Inner Join INV_INVESTMENTS MAS on MAS. ID = IH. PARENT_ID Inner Join INV_PROJECTS MP on MP. PRID = MAS. ID Inner Join INV_INVESTMENTS SUB on SUB. ID = IH. CHILD_ID Inner Join INV_PROJECTS SP on SP. PRID = SUB. id Where SP. IS_PROGRAM = 0 and MP. IS_PROGRAM = 0 Order by MAS. Code Portions obtained from CA documentation www. regoconsulting. com 24 Phone: 1 -888 -813 -0444
Portfolios Portions obtained from CA documentation www. regoconsulting. com 25 Phone: 1 -888 -813 -0444
Portfolios ● PFM_PORTFOLIOS ○ Main table that holds all portfolios. 26 www. regoconsulting. com Phone: 1 -888 -813 -0444
Portfolios ● PFM_INVESTMENTS ○ This table contains copy of investment attributes in the context of a portfolio. ○ Link to Investment ID is on this table. 27 www. regoconsulting. com Phone: 1 -888 -813 -0444
Exercise #3 – Portfolio Select INV. Name, INV. Code, PP. Name Portfolio. Name, pp. CODE Portfolio. Code From PFM_PORTFOLIOS PP, PFM_INVESTMENTS PI, INV_INVESTMENTS INV Where pi. Portfolio_id = PP. id and INV. ID = pi. investment_id Portions obtained from CA documentation www. regoconsulting. com 28 Phone: 1 -888 -813 -0444
Questions Contact US 888. 813. 0444 Email Contact Thank you for your time. info@regoconsulting. com Web Site www. regoconsulting. com 29 www. regoconsulting. com Phone: 1 -888 -813 -0444
- Slides: 29