Dodeca: Create and use a new Essbase MDX Selector List

The most recent version of Dodeca brought several exciting enhancements for MDX-related functionality. One of these is a new selector list based on a reusable MDX script object. Although MDX queries are probably most often associated with queries that return numerical data from a cube, they also have incredibly useful metadata capabilities that can be employed for various purposes. In Dodeca, it’s common to use a report script or member query specification to return members from an outline. For example, you might want to provide your users with a selector such that they can choose a particular product (or products) from your Product dimension in order to customize a report that they will build dynamically.

I see MDX scripts as being a natural, clean, and flexible way to populate these selectors, and moving forward I will recommend them whenever possible over the more arcane report scripts that have been around for years.

That all said, what I want to show today is the following: I’m going to edit an existing Dodeca view so as to replace one of its existing selector lists with a new list based on an MDX query.

Continue Reading…

Launch ClickOnce apps [such as Dodeca] through Firefox

I’m a Firefox man.  I’ve been a fan since version 1.5, liked version 2 (even though it was a memory pig), and I am quite happy with 3.0.  I am eagerly looking forward to some of the memory optimization and performance improvements that are coming down the pipe with 3.1.  I even look to Firefox as an example of subtley evolving a user-interface and polishing it as time goes on — I try to implement some of the same refinements in my own projects.

I also use Dodeca extensively as a front-end to much of my Essbase functionality.  As a ClickOnce app (.NET technologies), it has been necessary to launch it with Internet Explorer.  Of course, I can invoke it directly with a shortcut on my desktop, but frequently I find myself using a link to launch it since it’s just easier.  Sadly, this does not work out of the box with Firefox because Firefox just sees the .application file and doesn’t know what to do with it.  Some of my users have Firefox as their default web browser and have run into some slight issues as well.

Well, unbeknownst to me, there has been a Firefox ClickOnce add-in for some time.  One of the things I love and use in Firefox is it’s extension capabilities — I typically have the Foxmarks, Delicious, Greasemonkey, Web Developer, and Flashblock extensions installed as a minimum (I used to use Sage as well but I find myself in Google Reader now).  So I bounced over to the Mozilla addons page, clicked the button to install FFClickOnce, restarted my web browser, punched in my Dodeca URL, and without a hitch, I was prompted to run Dodeca.  Not that I have anything against Internet Explorer, but now I can do just about everything in Firefox and have less reason to fire up IE (I’m looking at you, Windows Update…).  Sometimes it’s the little things in life!

Getting everything done with just Essbase and Dodeca

Many people I talk to are amazed that a company can get by with just Essbase and Dodeca.  No Planning deployment, no HFM, none of that other stuff.  The core Essbase analytic engine brings a lot to the table, which is this: multi-dimensional analysis and all of the associated functionality for building, automating, and managing cubes (EAS, EDS, EIS, and such, in Essbase 7.x terminology).

So, out of the box with a typical Essbase setup, you can unlock the benefits of multi-dimensional analysis just by virtue of using using the Excel add-in.  We all know the Excel add-in has some issues (it’s not exactly aging gracefully), but it’s simple, fast, and many users absolutely love it.  The financial types tend to “get it” right away and go on their merry way.  They also seem pretty sad when they are “forced” to go to Business Objects for some data that isn’t in Essbase.

Providing access to cubes via the add-in gives your users a lot already.  Historically, it seems that many companies have grown organically around the Excel add-in.  They evolve to using VBA automation for retrieval and reporting jobs, creating formal Excel solutions, and of course facilitating various processes with a good old “Lock and Send,” with some calcs thrown in for good measure.  This is all well and I good, but for larger endeavors this approach can suffer from maintenance, security, and scalability issues.

Without clearly defined standards, the VB implementations seem to go south and turn into a mess of spaghetti code that breaks at the most inopportune times.  There are different versions of everything floating around.  The deployment method ranges from emailing updated files, to copying them off the network drive, to even throwing them on a USB stick and moving them around when all else fails.  It’s not always bad, but surely there is a way to improve upon this paradigm?

So, let’s step back for a moment.  Our big win in the first place was these useful cubes that slice and dice data in a way that other systems in the enterprise simply can’t compare to.  If we want to expand upon this existing success, what do we put on the wish list?  What are my goals, and what do I want to provide for my users?

I want to give my users something that’s easy to use, has a short learning curve, leverages existing Excel/ad-hoc knowledge, and is cohesive. As for myself , I’m a bit more concerned about the back-end.  I want something that is easy to deploy, easy to update, adapts to changing business needs, and is scalable.  Since I know and love cubes so much, I also want to leverage my existing knowledge of Essbase.

Here’s where Dodeca comes in.  Dodeca complements Essbase functionality (particularly my existing Essbase functionality) very well, by providing everything on top of the cubes that I want to give to my users.  My deployment and update issues are essentially solved since Dodeca uses Microsoft ClickOnce technology — each time the user runs Dodeca on their workstation, a process checks against the current version that has been published to a central server, and updates files if it needs to.  This functionality is dependent on the client workstation having the .NET Framework installed — this is not some obscure third-party library either, so if your client workstations don’t already have it, it is not incredibly difficult to get setup.  Essentially in this scenario, we get the benefits of a client-side application (responsive interface, complex widgets), coupled with the distribution benefits that the net provides.  Also, since all content is stored and retrieved from a central server, we just have to update content in one place and our clients will pull it down the next time they use it.

Once a user launches Dodeca, they are presented with a list of Views that they have access to.  Views can be different reports, web pages, and other things.  Dodeca is highly configurable, so my following comments will reflect a typical usage scenario, but not necessarily the absolute way that things have to be setup.  I also find it useful to set a user’s default view to a web page that provides status updates and other informational messages.

A common Essbase activity with Excel is to refresh a particular report (or numerous reports, or mountains of reports) at the end of a period.  This means updating the time period in some fashion, and refreshing everything.  This is either done by hand or with a little home brew VBA action.  This functionality lends itself quite well to being implemented in Dodeca.  We can provide the same report in Dodeca by modifying the current Excel sheet a little bit, importing it, and setting a few other things (who has access to it, how the report gets updated, what database it comes out of, etc).  By doing this, we can now provide the same report, with user-selectable dimensional members (such as Time period, but just as easily Location, Department, Scenario, and so on) to anybody in the organization.  If we need to modify the report, we can do it in one place (the server) and seconds later, if a user pulls up the report, they now see the latest version.

So far we’ve just scratched the surface, but as you can see, we’ve addressed our needs and wants fairly well.  Deployment and upgrades are simple (and I am largely abstracted from a sometimes lethargic IT department), and content updates ridiculously simple.  My users have a cohesive (one window with multiple tabs) environment to do their work in, with all of the functionality of Excel.  As an administrator I have gotten to keep, leverage, and extend all of my existing Essbase infrastructure and functionality.  And best of all, these tools are not mutually exclusive in the least bit: my users can keep using the Excel add-in the love so much.  In fact, while in Dodeca, any sheet they pull up can be instantly sent to Excel just by clicking a button.

It works for me, it gets things done, and it’s a winning combination.  In the coming weeks and months I’ll be expanding more on this.  Happy Holidays!