advanced data model
Download
Skip this Video
Download Presentation
Advanced Data Model

Loading in 2 Seconds...

play fullscreen
1 / 29

Advanced Data Model - PowerPoint PPT Presentation


  • 51 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