TM1 Drill Down
Date: 21/02/2017

By Denis Barchukov

TM1 is well-known for its flexibility, user friendly interface and powerful engine on the server side. It is perfectly suitable for data discovery tasks. That is why many customers use TM1 not only as a budgeting and forecasting tool, but also as an analysis tool for many reports and analytical dashboards. In this article I’ll show you how to leverage TM1 to deliver reports that provide maximum visibility and structural simplicity.

If you ask business users how much data they want to see in their reports, most likely, the answer will be, ‘Everything’. Based on an in-memory multidimensional database, TM1 is able to process enormous amounts of data. It is tempting to add more dimensions and more data, which, eventually, leads not only to performance degradation but a UX (User experience) nightmare for end-users.

How do we combine these two ideas -- having a simple, intuitive data structure and still have maximum data details at the same time? TM1 has an answer. You can create a drill process to associate a data cell with more detailed data. The data can provide underlying detail for the cell from another TM1 cube or external data source includes transactional data source.

Business Case

Let’s take a look the following example to see how this works in a real life. On the screenshot below you can see a typical TM1 Sales Plan Cube with some of the more useful analytical slices for sales data -- Channels, Products and Regions.

TM1 Drill Down_image 1.png

However, in some cases it’s good to have underlying information for the amounts in here. The requirement is to see the quantity broken down by Sales Reps and Purchase Orders. Instead of adding Sales Reps dimension and PO, we can setup a drill through process to extract data directly from the database, which contains all necessarily information.

How to do it

In order to access the source database, we can setup Open DataBase Connectivity (ODBC), which TM1 drill- through can use.

It consists of two components:

  • Drill Process - Defines the detailed data you want to associate with a cell
  • Drill Assignment Rule - Defines the relationship between the cell and the detailed data

Let's take a look at an example of how to create a drill process from the Sales Cube.

  • Right-click the Sales Plan cube, point to Drill, and then click Create Drill Process.
  • In the Drill Process Setup Wizard, you can see paramater names and values in it.

(TM1 provide this information to the SQL query below), click Next.

TM1 Drill Down_image 2.png

  • Select created ODBC source and Enter SQL query with the parameters from previous steps.

TM1 Drill Down_image 3.png

  • Click Finish.
  • In the Name box, type the name of it, (ex. SalesPlanDataBase), and then click OK.

After we setup drill process, the second step is to create drill assignment rule. A simple example of how to create a rule is below. In the example, we use function ISLEAF to apply the ' SalesPlanDataBase' drill assignment only for leaf cells.

  • Right-click the Sales_Plan cube, point to Drill, and then click Edit Drill Assignment Rules.
  • Type [] = S:IF (ISLEAF=1, ' SalesPlanDataBase', CONTINUE);.
  • Click Save and close the editor.

Drill Process has now been set up and we can see the result below.

TM1 Drill Down_image 4.png

We open the lowest possible level for the Sales cube: Selected channel, Product and store. In order to see mode details we can right click on quantity cell in Mar and pick ‘Drill’ option.

As the result we have additional information by Sales reps.

Conclusion

Keep this feature in mind the next time you have a requirement to look at your data with extra details. Drill through feature can provide additional granularity to your analysis.

If you would like to learn more about using Cognos for user-friendly Excel exports, call us on 1300 841 048 or reach us online.