83265,999,616 CAFE Not Returning All Rows Without Warning

[Alan Kirk]

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.

[RJ!]

Dynamic or static subsets?

I noticed some odd behaviour similar to this with Dynamic Subsets...

[Alan Kirk]

Dynamic or static subsets?

I noticed some odd behaviour similar to this with Dynamic Subsets...

Hi RJ,

No, all static subsets. I'm waiting on approval to send a scrambled version of it up to IBM for analysis.

Thanks anyway.

[Alan Kirk]

CAFE Support have confirmed that they can reproduce this and will be opening it as defect. No idea what the issue is yet, and as the fact that nobody else has reported the same issue would seem to support, it's not something that you can reproduce just by creating "any old large view". (Although that may be down to a relatively low takeup of CAFE so far as well, so there are probably fewer view samples out there than there would be in Perspectives.)

[dmillerksu]

I just experienced the same issue where Architect was showing 200+ rows, but CAFE capped it at 57. The row limit is currently set to 500. Did IBM or anyone else ever find a solution? I noticed the model I'm working on experiencing this uses UNDEFVAL. Alan - Do you know if your model had the same setting?

[dmillerksu]

I was able to build a view directly in CAFE, and got the correct result. But the Architect produced view continues to have issues.

[silw]

If you set 0 instead of 500, CAFE will return all rows

[George Regateiro]

I know this is an old thread but since it has gotten some chatter I will post up what I was told by support. The issue as we saw it was a combination of the row limit and zero suppression within Cafe. Essentially Cafe does not utilize the TM1 server for zero suppression, it does it on the client side. When you combine this with the Cafe row limit you can get weird results since it is only processing the first 500 rows of the non suppressed values returned by TM1.

So in practice if you turned off zero suppression in Perspectives and the first 500 rows were 0 and you had not changed the default row limit cafe would not return any data since it only sees the 0 values when it is row limited to 500. As pointed out the easy answer is to set the row limit to 0 but the order of operations within cafe seems backwards. If the view is zero suppress you should do that first and then apply the row limit, which is still not optimal since you can still miss data.

[mvaspal]

Hi
This has been fixed in 10.3, just tested after users complaining zero suppression not working correctly in 10.2.2
I tested with 10.2.2 FP4 and 6, neither worked, CAFE 10.3.0.1005 did work