SP5 Patch4 Dashboard Error XLS 000009

[Former Member]

We recently applied BI4 SP05 Patch 4 to fix promotion manager and since this update, we have a go live stop issue with the following error when triggering a Bex query from dashboard. These dashboards and BEx querys passed testing before applying patch 4.

A preview snapshot shows the expected query triggered and had the error below.
Executing the query from Bex Query Designer returns results.

We have reviewed note 1804169 that discusses this error when no data is returned, but in our case there is data to be returned

Request processing failed. (XLS 000009)
com.businessobjects.semanticlayer.qt.QT
Exception: Query script generation failed.
See your BusinessObjects administrator. (WIS 00013)

I realize this (XLS 000009) is posted many, many times, but this error has stopped us in our tracks after applying patch 4.

We have confirmed the following

1. Security - user is admin

2. Olap connection - pre-defined user (not SSO)

3. Restarted BI4 server (windows server 2008 R2) - so dashboard server processes restarted

4. BEX query has external checked

5. BEX query returns results with same OLAP connection user

I appreciate any feedback if you have come across this problem

Lee Lewis

[Former Member · Accepted solution]

Hi,

It seems to be a bug. This issue is scheduled to be addressed in Support Package 6

Please check this KBA:

"1789906 - Unable to manually enter or search a BICS based List of Values in Web Intelligence member selector by value"

http://service.sap.com/sap/support/notes/1789906

Regards,

Wafa

[Former Member · Accepted solution]

Yes, SAP confirmed this is a bug and to be fiex in SP6 due late April.

They proposed downgraded the Dashboard client to SP5 Patch 4, then opening each dashboard from a backup copy (pre SP05 - Patch5) and saving to platform that can be SP05 Patch5.

We think we may have found a work around for us by changing the Dashboard query prompt value from the characteristic key to the text. 

We modified each query in dashboard query browser to pass the prompt value back as text instead of key.  In our case this was for PLANT.So instead of ABC1, we passed 'Houston Plant'.

We are still testing this as we have to change a lot of queries in every affected dashboard.

Special thanks to Scott Sigsbey of Optimal Consulting for finding this workaround!

Will post back with confirmation later today

Lee Lewis

Summit Electric Supply

[former_member194419 · Accepted solution]

Hi Lee,
 

I'm facing the same problem.

The workaround works to you ?

If OK, could please comment the steps?

Thanks,

Rodrigo Silveira

[former_member184594 · Accepted solution]

Yes, this is a big problem. We have problem with one of customers and SAP admits that is a bug.

You can pass Key Values to characteristics in your case PLANT.

In Bex query designer, click on a characteristic and go to Properties pane on the right. Select Display tab. Change Display As to Key. By changing this to KEY, you will still be able to get Texts if the characteristic is placed in Result Objects pane of Query Browser of Xcelsius. If you place this same characteristic to filter pane, you will be able to see LOV as Keys instead of Texts.

Bex Query Designer:

Xcelsius Query Browser:

Hope this helps you as well. Have a nice day.

[Former Member · Accepted solution]

So, Zahid is correct and thank you for the details.

In short, we were passing Key value back to Bex and the workaround is to pass the Text value.

So we had to change every dashboard and every query in the dashboard query browser that performed a BICS call back to BEX query with a prompt value.

Super lame and goes against SAP recommendations to pass Key instead of Text to avoid multi-language problems.

We will be much more selective and cautious about taking on recommended patches.

Lee Lewis

[Former Member · Accepted solution]

Hi all,

we had the same issue and we were crazy! The workaround with using TEXT instead of KEY is not possible in every constellation. In our scenarios we need an identifier for employees for example. This is not possible with using TEXT.

Like you said in the posts before we created also a lot of OSS calls and support meetings and I can give you the news about this issue: We have reached that it was identified as an official bug! It will be fixed in the next patch releases with number: ADAPT01693215

Best Regards,

Benni

[Former Member]

Hi Suman..

My problem is solved as per your guidance in bex properties i passed as key and tried but even it doesn't work.

So i made some R&D and what i found is to Set a prompt on the Queries in the Dashboard itself.

I then kept an Optional Prompt in the filter panel and tried its working fine now.

I dont know how this trick works but its working fine now.

Thanks for your valuable information.

[former_member182541]

Happy to hear that!!!

[Former Member]

I didn't try by passing key. Should i pass KEY for the fields or how...

And how you solved it by passing TEXT..

Kindly guide me..

[former_member182541]

check it in the query browser preview mode itself, drag and drop few objects and take a dimension in filter and try to pass the text or key in it and try preview, be careful while passing the text or key we need the exact text or key to preview otherwise it will throw error.

Using the above method i identified the text is working for me. Having said that i am not assuring you that text will work, for one of my colleague text doesn't work he passed key to make it work. So try and let us know the outcome.

[Former Member]

Hi Experts,

I have gone through all your replies and comments as i am facing the same problem.

I am using BI 4.1 SP3.

I have a question but i am not whether the is appropriate or not (as i am new to this bex queries), can we build Webi on top of the same Bex Query and use it to build dashboard to overcome this is issue as we are using direct bex query connection to build the dashboard.

Your comments are welcome..

Thanks,

Madhu

[former_member182541]

yes you can create webi reports on top of bex query and call them through live office of webservice connection.

if you are having the issue in passing the key try with text, i had the same problem i solved to text not the key.

[Former Member]

Hi all,

I got the same issues. But my situation is a little bit weird.

DEV: 4.0 SP04:

- Set BEx queries default is key: able to filter by KEY

PRD: 4.0 SP07: just apply service patch

- Set BEx queries default is key: not able to filter by KEY, only allow filter by TEXT

Why 4.0 SP04 is find, but 4.0 SP07 got this issue.

Is anyone upgrade to 4.1 and solve this issue?

Please share with me.

Thanks & Best regards,

Quan.

[Former Member]

Hi Quan,

I had the same issues on our System with BO 4.0 and the SP's you told us.

We have installed a new BO 4.1 Server and move all the Content to this new Server. On the new BO 4.1 Server this issue is solved!

Best,

Benni

[Former Member]

Hi Benjamin,

What a great news for us!. Thanks for your sharing.

Do you have any SAP documents relating to this fixed issue? Then I can let my PM know about that.

Thanks,

Quan.

[Former Member]

Hi Quan,

I created a few OSS Calls and I've got a few ADAPT numbers. But I didn't find them on the release notes of the newest Versions. Sorry.

Benni

[Former Member]

Hi Benjamin,

That's ok. I will collect more information about this.

Thank you!

Quan.

[Former Member]

Hi all,

The issue got resolved in BI 4.1 from our testing result, no idea what changes made by semantic layer as Dashboards did not change anything on supporting this feature.

So the better choice is waiting for 4.1 release, GA version is coming soon.

Thanks,

Sarah

[Former Member]

Thanks Sarah for that information. I hope you are right

We are waiting and waiting and ...

Best,
Benni

[Former Member]

Hi Sarah,

While it is encouraging that the problem does not appear yet in 4.1,  what are we to do now on 4.0?

We reported this over 6 months ago and frankly have no plans to move to 4.1 until later, maybe 2014.

The last I heard from OSS on this was a couple weeks ago and seems development has not started on a fix for 4.0 SP05

Please advise.

Lee Lewis

[Former Member]

Hi Lee Levis,

Actually, Dashboards did not do anything in 4.1, but the issue that you mentioned cannot be found in 4.1, so some changes might happened from semantic layer, no idea what the exact fixes are, so it is hard to fix from Dashboards side.

I posted the response based on my test result, so better suggest customer to upgrade Dashboards and BOE version to 4.1.

About how to fix in 4.1 SPx, please communicate with Bill.

Thanks,

Sarah

[former_member963121]

Hi Lee:

Would you please provide:

1. Which specific version you mentioned Pre Patch5.4 works? Patch 5.3 or Aurora SP05? From my experience, Key value refresh event doesn't work for Aurora SP05.

2. You can provide the fiddler logs for both pre patch 5.4 and Post Patch 5.4 to see whether the request are the same.

thanks!

Bill

[Former Member]

Hi Bill,

Not sure what Aurora is, but there is no solution yet on BI 4.0 SP5 Patch 4 or higher and that includes SP06

Lee