9.00pm

Database stats and outline viewing with Vess

I had a little bit of time this weekend and dusted off Vess for some updates. I was able to greatly enhance the functionality and perhaps more important, widen the number of use cases that it could be used in.

I already wrote about a use case previously: using SQL to create a substitution variable. Vess supports fully symmetric substitution variable updates. That is to say, you can create, read, update, and delete substitution variables on your Essbase server just by changing the data in the proper table. So just to elaborate on my thoughts on the previous post, I think there are some nice use cases here. Of course, there’s nothing wrong with MaxL, but if you have an instance where you are dumping substitution variables via MaxL and scraping the ASCII art off of them in order to get to the real variable values, Vess offers a cleaner approach.

As of now, Vess also provides a window in to a whole slew of properties on various Essbase objects: server, application, and database. Here’s a view of some database properties (as shown in a generic JDBC GUI with Vess configured as a driver):

8.49Pm

Drillbridge vs. Essbase Studio

I get asked a lot about implementing drill-through with Drillbridge versus implementing with Essbase Studio. First of all, this question itself is kind of interesting to me – because many people look at Studio as a means to an end simply for achieving drill-through. Drill-through is one of the aspects of Essbase Studio, but certainly not the whole story.

Essbase Studio is a good tool. It’s the successor to Essbase Integration Services, which I was a huge fan of, and my blog articles from yesteryear continue to get a lot of web traffic, owing at least partly to their being very few EIS articles on the web.

In terms of implementing solutions with Essbase Studio, the most common scenario I see at organizations is that they want drill-through, and this alone drives the decision to use Essbase Studio. So the developer starts going through development: creating fact tables, dimension tables, foundational Essbase Studio elements, cube models, and deploying a cube.

Let’s take things a step further and think about the pieces involved with automating and managing the cube: some MaxL to deploy it via automation, ETL processes to load and update the dimension/fact tables, creating the drill-through reports with custom SQL, writing calc scripts, and more.

The point of all this isn’t to make Studio seem like a lot of work. It’s a reasonable amount of work if Essbase Studio solutions fit into your organization (more on that in a moment). I think it’s an unreasonable amount of work if you only want drill-through.

Implementing a solution with Studio just to get drill-through is what I sometimes call “backdoor drill-through” – basically overlaying an existing solution with Studio drill-through just so you can get drill-through, but not otherwise leveraging much of anything that Essbase Studio brings to the table.

That said, I think if your organization has a “strong” relational database model (e.g., a well-designed database designed for some system that has proper primary/foreign keys, constraints, and so on), plus some other unit of the business takes care of updating it on a regular basis, I think that’s a really good use-case for building out a cube with Essbase Studio. But why?

Again, it seems like most solutions built with Essbase Studio are built from tables and data specifically created for Studio – in other words, someone has to endeavor to design ETL processes to load tables from wherever that are specifically formatted for Studio and the cube to be built with it. One of the problems with this approach, however, is that many of us are cube experts and not necessarily relational database experts. You don’t have to be a relational database expert to make use of Studio, but it won’t hurt – in fact, it can help quite a bit.

So again, I think Essbase Studio can be a great choice to spin up multiple cubes when you are tapping in to an existing relational model. Not as much if you have to create that model yourself. Essbase Studio solutions should feel “natural” – you should be able to get away with the default data load code and stock drill-through code if you have designed everything correctly. All too often there is a custom data load and completely custom drill-through (which is often a sign that the data model is incorrectly built).

All this is why Drillbridge really shines in terms of drill-through ROI: You can keep your existing cube, automation, administration, and everything. Your relational data can come from anywhere. You can build completely custom web reports, drill from drill reports to other reports, and more. Drill-through on those dynamic calc YTD members in your alternate Time hierarchy are super easy to support. There is no 1,000 item limit on Oracle drill-to-bottom queries, and more.

But best of all: fewer moving parts, rapid deployment, and keep your processes exactly the same. I think this really resonates with organizations around the world. The free edition of Drillbridge is now in production at over 30 companies around the world (probably more), and there is a growing list of happy Drillbridge Enterprise customers. Even better: I get emails every single day from people that love Drillbridge and are enhancing the value of their Essbase solutions for their users.

8.46Pm

Book Review: Developing Essbase Applications – Hybrid Techniques and Practices

I am very pleased to be able to review Developing Essbase Applications: Hybrid Techniques and Practices for you today. I can’t believe it has already been over three years since my review of the first Developing Essbase Applications book. As with before, this book is a collection of in-depth chapters on various subjects, written by some of the best and brightest out there in the Essbase community. The author list is pared down slightly from before and contains some new, but familiar names.

Again leading the cat herding editing efforts is the venerable Cameron Lackpour, along with John Booth, Tim German, William Hodges, Mike Nader, Martin Neuliep, and Glenn Schwartzberg. Those of you that frequent OTN, the Network54 Essbase Forum, or conferences such as Kscope will recognize these names quite easily. I am quite honored to consider these authors my friends and colleagues.

The DEA reader will be treated to several compelling chapters on incredibly contemporary Essbase topics, including the following:

Exalytics

Hybrid Essbase

Young Person’s Guide to Essbase Cube Design

Essbase Performance and Load Testing

Utilizing SQL to Enhance Essbase

Integrating OBIEE and Essbase

Managing Spreadsheets with Dodeca

Smart View

Essentially, the book takes a divide and conquer approach where each author tackles one or more of the chapters. Now, when it comes to programming books, of which I am a fairly avid reader, I am not a fan of the “cookbook” approach – wherein the book is a collection of disparate topics. In the programming book realm these books tend to read like a bunch of polished up blog posts. For this Essbase book, I think the approach works wonderfully. I have to say that none of the chapters feels like a glorified blog post. They are incredibly densely packed with useful information, and perhaps more usefully, they are packed with an incredible amount of insight and pointers that are derived from countless hours of efforts on the part of the respective authors.

I remember talking to Cameron when the first book came out and I told him that it was a good book, but I wasn’t sure that it was a great book (in retrospect I think I was being overly critical). And I remember that he told me that if you can learn just one or two things from the book, it’d be worth it. And that notion really resonated with me.

Along those lines, the chapters on Hybrid Essbase, Performance and Load Testing, and Smart View are worth the price of admission on their own. The other chapters are also quite compelling but your particular skill set will affect how much you get out of a chapter. For instance, I have done my fair share of SQL work, so I found Glenn’s chapter on Utilizing SQL to be an interesting treatise on Essbase and SQL but I didn’t happen to personally get a lot of it. That said, while I take familiarity with SQL for granted, I now know that SQL isn’t in every Essbase developer’s wheelhouse (but it should be!).

So, does this book pass the “did you learn one or two things from it?” test?

Absolutely.

If you are at all serious about being an Essbase developer, I can easily and whole-heartedly recommend that you add this book to your collection. This is a no-brainer if you are a developer or a consultant. This is content from people at the top of their games that have put in countless hours to compile and write this content on incredibly useful topics. If you gain one insight on Exalytics, Hybrid Essbase, Smart View, OBIEE, SQL, cube design, performance testing, or learn something about the awesome Dodeca software, it will have been easily worth it.

If it were me, I might have spiced this book by adding in a Jason Jones chapter on ODI and Hyperion or some other interesting things, but I can’t fault them for that (next time, Lackpour, next time).

Nice job, guys. Now go buy this book.

8.44Pm

Introducing PBJ: The PBCS Java Client Project

I’d like to introduce the forthcoming availability of a new open source project called PBJ (or PB&J if you want): A Java client for PBCS (Planning and Budgeting Cloud Service) that serves as a clean, robust, and rapid way to work with the PBCS REST API in Java.

The PBCS REST API provides similar functionality to the EPM Automate tool and can perform such actions as exporting data, adding members to an outline, running business rules, and more. The REST API is an exciting development as Planning continues its march to the cloud, because it will allow integration from many languages and tools.

Consuming a REST API isn’t necessarily hard, but there are a lot of moving parts – connecting, managing credentials, invoking the right method, parsing the output, and then deciding what to do with it. Most of this is pretty boilerplate code that can get verbose pretty quickly.

Therefore, I thought it would be nice if there were a high-quality library that could easily be used and reused. This is where the PBCS Java Client – PBJ – comes in: it is a freely available library that is released under the Apache Software License. The ASL is a very generous license that essentially means you can do anything you want with the code. My hope is that this library becomes the de facto client for using Java to work with the PBCS API.

As an example of how PBJ makes things simple, consider the following example of connecting to a server and running a business rule:

PbcsClient client = new PbcsClientImpl(server, identityDomain, username, password);

PbcsApplication app = client.getApplication("Vision");

app.launchBusinessRule("AggAll");

Here’s a small example of checking the status of a job:

PbcsClient client = new PbcsClientImpl(server, identityDomain, username, password);

PbcsApplication app = client.getApplication(appName);

PbcsJobStatus jobStatus = app.getJobStatus(558);

System.out.println("Job status: " + jobStatus);

Three lines of code and we have connected to our PBCS server, grabbed a particular application, and then invoked a business rule on it. At the moment the API supports running business rules, exporting data, refreshing cubes, downloading files, and more.

PBJ is under active development and will be released in the near future. Contributions from others are entirely welcome. Not every PBCS feature will be supported on day one. Please let me know if you’d like to contribute to or test out the API.

8.40Pm

Copy Application Utility For HFM 11.1.2.4 - Well, sort of...

Oracle has just released a new patch for Financial Management 11.1.2.3. The patch delivers what many (if not all) customers who have adopted the latest HFM release have been asking for: a version of the Copy Application utility that works with HFM 11.1.2.4. The following actions are possible using the updated utility:

Copy HFM applications between different 11.1.2.4 environments (both need to have minimum patch level of 11.1.2.4.100)

Copy HFM applications from older release (11.1.2.x) to 11.1.2.4. The Upgrade Applications taks must be performed in EPM System Configurator afterwards.

The utility included in the PSE is the same old Windows program that has been bundled with HFM for ages. The only real change is that is has been fixed to correctly handle the APPNAME_CC_LOCKtables used by latest HFM versions. Oracle must have been in a hurry to re-release the tool though because copying HFM 11.1.2.4 applications has not been made very convenient:

"After successfully copying the application to the source DB, the source HSX_DATASOURCES table needs to be manually updated in order to add the application in Consolidation Administration."The reason why this manual database tweak is required is that there is a new column added to the HSX_DATASOURCES table in HFM 11.1.2.4. I can't imagine that detecting the HFM version and using the correct INSERT statement would have been that hard a task to accomplish... But this is what we have to work with right now. You can use the following SQL statement to add your newly copied application so HFM will detect it (in case an application didn't already exist by the same name):

INSERT INTO HSX_DATASOURCES(product,appname,description,dsn,adminmode) VALUES ('Financial Management','APPNAME','Application description here','',0);

As pointed out by Marc Reuter, another option is to create an empty application in the destination environment and use this application as the target. This way you can skip the manual step of running the SQL statement.

Please also note that this version of CopyApp requires HFM patch level 11.1.2.4.100 or higher. I tested the utility by copying an application between two environments running HFM 11.1.2.4.101 and it did the job without any apparent hitches. The need to manually tinker with the database is annoying but compared to the Application Snapshot feature the old utility is still a lot faster and more reliable.

Note: When using this CopyApp version to copy HFM applications from an older release you still need to run EPM System Configurator to upgrade the application tables to the latest version!

8.37Pm

I set the following variables before running:

set WSSAMP=E:\Oracle\Middleware\EPMSystem11R1\products\Essbase\aps\ws-samples

set JAVA_HOME=E:\Oracle\Middleware\jdk160_35

set PATH=E:\Oracle\Middleware\modules\org.apache.ant_1.7.1\bin;%JAVA_HOME%\bin;%PATH%;

ant -f %WSSAMP%\build.xml clean compile

On to the first issue, as I mentioned there are three WSDL files which describe the functionality of the web service, these should be accessible through the following URLs:

http(s)://<server>:<port>/essbase-webservices/DatasourceService?wsdl

http(s)://<server>:<port>/essbase-webservices/AdminService?wsdl

http(s)://<server>:<port>/essbase-webservices/QueryService?wsdl

From 11.1.2.3.502 the WSDL URLs for the Essbase Web Services have been disabled by default, if you try and access them directly in a browser you should see:

To enable them you will need to start up the WebLogic admin server and log into Enterprise Manager through:

http://<adminserver>:7001/em

Select the Provider Services WebLogic managed server and then Web Services

Select the “Oracle Infrastructure Web Services” tab, under the Web Service Name click “AdminServicePortType” Endpoint Name and then the configuration tab.

The WSDL parameter will be set to false.

Also set WSDL to true for the following Endpoint names “DatasourceServicePortType” and “QueryServicePortType”

Once set restart the provider services web application.

The WSDL URLs should now be accessible.

So now I can try and compile again.

This time it was successful and all the java files have now been compiled into classes.

You can now go and take them and start coding in Java to access the Essbase Web Services.

It is also possible to compile and run the sample programs directly from command line using Ant.

To do this you this you can edit ws-build.properties and set the sample.program property value and then execute:

ant execute

or you can specify name of sample program using:

ant execute -Dsample.program=<Sample Program>

In the following example I execute the Admin sample program which starts the Essbase Sample application, pings the application and then stops it.

8.27Pm

Simplified planning issue when offloading SSL

Recently I have seen an issue in 11.1.2.4 with simplified forms where SSL is being either terminated at the load balancer or OHS.

If you are on 11.1.2.4 and are not using any flavour SSL or have implemented full SSL then you will not come across this problem.

As the simplified interface is the way forward for planning and over time the standard interface will disappear then there is a possibility you may run into this issue so I thought I would write it up.

So let me go through an example of the problem first where SSL is being terminated at OHS, so basically you will be accessing planning over https via OHS and then OHS proxies to the planning WebLogic managed server over http.

You should not see any issues with functionality until you try to use a simplified form.