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

Post bug reports and the status of reported bugs
Post Reply
User avatar
Alan Kirk
Site Admin
Posts: 5657
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

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

Post by Alan Kirk » 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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

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

Post by RJ! » Fri Aug 21, 2015 6:49 am

Dynamic or static subsets?

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

User avatar
Alan Kirk
Site Admin
Posts: 5657
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

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

Post by Alan Kirk » Sun Aug 23, 2015 3:16 am

RJ! wrote: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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
Alan Kirk
Site Admin
Posts: 5657
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

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

Post by Alan Kirk » Wed Aug 26, 2015 11:21 pm

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.)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

dmillerksu
Posts: 4
Joined: Sun Apr 17, 2011 2:01 pm
OLAP Product: Cognos TM1
Version: 10.1.1
Excel Version: 14

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

Post by dmillerksu » Wed Jan 13, 2016 3:43 pm

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
Posts: 4
Joined: Sun Apr 17, 2011 2:01 pm
OLAP Product: Cognos TM1
Version: 10.1.1
Excel Version: 14

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

Post by dmillerksu » Wed Jan 13, 2016 4:17 pm

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
Posts: 10
Joined: Wed May 25, 2011 12:39 pm
OLAP Product: TM1
Version: 1022-FP*
Excel Version: 2010-2016

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

Post by silw » Wed May 04, 2016 8:58 am

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

User avatar
George Regateiro
MVP
Posts: 326
Joined: Fri May 16, 2008 3:35 pm
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 SP3
Location: Tampa FL USA

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

Post by George Regateiro » Wed May 04, 2016 12:54 pm

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
Community Contributor
Posts: 307
Joined: Wed Nov 03, 2010 9:16 pm
OLAP Product: tm1
Version: 9 5 1 - 10 2 2 - 10.3 PA
Excel Version: From 2007 to 2013
Location: Earth

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

Post by mvaspal » Wed Feb 08, 2017 7:19 pm

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

Post Reply