locked
MDX Expressions issue RRS feed

  • Question

  • Hi. Describing this issue without images may be a bit difficult... Ok, let's try it:

     

    The problem is that I have two similar views in ProClarity, and I get data only in one of them. Let's compare:

     

    ---------------------------------

     

    1. This view has 2 siblings items selected in ROWS and their children also selected via Level. More accurately:

     

               MACROBOOK

                     Level 07

               NOTAESTRUC

                     Level 07

     

    It works. The MDX obtained from ProClarity is:

     

    SELECT { [DIM TIEMPO].[FECHAS].[AÑO].&[2008].&[4].&[2008-04-02T00:00:00] } ON COLUMNS ,

     

    NON EMPTY { [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[400397], DESCENDANTS( [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[400397], [DIM GESTION CARTERA].[JERARQUIA CARTERAS].[Level 07] ), [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[400417], DESCENDANTS( [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[400417], [DIM GESTION CARTERA].[JERARQUIA CARTERAS].[Level 07] ) } ON ROWS 

     

    FROM [PRISMA2_CP]

     

    WHERE ( [Measures].[VAL VALOR AJUSTADO - FACT SENSIBILIDAD], [DIVISA CALCULO].[DIVISA CALCULO].&[5922], [DIM FACTOR RIESGO].[FACTOR RIESGO].&[151], [DIM GRIEGA].[GRIEGA].&[2], [DIM GESTION CARTERA].[UNIDAD].&[MADRID], [DIVISA PRESENTACION].[DIVISA PRESENTACION].&[5922], [DIM METRICA].[NOMBRE COMPLETO METRICA].&[Sensibilidad 1pb] )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORE_COLOR, BACK_COLOR, FONT_FLAGS

     

     

    2. This view has not Level 07 selected in NOTAESTRUC:

     

               MACROBOOK

                     Level 07

               NOTAESTRUC

     

    The problem is that it shows data in MACROBOOK and NOTAESTRUC members, but not in MACROBOOK Level 07 members with data. The MDX obtained from ProClarity is:

     

    SELECT { [DIM TIEMPO].[FECHAS].[AÑO].&[2008].&[4].&[2008-04-02T00:00:00] } ON COLUMNS ,

     

    NON EMPTY { [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[400397], DESCENDANTS( [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[400397], [DIM GESTION CARTERA].[JERARQUIA CARTERAS].[Level 07] ), [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[400417] } ON ROWS 

     

    FROM [PRISMA2_CP]

     

    WHERE ( [Measures].[VAL VALOR AJUSTADO - FACT SENSIBILIDAD], [DIVISA CALCULO].[DIVISA CALCULO].&[5922], [DIM FACTOR RIESGO].[FACTOR RIESGO].&[151], [DIM GRIEGA].[GRIEGA].&[2], [DIM GESTION CARTERA].[UNIDAD].&[MADRID], [DIVISA PRESENTACION].[DIVISA PRESENTACION].&[5922], [DIM METRICA].[NOMBRE COMPLETO METRICA].&[Sensibilidad 1pb] )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORE_COLOR, BACK_COLOR, FONT_FLAGS

     

    ---------------------------------

     

    Both queries have been tested in ProClarity Desktop Professional and in SQL Server Management Studio with the same result.

     

    Could you give me a hand?

     

    Thanks!

    Thursday, April 17, 2008 11:02 AM

Answers

  • Hi Daniel,

     

    Just to make sure I'm reading this right; you're not receiving incorrect data within your results, you simply do not get any data at all for Level 07 (in the 2nd query) even though it's included in the MDX.  Is that correct?  If so, can you make sure you're running the latest Analysis Services SP2?  And as Joey suggested, if this is reproducible in Adventure Works let us know.  I'd be happy to do some testing myself.

     

    I'm not sure how familiar you are with SQL Profiler, however a trace file may help to uncover the culprit.  Those files are typically convoluded so you can take that advice with a grain of salt.  Also, have you tried posting this to the Analysis Services forum?  They may be able to assist as well. 

     

    http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=83&SiteID=17

     

    Thanks,

    Amanda

     

    Thursday, April 17, 2008 9:52 PM

All replies

  • Even more:

     

    If I comment any line containing a row item in this query the query returns no value and no item too:

     

    SELECT { [DIM TIEMPO].[FECHAS].[AÑO].&[2008].&[4].&[2008-04-02T00:00:00] } ON COLUMNS ,

    { [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600379],
      [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600380],
      [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600381],
      [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600382],
      [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600383],
      [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600384],
      [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600385],
      [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600388],
      [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600386],
      [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600389],
      [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600390],
      [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600391],
      [DIM GESTION CARTERA].[JERARQUIA CARTERAS].&[600392] } ON ROWS 

    FROM [PRISMA2_CP]

    WHERE ( [Measures].[VAL VALOR AJUSTADO - FACT SENSIBILIDAD], [DIVISA CALCULO].[DIVISA CALCULO].&[5922], [DIM FACTOR RIESGO].[FACTOR RIESGO].&[151], [DIM GRIEGA].[GRIEGA].&[2], [DIM GESTION CARTERA].[UNIDAD].&[MADRID], [DIVISA PRESENTACION].[DIVISA PRESENTACION].&[5922], [DIM METRICA].[NOMBRE COMPLETO METRICA].&[Sensibilidad 1pb] )
    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORE_COLOR, BACK_COLOR, FONT_FLAGS

     

    How is that possible? It's one of the strangest issues I have ever seen in MDX world!

     

    Thanks four your attention!

    Thursday, April 17, 2008 2:43 PM
  • Hi Daniel,

     

    Can you reproduce this in Adventure Works and send me the MDX (a Briefing Book with examples against Adventure Works would also be handy)? 

     

    I've seen a couple of these types of corner cases, but I don't want to comment until I can repro it.

     

    -Joey

    Thursday, April 17, 2008 3:35 PM
  • Hi Daniel,

     

    Just to make sure I'm reading this right; you're not receiving incorrect data within your results, you simply do not get any data at all for Level 07 (in the 2nd query) even though it's included in the MDX.  Is that correct?  If so, can you make sure you're running the latest Analysis Services SP2?  And as Joey suggested, if this is reproducible in Adventure Works let us know.  I'd be happy to do some testing myself.

     

    I'm not sure how familiar you are with SQL Profiler, however a trace file may help to uncover the culprit.  Those files are typically convoluded so you can take that advice with a grain of salt.  Also, have you tried posting this to the Analysis Services forum?  They may be able to assist as well. 

     

    http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=83&SiteID=17

     

    Thanks,

    Amanda

     

    Thursday, April 17, 2008 9:52 PM
  • Hi. I can't reproduce this problem in Adventure Works DB  :-(

     

    In addition, I've had a look to SQL Server Profiler and I don't find the cause of the problem. I'll try to investigate further.

     

    Amanda, thanks for the AS forum URL, I'll post the issue asap.

     

    Regards

    Tuesday, April 22, 2008 8:19 AM