Transcript Advanced Data Model
Advanced Data Model
Maximizing Queries Using Tables
www.regoconsulting.com
Phone: 1-888-813-0444
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!
www.regoconsulting.com
Phone: 1-888-813-0444 2
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 DataMart is populated via several job processes – Rate Matrix Extraction, DataMart Extraction and Datamart Rollup 3 www.regoconsulting.com
Phone: 1-888-813-0444
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 4 www.regoconsulting.com
Phone: 1-888-813-0444
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. 5 www.regoconsulting.com
Phone: 1-888-813-0444
Time Slice Tables
Qtr Qtr Slice Range Qtr Qtr www.regoconsulting.com
Phone: 1-888-813-0444 6
Time Slice Tables
Rollover
Qtr Qtr Slice Range Qtr Qtr www.regoconsulting.com
Phone: 1-888-813-0444 7
Types of Slices
● ● There are five DAILY out of the box slices.
These slices are used to populate the datamart and some reporting tables.
#
1 2 3 10 11
Slice Name
DAILYRESOURCEAVAILCURVE DAILYRESOURCEACTCURVE DAILYRESOURCEESTCURVE DAILYRESOURCEALLOCCURVE DAILYRESOURCEBASECURVE
Portions obtained from CA documentation
www.regoconsulting.com
Phone: 1-888-813-0444 8
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.
#
4 5 6 7
Slice Name
MONTHLYRESOURCEACTCURVE MONTHLYRESOURCEESTCURVE MONTHLYRESOURCEALLOCCURVE MONTHLYRESOURCEAVAILCURVE
Portions obtained from CA documentation
www.regoconsulting.com
Phone: 1-888-813-0444 9
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. 10 www.regoconsulting.com
Phone: 1-888-813-0444
Common Columns
ID
CREATED_DATE CREATED_BY LAST_UPDATED_DATE LAST_UPDATED_BY
Primary Key
Date/time record was created User that created the record. Foreign key to CMN_SEC_USERS.ID
Date/time record was last updated User that updated the record. Foreign key to CMN_SEC_USERS.ID
www.regoconsulting.com
Phone: 1-888-813-0444 11
DB Object Prefix Naming Convention
Component
Business Development Business Process Management Calendaring Capacity Planning Collaboration Common PMO Accelerator Datamart Discussions Object Definition Fields COP NBI NTD ODF Project Accounting Portfolio Management PAC PFM Project/Portfolio Management PRJ Resource Management RSM Shared SRM Objects eXtensible Data Model
ID
BIZ BPM CAL CAP CLB CMN SRM XDM www.regoconsulting.com
Phone: 1-888-813-0444 12
DB Object Suffix Naming Convention
Type
Primary key constraints and indexes Foreign key constraints Check constraints Unique indexes Non-unique indexes Sequences (Oracle only) Triggers Packages Stored Procedures Functions Views Aggregate/Summary tables FKx CKx Ux Nx Sx
Type ID
PK TRGx or Tx PKG SP FCT V SUM
Description
Primary key constraints need to be named explicitly as they control the name of the primary key index (which is automatically created) Foreign key constraints need to be named explicitly (“alter table XYZ add XYZ_FK1 foreign key …”) i.e. CK1 or CK2 i.e. U1 i.e. N1 or N2 i.e. S1 or S2. S1 is used for primary key sequences only, i.e. CMN_LOOKUPS_S1.
i.e. TRG1 or TRG2, T1 or T2 13 www.regoconsulting.com
Phone: 1-888-813-0444
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. www.regoconsulting.com
Phone: 1-888-813-0444 14
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
www.regoconsulting.com
Phone: 1-888-813-0444 15
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
www.regoconsulting.com
Phone: 1-888-813-0444 16
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
www.regoconsulting.com
Phone: 1-888-813-0444 17
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
www.regoconsulting.com
Phone: 1-888-813-0444 18
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 www.regoconsulting.com
Phone: 1-888-813-0444 19
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 www.regoconsulting.com
Phone: 1-888-813-0444 20
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 www.regoconsulting.com
Phone: 1-888-813-0444 21
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
www.regoconsulting.com
Phone: 1-888-813-0444 22
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.
INV_INVESTMENTS (Parent) ID = PRID INV_PROJECTS IS_PROGRAM IS_TEMPLATE INV_HIERARCHIES_FLAT PARENT_ID = ID CHILD_ID = ID INV_INVESTMENTS (Child) ID = PRID INV_PROJECTS IS_PROGRAM IS_TEMPLATE
Portions obtained from CA documentation
www.regoconsulting.com
Phone: 1-888-813-0444 23
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 Where on SP.PRID = SUB.id
SP.IS_PROGRAM = 0 and MP.IS_PROGRAM = 0 Order by MAS.Code
Portions obtained from CA documentation
www.regoconsulting.com
Phone: 1-888-813-0444 24
Portfolios
Portions obtained from CA documentation
www.regoconsulting.com
Phone: 1-888-813-0444 25
Portfolios
● PFM_PORTFOLIOS ○ Main table that holds all portfolios. www.regoconsulting.com
Phone: 1-888-813-0444 26
Portfolios
● PFM_INVESTMENTS ○ This table contains copy of investment attributes in the context of a portfolio.
○ Link to Investment ID is on this table. www.regoconsulting.com
Phone: 1-888-813-0444 27
Exercise #3 – Portfolio
Select INV.Name, INV.Code, PP.Name PortfolioName, pp.CODE PortfolioCode 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
www.regoconsulting.com
Phone: 1-888-813-0444 28
Questions
Thank you for your time.
www.regoconsulting.com
Phone: 1-888-813-0444 Contact US 888.813.0444
Email Contact [email protected]
Web Site www.regoconsulting.com
29