Multiplication then sum : how optimize ?

Oct 15, 2014 at 7:23 AM
I create a calculated member like this : [Mesures].[Codes Mesures].[CA]
.
sum(
   (
         descendants([Organigramme].[Codes Organigramme].currentmember,,LEAVES)
         ,descendants([Projets-Récurrents].[Codes Projets-Récurrents].currentmember,,LEAVES)
         ,descendants([Axe Analytique].[Codes Axe Analytique].currentmember,,LEAVES)
         ,descendants([Sites].[Codes Sites].currentmember,,LEAVES)
         ,descendants([Temps].[Hiérarchie Temps].currentmember,,LEAVES)
         ,descendants([Versions].[Codes Versions].currentmember,,LEAVES)
         ,descendants([Effectifs].[Codes Effectifs].currentmember,,LEAVES)
         ,descendants([Investissements].[Codes Investissements].currentmember,,LEAVES)
         ,descendants([Rubriques].[Codes Rubriques].currentmember,,LEAVES)
         ,descendants([Tiers].[Codes Tiers].currentmember,,LEAVES)
         ,[Measures].[Charge]
   ),
   [Mesures].[Codes Mesures].&[Tarif] * [Mesures].[Codes Mesures].&[Quantité]

I want to mlultiply a quantity with a price at low level and then do the agregation with sum on the hierarchy.
The formula seems OK but because of volumetry, I have to kill the excution.
I try a similar formula on a small basis and all is ok.

Do you know if I can optimize it ?
Thanks of lot for your help
Coordinator
Oct 20, 2014 at 4:04 PM
Is that calculation truly calculating at the lowest grain? If so, I would highly recommend you talk to your SSAS developer and get them to add a new measure. Do the multiplication in T-SQL (in a view or in the DMV) before SSAS sees it. That's really the only approach that will perform like you want.
Marked as answer by furmangg on 10/20/2014 at 8:04 AM
Oct 20, 2014 at 4:15 PM
OK, thank for the advice.
Calculations are today already achieved with the SQL Server database. But processing times are also quite long (10 min). We were looking for a workaround for a calculation on the fly.