!"#$%&"'$% ()*+,+)
-+.$/01%$)2)34$.4"$5
Your%Guide:%Vipin%Chouhan ,% Rahul%Agrawal
67,.081',"07%
Let$Rego$be$your$guide.$
2
Take%5%Minutes
Turn%to%a%Person%Near%You
Introduce%Yourself
Ov erview%of%Timeslices /%Fundamentals
Timeslice Terminology
Types%of%Timeslices
SQL%con structs
Data%Warehouse%Fundamentals
DWH%Vs%CA%PPM%Slices
Examples
Let$Rego$be$your$guide.$
3
9:$78+
!"#$%&"'$ ;178+#$7,+&%
Let$Rego$be$your$guide.$
4
What%is%Timeslice?
Why%its%Important?
Let$Rego$be$your$guide.$
5
!"#$%&"'$ <1"'=)34$.4"$5
Let$Rego$be$your$guide.$
6
>?+#@&$A)BC3B)*+,+
CA%PPM:
BLOB%data%is%
Source%Data
PRO
Holds%huge%amounts%of%data
Compressed;%saves%s pace
Increased%performance
Legacy%format% MS%Proje ct,%Clarity,%Niku
CON
Reporting%tools%cannot%read
Let$Rego$be$your$guide.$
7
D$,,"7:)*+,+);0.)E$@0.,%F)*+%/G0+.8%F)*-HF)$,'I
Time$Slice$Tables
Reports,$Dashboards
Timeslice Job
Daily
Weekly
Bi-Weekly
Monthly
Quarterly
Yearly
…more
BLOB:%Binary%Large%Object
Data$Warehouse
CA%PPM:
BLOB%data%is%
Source%Data
BLO Bs%and%Timeslices
A%BLOB%is%a%database%data%type%and%acronym%for%Binary%Large%Object
BLOBs%in%CA%PPM%store%time-scaled%data%in%a%single%dat abase%data%entity
A%BLOB%may%sometimes%store%more%than%one%type%of%data
Reporting%tools%cannot%read%a%BLOB;%the%data%must%first%be%flattened
A%Timeslice is%a%flat%table%containing%da ta%extracted%from%a%sliced%BLOB
The%Timeslicing background%job%extracts%the%BLOB %data%into%a%flat%table%based%on%the%time%slice%
request%criteria%
Slice%requests
Determine%which%objects%are%time-sliced
Slice%size
Slice%request%rollover%interval
Each%record%in%the%timeslice table%contains%an%object%ID%that%corresponds%to%a%date%element
Let$Rego$be$your$guide.$
8
!"#$%&"'$ ;178+#$7,+&%
Why%use%BLOBs?
Decreases%database%size
Impr o ves%export%performance%for%BLOB- aware%client%applications%like%Open%Workbench%and%
Microsoft%Project
Allows%storage%of%multiple%data%points%as%opposed%to%individual%rows
Where%BLO Bs%are%used
Anywhere%in%CA%PPM%where%you%can%enter%values% over%time
Examples%include%Actuals%ETC%and%Baselines
Where%BLO Bs%are%not%used
Financial%transaction%data%do%not%use%BLOBs%and%are % instead%stored%in%the%PPA_WIP%and%
PPA_WIP_Values tables
Let$Rego$be$your$guide.$
9
!"#$%&"'$ ;178+#$7,+&%
BLO B%i n%raw%form%:
0x7E000000063A67007B0500019700000000029700000000000000000000F03F3A66007B4000007F0000020080700000C0A80000D0B6000010EF0000020080700
000C0A80000D0B6000010EF0000020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0B600001
0EF0000000000007D029700000000039700000000000000000000F03F3A66007B4000007F0000020080700000C0A80000D0B6000010EF0000020080700000 C0A8
0000D0B6000010EF0000020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0B6000010EF0000
000000007D039700000000049700000000000000000000F03F3A66007B4000007F0000020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0
B6000010EF0000020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0B6000010EF0000000000
007D049700000000059700000000000000000000F03F3A66007B4000007F0000020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0B60000
10EF0000020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0B6000010EF0000000000007D05
970000000006970000000000000000000000003A66007B4000007F0000020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0B6000010EF00
00020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0B6000010EF0000020080700000C0A80000D0B6000010EF0000000000007D40000000
0000000000000002007D
That%blob%represents%32%hours%of%time%entry%data%on%a%timesheet.%In%the%corresponding%slice%
tables%it%may%produce%records%such%as:
2017-02-14%00:00:00.000
8.0
2017-02-15%00:00:00.000
8.0
2017-02-16%00:00:00.000
8.0
2017-02-17%00:00:00.000
8.0
Let$Rego$be$your$guide.$
10
BC3B)$?+#@&$
!"#$%&"'$ !$.#"70&0:J
Let$Rego$be$your$guide.$
11
Slice$Request$ The%collection%of%parameters%for%a n%item%to%be%sliced
Stored%as%a%Row%in%the%prj_blb_slice_requests table
User%defined%and%s ystem%defined%Slice%Requests%show%as%a%line%item%on%the%Time%Slices%Page%in%CA%PPM
Slice%Requests%not%configurable%via%the%CA%PPM%UI%do%not%show%on%the%timeslice page
Timeslice Range$ The%moving%window%of %t ime%a%timeslice request%covers
For%example,%a%12%month%range%configured%for%six%months%past%and%six%months%future%shows% a%time%range%six%
months%prior%and%six%months%ahead%of%the%c urre nt%month
Rollover$ A%timeslice job%action%that%shif ts%or%moves %t he%timeslice range
For%example,%a%monthly%rollover%shifts%the%timeslice range%forward%one%month%each%month
Let$Rego$be$your$guide.$
12
!"#$%&"'$ !$.#"70&0:J
Let$Rego$be$your$guide.$
13
!"#$%&"'$ 98#"7"%,.+,"07
Navigate%to:%Administration%/%Data%Administration%%/%Timeslices
ROLLOVER$INTERVAL:$
Frequency%with%which%CA%
PPM%updates%the%From%Date%
and%shifts%the%time%slice%
range.%
FROM$DATE: The%
beginning%date%in%the%
Slice%Requests%range.%
SLICE$PERIOD: Specifies %
the%period%to%use%for%each%
time%slice%request.
NUMBER$OF$PERIODS: The%
number%of%periods%reported.
EXPIRAT I ON:$The%next%
rollover%date.
LAST$RUN$DATE: Last%time%
the%time%slice%job%processed%
a%particular%slice%request.%
Data$Warehouse:$
Indicat es%if%the%slice%is%
used%in%the%DWH
Let$Rego$be$your$guide.$
14
E+7:$)978)E0&&04$.)>?+#@&$
Jan Feb
Mar
Apr May
Jun
Jul Aug Sep Oct Nov Dec
From%Date
Rollover
NUMBER$OF$PERIODS: number%of%buckets%(12)
Slice$Period: Monthly
!J@$%)3K)!"#$%&"'$%
Let$Rego$be$your$guide.$
15
Standard%Out-of-the-Box
Basic%daily%timeslices
Slice%ID%1% 11%Populate s%the%Legacy%
Datamart
Slice%ID%1% 11%Used%in%Legacy%
BusinessO bjects Reports
Slice%ID%55555%Used%in%Data%Warehous e%and%
Jaspersoft Reports
Keep%daily%ranges%to%a%minimum!
Recommended%Range%90%days%past%and%90%
days%future
Let$Rego$be$your$guide.$
16
!J@$%)3K)!"#$%&"'$%
ID
Request$Name
1
DAILYRESOURCEAVAILCURVE
2
DAILYRESOURCEACTCURVE
3
DAILYRESOURCEESTCURVE
10
DAILYRESOURCEALLOCCURVE
11
DAILYRESOURCEBASECURVE
55555
DAILYRESOURCETIMECURVE
Standard%Out-of-the-Box,%cont.%
Basic%weekly%timeslices
Not%used%in %the%legacy%Datamart
Used%directly%in%legacy%BusinessObjects reports%
and%un iverses
Used%to%populate%Data%Warehouse
Recommended%Range%26%W eeks%past%and%26%
Weeks%future
Can%extend%range%further%to%52%Weeks%past%and%
future%if%needed
Let$Rego$be$your$guide.$
17
!J@$%)3K)!"#$%&"'$%F)L07,M)
ID
Request$Name
55556
WEEKLYRESOURCEACTCURVE
55557
WEEKLYRESOURCEESTCURV E
55558
WEEKLYRESOURCEALLOCCURVE
55559
WEEKLYRESOURCEAVAILCURVE
300
WEEKLYRESOURCEBASECURVE
306
WEEKLYRESOURCEHARDALLOC
307
WEEKLYBASEASSING
COST
Standard%Out-of-the-Box,%cont.%
Basic%monthly%timeslices
Not%used%in %the%legacy%Datamart
Used%directly%in%legacy%BusinessObjects reports%
and%Universes
Used%to%populate%Data%Warehouse
Recommended%Range%12%Months%past%and%12%
Months%future
Can%extend%range%further%to%36%months% past%and%
future%if%needed
Let$Rego$be$your$guide.$
18
!J@$%)3K)!"#$%&"'$%F)L07,M)
ID
Request$Name
4
MONTHLYRESOURCEACTCUR VE
5
MONTHLYRESOURCEESTCURVE
6
MONTHLYRESOURCEALLOCCURVE
7
MONTHLYRESOURCEAVAILCURVE
312
MONTHLYRESOURCEBASECURVE
305
MONTHLYBASEASSING
COST
55560
MONTHLYCURRENTBASEPROJUSAGE
Internal%timeslices,%a.k.a.% Hidden%or%InstaSlices
Used%in%time-scaled%views%in%CA%PPM%UI
I.E.%Tea m %Detail%page%and%Resource%Planning%portlets
Sliced%i nstantly%when%the%BLOB%data%i s%modified%in%the%CA%PP M %U I
Out-of-the-Box%Range%Limited%to%28%daily,%16%weekly,%36%monthly
Separate%slice%req u ests%for%daily,%weekly,%and%monthly
Item%slices%are%Actuals,%Estimates,%Base li n e,%Allocations%(soft%and%hard),%Availability,%
Capacity%Scenarios,%and%Requisitions
Data%stored%in%table%name%based%on%request%with%PRJ_BLB_SLICES_%prefix
For%example,%PR J_BLB_SLICES_M_ACT for%Actuals%Monthly%
Let$Rego$be$your$guide.$
19
!J@$%)3K)!"#$%&"'$%F)L07,M)
Internal%timeslices,%a.k.a.% Hidden%or%InstaSlices Contd.
There%is%an%approved%CA%insta-slice%extension%script%that%can%run%on%the%server%to%
extend%the%out -of-the-box%internal%timeslice range.
Let$Rego$be$your$guide.$
20
!J@$%)3K)!"#$%&"'$%F)L07,M)
Old$Slice$Ranges$(before$running$script)
New$Slice$Ranges$(After$running$script)
28$Daily$
-
7%week%in%the%p ast,%the%current%day%
and%3%weeks%in%the%future.
42$Daily$
- 7%days%in%the%past,%the%current%day%
and%4%weeks%in%the%future.
16$Weekly$
- 2%weeks%in%the%past,%the%current%
week%and%13%weeks%in%the%future.
45$Weekly$
- 4%weeks%in%the%past,%the%current%
week%and%40%weeks%in%the%future.
36$Monthly$
- 4%quarters%in%the%past,%the%
current%quarter%and%7%quarters%in%the%future.
51$Monthly$
- 4%quarters%in%the%past,%the%
current%quarter%and%12%quarters%in%the% fu ture
Actuals”%slices%can%be%furthe r%split%and%moved%in%the%past%as%
future%“actuals”%are%rare.
New%Fiscal%Timeslices CA%PPM%14.2%and%above
Used%primar ily%for%the%Data %Warehouse
Only%seeded%Slices%Requests
No%new/custom%Fiscal%slice%requests%can%be%created
Slice%request%definition%derived%from%Monthly%Fiscal%Calendar
Only%one%entity%used%set%on%Data%War ehouse%properties%
Data%stored%in%PRJ_FISCAL_BLB_SLICES
No%Slice_date column
PRJ_FISCAL_BLB_SLICES.PERIOD_ID%joins%to%BIZ_COM_PERIODS%table
Let$Rego$be$your$guide.$
21
!J@$%)3K)!"#$%&"'$%F)L07,M)
ID
Request$Name$(Subset)
Ite m
1000029
assignment::
practcurve::dwh_fiscal
Actuals
1000037
team::
alloccost_curve::dwh_fiscal
Allocation
1000031
resource::
pravailcurve::dwh_fiscal
Availability
N<C)L07%,.1',%
Let$Rego$be$your$guide.$
22
Let$Rego$be$your$guide.$
23
N<C)L07%,.1',%
Slice%Records%For%Actuals%and%Estimates
--slice%records%for%actuals
SELECT
Slice,
slice_date,
r.f u l l_na m e ,
t.prname Task_name,
p.name project
FROM
prj_blb_slices s,
pr assignment a,
prtask t,
srm_resources r,
srm_projects p
WHERE
s.prj_object_id = a.prid and
a.prtaskid = t.prid and
t.prprojectid = p.id and
a.prreso urceid = r. i d and
p.unique_name like 'PROJECT%UNIQUE%NAME%HERE' and
r.unique _ name like 'RESOURCE%UNIQUE%NAME' and
slice_request_id = 2
--slice%records%for%estimates
SELECT
Slice,
slice_date,
r.f u l l_na m e ,
t.prname Task_name,
p.name project
FROM
prj_blb_slices s,
pr assignment a,
prtask t,
srm_resources r,
srm_projects p
WHERE
s.prj_object_id = a.prid and
a.prtaskid = t.prid and
t.prprojectid = p.id and
a.prreso urceid = r. i d and
p.unique_name like 'PROJECT%UNIQUE%NAME%HERE' and
r.unique _ name like 'RESOURCE%UNIQUE%NAME' and
slice_request_id = 3
Link%slices%table%to%the%assignment%table
Let$Rego$be$your$guide.$
24
N<C)L07%,.1',%F)L07,M)
Link%slices%table%to%the%team%table%for%allocations%
Link%slices%table%to%resources%table%for%availability
--slice%records%for%allocation
SELECT
Slice,
slice_date,
r.f u l l_na m e ,
p.name project
FROM
prj_blb_slices s,
prteam t,
srm_resources r,
srm_projects p
WHERE
s.prj_object_id = t.prid and
t.prprojectid = p.id and
t.prresourceid = r. i d and
p.unique_name like 'PROJECT%UNIQUE%NAME%HERE' and
r.unique _ name like 'RESOURCE%UNIQUE%NAME' and
slice_request_id = 10
--slice%records%for%availability
SELECT
Slice,
slice_date,
r.f u l l_na m e ,
FROM
prj_blb_slices s,
srm_resources r
WHERE
s.prj_object_id = r.i d and
r.unique _ name like 'RESOURCE%UNIQUE%NAME' and
slice_request_id = 1
Slide%Records%for%Allocations%and%Availability
*+,+)-+.$/01%$)
;178+#$7,+&%
Let$Rego$be$your$guide.$
25
*+,+)-+.$/01%$)9.'/",$',1.$
CA#PPM#
Database
Data$
Warehouse
CA#PPM#
Application
Load#Data#
Warehouse# Job
Jaspersoft Reports,$Ad$Hoc$Views$&$Domains
Data%Model%is%easy%to%q u ery.
The%Data%Warehouse%has%its%own%database%which%takes%stress%off%the%transactional%CA%
PPM%database.%Because%the%Data%Warehouse%dat abase%is%separate%from%the%CA%PPM%
database,%it%can%be%tuned %differently%for%optimal%performance.
The%Data%Wareh ouse%carrie s%keys%and%descriptive%values%in%the%dimension%tables%so%that%
fewer%joins%are%required.%Facts%are%combin ed%into%summary%and%period%tables.
Columns%ar e%named%consistently%across%tables.%
Similar%tables%are%grouped%togethe r%by%the%tabl e%prefix,%and%the%names%are%descriptive.%
The%ability%to%query%the%data%warehouse%and%CA%PPM%transactional%database%within%the%
same%report%is%seamless.
Report%output%is%returned%faster%using%the%data%ware h ouse.
Let$Rego$be$your$guide.$
27
*+,+)-+.$/01%$)B$7$K",%
Each%Custom%Object%and%Custom%Attribute%has%Include%in%Data%
Warehouse%check%option
Let$Rego$be$your$guide.$
28
*+,+)-+.$/01%$A)L1%,0#)3GO$',(9,,."G1,$%
Following%40%Time%slices%(%25%New%and%15%existing)%are%included%in%DWH
Let$Rego$be$your$guide.$
29
*+,+)-+.$/01%$A)!"#$%&"'$%
Existing
Slices
New$Slices
DAILYRESOURCETIMECURVE%
WEEKLYRESOURCEACTCURVE%
WEEKLYRESOURCEESTCURVE%
WEEKLYRESOURCEBASECURVE%
WEEKLYBASEASSIGNCOSTS%
WEEKLYRESOURCEAVAILCURVE%
WEEKLYRESOURCEALLOCCURVE%
WEEKLYRESOURCEHARDALLOC%
MONTHLYRESOURCEACTCURVE%
MONTHLYRESOURCEESTCURVE
MONTHLYRESOURCEBASECURVE
MONTHLYBASEASSIGNCOSTS
MONTHLYRESOURCEAVAILCURVE
MONTHLYRESOURCEALLOCCURVE%
MONTHLYRESOURCEHARDALLOC
assignment::practcurve::dwh_fiscal
assignment::prest curve::dwh_fiscal
assignment::actcost_ curve::dwh_week
assignment::actcost_ curve::dwh_month
assignment::actcost_ curve::dwh_fiscal
assignment::etccost_curve::dwh_week
assignment::etccost_curve::dwh_month
assignment::etccost_curve::dwh_fiscal
baseline::current_assignment_usage::dwh_fiscal
baseline::current_assignment_cost::dwh_fiscal
baseline::current_team_usage::dwh_week
baseline::current_team_usage::dwh_month
baseline::current_team_usage::dwh_fiscal
baseline::current_team_cost::dwh_week
baseline::current_team_cost::dwh_month
baseline::current_team_cost::dwh_fiscal
resource::pravailcurve::dwh_fiscal
team::pr alloccurve::dwh_fiscal
team::hard_curve::dwh_fiscal
team::alloccost_curve::dwh_week
team::alloccost_curve::dwh_month
team::alloccost_curve::dwh_fiscal
team::hardallccost_curve::dwh_week
team::hardallccost_curve::dwh_month
team::hardallccost_curve::dwh_fiscal
Daily%=%1
Weekly%=%13
Monthly%=%13
Fiscal%=%13
Review%the%new%Data%W arehouse%Time%Slices%
Go%to%Administration%->%Time%Slices
Modify%if%necessary
Let$Rego$be$your$guide.$
30
*+,+)-+.$/01%$A)!"#$)N&"'$%
DWH%time%slice%requests%def ault%to%
the%following%time%periods
Daily:$1%year%back,%1%year%forward
The%From%Date%of%the%slice%request%is%
one%year%prior%to%the%system%date
Weekly:%1%year%back,%1%year%forward
The%From%Date%is%one%year%prior%to%the%
system%date%and%is%the%same%day%as%the%
First&Day&of&Work&W eek
Monthly :%3%years%back,%3%years%forward
The%From%Date%is%three%years%prior%to%
the%system%date%and%is%always%the%first%
day%of%the%month%
Fiscal:%3%years%back,%3%years%forward%
The%From%Date%is%three%years%prior%to%
the%system%date%and%corresponds%to%
the%start%date%of%a%Monthly%(or%13%
Period)%fiscal%period%type%that%is%based%
on%the%Entity&for&Fiscal&Periods value%in%
Data%Warehouse%Options
Slices%that%are%
in%DWH
Data$Warehouse
Use%DWH%when%huge%amount%of%data%is%needed%without%impacting%performance%of%
application%datab ase.
For%relatively%faster%query%performance%due%to%lesser%table%joins.%
Ease%of%use%due%to%consistent%table%and%column%names.
If%DWH%is%set%on%diffe rent%physical%database%server,%Complex%queries%execution%doesn’t%
impact%application%database%performance.%
When%Data%date%range%required%is%limited%to%Fiscal%Time%periods
Slices
Use%Slices%when%re al-time% data%is%needed
When%there%is%need%of%DAILY%slices%that%are%NOT%in%DWH.
Due%to%some%constraints% when%DWH%can’t%be%run%(i.e,%DB%size,%performance%considerations)
Let$Rego$be$your$guide.$
31
*-H)4%M)L9)PPQ)N&"'$%)R -/$7),0)S%$)T
Let$Rego$be$your$guide.$
32
>?+#@&$A)3&8) !$+#)<1$. J)UL9)PPQ)* +,+G+%$ V
Let$Rego$be$your$guide.$
33
>?+#@&$A)W$5)!$+#)<1$.J)U*+,+)-+.$/01%$V
Prefix
Table
DWH_CFG
Configuration%tables%used%to%supply%the%DWH%with%log%and%audit%information
DWH_CMN
Common%database%objects%used%across%most%areas%
DWH_CMP
Company%database%objects
DWH_FIN
Financial%management%database%objects
DWH_INV
INV%Investment%management%database%objects%
DWH_LKP
Lookup%database%objects
DWH_META
Meta%data%tables%that%help%determine%the%DW%structure%
DWH_ODF
Customer%specific%database%objects
DWH_PFM
Portfolio%management%database%objects
DWH_RES
Resource%management%database%objects
DWH_RIM
Risk%and%Issue%mana gement%database%objects%
DWH_TME
Time%management%database%objects
DWH_X
Internal%database%objects%used%to%help%populate%the%DW%f act%tables%
Let$Rego$be$your$guide.$
34
*+,+)-+.$/01%$)X*-HY)P.$K"?)N,+78+.8%
Let$Rego$be$your$guide.$
35
*+,+)-+.$/01%$)L07,$7,
Data$$Warehouse$Content
Change%Request%Management
Issue%Management
WBS%Structure
Exchange%Rates
OBS%Hierarchy
WIP%Transactions
Financial%Benefit%Plan
Portfolio%(High%Level)
Facts%by%Weekly/Monthly/Fiscal
Financial%Budget/Cost%Plans
Resource%Assignments
Summary%Facts
Investment%
Applications
Resources
All%Associated%Lookups
Investment%
Assets
Resource%User%Security
Customer%Specific%Attributes
Investment%
Ideas
Risk%Management
TSV%Values
Investment%
Other%Work
Team%Allocations
Summary%Facts
Financial%Budget/Cost%Plans
Resource%Assignments
Summary%Investment%EV%Data
Investment%
Product s
Time%Entry
Current%Baseline%Data
Investment%
Project s
Time%Entry%Notes
PMO%Accelerator
Investment%
Services
Time%Sheets
DB%Link%for%Missing%Data
Investment%
User%Security
Time%Sheet%Notes
New%Cost%Slices%
ETC/Allocations
Let$Rego$be$your$guide.$
<1$%,"07%T
Instructions$for$PMI$credits$
Access%your%account%at%pmi.org
Click%on%Certification
Click%on%Maintain$My$Certification
Scroll%down%to%Report$PDU’s
Click%on%Course%Training%(or%other%appropriate%category)
Enter Rego Consulting
Enter%Activity- Enter$Name$of$Course
Enter Description
Enter%Date$Started
Enter Date$Completed
Provide%Contact%Person%Name$of$Person$to$Contact
Provide%Contact%E-Mail%E-Mail$of$Person$to$Contact
Enter%Number%of%PDU’s$Claimed$(1%PDU%per%course%hour)
Click%on%the%I$agre e $this$claim$is$accurate$box
Click%Submit$button
Let$Rego$be$your$guide.$
37
!/+7=)Z01);0.)9,,$78"7:).$:0S7"4$.%",J
Phone
888.813.0444
Email
info@regouniversity.com
Website
www.regouniversity.com
Let%us%know%how%we%can%improve!%
Don’t%forget%to%f ill%out%the%class%survey.