Drill Through Processes in TM1: How to Create Them

In this quick tutorial I’m going to demonstrate how you would go about creating a TI Process which retrieves information from a source system. At my latest clients site we have a process which retrieves actual journals from PeopleSoft just from drilling on aggregate actual data.

How a Drill Through Process Works

1) The Drill Button – TM1 Cube viewer / Excel Slice / TM1 Web

When you right-click on a cell in any ‘official’ TM1 Client, TM1 will check for a ‘Drill Mapping Cube’. This is a ‘Control’ Object in TM1 – which are usually hidden.

(Well talk about how this is created & populated a little later)

If this ‘drill’ cube has the name of a TI Process in it at the same intersection of elements as the cell that was clicked then it will allow the ‘Drill’ button on the popup menu.

2) The Drill Process

When the drill button is pressed, the process specified in the ‘Drill cube’ is executed. Each element from dimensions in the selected cell/DBRW formula will be given to the drill process as parameters.

It’s then up to the prolog section of the Drill process to use these parameters to correct the ‘data-source’ of the process. It’s important to note that only the ‘Prolog’ section of the process is executed. The data that would usually be parsed in the ‘Meta’ phase is dumped to the client as results of the process.

Making a Drill Process:

  1. Right-Click on the cube you want to create the process on
  2. Hover over ‘Drill’ -> ‘Create Drill Process’
  3. Now in the ‘Drill Process Setup Wizard’ you need to define the scope of the process (what elements you can drill on) – You can change these later. They are actually not that important at present.
  4. Here you need to select the datasource. My Personal Preference is to work within the normal TI Editor window, to do this select ‘other’ as your datasource type and then click ‘Launch Turbo Integrator’
  5. Specify your datasource and query
  6. Save & Close

Making the Drill Process Relevant:

Now I wouldn’t quite say you’re done yet! But assuming you have done the previous steps correctly, the drill should work.

But this is really not very dynamic as it will only run one query for any cell selection that is mapped to this process.

I’m going to share some tips that will make your process a little more dynamic. The first tip is called Variable Expansion this allows you to use the TI Process Parameters within the Datasource Query (You can change the contents of the parameter variable within the prolog before the datasource query executes).

Normal SQL:  SELECT * FROM ledger WHERE Year=’2010’

Relevant SQL:  SELECT * FROM ledger WHERE Year=’?base_year?’

Where “base_month” is a parameter given to the process (the year that your user has drilled on)

For instance, say you would like to make the query search for the account which your user has drilled on.

Relevant SQL:  SELECT * FROM ledger WHERE Year=’?base_year?’ AND Account=’?Rep_Account?’

Making the Process Smart:

The previous step was alright in that it would work if the user clicked on a single account to drill. But what if they clicked on a Consolidated Account? Remember most SQL based systems don’t have Account Tree’s

It’s important to note that the ‘Prolog’ section of a process executes first. Then the data source of a process being loaded/executed.

Now I’m going to quickly show how you could potentially handle a Account Node and behave like a smart process by pulling all ‘N’ Level accounts from under that node.

The following script does this:

  1. Check if the account is a ‘N’ level or a Consolidation
  2. Create a Subset in a safe manner which consists of the children of the Given Account
  3. Create a list of those accounts in the Parameter variable ready for the SQL variable expansion to use it.
  4. Clean up

In this code, we are using ELLEV, have a loop (using WHILE), an IF and a few SUBSET management commands.

IF( ELLEV('Rep_Account',Rep_Account) > 0 );
  DimName = 'Rep_Account';
  SubName = DimName | ' Drill Set';

  IF( SUBSETEXISTS(DimName,SubName) = 1 );
    SUBSETDESTROY(DimName,SubName);
  ENDIF;

  SUBSETCREATEBYMDX(SubName,'{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Rep_Account].[' | Rep_Account | ']}, ALL, RECURSIVE )}, 0)}');
  iIndex = 1;
  sAcct = ' AND ( ACCOUNT = ''' | SubsetGetElementName(DimName,SubName,iIndex) | '''';
  WHILE(iIndex < SubsetGetSize(DimName,SubName) );
    iIndex = iIndex+1;
    sAcct = sAcct | ' OR ACCOUNT = ''' | SubsetGetElementName(DimName,SubName,iIndex) | '''';
  END;

  sAcct = sAcct | ')';
  Rep_Account = sAcct ;
ELSE;
  Rep_Account  = ' AND ACCOUNT = ''' | Rep_Account | ''' ';
ENDIF;

Relevant SQL:  SELECT * FROM ledger WHERE Year=’?base_year?’ ?Rep_Account?

You’ll notice that ?Rep_Account? Stands on its own, this is because the variable should have been populated with “ AND ACCOUNT= …” and this really only needs to be stated once of SQL will spit the dummy.

Mapping the Process

I don’t recommend you leave this job to the Wizard, I think after your process is complete you should make the mapping more specific so that users cannot use ridiculous elements like ‘All Years’ or any other TM1-only dimension elements as the source.

You can edit the mappings by right-clicking the cube -> ‘Drill’ -> Edit Drill Assignment Rules

I’ll leave you with this as it is the exact same as if you were writing a String Rule (Don’t forget the S: ) on a cube without FEEDER; SKIPCHECK;

You might also like