Cell Calculation Performance

Faraz

unread,

Dec 21, 2005, 2:55:27 PM12/21/05

to

I am using the following MDX, the Mdx works fine when there are few
members but it is causing performance issues with the increase of
members. Please review this MDX and comment. Is there any other
efficient way to do this or any optimization possible in this MDX?

-------------------------------------------------------------

With Member [Measures].[testmetric] AS '[Measures].[Store Sales
Net]-[Measures].[Store Sales]' --metric defination

Cell Calculation MYCALCULATION_ON_testmetric For '( {
[Measures].[testmetric]} )' As
- cell calculation name

'CalculationPassValue(Measures.CurrentMember, -1 , Relative )', --on
what should apply

CONDITION =' IIF([Time].currentMember.Level.Name="Quarter", --to see
if the level is Quarter so that it only compares quarters

IIF( ([Time].CurrentMember.PrevMember,[Measures].[testmetric]) >= 0 ,
--if for checking positve or negitive value for measure

CalculationPassValue( [Measures].[testmetric], -1,Relative ) < (
([Time].CurrentMember.PrevMember,Measures.[testmetric]) * 0.975) ,
--actual calculation for positive values

CalculationPassValue( [Measures].[testmetric], -1,Relative ) < (
([Time].CurrentMember.PrevMember,Measures.[testmetric]) * 1.025)
--actual calculation for negitive values

), --end of if for Value

CalculationPassValue( [Measures].[testmetric], -1,Relative ) = (
([Time].CurrentMember.PrevMember,Measures.[testmetric]) + 0.975) --
to always return false since I dont want other than quarters to be
evaluated

)' , --end of if for checking if level is quarter

SOLVE_ORDER ='2000', FORE_COLOR ='11004', CALCULATION_PASS_NUMBER ='2',
CALCULATION_PASS_DEPTH ='1'

SELECT { CrossJoin({[Product].[Product
Family].[Drink],[Product].[Product Family].[Food],[Product].[Product
Family].[Non-Consumable]},

{Measures.[testmetric]})} ON COLUMNS ,
{{[Time].[Year].[1997].CHILDREN}} ON ROWS

FROM [Sales for incremental update] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

-------------------------------------------------------------------------------------------------------

Thanks in advance

Faraz

Deepak Puri

unread,

Dec 21, 2005, 7:20:04 PM12/21/05

to

Hi Faraz,

Based ont testing with Foodmart Sales cube, here is a simplified version
which doesn't use calculated cells (assuming that you're only trying to
control the FORE_COLOR of a calculated member, based on its time trend):

>>
With Member [Measures].[testmetric] AS

'[Measures].[Store Sales Net]-[Measures].[Store Sales]',

FORE_COLOR ='iif([Time].currentMember.Level is [Time].[Quarter] And

([Time].CurrentMember.PrevMember,[Measures].[testmetric])

- CalculationPassValue( [Measures].[testmetric], -1,Relative )
> Abs(([Time].CurrentMember.PrevMember,Measures.[testmetric])) * 0.025,
rgb(255,0,0), rgb(0,0,0))',
FORMAT_STRING = 'Currency'

SELECT
{[Measures].[Store Sales], [Measures].[Store Sales Net],
Measures.[testmetric]} ON COLUMNS ,

CrossJoin({[Product].[Product Family].[Drink],
[Product].[Product Family].[Food],
[Product].[Product Family].[Non-Consumable]},

{{[Time].[Year].[1997].CHILDREN}}) ON ROWS

FROM [Sales] CELL PROPERTIES

[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]
>>

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Faraz

unread,

Dec 23, 2005, 11:48:42 AM12/23/05

to

Thanks for the simplfied query

i have another question that if i want apply the fore_color property on
any other measure how can i do this in the same way ?

With Member [Measures].[Sales Count] AS '??????????????????????'

i dont want to change or define this measure with any other name.

what are the way to apply for_color property on measures ans metrics
with same/ similar query ?

Another question is that Why Abs() function is not listed in the MDX
function list ?

Agian thanks in advance ,

Faraz

Deepak Puri

unread,

Dec 23, 2005, 5:45:38 PM12/23/05

to

To control the FORE_COLOR of an intrinsic cube measure in AS 2000, I
think that a calculated cell is needed.

Abs() is a VBA function, which is listed separately:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2
k2/html/odc_xlsql.asp
>>
Visual Basic for Applications Functions

Microsoft® SQL Server™ 2000 Analysis Services supports many functions in
the Microsoft Visual Basic® for Applications Expression Services
library. This library is included with Analysis Services and
automatically registered. Functions not supported in this release are
marked by an asterisk in this table.
..

Faraz

unread,

Dec 24, 2005, 11:01:38 AM12/24/05

to

Now i am using the following query but the response time is too slow
and for bigger report response time is huge.
I think the slow response time is due to the ABS function.

For the the next query it is good enough for the huge reports too. but
the problem is that is not genaratiung accurate results for negative
values. So is there any way possible to improve the performance of the
first query ?

Thanks in advance again. :)
-----------------------------------------------------------------------------------------------------------
QUERY 1
-----------------------------------------------------------------------------------------------------------
With Cell Calculation My_Cell_ON_Store_Cost For '({ [Measures].[Store
Cost]} )' As

'CalculationPassValue(Measures.CurrentMember, -1 , Relative )',

CONDITION ='
IIF([Time].currentMember.Level IS [Time].[Month] ,
CalculationPassValue( [Measures].[Store Cost], -1,Relative ) >
( ([Time].CurrentMember.PrevMember,Measures.[Store Cost]) + (Abs(
([Time].CurrentMember.PrevMember,Measures.[Store Cost]) ) * 0.02) )
, CalculationPassValue( [Measures].[Store Cost], -1,Relative ) = (
CalculationPassValue( [Measures].[Store Cost], -1,Relative ) + 1 )
)' , SOLVE_ORDER ='2000', FORE_COLOR ='11004', CALCULATION_PASS_NUMBER

='2', CALCULATION_PASS_DEPTH ='1'
SELECT {

CrossJoin({[Customers].[Country].[Canada],[Customers].[Country].[Mexico],[Customers].[Country].[USA]},{Measures.[Store
Cost]})} ON COLUMNS ,
{{[Time].[Year].[1997].[Q1].CHILDREN,[Time].[Year].[1997].[Q2].CHILDREN,[Time].[Year].[1997].[Q3].CHILDREN,
[Time].[Year].[1997].[Q4].CHILDREN,[Time].[Year].[1998].[Q1].CHILDREN,[Time].[Year].[1998].[Q2].CHILDREN,
[Time].[Year].[1998].[Q3].CHILDREN,[Time].[Year].[1998].[Q4].CHILDREN}}

ON ROWS
FROM [Sales] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

-----------------------------------------------------------------------------------------------------------
QUERY 2
-----------------------------------------------------------------------------------------------------------

With Cell Calculation MY_CELL_ON_Store_Cost For '( { [Measures].[Store
Cost]} )' As 'CalculationPassValue(Measures.CurrentMember, -1 ,
Relative )', CONDITION ='
IIF([Time].currentMember.Level.Name="Month", CalculationPassValue(
[Measures].[Store Cost], -1,Relative ) >
( ([Time].CurrentMember.PrevMember,Measures.[Store Cost]) * 1.02) ,
CalculationPassValue( [Measures].[Store Cost], -1,Relative ) = (
([Time].CurrentMember.PrevMember,Measures.[Store Cost]) * 1.02) )' ,
SOLVE_ORDER ='2000', FORE_COLOR ='11008', CALCULATION_PASS_NUMBER ='2',
CALCULATION_PASS_DEPTH ='1'
SELECT {
CrossJoin({[Customers].[Country].[USA].[CA],[Customers].[Country].[USA].[OR],[Customers].[Country].[USA].[WA]},
{Measures.[Store Cost],Measures.[Store Sales]})} ON COLUMNS ,
{{[Time].[Year].[1997].[Q1].CHILDREN,[Time].[Year].[1997].[Q2].CHILDREN,[Time].[Year].[1997].[Q3].CHILDREN,
[Time].[Year].[1997].[Q4].CHILDREN,[Time].[Year].[1998].[Q1].CHILDREN,[Time].[Year].[1998].[Q2].CHILDREN,
[Time].[Year].[1998].[Q3].CHILDREN,[Time].[Year].[1998].[Q4].CHILDREN}}

ON ROWS
FROM [Sales] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

-------------------------------------------------------------------------------------------------------------------------------------------

Deepak Puri

unread,

Dec 27, 2005, 1:44:33 AM12/27/05

to

Try this version - it seems to execute quickly:

>>
With Cell Calculation MY_CELL_ON_Store_Cost
For '( { [Measures].[Store Cost]} )'
As 'CalculationPassValue(Measures.CurrentMember, -1 ,
Relative )', CONDITION ='

IIF([Time].currentMember.Level is [Time].[Month],
CalculationPassValue(([Time].PrevMember,[Measures].[Store Cost]),
-1,Relative )
- CalculationPassValue( [Measures].[Store Cost], -1,Relative )
> Abs(CalculationPassValue(([Time].PrevMember,Measures.[Store Cost]),
-1,Relative )) * 0.02 ,
false )' ,
SOLVE_ORDER ='2000', FORE_COLOR ='rgb(255,0,0)', CALCULATION_PASS_NUMBER

='2',
CALCULATION_PASS_DEPTH ='1'
SELECT {
CrossJoin({[Customers].[Country].[USA].[CA],[Customers].[Country].[USA].
[OR],[Customers].[Country].[USA].[WA]},
{Measures.[Store Cost],Measures.[Store Sales]})} ON COLUMNS ,

[Time].[Month].Members

ON ROWS
FROM [Sales] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]
>>

Faraz

unread,

Jan 13, 2006, 5:03:31 AM1/13/06

to

Is there any way to optimize this query? the problem is with
performance in lager no of cells. i am trying to compare the value with

last year month.
thanks in advance

Faraz
-------------------------------------------------------------------------------------------------------------

MDX Query
-------------------------------------------------------------------------------------------------------------

With Cell Calculation MY_CELL For
'( { [Measures].[Sales Count]} )' As
'CalculationPassValue(Measures.CurrentMember, -1 ,Relative )',
CONDITION =' (ParallelPeriod ([Time].[Month Of
Year],1,[Time].CurrentMember),Measures.[Sales Count]) <
(CalculationPassValue( [Measures].[Sales Count], -1,Relative ) +
(ParallelPeriod ([Time].[Month Of
Year],1,[Time].CurrentMember),Measures.[Sales Count]) * 0.05)'
, SOLVE_ORDER ='2000', FORE_COLOR ='11005',

CALCULATION_PASS_NUMBER ='2', CALCULATION_PASS_DEPTH ='1'
SELECT

{ CrossJoin({[Customers].[Country].[USA].CHILDREN},{Measures.[Sales
Count]})} ON COLUMNS ,
{{[Time].[Month Of Year].MEMBERS}} ON ROWS