Essbase MDX queries not matching

Question

I am using a BI tool that auto-generates MDX code for an Essbase data source. I am running two queries that should bring back the same numbers, but I get different numbers. The first query brings back results by month for 12 months of the year and produces incorrect results:

SELECT 
  NON EMPTY 
    {
      [Jan]
     ,[Feb]
     ,[Mar]
     ,[Apr]
     ,[May]
     ,[Jun]
     ,[Jul]
     ,[Aug]
     ,[Sep]
     ,[Oct]
     ,[Nov]
     ,[Dec]
    }
  DIMENSION PROPERTIES 
    [MEMBER_UNIQUE_NAME]
   ,[MEMBER_CAPTION]
   ,[GEN_NUMBER]
   ,[LEVEL_NUMBER]
   ON COLUMNS
 ,NON EMPTY 
    Descendants
    (
      [ABOVE]
     ,[ABOVE].Level
     ,AFTER
    )
  DIMENSION PROPERTIES 
    [MEMBER_UNIQUE_NAME]
   ,[MEMBER_CAPTION]
   ,[GEN_NUMBER]
   ,[LEVEL_NUMBER]
   ON ROWS
FROM [F_IntPrf].[F_IntPrf]
WHERE 
  (
    [ALL_TERRITORY]
   ,[ALL_PS_BUS_AFF]
   ,[ALL_PS_BUS]
   ,[Input Currency]
   ,[MayFC]
   ,[FY17]
   ,[Forecast]
   ,[USDRep]
  );

The second query shows the results for January only, and produces the correct results :

SELECT 
  NON EMPTY 
    Descendants
    (
      [ABOVE]
     ,[ABOVE].Level
     ,AFTER
    )
  DIMENSION PROPERTIES 
    [MEMBER_UNIQUE_NAME]
   ,[MEMBER_CAPTION]
   ,[GEN_NUMBER]
   ,[LEVEL_NUMBER]
   ON COLUMNS
FROM [F_IntPrf].[F_IntPrf]
WHERE 
  (
    [Jan]
   ,[ALL_TERRITORY]
   ,[ALL_PS_BUS_AFF]
   ,[ALL_PS_BUS]
   ,[Input Currency]
   ,[MayFC]
   ,[FY17]
   ,[Forecast]
   ,[USDRep]
  );

What am I doing wrong in the first query ?

Thank you!

In what way are the results wrong? What happens if you add [Jan] to your slicer axis in the first query (since that's apparently the difference in the slicer between the two)? — jwj, Aug 09 '17 at 21:34

score 0 · Answer 1 · answered Aug 15 '17 at 08:38

To focus in on the problem I would start by comparing two very simple queries such as these two:

SELECT 
  NON EMPTY 
    {
      [Jan]
    }
   ON COLUMNS
 ,NON EMPTY 
    Descendants
    (
      [ABOVE]
     ,[ABOVE].Level
     ,AFTER
    )
   ON ROWS
FROM [F_IntPrf].[F_IntPrf]
WHERE 
  (
    [ALL_TERRITORY]
  );

versus this...

SELECT 
  NON EMPTY 
    Descendants
    (
      [ABOVE]
     ,[ABOVE].Level
     ,AFTER
    )
   ON COLUMNS
FROM [F_IntPrf].[F_IntPrf]
WHERE 
  (
    [Jan]
   ,[ALL_TERRITORY]
  );

Do they match? If they do then slowly add in the other dimensions until they don't match and then you know where the problem is

Essbase MDX queries not matching

1 Answers1