About Drill throughs

This topic explains what a Drill through is and how to access the Drill Through page.

The Drill through function allows users to drill through to data stored in an external relational database or an ODBC or OLE-DB compliant data source, right from a Board report. This function is useful when you need to investigate data in greater detail, greater than the one loaded into the Board Data model.

What is a Drill through?

In a typical Board Data model, the detail level of Cubes allows you to drill from summary reports down to the individual Cube cell defined by the Cube structure.

For example, if you have a Cube structure of Day, Product, and Customer and the sales of a particular product are exceptionally high, you can drill down to the Customer level first, then select a particular customer that you wish to analyze in detail. Then, you can eventually drill through and retrieve the exact transactions from the accounting ledger for that particular day/product/customer.

In a Drill through protocol, Board Entities are mapped to matching fields of the table or query of the SQL data source. If needed, additional fields of the table (or query) can be added.

The input of a Drill through protocol is the Entity members from which the user intends to drill through (they represent the multidimensional space coordinates). Then, the protocol executes an SQL query (limited to just those Entity members via the WHERE clause of the SQL query) on the relational data source: a Drill through is essentially a protocol which executes a query that considers a manually configured mapping between Board Entities and the corresponding fields in the external database. A WHERE clause is added to the end of the query where each mapped field is compared with the code resulting from the drill, the Select, or the Pagers in the Screen.

The query result is made of several records (just as a Drill down returns several detailed records that has been aggregated in the initial value) and additional information from the transaction table that does not correspond to any Entity or Cube in the Board Data model.

For example, a Drill through protocol on the invoice table can return the invoice number and the corresponding order number, even though these details do not exist in the Board Data model.

The Drill through page

To access the Drill through section of a Data model, access the designer space of the desired Data model and click on the Drill through tile.

Once in the Drill through page, you can see all existing Drill through protocols for that Data model and their main information; the table is sortable and searchable using the interactive header fields. You can also show or hide columns to your liking by clicking the Column chooser button in the upper right corner of the table.

You can perform different actions on one or more Drill throughs by selecting them and by clicking on the different buttons above the table. See Managing Drill through protocols for more details.

Pro tip:

When your Data model includes a large amount of Drill throughs, it might be difficult to locate a specific one or understand the purpose of all Drill throughs listed in the table. In this case, we strongly recommend that you logically group them using the Group cell in each row.

Groups are not part of the multidimensional Data model (i.e. they cannot be used in Procedures): the only purpose of groups is to improve viewing and searching through the list of Drill throughs.

The Drill through section of each Data model in Board allows you to:

• Create, delete, and copy Drill throughs

• Edit any of the two configuration steps of each Drill throughs (Source and Mapping)