TI process using more detailed cube as source

[kbogies]

I'd like some help on getting more detailed data from Weekly cube into the Monthly cube. The two cubes are almost identical except for the Month/Week time dimensions (the data is different based on the version dimension of Actual - Monthly, Plan - Monthly, Actual - Weekly, etc). I load the Monthly Actual and Plan data into the "Monthly" cube and Actual - Weekly data into the "Weekly" cube. The Weekly cube contains the Weeks time dimension, with contains consolidations of the possible dates (i.e. 1/1, 1/2, ....1/31 consolidate to January). The year is a seperate dimension in both cubes.

I'm trying to write a TI process to load the Weekly cube into the Monthly cube where the Weekly C:'months' = Monthly N:'months'. I've tried several options, but i'm limited to building a view for a source because several of the dimension constantly need to be recreated (based on people/turnover of job functions), so i think the static-ness of building subsets of these dimensions to only contain 0 level elements will not work well to always capture all level 0 elements. On the other hand, i'd like to build a cube view where all the parameters are skipped for consolidations, etc... with the exception of the Week dimension to select level 1 elements. Would anyone know a syntax to skip all parameters, with the exception of one dimension subset?

[paulsimon]

Hi

Like you I once assumed that, if you have a consolidation in your more detailed cube which matches the base level in your summary cube, then you should read from that consolidation. However, experience has shown that reading from consolidated views is slow. You will get far better performance by reading from a base level view in your weekly cube, which, from the sound of it, actually has daily data, and then loading your monthly cube, on an additive basis, eg

Source View has Skip Consolidations, Calculations, and Zeros

# Date is in the form YYYY-MM-DD

vMonth = subst(vDate,1,7) ;

CellPutN(
vValueFromWeekly
+
CellGetN(
vCube,
Dim1,
...
vMonth,
vMeas
),
vCube,
Dim1,
...
vMonth,
vMeas
);

Whenever you use an additive load, be sure to clear the actuals in your monthly cube first, otherwise you will get double counting (assuming that you get your monthly actuals from your weekly cube). You can use ViewZeroOut in the prolog to do this.

(By the way the answer to creating a base level subset without needing to maintain it is to record an MDX expression of yourself selecting All elements then level 0 on the dimension - That might be useful to know in other cases, but for this case it is not the best approach)

Regards

Paul Simon

[kbogies]

just to follow up... it turns out the two versions of actuals (month vs weekly) are unrelated , so they will remain two seperate cube. your advice was helpful, and i am also now using the mdx to create a subset in another way.

thanks again,
Kbogies