In the context of Data Warehousing there are tools such as IdeaSoft O3 geared towards information analysis, also called OLAP tools. These tools usually seek to facilitate analysis through the use of the multidimensional model and generally summarize greats amounts of information in a meaningful set of values that describe reality in the best possible way.

It is precisely this summary of information that lets us face the problem of analyzing large quantities of data in an effective and practical way. However, certain situations require the analysis of some details of the summarized data after identifying one problem area.

OLAP technology in general, and O3 as an implementation of such technology is not geared to handling these levels of detail within the information used for analysis. Among other reasons, because not all the set of available information deserves being analyzed in depth, so that queries made to obtain this information are associated to OLTP technology.

The latter fosters the idea for detailed information to remain in the databases and only be accessed when necessary. That is how functionalities such as Drill Through have appeared, to allow OLAP tool users to perform this type of queries in a simple way.

Drill Through comes from the fact that the typical operations associated to OLAP tools are: Drill Up, Drill Down, Drill To, which stand for going up, down, or to a specific place in one dimension. "Through", in this case means we are plunging into information, idealizing what was mentioned above.

What is "Defining the Drill Through"?

Defining the Drill Through means defining an SQL query that must be performed to obtain specific detailed information. IdeaSoft O3 lets you associate a set of queries to each cube, so as to access data not included in the multidimensional model.

For instance, let's consider the relationships shown in Picture 1 and the multidimensional model shown in Picture 2, a classical sales example. (Obviously very much simplified) where sales are made on a certain date and to a certain customer. A cube generated for the proposed multidimensional model has no individual detail for each sale, as in order to achieve this, a dimension Sales(id_sale) should be present.

The fact of not including the details of sales facilitates the analysis of a great amount of data. However, the need to identify, for instance through the invoice number, what the sales of any given day were may arise. Here is where the functionality known as Drill Through comes in, allowing us to state that a level of detailed data, called "Sales Details" for instance, and resulting from performing the following query on the database, exists.

SELECT id_sale, date, id_customer, amount_gross, amount_net
FROM sales

This query returns the total set of sales as a result, so we must establish restrictions to it, in order to obtain a sub-set of the sales. (for instance, for a day and customer in particular)

If, for instance, we want to analyze the sales to a customer identified as "1", for the month of "January 2003", something we get to by drilling the cube down to year, month and customer, the query we need to make would look like the following one:

SELECT id_sale, date, id_customer, amount_gross, amount_net
FROM sales
WHERE Year(date) = 2003 AND Month(date) = 1 AND id_customer = 1

To state this from O3, we must indicate the values that are selected in the cube to be introduced to filter conditions in the query.

To do so, we add the following expressions to the definition:

• In connection with the "date" dimension of the multidimensional model, and in particular with the first level (year), we find that "Year(date) = ?" is the condition that must be added to filter the year of the date. The "?" symbol indicates where the value selected in the cube must be substituted, in this case the year 2003
• As to the "date" dimension but in this case connected with the second level, we find the condition "Month(date) = ?"
• And as to the "customers" dimension in the first level, the related condition is "id_customer = ?".

This mechanism lets you create extremely powerful queries that are linked to the O3 cube enabling you to parameterize the conditions that must be added in each case. It is important to highlight that it is necessary to specifically indicate at which levels of the dimension the values to filter the query will be given.

As a result, O3 will add the necessary conditions to the query depending on whether the user has filtered certain elements at the time the Drill Through query is made or not.
Following you will find the necessary definition details to implement this functionality.