Page 1 of 1
ODBC Drillthrough query for consolidated elements
Posted: Tue Feb 22, 2011 12:42 am
by telula_w
Hello,
I am trying to write the prolog to deal with scenarios where the consolidated element is selected for drill through.
For example, the product hierachy is
Total Product
- AA
-1101
-1401
-AB
- 2301
-2411
-AC
-3813
-3555
Sql query = Select * from dbo.GL where product ='?pproduct?'
I want the results for the output for the query to be like
If pproduct ='AA', select * from from dbo.GL where product In ('1101','1401')
Is there a way to list the products in the 'In' statement programatically?
Thanking you in advance!
Re: ODBC Drillthrough query for consolidated elements
Posted: Tue Feb 22, 2011 1:24 am
by kpk
Why do not you create a view or table with an extra field "product_group" as a target to drill into?
When the product level is 1 you can use this field when 0 you can use the product.
Re: ODBC Drillthrough query for consolidated elements
Posted: Tue Feb 22, 2011 1:48 am
by telula_w
I have thought of that idea but we have several hierarchies for different business groups so 1 product could belong to many hierarchies.
Re: ODBC Drillthrough query for consolidated elements
Posted: Tue Feb 22, 2011 3:59 am
by tomok
The solution that Peter gave you is really the best practice way to accomplish this. Trying to do it via the IN construct in SQL "might" work but 1) you would have to create some sort of looping deal in the Prolog to build your IN section and 2) it would always be subject to breaking if your IN statement got too long (because of too many products inside the parentheses). If you have multiple hierarchies then you'll need a column for each in the view.
Re: ODBC Drillthrough query for consolidated elements
Posted: Tue Feb 22, 2011 8:11 am
by Michel Zijlema
In a situation where TM1 users update dimension hierarchies (consolidations) in TM1 directly, a flexible solution I regularly use is having TI processes create/update dimension tables in the relational DB, where in these dimension tables each consolidated element is listed against all of its N-level children.
The drill-through selection will select on the consolidated element in the dimension table, where the dimension table is joined on the fact table through the N-level elements field. This will automatically filter out the relevant set from the fact table.
Attention point is that you need to run the relevant dimension update process after changing a dimension structure to keep TM1 and the relational DB in sync.
Michel