[SOLVED] Pivot4j/Olap4j adding condition to where clause of generated query

Pivot4j/Olap4j adding condition to where clause of generated query

I have a ROLAP cube - Pivot4j 0.9, Olap4j 1.1.0, MySQL.

When I run the following MDX I can see an additional condition in the where clause which causes the resulting statement to return no rows.

SELECT Hierarchize({[Measures].[Unique Users]}) ON COLUMNS,  NON EMPTY Hierarchize([dimBrowserType].AllMembers) ON ROWS  FROM [Sessions]

Resulting SQL...

17:28:08,536 DEBUG RolapUtil:319 - Segment.load: done executing sql [
select `meta_date`.`date_day` as `c0`, count(distinct `sessions_551d03b3e4b0dc830608a95b`.`userId`) as `m0` 
from `meta_date` as `meta_date`, `sessions_551d03b3e4b0dc830608a95b` as `sessions_551d03b3e4b0dc830608a95b` 
where `sessions_551d03b3e4b0dc830608a95b`.`daykey` = `meta_date`.`daykey` 
and `meta_date`.`date_day` = '2015-01-01' 
group by `meta_date`.`date_day`
], exec+fetch 44 ms, 0 rows, ex=7, close=7, open=[]

Olap4j has added this clause...

and `meta_date`.`date_day` = '2015-01-01'

...which does not relate to anything in the intial MDX. There is no data for this particular value of date_day so the query returns no data.

What might be causing this?

Solution

You probably don't have an 'all member' on the date hierarchy. The MDX specifications say that in this case, the first member is used.

Try adding hasAll=true to your hierarchy.