Problem with Drill-Through

[RabbitSlava]

All greetings.

I have a problem with Drill-Through.

In model ТМ1 3 cubes

1st cube Test1
dimension: CFO1, Item1, Time

2nd cube Test2
dimension: CFO2, Item1, Time

3rd cube Test3
dimension: CFO1, Item1, Time

Test2 A source of data for Test1 and Test3

If I do Drill-Through from Test1 in Test2 Drill-Through works incorrectly on dimension CFO (does not transfer CFO Test1 in CFO Test2)
cube Test1 and cube Test3 differ dimension: CFO,
Elements in dimension: CFO1 and CFO2 partially coincide!!!

If I do Drill-Through from Test1 in Test3 Drill-Through works correctly on dimension CFO (transfers CFO Test1 in CFO Test2)
cube Test1 and cube Test2 coincide dimension: CFO

How to do Drill-Through in cubes with different dimension, but elements in differing dimensions coincide?

[paulsimon]

Hi

If you edit the Drill Process you will see that it has a series of parameters, once for each element of each dimension in the source cube.

The Drill Process returns a View on the Destination Cube at the end.

You need to set up a View in the Prolog using TI functions. See the TM1 Reference guide help for a list of these.

The general process is

First see my earlier tip about appending the name of the user to the view name to make it unique

Check to see if your Drill Thru View exists on the Destination Cube

If the View exists Destroy it

Create the View

For each Subset, if the SubsetExists DeleteAllElements otherwise Create it

(Subsets will also need to have the user name appended if you need them to be unique to the user)

Insert in to the subset the element given by the parameters. As you are now have control, you can insert your CFO1 element in to a subset on the CFO2 dimension. So long as the element exists there, the view will work

Assign the Subset to the View for each Dim.

Set the Row, Column and Title Dims

Set the Title elements

Suppress Display zeros (make sure you select Display rather than Extract as they are different

The View is now set up.

In the Epilog

I generally disable the standard View return, as this won't work if you have added a user name.

I do this by inserting IF( 3=5); just above the GENERATED STATEMENTS area and an ENDIF; just below it.

Then you can put in your own RETURNVIEWHANDLE(vCube,vView); statement to return the View that you created on the Prolog.

A bit involved but once you get it working it works very well.

One quirk I found in 9.1 is that it seems to hide the RETURNVIEWHANDLE every time you re-open the Drill Process and you need to put it back in again, so I generally had a comment out version of the RETURNVIEWHANDLE statement just above the one I added in case it got lost.

Regards


Paul Simon

[jstrygner]

To be honest I was surprised your second drill works without any additional coding (we learn all the time). But you definitely need such a coding in your first drill.

You can make your first drill work by setting in TI drill process (Test1ToTest2) in the Prolog tab following code:

Code: Select all

ElementIndex=DimIx('CFO2', CFO1);
ViewTitleElementSet('Test2', 'Default', 'CFO2', ElementIndex);

I hope I did not mistake, as I did not reproduce your case, but even if, I think you'll get the idea, especially after looking in reference guide what exactly ViewTitleElementSet does.

You just need to decide what to do in a case when your CFO1 dimension has an element that does not show up in the CFO2 dimension.

Generally speaking you can manipulate the view all the way in the Prolog tab (switch dimensions layout, rows, columns and title dimensions subsets, nest dimensions and so on).

HTH

[jstrygner]

After Submitting I see Paul also shared his hints

Just two more things I forgot to mention in my earlier post:
1. In a real life I would not use the Default view as a Drill Target (and Paul gave some nice tip here with view designed for a user).
2. Nice work with all of your screenshots, I am impressed

[RabbitSlava]

All many thanks.

[ioscat]

Good day.
In drill process all parameters names are multi-word: Sale Measures, Price Multiplier, etc.
So I cannot use them in the Prolog/Meta/Data/Epilog tabs. How to use drill parameters to built my own view to drill into it?

[Gregor Koch]

You could just strip off the spaces (blanks) and use that. E.g salesmeasures should work.

[ioscat]

Thanks a lot. Dummy question)