1 / 29

Maximizing Queries Using Advanced Data Model

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.

binh
Télécharger la présentation

Maximizing Queries Using Advanced Data Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced Data Model Maximizing Queries Using Tables

  2. 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!

  3. 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

  4. 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

  5. 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.

  6. Time Slice Tables

  7. Time Slice Tables Rollover

  8. 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

  9. 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

  10. 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.

  11. Common Columns

  12. DB Object Prefix Naming Convention

  13. DB Object Suffix Naming Convention

  14. 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.

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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

  24. 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

  25. Portfolios Portions obtained from CA documentation

  26. Portfolios • PFM_PORTFOLIOS • Main table that holds all portfolios.

  27. Portfolios • PFM_INVESTMENTS • This table contains copy of investment attributes in the context of a portfolio. • Link to Investment ID is on this table.

  28. 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

  29. Questions Contact US 888.813.0444 Email Contact info@regoconsulting.com Web Site www.regoconsulting.com Thank you for your time.

More Related