Shared Member Drill down – Surprises in Essbase Part VIII 12

This entry was posted in Essbase Excel Add-in Smart View Surprises on by (updated 2265 days ago)
This blog post is a surprise and I’m surprised why this change is not documented anywhere.

What is this about? Well I guess you’ll have to read till the end šŸ™‚

Conversion is a big deal. Oh no not the religious conversion šŸ™‚ I’m talking about converting Excel Add-in users to Smart View. (It was hard for me to use Smart View in the beginning)

Me and my friend,Ā KishoreĀ were discussing about this conversion where he had a big number of users who are still using EXCEL Add-in and like many of the discussions floating on web (on Smart View and Add-in) we also had a fair deal of – Smart View is better than Add-in and vice versa.
There were many questions that you’ll get from the community where they find the change difficult and different.

So there was this question where he was wondering whether I knew about an option in Smart View to drill down on a shared member (parent member which is shared in an alternate hierarchy)

He said he can do that in EXCEL Add-in.

I was like “No you cannot do that in Add-in, it stops the drill down if the alternate hierarchy parent is there”. I didn’t say that aloud.

I’m like I don’t know how to do that in Add-in and you are asking me “How can I still down on a shared member in Smart View?”

Even though I didn’t know how to do that in Add-in I did give him the idea on how you can make it work in Smart View.
If you’ve not used it – There is an option in Smart View which will allow you to zoom in on formulas (The formula can be a member equation or a consolidation to the parent).
This method won’t give the correct results if there is a child which is ignored or never consolidating.
Later he was generous enough to show me how that works in EXCEL Add-in (I know you already know this šŸ™‚ )
You can use Free Form mode in EXCEL Add-in to perform the drill down on a shared member. The only place I’ve used Free Form mode is to run report scripts.
I did like that option in EXCEL Add-in and I thought of finding an option in Smart View which can really work.
I was trying to replicate this in Sample Basic (I had to modify it to add a alternate hierarchy) and this is how my Market dimension looks like.
What happens when you drill down on East, when you’ve Northeastern in the grid?
Yes, you guessed it right. Nothing it won’t drill down.
No I don’t have duplicate members in my database. This was the change that got introduced in 11.1.2.3.000
From the new features guide

Unique Names for Shared Members
In grid clients (for example, Oracle Hyperion Smart View for Office), you can reference shared members as unique from their base members by typing them with a qualified name (for example, [Parent].[Child]). Shared members can be referenced with qualified names even if you have not set the outline to enable duplicate member names.
EXCEL Add-in User guide on drill operations on Shared members

Product


Ā  Ā 100
Ā  Ā  Ā  150 (regular member)
Ā  Ā  Ā  Ā  Ā 100-10
Ā  Ā  Ā  Ā  Ā 100-20


Ā  Ā Test1
Ā  Ā  Ā  150 (shared member)


Note that the regular member 150 contains children; the shared member 150 does not. Drilling up and down on 150 gives different results.
  • Drilling down on 150 returns nothing if interpreted as the shared member; or, return 100 10 and 100-20 if interpreted as the regular member.
  • Drilling up on 150 returns Test1 if interpreted as the shared member; or, return 100 if interpreted as the regular member.
The proximity of the shared member to the regular member gives different results when drilling down:
  • If Test1 is on the worksheet and very close to 150, Essbase Server understands thatĀ 150 is the shared member and drilling down does not result in the children, 100-10 and 100-20.Ā 
  • If Test1 is not on the worksheetĀ or members are in between them, Essbase Server understands 150 to be the regular member so drilling down results in the children 100-10 and 100-20.
This was what Kishore was trying to solve in Smart View (Use Zoom in – formulas on East)
As I said I was trying to find a perfect answer and tried this on 11.1.2.3.500 Essbase server. As I was playing around I accidentally double clicked on East.
Guess what (see the difference in the member names – East is East in 11.1.2.3.500), you can drill down on a Shared Member in 11.1.2.3.500.

I was first thinking that it could be a Smart View/Excel Add-in version issue (Kishore had an older and I’m using the latest).

I looked at my Software inventory šŸ˜‰ and was able to get an older version of Add-in and Smart View.

I could drill down on those versions also, which made me look at the Essbase version.
It is thus proven that on 11.1.2.3.000 I cannot drill downĀ on a shared parent. So where did it all start, I was lucky that there are not many patches that got released between 11.1.2.3.000 and .500

I patched it to 11.1.2.3.001, just because this patch fixed the issue with the way Shared members were showing up in Smart View (shared shows up as unique member) and see whether this behavior started from that patch and no one noticed it. You can read more about the issueĀ here.

Retrieve the same sheet in the patched version and zoom in on East voila. The long ask for drill on Shared members is here.

It is confirmed that from 11.1.2.3.001 onwards you can now drill down on a shared member.

I’m still not sure why this is not showing on any of the new features or why the change in behavior is not documented anywhere. Everyone + dog always asked for this feature and Oracle didn’t bother to tell us that it is here. (or they don’t know about it ;))

About Celvin Kattookaran

Iā€™m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with ā€œIntekgrate Corporationā€ based out of Aurora office. Iā€™m from Godā€™s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). Iā€™m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

12 thoughts on “Shared Member Drill down – Surprises in Essbase Part VIII”

  • Reply ↓
    Anonymous

    Celvin,

    Thank you for this interesting post.. Had a couple of questions…
    Based on what you said, does this mean that if I zoom to the bottom of the alternate structure, it would now go to the actual level 0 members? I do like the ability to go down to the actual base members, but at the same time I want to be able to control that behavior…

  • Reply ↓
    Celvin Kattookaran

    If you zoom in using bottom level it'll stop at the bottom level of the alternate hierarchy (won't expose the children of the shared parent).
    To see the actual level 0 members of a shared parent you'll have to zoom in on that member, here all zoom in option will work.

  • Reply ↓
    Peter Nitschke

    That's really interesting! It's always driven me mental that that isn't default behavior.

    Maybe that was what they were trying to get working when they put the 11.1.2.3 smartview shared members bug in? *grin*

  • Reply ↓
    Unknown

    it creates issue when user drills down on Entity and one its child (which is alt hierarchy with shared members which user have access to) user has no access, it drills down to all level

  • Reply ↓
    Rose Glazewski

    Hello, I know it has been a while since this post, but I am curious – I am using Essbase 11.1.2.3.508 and Smart View 11.1.2.5.000 Build 111 and I am not able to drill down into shared members. We are seeing this in Smart View as well as in CXO Cockpit.

    Any thoughts?

    • Reply ↓
      Celvin Kattookaran Post author

      It depends on the hierarchy structure too. Can you please try that again Sample Basic and see if that is working?

      • Reply ↓
        Rose Glazewski

        Hi Celvin,

        Thanks for the quick reply. I tried adding a shared member to Measures in Sample Basic and was able to drill in. However, when I added shared members to Market, I couldn’t drill in. Were you able to get the drill to work on a Sparse dimension?

        Appreciate your assistance.

        • Reply ↓
          Rose Glazewski

          I see that you did your analysis on Market – which is what I did as well. You can ignore the previous message šŸ™‚ Here is how I structured Market

          East
          West
          South
          Central
          East
          West
          TotEW
          TotSC
          AltMarketSparse
          Market

          But when I expand AltMarketSparse and TotEW, I can see East and West but I cannot drill into them. If I remove the parent TotEW, then I can drill into it. So it appears that the generation and/or level of the shared member comes into play. So I tried adding more levels in the original structure and then I couldn’t drill on East anymore.

          East
          West
          South
          Central
          TestLevel
          East
          West
          TotEW
          TotSC
          AltMarketSparse
          Market

          I even tried getting them to the same generation and I still could not drill into East.

          East
          West
          TestLevel1
          South
          Central
          TestLevel
          East
          West
          TotEW
          TotSC
          AltMarketSparse
          Market

          If you have any ideas on this I would appreciate it. My hierarchies are quite deep and users are wanting to drill in.

          Thanks.

          • Celvin Kattookaran Post author

            Yup that is exactly what I saw, I think there is a bug (maybe fixed in the latest patches) filed. I could be wrong. Best way is to file an SR and see if it is solved with a latest patch

  • Reply ↓
    Azmat Bhatti

    Hello Celvin,
    Thanks for this post. I was searching for an issue and this came up. We drill down on all levels and it stops on shared members and you have to manually drill on it to show the members.
    Do you know if there is any way that SV can go all the way down, without user drilling on Shared members ?
    Thanks,
    Azmat