Drill-through

What is drill-through?

Drill-through can be described as the act of exploring related information. Also, this can be described as the act of moving between related data via a link of some kind. Most specifically, drill-through usually exploits a relationship between master and detail information.

Components of TM1 drill-through

The two components that make up the drill-through feature in Cognos TM1 are:

Drill process: 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).
Drill assignment rule: The drill assignment rules that you create will define the relationship between the drill-from and the drill-to.

Where to drill from?

Typically you set up a drill-through with the intention of drilling from a cell in a view in an Open Cube Viewer.

Drill-through (source) types

There are basically three types of supported drill-through types—meaning that there are three ways to source your drill-to data:

TM1 cube views
ODBC
Other

TM1 cube views

This type of drill-through is drilling from an originating TM1 cube view to any other cube view of data.

MaximumViewSize

This is an optional parameter. It sets the maximum size that a cube view can display. This is any cube view—not just a drilled-to cube view. If you do not set this parameter and the defaults (100 or 500 MB) are exceeded, TM1 will display an error message.
The Format is as follows:
MaximumViewSize=n
Where n represents the amount of memory in MB to be allocated.

ODBC

Some examples of valid ODBC sources would include:

dBase files
Microsoft Excel worksheets
MS Access databases
Relational databases such as Microsoft SQL Server

Drill process

Drill processes are simply TI processes that you create or define in a different way than the method of creating and saving TI processes. Another way to think about a drill process is that it is the Cognos TM1 object that is used to specify the connection information in a drill-through.

Creating a drill process

A drill process can be created by performing the following steps:

Select the source or originating cube (the cube you will be drilling from) and right-click on it (in TM1 Server Explorer).
Select Create Drill Process… from the menu.
At this point, a dialog opens displaying a list of parameter values for the origination (source) cube and all of the cube’s dimensions.
TM1 uses these values as parameters to the drill process when executing (drilling) from a source cube to a destination cube.
Click on Next, and the second dialog is displayed. Then, you can select the Datasource Type for your drill-to data.
At this point you need to define the data source that you selected in step 5.
For example, if you decide to use an ODBC data source, you will need to supply the following things:

Datasource Name: This is the official name given to the ODBC data source (DSN) that you want to access when drilling from your source cube. Remember that this DSN must be accessible to the machine where the TM1 Server is running.
User Name: This is the valid user name which TM1 will use to log into the ODBC data source that you are using.
Password: This is the password that the user name you set is to use.
Query: This is the query that defines the data or information to return from the ODBC data source. These results will be displayed in your drill-to window.

If you want to use an existing TM1 cube view as a data source, you need to supply the following:

TM1Servername:cubename:cubeviewname

Once you have created the desired TI logic. You can perform the following steps:

Click on Finish. The Save Process As dialog will open.
Enter a name for the drill process in the Name
Click on Save. Then, TM1 saves your drill process as a TI process, but 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.

Editing drill processes

When you select a TM1 cube view as a data source in a drill-through, TM1 will automatically add some code to your TI drill-through process. The code which gets inserted in the TurboIntegrator function:
ReturnViewHandle(‘Cube’,’View’)
This function gets inserted above or below the generated statements area, which is located on the Epilog sub-tab of the Advanced tab in the TurboIntegrator window.
After editing:

Click on Save.
Then, close the TurboIntegrator window.

Removing drill processes

To remove an existing drill process, you need to perform the following steps:

From within TM1 Server Explorer, right-click on the source cube (the cube with which the drill process is associated).
Select Drill.
Then, select Delete Drill Process. The Delete Drill Processes dialog box opens which will list all of the drill processes associated with the cube.
Select the process(es) that you want to remove (delete).
Then, click on OK.

Drill rules

A drill assignment rule or drill rule is a rule that links cube cell areas with related or detailed data to be drilled-through to (the drill-to detailed or related data can reside in any existing TM1 cube on the same TM1 Server instance or reside in any ODBC accessible data, or really any data accessible through a TM1 TurboIntegrator process as discussed earlier).

Feeders

Unlike other TM1 rules, drill-rules do not require the addition of any supporting feeders specifically to make the drill work.

Creating a drill assignment rule

Right-click on the source cube (the cube you will be drilling from) from within TM1 Server Explorer.
Select Drill.
Then 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:

Again, you can do this manually or use the Rules Editor by performing the following steps:

Click the Area When you do this the Reference to Cube dialog box opens.
Then, click on the Dimension button and select the elements that define the cells that you want to associate with the drill-through data.
Finally, click on OK:

Drill-through area examples

Some examples of drill-through rule area definitions are as follows:
The following area definition would associate the drill process with all cells in the cube:
[]=
The following area definition would associate the drill process with only the dimension element named Actual:
[‘Actual’]=
The following area definition would associate the drill process with only the dimension elements named Actual and First Quarter:
[‘Actual’, ‘First Quarter’]=
The following area definition would implement the drill-through only for a version element with the string Act starting at position 6:
# Filter out by Version
[] = S: IF(SUBST(!PPP_Version, 6, 3) @<> ‘Act’, ”, CONTINUE);

Assigning 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 type the equal sign and then S:.
All drill rules must use type S: for string

More on drill rule area definitions

If your drill rule area definition is associated with a single source of detailed data, the data opens in a new (Cube Viewer) window.
If your drill rule area definition is associated with two or more sources of detailed data, a list of the data sources is displayed and you will have to select the source you want to view and click on OK.

Conditional drill rules

At some point you may want to add some additional logic to your drill process rule. You can use any valid TM1 Rules function to do this in your rule.
Some simple examples of this might be as follows:
# Sample drill number 1
#- Define the area – in this case the model dimension is used and the
#- area is defined as ‘Standard #- Model’. If the standard model in the #- surfboard Meta cube is X then execute the drill process
#- named launch_standard_model
[‘Standard Model’]=s:IF(DB(‘Surfboard Meta’,!subclass, !version, ‘Standard Model’)@=’X’,’launch_Standard_Model’,”);
and
# Sample drill number 2
#- Define the area – in this case the model dimension is used and the
#- area is defined as ‘Long
#- Board Model’. If the long board model in the surfboard meta ube is X #- then the user will be #- presented with a choice to execute the drill #- process launch_long_board_model
or
#- launch_standard_model
[‘Long Board Model’]=s:IF(DB(‘model_meta’,!subclass,!version,’% of Channel’)@=’X’,’launch_long_board_model,launch_Standard_Model’,”);

Drilling from an Excel Slice

We can follow these steps to slice the current cube view into a worksheet:

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

Drilling from an Active Form

Active Forms let you view and update live TM1 cube data directly in Excel whenever you are connected to the TM1 server on which the cube data resides. Active Forms retain the ability to expand and collapse row dimension consolidations in a TM1 view while allowing you to use native Excel features and functions to create complex reports.