Cognos TM1 Drill-Through

What is Drill-thru?

Drill-through can be described as the “act of exploring related   information” or the “act of moving between related data via a link of some kind”. More specifically, drill through usually exploits a relationship between “master” and “detail” information. (By clicking a master item in the master information the details of the clicked or selected item are then displayed or otherwise made available).

Drill-through is a very important feature supported “out of the box” by Cognos TM1!

Components of TM1 Drill-through

It’s actually pretty straight forward to implement drill-through in TM1. You simply use one (or more) drill
TurboIntegrator (TI) processes and one (or more) drill rules to associate a data intersection point with detailed or related data. It’s important to understand that the related data or information can be detailed data or really, any other relevant information that you choose.

The components that make up the drill-through feature in Cognos TM1 are the “Drill process” and the “Drill
Assignment Rule”.

The drill processes that you create will define the detailed or related data that you want to assign to the “drill from” data intersection point (the drill from cell).

The drill assignment rules that you create will define the relationship between the drill-from and the drill-to (and associate the drill process).

Once you have created the drill processes and drill assignment rules, you can execute that process (by right-clicking on the drill-from cell) and open the detail (or associated) information in a new “view”.

Drill through (source) Types

There are basically 3 types of supported drill-through types – meaning that there are 3 ways to “source” your drill-to data.  They are:

TM1 Cube Views

This type of drill-through involves drilling from an originating TM1 cube view to any other TM1 cube view
(preexisting or built on demand).

ODBC

You can define a drill-through from an originating view to any valid ODBC source as long as the ODBC source being drilled to is accessible from the computer on which the TM1 server is running. Some examples of valid ODBC sources would include: dBase files, Microsoft Excel worksheets, MS Access Databases as well as relational databases such as Microsoft SQL server.

Other

Keep in mind that you really can implement a drill-through from any TM1 view to any data source supported by TurboIntegrator.

Drill Processes

Drill processes are simply TurboIntegrator processes that you create in a slightly different way than other TM1 processes. Another way to think about a drill process is that it is the Cognos TM1 object that is use to specify the connection nformation in a drill thru.

Steps to Create the Drill Process

1. Select the source or originating cube (the cube you
   will be drilling from) and right-click on it (in TM1 Server Explorer).
2. Select from the menu “Create Drill Process…”
3. At this point a dialog opens displaying a list of parameter values for the origination (source)
   cube and all of the cubes dimensions. TM1 will use these values as parameters
   to setup the drill process.  When you execute the drill process to drill from an origination
   cube to the detailed data, TM1 updates the parameter values to reflect the cube
   location from which the drill-through originates.
4. Click Next, and the second dialog is displayed. You then select the Datasource Type for your “drill-to” data.
5. At this point you need to define the data source that you selected in step 4.
6. Click Finish. The “Save Process As” dialog will open.
7. Enter a name for the drill process in the Name box -don’t prefix with “}Drill”!
8. Click Save. TM1 then saves your drill process as a TurboIntegrator process, but automatically
   prefixes the name with the string }Drill_.

For example, if you save a drill process with the name

SurfboardSalesCubeToODBCSource

TM1 saves the process as

}Drill_SurfboardSalesCubeToODBCSource.

Drill Rules

A drill assignment or “drill rule” is a rule that links cube cell areas with related or detailed data to be drilled through to. Your drill rules will launch (execute) the TurboIntegrator process that you specify for a specific cube cell area.

Creating a Drill
Assignment Rule

Although a drill rule is really “just another” TM1 rule, it is not visible when you open the TM1 “rules editor” in the usual way. To create a TM1 drill rule you need to:

1. Right-click on the source cube (the cube you will be drilling from) from within TM1 Server Explorer and then
2. Select “Drill”.
3. Select “Create Drill Assignment Rule”. This will open the TM1 rules editor, but any non-drill rules that are currently associated with this cube will not be displayed. Once the rules editor is open, you can continue to create your drill rule.

The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

First, as with any TM1 rule, you need to define the area within your cube that you want to associate with the drill through.

The simplest area definition might be to associate all cells in the cube with the drill through. To do this you can use the “Area” button in the rules editor, or simply type in the empty brackets [].

To narrow the area definition (reduce the number of cells in the cube that are associated with the drill through) you would select the element or elements that define the cells you want to specifically associate with the detail data.

Again, you can do this manually or use the rules editor by:

1. Click the Area button, when you do this the “Reference to Cube” dialog box opens. Next,
2. Click the dimension buttons and select the elements that define the cells that you want to associate with the drill through data. Finally,
3. Click OK.

Some Examples

Some examples of drill through rule area definitions:

[]=

The above area definition would associate the drill process with all cells in the cube.

[‘Actual’]=

The above area definition would associate the drill process with only the dimension element named “Actual”.

Assignment of Drill Processes

Once you have defined your drill rule area definition (described above) you can assign a drill process to the area. To do this you can type the equal sign and then “S:”. All drill rules must be use type “S” for string.

Now you can enter the name of the drill process enclosed in single quotation marks to define the detailed data you want
to associate with the area definition that you defined for your cube.

An Interesting Fact

You can associate more than one drill process with a drill process rule area definition by enclosing all of the drill process names, separated by commas, within a set of single quotation marks:

An Example:

[]=s:’processname1,processname2’;

If you do this, (as in the above example) when the user attempts to drill from the defined area, they would be presented with
a simple “Select” dialog and they will have to select the drill process that they want to execute.

To terminate or end your drill rule you must add the semicolon to the end of your rule.

The final step is creating or defining your drill rule is to:

Click “Save”.

Drilling from an Excel Slice

It is possible to drill through to related data from a Microsoft Excel worksheet (rather than the TM1 cube viewer):

If we follow the normal steps to slice the current cube view into a worksheet:

1. Open (or create a new) view in the Cognos TM1 Cube Viewer.
2. From the Cube Viewer, click File.
3. Click Slice. TM1 then slices the view into a new Excel worksheet.

At this point you should see your view “sliced” into a Microsoft Excel worksheet. If you now right-click on a cell in the worksheet (that corresponds to the area definition in your drill rule) TM1 will respond by “drilling” the data that is related to that cell into a new Excel worksheet within your current workbook.

Why does this work? The cells in sliced worksheet contain the DBRW functions that retrieve and display the values from the Cognos TM1 cube. Therefore, the drill works!

Drilling from an Active Form

Another place drill through works – other than the TM1 cube viewer and a “sliced” worksheet is an active form. You can create your active form using either of these methods:

1. From the TM1 cube viewer File menu, click Active Form Slice.
2. From the TM1 cube viewer Toolbar, click the Active Form button.

The Active Form is created in a new empty Excel worksheet and of course, right-clicking in your drill rule area definition will result in TM1 drilling the related data into a new worksheet within your workbook.

Drill through Performance

Drill through is a great feature that Cognos TM1 supports. One of the most obvious uses for this functionality is providing the ability to examine the transactional data that make up a total. Although setting up a TM1 drill through to capture and display underlining transactional data is straight forward, you as a developer should make every effort to optimize the
performance of the drill. Areas to examine are sql statement optimization, relational database indexing schemas, even possibly restricting the maximum number of records that can be drilled to.

Drill On! and..

Happy Holloween!