Evolution of Essbase: new URL-based drill-through showed up in 11.1.1.3

Continuing on with the idea of getting insight into the Essbase feature set over time, as viewed through the lens of its Essbase Java API evolution, you can quite clearly see that the open/URL-style drill-through (as opposed to classic LRO-based drill-through) showed up in version 11.1.1.3, which in fact is pretty much the only thing that seemed to get added to this particular release, Java API-wise, along with some ancillary drill-through methods/functionality in some related classes.

More near to my heart: this is the exact functionality that paved the way for Drillbridge! Although it wasn’t available as a feature on day 1, subsequent versions of Drillbridge gained the ability to automatically deploy drill-through definitions to a given cube, and it uses exactly these API methods to accomplish it.

Drillbridge as drill-through solution with CSV data and replacing Access

An interesting use-case has come up with Drillbridge recently where drill-through is currently being “handled” with an Access database. I put the quotes around handled because the current solution requires the user to look at the current POV and then go fetch the corresponding data from an Access database. You might be thinking that this setup is horribly sub-optimal, but I wouldn’t characterize it as such. In my career on all sides of Hyperion – a developer, a consultant, and software developer – I have seen this pattern (particularly those involving Access) pop up again and again.

Access is often (perhaps all too often) the glue that binds finance solutions together, particularly in cases like this involving drill-through. It’s cheap, you can use it on the network simply by dropping the file onto a share drive, it gives you a quick and dirty GUI, and more. Many EPM projects I have been on involve many deliverables, often including drill-through. And all too often those projects had to cut it due to budget and time constraints. And if it gets cut, sure, finance might have to do the “quick and dirty” option like this with Access.

Now, the request du jour: use Drillbridge to quickly implement true drill-through, where the data currently resides in an Access database? A couple of options come to mind:

  • JDBC to ODBC data bridge to access current Access database
  • Export Access data to relational database
  • Export to CSV and access via JDBC CSV reader
  • Read CSV dynamically using Drillbridge’s embedded database

I won’t bore you with an exhaustive discussion of the pros and cons of these options, but I will say that the JDBC/ODBC bridge was a non-starter from the get-go (for me), mostly because I looked into it for another project years ago and the general consensus from Sun/Oracle was a) don’t do that [anymore] and b) performance is not too great. Regarding exporting Access to a relational database, yes that is more towards the ideal configuration, but if that were an easy/quick option in this case, we probably wouldn’t be on Access already (i.e., for whatever reason, finance didn’t have the time/patience to have the IT department stand up and manage a relational database, to say nothing of maintenance, ETL, and other things). Next, while there are a handful of JDBC CSV readers, they seem to have their quirks and various unsupported features, and hey, as it turns out, Drillbridge’s embedded database actually ships with a pretty capable CSV reading capability that let’s us essentially treat CSV files as tables, so that sounds perfect, and bonus: no additional JDBC drivers to ship. So let’s focus on that option and how to set it up! Continue Reading…

Configuring Drillbridge with Financial Reporting Web Studio

Drillbridge works perfectly with Financial Reporting Web Studio – the successor to the desktop-based version of Financial Reporting (also commonly called HFR, FR). FR was stuck with a very archaic client (let’s just say it’s from around the Clinton administration), but it has revamped for the future, with a completely web-based interface now. In retrospect, and based on my interactions with the interface, I think this product overall can be thought of as gap coverage for FR users. It’s not necessarily the place you want to do new development, especially given some of the other shifts/developments in the reporting ecosystem lately. My colleague Opal Alapat has posted some really great thoughts on FR and its place in this ever-changing world, which I encourage you to read.

In the meantime, there are countless current installs of FR that organizations need to support and perhaps transition to this newer incarnation of FR. As with before, Drillbridge works seamlessly to give you and your users advanced drill-through capabilities in Smart View, Hyperion Planning/PBCS, FR, and now FR web. I found that the UI had a few quirks to it, but I’ll walk through a simple example and try to point those out along the way.

Continue Reading…

New Drillbridge Plus feature: fetch attributes!

Drillbridge Plus has recently gained a new feature at the request of a customer. This one is kind of interesting and required a bit of deep thinking in terms of the best way to architect it. Here’s the deal: Smart View will let you drill-through on a data value where your grid is using attribute dimensions, but it won’t pass the attribute associations as part of the request. And as it turns out, there are instances where it’d be useful to have that attribute member so you can use it to dial in the SQL query that Drillbridge creates and executes.

What to do? Ask Drillbridge to go fetch those attribute member values for you anyway! In this post I’m going to walk through a use-case showing off the new feature, how to set it up, and I’m also going to show off some recent debugging enhancements that are really useful and have been around for awhile.

Let’s start. First, consider a normal Drillbridge report definition with a simple query:

A normal Drillbridge report definition (before adding attributes)

Continue Reading…

Interesting Time Period Conversion with Drillbridge/PBCS

I recently helped a customer with their Drillbridge installation/configuration for PBCS that had an interesting time period conversion issue I wanted to write about.

Drillbridge helps convert a given POV into a SQL query, webpage link, MDX query, or whatever you want (such as with a custom plugin). Out of the box, Drillbridge contains a number of commonly-used convenience functions for easily converting months to numbers (as well as other functions). You can do this in SQL too but it seems to almost always be a little “cleaner” to let Drillbridge do it for you, especially when it comes to upper-level drill-through.

Interestingly enough, a client has an interesting but not incredibly uncommon fiscal calendar where February is actually period 1,  March is 2, and so on. In this calendar, January is actually period 12. But the Drillbridge calendar conversion functions usually return the common month numbers. What to do? Just adjust the expression a little to check for January specifically, otherwise convert the month and subtract one. For example:

SELECT 1 WHERE FROM DUAL WHERE
PERIOD IN ({{"name":"Period","expression":"#Period == 'Jan' ? 12 : #monthAbbreviationToDigit(#Period) - 1","drillToBottom":true,"sampleValue":"Q1","quoteMembers":false,"suppressParentheses":true,"overflow":"","overflowAt":0,"flags":""}})

There are a few variant methods to handle this, but this one is pretty straightforward and clean. This token actually also handles upper level drill (such as from member Q1, Q2, and so on), so the query predicate to use is a SQL IN clause, to accommodate multiple values.

Now when we drill on member January, we get this test query:

SELECT 1 WHERE FROM DUAL WHERE
PERIOD IN (12)

And if we drill on Q1, for example, we get this:

SELECT 1 WHERE FROM DUAL WHERE
PERIOD IN (1, 2, 3)

You might have been expecting to see 12, 1, 2 there but it’s actually right since Q1 contains February, March, and April – so everything is mapping as expected.

I’ve been pretty happy over the years with how the original Drillbridge expression/token concept has been able to accommodate some tricky use cases, although this one is relatively straightforward. It’s also nice to be able to write a bit of a “pure” query that doesn’t have to join against a calendar table just to get the right dates. This is just one of the things that makes Drillbridge, in my opinion, a true turnkey drill-through solution.

My Top 10 Favorite Drillbridge Features

Drillbridge is a tool with an ostensibly narrow focus – drill from Essbase/Hyperion data to somewhere else. Typically that “somewhere else” is the relational data that has been summarized to load into the cube. While the concept of drill-through is very simple in principle, Drillbridge has been extensively engineered to make take this simple process and augment it with dozens of features that enhance its usefulness.

That said, in no particular order, I thought it might be fun to point out my ten favorite Drillbridge features. Continue Reading…

Showing off the power of Drillbridge query translation

Lately I have been working on new materials and demo ware to help show off the power, flexibility, and sophistication of both the Dodeca Spreadsheet Management System and Drillbridge/Drillbridge Plus. I came across a really great Drillbridge mapping example today that I hadn’t specifically solved before, but with a little creativity I was able to write the proper Drillbridge query and get exactly what I wanted.

Consider an Essbase cube with the following dimensions:

  • Years: FY15, FY16, etc
  • Periods: Periods/Quarters/Months
  • Scenario: Actual, Budget
  • Departments: balanced hierarchy with four levels
  • Location: Total/Division/Store
  • Measures: Ragged hierarchy with accounts at level-0

For this post I am going to design a Drillbridge query that maps from this cube back to its related relational data, with the additional wrinkle that we want upper-level drill in several dimensions, including one where the dimension in the cube is represented by two different columns in the source data.

Continue Reading…

Top Posts of the Year 2016

Well, 2016 is almost behind us. I haven’t done this before but given that I’ve been doing a fair bit of blogging this year, I wanted to point out the “top posts of the year” on ye olde Jason’s Hyperion Blog. The subjects are diverse (as far as a Hyperion blog goes I suppose) and I think are an interesting reflection of what things people are interested in. Starting with the most popular:

Running MDX queries through a JDBC driver (for fun?): I got a lot of feedback on the MDX over JDBC franken-driver in JDBC. In retrospect, I think this goes to show how rich, diverse, and challenging the world of data integration around Essbase can be. People – developers, consultants, users, whoever – are constantly spending time, energy, and money getting data in and out of their EPM systems. The Thriller MDX-over-JDBC driver hit a real chord with some people that see it as a way to bridge the gap between EPM and other systems.

Drillbridge acquired by Applied OLAP: Probably the biggest news for me this year. Applied OLAP acquired all of Drillbridge (as well as myself) and added it to their portfolio of products, including the Dodeca Spreadsheet Management System, Dodeca Excel Add-In for Essbase, and the Next Generation Outline Extractor. Recently I announced that the enterprise/supported version of Drillbridge was officially named Drillbridge Plus and offers many compelling features, such as upper-level drill support from PBCS.

Kscope16 sessions I’m looking forward to: Interestingly, people were very curious as to what sessions I planned on attending at Kscope16. I’ll be sure to post thoughts on Kscope17 sessions when the time is right. I’ll have a single presentation at Kscope17, which will focus on “demystifying the PBCS REST API”. I hope it’s a crowd-pleaser that people will find useful.

Dependent Selectors in Dodeca: I blogged extensively about Dodeca this year, and apparently this was one the most popular article. Dependent selectors are a great feature in Dodeca that allow for narrowing down or otherwise dynamically generating the selection values for a user. For example, choosing a state could cause another selector to narrow its list of cities to just those in the given state. I’m both surprised and not surprised that this is the most popular Dodeca article. I think it’s cool because this is the type of feature that really enhances the user experience by respecting their time and making a system easier to use.

Data Input with Dodeca, part 1: Dodeca is great for providing a structured way to input data into a cube that is incredibly more robust than “we do lock and sends”. This was the first part in my data input series (six articles!) that covered inputting to Essbase, relational datasources, both at the same time, commentary, and more.

Camshaft MDX tool updated and available: Again with the MDX/data integration theme, people were very curious to find out more about a command-line tool that helps convert MDX queries to useable data files.

Essbasepy updated for Python 3: Surprisingly (to me), people the article on the Essbasepy library caught a lot of people’s attention. A lot of people are using Python to do integration/automation, and Essbase is definitely a part of the picture.

TBC Files for Bankruptcy: My tongue-in-cheek look at the woeful situation at everyone’s favorite beverage company!

Drillable Columns in Drillbridge: Lastly (but not least), one of my favorite features in Drillbridge and I think one of the standout features that you get when it comes to drilling into a web browser instead of a tab in your workbook: the ability to drill from a drill. With drillable columns, you can specify a subsequent view to drill to and the POV of the row (the global POV plus the key/values from that row) will be used to execute it. Many organizations are using this to drill into further/related journal detail, PDF files of invoices, and more. It’s a great feature!

Well, that’s the highlights from 2016. I’ll be looking forward to another productive blogging year with all sorts of exciting things regarding Dodeca, Drillbridge, the Next Generation Outline Extractor, Kscope17, and even a few secret projects I have been working on. Happy new year!

 

Drillbridge Update: Officially Announcing Drillbridge Plus

It has been awhile since an official post on Drillbridge, so today I am happy to say that there has been a lot going on with Drillbridge behind the scenes!

For those of you not familiar, Drillbridge is an innovative software application that runs as a service and makes it very easy to implement drill-through on Essbase cubes from Smart View, Hyperion Planning (including PBCS, including drilling from upper level members!), and Hyperion Financial Reporting. It accomplishes this by offering a robust and flexible way to translate a given cell’s point of view into a SQL query that it then executes and presents to the user. I have blogged about it extensively and presented on it at multiple conferences. In fact, during both of my Kscope presentations on Drillbridge I did a live demo starting with literally nothing but an Essbase server and relational table and then proceeded to download the zip file containing Drillbridge, install it, configure it, and use it to perform an actual drill-through request from Smart View in less than 15 minutes.

Over the past few years, Drillbridge has been a really great solution for many companies because it’s non-invasive (keep your existing cube and automation), flexible (drill to bottom, drill between columns, automatic hyperlinks, formatting, and more), offers an “insanely fast development time”, and works with most relational database technologies. The number of companies that have installed and deployed Drillbridge is absolutely staggering to me. I get emails almost every week from people about how easy it is to use. Many of the emails mention that they downloaded Drillbridge earlier in the day and go it working in a very short period of time. I never get tired of hearing that.

Versions:  Drillbridge Community Edition & Drillbridge Plus

Drillbridge started off life as a totally free piece of software, and to this day there it is still available in a free form. This edition is now called Drillbridge Community Edition and it can be downloaded from the Applied OLAP website. Later on, a licensed version of Drillbridge was offered for companies that wanted additional features, and usually more importantly, came with official software support/maintenance. This version was called Drillbridge Enterprise; this version has been renamed to Drillbridge Plus. Besides being officially supported by Applied OLAP, Drillbridge Plus has numerous features that the free version doesn’t have. This includes advanced paging/caching options, automation integration, PBCS support, custom plugins, and more. It’s a really great piece of software with some really powerful capabilities.

Future of Drillbridge

Drillbridge has an exciting roadmap (that I’m looking forward to blogging about more in the future) along with its sibling software applications at Applied OLAP, including the Dodeca Spreadsheet Management System, Dodeca Excel Add-In for Essbase, and the venerable, completely free Essbase Outline Extractor. We are dedicated to making Essbase (and the lives of people in the greater Essbase community) better. Please do not hesitate to contact us for additional information.

Playing with the Thriller MDX/JDBC Driver in Drillbridge

Last week I talked about a new side project, which is a JDBC driver called Thriller for executing MDX queries against Essbase and mapping the results back into a normal relational database. And at the time, I said that this driver had some really interesting use cases, such as in Dodeca, Drillbridge, ODI, and other tools that work with JDBC drivers.

Speaking of Drillbridge – in the very near future I will be sharing Drillbridge’s official future direction, which I think is really exciting, but more on that later. In the meantime, let’s drop this baby into Drillbridge and see what happens!

The following walkthrough of using Thriller with Drillbridge will show off some features that are only available in the licensed version of Drillbridge, although this should in theory work with Drillbridge Community Edition (the free edition of Drillbridge), assuming you have the Thriller driver JAR file.

Continue Reading…