Adding Prompts to workbook

[Former Member]

Hey All,

New to using SAP Analysis for Microsoft excel, so if the questions seem trivial, please bare with me.

I am trying to connect to a BW info-cube located in the production environment. I am able to connect successfully, but when I am trying to build a report off of it, I am constantly getting a

"Size limit of result set exceeded error"

I then have to filter and make sure I am only displaying a few rows for the data to actually show up in the work book. Any idea on how to fix this?

Also As the title suggests, How do we add Prompts/Variable to the workbook, so that when a person opens the workbook, he is prompted to optionally select some values before running the report.

I know if we connect to a Bex Query with prompts, those prompts show up. But is there anyway we can manually add prompts at the Analysis level after it is connected to a BW Infocube??

Thanks in advance and sorry if it is confusing to understand what I am trying to achieve

[Former Member]

Hi Siva,

here you can find all the information available about size limits:

Analysis for Office Result Limit and Memory Consumption - Business Intelligence (BusinessObjects) - ...

Best regards,

Martin

[Former Member]

Hi all,

Using a query and prompts like Ingo already mentioned, will size down the workload on BW side.

Therefor you will have less stress on the BW server, your query will execute faster and the transferred data via network is less as well. On CLient side, Excel will be faster because rendering of Excel cells ( styles etc or whatever references or VBA macros you may use will be afster as well.

On BW server the big advantage is that queries will be cached and can be precached for max. performance by your BW crew. As well it is possible in transaction RSRT ==> Properties to enhance the query as well for optimization mode.

Default setting is optimized after generation ( after saviing changes to the query via BEx query builder or via generate button in RSRT). Here you can sometimes gain performance ( depending to your data load behavior and data distribution, to set up the queryoptimizer to "Query Optimization with individual period in" e.g. days to have the queryoptimizer run all 14 days.

Using the direct approach to Infoprovider you will not take advantage of most of thoe effects.

Joerg

[former_member193885]

For size limit exceeded you could refer to the note below.

http://service.sap.com/sap/support/notes/1656983

Depending on your installation, OS version (32 bit vs 4 bit) etc. you could change that parameter.  Even if you set it to a very high number if you workstation does not have resource AO will not be able to bring back the result set.

[TammyPowlas]

If you read the SAP Help, http://help.sap.com/businessobject/product_guides/AMS11/en/11_aaoffice_user_en.pdf prompts rely on the BEx query

Is there a reason why you are reporting directly against the InfoProvider?  At all the SAP conferences SAP says this is not a best practice; the best practice is to report against a BEx query where you define the variables that become prompts.

[Former Member]

To be honest I haven't looked into the best practices. I have just started using the tool so I am still new to it.

My thought process was, if its a replacement or a better version of BEx, I figured skipping BEx and connecting directly to a infoprovider using Analysis would be ideal and would help eliminated the additional BEx layer.

Any idea as to why it is not a best practice?

Thanks for the help!!

[IngoH]

Hello Siva,

Analysis Office is the replacement for BEx Analyzer, not for BEx query designer. If you access the InfoProvider level directly, you would lose out on all the elements that you create in the BEx query layer (restricted key figures, calculated key figures, variables, structures, ...).

In addition you can't use Authorization Variables when accessing the InfoProvider level and therefore the data would not be filtered based on your BI Authorizations anymore.

so yes, best practices is to use the BEx query layer.

regards

Ingo Hilgefort, SAP

[TammyPowlas]

Prompts from the BEx query will limit the amount of data coming to your workbook and will improve speed; plus you can reuse the calculated key figures / restricted key figures

Additionally the use of BEx authorization variables from the query is most important

I recommend searching SCN as SAP's Ingo Hilgefort has discussed this several times.