Problems with parent-child rollup

Hi,

We are building a cube with 4 parent-child dimensions and 2 regular dimensions, but because of business requirements all of the parent-child dimensions doesn't aggregate directly in to a sum function and here begins our problem.

When we determine the way the cube will aggregate in the MDX script, the first 2 dimensions works fine, but after that the MDX script is not even considered.

Does anyone have an idea of what's the problem? Next is the latest version of the MDX script.

Thank you

Rafael Augusto

-

/*Sector Institucional*/
scope([P_Sector_Institucional].[Sectores].members);
this = iif( not(isempty(calculationpassvalue((tuple with all the parent-child dimensions),0))),
calculationpassvalue((tuple with all the parent-child dimensions),0),
sum ([P_Sector_Institucional].[Sectores].currentmember.children));
end scope;

/*País*/
scope([P_País].[Países].members);
this = calculationpassvalue((tuple with all the parent-child dimensions),0);
end scope;

/*Instrumento*/
scope([P_Instrumento].[P_Instrumento].members);
this =
iif ([P_Instrumento].[P_Instrumento].currentmember.children.count>0, /*if the member has children*/
iif (not(isempty(calculationpassvalue((tuple with all the parent-child dimensions),0))), /*if the member has a value*/
iif( ([P_Instrumento].[P_Instrumento].currentmember is [P_Instrumento].[P_Instrumento].&[A950] or [P_Instrumento].[P_Instrumento].currentmember is [P_Instrumento].[P_Instrumento].&[P960] or isempty(sum([P_Instrumento].[P_Instrumento].currentmember.children))),
calculationpassvalue((tuple with all the parent-child dimensions),0), /*ignores the children's values*/
iif( ([P_Instrumento].[P_Instrumento].currentmember is [P_Instrumento].[P_Instrumento].&[P750760]),
sum (except([P_Instrumento].[P_Instrumento].currentmember.children, StrToSet("{" + MemberToStr([P_Instrumento].[P_Instrumento].currentmember.datamember) + "," + MemberToStr([P_Instrumento].[P_Instrumento].&[P75076010]) + "}" ))),
sum (except([P_Instrumento].[P_Instrumento].currentmember.children, StrToSet("{" + MemberToStr([P_Instrumento].[P_Instrumento].currentmember.datamember) + "}" )))
)
),
iif( ([P_Instrumento].[P_Instrumento].currentmember is [P_Instrumento].[P_Instrumento].&[P750760]),
sum (except([P_Instrumento].[P_Instrumento].currentmember.children, StrToSet("{" + MemberToStr([P_Instrumento].[P_Instrumento].&[P75076010]) + "}" ))),
sum([P_Instrumento].[P_Instrumento].currentmember.children)
)
),
calculationpassvalue((tuple with all the parent-child dimensions),0)
);
end scope;

/*divisa*/
scope([P_Divisa].[Divisas].members(0));
this = iif ([P_Divisa].[Divisas].currentmember is [P_Divisa].[Divisas].members(0),
sum([P_Divisa].[Divisas].currentmember.children),
calculationpassvalue((tuple with all the parent-child dimensions),0));
end scope

Hi Rafael,

Could you explain how the custom rollups in the MDX script are supposed to work, with some data examples? That will help in understanding them and suggesting changes. Here are questions/issues, based on a cursory review:

What is the purpose of: (tuple with all the parent-child dimensions) - does the tuple contain current members?

If [P_Instrumento].[P_Instrumento] is the key attribute (not parent-child) hierarchy, then only the [All] member has children, which makes the intent of this condition unclear: iif ([P_Instrumento].[P_Instrumento].currentmember.children.count>0

Within scope([P_Divisa].[Divisas].members(0)), what is the purpose of this condition: iif ([P_Divisa].[Divisas].currentmember is [P_Divisa].[Divisas].members(0) ?

According to this blog entry, CalculationPassValue(.., 0, ABSOLUTE) is usually unnecessary - use Freeze:

|||

Hello Deepak,

The data sample i'll have it ready in the next post, but regarding the questions you posted here are the answers:

What is the purpose of: (tuple with all the parent-child dimensions) - does the tuple contain current members?

|||

Hi,

This scenario also applies to AS2000, because we need to develop this prototipe cube for some users that have to see it with tools that are not compatible with as2005. Having said that, if anyone could help us implementing this aggregation rules in as2000 too, it would be great.

Next is a data sample i've compiled. I've translated the dimension members for better comprehension. The problem is that for 2 of these dimensions(anyone of them) works fine, so the MDX expressions look like ok, but when we add the third and fourth dimension then the MDX script is no longer considered.

Dimension "P_Currency" - return the value of the cell, except when it's the allMember("All Currency") where should return the sum of it's children. So it's something like this:

|||

Hi Rafael,

Maybe I didn't completely understand the rollup rules you describe; but it seems like the results depend on the order of dimension rollup. For example, EUR requires the cell value, but Sector A requires the sum of children. So what happens at cells with (EUR, Sector A, ..) intersecting co-ordinates - are cell values directly read, or summed from lower sector levels? Do the dimensions have to be rolled up in a particular order?

sql