TI referencing variables not in data source

[kenship]

Hi,

I'm trying to use TI to break down monthly budget into weekly budget. I built a cube to store the number of days in a week that belongs to a certain month. For example: If the week begins Sunday, then there will be 4 days in the week of Jun 27th to July 3rd that belong to June and 3 days belong July.

3 cubes are involved.
Cube 1: Monthly Budget
Dimension 1: Year
Dimension 2: Month
Dimension 3: Sales $

Cube 2: Number of days in week
Dimension 1: Year
Dimension 2: Month
Dimension 3: Week
Dimension 4: Number of days

Cube 3: Weekly Budget
Dimension 1: Year
Dimension 2: Month
Dimension 3: Week
Dimension 4: Sales $

I have been using rules to send the monthly budget data in Cube 1 to Cube 3 via the allocation data in Cube 2 without problem.

Now I want to convert it into TI. However since the data source (Cube 1) does not have week as dimension I don't know how I can setup CellPutN to refer to the number of days stored in Cube 2.

Any advice is appreciated.

Kenneth

[Wim Gielis]

To refer to a cube, wouldn't it be CellGetN rather than CellPutN ?

[kenship]

Dear Wim,

Allow me to clarify using an example.

Cube 1: Monthly Budget
Year = 2021
Month = June
Sales $ = 100

Cube 2: Number of days in week
Year = 2021
Month = June
Week = 23, 24, 25, 26, 27
Number of days = 4 (week 23), 7 (week 24-26), 5 (week 27)

Cube 3: Weekly budget
Year = 2021
Month = June
Week = 23, 24, 25, 26, 27
Sales $:
week 23, June's portion would be 4 days/30 days * $100 =$13.33, plus May's portion
week 24-26, 7 days/30 days * 3 weeks * $100 = $70
week 27, 5 days / 30 days * $100 =$16.67, plus July's portion
Total June = $100

Since I use Cube 1 as my data source, I don't know how to set variables for week for reference to Cube 2 to get the number of days correctly to calculate the right amount and put into Cube 3.

To refer to a cube, wouldn't it be CellGetN rather than CellPutN ?

[declanr]

You need to do some form of loop on the data tab where you loop through the weeks dimension.

The most basic form of this would be to simply loop the entire dimension e.g.

Code: Select all

iCount = 1;
iMax = DimSiz ( cDimWeek );
While ( iCount <= iMax );
	sWeek = DimNm ( cDimWeek, iCount );
	If ( DType ( cDimWeek, sWeek ) @= 'N' & CellGetN ( Cube2, vsYear, vsMonth, sWeek, 'Number of Days' ) > 0 );
		nValue = vnValue * ( CellGetN ( 'Cube2', vsYear, vsMonth, sWeek, 'Number of Days' ) \ 7 );
		CellPutN ( nValue, 'Cube3', vsYear, vsMonth, vsWeek, 'Sales $' );
	EndIf;
	iCount = iCount + 1;
End;

That most basic form may be fast enough... assuming you are looping though 52/53 weeks its pretty much nothing.
But it is for every single record, so checking 47 redundant weeks for every record can be a time waster.

In that case you can get smarter and do the processing earlier.
On your prolog tab do loops just of cube 2 and create temp subsets containing the weeks that are needed.

Code: Select all

iYearCount = 1;
iYearMax = DimSiz ( cDimYear );
While ( iYearCount <= iYearMax );
	sYear = DimNm ( cDimYear, iYearCount );
	If ( DType ( cDimYear, sYear ) @= 'N' );
		iMonthCount = 1;
		iMonthMax = DimSiz ( cDimMonth );
		While ( iMonthCount <= iMonthMax );
			sMonth = DimNm ( cDimMonth, iMonthCount );
			If ( Dtype ( cDimMonth, sMonth ) @= 'N' );
				sSubsetName = sYear | '_' | sMonth;
				SubsetCreate ( cDimWeek, sSubsetName, 1 );
				iWeekCount = 1;
				iWeekMax = DimSiz ( cDimWeek );
				While ( iWeekCount <= iWeekMax );
					sWeek = DimNm ( cDimWeek, iWeekCount );
					If ( Dtype ( cDimWeek, sWeek ) @= 'N' & CellGetN ( Cube2, sYear, sMonth, sWeek, 'Number of Days' ) > 0 );
						SubsetElementInsert ( cDimWeek, sSubsetName, sWeek, 1 );
					EndIf;
					iWeekCount = iWeekCount + 1;
				End;
			EndIf;
			iMonthCount = iMonthCount + 1;
		End;
	EndIf;
	IyearCount = iYearCount + 1;
End;

Then on your data tab you can just use the below to find the weeks that belong to a month:

Code: Select all

iCount = 1;
iMax = SubsetGetSize ( cDimWeek, vsYear | '_' | vsMonth );
While ( iCount <= iMax );
	sWeek = SubsetGetElementName ( cDimWeek, vsYear | '_' | vsMonth, iCount );
	### do your stuff here
	iCount = iCount + 1;
End;

Or if you are happy to have some persistent subsets hanging around you can just create them once when you populate cube 2... assuming that the data in cube 2 doesn't have to change that often. Which it shouldn't.

[kenship]

Yeah I was trying to avoid doing a loop.

However, say I save a subset in Cube 2 which includes Year 2021, months 1-12, weeks 1-53 and the number of days, how would I include it in the TI and have it looked up correctly since I don't have the week as variable?

Kenneth

I'll give some thoughts to

You need to do some form of loop on the data tab where you loop through the weeks dimension.

The most basic form of this would be to simply loop the entire dimension e.g.

Code: Select all

iCount = 1;
iMax = DimSiz ( cDimWeek );
While ( iCount <= iMax );
	sWeek = DimNm ( cDimWeek, iCount );
	If ( DType ( cDimWeek, sWeek ) @= 'N' & CellGetN ( Cube2, vsYear, vsMonth, sWeek, 'Number of Days' ) > 0 );
		nValue = vnValue * ( CellGetN ( 'Cube2', vsYear, vsMonth, sWeek, 'Number of Days' ) \ 7 );
		CellPutN ( nValue, 'Cube3', vsYear, vsMonth, vsWeek, 'Sales $' );
	EndIf;
	iCount = iCount + 1;
End;

That most basic form may be fast enough... assuming you are looping though 52/53 weeks its pretty much nothing.
But it is for every single record, so checking 47 redundant weeks for every record can be a time waster.

In that case you can get smarter and do the processing earlier.
On your prolog tab do loops just of cube 2 and create temp subsets containing the weeks that are needed.

Code: Select all

iYearCount = 1;
iYearMax = DimSiz ( cDimYear );
While ( iYearCount <= iYearMax );
	sYear = DimNm ( cDimYear, iYearCount );
	If ( DType ( cDimYear, sYear ) @= 'N' );
		iMonthCount = 1;
		iMonthMax = DimSiz ( cDimMonth );
		While ( iMonthCount <= iMonthMax );
			sMonth = DimNm ( cDimMonth, iMonthCount );
			If ( Dtype ( cDimMonth, sMonth ) @= 'N' );
				sSubsetName = sYear | '_' | sMonth;
				SubsetCreate ( cDimWeek, sSubsetName, 1 );
				iWeekCount = 1;
				iWeekMax = DimSiz ( cDimWeek );
				While ( iWeekCount <= iWeekMax );
					sWeek = DimNm ( cDimWeek, iWeekCount );
					If ( Dtype ( cDimWeek, sWeek ) @= 'N' & CellGetN ( Cube2, sYear, sMonth, sWeek, 'Number of Days' ) > 0 );
						SubsetElementInsert ( cDimWeek, sSubsetName, sWeek, 1 );
					EndIf;
					iWeekCount = iWeekCount + 1;
				End;
			EndIf;
			iMonthCount = iMonthCount + 1;
		End;
	EndIf;
	IyearCount = iYearCount + 1;
End;

Then on your data tab you can just use the below to find the weeks that belong to a month:

Code: Select all

iCount = 1;
iMax = SubsetGetSize ( cDimWeek, vsYear | '_' | vsMonth );
While ( iCount <= iMax );
	sWeek = SubsetGetElementName ( cDimWeek, vsYear | '_' | vsMonth, iCount );
	### do your stuff here
	iCount = iCount + 1;
End;

Or if you are happy to have some persistent subsets hanging around you can just create them once when you populate cube 2... assuming that the data in cube 2 doesn't have to change that often. Which it shouldn't.

[Wim Gielis]

Declan showed you how to create and populate subsets. Each subset contains the year and the month in its name.
Wouldn't that be an option for referencing the subset and loop through its elements ?

[burnstripe]

You could create a dimension that could be used for a lookup. Why not have a dimension like this

2021 > Jan 2021 > Week 1 2021
Then when you're doing the push from cube 1 to 3 you can loop through only the weeks required aka children of Jan 2021 instead of the whole year. Also gives you the option to run the process by month then

Also if its structured like this you can move weeks around without amending other years rather then just calling it week 1, which may belong to Jan one year but maybe Apr in another. (particularly if changing financial years)

[declanr]

Yeah I was trying to avoid doing a loop.

Why do you want to avoid doing a loop?


I would say that for this sort of thing a loop be it on a dimension, subset, or "lookup dimension" (as per Burnstripe's comment) is *almost* essential - and very likely the most efficient way of doing it.

There are other options that wouldn't involve a loop but I wouldn't do them personally e.g.:
1/ You prepopulate Cube 3 with number of days in one TI process. You can have a second TI process that transfers values from Cube 1 with a CellPutProportionalSpread. You would need to decide how to exclude populating number of days where there would be no value to transfer in the end.
This would likely be very inefficient.

2/ Depending how big Cube 3 is, you can just have a Process that has Cube 3 as its datasource. Set to not skip zeroes. Then for each cell, it checks against Cube 1 and 2 to decide what value it should have.
This would be inefficient BUT if the cube is small enough it could still be fast.

[kenship]

Hi all,

I haven't started working on it yet but will do today or tomorrow. Thanks to all for the advice.

Kenneth