Thursday, April 20, 2017

Breaking News! Dodeca Spreadsheet Management System Certified on Oracle Analytics Cloud!

Now that the Oracle Analytics Cloud, or "OAC", has been released, we had to get serious about our work with one of the Oracle Analytics Cloud components, the Essbase Cloud Service, or "EssCS" for short.  You would think that we should have been working hard on EssCS for quite some time, but we had been assured by Oracle product management that the Essbase Java API would be available in EssCS.  Of course, Dodeca was built using the Essbase Java API and thus we expected that support for EssCS would be very easy.

We got access to a production version of the EssCS last week and started our work.  As promised by product management, the Essbase Java API is available in EssCS and, believe it or not, we did not need to change a single line of source code in order to support the Essbase Cloud.  We did, however, have to update our build processes to use Java 8 instead of the decrepit Java 6 used in Essbase 11.x.

As far as configuration inside Dodeca itself, the only change we made was that we configured the APSUrl in the Essbase Connection object to point to the Essbase Cloud APS instance.  Note that the URL format has changed in the cloud.  The Java API was accessible in Essbase 9.3.1 through Essbase 11.1.2.4 using the format:

http://<server>:<port>/aps/JAPI

In the cloud, this has changed to:

http://<server>:<port>/essbase/japi

The Essbase Connection configuration looks pretty much the same as the configuration for an on premise connection configuration:








Of course, the Dodeca views look identical when run against an on premise or a cloud server:



In summary, it was trivial to test Dodeca using EssCS.  Every single Essbase functionality that we use in the product, from data grid operations to metadata operations and even report scripts, worked exactly the same as it does against an on premise Essbase cube.  Based on our testing, we are certifying the Dodeca Spreadsheet Management System to work on the Oracle Analytics Cloud.

We have a number of innovations we plan to introduce in the near future aimed to improve the Essbase Cloud experience, so stay tuned.  If you are planning to come to Kscope17 in San Antonio, plan to attend the Dodeca Symposium and you may just be the first to see of these cool new things!


Thursday, February 16, 2017

Kudos to the Dodeca Support Team!

Here at Applied OLAP, we have a great support team that we have tasked with doing their best to make sure our customers are happy with their Dodeca software.  On most days, they are answering emails and firing up impromptu support webcasts to help our customers with any questions they have.

Today, we got some great feedback from a customer after a popup support webcast.  Here is an email I received today from Edgardo Rodriguez at Wall Street law firm Sullivan and Cromwell (email used with permission):

Hi Tim – just wanted to drop you a quick line on the tech support I received this morning.  Kevin & Rodney were helping me with a headcount report.  Have to tell you – they were great. They helped me leverage some of their code and walked me through the finer points of using it under our scripts – all under an hour!  They also turned me on to using the SQL retrieve as a possible solution (which I will be using shortly).

I appreciate you support more than you know. 

Just thought you should know..

Regards

Ed

So, here is a special call-out to our support team.  Way to go!

Monday, January 2, 2017

Essbase Cloud Service Webcast Available Online

Essbase Cloud Service is coming soon and, if you haven't had a chance to learn about it, here is how you can learn about it at your leisure. Oracle had a public webcast about two weeks ago and have made both the webcast and the slides available; you must have an Oracle login to see it. Here is the link to the EssCS webcast:

EssCS Webcast

And here is a link to the slides:

EssCS Webcast Slides

Essbase Cloud Service has some exciting new functionality that you should check out, even if you plan to keep your Essbase installations on-premise.  Over time, I would expect that most, if not all, of the innovations you see will be released in the on-premise version of Essbase.  If I were a betting man, I would guess the timing to be likely near the end of 2017 with the lrelease of "EPM 2017", which is the code name for the next major on-premise release,

Enjoy!

Saturday, November 26, 2016

Another Thanksgiving Email

Here is another email we got Wednesday from a Dodeca Excel Add-In for Essbase customer.  This customer has a lot of VBA macros running some automation with Essbase and asked for some assistance.  We told it was as easy as replacing they Essbase function declarations file with our Dodeca Add-In  function declarations file, and then setting the variables that contain the location for the Dodeca-Essbase server.  In other words, replace this file:

With this file:





Easy, right? Here is the email:

I hadn't had a chance to test this massive file out with the latest version of the add-in yet, but was more than pleasantly surprised when I replaced the add-in code for the Dodeca wrapper and the only thing I had to do was change the connection to our new server and the file was live! It's truly drop and go! Thanks so much!!!!



Wednesday, November 23, 2016

Happy Thanksgiving (Featuring an Email That I Am Thankful For)


I get tons of email every day and often have hundreds of emails that have to track on a daily basis which explains, in part, my absence from doing recent blog entries.   Most of the emails I track are sales and support relating to Dodeca.  After all, with Dodeca, the buck stops here, right?  I also get some spam in the mix.  Sometimes, there is an email that really makes my day.  Here is one of those types I received this morning:

From: (masked)
Sent: Wednesday, November 23, 2016 8:48 AM
To: Applied OLAP Support
Subject: Happy Thanksgiving!

Although we are a smaller Essbase shop at 105 users, our users are strong advocates for Essbase and use it extensively. So, it is great timing to say how thankful I am to you and your teams for developing the Dodeca Essbase Add-In. At the end of the day, it’s the user experience that drives the support for Essbase and your product will excite our user base! [And, it requires little or no support from my team!]

Happy Thanksgiving to all!


Wow!  This email really made my day!  To our new customer that sent this email (and who graciously granted me permission to post her words), Happy Thanksgiving to you as well.  We hope this is the first of many Thanksgivings that we work together!


Saturday, September 24, 2016

Thoughts/Info on Essbase/EssCS after Oracle Open World 2016

I was at Oracle Open World last week and have some notes to share on the upcoming EssCS (Essbase Cloud Service) product and on Essbase in general.

EssCS will be (Infrastructure as a Service (IaaS) Oops..  I realized I blew that one when I read Cameron's post on Network54!) Platform as a Service (PaaS) product and will be priced on the number of cores and memory.  I believe there will be a metered and an unmetered pricing as well (metered meaning pay per cycle or some other usage measure).  According to presentations at Open World, which did have safe harbor statements meaning "do not make decisions based on this info as it could change", there will be options for 1, 2, 4, or 16 physical cores ("OCPU's") and 7.5 to 15 Gb of RAM.  In addition, it will be an updated version of Essbase that is not the current on-prem version.  It will feature, among other things:


  • The new Java Agent running in Weblogic that moves security from the essbase.sec file to a relational database.
  • Simplified security
  • ASO / BSO / Hybrid
  • Sandboxing and scenario management - what if's without copying data with workflow for approval to merge into given scenarios
  • Cloud-based UI
  • Ability to specify/create an Essbase cube from Excel 
  • A cube template library ("app store" type of thing)
  • A web-based outline editor (though most editing should be done in Excel)
  • EssCLI (Essbase Command Line Interface) - a sort of EPMAutomate for Essbase
  • The Essbase Java API and a new REST API (which is currently being engineered)

I do not remember hearing any dollar amount for EssCS at Open World.  I expect availability in the next 3 to 6 months though it wouldn't surprise me if it were to slip further.

As far as on-prem Essbase updates, I would expect that the updates we see in EssCS will go on-prem as part of the EPM 2017 on-prem release which Oracle currently believes will be delivered late in 2017 (also subject to safe harbor, etc).

As far as how Oracle is selling Essbase, Essbase is now firmly in the BI organization and is being sold by the BI reps; EPM reps do not generally sell Essbase.  To the Essbase team, EPM is important as they are an internal customer, but EPM is not their only customer.  As such, I saw at least one presentation that promoted the idea of customers writing custom planning applications using Essbase.  While some people I talked with thought that approach muddled the EPM message for customers, I see it as a situation where if they don't compete in the custom market, then someone else will.  As someone who frequently is involved in complex applications where the EPM Planning product may not be a fit, I am thrilled to see that message from Oracle.




Wednesday, August 17, 2016

Cool Essbase MDX Stuff – Dimension Properties Edition

I have always liked MDX as it exposes very powerful operations for creating member sets.  It has this amazing ability to take a set of members, union it with another set of members, intersect it with yet another set of members, and then exclude members from yet another set.  This is very powerful.

Recently, I was talking with a customer about some creative uses for MDX and they told me about some cool things they had done with MDX.  Kudos to George Cooper and Esam Jaber at Gap for showing me some creative MDX.  Though this is a really cool and new-to-me technique, after doing some online searches I found that the technique isn’t new to everyone.  Both Gary Crisci and Harry Gates have blogged on it already at http://garycris.blogspot.com/2014/03/mdx-queries-to-get-parentchild-and.html and http://www.cubesavvy.com/cubesavvy-utilities-updated-mdx-capabilities, respectively.

This technique, which gives developers access to outline information that was not previously available, also seems to be lightning fast.  I decided maybe I need to write on it as well and, of course, put my own spin on it by showing how we can use this in the Dodeca Spreadsheet Management System.

So, what is it?  They are called dimension properties and can utilize the PROPERTY_EXPR function.  This function provides the ability to query for, and return, members related to a given member.  The ability to return related members, on the same row, is something that is unique to the Essbase query languages.  The Grid API, or query-by-example and used to retrieve data from Essbase in the classic add-in, Smart View, and Dodeca, cannot do it out of the box.  The Essbase Report Script language, which is frequently used for exporting data, cannot do it either.  Regardless, we have customers who have asked to how have the parent member and/or the grandparent member on the same row as a member.   In the remainder of this blog post, I will explain dimension properties, and more specifically, the PROPERTY_EXPR function.

For this post, I will use the Geography dimension of the ASOSamp.Sample database.  The Geography dimension features members from different regions of the United States including details of the Region, the State, the City, and the Postal Code.  Here is a screenshot of some members in the Geography dimension:

Geography dimension members

With this dimension, let’s suppose you would like to get outline information at the city level, or level 1 in Essbase-speak.  In addition, let’s say you would like to have the state and the region as well.   Finally, while we are at it, maybe you also want to know the generation number of the member.  With dimension properties and the PROPERTY_EXPR function, you can get all of that information in one trip to the server.  Here is the query:

Select {} on COLUMNS,
Hierarchize(Descendants(Geography, Geography.Levels(1), SELF_AND_BEFORE), POST)
DIMENSION PROPERTIES
 GEN_NUMBER,
  PROPERTY_EXPR(Geography, MEMBER_NAME, Ancestor(CurrentAxisMember(), Geography.Generations(2)),"Gen2"),
  PROPERTY_EXPR(Geography, MEMBER_NAME, Ancestor(CurrentAxisMember(), Geography.Generations(3)),"Gen3")
 ON ROWS
FROM ASOSamp.Sample

Let’s break down the query to examine the components.  First, the Column axis specification is simple:

Select {} on COLUMNS,

The Column axis specification contains an empty set, but why do you need to even specify a set at all?  The Essbase MDX specification states that a query cannot skip axes based on a set order.  It seems much more natural for me to get data back on the second axis, or the Row axis, and as the Column axis is the first specified axis, a query cannot skip the Column axis and specify a Row axis.  As we really aren’t looking for any data to be returned in this query, then we can just use an empty set.

Next, let’s look at the Row axis specification:

Hierarchize(Descendants(Geography, Geography.Levels(1), SELF_AND_BEFORE), POST)

Let’s split this into sections starting from the inside out.  The Descendants function returns, naturally, all of the descendants of a given member down to, and including, level 1 members in the outline.  For this database, this query will return the Geography dimension down to the City level, but will not return the bottom, or zip code, level.

The member set returned by the Descendants function is then sorted by the Hierarchize function.

Hierarchize(Descendants(Geography, Geography.Levels(1), SELF_AND_BEFORE), POST)

The POST argument specifies that child members are sorted before their parent as they are in the spreadsheet add-ins.

Next, the DIMENSION PROPERTIES modifier for the Row axis specification provides the ability to return additional outline information related to each member returned in the set.  The DIMENSION PROPERTIES specify that the generation number, the related generation 2 and generation 3 members are returned for each member in the set.

DIMENSION PROPERTIES
 GEN_NUMBER,
  PROPERTY_EXPR(Geography, MEMBER_NAME, Ancestor(CurrentAxisMember(), Geography.Generations(2)),"Gen2"),
  PROPERTY_EXPR(Geography, MEMBER_NAME, Ancestor(CurrentAxisMember(), Geography.Generations(3)),"Gen3")

The GEN_NUMBER argument is self-explanatory, so let’s look at the PROPERTY_EXPR function to see how it works.  Consider this portion of the statement:

PROPERTY_EXPR(Geography, MEMBER_NAME, Ancestor(CurrentAxisMember(), Geography.Generations(2)),"Gen2")

The first argument is the dimension name, so that is easy enough.

The second argument, called the property_name argument, has a number of valid values as documented by Oracle in the documentation.  Valid values are MEMBER_NAME, MEMBER_ALIAS, LEVEL_NUMBER, GEN_NUMBER, IS_EXPENSE, COMMENTS, RELATIONAL_DESCENDANTS, MEMBER_UNIQUE_NAME), an attribute dimension name, an alias-table name, or a UDA.  At the time of this writing, the current docs are located at http://docs.oracle.com/cd/E26232_01/doc.11122/esb_tech_ref/frameset.htm?mdx_property_expressions.html.

The third argument is called member_value_expression and this is where the magic happens.  In this argument, you can use a number of functions that return exactly one member that is related to the current member.  In my example above, the Ancestor function is being used to return, for each member in the set as specified by the CurrentAxisMember function, the ancestor of that member at a given generation.  Among other functions are functions that allow you to get the parent, first child, next sibling, or previous sibling of the current member.

The fourth and final argument is used to give a title to the column containing the extended information.

So, now that we know a bit about this syntax, what does it look like in EAS?  

MDX dimension properties in EAS
In their blogs, both Gary and Harry talk about ways to actually consume this information.  Harry wrote a special interface to display dimension properties returned by MDX.  Gary talked about the idea of using an Excel macro to parse the output.  I had my own ideas on how to use dimension properties in Dodeca.  I setup a simple Dodeca report and used a only 3 lines of our automation language, workbook scripting, to build the view.  Here is screenshot of my simple Dodeca view.

Dynamic MDX View in Dodeca
While running this view in Dodeca, the user can filter based on Geography, Product, and Stores dimensions and the report is highly dynamic.  The automation in Dodeca performs these tasks:
  1. Returns members and properties as the descendants of a user-selected Geography member
  2. Retrieves and places the dimension properties and the members on the worksheet
  3. Places the selected Product and Stores dimension members in the proper location
  4. Retrieves Essbase data into the worksheet
  5. Creates Excel grouping based on the generation number
I will leave the step-by-step of creating this view in Dodeca to another blog post.

So, how could you use dimension properties?