A small change of plan
My original, and continuing, intent with this series of blog posts is to demonstrate the awesomeness that is Dodeca.
I wrote an outline (yes, this gibberish sadly does not spring from my fingers willy-nilly but in fact is organized, sort of) that started off with a grand tour of the Dodeca interface.
As I began to take screen shots, it occurred to me that there is a lot of functionality to cover. I don’t think I’ve got a Grand Unified Theory of Dodeca blogging just yet – there’s so much going on.
At least initially, I’m going to focus on functionality across the three products: the Excel Essbase add-in, Smart View, and Dodeca, and then build from there.
This post is a trip back in time to English Composition class – I’m going to compare and contrast these products. There is a point to this – you won’t understand the genius of Dodeca until you understand the limitations of its competitors. J
Member selection in Excel happens when:
1) An off-grid (classic Essbase add-in) or Point of View (POV) (Smart View) member is selected; only one member can be selected, although in Smart View’s POV manager, you can preselect multiple members and then use the dimension drop down to navigate through the selections.
2) An on-grid row or column member is selected; one or more members can be selected, either down rows or across columns.
Okay, for those of a pedantic bent whose name isn’t “Cameron”, there are two other kinds of member selection that can happen in Excel without using the Member Select (classic)/Member Selection (Smart View):
- Member selection can also occur simply by typing in the member name on the sheet (you will likely know Scenario, Version, Year, Period, etc. dimension member names or probably your most commonly selected Accounts, Products, Regions, etc.
- And I guess you could argue that member selection can also occur as part of drilling up and down in a dimension and using keep- and remove-only functions to narrow selections.
How do I get there?
Click on the Essbase menu in the Excel menubar and navigate to Member Select:
If you’re using Excel 2007 (and the above menu screenshot is from that release), you’ll first need to select the Add-Ins ribbon, and then click on the Essbase menu.
Remember how I wrote that if you have lots of automation in Excel you’ll lose it all with Excel 2007? Remember that cool menu? Stuck in the Add-Ins ribbon. Remember that cool toolbar you wrote? Gone.
Someone came to the rescue, but you’ll still have to roll your own for your code
The good folks at in 2 Hyperion wrote a ribbon bar for the Classic add-in. For those of us still using Classic (I hereby give up on the full name “Classic Essbase Excel add-in”), it’s a lifeline till Oracle kills Classic. Find it here. Highly recommended for those using Excel 2007, but Dodeca’s still better.
Smart View splits up dimensions between on-sheet and in-POV manager. When a dimension is on the sheet, member selection works much the same way, i.e., click on a cell, and then begin Member Selection either via the Ribbon (Excel 2007), toolbar (Excel 2003), or Hyperion menu (2003 or 2007).
Ribbon (Excel 2007)
Toolbar (Excel 2003)
Remember that the Hyperion menu is also available under the Add-Ins ribbon in Excel 2007.
Point of View Manager
Click on the dimension in question’s down arrowhead, select the ellipses, and Member Selection is coming your way.
Dodeca differentiates between the in-built member select dialog box and customizable member selectors.
Member select = the Classic interface with a few improvements.
Member selectors = totally customizable, incredibly flexible dimension pickers that you could write in VBA, or VB.Net, or whatever, but after you taste Dodeca’s approach, you will give up on in defeat and despair.
Ad-hoc Member Select
This is similar to the way Classic works, but, of course better because Dodeca knows whether the member select is in a row, column, or off-grid dimension and expands/limits functionality to the appropriate position on the grid.
Just click on the member, then the toolbar button to get the Member Select button.
Simplicity itself – click on the selector box in the report toolbar.
Classic doesn’t know a dimension dropdown selector from its elbow.
Smart View’s POV selector can use dropdown selectors within the POV. Dodeca’s are easier and centrally controlled.
What do they look like?
So what does it look like? I’ve been using it so long I can almost recite the objects from memory.
Member Select description
Welcome to 1992’s idea of User Interface design. Time machines are always fun.
What do I mean? Think about the down arrowheads for expanding down – replace by up arrowheads for expanding up. It’s a logical metaphor, but try to think of another hierarchical selector that works this way other than Essbase Application Manager – I dearly miss that product, but it’s time has come and gone. Plus and minus signs seem to be the universal drill up and drill down metaphor.
At the very upper left hand corner, there’s a dimension picker dropdown control. This is only useful if you’ve selected a blank cell and the right dimension doesn’t pop up (this will always be the first dimension in the outline), otherwise the dimension will be whatever is selected in Excel. Of course, Classic will let you select the wrong dimension and overwrite valid selections. Whoops.
You can use the Find button to search for strings, but remember, no leading wildcards.
Information about a member is but a click away.
Dimensionality can be viewed by member name, generation, level name, or even by Dynamic Time Series if you’re dealing with a DTS-enabled Time dimension.
And of course there’s the option of viewing this by member name, aliases by alias table, and the option to place the selections across the rows or before the selected cell.
What’s really cool about Classic’s member selection process is that the selected members can be saved either to the server (stored in the Essbase database’s directory) or locally. Nice if you’ve defined complicated searches.
Once the selections are made, you can move the selected members to the right hand list box.
But wait, there’s more
Once a member has been moved over to the Rules list box (betcha never looked at the description – me neither), the member can be right clicked on and member relationship selections can occur.
Right there you can pick Children, Children and Member, Descendants, and Descendants and Member.
But it all gets powerful when Subset… is selected.
Attribute members, ANDs, Ors, NOTs, generation and level names, patterns – the whole shebang is there. Powerful selections can be created and then saved as mentioned before. You can even use this functionality to figure out what Attributes are associated with a member – this is missing in the Member Information button.
But it’s a hard way to find out that Massachusettes has an attribute of Medium_9000000. At least you can select it.
However you get there, it’s the same thing
Whether it’s through an on-sheet member select, or in the POV, the same Member Selection dialog box pops up:
In many respects, Smart View’s Member Selection dialog box is a step forward in functionality from Classic:
- Pluses and minuses are used to drill up and down.
- Filtering of members is easier because you need only pick the member, and then choose the Children, Descendants, Level, Generation, UDA, Attributes, and Subset all from the main dialog box.
- No need to decide if the selections go in rows or columns – Smart View knows the right way.
See Attribute in the Filter dropdown? Easier to get to than moving Market to the Rules listbox and then right clicking on it, then selecting Subset, and then finally getting to the Attributes.
But there are some things missing:
- Member information
- Attribute member selections aren’t as detailed as Classic (or Dodeca)
I don’t know how often you use this – Essbase developers can always go to Essbase Administration Services and find out all we ever wanted to know about a particular member. For the hoi polloi (that would be your users, not you, dear reader) they’re largely out of luck. Too bad.
Attributes are selectable, but not completely. Whoops.
Smart View’s Population
See anything missing?
See what’s not missing?
It’s smarter than you. Or at least it’s smarter than me.
As I was exploring Dodeca member selection for this blog, I found out a couple of cool things:
- In ad-hoc views, member selection is modal – Dodeca knows when a member is in a row, a column, or off-grid, and allows selection appropriately.Dodeca won’t allow stupid member selections that break ad-hoc reports.
- In standard views, member selection is surfaced immediately, without a trip to a dialog box.
- AppliedOLAP spent a *lot* of time figuring out how to customize member selector dialog boxes without you writing code. I won’t cover this in this post (what, not long enough for you?), but it will be my very next post. I cannot get over how freaking cool this stuff is.
Think about this – only one member for a given dimension is on the grid. Hence, only one member from that dimension should be selectable. Guess what, Dodeca knows this, and the Member Select dialog reflects that.
Classic breaking retrieves
It’s easy to break a Classic retrieve through Member Selection. In this case I am selecting the four (sort of) points of the compass. Note – this doesn’t make sense, but Classic Member Selection isn’t going to save you from yourself.
Place Down the Sheet
Whoops, forgot to untick Place Down the Sheet.
Not Placed Down the Sheet
Nope, that doesn’t work either.
Smart View breaking retrieves
Smart View is a little better at this – it understands rows, columns, and off-grid dimension members, but it will still let you make selections that don’t retrieve.
Dodeca breaking retrieves
Erm, you can’t do it. Remember how I wrote above that Dodeca knows that a dimension is off-grid and thus does not support multiple selections? Well, that’s the trick – it doesn’t let you select more than one member and it doesn’t break. Genius, if you want my opinion. Or even if you don’t.
It’s the same as Classic, with one improvement.
Did you spot them? Plusses and minuses to expand/collapse the hierarchy.
No progress? There are those who would call Classic’s Essbase Member Selection dialog box perfection itself. And Dodeca improved upon perfection with the +/- controls.
Remember, this is different than plain old member select from an ad-hoc retrieve. Member selectors have a lot of functionality that you, the developer, customizes as part of a report. More on this in the next post, I swear.
There it is, right there on the right (position customizable – hey, everything’s customizable). And the dimension’s available any time you want. No more clicking on a member and then selecting a menu/toolbar/ribbon and then getting to the dimension to switch from member A to B.
In the meantime, feast your eyes on the below.
When I write that there’s surfaced functionality, I mean that without clicking into modal dialog boxes the following functions are instantly available:
- Dimensions (depends on how the View is set up)
- Swapping between dimensions
All without writing a line of code.
Oldie but goodie
For those of us who know and love the Classic Essbase Excel add-in (there, I wrote the whole thing), member selection is pretty nice, if somewhat old fashioned looking. Eh, looks aren’t everything.
It’s pretty easy to break reports, so be careful.
Newer, but curiously missing some functionality
Smart View keeps on getter better and better, but there are some big holes out there such as member information and a decent attribute member select.
Retrieve intelligence is better, but it’s still easy to break ad-hoc retrieves.
Why use anything else?
Ad-hoc analysis isn’t even Dodeca’s raison d’etre, yet it does it flawlessly with Classic’s functionality and none of Smart View’s holes.
Even more importantly, its intelligence around dimension placement means that you can’t break an ad-hoc report through a member select. This is a Good Thing. You haven’t lived (if you can call it living) until you walk a user through why the product breaks their retrieve.
What’s coming next?
Think of this as the first course in a multi-course meal. You’ve had the starter, but the main plate is to come. Hopefully your salivary glands are hard at work.
We’ve now set the baseline; the next post will (soon, I swear, just a week, maybe a little longer…) get to the interesting stuff – all of the different ways to customize member selectors in Dodeca.