Did you know you can use Planning Analytics’ drill-through feature to access detailed data in another Planning Analytics cube or a configured relational table? This feature can allow for a user to quickly find detailed information underlying a data point which is either in the model in a more detailed cube, or not in Planning Analytics at all.

A cube drill through will allow a user to drill through to another, more detailed cube view, via the drill through viewer. A relational drill through will allow for a user to drill through to a data source, ODBC or flat file, via the table viewer. The cube drill through viewer is interactive while the relational table view is not.

Whether you are setting up a drill through to a cube or a relational data source, there is prerequisite configuration that must be completed.

How to setup a drill through:

  • Create or Edit the drill assignment rule on the cube you intend to drill from (generally a reporting cube)
    • Drill Assignment rules behave the same as normal Planning Analytics rules
    • Define the intersection on the right side of the rule where you want the drill through to be accessible for users
    • Define the string of the drill through process (yet to be created) on the left side of the rule
  • Create the drill process by utilizing the Drill process Setup Wizard
    • Follow the steps and select either a target cube view or relational connection
  • Edit the drill process – this can be done via the drill window or by identifying the control process (identified with the prefix “}Drill_”)
    • If a cube to cube drill has been created, when the process was created a target cube and view was selected. To modify this to be dynamic based on the source intersection you will be required to code a dynamic view on the prolog of the process based upon the parameters passed and use the RETURNVIEWHANDLE(‘cube_name’,’view_name’); function to return the new, dynamically created view.
    • If a cube to relational table drill has been created, when the process was created a SQL statement was created for return. To modify this to be dynamic based on the source intersection you will be required to code a dynamic SQL query on the prolog of the process based upon the parameters passed and use the RETURNSQLTABLEHANDLE function to return the new, dynamically created view.

How to parametrize a drill through:

As outlined above, a drill through in its initial form will return a named view or query, however you may wish to parametrize the drill through to be more exact. An example might be to drill on an order id to see more detail such as the employee and customer involved in the transaction or the need by date.

Parametrized Cube to Relational Table drill through:

To do this with a relational table drill open the process initially created and modify the query, this can be done on the data source tab for simple queries but may require moving the query to the prolog.

The initial process was created with the query on the data source tab:

SELECT [order_id]

      ,[customer_id]

      ,[order_status]

      ,[order_date]

      ,[required_date]

      ,[shipped_date]

      ,[store_id]

      ,[staff_id]

  FROM [BikeStores].[sales].[orders]

This query will return the same value no matter what cell is drilled from. If we want to narrow down the drill to contain only the order_id we are drilling from in the cube, the query can be updated to include a where clause with a parameter.

First notice that in the process initially created there are system generate parameters for the dimensions of the cube:

 

 

 

 

 

 

 

 

 

Note: We recommend setting these to be prefixed with a “p” for parameter and removing any spaces

When the drill process is executed by the user, it will automatically pass the intersection of the drill into the process via these parameters. To update the query, we must take these parameters into account.

This can be done by updating the query to

SELECT [order_id]

      ,[customer_id]

      ,[order_status]

      ,[order_date]

      ,[required_date]

      ,[shipped_date]

      ,[store_id]

      ,[staff_id]

FROM [BikeStores].[sales].[orders]

WHERE [order_id] = ‘?pOrderID?’

Now we can test and see that we only return the specific order identified in the drill intersection.

As mentioned previously, more advanced queries may require moving the use of the prolog tab instead of the data source tab. If this is required leave the initial, simple, query on the data source tab and write the more advanced query on the prolog along with the using the DatasourceQuery function as seen below.

Parametrized Cube to Cube drill through:

To parameterize a cube to cube drill though open the process initially created and create a dynamic target view, this will be done prolog tab.

The initial process what created with a named target view defined. This view returns the same values no matter what cell we drill from. If we want to narrow down the drill through to contain only the order_id we are drilling from in the cube, the view must be created a view to take into account the parameter passed.

First notice that in the process initially created there are system generate parameters for the dimensions of the cube:

Note: We recommend setting these to be prefixed with a “p” for parameter and removing any spaces

When the drill process is executed by the user, it will automatically pass the intersection of the drill into the process via these parameters. To create the new view, we must take these parameters into account.  This can be done by creating a view on the prolog tab and reassigning the view to the drill on the epilog.

Sample Prolog:

Sample Epilog:              

Now we can test and see that we only return the specific order identified in the drill intersection.

A few setup notes to be mindful of:

  • Drill rule assignment’s will always be string rules.
  • Drill assignment rules will identify the drill process name, without the prefix “}Drill_” on the left side of the rule.
  • Drill through viewer for relational tables can be exported or saved to .csv file.
  • Drill through is available in Exploration Views, Quick Reports, Custom Reports, and Dynamic Reports.

Creating the initial Dill Assignment Rule:

Creating the initial Drill Process:

Editing the Drill Process: