Drilling Through to Greater Detail with Planning Analytics

Evan McKirdy, April 13, 2020

Table of Contents

Introduction

Planning Analytics is an analytics application traditionally used for slicing and dicing multidimensional data often stored as summarized balances. These balances are often the aggregate of several transactions for an account during a given fiscal period. Financial analysts will occasionally need to review specific transactions that make up these balances if anomalies or outliers are detected in reports. The capability to ‘drill through’ a summary balance into a separate data source containing transactional detail (without logging into a separate system) is one of the many benefits provided by Planning Analytics.

What is a ‘Drill Through’ Process?

Before diving in, let’s establish a couple of basic terms and definitions.

• Drill Process – A TurboIntegrator process that extracts detailed source data associated within a specific cross-section of cells in a TM1 cube view.
• Drill Assignment Rule – A rule that defines the relationship between the specific cross-section of data in a cell and the detailed source data from which it originated.

Creating both a process and rule associated with it, allows for the ability to then view detailed source data specific to a cross-section within a cube in TM1. Thereby, ‘drilling through’ to a new level of detail.

Creating a Drill Through Process

Step 1: Right-click the cube you wish to drill through in Server Explorer.

Step 2: Click on said Cube, then select ‘Drill’, which will prompt you to select ‘Create Drill Assignment Rule’.

The first window of the Drill Process Setup Wizard opens.

The Drill Process wizard will appear with the dimensional order of the cube you are drilling into. For example, the finance cube has the following dimensions (in order): Version, Day, Organization, Department, Account, and Finance Measures. The specific cross-section you wish to dive deeper into can be specified in the wizard that appears in the screenshot below.

Step 3: Click on the ‘Next >’ button.

The second window of the Drill Process Setup Wizard opens. (see next screenshot below)

Step 4: You will be prompted to select the Datasource Type for the cube’s cross-section you want to drill through.

There are three ‘Datasource Type’ options:

a. ODBC – Looks from the cross-section of the cube view to an ODBC data source. You must be able to access the ODBC data source from the same computer on which the IBM® Cognos® TM1® server is running. This is done via a setup on the machine hosting the TM1 environment via the ‘ODBC sources’ computer management configuration tool.

b. Cube View – Looks from the cube view to a source cube view. You can drill to any cube that resides on the same server as the origination cube. Note that maximum view size for the drill through is defined as a parameter in the TM1s.cfg file.

c. Other – Looks from the cube view to any data source TurboIntegrator supports (i.e .csv,.txt)

Step 5: Depending on the datasource type, you will then have to define the data source. Each data source type has different definition requirements.

a. ODBC – Datasource Name (DSN), User Name & Password to log in, query in relational data table

b. Cube View – Datasource Name of View in Cube

c. Other – Click ‘launch TurboIntegrator’ to define the data source

Step 6: Click on the Finish button. You will then be prompted to ‘Save as’.

Step 7: Enter a name for the drill process in the Name box and hit Save.

Note: TM1 saves the drill process as a TurboIntegrator process but prefixes the name you assigned to the process with the string }Drill_.

For example, if you save a drill process with the name GrossSales2012, TM1 saves the process as }Drill_GrossSales2012.

Creating a Drill Through Assignment Rule

A drill assignment rule is the TM1 rule that connects cells in a TM1 cube view with the data source setup in the previous step. As indicated above, the related data can be a cube view, an ODBC source, or any other data source that is accessible through TurboIntegrator.

Step 1: In the Server Explorer, select the cube for which you want to create a drill assignment rule.

Step 2: Click on said Cube, then select ‘Drill’, which will prompt you to select ‘Create Drill Assignment Rule’.

The Rules Editor prefixed by }CubeDrill_(CubeName) will open.

For each cube area you want to associate with detailed data, continue with steps 3 through 8.

Step 3: In the Area section of the rule (left-hand side), define the cube cells you want to associate with detailed data.

When you click the Area button […], the Reference to Cube dialog box opens.

Step 4: To define the area, do one of the following:

a. To define the area as the entire cube, click OK.

b. To narrow the area definition, click the dimension buttons and select the elements that define the cells you want to associate with the detailed data, and then click OK.

Step 5: Click on the equal sign (=).

Step 6: Click on S: This signifies the entry of a string-based rule.

Step 7: Enter the name of the drill process rule. For our example, enter ‘GrossSales2012’.

Note: Do not include the }CubeDrill_ prefix in a drill process name. For example, enter ‘GrossSales2012’ for a drill process named

}Drill_GrossSales2012.

Step 8: Click on the semicolon (;). This signifies the completion or end of a rule in the rules editor.

Step 9: Click on Save and click Exit.

The drill assignment rules need to be specific as well, across all N levels. Once this is updated, it can call the proper drill process for each cell as needed. This will be shown across the ODBC source example (Gross Sales) vs the cube view example (Trade Sales).

Customizing a Drill Through Process

When you create a drill process with a Cube View data source, TurboIntegrator will insert the function ReturnViewHandle (‘Cube Name’,’View Name’) in the Generated Statements area. It is located on the Epilog subtab of the Advanced tab in the TurboIntegrator process editor.

Should you later decide to change the cube view used for the drill process, you will need to delete and recreate the drill process as this coding is written within Generated Statements which cannot be edited.

ODBC Source

Results of a drill process can be customized within the SQL query either during the creation of the drill process or later when changes might be required.

During Dill Process Creation

Revising SQL query of an existing Drill Through Process

Cube View Source

Step 1: In the source cube you wish to drill through, create a view called ‘zDrillView’.

Step 2: For any dimensions on the rows or columns of the Source Cube View, create Subsets called ‘zDrillSubset’. In this example, I’m only doing this for ‘Day’ time dimension.

Step 3: Create a Drill Process that drill from the Trade Sales accounts in your Finance cube to where those transactions came from, in the transaction cube. Save the process as ‘TradeSalesTransactions’.

Step 4: Create the Drill Rule for Trade Sales accounts to be triggered by the Trade Sales transactions drill process.

Step 5: Edit the Prolog tab of the drill TI Process with the following code. Take the Parameters from the top of this document.

Prolog

#Set Constants for Inserting into View

cub = ‘Transaction’ ;

view = ‘zDrillView’ ;

dim = ‘Day’ ;

sub = ‘zDrillSubset’ ;

#Set element into DrilSubset, this can be repeated across all necessary dimensions

SubsetDeleteAllElements( dim, sub ) ;

SubsetElementInsert( dim, sub, Day, 1 ) ;

ViewSubsetAssign( cub, view, dim, sub );

Epilog

#****Begin: Generated Statements***

RETURNVIEWHANDLE(‘Transaction’,’zDrillView’);

#****End: Generated Statements****

Testing and Examining the Drill-Through

Step 1: Go to a cell in the defined cross-section of the cube as specified in the drill through process wizard.

Step 2: In this example, we have a Finance cube where we will drill down on Net Sales Accounts from 2012. Right-click the cell at the intersection of Trade Sales – 4200, 3 – Northeast Division, and Research and Development as shown.

Step 3: Click Drill.

a. An extract from the relational data source table opens, displaying the monthly detail for the cell.

b. If you chose a cube extract, the drill through will pull up the cube view referenced in the ReturnViewHandle process function.

Step 4: Click OK to close the window.

Step 5: Drill through other cells in the cube view to view the monthly detail. You should be able to drill through any cell in the view.

Conclusion

Drill Through is a feature that allows users to easily locate the origination of data points within the PA model. Once drill through is configured, transactions surrounding a certain balance can easily be isolated and analyzed should there be any anomalies or outliers that require further review. This capability provides users with the great benefit of having rapid transparency down to the absolute lowest level of detail in the data set.

Next Steps

We hope you found this article informative. Be sure to subscribe to our newsletter for data and analytics news, updates, and insights that are delivered directly to your inbox.

If you have any questions or would like PMsquare to provide guidance and support for your analytics solution, contact us today.