Clarity

CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 5/16/2011

Today we will take a closer look at the slice request table.

Clarity defines all slices in a table called prj_blb_slicerequests. This table has many fields, so I'll just explain some of the more useful ones below.

ID - This is simply the unique identifier for the request.

REQUEST_NAME - This is the name of the request.

FIELD - This column defines the type of data to be sliced (i.e. 0=assignments

FREQUENCY - This is the rollover frequency. The frequency represents an increment of time in which the window of data will shift. If the frequency is monthly, at rollover (start of month) the oldest month will be removed and a more recent month's worth of data will be added.

FROM_DATE - This is the starting time of the slice. (the starting date in which slice data will be generated)

PERIOD - This represents the detail level of the slice.
i.e. The data will be output in increments representing the following levels of aggregation.
0 = Daily
1 = Weekly
3 = Monthly

NUM_PERIODS - This column indicates the number of periods that will be sliced. (i.e. if we are slicing 10 weekly periods, the slice amounts would represent week totals for 10 weeks.)

EXPIRATION DATE - This is the date the slice request expires and needs to be

REQUEST_COMPLETED_DATE - This is the last time this request was processed. (A good way to see if your slicing is current.)

TABLE_NAME - This is the table in which the slices defined by this request are stored.

---------

In last week's time slicing discussion, the question came up as to how to write queries against a slice that is created form a time varying attribute, without hard-coding the table name, which could be different across instances.

This is a tricky one, because the table name needs to be determined at run time by something like:

select table_name from prj_blb_slicerequests where request_name = 'project::tva::day'

This can be done dynamically in PL/SQL using a number of techniques available in Oracle's documentation depending on the version you are using. These techniques all seem to use pl/sql so you won't be able to use these in NSQL. There may be some program-ability within Business Objects that would let you build these on the fly. (I'm not an expect in BO though.)

-shawn

Thanks for the great tip Shawn! Jump in folks with your comments even if it's just to let Shawn know if these tips are of value to you.

Thanks!
Chris

Thanks Shawn.. It's good to know Field details of Slice Requests table..

I would like to know couple of things related to Slice Request table

1. What does DATE_FLAGS and REQUEST_STATUS fields have role in this table ?

2. (Technically speaking) Can EXPIRATION DATE and REQUEST_COMPLETED_DATE be set to NULL to reset/rebuild a slice ?

Thanks
Sangeet

Sangeet,

Good questions:

1a) date_flags:

The date flags are used in calculating rollovers and from dates, but the logic isn't really clear to me. However, a value of 1 represents the "end of month" The seed data does show different values than 1 though.

1b) request_status:

This variable tells TimeSlice whether or not all the associated object records have had their slice_status fields updated to either the expired and full reslice values. If the value is 0 then timeslice will reset the slice_status values otherwise it will process as though they'd already been set. This flag was added to ensure we don't do the expensive reset if timeslice failed in a previous run after setting the values.


2) yes, you can reset the slice by setting both to null:

i.e.

update PRJ_BLB_SLICEREQUESTS set EXPIRATION_DATE = null, REQUEST_COMPLETED_DATE = null where ID= nnnn


-shawn