Pulling Essbase data into Google Sheets

I created a thread on network54 to gauge the interest in a Google Sheets Essbase add-in. I developed a sidebar that can run an MDX query against an Essbase database and return the data to a Google Sheet.

The following video demonstrates this functionality: google_sheets_POC

As I mentioned in the network54 thread, please keep in mind the inherent limitation of Google Sheets is that it can only connect to a publicly-available (i.e. internet) URL. As we all know, most companies keep Essbase walled off from the internet behind their firewall. A couple I've worked with have, however, opened up a secure port (https) to the Provider Server URL (e.g. https://CompanyAPS:13080/aps) for partner companies to access data. So it's not completely impossible that companies would be amenable to this option.

I can understand IT Security's reluctance to expose the APS https URL to an external web server, but risks can be mitigated. It is a secure (https) protocol and access is still controlled by Shared Services usernames and passwords passed from the Google Sheet.

If there is sufficient interest, the next step would be to add the functionality contained in the Essbase Excel add-in (and Smart View) to the Essbase Google Sheet add-on.

Please leave a comment if this is something you are interested in.

Thanks,

Harry Gates

cubeSavvy Utilities 3.0 - XML Outline Editing

I was extremely excited to see XML Outline Editing show up in the 11.1.2.4 Essbase New Features. You see, I despise load rules. They are as close to evil as a software feature can get. Their hundreds of conflicting settings hidden away, just waiting to ruin your weekend. Not to mention their maddening propensity for corrupting at the least opportune moment. It's fair to say that anyone who's used them has been subjected to their fair share of pain.

The ability to export an outline to XML got us halfway there back in 11.1.2.0. Now that we can also make changes to outlines using XML, we can finally relegate these binary-only relics (a.k.a. load rules) to the dust-bin of Essbase history where they belong.

As with any Oracle product, however, there is a gotcha. In the case of XML Outline Editing there are two. The first is that this functionality is only available through the C and Java APIs. Oracle appears to be in the process of replacing EAS, having deprecated the EAS Java API. Hopefully the new tool will be better.

The second catch is one that appears to have fooled most people who have only read the Oracle announcement of this feature and not actually looked into its implementation: By XML Outline Editing Oracle doesn't mean that you can take the XML output from the MaxL "export outline" command, edit it, and load it back to the outline. The XML that allows you to make changes to the outline actually looks like this:

<mbrAdd mbrName="Yacht75" parent="Larry's Toys"></mbrAdd>
 <mbrUpdate thisMbr="Yacht75">
  <mbrInfo>
   <alias aliasTable="default" alias="PoochYacht"/>
   <alias aliasTable="LongName" alias="Fido's Oversized Pool Toy"/>
   <dataStorage>storeData</dataStorage>
   <consolidation>+</consolidation>
  </mbrInfo>
 </mbrUpdate>

As you can see, this XML looks nothing like the XML output from "export outline". The dream of being able to directly edit that XML, then load it back in is off the table for now. Oracle even made it hard to find documentation for the commands they have exposed. The Java doc has zero information. As is usually the necessary when this is the case, I had to turn to the C API. (This really makes me wonder if, in fact, the Java API has higher priority as Oracle usually claims.)

The EssBuildDimXML documentation does a good job of explaining the new XML tags/commands and even provides an example XML document. Be aware, however, that this example document doesn't work. It tries to make Ratios a sibling of Margin, rather than its parent, Profit. Since Profit is dynamic calc, code no worky. The frustrating part is that the function call asks you to include the name of a file to which errors will be logged. This also doesn't work. No matter what I tried, I couldn't get errors to log. Even worse, errors are never even thrown, so you have to actually check your outline to ensure your changes went through.

In order to make this new functionality more accessible to those who don't code using either the C or Java APIs, I've added the ability to call EssBuildDimXML from cubeSavvy Utilities. There's a new tab in the GUI and a new section in the conf file for running at the command line or in scripts.

Please download cubSavvy Utilities and let me know what you think.

-Harry Gates