Size Limit of Result Exceeded is a common issue with Analysis for Office.
Below blog already explains on how to avoid this issue, but this doesn’t work with Analysis for Office 2.2
Below steps will enable to you overcome the default limit of 500000.
1. Screenshot 001 below shows that the default limit is 500000.
2. Screenshot 002 clearly states that for 1.x versions of AO, we need to change it from registry but from 2.x onwards we can change it at file system level.
3. Screenshot 003 will shows how to navigate to the file system and check whether the settings are UserRoaming or PerMachine. If user UserRaoming, then we user can change it at user level. If PerMachine is maintained, it is applicable for all the users in the machine.
4. Screenshot 004 shows the Ao_app.config file in C:\ProgramData\SAP\Cof
Here parameter “ResultSetSizeLimit” is maintained as “UserRoaming”. This setting enables each user to have different settings on the same machine.
5. Screenshot 005 shows how to before and after of Ao_user_roaming.config file in Users\[UserID]\AppData\Roaming\SAP\Cof.
If the entry is not maintained, we need to insert a new line and add the entry. In this case there was no entry maintained.
Below is the link for Administrator Guide that I referred to
Please use this option carefully, as this can increase the load on db server.