Prompt take a long time to display in Webi

[Former Member]

Post Author: achin1.jain@ge.com

CA Forum: WebIntelligence Reporting

Hi All

I have few reports in BO 6.5. These reports are published in the repository. When the users are trying to view the reports, the prompt page take aroung 6-7 minutes to display. Please help....

Regards

Achin Jain

[Former Member]

Post Author: James MacDonald

CA Forum: WebIntelligence Reporting

If it takes a while for the prompt screen to show up - then it is probably because the prompts are refreshing the list, and the query is taking a long time to run.

Here are a couple of points:

If the LOVs are set to 'Automatic Refresh Before Use' then the LOV is refreshing each time. Check in the universe to see how long the refresh of each LOV takes. If the LOV is sourced from a fact table (say 1MM rows) consider creating a code table that just holds the distinct values (say 100 or so) and source the LOV from there. Also ensure the LOV query is using indexes etc. Also, review if a LOV is really needed. If the list just contains dates or meaningless codes that the user already knows - can they just enter them?

The the LOV is static (ie not being refreshed each time) then how many items are included in the list. If the list has thousands or items it will take time to load - and is maybe of little value.

Another way to debug this is to remove the prompts one at a time and see which one is causing the problem. Then concentrate on that one.

[Former Member]

Post Author: jsanzone

CA Forum: WebIntelligence Reporting

Achin Jain,

I'm not an expert on the concept of "published in the repository" (is this like a "cached" mode?). The other mode, "view the reports", assumes to me as the user is running the report "live" and that is where a noticeable slowdown is being noted by you. I too have experienced the latter situation and have come to realize that the problem could be the way that the universe is set. For instance, we use a lot of columns from the datamart that are coded and in order for the user to get a clearer description of the data in a report we must join the coded data to a reference table (or lookup table). In some cases, we want to present a prompt that actually uses the coded values (like for gender we have 'M' and 'F' coded, which is pretty self-explanatory). However, in the report production we might want to be more explanatory (or more flowery) so we'll have the report display 'Male' and 'Female' as appropriate via the lookup table. The problem with using the coded values as prompt items from the datamart is that when building the prompt values on the fly, BusObjects will perform a "select distinct gender from table1", and if there are ten million rows in the table, guess what, about a minute (or more) wait time will ensue. On the other hand, consider that at a minimum the columns in your lookup table will have an "ID" and "Description", and the ID column has 'M' and the Description column has 'Male', etc, etc, and, you use the ID column as the basis for your prompt versus the Gender column from your datamart, then the "select distinct ID from Lookup_Gender" will take a very short time because there are fewer rows in the lookup table than there are in the datamart. So, as you move forward with building prompts for your users, you'll want to consider how the universe can be repositioned in order to speed up prompts.