About Drill throughs
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 to a deeper detail, greater than the one loaded into the Board Data model.
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 Day, Product and Customer. If 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 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 the 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.
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.
When your Data model includes a large amount of Drill throughs it might be difficult locating a specific one or understanding 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 reading 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)