Drill-through to a cube view

Post Reply
Wim Gielis
MVP
Posts: 1839
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
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 ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Wim Gielis
MVP
Posts: 1839
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
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 330 times

2: to be clear, the drill view is created

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

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

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



Thank you
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

lotsaram
MVP
Posts: 3146
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: 1839
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
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 ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Wim Gielis
MVP
Posts: 1839
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
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 ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
Steve Rowe
Site Admin
Posts: 1826
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: 1839
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
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 ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
Steve Rowe
Site Admin
Posts: 1826
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: 3146
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: 1826
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: 1839
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
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 ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Post Reply