SQL Server Performance

MDX get all negative values

Discussion in 'SQL Server 2005 Analysis Services' started by smithsi, Mar 9, 2007.

  1. smithsi New Member

    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.
  2. ranjitjain New Member

    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]
  3. smithsi New Member

    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?


  4. ranjitjain New Member

    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.
  5. smithsi New Member

    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.

Share This Page