none
MDX Expression – filter results using dimension members

    Pregunta

  • I’ve pulled almost all my hair out due to frustration as I’m failing to come up with an MDX expression that filters results from a cube using a dimension member. Here are the objects that I’m using:

    Cube name: MyCube

    Dimension1: DimArrearsBands (Attributes – ArrearsBandKey)

    Dimension2: DimAccount (Attributes – AccountKey, CurrentBTV)

    Measures:  MonthEndAccountBalance, RollRateProvision

    Using a SQL statement illustrated below on the relational database I get the results that I want. How can I translate this to MDX so that I use it to query the cube? It is the WHERE ma.CurrentBTV > 80 which is giving me a tough time.

    SELECT     ab.ArrearsBandKey, fm.MonthEndAccountBalance, fm.RollRateProvision

    FROM         fact.FactAccountProvisions fm INNER JOIN

                          dim.DimArrearsBands ab ON fm.ArrearsBandKey = ab.ArrearsBandKey INNER JOIN

                          dim.DimAccount ma ON fm.AccountKey = ma.AccountKey

    WHERE ma.CurrentBTV > 80

    Many thanks,


    Mpumelelo

    jueves, 21 de junio de 2012 14:29

Respuestas

  • Hi,

    Can you try this now...

    SELECT 
      NON EMPTY 
        {
          [Measures].[Month End Mortgage Balance]
         ,[Measures].[Roll Rate Provision]
         ,[Measures].[Fact Mortgage Provisions Count]
        } ON COLUMNS
     ,NON EMPTY 
        {
          [Arrears Bands].[Arrears Band Key].[Arrears Band Key].ALLMEMBERS
        }
      DIMENSION PROPERTIES 
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS
    FROM 
    (
      SELECT 
        {[Mortgage Account].[FSA Regulated].&[Y]} ON COLUMNS
      FROM 
      (
        SELECT 
          Filter
          (
            [Mortgage Account].[Current BTV].[Current BTV]
           ,
              [Mortgage Account].[Current BTV].CurrentMember.Properties("Key")
            > 80
          ) ON COLUMNS
        FROM [Provisions]
      )
    )
    WHERE 
      [Mortgage Account].[FSA Regulated].&[Y]
    CELL PROPERTIES 
      VALUE
     ,BACK_COLOR
     ,FORE_COLOR
     ,FORMATTED_VALUE
     ,FORMAT_STRING
     ,FONT_NAME
     ,FONT_SIZE
     ,FONT_FLAGS;


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    • Marcado como respuesta Mpumelelo S martes, 26 de junio de 2012 11:16
    viernes, 22 de junio de 2012 9:43
  • Hi Mpumelelo

    In regards to point 1. It should be as simple as changing the comparison sign to <=, or if you want to make sure, try and make it < 81 (assuming Current BTV is an Integer? If you are getting no data, is it not due to some other filter and so you have no data?

    With regards to point 2. How would you pass in your parameter, and where would you want to see it? Is it a Single or Multi-Value parameter?

    Essentially what you would want to do is use the StrToMember(@DateParameter) - If single valued parameter - or StrToSet(@DateParameter) -Single or Multi-valued - in either the Rows or Where clause, as well as the SubQuery if applicable.

    So using Raunak's query above you could do the following:

    SELECT 
      NON EMPTY 
        {
          [Measures].[Month End Mortgage Balance]
         ,[Measures].[Roll Rate Provision]
         ,[Measures].[Fact Mortgage Provisions Count]
        } ON COLUMNS
    ,NON EMPTY 
        {
          [Arrears Bands].[Arrears Band Key].[Arrears Band Key].ALLMEMBERS
        }
      DIMENSION PROPERTIES 
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS
    FROM 
    (
      SELECT 
        {[Mortgage Account].[FSA Regulated].&[Y]} ON COLUMNS
      FROM 
      ( 
       SELECT StrToSet(@DateParameter, CONSTRAINED) ON COLUMNS FROM 
       (
        SELECT 
          Filter
          (
            [Mortgage Account].[Current BTV].[Current BTV]
           ,
              [Mortgage Account].[Current BTV].CurrentMember.Properties("Key")
            > 80
          ) ON COLUMNS
        FROM [Provisions]
      )
     )
    )
    WHERE 
     ( [Mortgage Account].[FSA Regulated].&[Y]
     , StrToSet(@DateParameter, CONSTRAINED) 
    )
    CELL PROPERTIES 
      VALUE
    ,BACK_COLOR
    ,FORE_COLOR
    ,FORMATTED_VALUE
    ,FORMAT_STRING
    ,FONT_NAME
    ,FONT_SIZE
    ,FONT_FLAGS;

    • Marcado como respuesta Mpumelelo S martes, 26 de junio de 2012 11:16
    viernes, 22 de junio de 2012 10:50

Todas las respuestas

  • Hi Mpumelelo

    What you want to do is filter your accounts where CurrentBTV > 80, which you can do in either the where clause or a sub clause:

    SELECT {[measures].[MonthEndAccountBalance], [measures].[RollRateProvision]} ON 0

             ,NON EMPTY([DimArrearsBand].[ArrearsBandKey].[ArearsBandKey]) ON 1

    FROM (SELECT FILTER(NONEMPTY([DimAccount].[AccountKey].[AccountKey]), [DimAccount].[AccountKey].currentmember.properties("CurrentBTV") > 80) FROM [Cube]

    Alternatively if your CurrentBTV attribute hierarchy is set up so that it runs from 0 -> 100 correctly, you could replace the subquery with something like

    EXISTS([DimAccount].[AccountKey].[AccountKey], {[DimAccount].[CurrentBTV].&[80]:[DimAccount].[CurrentBTV].&[100]})

    Hope that helps

    jueves, 21 de junio de 2012 15:04
  • Hi Michael

    Thank you for your response. The second suggestion using EXISTS which you have given is the more straightforward of the two. I tried it before I even posted my question. Whilst my query was syntactically correct, it didn’t return any results because there is no sequential increment of the CurrentBTV range. Using your example, from 0 -> 100, it is not like that. In the data that I have there is not even 80 but the requirement is that the results should be filtered from 80 upwards. Besides, the upper range is more than 100.

    I haven’t managed to get this working.

    Many thanks.


    Mpumelelo

    jueves, 21 de junio de 2012 15:49
  • I think I will pull all the results with CurrentBTV ranging from 0 to the maximum figure in the dataset and then use Report Builder to filter the ranges which have been specified in the report requirements. It looks like this may be straightforward. I've tried it and it appears to be working.

    Many thanks,


    Mpumelelo

    jueves, 21 de junio de 2012 16:08
  • Hi Mpumelelo

    Does your CurrentBTV value start at 0? The other thing to try in the Subquery is the reverse of the so do something like:

    EXCEPT([DimAccount].[AccountKey].[AccountKey] , EXISTS([DimAccount].[AccountKey].[AccountKey], {NULL:[DimAccount].[CurrentBTV].&[80]})

    Except I just saw that you don't have a member 80! Have you tried the first query I gave you, as that is the only other way I can think of doing it, other than adding a new Attribute to the Account dimension which would probably give you the best result.

    Actually, I believe you can simply use the CurrentBTV attribute in the query, so

    FILTER([DimAccount].[CurrentBTV].[CurrentBTV], [DimAccount].[CurrentBTV].CurrentMember.Properties("Key") > 80)

    Hope that helps

    Mike

    jueves, 21 de junio de 2012 21:17
  • Hi Michael

    Thank you for your latest response. It appears like I still have issues with my query even when tying your latest suggestion. I’m receiving a message Parser: The end of the input was reached. Maybe there is something wrong with my query from my side. In the meantime I will use Report Builder to do the filtering as I am working towards a deadline for the report that I’m building. I will revisit the stuff once I’m done with the report.

    Many thanks,


    Mpumelelo

    viernes, 22 de junio de 2012 8:10
  • Hi,

    Can you paste the MDX here?


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    viernes, 22 de junio de 2012 8:39
  • My original attempt to use pseudo names here has been beaten as it is taking so much time for me to do that in the MDX that I’m posting. Sorry Michael, your suggestions could be right and maybe I’m failing to correctly translate them to my proper MDX statement. My MDX skills are not that good at all. So most of the things are from using designer on Report Builder and then try to tweak the results. I have no choice but offload my attempts here as they are.

    SELECT NON EMPTY { [Measures].[Month End Mortgage Balance], [Measures].[Roll Rate Provision], [Measures].[Fact Mortgage Provisions Count] } ON COLUMNS,

     NON EMPTY { ([Arrears Bands].[Arrears Band Key].[Arrears Band Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

     FROM ( SELECT ( { [Mortgage Account].[FSA Regulated].&[Y] } ) ON COLUMNS

     --FROM ( SELECT ( [Mortgage Account].[Current BTV].&[.000] : [Mortgage Account].[Current BTV].&[79.999] ) ON COLUMNS

     --FILTER([DimAccount].[CurrentBTV].[CurrentBTV], [DimAccount].[CurrentBTV].CurrentMember.Properties("Key") > 80)

     FROM ( SELECT FILTER([Mortgage Account].[Current BTV].[Current BTV], [Mortgage Account].[Current BTV].currentmember.Properties("Key") > 80)

     FROM [Provisions])) WHERE ( [Mortgage Account].[FSA Regulated].&[Y] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    Many thanks.


    Mpumelelo

    viernes, 22 de junio de 2012 9:20
  • Hi,

    Can you try this now...

    SELECT 
      NON EMPTY 
        {
          [Measures].[Month End Mortgage Balance]
         ,[Measures].[Roll Rate Provision]
         ,[Measures].[Fact Mortgage Provisions Count]
        } ON COLUMNS
     ,NON EMPTY 
        {
          [Arrears Bands].[Arrears Band Key].[Arrears Band Key].ALLMEMBERS
        }
      DIMENSION PROPERTIES 
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS
    FROM 
    (
      SELECT 
        {[Mortgage Account].[FSA Regulated].&[Y]} ON COLUMNS
      FROM 
      (
        SELECT 
          Filter
          (
            [Mortgage Account].[Current BTV].[Current BTV]
           ,
              [Mortgage Account].[Current BTV].CurrentMember.Properties("Key")
            > 80
          ) ON COLUMNS
        FROM [Provisions]
      )
    )
    WHERE 
      [Mortgage Account].[FSA Regulated].&[Y]
    CELL PROPERTIES 
      VALUE
     ,BACK_COLOR
     ,FORE_COLOR
     ,FORMATTED_VALUE
     ,FORMAT_STRING
     ,FONT_NAME
     ,FONT_SIZE
     ,FONT_FLAGS;


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    • Marcado como respuesta Mpumelelo S martes, 26 de junio de 2012 11:16
    viernes, 22 de junio de 2012 9:43
  • Thank you Raunak. That query is working perfectly well. However, I have two more questions. Bear with me as it appears like you guys are now doing my work.

    1. I also want to view result of those data items that are less than or equals to 80. If I change the comparison sign from >80 to <=80 like I would do in SQL no results are getting returned. How do I accomplish <=80 in MDX? I mean, in the query that you have given me it can’t work by simply changing >80 to <=80.
    2. Another thing is that I want to include a parameter into this query. It appears like doing that is not as easy as I imagined as it is TSQL. Results need to be viewed by MonthEnd date; hence I want to include a @MonthEnd parameter in my MDX query. The dimension that I want to use for that is called [Dim Month End]. The attribute hierarchy that I want to use is [Dim Month End].[Date New Format]. How do I include that as a parameter in my MDX query?

    Mpumelelo

    viernes, 22 de junio de 2012 10:11
  • For 1: I would suggest you re-frame your MDX to use the EXISTS() function in MDX. Check here: http://msdn.microsoft.com/en-us/library/ms144936.aspx. Using Except can also be considered. http://msdn.microsoft.com/en-us/library/ms144900.aspx

    For 2: See this. http://www.sqlservercentral.com/Forums/Topic1183489-17-1.aspx#bm1286193


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    viernes, 22 de junio de 2012 10:42
  • Hi Mpumelelo

    In regards to point 1. It should be as simple as changing the comparison sign to <=, or if you want to make sure, try and make it < 81 (assuming Current BTV is an Integer? If you are getting no data, is it not due to some other filter and so you have no data?

    With regards to point 2. How would you pass in your parameter, and where would you want to see it? Is it a Single or Multi-Value parameter?

    Essentially what you would want to do is use the StrToMember(@DateParameter) - If single valued parameter - or StrToSet(@DateParameter) -Single or Multi-valued - in either the Rows or Where clause, as well as the SubQuery if applicable.

    So using Raunak's query above you could do the following:

    SELECT 
      NON EMPTY 
        {
          [Measures].[Month End Mortgage Balance]
         ,[Measures].[Roll Rate Provision]
         ,[Measures].[Fact Mortgage Provisions Count]
        } ON COLUMNS
    ,NON EMPTY 
        {
          [Arrears Bands].[Arrears Band Key].[Arrears Band Key].ALLMEMBERS
        }
      DIMENSION PROPERTIES 
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS
    FROM 
    (
      SELECT 
        {[Mortgage Account].[FSA Regulated].&[Y]} ON COLUMNS
      FROM 
      ( 
       SELECT StrToSet(@DateParameter, CONSTRAINED) ON COLUMNS FROM 
       (
        SELECT 
          Filter
          (
            [Mortgage Account].[Current BTV].[Current BTV]
           ,
              [Mortgage Account].[Current BTV].CurrentMember.Properties("Key")
            > 80
          ) ON COLUMNS
        FROM [Provisions]
      )
     )
    )
    WHERE 
     ( [Mortgage Account].[FSA Regulated].&[Y]
     , StrToSet(@DateParameter, CONSTRAINED) 
    )
    CELL PROPERTIES 
      VALUE
    ,BACK_COLOR
    ,FORE_COLOR
    ,FORMATTED_VALUE
    ,FORMAT_STRING
    ,FONT_NAME
    ,FONT_SIZE
    ,FONT_FLAGS;

    • Marcado como respuesta Mpumelelo S martes, 26 de junio de 2012 11:16
    viernes, 22 de junio de 2012 10:50
  • Thank you Michael. I intend passing this the parameter in the report. While the users will predominantly use single values, I think it will be safe for me to make my parameter to be multivalued. Also, thank you for the hint of changing the figure to 81. I think that will be easy to accomplish. Let me try to implement the suggestions and take them over to my report and see if I will win. Very grateful to you both. I will mark your suggestions as answers once I’m done with the report in case another question comes up in the meantime.


    Mpumelelo

    viernes, 22 de junio de 2012 11:21