Creating a Drill Process

A drill process is a TurboIntegrator process that defines the detailed data, which opens in a new window. The cube from which a drill-through originates is called the origination cube.

Before you create a drill process, you should be familiar with the data you want to open when drilling from the origination cube.

Steps

  1. Right-click the origination cube in the Server Explorer.
  2. Click Cube, Drill, Create Drill Process.

The first window of the Drill Process Setup Wizard opens.

The wizard displays a table with parameter values for the origination cube and all its dimensions. TM1 uses these parameter values to set up 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.

  1. Click Next.

The second window of the Drill Process Setup Wizard opens.

  1. Select the Datasource Type for the detailed data you want to drill through to from the origination cube.

There are three Datasource Type options.

Option / Description
ODBC / Drills from the origination cube to an ODBC source. The ODBC source must be accessible from the computer on which the TM1 server is running.
Cube View / Drills from the origination cube to a different cube view. You can drill to any cube that resides on the same server as the origination cube.
You can define a cube view data source that exceeds the maximum amount of memory that TM1 can allocate when you access a view. By default, the memory threshold for the MaximumViewSize parameter in the Tm1s.cfg file is 100MB on a 32-bit system, and 500 MB on a 64-bit system.
Important: If you do not specify the maximum view size in the configuration file, TM1 displays an error message when you attempt to drill to the cube view.
Other / Drills from the origination cube to any data source TurboIntegrator supports.

TM1 displays the data source options for the data source type you selected in the wizard.

  1. Define the data source.
  2. For an ODBC data source, you must supply the following information.

Option / Description
Datasource Name / Name of the ODBC data source (DSN) you want to access when drilling from the origination cube.
User Name / Valid user name to log on to the ODBC source.
Password / Password for the user name.
Query / Query that defines the data to return from the ODBC source. Query results data displays in a separate window when you drill from the origination cube.
  • For a Cube View data source, you must supply the following information.

Element / Description
Datasource Name / Name of the view you want to open when drilling from the origination cube. Click Browse to select a view name or create a view.
  • For the Other data source, click Launch TurboIntegrator to define the data source.
  1. Click Finish.

The Save Process As dialog box opens.

  1. Enter a name for the drill process in the Name box.

Note: For best practice, we recommend that you use a drill process name that identifies the origination cube associated with the drill process. For instance, if you create a process to drill from a cube named PriceCube to an ODBC source, you would name the drill process PriceCubeToODBCSource. This type of naming convention makes it easier to identify a drill process name when you edit a drill process, or select from several drill processes associated with a cube.

  1. Click Save.

TM1 saves the drill process as a TurboIntegrator process, but prefixes the name you assigned in step 7 with the string }Drill_. For example, if you save a drill process with the name PriceCubeToODBCSource, TM1 saves the process as }Drill_PriceCubeToODBCSource.

Drill-Through Example (Customized)

This section guides you through the creation of a drill process and drill assignment rule, which allows you to drill from the SalesByQuarter cube to a relational table that is the original source for the cube data. The table contains data at the monthly level, while the SalesByQuarter cube contains data at the quarterly level. By drilling through to the relational source, you can view the underlying detail for the cube data.

Setting Up the ODBC Data Source

The example in this section drills through to an ODBC source (Access database). Before looking at the example, you must set up the ODBC data source.

Steps

  1. Open the Windows ODBC Data Source Administrator dialog box.

The procedure for opening this dialog box varies, depending on the version of Windows you are running. For details, see the Windows Help.

  1. On the System DSN tab, click Add.

The Create New Data Source dialog box opens.

  1. Select Microsoft Access Driverand click Finish.

The ODBC Access Setup dialog box opens.

  1. Type TM1_sample_data in the Data Source Name box.
  2. Click Select.

The Select Database dialog box opens.

  1. Navigate to your \install_dir\Custom\TM1Data\PData\RelationalData directory and select Sales.mdb.
  2. Click OK to exit the Select Database dialog box.
  3. Click OK to exit the ODBC Administrator dialog box.

The Access database named Sales is now available as an ODBC source. The example drill processes use this ODBC data source.

Creating a Drill Process

You can now create a drill process for the SalesByQuarterCube cube. The drill process defines an ODBC data source as the detailed data that you can view when you drill from the SalesByQuarterCube cube.

Steps

  1. In the Server Explorer, right-click SalesByQuarterCube.
  2. Click Drill, Create Drill Process.

The Drill Process Setup Wizard opens. The table contains the default parameters for the drill process.

  1. Select the Year parameter value.
  2. Click Select Element.

The Subset Editor opens.

  1. Select 1 Quarter and click OK.
  2. Repeat steps 3 through 5 to change the Gross Margin% parameter value to Units.

The table in the wizard should now appear as follows:

  1. Click Next.

The second screen of the wizard opens.

  1. Select ODBC as the Datasource Type.
  2. Click Browse next to the Data Source Name box and select TM1_sample_data.
  3. Enter admin as the User Name.
  4. Enter the following query in the Query box.

This query is specific to the sample Access database. It uses IIF functions that are unique to Access and cannot be used against any other database.

SELECT ActvsBud, Region, Model, Account1, Month, Data

FROM Sales WHERE ( ActvsBud = '?actvsbud?' AND Region = '?region?'

AND Model = '?model?' AND Account1 = '?account1?' AND

(

IIF( '?quarter?'= '1 Quarter', Month = 'Jan' OR Month

= 'Feb' OR Month = 'Mar',

IIF( '?quarter?'= '2 Quarter', Month = 'Apr' OR Month

= 'May' OR Month = 'Jun',

IIF( '?quarter?'= '3 Quarter', Month = 'Jul' OR Month

= 'Aug' OR Month = 'Sep',

IIF( '?quarter?'= '4 Quarter', Month = 'Oct' OR Month

= 'Nov' OR Month = 'Dec',

IIF( '?quarter?'= 'Year',TRUE,TRUE)))))

)

)

Note: Be sure to enclose the references to the TM1 parameter names in question marks (?).

  1. Click Finish.

The Save Process As dialog box opens.

  1. Save the process as RELATIONALTABLE_SalesByMonth.

The new drill process displays as an available process on the server. Note that the process name includes the }Drill_ prefix, indicating that it is a drill process.

You can now create a drill assignment rule to make the ODBC data source available from the SalesByQuarterCube cube.

Creating a Drill Assignment Rule

Follow these steps to create a drill assignment rule that makes the RELATIONALTABLE_SalesByMonth process available from the SalesByQuarterCube cube.

Steps

  1. In the Server Explorer, right-click SalesByQuarterCube.
  2. Click Drill ,Create Drill Assignment Rule.

The Rules Editor opens.

  1. Enter the following rule in the large box.
  2. ['Year'] = S:IF( ( ELLEV( 'actvsbud', !actvsbud ) = 0

) &

( ELLEV( 'region', !region

) = 0 )

( ELLEV( 'model', !model

) = 0 )

( ELLEV( 'account1', !account1

) = 0), 'RELATIONALTABLE_SalesByMonth', '' );

[] = S:IF( ISLEAF= 1, 'RELATIONALTABLE_SalesByMonth',''

);

This rule indicates that the RELATIONALTABLE_SalesByMonth drill process will be executed when you select the Drill option from a cell that is either:

  • Identified by the Year element and 0-level elements from all other dimensions
  • Identified by all 0-level elements
  1. Save the rule.

You can now test the drill-through functionality for SalesByQuarterCube cube.

Viewing the Drill-Through Example

Follow these steps to view the result of the drill process and drill assignment rule.

Steps

  1. Open the Drill_relational view of the SalesByQuarterCube.
  2. Right-click the cell at the intersection of Units and Year.
  3. Click Drill.

An extract from the TM1_sample_data data source opens, displaying the monthly detail for the cell.

  1. Click OK to close the window.
  2. Drill through other cells in the Drill_relational view to view the monthly detail. You should be able to drill through any cell in the view.
  3. Change the Region title element from Germany to Europe.
  4. Right-click any cell in the view.

Note that the Drill option is not available. This is because the drill assignment rule for SalesByQuarterCube indicates that the Drill option is only available for cells identified by the 0-level elements or for the cells identified by the Year element and 0-level elements. Europe is a consolidation, so the Drill option is not available.

Creating a Drill Assignment Rule

A drill assignment rule is a TM1 rule that links cube cells with related detailed data. As indicated above, the related data can be a cube view, ODBC source, or any other data source accessible through TurboIntegrator.

Steps
  1. In the Server Explorer, select the origination cube for which you want to create a drill assignment rule.
  2. Click Cube, Drill, Create Drill Assignment Rule.

The Rules Editor opens.

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

  1. Click Area to define the cube cells (area)you want to associate with detailed data.

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

  1. To define the area, do one of the following:
  2. To define the area as the entire cube, click OK.
  3. 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.
  4. Click Equal.
  5. Click String.
  6. Enter the name of the drill process enclosed in single quotation marks to define the detailed data you want to associate with the area. For example, enter 'PriceCubeToSource'.

Caution: Do not include the }Drill_ prefix in a drill process name. For example, enter 'PriceCubeToSource' for a drill process named }Drill_PriceCubeToSource.

You can associate more than one drill process with an area. Enclose all drill processes, separated by commas, within a set of single quotation marks.

Note: You can also use conditional logic or other functions to return the name of a drill process.

  1. Click Semicolon.

The semi-colon indicates the end of a TM1 rules statement.

  1. Click Save.

You can now drill through to detailed data for which you have created a drill process and a drill assignment rule.