TM1 Forum

Hi guys, ok just a couple of basic ones... sorry for the noob questions...

1/ I'd like to setup a Cube to Cube Drill, where the Drill Cube is the same as the Source Cube, but the criteria is matched to the intersection of elements the user has clicked on... but am uncertain of how to construct the prolog in the TI Drill process...? eg not sure how to make it dynamic apart from creating user specific views..?

2/a random question - is it possible to setup a Drill from a graph in Excel (making it dynamic as per above depending on what point in the graph they click)? ...I guess if it is possible you would need to use some VBA script...?

Thanks!

Matt

P.S. am using tm1 v9.4.1 ...no web module

Hi,

I think you should read the tm1 developer guide on how to build Drill Downs to get started.

I found that it was a good initial guidance to identify the steps to take in order to implement drill downs.

After that you can apply the relevant customisations in the drill process depending of you target in terms of drill interactions / destination.

See below extract from Page 66 of Developer Tm1 guide.

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.

Hope this helps,

Hi Olivier,
thanks for the response - I am familiar with the drill process for ODBC, but uncertain on how to setup the cube to cube drill e.g. I can set it up to spawn a fixed cube view, but how to make it dynamic so that it references the criteria selected by the user in the source cube view?

...so far am not sure if there is another way to do this besides assigning a custom subset to a default view each time...?

Matt

Hi Matt,

Just give it try, it ought to handle the dynamic part for you. From memory only the page headers of the destination view are dynamic based on the source view.
If you want therow and column headers to be dynamic based on the source too then you will need to create subsets on the fly and put them in the view.

Cheers,

thanks for the tips guys, I managed to get it working by assigning subsets to each dimension in the view... but am still curious to know if there's a more efficient method of making the cube to cube drill dynamic...

M.

fleaster wrote:thanks for the tips guys, I managed to get it working by assigning subsets to each dimension in the view... but am still curious to know if there's a more efficient method of making the cube to cube drill dynamic...

The way cube drills work is that the coordinates of the cell being drilled from are stored as parameters in the drill process. In the target view any matching dimensions that are used as title elements (context filters) will automatically be assigned the parameter values. Usually the row and column dimensions would be static so you wouldn't need to assign and dynamically rebuild the subset in the prolog of the drill process.

However there are plenty of occasions where you might want to dynamically assign rows or columns, most commonly in situations where you are drilling from a consolidation and want to display the children (either within a cube or from a summary cube that doesn't have the detail to a more granular cube.) You should only need to create and assign (single element) subsets for the title dimensions in situations where you have dimensional asymmetry between the source and target. Usually you would do this via some sort of attribute mapping.

Note: In 9.5.1 there is a bug where cube to cube drill-through does not work in the Perspectives/Architect cube viewer UI (in that the title elements do not automatically change and assume the parameter values) but the drills do work in TM1 Web and in Excel from a DBRW.

fleaster wrote:... is it possible to setup a Drill from a graph in Excel (making it dynamic as per above depending on what point in the graph they click)? ...I guess if it is possible you would need to use some VBA script...?

I'm interested in the 2nd part of your question which seems to have gone by the wayside. Are you still interested in this aspect? I think it would be possible but it would require a fair amount of VBA coding ...

(A) OK, tell me what I could have improved with the cube drill setup:

1/ GL Cube Drill rule assignment: if 'Account' <= Level 1, then refer to ODBC drill process to subledger database (this part works fine); otherwise refer to Cube View drill process to GL Cube View 'Sys_DrillView'

2/ GL Cube Drill process:
(i) reference GL Cube View: 'Sys_Drill View'
(ii) assign all elements in TI to type "Other"
(iii) assign a subset to each dimension in 'Sys_Drill View' based on user selection

3/ when user drills on GL Cube where Account>Level 1, it should spawn 'Sys_Drill View', which is filtered by their selection

If anyone has any tips or can provide some sample code that would be great

=================

(B) re: Drilling into a Graph - was just brainstorming if someone has a Dashboard report w/ graphs etc, having the ability to click into a graph and open up an Analysis window would be kind of cool... haven't thought through the VBA logic yet, but just wondering if anyone has managed to do it successfully yet...

Am also wondering is this sort of function available in other packages e.g. SAP...?

Hi there,

A / maybe you could post your drill assignment rules and some infos on your cube dimensionality to get some feedback on that ?
All I can say from here is that the concept looks right... "define the trigger of a drill on the base of a data perimeter, element level or so"

B / I have seen it done in VBA, but the amount of code involved is quite important.
It also comes out the box in Tm1 web ( drill down in dimension hierarchies or drill through according to set up rules / processes).
You simply have to open a view, chart it and then right click the items to drill on...similarly to what you do in a view.

fleaster wrote:(A) OK, tell me what I could have improved with the cube drill setup:

I think I mentioned this already but to spell it out. You don't need to dynamically assign or change a subset for each and every dimension. Any shared dimensions that are used as title in the destination view will be set automatically, this is part of the built in functionality of a drill.

lotsaram wrote:I think I mentioned this already but to spell it out. You don't need to dynamically assign or change a subset for each and every dimension. Any shared dimensions that are used as title in the destination view will be set automatically, this is part of the built in functionality of a drill.

That is the behaviour I experienced all the time while using it on different versions except ... one of my implementations. I first developed it on 9.5.1 and it was working. After migrating to 9.5.2 it lost it and I had to rebuild and set also elements in title dimensions. I am not sure if it was pure 9.5.2 or with any HF/FPs - the server was AIX (not sure if setting title dimension elements is done on server or client side while performing drill to another view).