0

Following is my query:

WITH
    SET [sp] AS
        ([time.fin].[day].[${parDate}]:[time.fin].[day].[${partoDate}])

SET [factory] AS
    {[organization].[org].[Fact1],[organization].[org].[Fact2],[organization].[org].[Fact3]}

MEMBER [btype].[b] AS
     AGGREGATE(IIF('${param}'='All',
                    [btype].[type].members,
                     [btype].[type].[${param}]
        ))    

SELECT
NON EMPTY {[factory]} ON COLUMNS,
NON EMPTY {[sp]}ON ROWS
FROM [cube1]
WHERE ([btype].[b], [Measures].[qty])

in this , btype is the degenerate dimension. When i execute this query on CDE .. sometimes I get java.lang.nullpointerexception , the behaviour is very random. Often, it gives the result and for default load , it always results positive . But for date range change , I randomly get the exception.

my fact_table structure has 5 normal dimension and 3 degenerates .

meanwhile, I have also observed that if add some more grain to the query , then the exception doesn't appear anymore. But adding that doesn't fullfill my required result.

Is it something to do with degenerate dimension concept or high cardinality issue

  • this is relatively straightforward `mdx` so unsure that your problem is the `mdx` – whytheq Apr 22 '15 at 11:26
  • duplicate of this: http://stackoverflow.com/questions/29755017/randon-error-processing-on-pentaho-cde-dashboard-for-mdx-query-widget – whytheq Apr 22 '15 at 11:27
  • i think the actual problem lies somewhere in degenerate dimension concept and not in mdx. I started this new thread , because this was a more specified and defined problem , rather than generic like that one.. the mdx query that i mentioned is for reference only –  Apr 22 '15 at 11:54

1 Answers1

0

Maybe this measure needs to be more explicit:

MEMBER [btype].[b] AS
     AGGREGATE(IIF('${param}'='All',
                    [btype].[type].members,
                     [btype].[type].[${param}]
        ))

If you change it to the following do you encounter the error?

MEMBER [btype].[b] AS 
    Sum
    (
      IIF
      (
        '${param}' = 'All'
       ,[btype].[type].MEMBERS
       ,[btype].[type].[${param}]
      )
    ,[Measures].[qty]
    )
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • my first approach was to calculate it explicitly only .. which din't work out .. then I tried it to do explicitly using aggregate .. which has reduced the frequency of exception occuring . . but still not able to get rid of this –  Apr 23 '15 at 05:22
  • ....and using `Sum` rather than `Aggregate` - they are not the same: and Aggregate can sometimes behave quite surprisingly. Please try swapping to `Sum` to test if `Aggregate` is your problem. – whytheq Apr 23 '15 at 06:45
  • I considered Sum as recursively adding the [Measures].[qty] for each member of [btype].. which might increase the iterations and degrade the performance . and throwing the exception randomly,, So I replaced it with Aggreagate which applies the default aggregation for the measure on specified members impliciltly.. and along with that I also declared degenerate dimension ([btype]) members as unique members in the cube definition.. these two changes has remarkably reduced the frequency of exception .. and also I have verified the results –  Apr 23 '15 at 07:10