Drill down from Top level on stacked dims

[tosca1978]

Hi all,

I have a cube view with 2 dims stacked in the rows - Say Dim A and Dim B.

Dim A has a hierarchy with 3 levels: Total Costs; Cost group; n Level Cost
Dim B has a hierarchy with 2 levels: Total ID; n Level ID

The cube view (which will be what my active form is based on) starts with showing the top level on both dims A and B. When I drill down the cube works just as expected - however I would like it to behave differently!

What is currently happening:
Stage 1: on opening cube you see Total Costs in Dim A and Total ID in Dim B
Stage 2: I drill down on Dim A to see Cost Group Level. Dim B is still at Total ID
Stage 3: I drill down on Dim A to see n Level Cost. Dim B is still at Total ID
Stage 4: Dim a is already fully expanded. I drill down on Dim B to n Level ID

When you get to stage 4 all of the n Level ID's in Dim b are shown for all of the levels of hierarchy in Dim A. So Total Costs (top level) is a couple of hundred rows of data showing n Level ID's - which is pointless as the user cannot post through the active form at this level as it's consolidated and what they really want to see is the detail held at n Level cost and n Level ID.

Ideally I would like consolidation elements in Dim A to always be a one liner which would involve the hierarchy in Dim to only show Total ID on that row and not show n level ID for the consolidated Dim A element.

that way the user would not have to scroll past all of the consolidated Dim A /n level Dim B data to get to what they want to see; and the performance would be better on refreshing the active form.

I hope this makes sense - apologies if what I am trying to achieve is badly written.

If the front end was a regular TM1 worksheet I could do what I likes on the rows, however, I need this to be an active form as the cost groups and ID's will change depending on the user, so the zero suppression offered by active forms is valuable.

I have considered using n level susbets on the active form and having an a few rows above the active form that shows Totals though regular DBR formula. But the consolidation elements really need to be within the body of the data for end user acceptance.

I have attempted to override the consolidation on Dim A with a rule like "['Total Costs','ID 01']=0;". this was an experiment to see if the zero at the consolidation point would be suppressed but it wasn't.

I have looked at using MDX in the subset editor using TOGGLEDRILLSTATE for example. But I don't see an answer here as it's two dims stacked on the rows.

If anyone has any suggestions I would be grateful to hear them.

Cheers

[Christopher Kernahan]

Hi tosca,

I really tried to follow along but you lost me. Can you post a screenshot of what is happening, and what you would it like to do.

If what is driving the active form is DimB, then have you tried creating an Active Form with only DimB in the rows, then inserting a column in front of it and using a DBR formula to pull the DimA element that you want?

[tomok]

I know exactly what you are talking about and this is one of the frustrating things about how the Active Form drilling works. The only solution I was able to come up with was to add a cell outside of the active form to hold the starting point in either Dim (and point the TM1RPTROW formula to that cell) so that when you got to the point you want in DimA (an N level) you can lock that in by picking that in the "lock" cell and then you can drill your way down to the N level in DimB so users can enter data.

[tosca1978]

Hi Christopher/Tomok,

thanks for your replies.

Christopher - I'm not surprised that you couldn't follow, it was difficult to explain without a screen shot. Supplying this from cube viewer or the active form is difficult due to the number of rows when you drill down on the first consolidation so I have mocked up an example in excel to illustrate the issue.

Stacked Dim drill down issue in active forms.xlsx

(254.55 KiB) Downloaded 491 times

Tomok,

After my post last night I did the following:

1. Created a named subset for each of the level 1 consolidation elements in Dim A (dynamic with mdx that just shows children of Level 1 Consolidation). there are only 8 and these should not change very often. A 9th subset is a drill down from Dim a parent 2 - this allows user to see all.
2. Listed these out above the active form and added a combo box using the list as its source. When user selects Dim A lev 1 parent in here, the index number is used in another cell to bring back the selected element.
3. Ammended the TM1RPTROW formula for Dim A to point to the subset selected by user.
4. Dim B is still a drill down from the Level 1 consolidation.

The results are OK. It means that the user can select Total Costs (Dim A) and drill down from there for when reviewing data. Then when inputting data, can select the Cost Group in Dim A that he wants to input against. The refresh times are much better. However, when I have presented this kind of workaround in the past the business has found the experience cluncky and would rather see the whole report when inputting their data.

Your solution could be a much better option and I'd like to try it but I'm not sure I fully understand it!

Tomok wrote:

The only solution I was able to come up with was to add a cell outside of the active form to hold the starting point in either Dim (and point the TM1RPTROW formula to that cell) so that when you got to the point you want in DimA (an N level) you can lock that in by picking that in the "lock" cell and then you can drill your way down to the N level in DimB so users can enter data.

1. I have created a new active form.
2. Above it typed in an n level element from Dim A (typed in for now - whilst experimenting - would need to be a drop down list or something but not sure what the source would be).
3. I have pointed the TM1RPTROW formula for Dim to this cell.
4. On rebuilding the worksheet only that element appears in active form in Dim A and then I can drill down from Total ID in Dim B and input data. I guess I could put an IF statement into another cell which would only point the TM1RPTROW formula to this if the user has selected "Lock" etc.
5. Once "Lock" is switched off the original drill down active form is displayed.

Is this your solution or have I misinterpreted it?

Cheers

[tomok]

Is this your solution or have I misinterpreted it?

That's pretty much it. I agree that it adds extra steps for the users in that they have to navigate themselves to a leaf in DImA using the "lock" cell and then drill the active form for DimB to get to a leaf where they can input but if you have hierarchies of any decent size in both dimensions the report can get rather large when you start drilling and they will spend forever scrolling around finding a leaf cell to input. Its a pick your poison situation.

2. Above it typed in an n level element from Dim A (typed in for now - whilst experimenting - would need to be a drop down list or something but not sure what the source would be).

If you put a SUBNM formula in that cell then the user can navigate their way to the desired leaf using the subset navigator.

4. On rebuilding the worksheet only that element appears in active form in Dim A and then I can drill down from Total ID in Dim B and input data. I guess I could put an IF statement into another cell which would only point the TM1RPTROW formula to this if the user has selected "Lock" etc.

An excellent idea. Have not tried that but it should work.

[tosca1978]

Hi Tomok,

I went with the TM1RPTROW referencing the subset design in the end as it seems to deliver similar results to your design. It's looking quite slick now and I have put an if statement on Dim B to make use of the "ConsolidationDrilling" part of the TM1RPTROW formula - now when a consolidation element of Dim A is selected, the user CANNOT drill down on Dim B. this means they will only ever use this view to review high level numbers and won't be tempted to try and drill down from here in Dim B. Works quite well.

thanks again for all of your help.

Cheers

[paulsimon]

Hi

Another possible option is to use a Data Filter to hide the rows that you don't want the users to see. However, this is only feasible post retrieval, and only if you can use Macros to automate it, which rules out the web.

Another solution I have used in the past is to create a composite dimension. Dim AB has the upper levels of dimension A, and only when you get to the base of level A, the hierarchy then expands out to show levels of dimension B, with AB combinations. It is however not a very generic approach sinde you need to manufacture the composite dimension for each two dimensions you combine. However, the subset approach that you have also sounds like it could take some work to maintain and keep in step. When I used this approach it was before the days of ActiveForms. Unfortunately to use the Composite dimension approach with an ActiveForm, you would probably need to make it part of the cube. However, you might get away with it provided that your DBRW references the split of the composite dimension into its two component dimensions, which do really exist in the cube.

Regards

Paul Simon

[tosca1978]

Hi Paul,

thanks for your post.

Re using excel filters: I did experiment with this (without the use of macros as the final solution is the active form on TM1 web). I had 3 new columns:
1. ELLEV formula on Dim A
2. ELLEV formula on Dim B
3. something like if(1=0,IF(2=0,0,1),2)

Then using excel filter I could select only "0" on column 3 and get all of the n levels for both dims. However, this broke very quickly after an active form rebuild. Also, the performance was still an issue as the active form was still bringing back lots of data in the hidden rows. So I gave up on this idea.

The composite dimension is an interesting solution. Funnily enough my first design if this cube was a combined dim. However, DIM A already has circa 15,000 n levels and Dim B is another circa 200 n levels. I figured that a Dim with 3m + elements can't be healthy, hence I broke it out into 2 dims.

I'm hoping that the subset design won't be a high maintenance overhead. The Cost Groups which form the subsets on Dim A are only 8 and this number should not grow very often as these are large generic cost buckets. The n levels of Dim A will grow (more added every week), however the MDX creating the subset should always pick up the new ones in the correct bucket). That's the idea anyway!

Cheers