Drill-through to a cube view

Wim Gielis
MVP
Posts: 2289
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Drill-through to a cube view

Post by Wim Gielis » Mon Oct 08, 2018 1:41 am

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 !
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Wim Gielis
MVP
Posts: 2289
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Drill-through to a cube view

Post by Wim Gielis » Mon Oct 08, 2018 3:07 am

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
1.png (16.98 KiB) Viewed 6717 times

2: to be clear, the drill view is created

2.png
2.png (22.66 KiB) Viewed 6717 times

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

3.png
3.png (21.68 KiB) Viewed 6717 times



Thank you
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

lotsaram
MVP
Posts: 3330
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Drill-through to a cube view

Post by lotsaram » Mon Oct 08, 2018 9:27 am

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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Wim Gielis
MVP
Posts: 2289
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Drill-through to a cube view

Post by Wim Gielis » Mon Oct 08, 2018 9:51 am

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Wim Gielis
MVP
Posts: 2289
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Drill-through to a cube view

Post by Wim Gielis » Mon Oct 08, 2018 12:48 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

User avatar
Steve Rowe
Site Admin
Posts: 1969
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Drill-through to a cube view

Post by Steve Rowe » Mon Oct 08, 2018 2:10 pm

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
MVP
Posts: 2289
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Drill-through to a cube view

Post by Wim Gielis » Mon Oct 08, 2018 3:14 pm

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

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 :-)
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

User avatar
Steve Rowe
Site Admin
Posts: 1969
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Drill-through to a cube view

Post by Steve Rowe » Tue Oct 09, 2018 8:30 am

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
MVP
Posts: 3330
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Drill-through to a cube view

Post by lotsaram » Tue Oct 09, 2018 1:09 pm

Steve Rowe wrote:
Tue Oct 09, 2018 8:30 am
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 );
Steve Rowe wrote:
Tue Oct 09, 2018 8:30 am
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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
Steve Rowe
Site Admin
Posts: 1969
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Drill-through to a cube view

Post by Steve Rowe » Tue Oct 09, 2018 2:45 pm

!
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
MVP
Posts: 2289
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Drill-through to a cube view

Post by Wim Gielis » Tue Oct 09, 2018 4:35 pm

Steve Rowe wrote:
Tue Oct 09, 2018 2:45 pm
!
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 :lol:

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 ! :D
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

User avatar
PavoGa
Community Contributor
Posts: 387
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Drill-through to a cube view (EDIT)

Post by PavoGa » Thu Feb 28, 2019 2:40 pm

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.
Ty
Cleveland, TN

User avatar
gtonkin
MVP
Posts: 743
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Drill-through to a cube view

Post by gtonkin » Thu Feb 28, 2019 6:50 pm

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

User avatar
PavoGa
Community Contributor
Posts: 387
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Drill-through to a cube view

Post by PavoGa » Thu Feb 28, 2019 7:32 pm

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.
Ty
Cleveland, TN

User avatar
PavoGa
Community Contributor
Posts: 387
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Drill-through to a cube view

Post by PavoGa » Tue Oct 22, 2019 2:23 pm

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.

:(
Ty
Cleveland, TN

a1m80t
Posts: 10
Joined: Mon Mar 09, 2015 11:54 pm
OLAP Product: TM1
Version: 9.1 to 10.2.2
Excel Version: Office 2010

Re: Drill-through to a cube view

Post by a1m80t » Tue Oct 29, 2019 3:09 am

gtonkin wrote:
Thu Feb 28, 2019 6:50 pm
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
MVP
Posts: 2289
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Drill-through to a cube view

Post by Wim Gielis » Tue Oct 29, 2019 9:01 am

a1m80t wrote:
Tue Oct 29, 2019 3:09 am
gtonkin wrote:
Thu Feb 28, 2019 6:50 pm
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 ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

a1m80t
Posts: 10
Joined: Mon Mar 09, 2015 11:54 pm
OLAP Product: TM1
Version: 9.1 to 10.2.2
Excel Version: Office 2010

Re: Drill-through to a cube view

Post by a1m80t » Tue Oct 29, 2019 3:13 pm

Wim Gielis wrote:
Tue Oct 29, 2019 9:01 am
a1m80t wrote:
Tue Oct 29, 2019 3:09 am
gtonkin wrote:
Thu Feb 28, 2019 6:50 pm
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

User avatar
PavoGa
Community Contributor
Posts: 387
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Drill-through to a cube view

Post by PavoGa » Tue Oct 29, 2019 4:13 pm

a1m80t wrote:
Tue Oct 29, 2019 3:13 pm

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.
Ty
Cleveland, TN

a1m80t
Posts: 10
Joined: Mon Mar 09, 2015 11:54 pm
OLAP Product: TM1
Version: 9.1 to 10.2.2
Excel Version: Office 2010

Re: Drill-through to a cube view

Post by a1m80t » Tue Oct 29, 2019 10:03 pm

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.

Post Reply