83265,999,616 CAFE Not Returning All Rows Without Warning
Posted: Wed Aug 12, 2015 2:28 am
Has anyone come across this gem? (No comments about the cube size, please. You know about the cube size, I know about the cube size, the person who built the cube knows about the cube size. But it's what was asked for, and if it's allowed by the specs, then it's supposed to work. ) Obviously I'm not including the attachment but it shouldn't be needed to see what the problem is. This is obviously on our 64 bit 10.2.2 system, not the soon to be upgraded 9.5.2 one:
===============================
We have a situation where CAFE will fail to return all rows when a large dimension is added to the rows area. Note that the query itself does not return a disproportionate number of rows; with zero suppression on the total number of rows is less than 260.
The cube has 33 dimensions, the names and sizes of which are shown in the attached workbook.
The workbook also contains static CAFE crosstabs, and the corresponding Perspectives snapshot. The values have been randomised for obvious reasons, and only show where values appeared, not what those values are. Let's take it as read that I've cross-checked the values and have confirmed that the values returned by both CAFE and Perspectives are the same.
We begin with a predefined public view which has only a single row dimension (XXX), using a named subset consisting of 9 elements. (Original /OriginalSnapshot). This renders correctly on both.
We then add a second row dimension (XXXX) with an unsaved subset of 12 members. This results in 26 rows being returned through both interfaces.
We then add a third rows dimension (XXXXX) which has an unsaved subset of 1,833 members, and this is where things start to go wrong.
Perspectives correctly returns 254 records. CAFE returns only 102, stopping part way through the list without any warning.
We then add a fourth rows dimension (XXXXXX), an unnamed subset of only 4 consolidated elements, only one of which actually has data.
Perspectives still correctly returns 254 rows. CAFE drops to only 85 rows.
The only thing I noticed is that in both cases it does seem to stop at the end of a particular "group" of elements.
For example after adding the third dimension it returns all of the ZZZZZZZ rows for ZZZZZZ before stopping.
After adding the fourth dimension it returns all of the ZZZZZ rows for ZZZZ before stopping. In both cases the break does seem to be at a point where the data transitions between one second dimension element to another.
After this failure was noted with the default CAFE settings the Member display count limit was bumped up to 10000, the data display row limit to 50000, the expand member limit to 20000, the grouping option to Repeat Labels, the data object cell limit to 1000 and the processing time to 100000. None of these, individually or collectively, made any difference to the results.
===============================
We have a situation where CAFE will fail to return all rows when a large dimension is added to the rows area. Note that the query itself does not return a disproportionate number of rows; with zero suppression on the total number of rows is less than 260.
The cube has 33 dimensions, the names and sizes of which are shown in the attached workbook.
The workbook also contains static CAFE crosstabs, and the corresponding Perspectives snapshot. The values have been randomised for obvious reasons, and only show where values appeared, not what those values are. Let's take it as read that I've cross-checked the values and have confirmed that the values returned by both CAFE and Perspectives are the same.
We begin with a predefined public view which has only a single row dimension (XXX), using a named subset consisting of 9 elements. (Original /OriginalSnapshot). This renders correctly on both.
We then add a second row dimension (XXXX) with an unsaved subset of 12 members. This results in 26 rows being returned through both interfaces.
We then add a third rows dimension (XXXXX) which has an unsaved subset of 1,833 members, and this is where things start to go wrong.
Perspectives correctly returns 254 records. CAFE returns only 102, stopping part way through the list without any warning.
We then add a fourth rows dimension (XXXXXX), an unnamed subset of only 4 consolidated elements, only one of which actually has data.
Perspectives still correctly returns 254 rows. CAFE drops to only 85 rows.
The only thing I noticed is that in both cases it does seem to stop at the end of a particular "group" of elements.
For example after adding the third dimension it returns all of the ZZZZZZZ rows for ZZZZZZ before stopping.
After adding the fourth dimension it returns all of the ZZZZZ rows for ZZZZ before stopping. In both cases the break does seem to be at a point where the data transitions between one second dimension element to another.
After this failure was noted with the default CAFE settings the Member display count limit was bumped up to 10000, the data display row limit to 50000, the expand member limit to 20000, the grouping option to Repeat Labels, the data object cell limit to 1000 and the processing time to 100000. None of these, individually or collectively, made any difference to the results.