Question: In XI R2, when I refresh Web Intelligence or Desktop Intelligence documents, that contain prompts with Lists of Values, I experience, long, sometimes unacceptable wait times until the prompts are displayed. What is the cause for this?
Answer: The cause for this problem is the fact that the Lists of Values (LOV) are not persisted on the server. It means that once the cache for that particular LOV expires, the list must be repopulated against the database. And each session has a separate cache, which in turn means each user will cause the list to be refreshed each time they use it.
The default technology in BusinessObjects Universes consists in extracting distinct values from whatever the object SQL is. If the object is based on a very large fact table, it may mean that millions of rows have to be scanned for each use of the particular LOV.
There are two possible solutions to alleviate this problem. Both of them rely on using a smaller table to feed the LOV.
Use a Lookup Table
If there is a lookup table containing those values, customize the LOV to use that table instead. E.g. if you are dealing with a country code, you may expect less than 200 codes total (there are not that many countries in the world). A default LOV will scan all rows from the fact table every time it needs to be populated. Instead, create the LOV as a query on the Country table, only the much smaller number of rows will be scanned. To achieve that you will need to:
- Create an object based on the column you want to use.
- Modify the original object’s LOV to use that object instead.
- Possibly hide the “LOV object” once you are done.
- Shows the table relationship.
- Create an object that feeds off the lookup table.
- This is the object to modify.
- Make sure the LOV will be exported with the Universe and…
- …edit it, changing the query to use the object in #2.
- You may hide the “for LOV” object if you wish.
Create and ETL (Extract, Transform, Load) a Dedicated Table
This method is essentially the same, but we do not have an available lookup table. You will have to create a table along with the necessary loading script to extract the distinct values from the source (fact) table.
It will be necessary to ensure there is a scheduled refresh of the table in order to keep it up-to-date. This scheduled refresh should be part of normal operations procedures, as it would not be handled by BusinessObjects.