Method To Archive cube data

[olap_knk]

Hi Team,
There is a requirement to archive cube data - older than 5 years. Presently X.cub file size is 3.0 GB. Seeking for a better advise to achieve this.

The cube X has a dimension 'Week', which has elements like week1, week2, week3 ...week52. and elements rolls up to 'All weeks'.
My requirement is to take the sum of all the elements data and push it against the last week (Say week52) in the cube and zeroout the rest of the week.

I have done it by the following steps :-

1. Created 2 views inside Prolog tab - i. Main view , ii. ZeroOut View (Excluding last week)
2. Typed inside Data tab
i. Condition to process only records having last week.
ii. Value=CellGetN('X', ...,....,....,...'All Weeks');
iii. CellPutN(Value,'X',...,....,.....,.....,'Week52');
3. Inside Epilog tab
Viewzerout function.

Ran the process :-
In first execution,view skip zeroes option was turned on (By default). I found It worked partially. It had worked only for those records which have data (NOT ZERO) for the last week. Those departments which have zero figures for last week was not overwritten with the all weeks data.
Afterwards I used ViewExtractSkipZeroesSet function to turn off skip zeroes option and executed the process. This time I got success.

But the problem over there is it took around 2 hrs. to complete the process where as in first time process was completed in 6-8 Mins.

Do you guys have any better solution to achieve this requirement expeditiously ?

[lotsaram]

There are a few things you can do to make your archiving process better and more robust. I'm not sure where you're at as a Tm1 developer so hopefully you'll be able to follow at least some of what follows.

Here are some cube design considerations:
1. Consider having an archive cube which is load on demand to retain the full history by week
2. Rather than loading to week 52 or week 1 you would be better served by having a "Year N" or "Full Year Dummy" element to hold the full year value. This will make your process to load values to the year and zero out the weeks once done much more straightforward and avoid potential pitfalls of circular references
3. An alternate design to having the archive all in one cube is to have a "historical values" cube that has no week dimension, only year. Load the values you want to archive to this cube and then for your archived years have a rule that pulls values from the historical cube to week 52 only (and of course feed from !year in the historical cube to !year, 'wk 52')

Here are some data processing considerations:
1. ALWAYS process from a zero suppressed view
2. It is much more efficient to process from a leaf level view only (i.e. skip calcs) and ACCUMULATE within the TI code. In your case don't process from the "Full Year" consolidation in your week dimension, it is much more efficient to process from N level weeks 1 - 52 and do a oldValue = CellGetN on the data tab for the current value of the "Full Year Dummy" element then CellPutN(oldValue + Value

Hopefully that's enough to point you in the right direction.

[olap_knk]

Yup, got your point.

Thanks a lot.