Dave's Adventures in Business Intelligence

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.