Non-Empty Fails with Degenerate Dim in Aggregate

[sonalb]

Hello All,

I've created an aggregate table with a collapsed time dimension, and several lost dimensions. I have retained several degenerate dimensions as-is from the fact table.

Now, I've created an Analysis View with these degenerate dimensions. I have selected the "Suppress Empty Rows / Columns". It works fine as long as I'm at the "All" Members level ... but the minute I drilldown the system locks-up.

I checked the database and found that the underlying database query is trying to run a lookup of the aggregate table vs. the fact table !

I have checked and found the following things:

1.) It works fine if I remove the "NON EMPTY" clause from the MDX or by removing the "Suppress Empty Rows / Columns" option.

2.) I made the degenerate dimension as "NOT NULL" in the database ... still locks up (I did flush the OLAP cache too).

3.) This problem only comes with degenerate dimensions.

4.) I have indexes on all the degenerate dimensions in the aggregate table ... doesn't seem to help.

 

Is there something wrong with my setup ... or could it be a Mondrian issue ?

I'm using the MySQL database. I have attached the schema, the MDX, and the bad database SQL.
 

Would appreciate any pointers from the experts.

 

Thanks,

Sonal.

[swood]

This could be a Mondrian bug. I know there has been some work done in Mondrian lately around this, but it is not in Mondrian 3.0.4 that is in JS 3.5.

 

Can you show the SQL that is generated when you are not using NON EMPTY in the MDX query?

 

You have no aggregate definitions in your schema. Are you using the automated aggregate discovery process of Mondrian? If not, please show that part tof the schema? Can you show the aggregate table definition?

 

Sherman

Jaspersoft

[sonalb]

Hi,

@Sherman: Thanks for your response.

I have attached the SQL when the "NON EMPTY" clause is not used.

I am indeed using the automated aggregate discovery process of Mondrian. I have also attached the aggregate table definition, as well as the indexes in the aggregate table.

Would appreciate your thoughts. Would an explicit aggregate definition in the schema help ?

Thanks & Regards,

Sonal.

[swood]

It looks like the aggregate table is being picked up, but it is being used incorrectly in the NON EMPTY case. The lack of a join between the fact and aggregate table in the SQL is a killer.

 

I don't have experience with the way NON EMPTY effects the SQL generation relating to aggregates. In this case, it looks like a bug. What version of JS are you using?

 

 

Sherman

Jaspersoft