MDX Expression – filter results using dimension members

Formular una pregunta

jueves, 21 de junio de 2012 14:29

0

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
- Responder
- Citar

Todas las respuestas

jueves, 21 de junio de 2012 15:04

0

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
- Responder
- Citar
jueves, 21 de junio de 2012 15:49

0

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
- Responder
- Citar
jueves, 21 de junio de 2012 16:08

0

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
- Responder
- Citar
jueves, 21 de junio de 2012 21:17

0

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
- Responder
- Citar
viernes, 22 de junio de 2012 8:10

0

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
- Responder
- Citar
viernes, 22 de junio de 2012 8:39

0

Hi,

Can you paste the MDX here?
Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog
- Responder
- Citar
viernes, 22 de junio de 2012 9:20

0

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
- Responder
- Citar

viernes, 22 de junio de 2012 9:43

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 10:11

0
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
- Responder
- Citar
viernes, 22 de junio de 2012 10:42

0

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
- Responder
- Citar

viernes, 22 de junio de 2012 10:50

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 11:21

0

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
- Responder
- Citar

MDX Expression – filter results using dimension members

Todas las respuestas

Productos

clave

Soluciones

Actualizaciones

Pruebas

Sitios relacionados

Aprendizaje

Certificaciones

Otros recursos

Por producto

Otros vínculos