Clarity

Hello All,

Can anyone tell, from which table time scaled values are populated in weekly details portlet?

Thanks,

Pragya Singh

Hey Pragya,

since the portlet uses the standard data provider "Team Allocations", I'm afraid there is not a real table behind this which contains the data as shown, strictly speaking.

It is most likely derived from the column PRALLOCCURVE in the table PRTEAM. It's from type BLOB.

It stores a curve, which is a binary object storing the information throughout the time. This makes it possible to aggregate the data to any custom time scaled value and type of period.

However, the Time Slicing job will pre-calculate slice data based on these curves.

Your best job would probably be the Slice "WEEKLYRESOURCEALLOCCURVE" which calculates Team allocations on a weekly basis. You can look this up in the database Table "PRJ_BLB_SLICES", but you need to filter for the correct Slice request id.

If the time span of the slice is not sufficient, you have to configure it in Clarity UI. This should only be done after evaluating this on a test system.

Let me know should you need more input on this case.

Kind Regards,

Dennis

Hello Dennis,

We need to find out from which slices, the ETC and Baseline usage value is populated in Weekly details portlet.

Can you please help us in this.

Thanks,

Pragya

Have you tried taking an SQL trace to check which slice is used ?

NJ

Also, check this -

https://communities.ca.com/message/99533392#99533392

NJ

Hi Pragya,

I think they are populated from W_ETC(108) and W_Base(116) slicerequests.

Regards

Chandrani

Thank you everyone for the reply.

Chandrani,

Can we modify these slices to extend the range?

Thanks,

Pragya Singh

Pragya,

Yes you can. But its limited to some range. It depends on what you have now. It can be extended upto :

42 Daily - 7 days in the past, the current day and 4 weeks in the future
45 Weekly - 4 weeks in the past, the current week and 40 weeks in the future
51 Monthly - 4 quarters in the past, the current quarter and 12 quarters in the future

If you would like to extend them, please raise a case with support who would assist you for the same.

Regards

Chandrani

This is a sample query from the trace (most likely from v12.x)

  weekly detail query 2  slice values from the insta slices

  SELECT   PRTEAM.PRID ID

                 , periods.start_date PERIOD

                 , SUM(slices.slice) TOTAL

          FROM    PRJ_BLB_SLICES_W_ALC slices

                  ,(SELECT  WEEK_KEY

                            , min(day) start_date

                            , max(day) end_date

                    FROM nbi_dim_calendar_time

                    WHERE day = '2012-12-03 000000.0'--1

                    AND day  '2013-01-14 000000.0'--2

                    GROUP BY WEEK_KEY

                  ) periods

                  , INV_INVESTMENTS

                  , PRTEAM

                  , SRM_RESOURCES RS

                  , PRJ_RESOURCES PRES

          WHERE   slices.SLICE_DATE = periods.start_date

          AND         slices.SLICE_DATE = periods.end_date

          AND            slices.SLICE_DATE = '2012-12-03 000000.0'--3

          AND slices.SLICE_DATE  '2013-01-14 000000.0' --4

          AND     slices.PRJ_OBJECT_ID = PRTEAM.PRID

          AND     RS.ID = slices.RESOURCE_ID

          AND     PRES.PRID = slices.RESOURCE_ID

          AND     INV_INVESTMENTS.ID = slices.INVESTMENT_ID

                AND            ISNULL((SELECT IS_TEMPLATE FROM INV_PROJECTS WHERE INV_PROJECTS.PRID = INV_INVESTMENTS.ID), 0) = 0

            AND INV_INVESTMENTS.IS_ACTIVE = 1--5

            AND PRTEAM.PRID  

            IN (5000209,5000179,5000182,5000187,5000176,5000175,5000188,5000140,5000153,5000166,5000141,5000164,5000233,5000151,5000142

            ,5000220,5000152,5000148,5000149,5000138) GROUP BY PRTEAM.PRID, periods.start_date

Umas' response is closer the values are stored in time slice tables such as PRJ_BLB_SLICES_W_ALC depending on what data is shown in the time scale and what time period type it shows.

Regarding the time ranges Naga's post applies for those slices.