Measure expressions and currency conversion

OK - here is my scenario... I have a fact table containing one measure: Sales. The values of the measure are stored in the same currency (DKK) for all records. In another fact table I have exchange rates for four different currencies per day for a 15 year period (approx. 22000 records). I have built a cube around these fact tables containing two measure groups.

Measure Group 1
Measure: Sales
Dimensions: Time, Product, Company

Measure Group 2
Measure: Exchange rate
Dimensions: Time, Currency

The only shared dimension between the two measure group is thus Time... The currency dimension contains one attribute hierarchy (CurrencyCode) with IsAggregatable set to False and DefaultMember set to DKK.

For the measure Sales, I have created a measure expression like this:

[Measures].[Sales] / [Measures].[Currency]

I thought this should work - i.e. give me the opportunity to select any of the four available currencies and have the value of Sales displayed accordingly. However - it does not. Instead my Sales measures is multiplied by 4, which corresponds to the number of currencies for which I have exchange rates in my exchange rate fact table. Why is this? Am I not modelling the scenario correctly?

I hope someone can help, since this really puzzles me!

Hi Michael,

I think I might know what's going on here: you need to give your Currency dimension a many-to-many relationship with Measure Group 1 using Measure Group 2 as the intermediate measure group. Does this work?

Regards,

Chris|||Hi Chris,

Sound very reasonable... I will give it a try. Do you think this approach will perform better than an MDX script like this (inspired by one of your own blog entries: http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry)

([Measures].[Sales],LEAVES([Time]),LEAVES([Currency])) = [Measures].[Sales] / [Measures].[Exchange Rate]

?

Chris, thanks for your input. It would be a shame to say that Microsoft has spent too much time documenting how to use measure expressions. |||Well, I think it might perform a little bit better, but to be honest I'm not sure. You'll have to try both!|||Michael,

Since you've marked my first reply as solving your problem, I don't suppose you can tell us what the performance on your cube is like now? Do your currency conversion queries run fast enough with measure expressions? I'd really be interested to know.

Chris|||Hi Chris

Unfortunately, I have not yet had the time to implement the solution with measure expressions yet (currently, we are using the MDX Script approach for solving the problem). I will, however, test it with measure expressions as well and report back my findings. I am sure that the solution you pointed out is correct wrt. using measure expressions (also after having reviewed the implementation in the AdventureWorks cube), which is why I marked your answer as correct.

I really appreciate your input!|||

Mosha's latest blog entry is also highly relevant to this problem:
http://sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx

|||OMG... Really a very nice article. It guess it also means that if we have to divide two measures with each other in a measure expression like this: A / B, and B has the higher granularity, then it would be a good idea to invert the B measure (1/B) and instead make a measure expression like this: B * A...

Lesson learned... I guess.

Still has not tested the measure expression vs. the MDX Script approach, though...|||Hi,

I finally got around to test this... My results show that measure expressions performs marginally better compared to using MDX scripts. But the two approaches perform almost just as well (and they do perform remarkably good) - a statement Mosha has previously made as well. So which approach do I prefer? Well - since using measure expressions has the best peformance (not by much, though), I will be using these. Perhaps there are scenarios where you cannot model your UDM to fit this approach, and MDX scripts might be more appropriate then...

I hope that these findings are useful to others...|||Well... What do you know?! I just found a scenario myself in which using measure expressions for currency conversion is not possible. One of my measures used "LastNonEmpty" as the aggregation function and measure expressions do not support this... Will of course be using the MDX script approach for solving this...

Measure expressions and currency conversion

OK - here is my scenario... I have a fact table containing one measure: Sales. The values of the measure are stored in the same currency (DKK) for all records. In another fact table I have exchange rates for four different currencies per day for a 15 year period (approx. 22000 records). I have built a cube around these fact tables containing two measure groups.

Measure Group 1
Measure: Sales
Dimensions: Time, Product, Company

Measure Group 2
Measure: Exchange rate
Dimensions: Time, Currency

The only shared dimension between the two measure group is thus Time... The currency dimension contains one attribute hierarchy (CurrencyCode) with IsAggregatable set to False and DefaultMember set to DKK.

For the measure Sales, I have created a measure expression like this:

[Measures].[Sales] / [Measures].[Currency]

I thought this should work - i.e. give me the opportunity to select any of the four available currencies and have the value of Sales displayed accordingly. However - it does not. Instead my Sales measures is multiplied by 4, which corresponds to the number of currencies for which I have exchange rates in my exchange rate fact table. Why is this? Am I not modelling the scenario correctly?

I hope someone can help, since this really puzzles me!

Hi Michael,

I think I might know what's going on here: you need to give your Currency dimension a many-to-many relationship with Measure Group 1 using Measure Group 2 as the intermediate measure group. Does this work?

Regards,

Chris|||Hi Chris,

Sound very reasonable... I will give it a try. Do you think this approach will perform better than an MDX script like this (inspired by one of your own blog entries: http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry)

([Measures].[Sales],LEAVES([Time]),LEAVES([Currency])) = [Measures].[Sales] / [Measures].[Exchange Rate]

?

Chris, thanks for your input. It would be a shame to say that Microsoft has spent too much time documenting how to use measure expressions. |||Well, I think it might perform a little bit better, but to be honest I'm not sure. You'll have to try both!|||Michael,

Since you've marked my first reply as solving your problem, I don't suppose you can tell us what the performance on your cube is like now? Do your currency conversion queries run fast enough with measure expressions? I'd really be interested to know.

Chris|||Hi Chris

Unfortunately, I have not yet had the time to implement the solution with measure expressions yet (currently, we are using the MDX Script approach for solving the problem). I will, however, test it with measure expressions as well and report back my findings. I am sure that the solution you pointed out is correct wrt. using measure expressions (also after having reviewed the implementation in the AdventureWorks cube), which is why I marked your answer as correct.

I really appreciate your input!|||

Mosha's latest blog entry is also highly relevant to this problem:
http://sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx

|||OMG... Really a very nice article. It guess it also means that if we have to divide two measures with each other in a measure expression like this: A / B, and B has the higher granularity, then it would be a good idea to invert the B measure (1/B) and instead make a measure expression like this: B * A...

Lesson learned... I guess.

Still has not tested the measure expression vs. the MDX Script approach, though...|||Hi,

I finally got around to test this... My results show that measure expressions performs marginally better compared to using MDX scripts. But the two approaches perform almost just as well (and they do perform remarkably good) - a statement Mosha has previously made as well. So which approach do I prefer? Well - since using measure expressions has the best peformance (not by much, though), I will be using these. Perhaps there are scenarios where you cannot model your UDM to fit this approach, and MDX scripts might be more appropriate then...

I hope that these findings are useful to others...|||Well... What do you know?! I just found a scenario myself in which using measure expressions for currency conversion is not possible. One of my measures used "LastNonEmpty" as the aggregation function and measure expressions do not support this... Will of course be using the MDX script approach for solving this...

measure counting distinct values from a field

How do I count the amount of distinct values from a column in a fact table as a calculated measure

Assume the primary key of my fact table is a composite of three columns (a,b,c).

How can I code a calculated measure to count every distinct value of column a, not the amount of rows in my fact table.

Any advise help would be appreciated!

All you need to do is change the measure type from Count to Distinct Count ... make sure you select Column A as your key column....

MDX: combining SUM and Existing

Hi,
I have problem summing values in leaf level and total level.
Lets say, I would like to to get orders in dealer price for each selected
product as well as sum for selected products only. As example, this MDX
could demonstrate my case in Adventure Works database:

with member [Dealer Price Orders] as

SUM(
Existing ([Product].[Product Categories].[Product]),
iif([Product].[Dealer Price].MemberValue = 0 , null,
[Order Quantity]*[Product].[Dealer Price].MemberValue)
)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product
Categories].[All Products]})})
ON COLUMNS
FROM (SELECT ({[Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure
Works])
WHERE ([Measures].[Dealer Price Orders])

Unfortunately, this MDX returns Dealer Price Sales for all Products, but not
for Selected [Accessories] and [Bikes]. VisualTotals function does not help
in this case. Does anyone has a solution how to rework my calculated member?

Ramunas Balukonis

on 2005 sp2, I get a blank resultset from your test query - so a lot of people probably can't use it to give you an answer.

are you on something like sp1 or ssas 2000?

|||

Unfortunately the Existing statement currently doesn't take into account subselect restrictions.

One possibility is if you can define your calculation on leaf cells, then subselect visualtotals can be achieved. Here is one example, note that I changed [Product] to [Product Name] which matches my version of Adventure Works.

with cell calculation X for '([Product].[Product Categories].[Product Name].members, [Order Quantity])' as

iif([Product].[Dealer Price].MemberValue = 0 , null,

[Order Quantity]*[Product].[Dealer Price].MemberValue)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})})

ON COLUMNS

FROM (SELECT ({[Product].[Product Categories].[Category].&[4],

[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure Works])

WHERE ([Measures].[Order Quantity])

|||

Here is another way which uses calculated member instead of cell calculation. It uses the fact that query scope named sets are resolved using subselect restrictions.

with set S as [Product].[Product Categories].[Product Name].members

member [Dealer Price Orders] as

SUM(

existing(S),

iif([Product].[Dealer Price].MemberValue = 0 , null,

[Order Quantity]*[Product].[Dealer Price].MemberValue)

)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})})

ON COLUMNS

FROM (SELECT ({[Product].[Product Categories].[Category].&[4],

[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure Works])

WHERE ([Measures].[Dealer Price Orders])

mdx script context execution

Hi everybody.

I have a mdx script on a cube involving several cells through subcube. The subcube takes the values of some cells and use them to calculate the values of some other cells.

I have a user who has limited access rights on the dimensions, so that he can't see all the cells of the cube.

But I need that all the users can perform all the calculations in the cube even if there are some members (used to evaluate the calculations) they are not allowed to.

I know that the calculations script are evalueted in the context of the user querying the cube.

I wonder if there is a command to make the script being evaluated in the context of a specified user, an administrator in this case (something like "execute as"). So that the calculations are applied first, and after that, the user with reduced rights can see only the cells he is allowed to...

thank you very much.

Sorry, but there is no 'Execute As' feature and I can't think of anyway that you could reference a secured cell in a calculation.|||

Actually one possible solution to this would be to write a .Net stored procedure, that would would use AdoMd to connect back to the user with a new connection and execute an MDX query and return the results. When you deploy a stored proc assembly you have the option of specifying the account to impersonate.

Performance could be an issue with this approach as there is a bit of overhead involved in calling out to a stored procedure, but if your calc is at a fairly high level this might be a solution.

|||

Well, you give me a wisdom, thank you very much!

A more question, because I've never used a .Net stored procedure for Analysis Services. Do you mean that through it I can make the calculations in the script of the cube being executed by a specified user with all the necessary rights, and, after the calculations are realized on all the involved cells, then I can make the application to connect back as the current user to query the cube (therefore, seeing the previous results but only for the allowed cells)?

It'd be great.

have you any examples?

Thank you very very much!!!!

|||Check out this link for examples: http://www.codeplex.com/ASStoredProcedures|||

The AS Stored Procedure project has some good examples (I wrote some of them). However I don't think we have any that do exactly what you are after.

What would happen is that from a "limited" user, they would request a value.

That value would be calculated by a stored procedure. When the stored procedure is deployed it can be configured to run under a specific user account. In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query. This would return a cellset and the stored proc would extract the value of the required cell and return that value|||

Thank you very much again.

You say: “

v In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query.

v This would return a cellset and the stored proc would extract the value of the required cell and return that value

“

But I wonder if in this way the “poor” user (with limited rights) in Role1, issuing the query through the specified user for th AS SP, would be allowed to see also the cells he is forbidden to… In fact I need him to see only the cells he is allowed to, but with the values calculated with calculations involving all the cells (even the ones he is not allowed to). Is this possible this way?

However, I give you an example of what I need to do. Let’s say I need to do something like in the following script (a very simplified version of my script, but just to understand the type of calculation).

I have a dimension [DIM Mesi] with four key members [01], [02], [03], [00] and a one-level attribute hierarchy [Dim Mesi].

Role1 can access only the “[DIM Mesi].[DIM Mesi].&[01]”, “[DIM Mesi].[DIM Mesi].&[02]” and the ”existing” with these ones (I mean the ancestors of these members, which in this case, is the AllMember).

There is a basket member [DIM Mesi].[DIM Mesi].&[00] from where I take the values to be reversed on the three members [01], [02], [03] from the Measure [Costi1] into the same measure (or even in a different measure).

The calculations are something similar to the ones below.

I’d like the users of Role1 be allowed to see 1/3 of the values of cells of the &[00] member in the cells of &[01] and &[02], but they should not be able to see nor the &[00] member related cells neither the &[03] member related cells.

Further, the [DIM Mesi].[DIM Mesi].&[00] should be assigned a 0 value (even if the user can’t see that value) so that the total is not twice, in case I don’t use VisualTotals.

If possible, I’d like to even be able to decide whether he must see the Visual Totals or not, but I suppose that through the AS SP executed by another user I can only see the Actual Totals, or am I wrong?

THANK YOU VERY MUCH for your kind help!

CALCULATE;

CREATE SET CURRENTCUBE.[GoodMembers]

AS {

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[01]")), {}, [DIM Mesi].[DIM Mesi].&[01]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[02]")), {}, [DIM Mesi].[DIM Mesi].&[02]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[03]")), {}, [DIM Mesi].[DIM Mesi].&[03])

};

scope

(

{[Measures].[Costi1]}

,

{

[GoodMembers]

}

);

this= [DIM Mesi].[DIM Mesi].&[00]/3

;

freeze(this);

end scope;

scope

([Measures].[Costi1],

{

[DIM Mesi].[DIM Mesi].&[00]

}

);

this= 0;

end scope

mdx script context execution

Hi everybody.

I have a mdx script on a cube involving several cells through subcube. The subcube takes the values of some cells and use them to calculate the values of some other cells.

I have a user who has limited access rights on the dimensions, so that he can't see all the cells of the cube.

But I need that all the users can perform all the calculations in the cube even if there are some members (used to evaluate the calculations) they are not allowed to.

I know that the calculations script are evalueted in the context of the user querying the cube.

I wonder if there is a command to make the script being evaluated in the context of a specified user, an administrator in this case (something like "execute as"). So that the calculations are applied first, and after that, the user with reduced rights can see only the cells he is allowed to...

thank you very much.

Sorry, but there is no 'Execute As' feature and I can't think of anyway that you could reference a secured cell in a calculation.|||

Actually one possible solution to this would be to write a .Net stored procedure, that would would use AdoMd to connect back to the user with a new connection and execute an MDX query and return the results. When you deploy a stored proc assembly you have the option of specifying the account to impersonate.

Performance could be an issue with this approach as there is a bit of overhead involved in calling out to a stored procedure, but if your calc is at a fairly high level this might be a solution.

|||

Well, you give me a wisdom, thank you very much!

A more question, because I've never used a .Net stored procedure for Analysis Services. Do you mean that through it I can make the calculations in the script of the cube being executed by a specified user with all the necessary rights, and, after the calculations are realized on all the involved cells, then I can make the application to connect back as the current user to query the cube (therefore, seeing the previous results but only for the allowed cells)?

It'd be great.

have you any examples?

Thank you very very much!!!!

|||Check out this link for examples: http://www.codeplex.com/ASStoredProcedures|||

The AS Stored Procedure project has some good examples (I wrote some of them). However I don't think we have any that do exactly what you are after.

What would happen is that from a "limited" user, they would request a value.

That value would be calculated by a stored procedure.

When the stored procedure is deployed it can be configured to run under a specific user account.

In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query.

This would return a cellset and the stored proc would extract the value of the required cell and return that value|||

Thank you very much again.

You say: “

v In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query.

v This would return a cellset and the stored proc would extract the value of the required cell and return that value

“

But I wonder if in this way the “poor” user (with limited rights) in Role1, issuing the query through the specified user for th AS SP, would be allowed to see also the cells he is forbidden to… In fact I need him to see only the cells he is allowed to, but with the values calculated with calculations involving all the cells (even the ones he is not allowed to). Is this possible this way?

However, I give you an example of what I need to do. Let’s say I need to do something like in the following script (a very simplified version of my script, but just to understand the type of calculation).

I have a dimension [DIM Mesi] with four key members [01], [02], [03], [00] and a one-level attribute hierarchy [Dim Mesi].

Role1 can access only the “[DIM Mesi].[DIM Mesi].&[01]”, “[DIM Mesi].[DIM Mesi].&[02]” and the ”existing” with these ones (I mean the ancestors of these members, which in this case, is the AllMember).

There is a basket member [DIM Mesi].[DIM Mesi].&[00] from where I take the values to be reversed on the three members [01], [02], [03] from the Measure [Costi1] into the same measure (or even in a different measure).

The calculations are something similar to the ones below.

I’d like the users of Role1 be allowed to see 1/3 of the values of cells of the &[00] member in the cells of &[01] and &[02], but they should not be able to see nor the &[00] member related cells neither the &[03] member related cells.

Further, the [DIM Mesi].[DIM Mesi].&[00] should be assigned a 0 value (even if the user can’t see that value) so that the total is not twice, in case I don’t use VisualTotals.

If possible, I’d like to even be able to decide whether he must see the Visual Totals or not, but I suppose that through the AS SP executed by another user I can only see the Actual Totals, or am I wrong?

THANK YOU VERY MUCH for your kind help!

CALCULATE;

CREATE SET CURRENTCUBE.[GoodMembers]

AS {

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[01]")), {}, [DIM Mesi].[DIM Mesi].&[01]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[02]")), {}, [DIM Mesi].[DIM Mesi].&[02]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[03]")), {}, [DIM Mesi].[DIM Mesi].&[03])

};

scope

(

{[Measures].[Costi1]}

,

{

[GoodMembers]

}

);

this= [DIM Mesi].[DIM Mesi].&[00]/3

;

freeze(this);

end scope;

scope

([Measures].[Costi1],

{

[DIM Mesi].[DIM Mesi].&[00]

}

);

this= 0;

end scope

MDX question - comparing values for individual member against average for whole population

Hi,
I need some MDX help.
I've got an AS 2005 cube that includes a dimension called "Company" consisting of a number of different companies. Within my cube I need to be able to show measures for individual companies against the same measures for the whole market (i.e. all companies), both the total and the average. Note: I need to be able to do this for any measure within my cube.

e.g.

Company Name Company Total Market Total Market Average
BOB'S COMPANY 100 5000 150
ERIC's COMPANY 200 5000 150

I thought I might be able to implement this by creating an extra dimension called, say, "AmountType" with a default member of "Company Total" hardcoded into the underlying fact view. I could then create a calculated member for this dimension called "Market Total" which could be used to derive the total for the whole population. The following illustrates the type of MDX statements that I am trying (the set that I am using in the sum function includes a set and a tuple - this is valid isn't it?):

sum({Company.ALLMEMBERS, [Amount Type].&[Company Total]}, [Measures].CurrentMember)

Am I on the right lines. Can anyone tell me the sort of MDX i need to get this to work?

Thanks

George

George,

I think I understand what you have done, but just to make sure I will go over the steps I think you have taken and outline a solution that should work for you.

You have a dimension called "AmountType" that has a single member coming from your data source view called "Company Total". One thing that you should do is to make sure that the properties for this attribute hierarchy are set so that the "All" member does not show up if you are using the dimension soley as a placeholder for calculated members. This can be done by openning the dimension in the dimension editor, clicking on the attribute hierarchy and then setting the "IsAggregatable" property to false. You should now have a dimension with one member: "Company Total". Do you have an "All" member level defined for your "Company" hierarchy? I am guessing that you do and the calculations I will provide would have to be modified if you don't. Once you have the "AmountType" dimension rebuilt you can try adding the following calculations to your script:

CREATE MEMBER CURRENTCUBE.[AmountType].[AmountType].[Market Total]

AS

(Company.Company.DefaultMember,[AmountType].[AmountType].DefaultMember),

FORMAT_STRING = "Standard",

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time].[Period Calculation].[Market Avg]

AS

[AmountType].[AmountType].[Market Total] / Company.Company.Members.Count,

FORMAT_STRING = "Standard",

VISIBLE = 1;

Let me know if I misunderstood something or this MDX gives you values different from what you are looking for.

HTH,

- Steve

MDX query to get negative measures

I need to query a cube with all negative

values, unfortunately I'm new to MDX and find the syntax very alien compared to

SQL.

I want to get the sum

of all negative values from the database.

SQL would

be:

SELECT Desk,Rating_Group, SUM(Value)
FROM fact
WHERE

SUM(Value)<0
GROUP BY Desk,Rating_Group

I've tried various ways to

get the data, but the aggregation does not

appear to be correct.

MDX:
WITH MEMBER [Measures].[Long]

AS
IIF(
[Measures].[Risk Value]<0,
[Measures].[Risk

Value],
NULL)

SELECT NON EMPTY { [Measures].[Long]} ON COLUMNS,

NON EMPTY { ([Vdim Curve Family].[Hierarchy].[SP Rating Group]*[Vdim

Book].[Desk].[Desk].Members) }
ON ROWS
FROM [DM]

What appears to

be happenning is that the data is being pre-aggregated, so it returns the

equivalent of the SQL

SELECT Desk,Rating_Group, SUM(Value)
FROM

fact
GROUP BY Desk,Rating
HAVING SUM(Value)<0

I've also tried

filter, aggregate,sum and all appear to give me the same

problem.

Comments gratefully received, thanks if anyone can enlighten

me.

I'm considering going back to the view that builds my cube and

changing it and adding a new measure, but it seems over the top for something

which is so easy in SQL.My advice to you: For this task stay with SQL, and don't even try to do it in MDX. While technically it is possible, the performance on any non-trivial size cube will be bad. Your task is a good example of what should be left to SQL and not to OLAP.|||Thanks for the reply Mosha.

One of the main benefits of putting the data into a cube is that there are less problems with locking, which is a major problem due to the requirements to run a large number of reports.

MDX queries have less performance problems in terms of concurrency and this is my reason for attempting to get the data into a cube. Going back to SQL would excacerbate the problems I have at the moment (unfortunately politics means that I cannot replace some of the existing data sources with a integration services solution).

I think my research leads me to believe that my best option will be to go back to my data source views that populate the cube and attempt to use a case statement to populate a negative and positive value fields.

I'm finding MDX a fustrating experience, the language is very close to SQL, but a lot of the functions do not do what would be obvious to a SQL programmer and there is a steep learning curve as I'm finding 10 years of SQL development is not always helpful.|||

> MDX queries have less performance problems in terms of concurrency and this is my reason for attempting to get the data into a cube. Going back to SQL would excacerbate the problems

OLAP (MDX) have less performance problems when you are using them for the queries they were designed for. Trying to find all the negative values at the leaves level is something that OLAP is not well suited for, and I bet that SQL will outperform it easily.

> I think my research leads me to believe that my best option will be to go back to my data source views that populate the cube and attempt to use a case statement to populate a negative and positive value fields.

Yes - the best option is to add a new attribute in the cube which will take one value for negative measures and another for positive measures. Then OLAP & MDX will work fine and produce a great performance.

Mdx Query doubt about years?

Hi,
The below query which has hardcoded year ending values works fine.

What i want to do is if I pass on last 3 years or last 4 years etc as parameter along with current year it should bring back the results accordingly for the required years.

with
member [YearEnding].[CurrYear] as '[YearEnding].[All YearEnding].[2005]'
select
non empty {[Product].Children, Product.[All Product]} on columns,
non empty Filter({ {[Measures].[stock Quantity Ola] } * { [YearEnding].Children} }, (([YearEnding] = [YearEnding].[2005]) or ([YearEnding] = [YearEnding].[2004]) or ([YearEnding] = [YearEnding].[2003])) ) on rows
from Stock
Help much appreciated
Many Thanks

VeeraFirstly, you could run into problems using '=' operator to test for dimension members - this blog entry by Mosha Pasumanskyexplains why:

http://sqljunkies.com/WebLog/mosha/archive/2004/11/4.aspx
>>
Comparing members in MDX
...
>>

Since measures are not on columns, this looks like OLE DB Provider for OLAP, for which the MDX query has to be converted to an expression, to add parameters. Assuming a multi-select parameter like "SelectedYears", with selected string values like "[YearEnding].[2003]", etc, then the filter condition can be built using Join():

+ "Count(Intersect({[YearEnding].CurrentMember}, {"
+ Join(Parameters!SelectedYears.Value, ",")
+ "})) = 1) on rows from Stock"