ClosingPeriod Function in Analysis services does not utilise partiton pruning
I have an Inventory cube with a time hierarchy where the days roll into weeks and weeks into months and months into years.
I have a base measure for inventory.
the cube is partitoned by week
When I select weeks 1-4 (which make up month 1) and select month 1 simulataneously and look at the trace in profiler, 4 partitons are accesed.
I then created a calculated measure using the closing period funcion and again ran a similiar query for the same time period. This time all partitons in the cube were referenced and the query was much slower.
I am using novembers CTP but have not seen any bugs reported for this
Upvotes: 0<=-=Jul 25 2008 11:18AM=-=>
Thank you a lot for reporting the problem.
First we suggest you take a look at the recommendations outlined in following whitepaper on best pracices on creation of partitions- http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx
In particular see the recommendation on setting partition slices.
The Analysis Services Team