...a potential solution, for once!
I've recently investigated an issue within all Brio (Hyperion) reports against the local web server (Brio ODS) seemingly running very slowly. On investigation, I found that the connection setup file (xxx.oce) contained some default parameters to pre-fetch and list all of the database tables, prior to launching any Queries.
What?
OK - a Report Designer may need to list the database Tables, from which to create a new Query on which to base a Report. They [i]need [/i] to pre-fetch the Tables, in order to use them.
(Business) Users should be presented with an EIS and shouldn't need access to the Query or Table sections at all, so their connection file should not include the pre-fetch parameters which are default set in an .oce file.
My suggestion to those of you who have found that, even [i]before [/i] you run a Query in a Report, the Report opens very slowly, is that you have pre-fetch parameters set in your .oce. If you are able, open the full client and investigate your "optional" components in your connection file (xxx.oce). You may have to dig around but there will be a section which details the pre-fetch parameters, which may look something like this, on the database side:
[color=blue]SELECT DISTINCT TABLE_NAME, :"SYS_B_0", OWNER, TABLE_TYPE, :"SYS_B_1"
FROM SYS.ALL_CATALOG
WHERE OWNER NOT IN (:"SYS_B_2",:"SYS_B_3",:"SYS_B_4")
AND TABLE_TYPE IN (:"SYS_B_5",:"SYS_B_6",:"SYS_B_7")[/color]
- Next, remove these pre-fetch parameters, save the file with a new name and distribute the revised version to your Business Users [b]only [/b] - keep the original version for your Report Designers.
You may not see a marked improvement until everyone is using the new connection file, since others will be stuffing up your database by still pre-fetching all of the Tables in your Schema but it will work if you replace the majority of Power Users who do not need to see/access the tree list of Tables in the Query section of a Report.
[color=red]Remember - always [b]TEST [/b] before you [b]IMPLEMENT[/b]!
[/color]
I hope this helps someone, at least!
Tony
