My cube does complex analysis across a hierarchy for specific benchmarks (current v prior period/prior corresponding period, current v budget etc etc).

To allow users more flexibility i've used the AXIS() command to allow them to use any hierarchy as the portfolio that is benchmarked.

But the 'price' component of my PVM analysis is giving me serious performance headaches.

I've isolated a the major performance issue to the following measure in my cube

CREATE MEMBER CURRENTCUBE.[LowestPrice (dynamic)] AS NULL;

SCOPE ([Measures].[LowestPrice (dynamic)]);
    THIS = SUM(DESCENDANTS(Axis(1).item(0).item(Axis(1).Item(0).Count-1).Hierarchy.CurrentMember,,LEAVES),[Measures].[Price (dynamic)]);
END SCOPE;

here's the output from mdx studio

Time              : 1 min 48 sec 452 ms
Calc covers       : 0
Cells calculated  : 1130379
Sonar subcubes    : 155087
NON EMPTYs        : 1
Autoexists        : 3
EXISTINGs         : 119154
SE queries        : 88081
Flat cache insert : 19014
Cache hits        : 764375
Cache misses      : 828
Cache inserts     : 0
Cache lookups     : 765203
Memory Usage KB   : 332224

for this query

                    SELECT Non Empty{Measures.[LowestPrice (dynamic)]} ON 0                    
                    ,Non Empty({[Dim Date Calculations].[Date Calculations].[Prior Period]}
                    ,{[Dim Metric].[Measure Name].[List Price]}
                    
                    ,{[Dim Date].[Fiscal Hierarchy].[Fiscal Year]
                    ,[Dim Date].[Fiscal Hierarchy].[Fiscal Semester]
                    ,[Dim Date].[Fiscal Hierarchy].[Fiscal Quarter]
                    ,[Dim Date].[Fiscal Hierarchy].[Fiscal Month]}

                    ,{[Dim Product].[Product Hierarchy]
                    ,[Dim Product].[Product Hierarchy].[English Product Category Name]
                    ,[Dim Product].[Product Hierarchy].[English Product Subcategory Name]
                    ,[Dim Product].[Product Hierarchy].[Model Name]
                    ,[Dim Product].[Product Hierarchy].[English Product Name]}

                    ) ON 1
                    FROM [GPA]
                    WHERE (
                          [Dim Date].[Fiscal Year].&[2008]
                    )

it's based off adventureworks and the fact has been preaggregated to the month grain so there's only 1960 records in the fact for 2008

As a test ive gone back and created like for like measures that are scoped and hardcoded to a the product hierarchy so that I know what the 'best case' performance will be.

The difference is performance is acceptable until i get to the measure above. The hardcoded version of lowestprice has the following mdx studio stats:

Time              : 2 sec 808 ms
Calc covers       : 0
Cells calculated  : 42435
Sonar subcubes    : 13958
NON EMPTYs        : 1
Autoexists        : 3
EXISTINGs         : 13875
SE queries        : 191
Flat cache insert : 43
Cache hits        : 15033
Cache misses      : 86
Cache inserts     : 0
Cache lookups     : 15119
Memory Usage KB   : 173180

2 sec vs nearly 2 min! I've tried tried switching out the hardcoded version of measures.price into the lowestprice(dynamic) no/marginal improvement

and I've also switched out dim product.product hierarchy.currentmember for the axis() equivalent from the 'hardcoded' lowestprice and it blew out to 1min 30. So i'm fairly sure its the combination od descendants and axis that's causing me problems.

Is there anything i can do to improve the performance of my measure while retaining the use of AXIS()?