Drill-through to a cube view

[Wim Gielis]

Hello all,

I am experimenting with setting up a drill-through to a cube view, in Architect. 2.0.5 as the PA version.
The scenario is that any cell can be drilled upon and the resulting view contains all descendants of the chosen cell. Like that, we see all numbers that make up the initial number.

If the drill view is pretty fix and does not change, this exercise is rather easy. It's more difficult, however, in these 2 cases:
1. the user and/or the time of the drill should be added as a suffix to the names of drill views and subsets
2. we want to script the view because we maybe want to bring the subsets with only 1 element to the titles. Having these subsets in rows or columns does not add a lot IMHO.

I can do point 2 but I am stuck with point 1. I saw this page: https://lodestarsolutions.com/tm1-drill ... t-working/

The page contains code to script the view and subsets with names that dependent on the user.
I cannot get this code to work. For me the function RETURNVIEWHANDLE does not seem to pick up the new view name.

Example: the drill is created based on view 'Level 0' on cube 'My_test_cube', data source type is 'cube view'. In the Prolog tab we script view 'Level 0_Wim_20181008 033650' and its associated subsets. The latter view should be picked up as the drill view, not the first view. To this end, I used code like:

Code: Select all

If( 'TM1' @= 'slow' );

#****Begin: Generated Statements***
RETURNVIEWHANDLE('My_test_cube','Level 0');
#****End: Generated Statements****

EndIf;

View = 'Level 0_Wim_20181008 033650';
RETURNVIEWHANDLE('My_test_cube',View);

Is this supported ? How do you make drill views dependent on for example user or time or a random number ?

Thanks !

[Wim Gielis]

The drill view statement is:

Code: Select all

RETURNVIEWHANDLE( pCube_Tgt, c0 );

To make the example more visible, here I added 3 screenshots:

1: the view selection dropdown is empty and the drill view is not selected. Moreover, the 2 row dimensions contain the correct subset (with name and timestamp in it), but the subsets in the titles do not show the subsets I created (with the same variable name).

1.png (16.98 KiB) Viewed 19568 times

2: to be clear, the drill view is created

2.png (22.66 KiB) Viewed 19568 times

3: here I selected the drill view. All subsets contain the correct variable name.

3.png (21.68 KiB) Viewed 19568 times

Thank you

[lotsaram]

Hi Wim,

This bug seems to have been there for a while. IMO it's harmless as it is just a display issue. As long as the user is directed to the correct target view layout with the correct element son filter and subsets on rows & columns that's what really matters.

[Wim Gielis]

Hello Lotsa, thank you for confirming it’s a bug. However I’m not sure it’s harmless. The subsets in the titles are not picked up. I will test again today to exclude errors on my part. I will also test in a slice with DBRWs.

[Wim Gielis]

It is clearly a bug. The view is not shown, while still be created correctly. The subsets in the rows and columns are shown, but not those in the titles, while still be created correctly. In Architects / Perspectives cube viewer, we could still select the view from the dropdown, even though this should not be necessary.

But a drill in Excel Perspectives from one Active form to another, just comes out as incorrectly as in the cube viewer. There, you can't select a different view or subsets in the SUBNM's.

This happens in PA 2.0.5.

I wanted to automate in TI the entire setup of a drill-through process to a level 0 filtered view, whereby subsets can be laid out in the titles if we only have 1 element (like year or month). I don't need to have this subset in the rows for only 1 element. Seems like I am stuck on showing the view is created in the drill.

[Steve Rowe]

AFAIK you've never been able to change the name of the view being opened, when we've wanted to heavily customise the view the user is opening you can change the subsets (and the postion?not sure I've tried) to something specific to the user at runtime.

This is not normally an issue in a multi-user environment, except if
1. Person A performs a drill and gets their version of the "Drill View"
2. Person B performs a drill and gets their version of the "Drill View"
3. Person A reloads their view (not recalculates) and gets Person Bs view.

Not something that we've really seen outside of lab conditions.

Cheers,

EDIT : Changed the words so it made sense!

[Wim Gielis]

Okay, thanks Steve. So I tried to do things that were not (meant to be) possible

However, your scenario does not strike me as very improbable nor impossible.
It only takes a couple coworkers working on the same data (cubes) and using the drill down in a non-trivial way - sooner or later one of them is going to save the generated drill view (no Save as but a Save).
But in the end it's not a big deal, I agree. Users need to have admin access to be able to overwrite a public view, another argument to factor in.

Anyway, I parameterized my generic process so that one can choose - fixed view names or dependent on user / time.
We will have to live with the negative side effects.
I will try to use the REST API to automate the drill process generation, that will take out the last manual bit of work

[Steve Rowe]

It would of course be great if we could change the view name in the returnviewhandle function. As I often find the last 10% of the design and implementation of the functionality can remove large amounts of possible use cases for the functionality.

DBRW type formula and that can access hierarchies being the current prime example...

[lotsaram]

It would of course be great if we could change the view name in the returnviewhandle function. As I often find the last 10% of the design and implementation of the functionality can remove large amounts of possible use cases for the functionality.

Well you can. Our standard approach (workaround) is the following ...

Code: Select all

If( 1 = 0 );
#****Begin: Generated Statements***
RETURNVIEWHANDLE('My_test_cube','Level 0');
#****End: Generated Statements****
EndIf;

ReturnViewHandle( cCubeTgt, cViewTgt );

DBRW type formula and that can access hierarchies being the current prime example...

No solution as yet for being able to address multiple hierarchies within a dimension. But you can actually already access hierarchies through DBRW formulas (even in Perspectives if the server is on v11). In a slice all you need to do is replace the Element reference with Hierarchy:Element and the correct value is retrieved. (Note: for some reason it only works with auto-calc and not manual calc. Necessity is the mother of invention as they say ). You can also use a similar trick to access alternate hierarchies in perspectives even though they aren't visible in server explorer; in a SUBNM formula simply replace the server:dimension reference with server:dimension:hierarchy and on double-click you will be browsing the alternate hierarchy and able to select elements from it.

[Steve Rowe]

!
Of all the combinations we tried I don't think we tried that one.
TYVM!

Also thanks for the info on DBRW, shame we need to find this stuff out so indirectly. I get that this works if you want to switch from 1 hierarchy to another, does it work if you want to reference multiple hierarchies from the same dim?

i.e.
Vanilla
=DBRW("Cube" , H1:D1, H1:D2)

I get that this would work
=DBRW("Cube" , H1:D1, H2:D2)

but does this?
=DBRW("Cube" , H1:D1, H1:D2, H2:D2)
which is the key USP of hierarchies.

[Wim Gielis]

!
Of all the combinations we tried I don't think we tried that one.
TYVM!

Hi Steve,

But I did, see the first post of this topic. Instead of If( 1 = 0 ); I used:

Code: Select all

If( 'TM1' @= 'slow' );

and luckily this gives False as the result

i experimented with a Datasource type equal to None, or to Cube view.

If it is None, PAW and PAX disable the drill-through, it appears.

In both cases, getting the drill fully dynamic with named subsets and views, does not work.
The view and subsets are created but we need to select the view from the dropdown in the cubeviewer (Architect).
Other clients will have equal or worse behavior.

Regarding the DBRW questions, the variant with a variable number of arguments to the DBRW, is not implemented yet.
Anyone with an outlook or roadmap, please post it !

[PavoGa]

Have run head-on into this with PA-2.0.05.

Is it fixed in any of the subsequent releases? Did not have this problem in 10.2 using the technique of building custom cube views and by-passing the default Epilog ReturnViewHandle.

UPDATE

In 10.2, the method I used was building subsets and a view with a date/time and random number sequence in the name like "tmp_processname_28094846_09410." Used IF (1=2); ENDIF; in the epilog to skip the default generated ReturnViewHandle, adding it with the custom view after the ENDIF to call the temporary named view. Worked great.

So then ran into the behavior that Wim described. In my specific case, the cube has nine dimensions. The drill puts seven as title dimensions, one row and one column dimension. The first two title dimensions were coming through without their associated subsets. Rearranged the deck chairs, but the view would open with these two dimensions without their subsets regardless of position. Turns out this seemed somewhat a red herring, as it was noticed the custom view name was not being returned by ReturnViewHandle; selecting the view name returned the view as expected. Just what Wim described.

So, created a view on the cube called "Drillthrough." Used "Drillthrough" as the view name in the drill and did the check existence, destroy, create, assign subsets, etc.

ReturnViewHandle now returns "Drillthrough." But the same two dimensions are missing their associated subsets just like they were doing when the custom view with a temp name was not being returned before.

Really odd. Not sure what the value of ReturnViewHandle is now. If I find some tweak that is causing this problem with these two dimensions will provide that information.

[gtonkin]

I normally copy the ReturnViewHandle below the generated section then go to the data source and set to none.

I generally only need the parameters so that I can build and assign the view in Prolog. No need for Metadata and data. YMMV

[PavoGa]

Tried that and the drill went back to the behavior of not returning the view name to the viewer and the same two dimensions without their associated subsets as well.

However, an update on rebuilding the pre-existing view and calling it with ReturnViewHandle: it does return the view (sans the two subsets for the aforementioned dimensions) and clicking the Reload View button displays the view as it is built in the Prolog of the drill.

So, it seems that the drill allows one to update a pre-existing view and have a proper display of it with an additional click. Not a very good thing if multiple people are working in the same cube and using the drill, a very real possibility.

Building a new view with a temporary name results in the cube without the new view in the cube viewer.

[PavoGa]

This has been awhile, but needed to update it (got a question on it today). If the subsets and the view exist and are updated, not created fresh, the drill through works fine with customized subset and view definitions. To avoid user conflicts, we added views on each cube and subsets on dimensions with this as a naming convention:

zdrillthrough_clientname.

The drill updates the client's objects and it works. Of course, if a client were to drill on different cubes at the same time that share dimensions, but different members depending on the source drill cell (likely), then this method is shot to pieces.

[a1m80t]

I normally copy the ReturnViewHandle below the generated section then go to the data source and set to none.

I generally only need the parameters so that I can build and assign the view in Prolog. No need for Metadata and data. YMMV

Bingo, this did it. The view name will name will show up as "blank" but the subsets that were created at run-time will be there. Old drill sessions are unaffected by new drill sessions so it seems that the run-time generated view is indeed being used. This was quickly tested on Architect/PAX/Web/PAW using PA 2.0.8/PAW 2.0.47

1. Create a "Placeholder" drill view
2. Create a "Drill Process" using the "Placeholder" view
3. Edit the "Drill Process" Data Source to "None"
4. Define the drill process in prolog, You may have to play with ViewRowDimensionSet/ViewTitleDimensionSet/etc
5. Use "ReturnViewHandle" in epilogue pointing the to the run-time generated view with run-time subsets.

[Wim Gielis]

I normally copy the ReturnViewHandle below the generated section then go to the data source and set to none.

I generally only need the parameters so that I can build and assign the view in Prolog. No need for Metadata and data. YMMV

Bingo, this did it. The view name will name will show up as "blank" but the subsets that were created at run-time will be there. Old drill sessions are unaffected by new drill sessions so it seems that the run-time generated view is indeed being used. This was quickly tested on Architect/PAX/Web/PAW using PA 2.0.8/PAW 2.0.47

1. Create a "Placeholder" drill view
2. Create a "Drill Process" using the "Placeholder" view
3. Edit the "Drill Process" Data Source to "None"
4. Define the drill process in prolog, You may have to play with ViewRowDimensionSet/ViewTitleDimensionSet/etc
5. Use "ReturnViewHandle" in epilogue pointing the to the run-time generated view with run-time subsets.

Do you create new subsets and a new view ?
Or populate the subsets of an existing placeholder view ?

[a1m80t]

I normally copy the ReturnViewHandle below the generated section then go to the data source and set to none.

I generally only need the parameters so that I can build and assign the view in Prolog. No need for Metadata and data. YMMV

Bingo, this did it. The view name will name will show up as "blank" but the subsets that were created at run-time will be there. Old drill sessions are unaffected by new drill sessions so it seems that the run-time generated view is indeed being used. This was quickly tested on Architect/PAX/Web/PAW using PA 2.0.8/PAW 2.0.47

1. Create a "Placeholder" drill view
2. Create a "Drill Process" using the "Placeholder" view
3. Edit the "Drill Process" Data Source to "None"
4. Define the drill process in prolog, You may have to play with ViewRowDimensionSet/ViewTitleDimensionSet/etc
5. Use "ReturnViewHandle" in epilogue pointing the to the run-time generated view with run-time subsets.

Do you create new subsets and a new view ?
Or populate the subsets of an existing placeholder view ?

This works on new subsets/views on rows and columns with some stipulations. After further testing, TM1 doesn't actually pull up the new view during drill-through but rather takes some of its parameters. Because the Drill-Through is not actually a saved view, there isn't a reload view button but there shouldn't be interference issues between old/new successions or between multiple users (still testing).

Subsets are only assigned to title dimensions of different dimensions between the source and target. If the dimensions are the same, It seems subsets are ignored regardless if they are pre-created or not. I only spent 30 minutes on this so part so free to experiment.

Edited:
For corrections

[PavoGa]

This works on new subsets/views on rows and columns with some stipulations. After further testing, TM1 doesn't actually pull up the new view during drill-through but rather takes some of its parameters. Because the Drill-Through is not actually a saved view, there isn't a reload view button but there shouldn't be interference issues between old/new successions or between multiple users (still testing).

Not exactly sure what it means that a Drill-Through is not actually a saved view. Are you saying it is not creating a view and assigning subsets to it?

Subsets are only assigned to title dimensions of different dimensions between the source and target. If the dimensions are the same, It seems subsets are ignored regardless if they are pre-created or not. I only spent 30 minutes on this so part so free to experiment.

If I recall correctly, this was not our experience, but we are not on 2.0.8 yet either. The only way we could get drills to work as they did in 10.2 was the view and the subsets had to pre-exist, then it worked perfectly. To be clear, it was the objects that had to exist and the drill process just updated them. It does seem you found one thing that was puzzling, why some title dimensions worked and why some did not: the common dimensions between source and target cube.

Also, one of our guys found yesterday that the API for drill through would not work using an index for the title dimension element, but does with the element name. Do not know if there is some commonality there or not, but will try that as well.

Will do some more testing, hopefully in the morning.

[a1m80t]

Not exactly sure what it means that a Drill-Through is not actually a saved view. Are you saying it is not creating a view and assigning subsets to it?

There isn't a view name listed in any of the TM1 user interfaces. However, you basically have full control of the Drill-Through "view" that it creates, except with the limitations of the Title dimensions as mention earlier. It wouldn't surprised me If this is unique to PA 2.0.8.