Long Loading Time for Prompts

[jeff_crisp]

I have some dynamic prompts in my universe.  When I publish the universe and attempt to generate some reports, the prompts take a considerable amount of time to pop up (20-30 seconds). 

I was wondering if anyone else has experienced this and\or what was done to cut down the prompt time?  For example, was a derived table used or something along those lines.

I am open to any ideas.

Let me know if you need any further detail and I will do my best to explain.

[Former Member]

Hi,

we are also facing the CUID Error.

We are on version SP6.1.

We have same content testet in reference environment and just deployed to production on saturday.

First tests are ok, but today the report application server starts having problems.

Has somebody found a solution?

Thanks,

Michael

[former_member190781]

Jeff, did you find the resolution of this issue?

We are also facing same kind of issue with BI 4.1 SP06, it started happening as soon as we upgraded the environment to SP06.

When you try to schedule a webi report and go to parameters there is a huge delay in populating prompt window and for most of the prompts when you select a parameter and click on apply it will freeze and does nothing.

I tried to set high level traces and found following.

in the CMS logs

assert failure: (.\sessionmgr.cpp:3536). (0 : CSessionMgr::ValidateApplicationCUID: Attempt to login when specifying an invalid application CUID session. The specified id is = ).

In WIPS logs:

C3_LocalizedObject:[LOC]:cdbLocalizedObjects::IsLocalePublished: sLocale: en_US; not found => Not-Published [cdbLocalizedObjects.cpp:663]

From webi rich client there is no issue, prompt pops up within few seconds and when you select and enter a parameter it accepts immediately and refreshes the report without any issues.

Tried following steps:

Tried to find if there is any missing dependencies for the servers.

Recreat SIA

Deleted existing WIPS and created new.

Tried creating new SIA.

Uninstalled all JRE versions from local machine and installed JRE 1.8.

I have raised an incident with SAP, it would be of great help if you can provide any inputs.

Thanks in advance.

[former_member190781]

Update:

I installed patch 3 on top of SP06 in sandbox environment and it seems the issue is not reproducible there. Still waiting for SAP to respond on the incident.

[jorge_sousa]

Hi Sohel,

Was it solved from your side?

We are facing the same issue with SP6 and Patch3 where we observe that in the CMS log we are getting many times the error:

assert failure: (.\sessionmgr.cpp:3536). (0 : CSessionMgr::ValidateApplicationCUID: Attempt to login when specifying an invalid application CUID session. The specified id is = ).

Thanks in advance

[former_member190781]

Hi Jorge,

For us Patch 3 fixed the issue.

Regards,

Sohel

[omacoder]

Jorge, do you remember what the fix was here? We did not apply any updates or patches, just rebooted the server and then this same behavior started occurring.

[former_member193452]

Hi Jeff,

You mentioned this is customer SQL in the Business Layer.

What are the data types in the prompts?

In the custom sql are Case statements or If then else statements used?

If you create a new simplified prompt, does this behave differently?

Is this a multisource or single source?

Is the custom SQL Ansi92 ?

[jeff_crisp]

Answers:

What are the data types in the prompts? Strings.

In the custom sql are Case statements or If then else statements used?  Its just selecting 1 column.  SELECT <coulmn> FROM <Table> WHERE id = @Variable(ID).  The table it is selecting from is very small and we even put a clustered index over the table to try and help.

If you create a new simplified prompt, does this behave differently?  I am not sure we can simplify it any further.

Is this a multisource or single source?  Multi source

Is the custom SQL Ansi92 ? Yes

[former_member193452]

Hi Jeff,

Can you check your patch level is one of the following:

https://service.sap.com/sap/support/notes/2049026

This is in reference to the incident you posted above.  This should be resolved.

And there is a work around to

Disable the Web Intelligence Monitoring service on the Adaptive Processing Server with the DSL Bridge.

The DSL Bridge is the service that is used to retrieve list of values.

Also, please check if you have Platform search set to continous and if so, change it to the timed mode.

Jacqueline

[jeff_crisp]

We are on 4.1 SP6

[jeff_crisp]

More Information.

In looking at logs, I am seeing the following error message every time a report is run:

**ERROR:C3_LocalizedObject:addlocalizedObject: invalid creation value : "<message>":1(add) instead of "ps<message>" (in map) ! [cdblocalizedobjects.cpp;808] kc3dsldataprovider.cpp:4193:long __cdecl cdbDSLDataProvider::RefreshNoCommit(void):

<message> = The message that was entered for the @prompt.

[Former Member]

So <message> is the prompt text from the universe?  Is the prompt defined in the data foundation or the in the business layer?

The "localized object" stuff in the error message makes me wonder: are you by any chance using Translation Manager?

[jeff_crisp]

That is correct.  <message> is the prompt text.  The prompt is defined at the business layer.

We are not using the Translation Manager.

Thanks

[Former Member]

Any difference if you recreate the prompt in the data foundation?

[jeff_crisp]

No difference.  What I am seeing on a regular basis is that when I click "Refresh" or "Run Query", it is taking roughly 10 seconds before it even hits the database to get the values for the prompt.

[Former Member]

Two other things to try, to see if the behavior changes:

1) Use the LOV without the prompt  (Attached to an object in the filter portion of the query)

2) Keep the prompt, but replace the sql LOV with a static value LOV

It might give you an idea whether the problem is coming from the LOV or from the prompt, if that makes sense...

[former_member185138]

Have you resolved the issue?

we are facing the same.

[jeff_crisp]

Not resolved yet.

[jeff_crisp]

We are on BO 4.1.

The prompts are based on custom SQL.  We have ran profiler on the server and seen the request and response be completed in a matter of milliseconds, but it still takes a while before the prompt appears in webi.

While our team was doing some research the following ticket was discovered:

https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/sno/ui_entry/entry.htm?param=69765F6D6F6465...

Looks like SAP may already be researching the issue.

[Former Member]

Hi Jeff,

If the LOVs are getting fetched in less time then you might have to look into the Java version installed on the machine.

Try clearing the Java cache and check if there are multiple JRE versions on your machine.

Is this issue coming on single machine or multiple machines? If single machine, then compare the Java version from the machine (on which it is working fine) from yours.

Just to troubleshoot, you may also check if same behavior is seen in Webi Rich client as well.

Regards,

Yuvraj

[jeff_crisp]

Hey Yuvraj,

This is happening on multiple machines.  I did test the Rich Client and the loading of the prompts still took around 20-25 seconds to actually display for me to select something.

I welcome the ideas.  We are will to try anything at this point.

Thanks

[Former Member]

Hi Jeff,

Which BO version are you using?

Can you uncheck "Automatic Refresh before use" option inside the Object Properties and check if it helps to fix the issue?

Actually, in order to populate the LOVs, Webi executes simple SELECT statements to fetch the values and then populates them on prompt window. Therefore, in case of large number of LOVs, this operation takes time and the prompt window pop-up delays.

By unchecking the above option, you can avoid this and once the prompt window is up, you can either search for a particular value or you can refresh the respective the prompt values if required.

Hope it will help.

Regards,

Yuvraj

[Former Member]

Is the time taken up when generating the list of values for the prompt?

If so, are the prompts based on the default LOVs on the universe objects? 

Default LOVs are built with a 'SELECT DISTINCT' on the values in the underlying table, which can be quite slow if that table is large.  If you can, try to generate the LOV with a query on a smaller table. I use an SQL LOV on the codes in a dimension table whenever possible.

Also, a useful topic to explore at the same time is index awareness, which ensures you're filtering on the most efficient columns when the prompts are applied.