I told you I just can’t stop adding functionality to my Google Sheets Essbase add-on. Now you can ZoomIn to a member’s children both in rows and columns.
I’ve also added indentation to the member names to reflect their level. This is much the same as in the Excel add-in.
Next I may add the ability to ZoomOut to a member’s parent.
Please let me know what you think in the comments.
I just can’t seem to abandon the idea of developing a Google Sheets Essbase add-on. Maybe I’m a little ahead of my time, or maybe I’ve got some of this guy in me. I don’t know. What I do know is that every time I fire up my personal Essbase development environment, a Google Sheets script editor seems to also magically appear. If I build it, maybe they will come.
With Oracle moving more and more services to The Cloud, I have to think that companies’ resistance to also moving at least some users to cloud-based office productivity tools like Google Sheets is destined to crumble. After all, the security model of Oracle Planning and Budgeting Cloud Services is exactly the same as Google Sheets. All your data is already in the cloud, so why not do your analysis there, too? And since a spreadsheet is really only useful if you can share it with others in your organization, which is where Google Sheets excels (sorry for the punny), why not eliminate the Excel middleman?
As the animation below demonstrates, the add-on currently has the ability to retrieve data from a range using the Essbase Query By Example (”QBE”) paradigm explained by Tim Tow in a blog post here. It actually even eliminates the need to prefix numeric member names with a single quote. This requirement can be extremely vexing to both users and admins alike. The add-on accomplishes all of this through the XMLA web service that you already have running through Provider Services. No additional software currently need be installed.
Of course, in order to also provide the ability to send updated data (a.k.a. lock & send; a.k.a. L&S) back to the Essbase server, it would be necessary to either install a Java API-based web application in-house or to enable the new Essbase Web Services that no one, outside of Oracle, appears to be using.
Before I expend any more effort on this add-on, am I tilting at windmills? Please let me know in the comments.
-Harry
Following are the bug fixes and enhancements released in version 5.0:
Following are the bug fixes and enhancements released in version 4.1:
Following are the bug fixes and enhancements released in version 4.0:
One of the most-requested enhancements in this release is the ability to parse an ASO export file without having access to the outline. This can now be done by selecting a file containing the outline in xml format, using the “MaxL export outline XML file” option below.
The screen below shows additional options added to the ASO Export Parser tab when logging on in “embedded” mode. You can now export an ASO database directly from the tab. It’s now also possible to export the outline to XML format, which will subsequently be used to parse the native export to column format. Please note that the XML file, if specified, will always be used instead of accessing the outline.
A limitation of using the outline XML for parsing is that Member Filters cannot be used since member functions like <CHILDRENOF and <DESCENDANTSOF are only available when connected to Essbase. I have some ideas to work around this which may make their way into the next release.
As hinted above, it’s now also possible to export the outline to XML directly on the Outline XML Parser tab:
You can see the other enhancements and bug fix in the version 4.0 change log. The new version is already available on the download page.
As always, if you have any questions or ideas for improvements, please let me know.
-Harry Gates
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
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
A common task I encounter is needing to filter a flat file using a list of Essbase members. For example, I have a data file that contains all months for all CostCenters. However, I currently only need to work with a subsection of the file’s data. Say, Qtr1 data for all of CostCenter1’s children.
Anyone who knows me would likely say, “Well, Harry, you’re a linux command line guru. Just grep what you need out of the data file into another file!” And that’s what I’ve been doing for years. I’d write something like: egrep -i ‘jan|feb|mar’ sourceFile.txt | egrep ‘CostCenterA1|CostCenterA2|CostCenterA3|CostCenterB1|CostCenterB2’ sourceFile.txt > filteredFile.txt.
This works well enough, but I’m sure those of you who’ve worked with Essbase for any length of time can see the obvious flaw in this method. It’s pretty apparent: what happens when CostCenterC gets added or CostCenterB has more children added? Or what if CostCenter1 has a total of 100 children? That’s one loooong command. There are ways to handle this using grep. You could add all the member names to be filtered to a separate memberSelection.txt file, then do “grep -f memberSelection.txt sourceFile.txt > filteredFile.txt”. Still, memberSelection.txt has to be manually updated to reflect changes in the Essbase outline.
I’ve had to perform this file filtering so often lately that I finally decided to create a utility to make my life easier. And to keep my life as simple as possible, I added File Filter to an existing tool – cubeSavvy Utilities.
Similar to the Member Filter in ASO Export Parser, File Filter has Member Selections:
Using my example above, I could specify a Member Selection of <CHILDRENOF Qtr1 to get Jan, Feb, and Mar. But where it really gets useful is being able to specify <CHILDRENOF CostCenter1. Sham-Pow! No more cumbersome grep files that I have to remember to update every time a new CostCenter is added. Essbase administrator nirvana:
The result above is running against a flat file with 1.25 million records. 12 seconds is not too shabby, if I do say so myself.
Please give it a test drive, kick the tires, and let me know what you think.
-Harry Gates
As promised when I introduced cubeSavvy Utilities, expanding the MDX Query functionality to handle PROPERTIES and PROPERTY_EXPR was my next task.
I’m happy to say that version 1.1, now available for download, incorporates this feature.
This version can also handle an empty Columns axis:
Or an empty Rows axis:
As you can see above, this gives you the ability to specify whatever you want in a Column or Row – and even by Dimension. Below is the MDX query from the empty Columns axis screenshot above. Note how I specify [Pkg Type] for the Product dimension property, but [ChineseNames] for Year.
SELECT {} on axis(0),
{crossjoin([100].children,{[Jan],[Feb],[Mar]})} PROPERTIES [Product].[Pkg Type], [Year].[ChineseNames] on axis(1)
FROM Sample_U.Basic
This flexibility gives you the power to control exactly what you want to see in the rows and columns. Therefore, I removed the 3 “Display Alias” checkboxes that were in the first version. Now you can let your MDX query do the talking. I love MDX and XMLA!
Happy querying!
cubeSavvy Utilities
I’ve wanted to combine my various Essbase-related tools into a single, integrated tool for a while. That tool is now called cubeSavvy Utilities. It encompasses my MDX Query Tool – XMLA Edition, ASO Export Parser, and MaxL Outline XML Parser.
I’ve also made some enhancements to the MDX Query tool’s functionality. The most obvious is the new ability to ‘Display Both Members and Aliases in Rows’, as seen below. However, it can also now handle queries that either have just COLUMNS or just ROWS. The next version will have expanded capability to display DIMENSION PROPERTIES and PROPERTY_EXPR. These are currently just ignored.
All 3 functions have been made scriptable, with the addition of a cubeSavvyUtilities.conf configuration file to specify the same parameters found in the GUI version. The configuration file also stores Essbase server information, like user name, password, server name, and APS url. I’m extremely security conscious, so the password is stored in encrypted format. Just enter it the first time and it’s encrypted for future uses. Running from the command-line/script is as easy as: java -jar cubeSavvyUtilities.jar out. The possible flags are ‘aso’ (for the ASO Export Parser), ‘out’ (for the XML Outline Parser), and ‘mdx’ (for the MDX Query Tool).
Following are the contents of a sample cubeSavvyUtilities.conf file:
#——————————————————————————-
#cubeSavvy Utilities, copyright 2015. Harry Gates
#Place quotation marks around entries:
# ASOExportParser.FileToParse=”C:\\Documents and Settings\\harry\\asosamp.txt”
# OR
# ASOExportParser.FileToParse=”C:/Documents and Settings/harry/asosamp.txt”
#To enter new Essbase.Password
#1) Enter new password value in line: Essbase.Password=”newPassword”
#2) Set: Essbase.PasswordEncrypted=false
#3) Essbase.PasswordEncryped will be set to true the first time the program is run as a script
#To specify filters:
# ASOExportParser.Filters=[“<DESC/Qtr1″,”<DESC/Senior”,”<CHILD/Digital Cameras”]
#To specify no filters, you can delete everything after the equals sign on the line: ASOExportParser.Filters=
#The MDX Query can span multiple lines by surrounding it with three quotation marks as follows:
# MDXQuery.MDXStatement=”””
# SELECT CrossJoin([Measures].CHILDREN, [Market].CHILDREN) on columns,
# Product].Members on rows
# from Sample.Basic
# “””
Essbase.APSUrl=”http://localhost:9100/aps/JAPI”
Essbase.ServerName=”epm”
Essbase.UserName=”admin”
Essbase.Password=”password”
Essbase.PasswordEncrypted=false
ASOExportParser.Application=”ASOsamp”
ASOExportParser.Database=”Sample”
ASOExportParser.FileToParse=”asosamp.txt”
ASOExportParser.OutputFile=”outputConf.txt”
ASOExportParser.Filters=[“<DESC/Qtr1″,”<DESC/Senior”,”<CHILD/Digital Cameras”,”<DESC/Sale”,”<CHILD/Price Paid”,”<DESC/Original Price”]
OutlineParser.FileToParse=”/Users/harry/test_124.xml”
OutlineParser.OutputFile=”/Users/harry/out124.txt”
OutlineParser.FieldDelimiter=”|”
MDXQuery.OutputFile=”/Users/harry/mdxResult.txt”
MDXQuery.DisplayAliasInRows=false
MDXQuery.DisplayMemberAndAliasInRows=true
MDXQuery.DisplayAliasInColumns=false
MDXQuery.MDXStatement=”””
SELECT CrossJoin([Measures].CHILDREN,[Market].CHILDREN) on columns,
[Product].Members on rows
from Sample.Basic
“””
#——————————————————————————-
I originally developed each of these tools to scratch my own Essbase development and administration “itches”, where Oracle had yet to provide a solution. I hope you find them useful, too.
Please let me know if you have any questions or ideas for improvement. I can be reached at harry.gates@cubesavvy.com
I’ve updated the ASO Export Parser to allow for filtering the output to only include those members specified on the “Member Filter” tab. You can see the allowable “Selection Type” commands in the screenshot below. Keep in mind that only level-0 members from the result set will be used. If your filter doesn’t result in any level-0 members, you will receive a message to that effect after clicking the “Parse using Filters” button.
You can download the ASO Export Parser here.
I’m really interested in any feedback you may have. Please let me know at harry.gates@cubeSavvy.com.
