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.