Selecting Dodeca

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 selections

A definition

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?

Classic

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

It depends

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).

On-sheet

Ribbon (Excel 2007)

Toolbar (Excel 2003)

Menu

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

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.

Member Selectors

Treeview

Simplicity itself – click on the selector box in the report toolbar.

Dropdown

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?

Classic

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.

Smart View

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.

Filter

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)

Member information

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.

Attribute selection

Attributes are selectable, but not completely.  Whoops.

EAS’ Population

Smart View’s Population

See anything missing?

Classic’s Population

See what’s not missing?

Dodeca

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.

Modal

Off-grid

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.

On-grid

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.

Member Selector

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:

• Expand/Collapse

• Find
• Dimensions (depends on how the View is set up)
• Swapping between dimensions

All without writing a line of code.

Conclusion

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.

Ye Olde Commute

When the big storm hit last week, like many in my town, I was left in the cold and dark for several days. The Darien Library became my second home, affording me a chance finally to read historian Kenneth Reiss’s “The Story of Darien Connecticut”, an excellent new book.

What I found was that the story of this “bedroom” community’s growth was intimately linked to transportation.

As early as 1699 roads had been laid out on routes still used today. But where today those roads are lined with trees (whose felling by the storm left us without power or passage!), by the mid-1700’s most of southern Fairfield county had been cleared of all trees to allow for farming. Those mighty oaks taken out by the storm were not as old as we’d thought.

In the 1770’s the maintenance of Country Road (now known as Old Kings Highway) was the responsibility of the locals. Every able bodied man and beast could be enlisted for two days each year to keep the roads in good shape. But traffic then consisted mostly of farm carts, horses and pedestrians.

By 1785 there was only one privately owned “pleasure” vehicle in all of Stamford, a two-wheeled chaise owned by the affluent John Davenport.

At the end of the 18th century it was clear that we needed more roads and the state authorized more than a hundred privately-funded toll roads to be built. The deal was that, after building the road and charging tolls, once investors had recouped their costs plus 12% annual interest, the roads were revert to state control. Of the 121 toll-road franchises authorized by the legislature, not one met that goal.

One of the first such roads was the original Connecticut Turnpike, now Route 1, the Boston Post Road. Another was the Norwalk to Danbury ‘pike, now Route 7.

Four toll gates were erected: Greenwich, Stamford, the Saugatuck River Bridge and Fairfield. No tolls were collected for those going to church, militia muster or farmers going to the mills. Everyone else paid 15 cents at each toll barrier.
The locals quickly found roads to bypass the tolls which were nicknamed “shun-pikes”.

Regular horse-drawn coaches carried passengers from Boston to NY. And three days a week there was a coach from Darien to Stamford, connecting to a steamboat to New York.

The last tolls were collected in 1854, shortly after the New York & New Haven Railroad started service. An 1850 timetable showed three trains a day from Darien to NYC, each averaging two hours and ten minutes. Today Metro-North makes the run in just under an hour.

The one way fare was 70 cents vs today’s $12.25 at rush hour.

By the 1870’s Darien was seeing what we today call “transit oriented development”, as full page ads lured city dwellers to newly built homes near the Noroton station which opened in the 1870’s.

In the 1890’s the one-track railroad was replaced with four tracks, above grade and eliminating street crossings.

In the 1890’s the trolleys arrived. The Stamford Street Railroad ran up the Post Road connecting in downtown Darien with the Norwalk Tramway (rattling along Railroad Ave., now known as Tokeneke Rd.); the latter also offered open-air excursion cars to the Roton Point amusement park in the summer.

Riders could catch a trolley every 40 minutes for a nickel a ride. There were so many trolley lines in the state that it was said you could go all the way from New York to Boston, connecting from line to line, for just five cents a ride.
The trolleys were replaced by buses in 1933.

Fast forward to the present where we are again debating tolls on our roads, possible trolley service in Stamford and T.O.D. (“transit oriented development”) is all the rage. Have things really changed that much over two hundred years?

Mass Transit Faces Service Cuts, Fare Hikes

From coast to coast, mass transit is under attack. Decreased ridership due to the economy and reduced state subsidies are leading to cuts in service and fare increases.

Visit msnbc.com for breaking news, world news, and news about the economy

For us in Connecticut, New York’s MTA and its $800 million budget shortfall could affect our daily commute. The NY transit agency is holding public hearings on plans to cut bus and subway service, eliminate student discount fares and, yes, even target Metro-North service.

Starting this June, the MTA wants to shorten Metro-North trains (achieving a $2.8 million annual savings) and eliminate others (a $1.6 million savings). Targeted for cuts in Connecticut are two mid-day trains between Grand Central and New Haven, and a late night local from GCT to Stamford.

But neither of these cuts will happen, thanks to our Governor.

First, many New Haven line trains are already standing room only so it would be impossible to reduce their length. Some 6 – 7% of our trains don’t have enough cars to handle the passenger load, let alone see the number of cars get reduced.

Second, under our operating contract with Metro-North, none of these service reductions can be unilaterally dictated by MTA without agreement by the state of Connecticut. And Governor Rell has said “no way” to any service cuts in Connecticut.

Having for years sought a voting seat on the MTA or Metro-North board and been ignored, Governor Rell is quite correct in reminding those NY agencies that their current economic problems are of their creation, not Connecticut’s. Decades of over-zealous bonding for massive projects like East Side Access (a $7 billion project to bring LIRR trains into Grand Central) have left a pit of pain which New Yorkers dug, but have the chutzpah to now ask our state’s riders to fill. No way, MTA!

As I reminded the MTA Board when I testified at a recent public hearing… “Metro-North is a vendor to the state of Connecticut. We hire you to operate our trains. But we are not equal partners in the operation of this railroad.”

Governor Rell has told CDOT Commissioner Joseph Marie to block those proposed service cuts, and the dutiful transportation czar is following orders, much to the chagrin of Metro-North which, doubtless, will get their revenge at a later date.
If cuts in Metro-North service are needed, let them be in NY State, not Connecticut. New York already has more trains and lower fares than we do, so they can bear a loss of service with less pain.

While there are two trains operating each hour between Stamford and Grand Central, we have only one train an hour between New Haven and GCT. So, let them cut the Westchester trains, not Connecticut’s.

The final piece of good news is that we will not be looking at any fare increase here in Connecticut for the foreseeable future. A rumored 10% fare hike last fall to balance the state’s budget was postponed and a planned 1.25% fare hike January 1st 2010 (to help pay for the new M8 rail cars) was delayed, keeping the Governor’s promise of no fare hike until the (now delayed) rail cars go into service.

Our neighboring states have entered a death spiral of less mass transit at higher costs, discouraging ridership even further and eventually forcing more service cuts or fare hikes. But here in Connecticut, for a change, we remain a leader in maintaining fast, on-time Metro-North rail service with no price increase. And the credit all goes to our Governor, Jodi Rell, for holding firm against the MTA.