Advanced data model
This presentation is the property of its rightful owner.
Sponsored Links
1 / 29

Advanced Data Model PowerPoint PPT Presentation


  • 31 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

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


Time slice tables1

Time Slice Tables


Time slice tables2

Time Slice Tables

Rollover


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

    1DAILYRESOURCEAVAILCURVE

    2DAILYRESOURCEACTCURVE

    3DAILYRESOURCEESTCURVE

    10DAILYRESOURCEALLOCCURVE

    11DAILYRESOURCEBASECURVE

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

    4MONTHLYRESOURCEACTCURVE

    5MONTHLYRESOURCEESTCURVE

    6MONTHLYRESOURCEALLOCCURVE

    7MONTHLYRESOURCEAVAILCURVE

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.


Common columns

Common Columns


Db object prefix naming convention

DB Object Prefix Naming Convention


Db object suffix naming convention

DB Object Suffix Naming Convention


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.


  • Login