Excel =SUBNM Limitations

Post Reply
Posts: 26
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Excel Version: Office 365 x32

Excel =SUBNM Limitations

Post by Adam » Mon Jan 04, 2021 8:50 pm


I was going to ask the question is there a limit to the number of items that can be shown on an Excel SUBNM list. However, I just figured, why don't I try instead of ask.

I pointed SUBNM against the longest dimension I have, in this case it's }LineNumber. :D
First good news is that it displays all 10,000 items. The second good news is I don't believe my longest dimension will get to 10,000 items. (Famous last words?)

Only question I now have left, any performance hits in Excel anyone's seen with pointing SUBNM against such a long dimension? I'm using PA for Excel.
Take care.

User avatar
jim wood
Site Admin
Posts: 3834
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA

Re: Excel =SUBNM Limitations

Post by jim wood » Mon Jan 04, 2021 10:38 pm

If you're combining the SubNM with other large amounts of formulas you may hit some performance issues down to just volume. I've only ever tried doing this kind of thing with old web forms rather than the newer stuff though.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
Posts: 866
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL; PAoC
Excel Version: Office 365 64-bit
Location: JHB, South Africa

Re: Excel =SUBNM Limitations

Post by gtonkin » Tue Jan 05, 2021 5:51 am

When using PAfE, the SUBNM function is rendered as a data validation. The data validation uses a range like "cafe_validation_temp" (hidden named range).
This range is located on the very hidden Cognos_Office_Connection_Cache sheet and values start at row 2.

I guess you will run out of options when you have more than 1,048,575 elements in your subset as there are not enough rows to hold these elements for the data validation.

But like Jim points out, performance will probably be horrendous way before this kind of volume. Also note that the values in the hidden sheet are over-written each time you go to another SUBNM - there is obviously overhead in clearing out what is there, getting the list of elements related to this SUBNM, updating the named range etc. etc. This is also why you notice a lag when navigating and activating a cell with a SUBNM formula.

Post Reply