Oct 13 2007

Peeling the Onion…

Categories: General Dave Rathbun @ 9:07 am

A few weeks ago I spent a good bit of time trying to solve the wrong problem. Don’t you hate that when it happens?

It all started when we added another fact table to our database. I had to add the new fact table to my universe, create the new context, and repointed a bunch of existing objects from the old fact table to the new one. As we started testing the reports we started seeing an error:

WIS 00013 Error during SQL Generation

Naturally this was because of the switch from one fact table to another, right?

Some reports were generating this error, and others were not. Rather than try to figure out what the differences were I simply started recreating one of the failing reports from scratch. After adding / removing objects one at a time I narrowed the search down to a specific set of objects that included embedded prompts. Report without these objects worked, reports with these objects failed. Seemed to be a good indication of where the problem was coming from.

The objects in question parsed fine in Designer. And using them in Desktop Intelligence did not cause any issues whatsoever. It was just Web Intelligence that was failing. Hm.

The objects in question were somewhat complex in that they include nested case statements. The object was designed to count a value based on the results of a prompt and one other condition. The prompt was a single-selection but included an “All” option. In order to avoid using the where clause of the object everything was pushed into the select statement with logic that looked something like this:

count(distinct ( case when table.item = case when @Prompt('Please select Item','A','LOV\Item',mono,constrained) = '- All -' then table.item else @Prompt('Please select Item','A','LOV\Item',mono,constrained) end and table.qty > 0 then table.unique_id end ))

Looks complicated? Not really. 🙂 This logic is designed to count something when the quantity is greater than zero, and the item to be counted matches that entered on the prompt. Think of it as a way to count stores that sold a particular category of product, where the category was prompted for the user to select. The prompt also includes an “All” option, so that has to be addressed. If I included this object on the report it failed. If I removed it, the report worked. So it seemed clear that this object was where the problem was.

The only thing wrong with that statement is that this object – with the exact same logic – worked in an older version of the universe. Confusion. 😕

I spent some time working with this object and trying to come up with various different logical ways to accomplish the same thing. Nothing worked. I spent some time checking the data in the two different databases to see if that was causing issues. It wasn’t. I rebuilt the object by copying the code from a universe that worked into the one that did not, and it didn’t fix the problem. And as I have already said, this parsed in Designer, worked in Desktop Intelligence, but not Web Intelligence. Very frustrating.

Finally I started peeling the onion, which is to say I removed one thing at a time until I got it to work. First I removed the check for qty > 0. Then I removed the All. Then I removed the prompt. Aha, without the prompt the object works.

I created an object with just the prompt and it failed. So the complexity of the original object was really masking where the problem was. Now that I knew the problem was with the prompt, I am finally at the root cause, right?

Wrong. 🙂 The problem was not with the prompt. The problem was with the list of values (LOV) query associated with the prompt! Now I am finally at the core of the onion.

You see, along with creating the new fact table and the new context and so on, I had also made a very minor alteration to the LOV query for one of my objects. By default a LOV shows all of the elements of a table column; that’s what it’s supposed to do. I had already altered the LOV to include the All token. Now the users decided that they wanted to eliminate a couple of values from the list that were not going to be used on the report. So I added a condition to the LOV query:

table.item in list ('A', 'B', 'C')

Very simple. With this condition on the LOV I get the error mentioned above. If I remove it, the error goes away.

We’re in the process of logging a case with Business Objects on this one, as there is no reason that a condition on a LOV should cause SQL generation errors in Web Intelligence. For now I have removed it.

I worked on this over the course of two days. I don’t think that makes me stupid. 🙂 It just goes to show that the initial indications of where a problem might be can be completely wrong. In this case it had nothing to do with the major universe work that I had just completed, and everything to do with a very simple change that should not have had any impact at all. Peeling the onion, indeed.

6 Responses to “Peeling the Onion…”

  1. Comment by Irfan

    Dave,
    Just want to say one thing, Its really strange that the WebI report throws the said error, you might have already tried it, but want to remind you, whether you try deleting the universe and LOVs from the WebI Server…… and try refreshing the report….. by any chance old LOV’s might have got corrupted??

  2. Comment by Dwayne Hoffpauir

    I have often said that if accounting and / or computers (my career choice) suddently disappeared, I could become a forensic scientist … the skills are very similar!

  3. Comment by Mike McErlain

    Dave

    Good to see you struggling with XIR2!

    I think you’ll find there is a variety of neat new errors in XIR2 that you never saw before in the prior versions. At times it appears the some of the coders had no idea at all what the code was supposed to do!

    I found some errors that occur only after you run a macro in a report and then to make them go away you have to exit and restart DESKI! Not too big a deal but annoying!

    Of course I also find the constant revisions of the code via monthly hotfixes to be problematic in shops where you have many different servers handling many different processes in the environment!

    Good luck on your endeavors!

    Mike McErlain

    PS – Hate to miss this years version of your presentation to the Users Conference but my client had urgent requirements and I like to keep my clients happy! Have a good “performance” at the User Conferene and say Hi to Dave for me and congratulations on the merger.

  4. Comment by Dave Rathbun

    Irfan, I am quite certain it is not a corrupted LOV. I can undo the change, export the universe, and everything works. I can then put the condition back and recreate the error. I think the “cause” and “effect” are quite clear in this case.

    Mike, I passed on your comments to Dave (he’s sitting about 15 feet from me as I type this). Thanks. 🙂

  5. Comment by Dan Flanery

    Hey Dave, wild speculation here, but could this bug have anything to do the recent change to the Eval Without Parenthesis Universe parameter you outlined in a recent post? Or did you do make that change in another Universe?

  6. Comment by Dave Rathbun

    Hi, Dan, thanks for your comment. I don’t think the two are related but it could be something worth testing. Now that the conference is over I have to catch up with the backlog of work that accrued while I was away, and then I can try to do more testing on this issue.