Moving data @ Changing Levels

[CiskoWalt]

Hello,

I have two cubes that are similar to the cubes described in the TM1 Rules guide,chaper 6 "Moving Data and Changing Levels". I am moving data 'Buys' and 'IFC Revenue Share' from the Source cube to the Target cube. The Souce cube has data at a Daily level and the target cube has data at a Monthly level. The Source cube also has 2 more dimensions than the Target cube.

The thought is that data will be summarized by month in the VodDay dimension of the Source cube (via a parent-level consolidation (Month 'owns' Week and Week 'owns' days)) and the summarized data can be pulled into the VodMonthlyAnalysis cube, which has a date dimension named VodMonth and has month as a leaf-level elements.

Source cube: VodDailySnapshots

Has data at a daily level. The date dimenson is illustrated in the attached file named 'Source_VodDay'

Has the following dimensions:
VodDay, VodEntity, VodMso, VodSystem, VodTitles, Vodformat, VodWindow, VodBalTYpe, VodDailyFact

Has the following feeders in the Source cube that feeds the cells in the Target cube:

['All System','All Window','Rentrak','Buys'] =>DB('VodMonthlyAnalysis',!VodDay,!VodEntity,!VodMSO,!VodTitles,!VodFormat,!VodBalType,'IFC Revenue Share');

['All System','All Window','Rentrak','Buys'] =>DB('VodMonthlyAnalysis',!VodDay,!VodEntity,!VodMSO,!VodTitles,!VodFormat,!VodBalType,'Buys');

On the left hand Side:

'All System' is a parent level element of the VodSystem dim. Note: VodSystem does not exist in Target cube.
'All Window' is a parent level element of the VodWindow dim. Note: VodWindow does not exist in Target cube.
'Rentrak' is one leaf-level element of the VodBalType dim.;
'Buys' is one leaf-level element in the VodDailyFact dim.

So if we are working with data from JAN-2011 if any of the days under the Jan-2011, parent element, of the Source cube's VodDay dimension has a value, then we will feed the cells in the Target cube that also have a leaf-level element in the VodMonth dim of Jan-2011.

Source VodDay dim_____________________________________Target: VodMonth dim
All Years__________________________________________________All Years
-2011____________________________________________________-2011
--2011-Q1________________________________________________2011-Q1
---Jan-2011 __________________________________> Jan-2011
W/E Jan-07-2011
1/1/2011
1/2/2011
1/3/2011

* This feeder does not work. When I change the !VodDay to a string 'Jan-2011' , the feeder works.
However, when we change the !VodDay to a string, the consolidations in the VodMonth dimension ('Jan-2011 YTD,
Feb-2011 YTD..) of the Target cube do not work.

I think that this should work, since:

1) The VodMonth dimension shares common element names with the VodDay dimension; VodDay has 'Jan-2011' as a consolidated (parent) element and VodMonth has 'Jan-2011' as a leaf-level element.

2) Both cubes Source and Target have the date dimension as the first dimension listed.

In short, the DB function looks for elements in the VodDay dimension that match element names in the VodMonth dimension.


Target cube:VodMonthlyAnalysis

Has the following dimensions:

VodMonth, VodEntity, VodMso, VodTitles, Vodformat, VodBalTYpe, VodMonthFact

Has the following rules that pull data from the Source cube:

SKIPCHECK;

# Pull Buys and 'IFC Revenue Share' from the VodDailysnapshots cube

['Rentrak','Buys'] = DB('VodDailySnapshots',!VodMonth,!VodEntity,!VodMSO,'All System',!VodTitles,!VodFormat,'All Window',!VodBalType,'Buys');

['Rentrak','IFC Revenue Share'] = DB('VodDailySnapshots',!VodMonth,!VodEntity,!VodMSO,'All System',!VodTitles,!VodFormat,'All Window',!VodBalType,'IFC Revenue Share');

#Calculte Rentrak Variance to Accrued
['Rentrak Variance to Accrued'] = ['Rentrak']-['Accrued'];

FEEDERS;

['Accrued'] =>['Rentrak Variance to Accrued'];
['Rentrak'] =>['Rentrak Variance to Accrued'];


----------------------------------------------------------------------------------------------------------------------------------------------

Appreciate any help with this.

Could this be related to the fact that the date elemetns contain a special character '-'?


We have two cubes since we need the Month as an n-level element in the MonthlyAnalysis cube.

[Andy Key]

Feeders only work at the N level. Feeding from a C level is simply shorthand for feeder statements for each of the lowest level descendants.

So the Jan-2011 in your source dim itself does not feed anything, only its lowest level descendants will, i.e. 1/1/2011, 1/2/2011, 1/3/2011 etc will try to feed an element of the same name in the target dimension. You either need your feeder to feed to the ElPar(ElPar()) of your N level element, or (much more) preferably create an attribute on your source dimension that holds the element in the target dimension and feed to that.

[CiskoWalt]

Andy,

Thanks for your help.

I went with your "Preferred" method and it worked.

Unfortunately, the 'YTD Consolidataions' and the 'All Years' parent in the Target cube's dimension 'VodMonth' do not aggregate the data pulled from the Source cube

The VodMonth dimension contains 2 hierarchys. The first hierarch works fine. Meaning all parent level consolidations work.

First hierarchy

All Years
2010 ------> this works
2001-Q1 ----> This works
Jan-2010 ---data stored at this level

Second hierarchy
YTD Consolidations

Jan-2011 YTD ----> Does not work
Jan-2011
Feb-2011 YTD -----does not work
Jan-2011 YTD
Feb-2011

See attached file Target_VodMonth_YTD_Consolidations

I would think that these consolidations would work, since the cells in the Target cube are fed.

Appreciate your help.

Walt

[jstrygner]

SKIPCHECK;

# Pull Buys and 'IFC Revenue Share' from the VodDailysnapshots cube

['Rentrak','Buys'] = DB('VodDailySnapshots',!VodMonth,!VodEntity,!VodMSO,'All System',!VodTitles,!VodFormat,'All Window',!VodBalType,'Buys');

['Rentrak','IFC Revenue Share'] = DB('VodDailySnapshots',!VodMonth,!VodEntity,!VodMSO,'All System',!VodTitles,!VodFormat,'All Window',!VodBalType,'IFC Revenue Share');

#Calculte Rentrak Variance to Accrued
['Rentrak Variance to Accrued'] = ['Rentrak']-['Accrued'];

Walt,

Above rules take values both for N and C level cells, so even if you feed properly, but in the source cube on C level you have zeroes, you will see zeroes on target cube.

Try switching to:
['Rentrak','Buys'] = N: DB('VodDailySnapshots',!VodMonth,!VodEntity,!VodMSO,'All System',!VodTitles,!VodFormat,'All Window',!VodBalType,'Buys');

Also I would consider changing this:
['Rentrak Variance to Accrued'] = ['Rentrak']-['Accrued'];
to a 'Rentrak Variance to Accrued' consolidation where 'Rentrak' has a weight of 1 and 'Accrued' weight of -1.

HTH

[CiskoWalt]

Thanks HTH.

The consolidations in the Target cube now work and I will change the rule to a consolidation.

Walt

[qml]

Thanks HTH.

This seems to be a recurring theme.

[lotsaram]

ROTFLMAO