Troubleshooting
Problem
This Technote explains a known limitation of ODBC drill through in Planning Analytics Workspace.
Symptom
An ODBC drill through that works as expected in Architect, Perspectives, and TM1Web fails to return any data when used in Planning Analytics Workspace.
Cause
An ODBC drill though in Planning Analytics passes the context of the cell where the drill though is being performed. This context includes the name of the cube, and the element names that define the cell. When the drill through is performed in Architect, Perspectives, and TM1Web either the element names or the currently displayed alias are passed. In Planning Analytics Workspace only the element name can be passed. This difference is specific to the MDX implementation of the cube viewer in Planning Analytics Workspace.
If alias names (not element names) are used to match rows or columns in the ODBC data source the drill thru will not return data.
Resolving The Problem
To resolve the problem the drill through Ti process should be updated to lookup the expected alias name, and use that alias name in it's SQL query. This can be done in the prolog section of the Ti process. The prolog code of the Ti process is executed before the SQL query.
Consider the following view. The dimension named Numbers includes three elements named 1, 2, and 3. These elements have aliases N1, N2, and N3, which are used in the view.
The ODBC table being drilled into contains the following data.
The drill through Ti process has the following parameters.
The prolog section of the drill through Ti process can be updated to include the following code.
vAlias = ATTRS ( 'Numbers', vNumber, 'N_Value' );
sqlQuery = 'select * from dbo.table where rowID = ''' | vAlias | '''';
DataSourceType='ODBC';
DatasourceQuery=sqlQuery;
The ATTRS function is used to lookup the alias value of the element. This works if either the element name or an alias value is passed as a parameter (vNumber) to this drill process. The drill process will return data if it is run in Architect, Perspectives, TM1Web, and Planning Analytics Workspace.
Was this topic helpful?
Document Information
Modified date:
19 December 2018
UID
ibm10792069