Prompt LOV Load Issue

[former_member402770]

Hi,

   I tried to create an fiscal period prompt at the universe level like condition on custom dimension obj like below in steps:

Universe prompt Fiscal Period LOV's:

After 2-3 mnts, I get the LOV populated for the prompt with count of members as below:

Slow prompt response on populating LOV's is the actual issue..

Is the time taken up when generating the list of values for the prompt  --- 2-3 Mnts

Can this be built with a 'SELECT DISTINCT' on the values in the underlying table

The custom object at business layer for fiscal month and year are If then else statements used..

This is an multisource having independent derived tables with each fiscal period object.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

try with some optimization tips and see if performance improve.

In the Business layer -> click on the Parameters tab and change the setting DISABLE_ARRAY_FETCH_SIZE_OPTIMIZATION from the default of "No", to "Yes" and click Ok.Export the universe and check the LOV's.

In the BO client tools open the Data Federation Admin Tool and login on the system.Same server name  and user details which you are using to connect IDT.

In the query monitoring tab check the run times of your query when you refreshing the LOV's or webi reports.See if some difference will come or not.

You needs click on the refresh icon on the right side to see the latest query running status.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

  Thanks, I had this settings already no improvement:

According to one of your post response below:

Created Custom LOV's at universe level for let say Fiscal Year /Period from delhi class.

Followed these steps.

1) click on parameters and list of values and create list of values based on the business layer objects.

2) drag the object and click on the view script.

3) add the union and manually the second object sql(Fiscal Year /Period from Mumbai class) & validate and save it.

4) click only Delhi fiscal month/year object ->advance->List of values and select the newly created LOV's for this.

5) For Delhi Data provider (Main Universe Table), used this fiscal month/year object as inlist/equal to  in the webi query panel as prompt.

6) For Mumbai Data provider (Derived Table with no joins to Main), used this fiscal month/year object as inlist/equal to  in the webi query panel as prompt.

In webi keep the same prompt text for both dataprovider Select the"prompt with list of values" option in Prompt option  for Delhi fiscal month/year prompt. because i have added the custom lov's for Delhi object.

For Mumbai unchecked the "prompt with list of values"option.with this you will get the all values from Delhi fiscal month/year object.

Iam getting below issue instead LOV's display after running for long time in the Delhi fiscal month/year object..(used inlist/equal to but the requirement to use inlist operator )

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi

click on the details icon and share the error message details.

one qns is it mandatory requirement to display the Lov's and user should select from the list.if it is not working then might  you can go for manually enter the value in the prompt or search the particular value.

like  *009* in the search.

Amit

[mhmohammed]

Hi Din,

I strongly believe that some of these complex logics shouldn't be created in the Universe, when DBA's can do this in a Date Dimension table. DBA' should have a Stored Procedure which populates the Period values in that column. So, you don't have to go through any of this hassle.

Why don't you talk to your Manager first and let him talk to the DBAs?

Thanks,

Mahboob Mohammed