Business Objects Query Drill

Query drill (or apply drill filters) is a somewhat hidden feature of Business Objects. In fact, I rediscovered it when I was teaching BO Webi.

It got me somewhat curious, to find out if it also existed in BO 5 and 6.5 — and in fact, it exists in every single one of those versions.

1) Where do you find it :
In webi, it is located in the document properties — you check “query drill”
In full client, it is located in Tools>Options and on the tab Drill, check “Apply Drill filters on drill through”

2) What does it do
In fact, drilling is a tricky business — you navigate through your data and little by little, you get your data and you extend your query. Start with 10 records, and end up with a query of over 50000 records.

Because — that is what happens, you want more details, so you drill for an extra column using drill through and you end up multiplying the data in your dataset.

Apply drill filters on drill through will actually limit your query to the data you filtered for using drill by setting conditions on the query.
So, you drill down on year and get quarters (2006). Then you drill through to months on Q3 — your query is now adapted and filtered for 2006/Q3.

This results in a number of things :

1) Less data (only the data you drilled for is shown)2) Functions aggregated on the database level are more likely to be calculated correctly3) A speed-up (or a slow down) compared to regular drill will occur (depending on the nature of the fields you filter on !

This last point asks for some extra explanation :

Lets assume you have a date field which is indexed at the database level. On the universe, the designer (made the error and ) created a field with function year(datefield) and now youare drilling for a given year. This will filter on a function applied on a date-field — the result is desastrous for performance. (if the year were a physical (indexed) field on the database, the
performance would be very good)

enough already,

Back to reporting

Expand/Collapse (Business Objects)

This is the place where I store the things I tried once, found interesting and choose to forget again… to avoid loosing the info altogether, I write it down. Here is a perfect example of such a fact.

At a certain point, I was wondering what “expand” and “collapse” in the analysis menu were used for. I figured it out — but at the time I didn’t have this Blog yet — so I forgot all about it pretty soon after.

Let’s assume you have a table with Year and Average Revenue (which is a variable, calculated from “Sales Revenue” and “Quantity Sold”). Next, you enter drill-mode. You can now drill on the year and look at the quarter, to month etc… but what about the individual figures which were used to calculate the average revenue… well — there you go. Still in Drill Mode, you go to the Analysis menu and click “expand”.

Automatically, both measures are shown in colums next to the average Revenue. When you choose collapse, they disappear again.

Nifty little trick.

Couldn’t find this in the Core Reporting, the Intermediate or the Advanced Reporting courseware. But it is in the product-documentation — unlike some other stuff.