Analysis Excel 2010 - Size limit of resultset exceeded

[patrickbachmann]

Hi, I have recreated the equivalent of a very slow SAP Infoset query in HANA using an Analytical View.  I have started testing with small result sets in both SAP and HANA, then compare the runtimes and record count.  Things have been great however I am now up to about 250,000 records in SAP which takes about 2.5 hours to run.  When I run in HANA with this many records, using Analyzer for Excel 2010 I am getting the error 'size limit of resultset exceeded'. 

1) I thought the limit for excel was now a million records?

2) When I take the SAP spool file and save it as a spreadsheet I can successfully open it with all 250,000 records.  So why can't I display that many records via HANA and Analyzer for Excel?

Thanks!

[Former Member · Accepted solution]

Hi Patrick,

My first reaction was "why the heck would anybody want to look at 250,000 rows in Excel???". After all, the charme of Analysis for Office with HANA is that it let's you aggregate your numbers across any dimensions pretty much in real time. Downloading all the raw data from HANA into Excel pretty much defeats that purpose.

But then I got more into the technical question behind it - why this weird limitation? I checked the Admin Guide on help.sap.com, and I found this:

HKEY_CURRENT_USER\Software\SAP\AdvancedAnalysis\Settings\DataSource:

This setting defines the maximum number of crosstab cells that are loaded from the server for one data source. If a data source contains data for more cells than defined here, a message is displayed.

By default, this setting is empty and the maximum number of cells is 500000.

If you set the parameter to a specific number greater or equal than 0, you define the maximum number of cells with this value.

Now I assume your spreadsheet has two columns, that would explain the 250,000 rows - 2 * 250,000 = 500,000

Try to change this parameter with regedit and check things change...

cheers

--Juergen

[Former Member]

Hi Patrick,

it helps to know your data and how it is rolled up. i have tested Excel pivots with over 170M records and was able to 'fit' them into one single pivot instance. i would say more than 50 numerical values in a column if not less renders any tool unusable whether they are on a piece of paper, on an adding tape, in spreadsheet or in any static report.

million records or 1,048,576 limitation came in handy with running a visual basic script to convert ECC table "packets" into uploadable csv files, so until ECC is hosted on HANA, Excel (and a bit of VBA) seems to be the tool to work around with for the load and presentation issues. let's call it a "poor man's option".

[patrickbachmann]

Hi guys, I agree with you both about the value of returning so many rows.  I was only trying to do benchmarking of performance comparing runtime of HANA report using Excel Analysis versus an SAP infoset query.  Basically I was trying to make the query in SAP take longer and longer to simulate the users real life experience of almost 4 hours runtime each day.  I had assumed adding more records would make it run longer and longer but I was able to find out what variants the user was using in SAP and it turns out (interestingly) with 250,000 result records it was loading fast in SAP but with just 193 records was running very slow.  So it wasn't the amount of records returned that was affecting runtime but which fields they were selecting in SAP.  HANA on the other hand did seem to show a correlation between number of records and runtime.  Anyway I gave up on the 250,000 scenario in HANA but I want to thank you for both of your helpful answers.

-Patrick

[Former Member]

Patrick,

Recently we deployed this analysis tool, I would like to increase the excel limit, as currently we are using 2007.

As per the guide I tried to change regedit entry on my machine and somehow I am not able to locate the below folder under KEY_CURRENT_USER\Software\SAP\

HKEY_CURRENT_USER\Software\SAP\AdvancedAnalysis\Settings\DataSource:

Please advise how to increase the record count.

Thank you.

[Former Member]

Hi Patrick and Juergen

We have same issue with the this and I'm not able to find this registry entry on Windows 7 Pro, Analysis for Office v14.2.2625 on MS Office Plus 2013

Are you able to help,

Kind regards,

Alen

[Former Member]

Hi All,

Following should solve the problem:

On local PC set following parameters: run REGEDIT

Find:

HKEY_LOCAL_MACHINE\Software\SAP\AdvanceAnalysis\Settings\DataSource

"ShowBicsSample"="True"

"ResultSetSizeLimit"="-1"

If running in Windows 7

Find:

HKEY_LOCAL_MACHINE\Software\Wow643Node\SAP\AdvanceAnalysis\Settings\DataSource

"ShowBicsSample"="True"

"ResultSetSizeLimit"="-1"

Gustavo

http://scn.sap.com/thread/3196199