Monthly Archives: July 2015

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):

Using Vess to view Essbase database properties for Sample.Basic

There’s a use case here too. I know of more than a few people that are dumping database stats using MaxL (such as to monitor a database/server stats over time).  So instead of running a script, dumping to a file, parsing that out (or whatever), you could slam the data directly into SQL (using ODI or just some tool that can execute SQL statements).

Next up, Vess models tables for the various dimensions in a cube. This is not unlike how ODI would create a model of a cube: one table per dimension. Here’s a view of some information from Sample/Basic:

Using Vess to view outline information

Outline functionality in Vess is not symmetric: you can read but updates are disabled for now. The use case here is that you could pull outline information without needing to use ODI (or something else) and possibly if you are using the Essbase Outline Extractor in your automation. Since Vess is already a fully functional SQL database, you could pretty easily do some interesting things like just grabbing all of the level-0 members in an outline (“SELECT * FROM SAMPLE.BASIC_DIM_MARKET WHERE LEVEL = 0”).

For now, Vess supports data loads to a cube (look mom, no load rules!). I wrote about this before too. Data reads are turned off for now (as in, you can INSERT but SELECT always returns nothing). It’s a bit of an interesting issue when you want to map this into a relational model.

Lastly, I started writing up the ODI technology for Vess. I think this is an absolute win for Vess because it would mean that you could build a nice clean technology/Knowledge Module that doesn’t have to use a bunch of Jython to glue things together: you can treat everything as a vanilla JDBC database model. No idea when this could be ready but you can rest assured that I’ll blog about it.

Vess Updates Substitution Variables

A colleague of mine is running into an issue with substitution variables and was looking for a solution that he could use to sync values up. He thought maybe Vess would be a good fit. Vess, as I have blogged about before, is a “virtual” Essbase JDBC driver. Vess maps Essbase concepts and crams them into a typical database model. For example, Vess exposes tables that model substitution variables.

In the case of server-wide substitution variables, there is a “VARS” table that has two columns: NAME and VALUE. For each application, there is another table that contains four columns: NAME, VALUE, APPLICATION, DATABASE.

As a quick aside, this might seem a little odd to have separate tables. After all, this table is notionally about the same as the Variables screen in EAS. Well, you have to kind of flip your thinking a little. Don’t think of variables as being only either server or a cube: think about in terms of what variables are applicable to a cube. In other words, if you ask Essbase what variables are applicable to the whole server, then this would be the global variables only. If you ask Essbase what variables are applicable to a cube, then it’s the cube, app, AND the server specific variables. This is one of the reasons there are multiple tables to model the variables.

Getting back on track, given that we have these tables and we can treat them just like normal SQL tables, we can do some interesting things. Let’s say we want to create or update a variable specific to an app that exists in the global scope. We can do this in one line:


INSERT INTO SAMPLE.VARS<br />
SELECT NAME, VALUE, 'Sample', 'Basic'<br />
FROM VESS_SCHEMA.VARS WHERE NAME = 'Foo';

What’s going on here? In Vess, a schema named VESS_SCHEMA is presented for server-wide things (server wide variables are in the table VARS in this schema, as shown above). The server VARS table only has columns NAME and VALUE.

Each application on the Essbase sever is modeled as its own schema. In this case, our favorite app – Sample – gets a schema named SAMPLE. This schema also contains a VARS table (containing columns NAME, VALUE, APPLICATION, and DATABASE).

Given these tables we have, it’s a simple matter of selecting the server variable with the name we want (in this case, a variable named ‘Foo’), and insert it into the variables for the Sample app.

Of course, if we wanted to for some reason, we could alter the name using normal SQL (string truncating, substrings, etc), or whatever. We can also delete variables, such as this:


DELETE FROM SAMPLE.VARS WHERE CUBE = 'Basic';

As I’ve said before, Vess continues to be an “interesting” proof of concept. As time permits I am filling out more and more functionality. At present, Vess models things like substitution variables, metadata you might see in MaxL or EAS (cube statistics, user sessions, etc), can load data to cubes, and can do certain outline related operations.

Vess is not available as a public download at this time but I have handed a few copies out to get feedback. I think Vess is just about good enough to be used in automation and other things. If you’re interested in using this in a production situation (automation or otherwise), please contact me to discuss support options.

Custom Drillbridge Reports feature

I have been waiting to write about this feature for over a year – and I finally can.

The newest version of Drillbridge – Drillbridge Enterprise 2.0 – contains an amazing capability that is incredibly powerful. Drillbridge Enterprise now includes the ability to plug in custom reports.

What are custom reports? Custom reports are new report types that can be plugged in to Drillbridge by developers, just like a calc script or a CDF is added to Essbase. Think of the most common Drillbridge report type: Drillbridge users know this as just a “Report” but under the hood, it’s actually a specific type of report – one that receives the POV from Smart View, Planning, or Financial Reporting and then executes a specially crafted Drillbridge query against a database and displays the results to the user.

What if we could stand on Drillbridge’s shoulders for help with interpreting the point of view, opening a database connection, pulling members from an Essbase outline, and more, but then completely customize what is shown to the user – all using just a little bit of custom code?

This is where custom report modules come in: a custom report module gets to leverage all of the power and flexibility of Drillbridge and its framework, but then completely take over what is shown to the user. Don’t just customize a column in a table: customize the entire display!

There are several compelling use cases I can think of off the top of my head that are well-served by custom modules:

  • Show images/thumbnails in the drill-through results with links to full-size copies
  • Call a web-service to pull data to display to the user
  • Generate custom page layouts

As an aside, for the past year I have been somewhat regularly been getting asked the question, “Can I get the results directly in Excel?” To which I have said, “No, but it’s just one click away – and I don’t think you want to do that anyway.” What I didn’t say (but had in mind) was custom reports, where we could easily drop in a  module that would show us images, a custom layout, or anything we could dream up – but more importantly, generate something that just does not fit into our spreadsheet world. So not only is data just one click away from Excel (as always) but we can now display absolutely anything we want to the user.

Sweet.

Do you remember Linked Reporting Objects (LROs?). This is them on steroids – and more. Any intersection in your cube is up for grabs to drill into something interesting – user drilling on Actuals? Let’s show them PDFs of the original invoices. User drilling on dynamic calc YTD members under Budget in an ASO database? Let’s generate a link to our internal website with budget info – whatever. How about we search Twitter for a certain hashtag related to one of our products… why not? The possibilities are literally endless. Not only are they endless – they are within reach.

One last thing, the custom report modules also open the door for another oft-requested feature: generating custom links. It is now trivially easy to take the POV from a user, use the Drillbridge expression language to transform it into a URL (such as drilling to an internal site, an OBIEE page, or whatever), and forward the user to it.

Custom report modules (including the link forwarding report I just mentioned) are available right now in Drillbridge Enterprise 2.0. While Drillbridge Enterprise has a list of compelling features, this one is my absolute favorite.

If you’re feeling adventurous (and you’re a Java aficionado), you might check out a bare-bones custom report example on the Drillbridge wiki – and I’ll be posting some interesting examples in the near future.

Introducing Drillbridge Enterprise

I am very pleased to officially announce the launch of Drillbridge Enterprise. This new software offering represents the 2.0 version of Drillbridge and complements the existing free edition of Drillbridge.

The existing or “plain” version of Drillbridge continues to exist as a free product. This allows organizations to continue downloading and using Drillbridge to enhance the value of their investment in Essbase.

Drillbridge Enterprise is a paid and licensed version of Drillbridge that comes with enhanced features and capabilities and is intended for organizations that have advanced integration needs as well as need software support. These advanced features include such things as automation integration tools, enhanced security, custom report modules, a Java SDK, RESTful API, and more. It’s a really great release that is available immediately and is licensed through Saxifrage Systems LLC.

Moving forward, development will be focused on Drillbridge Enterprise, while the free version of Drillbridge (also known as just Drillbridge or as Drillbridge Community Edition) will see updates mostly focused on addressing one-off issues as needed.

Drillbridge Enterprise is built from the same solid code base as Drillbridge that has proven so robust, flexible, and easy to configure. In just the week alone after Kscope15 (where Drillbridge had a well-attended session), at least three different people independently downloaded, installed, and setup Drillbridge at their companies.

Clearly, people are hungry to enhance the value of their existing systems in a simple, non-invasive, and incremental way. I am absolutely astounded at the number of times that I get emails from people saying “It just works!”. It brings a smile to my face.

That said, there are some truly incredible features available right now in Drillbridge Enterprise 2.0, it is competitively priced, and I can’t wait to get it into the hands of Essbase users around the globe. Please check out the Drillbridge website for more information, and as always, don’t hesitate to reach out to me with any questions.