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…

Kscope16 is almost here!

Here we are again, just less than a week before Kscope16. I say this every year but this year’s Kscope promises to be the best yet, and I’m starting to get really pumped. There are a lot of exciting things going on in the greater Essbase world right now, and even my own little corner of this world is quite exciting (to me at least), including such fun things:

All of these items will be present at the conference, in one form or another. Dodeca will be featured in several presentations this year as customers talk about their implementation successes. Drillbridge has its own presentation again this year, given by yours truly, the outline extractor has a session put on by Tim, and the PBJ library will make up about half of a presentation with Cameron talking about on-prem versus PBCS.

That all said, this week I’m going to blog daily about some of the cool things in the world of Dodeca, Drillbridge, the Outline Extractor, and more. Stay tuned for a busy week!

Drillable Columns in Drillbridge

One of the more often requests that Drillbridge users have is to be able to have drillable columns. That is, they’d like to drill from a column in a Drillbridge report by clicking on the column value and pulling up another report, using the point-of-view (POV) of the original cell. This is most often used in a few different situations:

  • Drill into yet more detail for a given transaction
  • Drill to a PDF or image related to the current item
  • Drill to a custom URL, such as a Sharepoint URL

Drillbridge has supported this feature for quite some time, and it’s pretty easy to setup. Today I’m going to walk through the steps for setting up drillable columns in the latest version of Drillbridge Enterprise.

First of all, let’s take a look at a generic Drillbridge report that executes a query by converting the member from the Years dimension from something like “FY12” into the value of “2012” and then executes a query:

drillbridge-drillable-columns-source-query

Note that for readability I have broken the token up across several lines. This works just fine in the latest versions too and is a nice way to increase readability. Let’s see what this query generates by going over to the Test screen:

drillbridge-drillable-columns-testing-source-query

And building the report:

drillbridge-drillable-columns-source-query-data

Everything looks good so far – note that the drillable column hasn’t been setup yet so we shouldn’t be expecting any special links to appear yet.

Now let’s create a new report definition. This new report type will be a special report type known as a “Forwarding Link Report”. If you think about a normal Drillbridge report, it builds a SQL query by taking the POV and plugging it into a template. A Forwarding Link Report works in much the same way, except instead of building a SQL query, it builds a URL and forwards the browser to it.  In this case I’m just going to build a simple link over to Wikipedia since they have a nice simple URL structure that is good for illustrative purposes.

Here’s our link definition:

drillbridge-drillable-columns-link-query

Note the label above the text box for editing the link definition. Instead of saying it’s a SQL query, it refers to it specifically as the Link Template. This is because the report type itself has been changed (which is needed to make this type of report work). To change the report type we go to the Advanced menu and the click on Class, bringing up this editor:

drillbridge-drillable-columns-target-class-type

Note that in this case, the types available are JDBC drill-through report (the typical report type), Forwarding Link Report, or MDX report (yes, Drillbridge can execute MDX queries too!). The Drillbridge report system is completely modular and developers can add their own report types using a simple Java plugin.

Custom report plugins can also easily “advertise” their available options to Drillbridge so that Drillbridge can provide a nice UI for configuring them. For example, in the case of the forwarding link report, there is just one option available (as opposed to the 20 or so options available on a typical report) that let’s us choose the HTTP “verb” type to use:

drillbridge-drillable-columns-link-options

Let’s head over to the Test tab for this new link report and see what happens:

drillbridge-drillable-columns-testing-link

Let’s build it and see what happens:

drillbridge-drillable-columns-link-wikipedia

As expected, the given input was used to build a link and our browser was redirected there immediately. This is one of the nice things about doing drill-through in a web browser: you can do pretty much anything you want.

Now, what we’d like to do is make one the columns in our first report drill over to the results of this new link report we just setup. To do that, let’s go back to the original report, then go to the Advanced menu, then Drillable Columns:

drillbridge-drillable-columns-drillable-columns-list

Then click on New Drillable Column. The editor will be brought up:

drillbridge-drillable-columns-editing-column

Here we have a chance to give this drillable column a name, tell the report which column should be drillable, and then choose the target report. We can put in anything we want for the name. What’s important in the column index and target. The column indices start at 0 for the first column and then count up. In this case we want the third column to be a link, so this is column index 2 (0 = first column, 1 = second column, 2 = third column, etc.). Then we select our new report as the target report, which I named “Wikipedia”.

Let’s go and test this source report again and see that the third column has links now:

drillbridge-drillable-columns-viewing-drillable-column-links

And sure enough, clicking on one of the links brings up the Wikipedia report – which doesn’t bring up a report at all, per se, it just executes the report, which uses the POV to build a URL and automatically redirect the web browser. That’s it!

Report POV vs. row POV

It’s important to consider what the POV handed to our target report will be. The POV is actually the combination of two things, out of necessity: the POV of the source report merged with the POV of the row itself. Whereas the normal report POV might be something like Scenario = Actual, Year = FY12, Time = June, the POV handed to the target report will be all of those things, plus keys and values from the current row. For example, if the original report only considered a given account but showed details for transactions, and there was a column called TRANSACTION_NUM, then the POV would also include a key named TRANSACTION_NUM with a value of whatever the value from that row is. This is critical for users that want to link to something or execute another query using data from the row.

Drillable Columns is one of my favorite features of Drillbridge because it’s so easy but powerful, but more importantly, it saves people time, and that’s what Drillbridge is all about.

Drillbridge Community Edition 1.5.5 available

Hot on the heels of the announcement that Applied OLAP has acquired Drillbridge, there is a small update to the latest free edition of Drillbridge. Drillbridge 1.5.4 has been bumped up to version 1.5.5 and includes the following three very specific updates:

  1. Applied OLAP software license
  2. Fix for sporadic report deletion issue
  3. Change to default temp/working directory

To go into further detail, the license shipping with the previous version of Drillbridge has been changed to a standard Applied OLAP license that is more or less the same as that used for the free Outline Extractor. The switch to this license serves mostly to give uniformity and consistency to Applied OLAP software offerings rather than materially affect the usage of the software.

Second, there is a sporadic issue with respect to the ability to delete reports that has been fixed. How this would manifest is that the Delete button would just literally not show up sometimes to delete a report, thereby preventing the user from being able to delete it. The workaround was/is to go into the Drillbridge repository and manually delete the report entry.

Lastly, the default working directory has been changed. Some versions and combinations of Java 1.7, 1.8, and Windows Server 2008/2012, and various group/security policies would have problems launching Drillbridge. This was generally due to an issue where the temporary file directory to be used by Java would not be writable by the service running Drillbridge. So by default, the temp folder will try to be located inside of the Drillbridge installation folder itself, which should prevent most issues of this type from happening. This location can be changed via a config file as always, if needed.

As a bit of an aside, Drillbridge Enterprise 3.0 is still under development with a release planned for later this year. This Community Edition release of Drillbridge is currently slated to the last release in the 1.5.x series (unless further issues are discovered). Future versions will likely be based off of the 3.0 codebase.

Please let me know if you have any questions! As usual, Drillbridge can be downloaded from the Saxifrage Systems LLC downloads section. Note that in the hopefully not too distant future, this will be migrated over to the Applied OLAP downloads section but will remain where it is for now.

Drillbridge acquired by Applied OLAP

The last few months have been rather hectic (as evidenced by the lack of blog posts, unfortunately), but I am happy to report some exciting news! There are two main things of note:

First, I am now a full-time employee of Applied OLAP. Applied OLAP is one of the biggest and best names in the EPM space, having worked with Essbase for many, many years. Applied OLAP develops and supports the Dodeca Spreadsheet Management System (much more to come on this!). Back in my Kroger days I helped roll out one of the earliest Dodeca implementations to what became a very enthusiastic user base and incredibly happy executives. Many of you are just as, if not more familiar, with the Outline Extractor – software that is also maintained by Applied OLAP as a free tool for the benefit of the community.

Second, Applied OLAP has acquired Drillbridge in its entirety. The free edition of Drillbridge will now be available alongside the Essbase Outline Extractor and other tooles that many people are so familiar with. Enterprise licensees will now have their software supported by Applied OLAP. Drillbridge Enterprise has an exciting roadmap and will continue to exist as a standalone product.

That’s the short version for now, many more exciting things to come in the days ahead!

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.

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.