Optimization(Performance Issue) - Analysis Services

Learn Microsoft BI Forum Core Servers Analysis Servic... Optimization(Performance Issue)

4/14/2009 4:10 PM

	Qazafi
51 posts

Optimization(Performance Issue)

Hi All

I am facing lot of problem with my Cube performance. Actually I have some calculated members which are causing that issue.

For Example

I have YTD Actual Income which I need to calculate because I have time dimension based on months, Quarters and year. If I do not calculate Using periodsToDate function and store as measure then 1^st quarter total will be sum of July, August and September and 2^nd quarter total will be the sum of six month which is wrong. I am in deep trouble as I have similar YTD calculation for BUDGET and FORECAST which make it worst. Apart of that I need to calculate the VARIANCE between these values as well. I try to use usage based optimization but it did not help me either as taking lot of time to insert those values in the database when I need to display lot of data.

Can any one please help me its urgent

Many thanks in advance

Qazafi

4/19/2009 10:08 PM

	cutley
363 posts

Re: Optimization(Performance Issue)

Qazafi,

Usage Based Optimization won't help you here. Calculated members are not aggregated; they are calculated at query time so no aggregations are created for them. For aggregations to be useful, the measures must be physical values. In the case of YTD calculations that usually isn't possible.

What does you YTD look like? Something like this?

SUM(YTD([Date].[Calendar].CurrentMember), [Measures].[Internet Order Quantity])

Page 1 of 1

Learn Microsoft BI Forum

Core Servers

Analysis Servic...

Optimization(Performance Issue)