TM1 Forum

Hello,

I need to perform a drill through on consolidation and leaf elements as well. I have a cube view with 5 dimensions, a measure dimension and a time dimension as well. Drill through should be applicable to the elements of the Measure dimension for both consolidated and its child elements (any level) across any combination of consolidated or leaf elements of the remaining dimensions as well.

Eg: Measure Dim element:

Dim1 Dim2 Dim3
(+)All Leaf element Any level element

Measure \ Time: Jan-2001 Feb 2001....
(+)A
(-)B
b1
(-)C
c1

The data is been retrieved from SQL database. I have created a drill process and am storing the data in a text file. However, i get all the records (in millions) for the drill through applied at any level, be it consolidated level..level 2 or any of its child, which should not be the case.

Is there any way out that we can define in the Data tab so as to get the records pertaining to the selected level of dimensions only and not process all the records each time.

Kindly share your knowledge on the same.

Have you bothered to read the documentation about how to create TI drill processes? Are you aware of how you can use parameters (the specific elements from the cell you are clicking on) in the WHERE clause of your drill-thru query to return only the records you want? It's all in the documentation.

Hi tomok,

Yes.
I have read the documentation, applied it and then when am not retrieving the desired results I have posted this query.

If u read properly my post, I have mentioned there that the data after drill through for both consolidation and its child elements comprises of all the records which should not be the case. For child elements, slice of information pertaining to that element ONLY should be retrieved and not all the records should get processed.

I would rather appreciate if u share the condition to be specified in the WHERE clause using parameters.

Richa, to be honest, from your first post, it is hard for me to understand what exactly you have, and what you want to achieve, and probably that is why tomok assumed, what assumed. For me the most confusing is the "Eg." part

Anyway maybe this hint will help you. In your SQL you can write something like:
All you need to do then is to prepare sMonthList, sRegionList... in the TI's Prolog on the basis of what leaf level descendants have elements in your dimensions you made a drill from.

It gets more complicated when you use versions before 9.4, as your variables are limited to 256 length. In such a case modify to:
I used such approach when my dimensions were not bigger than about 500-1000 elements and it works really nice (part where SQL lists are generated worked superfast on 9.1 SP2 U3) and the results from table also came up very quickly (SQL table had about 8000 records per month and was nicely indexed).

Hope you can use this.

I've done this a number of times. My approach was to push the TM1 dimensions to SQL tables, 'inverting' them to link each n level element to all possible ancestors, then build a view linking to the base table. Works well enough. Warning (this is for you Ken ) don't try to select * from the view - you will blow your SQL workspace.

Hi All,

Below jstrygner has written:

"Anyway maybe this hint will help you. In your SQL you can write something like:

Code: Select all
select COL_MONTH, COL_REGION, COL_PRODUCT, ...
from FACT_TABLE
where
COL_MONTH in ('?sMonthList?')
and COL_REGION in ('?sRegionList?')
and COL_PRODUCT in ('?sProductList?')
and ...

All you need to do then is to prepare sMonthList, sRegionList... in the TI's Prolog on the basis of what leaf level descendants have elements in your dimensions you made a drill from."

My question is: in the Prolog how do you setup sMonthList so that the SQL statement can accept multiple parameters? At the moment I have GL.PERNTJ IN ('?Months?') in the WHERE part of my SQL statement.
In the Prolog I have Months = '4,1';. I have hard coded the month numbers just for testing purposes. Just so you know, 1 = Jul, 4 = Oct.

When I drill in Cube Viewer it only returns lines for Months = 4.

Any assistance would be appreciated.

Regards,
Christian

Hi Christian

I think the problem may be in your SQL

Try changing

GL.PERNTJ IN ('?Months?')

to

GL.PERNTJ IN (?Months?)

Assuming that GL.PERNTJ is a numeric.

If this fails, try capturing the SQL that TM1 is sending and past it into the SQL editor of your database and run it there to see what it returns. Check that there is actually data for period 1 in the database.

Regards

Paul Simon