crosstab/cube limits?

Birt 2.3.2. I have a crosstab problem I haven't seen before - I seem to be hitting some kind of limit after which some crosstab data is discarded w/o error of any kind. Seems to have more to do with the number of cells (or rows and columns) in the crosstab, versus the Data Set size. If I have a very large result set, but filter enough rows out in the Cube filter, all my data is displayed. I don't really have a handle on the threshold because it's not like it is always displaying a fixed limit amount of data... more like it's always displaying the modulus of "the limit". In other words, if the number of cells (or rows and columns) goes over "the limit" the data is drastically truncated.

We're not using the viewer, but I understand there are a number of web.xml settings for it. So, my question is, what kind of limits might I be hitting and how do I control them? Could the viewer web.xml params such as BIRT_VIEWER_MAX_CUBE_ROWLEVELS be limiting me even though we're not using the viewer?

Find more posts tagged with

Comments

mwilliams

So, you're not noticing a limit on the number of rows/cells in the crosstab? It just seems random?

jsherriff

Michael,
Can't quite tell if it's cube size or number of rows/columns - only that I don't see all the data I expect to see unless I reduce the result set size via the query, or apply a cube filter. For example, if I filter for September 1 - 22, the crosstab displays only 11 rows and a small total. If I filter for one day less, the crosstab displays a little over 2000 rows and a much much larger total. But, because those are aggregated, I can't say with any certainty that that number is meaningful - as in there isn't necessarily a 1 : 1 between cube rows and crosstab rows.

By default, without a filter, there are quite a few rows being put in the cube (> 22000) and that results in a very small number of displayed rows also. I just discovered this issue last night so I'm just starting to dig in a try to see what the actual limits are without aggregated values in play. I've seen the viewer limits that can be configured in web.xml (which we have not configured) and though we're not using "the viewer", we are using the api (coreapi, etc, etc) so I'm wondering if we could be hitting one of those defaults somehow.

I'll try today to come up with a more organized test set so that I can perhaps see what the thresholds I'm hitting are.

Thanks

Joel

mwilliams

Is this from the designer or deployed in your application?

jsherriff

deployed

mwilliams

In the designer, under preferences, there is a limit for max number of values to display. The default in my preferences is 100. This obviously doesn't make much sense given that you have a crosstab with 2000 rows showing up, but there might definitely be a preference you can set for the engine.

jsherriff

Michael, no specific cause found yet. But, it does seem to be a cube size issue...though I'm surprised I haven't seen it before since I have some large data sets.

I removed all but the needed columns from the cube (the cube has a lot more columns than is being selected to be displayed in the crosstab) and it worked perfectly (I see the totals I expect). The (full) data is (only?) approximately 10Mb, which doesn't seem all that big so this is still puzzling.

mwilliams

You might try downloading a newer version of BIRT to try it in to see if there is the same issue. Maybe this is something that is improved in a newer version of BIRT?

jsherriff

Michael, sure enough, it's fixed in 2.5.2 (not 2.5.1 which I had tested). So I managed to find The fix amongst a bunch of other updates and port it back to 2.3.2...hope to update to 2.6 soon.

mwilliams

Great to hear! Thanks for the update!