TM1 Forum

If I have a cube with more than 17.000 rows and want to visualize it using the In Spread Sheet Browser option, it only appears around 16.000 rows. Is there any parameter available to let the user visualize all the rows the cube has?

Are you using the spreadsheet browser or Active forms?

It happens when you insert into a blank sheet a cube using the In Spread Sheet Browser option and with the active form. The only way to visualize more than 16.000 rows is using the slice export option.

I think it could be because of the Stargate views. Try adding the DisableWorksheetView switch to your TM1S.CFG file as follows:
You will have to restart the TM1 Server after to apply this switch.

Olga:

If Slicing from the Cube Viewer works properly, but doing something similar from the In-Spreadsheet Browser or Active Forms does not, then that, to me, might indicate a bug that needs to be reported to IBM. That 16-17,000 cut-off range is an interesting one, since much older versions of Excel (Excel 7/95 and earlier, maybe?) were limited to this many rows and at certain points in the history of the TM1 Excel client (going back to Excel 5, I think) it had that row limit hardcoded in some places. It's possible they still do have it hardcoded in the cases you mention, or that they've made a deliberate cutoff in Active forms/ISB for some reason (which they should document).

Also, be advised that the ISB is something that's been around for years, but which has not gotten much product development attention, despite having some issues. I haven't used it in ages, so others on the forum can probably go into more detail on those issues. I'm surprised it's still there, to be honest, since it seemed like something they might get rid of at some stage. In other words, you might not want to rely on it too heavily or get too attached, particularly if you're seeing issues like this - I'd recommend the Cube Viewer for everyday cube browsing over the ISB.

rkaif wrote:I think it could be because of the Stargate views. Try adding the DisableWorksheetView switch to your TM1S.CFG file as follows:

Code: Select all

DisableWorksheetView=T

You will have to restart the TM1 Server after to apply this switch.

Rizwan:

I'm curious what makes you think Stargate views would be the culprit? This switch, to my knowledge, simply determines whether or not TM1 tries to generate a Stargate view to help grab the values being asked for in a workbook in "wholesale" rather than one-by-one from the cube. It works closely with the VIEW TM1 worksheet function, which helps TM1 Perspectives know more about the Stargate view it needs to create to support the workbook DBRW requests. That's a really rough description of the functionality, but as far as I know Stargate views, like your typical TM1 view, have no limitations on # of rows, columns etc (beyond what your TM1 server RAM limits allow).

In general, this is a very good thing to have enabled, so before Olga goes and disables this for all users on the TM1 server it might be helpful to know if you have some specific experience regarding this option and the # of rows returned in an Active Forms/ISB slice or if you're just taking a shot in the dark. A slice from the Cube Viewer apparently shows more than 16K rows when Olga uses that option, and that is almost certainly using the same Stargate View functionality.

Sorry to nitpick, but changing the setting as you suggest could have a very negative impact on overall TM1 Excel Workbook recalc performance - at least you should stress how important it is to try this sort of thing in a Development/Test environment first if you're just throwing out guesses. Or, if you know that disabling the worksheet VIEW functionality is a solution to this problem then please let us know more about that as a known known issue, since it would be news to many of us.

There were some issues with the VIEW function/Stargate views way back in version 7 of TM1, but they seemed to fix most of those starting with v8 of TM1, and I haven't seen too much reason to disable it via this CFG parameter since then - in fact, enabling it again for a few customers who previously had it disabled ended up providing huge Excel recalc performance boosts for some models.

Regards,
Mike

Mike Cowie wrote: If Slicing from the Cube Viewer works properly, but doing something similar from the In-Spreadsheet Browser or Active Forms does not, then that, to me, might indicate a bug that needs to be reported to IBM. That 16-17,000 cut-off range is an interesting one, since much older versions of Excel (Excel 7/95 and earlier, maybe?) were limited to this many rows

Yes, it was Excel for Windows 95, which had a limit of 16,384. My first version, in fact; you always remember your first. Excel 97 introduced 65,536 as the limit. For a time .xdi (and, at the time, .ldi, remember them?) sheets had the old limit as well, even if you were using them in Excel 97. (I can't recall which TM1 version that was fixed in.)

Mike Cowie wrote:and at certain points in the history of the TM1 Excel client (going back to Excel 5, I think) it had that row limit hardcoded in some places. It's possible they still do have it hardcoded in the cases you mention, or that they've made a deliberate cutoff in Active forms/ISB for some reason (which they should document).

I think that the 65,536 limit still applied to some features when you were using TM1 in Excel 2007 though I can't find a reference to that in the release notes. However the ISB shouldn't be limited to the old 16,384 limit; indeed I'm looking at an ISB right now that runs to row 60,844. And that's in version 9.0 SP3 U9.

This makes me suspect that perhaps the difference is zero suppression, or perhaps it just not having as many rows of data as was thought, or perhaps unwittingly using different subsets for the rows. I'd suggest doing a side by side comparison between a slice and the output from an ISB to determine exactly where the variations are.

Mike Cowie wrote:Also, be advised that the ISB is something that's been around for years, but which has not gotten much product development attention, despite having some issues. I haven't used it in ages, so others on the forum can probably go into more detail on those issues.

Agreed; it's a flake-fest. I used to use it for some reconciliation workbooks but it was always random chance whether the control would be broken or not when I opened the workbook. It ended up being more trouble than it was worth. There are some who still like it, though; Paul Simon has written that he still uses it, but the only advantages that ISBs usually have over active forms that I can see is that they allow you to expand and collapse columns as well as rows, and to drag and drop dimensions between rows, columns and titles as needed.

Mike Cowie wrote:I'm surprised it's still there, to be honest, since it seemed like something they might get rid of at some stage.

That was the plan; it was supposed to be gone after 9.4. For some reason they gave it a reprieve in 9.5 and haven't put it back on the death watch. If I had to guess it was probably because of the two things that it can do that Active Forms can't (at this time).

Mike Cowie wrote:In other words, you might not want to rely on it too heavily or get too attached, particularly if you're seeing issues like this - I'd recommend the Cube Viewer for everyday cube browsing over the ISB.

If the columns are fixed and the distribution of dimensions between titles, columns and rows static, I'd agree completely.

but the only advantages that ISBs usually have over active forms that I can see is that they allow you to expand and collapse columns as well as rows, and to drag and drop dimensions between rows, columns and titles as needed.

I'm a fan as much as you can be with its quirks and potential limited lifespan. Other reasons I like it are,

• - less intimidating when introducing new users to TM1 than the grey cube viewer.
  - Copy and paste functionality
  - Navigation for end users. Can have viewer and month reports on sheets beside each other without having to hunt it down thru perspectives.
  - Flexability of VBA
  - Faster than Activeform

I've just finished creating a new model with ISB for our accountants to analyse the monthly results with drill thru. While the drill thru is great in cube viewer is great to get to transactions, our company sells an invoice scanning service and wanted to further drill into the physical scanned invoice (PDF). Something I couldnt do in the cube viewer and wanted to retain the flexability of moving columns etc.

Yes it still has its fair share of quirks and bugs, but the end result is nice and I think a two stage drill thru ( OLAP -> transactional -> document) will become more common request in the near future.

Steve

Alan Kirk wrote:

Mike Cowie wrote:I'm surprised it's still there, to be honest, since it seemed like something they might get rid of at some stage.

That was the plan; it was supposed to be gone after 9.4. For some reason they gave it a reprieve in 9.5 and haven't put it back on the death watch. If I had to guess it was probably because of the two things that it can do that Active Forms can't (at this time).

The explaination I heard from what I would assume is a reliable source is that internally IBM are replacing many legacy Essbase reporting tools with TM1 and Essbase has an in Excel cube browser that looks and feels remarkably similar to the TM1 ISB. Since this was a feature the users were familiar with and liked they kicked up a stink when told it would no longer be there. Hence the reversal of the end of life decision.

lotsaram wrote:The explaination I heard from what I would assume is a reliable source is that internally IBM are replacing many legacy Essbase reporting tools with TM1 and Essbase has an in Excel cube browser that looks and feels remarkably similar to the TM1 ISB. Since this was a feature the users were familiar with and liked they kicked up a stink when told it would no longer be there. Hence the reversal of the end of life decision.

Lotsaram:
Very interesting to know - maybe I'll revisit/reconsider using it again. I actually thought Dynamic Slices felt similar/closer to the Essbase cube browser (which I've only ever used on rare occasion), but I am certainly not asking for dynamic slices to come back! I wonder if they'll give the ISB any (much needed) attention/enhancement. The TM1 Excel client, in general, rarely seems to undergo much in the way of enhancements...

This makes me suspect that perhaps the difference is zero suppression, or perhaps it just not having as many rows of data as was thought, or perhaps unwittingly using different subsets for the rows. I'd suggest doing a side by side comparison between a slice and the output from an ISB to determine exactly where the variations are.

Alan:
Good point - that would probably be the simplest explanation - maybe there's some Private/Public subset interference happening, too (private Default overriding a public Default subset, for example, which would not be obvious).

The reason I was thinking of hard row cutoffs buried in some code was that I (probably mistakenly) got the impression that the OP was seeing this abrupt cutoff after taking a slice from some of the cube browsing tools (with the Cube Viewer not having this problem), but looking back again that may not be the case.

Regards,
Mike