Advanced data model
Download
1 / 29

Advanced Data Model - PowerPoint PPT Presentation


  • 49 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Advanced Data Model' - kueng


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Advanced data model

Advanced Data Model

Maximizing Queries Using Tables


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




Types of slices
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 slices1
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
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
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
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
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
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
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

  • 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

  • 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
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
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 sub1
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
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
Portfolios

Portions obtained from CA documentation


Portfolios1
Portfolios

  • PFM_PORTFOLIOS

    • Main table that holds all portfolios.


Portfolios2
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
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
Questions

Contact US

888.813.0444

Email Contact

[email protected]

Web Site

www.regoconsulting.com

Thank you for your time.


ad