Hi all,

We use AS2K to create forecasting models for real estate, we create
thes models in a seperate app that translates it to cubes and MDX.
Typically a cube will have some 800 calculated members, that all refer
to each other in a sort of tree structure. At the end of this post a'll
include some examples of typical calculations.

Currently we're trying to migrate this to SSAS 2005. We upgraded a cube
to 2005 using the upgrade wizard, this went reasonably well, but the
performance is very dissapointing.
A query at the top of the tree that took 20 secs in 2K now takes 5
minues during which memory usage in the server peaks to 800MB.

I tried stripping the dimension of any attributes that wouldn't be
used, redesinging aggregates, setting attributes to not optimized and
ome more things, but no improvement.

Would anybody (Mosha, Chris....) be able to give us some pointers as to
where to look for improvements?

TIA,

John

Some sample calcs:

CREATE MEMBER CURRENTCUBE.[Measures].[Overerving] AS '
Iif(Not [Scen Id].Currentmember Is [Scen Id].&[0] AND (ScenHasValue,
[All Tijd], [All PP]) = 0, (Overerving, [Scen Id].&[0] ), --Return base
case
Iif(Ancestor(pp.currentmember, [ValueGroup]) Is [PP].[Calcs], --Is Calc
Of PP?
Iif(IsEmpty(([Extra Value])), 0, [Extra Value]), -- Is calc -
altijd 0, nooit leeg icm cdbl functie
Iif(IsEmpty((Ancestor(tijd.currentmember, tijd.yr),
[OverervingStapExtrapolatie])),
Null,
(Ancestor(tijd.currentmember, tijd.yr),
[OverervingStapExtrapolatie])) -- Is parm (neem altijd YR level)
)
)
--)', SOLVE_ORDER= 0,FORMAT_STRING='#,#' ;




CREATE MEMBER
CURRENTCUBE.[PP].[Calcs400].[CO_ruimte_CF_markthuur_start] AS
'Cdbl(iif(Object.CurrentMember.Level Is
Object.Levels(7),Iif(Object.CurrentMember.Properti es("ObjectType") =
"CO_ruimte",Iif(Object.CurrentMember.Properties("O bjectLevel") =
"8",iif(Tijd.CurrentMember Is
Tijd.Members.Item(Val(Object.Currentmember.Propert ies("ObjectStartDateOrdinal"))
- 1) ,0 + Iif(( [PP].&[-862563275],[Overerving],
Ancestor(Tijd.CurrentMember, Tijd.Yr) ) <> 0, (
[PP].&[-862563275],[Overerving], Ancestor(Tijd.CurrentMember, Tijd.Yr)
), Iif(( [PP].[CO_ruimte_ML_ruimtetype], Tijd.[Month].Members.item(12)
) = 10, ( [PP].&[-1490286326],[Overerving],
Ancestor(Tijd.CurrentMember, Tijd.Yr) ), Iif((
[PP].[CO_ruimte_ML_ruimtetype], Tijd.[Month].Members.item(12) ) = 20,
( [PP].&[-915633345],[Overerving], Ancestor(Tijd.CurrentMember,
Tijd.Yr) ), Iif(( [PP].[CO_ruimte_ML_ruimtetype],
Tijd.[Month].Members.item(12) ) = 30, (
[PP].&[-206710992],[Overerving], Ancestor(Tijd.CurrentMember, Tijd.Yr)
), Iif(( [PP].[CO_ruimte_ML_ruimtetype], Tijd.[Month].Members.item(12)
) = 40, ( [PP].&[-806288857],[Overerving],
Ancestor(Tijd.CurrentMember, Tijd.Yr) ), Iif((
[PP].[CO_ruimte_ML_ruimtetype], Tijd.[Month].Members.item(12) ) = 50,
( [PP].&[-936206299],[Overerving], Ancestor(Tijd.CurrentMember,
Tijd.Yr) ), Iif(( [PP].[CO_ruimte_ML_ruimtetype],
Tijd.[Month].Members.item(12) ) = 60, (
[PP].&[-821197394],[Overerving], Ancestor(Tijd.CurrentMember, Tijd.Yr)
), Iif(( [PP].[CO_ruimte_ML_ruimtetype], Tijd.[Month].Members.item(12)
) = 70, ( [PP].&[1483024463],[Overerving],
Ancestor(Tijd.CurrentMember, Tijd.Yr) ), Iif((
[PP].[CO_ruimte_ML_ruimtetype], Tijd.[Month].Members.item(12) ) = 80,
( [PP].&[1488362777],[Overerving], Ancestor(Tijd.CurrentMember,
Tijd.Yr) ), Iif(( [PP].[CO_ruimte_ML_ruimtetype],
Tijd.[Month].Members.item(12) ) = 90, (
[PP].&[-197962739],[Overerving], Ancestor(Tijd.CurrentMember, Tijd.Yr)
), 0) ) ) ) ) ) ) ) ) ) *( [PP].[CO_ruimte_ML_oppervlakte_vvo_calc],
Tijd.[Month].Members.item(12) )/12, 0 +
(Tijd.Members.Item(Val(Object.Currentmember.Proper ties("ObjectStartDateOrdinal"))
- 1), [PP].[CO_ruimte_CF_markthuur_start]
)),0),0),iif(Object.CurrentMember.Level.Ordinal < 7, 0 +
SUM(Filter(Descendants(Object.CurrentMember, Object.levels(7)),
Object.CurrentMember.Properties("ObjectLevel") = "8" AND
Object.CurrentMember.Properties("ObjectType") = "CO_ruimte"),
([PP].[CO_ruimte_CF_markthuur_start]) ) , 0 +
(Ancestor(Object.CurrentMember, Object.levels(7)),
[PP].[CO_ruimte_CF_markthuur_start] ))))', SOLVE_ORDER= 13 ,
FORMAT_STRING='#,#0' ;


CREATE MEMBER
CURRENTCUBE.[PP].[Calcs700].[CO_contract_LT_dagen_huur_primo] AS
'Cdbl(iif(Object.CurrentMember.Level Is
Object.Levels(6),Iif(Object.CurrentMember.Properti es("ObjectType") =
"CO_contract",Iif(Object.CurrentMember.Properties( "ObjectLevel") =
"7",iif(Tijd.CurrentMember.Level Is Tijd.Levels(2),0 + Iif((
[PP].[CO_contract_IC_actief]), Iif((
[PP].[CO_contract_IC_mut_leeg_verhuur]), Iif((
[PP].[CO_contract_LT_expiratiedag], Tijd.[Month].Members.item(12) ) =
30, 30, 0) , Iif(( [PP].[CO_contract_IC_mut_verhuur_leeg]), Iif((
[PP].[CO_contract_LT_expiratiedag], Tijd.[Month].Members.item(12) ) =
30, 0, ( [PP].[CO_contract_LT_expiratiedag],
Tijd.[Month].Members.item(12) )-((
[PP].[CO_contract_LT_eerste_dag])-1)) , Iif((
[PP].[CO_contract_IC_mut_verhuur_verhuur]), (
[PP].[CO_contract_LT_expiratiedag], Tijd.[Month].Members.item(12) )-((
[PP].[CO_contract_LT_eerste_dag])-1), Iif((
[PP].[CO_contract_IC_huurstatus]) = 1, 30-((
[PP].[CO_contract_LT_eerste_dag])-1), 0) ) ) ) , 0) , 0 +
SUM(Descendants(Tijd.CurrentMember, Tijd.levels(2)),
([PP].[CO_contract_LT_dagen_huur_primo])
)),0),0),iif(Object.CurrentMember.Level.Ordinal < 6, 0 +
SUM(Filter(Descendants(Object.CurrentMember, Object.levels(6)),
Object.CurrentMember.Properties("ObjectLevel") = "7" AND
Object.CurrentMember.Properties("ObjectType") = "CO_contract"),
([PP].[CO_contract_LT_dagen_huur_primo]) ) , 0 +
(Ancestor(Object.CurrentMember, Object.levels(6)),
[PP].[CO_contract_LT_dagen_huur_primo] ))))', SOLVE_ORDER= 19 ;

Are you using ragged hierachies and hidememberif property ?
If it is, you need to ask hotfix for solving that issue.
http://support.microsoft.com/?id=918735

In addition, I also have same issue with custom rollup definition in
dimension.
If you using it, please try to remove it and check it.

I already escalated it to MS and it will be better in SP2 but it still needs
to improve.

"John" wrote:

Hi,

John wrote:
according to our experiences SSAS 2005 cubes recreated manually
perform _much_ better than cubes migrated by the upgrade wizard.


regards,
Gerald