Assign Dim Element - Based on Date Range

[CiskoWalt]

TM1.jpg (49.53 KiB) Viewed 3186 times

Hello,

Proposed Solution - Use a Cube to Store Window Attributes
The Source file does not have the VodWindow or the VodEntity, so as I load a file, I want to assign the VodWindow and the WindowEntity based on the Transaction date on the source file to a variable and then insert this record with its two new columns to a cube named VodDailySnapshots.

If the Transaction Date is between the WindowStartDate and the WindowEndDate for a given VodTitle, I should be able to get the VodWindow and VodEntity.

For each VodTitle, only one record will exist in the VodTitleWindowCube for a given date range. - Classic for example.

On the source file, I will know the Transaction date and the VodTitle (SF00001). I want to assign the VodWindow to a Variable and the WindowEntity to a variable, so that I can put these values in a cube.


Current Solution - Use Dimension Aliases
I have one working solution.

In the VodTitles dimesion:

Added one Attribute for the VodEntity.

#^^^ Get the Entity for the Vod Title. One Entity is asociated with each SF code.
sEntityCode = TRIM(CELLGETS ('}ElementAttributes_VodTitles', sSFCode, 'CurrentVodEntity'));


Added one Attribute (a start date) for each of the 3 Windows shown above. In a Ti Process I fetch the 3 assigned start dates to variables

nPreTheatricalWindowStartDateSerial = DAYNO(CELLGETS ('}ElementAttributes_VodTitles', sSFCode, 'PreTheatricalStartDate'));
nPremierWindowStartDateSerial = DAYNO(CELLGETS ('}ElementAttributes_VodTitles', sSFCode, 'PremierStartDate'));
nClassicWindowStartDateSerial = DAYNO(CELLGETS ('}ElementAttributes_VodTitles', sSFCode, 'ClassicStartDate'));

As the data is loaded the transaction date is compared to the Attribute dates and a Window is assigned.

#^^^ Get the Window for the the SFCode.

If (l_OrderDateSerial <= nPreTheatricalWindowStartDateSerial);
sWindow = 'PreTheatrical';
ElseIf ((l_OrderDateSerial > nPreTheatricalWindowStartDateSerial) & (l_OrderDateSerial < nClassicWindowStartDateSerial));
sWindow = 'Premier';
ElseIf(l_OrderDateSerial >= nClassicWindowStartDateSerial);
sWindow = 'Classic';
Else;
ItemReject('Window can not be assigned');
EndIf;


Insert the data to the Cube

#^^^ Load the Transaction 'View' Count - FOD is Free on Demand
IF (v_content_type @<> 'FOD');
iCurrVal = CELLGETN (cubRef1, sOrderDay, sEntityCode, v_operator_no, v_cable_system_no, sSFCode, sFormat, sWindow, sBalType, 'Buys');
CELLPUTN (iCurrVal+ v_transactions, cubRef1, sOrderDay, sEntityCode, v_operator_no, v_cable_system_no, sSFCode, v_sd_hd, sWindow, sBalType, '
Buys');
ENDIF


Although this works, I was informed that that there will be many Windows added in the future. The way I have it set up, evey time a Window is added, I would have to 1) Add a new Attribute for the StartDate for the new Window and 2) modify the Ti Process code. I don't think that this is the best solution.

If I keep track of the Window attributes using a cube then as soon as a new Window is added to the VodWindow dim It will be available in the cube. The user would have to add the WindowStart and WindowEnd date ranges and the Ti Code would not have to be modified.

Would appreciate some help in determining how/if this can be accomplished.

Thanks,

Walt

[Steve Rowe]

Hi,

Not sure if the date windows overlap or not, hopefully not.

I'm not 100% sure what you are trying to do but I think if you swap the data and the dimensions around you may have closer to what you need. By this I mean have a cube dimensioned by Day and VodTitle and so on. The day dimension will have all possible days in it.

You can then populate this cube with the relevent attribute information (vodWindow?) which should then be straight forward to access from the TI.

HTH

Steve

[CiskoWalt]

Thanks Steve,

My goal is to assign a Window and an Entity to a record that is loaded by a Ti Process. There will not be an overlap; on any given date, there will only be one Window for each VodTitle.

Source File

VodTitle: XXXX
Trans Date 4/1/2011
Volume: 10

What I like about the date Ranges is I would not need to have one record for each day. If the same Window is used for all of 2011 then one record could be used to capture this.

VODTitle: XXXX
Window: Classic
Entity: 888
StartDate: 1/1/2011
EndDate: 12/31/2011

As I load the file, if the transaction date is between the Start and EndDate, then use the Window and Entity that is associated with the Date range.

New Record

TransDate: 4/1/2011
VODTitle: XXXX
Window: Classic
Entity: 888
Volume: 10

[Steve Rowe]

Hi Cisko,

I can understand your preference for a date range as you have far less reference data to hold and maintain but as you are finding the decision making process you need to use in your TI is greatly complicated.

If you want to use the date range cube within your data load TI then you are going to need some fairly complex logic that gets evaluated for every line of your data. I can see a possible approach but I think it would really drive the speed of your data load down. It would probably go something like the following.

1. In the prolog establish the length of windows dimension
2. In the data tab write a while loop that does the date testing, something very roughly like the following

counter=1
While counter<=mxDimSiz
vWindow=dimnm ('window', counter);
startWindow = CubeRef ( ..... vWindow etc )
endWidnow = CubeRef ( ..... vWindow etc )
If ( startWindow <= DataDate <= endWindow);
#We found the right window
RightWindow=vWindow
entity = cubeRef ( etc)
counter=mxDimSiz
endIf
counter=counter+1
end (while)

Hopefully you can see how the logic will flow and that you would have to repeat it for every row of the data. Depending on the frequency of load and the length of the window dimension it might be too heavy a load for your users.

I'd still be inclined to write a TI that took my range based cube and built a cube with a day dimension so that I could get from a date to a window and an entity in a single step.

HTH

Cheers,

Steve