Data & Intelligence

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.
Data Intelligence - The Future of Big Data
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!

Thoughts on “Cognos TM1 Drill-Through”

  1. Hi Jim,
    Very informative guide on drill through! I have a question though – is it possible to drill from one webslice to another webslice? It seems that you can drill from a wide variety of sources (cubes, websheets, excel etc) however the result comes back to a particular view (or a sliced view for that matter). We have quite a few excel templates set up that we will be publishing on TM1 Web and i was wondering if it is possible to drill from one to the other (passing values if possible). I know you can achieve this to a point with Action Buttons, however list of values (for example products with sales information) and you want to pass the product number to another websheet via drill through, i get stuck on the TI process that would do this. Any help would be greatly appreciated

  2. Bonjour!
    1) Thank you for you big work.
    Second: Congratulations for very special issues especialy in the article https://blogs.perficient.com/businessintelligence/2011/10/13/cognos-tm1-drill-through-3/. Amazing to see this blog that gets the sage ideas.
    Third: Could you send me the some texts somewhere or drop me an email Homrich@gmail.com? – will be great:)
    I no doubt give your link to Facebook Cognos TM1 Drill-Through | Perficient Business Intelligence

    Thanks!
    Best regards
    Lehmann Architecture.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Jim Miller

Mr. Miller is an IBM certified and accomplished Senior Project Leader and Application/System Architect-Developer with over 30 years of extensive applications and system design and development experience. His current role is National FPM Practice Leader. His experience includes BI, Web architecture & design, systems analysis, GUI design and testing, Database modeling and systems analysis, design, and development of Client/Server, Web and Mainframe applications and systems utilizing: Applix TM1 (including TM1 rules, TI, TM1Web and Planning Manager), dynaSight - ArcPlan, ASP, DHTML, XML, IIS, MS Visual Basic and VBA, Visual Studio, PERL, Websuite, MS SQL Server, ORACLE, SYBASE SQL Server, etc. His Responsibilities have included all aspects of Windows and SQL solution development and design including: analysis; GUI (and Web site) design; data modeling; table, screen/form and script development; SQL (and remote stored procedures and triggers) development and testing; test preparation and management and training of programming staff. Other experience includes development of ETL infrastructure such as data transfer automation between mainframe (DB2, Lawson, Great Plains, etc.) systems and client/server SQL server and Web based applications and integration of enterprise applications and data sources. In addition, Mr. Miller has acted as Internet Applications Development Manager responsible for the design, development, QA and delivery of multiple Web Sites including online trading applications, warehouse process control and scheduling systems and administrative and control applications. Mr. Miller also was responsible for the design, development and administration of a Web based financial reporting system for a 450 million dollar organization, reporting directly to the CFO and his executive team. Mr. Miller has also been responsible for managing and directing multiple resources in various management roles including project and team leader, lead developer and applications development director. Specialties Include: Cognos/TM1 Design and Development, Cognos Planning, IBM SPSS and Modeler, OLAP, Visual Basic, SQL Server, Forecasting and Planning; International Application Development, Business Intelligence, Project Development. IBM Certified Developer - Cognos TM1 (perfect score 100% on exam) IBM Certified Business Analyst - Cognos TM1

More from this Author

Subscribe to the Weekly Blog Digest:

Sign Up
Follow Us
TwitterLinkedinFacebookYoutubeInstagram