Parameters of a document taking long time in BI 4.2 sp2 patch 3

[Former Member]

Yes, I know this exact issue has been asked in the past, in this thread:

Parameters of a document taking long time in BI 4.2 sp2 patch 3

Problem is, this issue is still alive and kicking, and I can't add a comment to that Archived thread.

The Solution which was given was to append "?lovInfo=false" to the URL, eg:

http://YOUR_SERVER_NAME:6405/biprws/raylight/v1/documents/11729/parameters?lovInfo=false

I have done this, the REST service does now return much less data (about 2Kb, rather than 300Kb) but it still takes about 3 minutes to call this service.

I've tried calling this, asking for JSON or XML as a result... either way, it takes a really long time to return the results.

Has anyone else seen this behavior ?

[Former Member]

As a workaround, I tried to get the BO4 REST services to run some SQL to retrieve the report parameters.

In BO3, the following SQL worked fine. But in BO4, it returns a "Not a valid query." error.

SELECT SI_PROCESSINFO.SI_WEBI_PROMPTS.* 
FROM CI_INFOOBJECTS 
WHERE SI_ID = 44582

Is there a BO4 version of this SELECT command ?

If I try running this SQL using the BO4 "cmsquery" REST service, it throws an error, and returns this JSON:

error_code: "RWS 00070"
message: "Internal server error. (RWS 00070)"

Interestingly, the next SQL command does run okay, and gives details about instances of a particular report... but it doesn't show the Parameter Names & Types (eg "text" or "numeric"). Does anyone know how to get this information for a particular report?

SELECT *
FROM CI_INFOOBJECTS 
WHERE SI_ID = 44582

[Former Member]

I doubt anyone else will hit this problem, but if they do, I found the cause.

When I opened one of these reports which were taking a long time to run the "parameters" REST web service, it opened straight away, but I realised that our users had added an "Advanced parameters" dialog, which painfully went off to an external database to load list of values.

This dialog would take 4-5 minutes to appear.. and, of course, when I call the "parameters" REST service, it was also calling this dialog to load the values (even though, actually, all I wanted was a list of the basic parameter names and types).

So, if you do get this issue, check what happens if you open the report and click on the Advanced button.

Meanwhile, I'm still stuck on this problem, as I want to get just the "basic" list of parameter names, the same list which is returned when I run this SQL. Does anyone know how to do this ?

SELECT *
FROM CI_INFOOBJECTS 
WHERE SI_ID = 44582