SQL Server Performance Forum – Threads Archive
MDX get all negative values
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.
Hi Smith,
Try this: select [Measures].[Risk Value] on 0,
non empty FILTER([Vdim Curve Family].[Hierarchy].[SP Rating Group].members*[Vdim Book].[Desk].[Desk].Members,[Measures].[Risk Value]<0) on 1 from [DM]
Thanks for your response Ranjit I’ve checked this against the star schema and it is still not quite giving me what I want. I can confirm this by adding the totals for a specific set. — If I check for all negative values I with this SQL
use DM
GO
SELECT Desk, SUM(Risk_Value)
FROM olap.vTbl_Fact_MO_Mart_DLY_2007_01_08 F, olap.vdimBook B,olap.vdimCurveFamily C
WHERE F.BusinessID = B.BusinessID
AND F.MarketDataID = C.MarketDataID
AND B.Desk = ‘PRINCIPAL FINANCE’
AND SP_Rating IN (‘A-‘,’A+’,’A’)
AND Risk_Value < 0
GROUP BY Desk Result = -718573.208186038 — If I check for all positive values with this SQL
use DM
GO
SELECT Desk, SUM(Risk_Value)
FROM olap.vTbl_Fact_MO_Mart_DLY_2007_01_08 F, olap.vdimBook B,olap.vdimCurveFamily C
WHERE F.BusinessID = B.BusinessID
AND F.MarketDataID = C.MarketDataID
AND B.Desk = ‘PRINCIPAL FINANCE’
AND SP_Rating IN (‘A-‘,’A+’,’A’)
AND Risk_Value < 0
GROUP BY Desk Result: 703298.312006189 –Which means that the SUM of all values is
SELECT 703298.312006189-718573.208186038 Result: -15274.896179849 The MDX you’ve supplied is giving me the same total of all risk in my cube and not negative values, I’m not sure whether this is a bug in the cube/SSAS or the way the cube was built, or whether I am not getting the correct values, because I do not understand MDX. The MDX appears equivalent to this SQL, based on the result: use DM
GO
SELECT Desk, SUM(Risk_Value)
FROM olap.vTbl_Fact_MO_Mart_DLY_2007_01_08 F, olap.vdimBook B,olap.vdimCurveFamily C
WHERE F.BusinessID = B.BusinessID
AND F.MarketDataID = C.MarketDataID
AND B.Desk = ‘PRINCIPAL FINANCE’
AND SP_Rating IN (‘A-‘,’A+’,’A’)
GROUP BY Desk
HAVING SUM(Risk_Value)<0 So it is first calculating the total of all risk and then only returning totals that are less than zero. What I was trying to do was to get all negative values and total them, which is slightly different. Any ideas?
Hi Smith,
I think I got confused,
You need to use the same MDX with FILTER criteria other way round. Check this:
select [Measures].[Risk Value] on 0,
non empty FILTER([Vdim Curve Family].[Hierarchy].[SP Rating Group].members*[Vdim Book].[Desk].[Desk].Members,[Measures].[Risk Value]>0) on 1 from [DM] The above MDX will filter out all positive [Risk value] thus returning only negative ones.
I think I’m coming round to understanding the problem myself, it’s my SQL mindset that is letting me down. The code would give only positive totals. select [Measures].[Risk Value] on 0,
non empty FILTER([Vdim Curve Family].[Hierarchy].[SP Rating Group].members*[Vdim Book].[Desk].[Desk].Members,[Measures].[Risk Value]>0) on 1 from [DM] What I was trying to do was go to the individual cell level and get each value and ascertain whether it was negative or positive. I think what I should be doing is changing my database view which shows risk and create 2 new fields long and short for positive and negative values.
]]>