290 likes | 391 Vues
Explore high-level overview, core tables, time slices, datamart, and database details to enhance query performance. Learn about common columns, object types, and naming conventions for efficient data access. Resources and documentation guide included.
E N D
Advanced Data Model Maximizing Queries Using Tables
Summary • High Level Overview • Core Tables • Time Slices • Datamart • Database • Common Columns • Table Naming Convention • Object Types • Where to find documentation • Object Details • Ideas • Projects • Database Details • Resources • Ideas • Projects • Baselines • Master / Sub • Portfolios • Open Discussion!
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 • DataMart Tables • Provides Summary and Rollup Data • DataMartis populated via several job processes – Rate Matrix Extraction, DataMart Extraction and DatamartRollup
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 • PrTimeEntry
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.
Time Slice Tables Rollover
Types of Slices • There are five DAILY out of the box slices. • These slices are used to populate the datamart and some reporting tables. # Slice Name 1 DAILYRESOURCEAVAILCURVE 2 DAILYRESOURCEACTCURVE 3 DAILYRESOURCEESTCURVE 10 DAILYRESOURCEALLOCCURVE 11 DAILYRESOURCEBASECURVE Portions obtained from CA documentation
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. # Slice Name 4 MONTHLYRESOURCEACTCURVE 5 MONTHLYRESOURCEESTCURVE 6 MONTHLYRESOURCEALLOCCURVE 7 MONTHLYRESOURCEAVAILCURVE Portions obtained from CA documentation
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.
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.
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
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
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
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
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
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
Exercise #1 – Baseline Details Select INV.CODE ,INV.NAME ,PB.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
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
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
Exercise #2 – Master / Sub Select MAS.NAME MasterName, MAS.CODE MasterCode, SUB.NAME SubName, SUB.CODE SubCode 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
Portfolios Portions obtained from CA documentation
Portfolios • PFM_PORTFOLIOS • Main table that holds all portfolios.
Portfolios • PFM_INVESTMENTS • This table contains copy of investment attributes in the context of a portfolio. • Link to Investment ID is on this table.
Exercise #3 – Portfolio Select INV.Name, INV.Code, PP.NamePortfolioName, pp.CODEPortfolioCode 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
Questions Contact US 888.813.0444 Email Contact info@regoconsulting.com Web Site www.regoconsulting.com Thank you for your time.