One of the requests I got from an earlier version of Drillbridge was to put less space between rows. Fair enough – we all have different stylistic tastes. I could have just changed the spacing and called it a day. But what about other formatting, colors and tweaks? I decided, ya know what would be cool? Styling things however you want.

To that end, there are now a handful of styling options that can be used to achieve any result you want:

1. Global CSS
2. Report-specific CSS
3. Report header
4. Report footer

These should be pretty straightforward, but there’s a couple of nuances I want to point out.

Global CSS

Global CSS is a server-wide setting that allows you to define your own Cascading Style Sheet (CSS) that will be included in every single report. This is a good place to set some global spacing, fonts, colors, and perhaps some settings inspired by your company colors.

Report-specific CSS

Individual reports can have their own CSS. This useful, for example, if you want/need to adjust things (perhaps a column width or two) for a specific report but not all reports. You might even want to customize settings for different divisions that have different styles (the possibilities are endless!). Additionally, for ease of use reports now have certain CSS classes appended to the data table, rows, and each column, so you can easily apply settings to the 1st, 2nd, 10th, or whatever column specifically. You might want to align some text to the right (text-align: right), for example.

Report Header & Footer

Reports can each have a custom header and footer that is your own custom HTML enclosed within a particular <div> tag at the top and bottom of a report. I thought about making this a global setting but decided against it. You might want to point to some company logo or otherwise include some standard company text or links. There is no limit to what you can put in these areas.

That’s it for these new features – fairly straightforward – but should provide exactly the kind of flexibility that people want, without having to resort to one-off changes in each version of the software.

As of Drillbridge 1.3.0, paging is now supported. More specifically, tokens
that help write paging queries in SQL are provided – paging is not done
automatically. The reason that automatic paging is not supported is that while
this approach ostenisbly means writing a bit of SQL code to perform the paging, it’s
the most flexible and most performant.

If paging is turned on for a given report, then when that report is built, in
addition to the normal Point-of-View parameters that are made available to the
query, there will be three additional parameters:

• PAGE
• ROWS_PER_PAGE
• OFFSET

Drillbridge will start the PAGE variable off with 1. This token can be used like any other token. The ROWS_PER_PAGE variable can also be used like any other token and can be configured on the report itself. Typical values might be 20, 50, 100, 500, or 1,000.

The OFFSET variable is not specified in the report request, it is calculated as a convenience variable to be used in queries. OFFSET is provided since in some SQL dialects, the total number of rows to skip is needed rather than a page or other option. The formula for OFFSET is:

(page - 1) * rowsPerPage

For example, if a report is meant to page on every 20 rows, meaning that page 1 is rows 1-20, page 2 is rows 21-40, and so on, the following would be true on page 2:

• PAGE = 2
• ROWS_PER_PAGE = 20
• OFFSET = (2 – 1) * 20 = 20

If your data source was a MySQL database for example, then a paging query to this technology might be this:

SELECT column FROM table LIMIT 10 OFFSET 10

As you can see, we will want to plug in the ROWS_PER_PAGE for the 10 after LIMIT, and then the OFFSET for the 10 after the OFFSET keyword. Different SQL technologies have different ways of paging data (some are more complex than others…) but this one is straightforward, thankfully.

Just plug in the given variables like any other, turn on paging in the report, and presto, instant paging – with good performance.

Next week I’ll talk about some of the other cool features coming soon…

I had originally been planning just a few fixes and tweaks for the next point release of Drillbridge which was to be 1.0.3. But after adding quite a handful of new and improved features, I have decided to bump the version more significantly. I’m happy to say that everything I’m about to describe is fully implemented and just undergoing some documentation and testing on my part. It’s going to be a super cool release, hence bumping the version up a bit more.

Of particular note, the following features have gone in:

1. Paging and results per page settings
2. Faster Essbase outline queries and a new outline caching mechanism
3. Smart Formatting option for locale-specific formatting of dates and numbers (French, German, and Russian users rejoice!)
4. Row limit and query timeout settings available on reports
5. Global CSS formatting, report-specific CSS, header, and footer
6. Documentation improvements and more examples
7. Tons and tons of small improvements and fixes (too many to enumerate here but full notes will be in the changelog)

I’m going to try and get 1.3.0 wrapped up in the next couple of weeks. If you’d like to try it early, let me know and I’ll send a build your way. After this release there’s just one major feature left that I think this product needs, then it should be a matter of bug fixes and minor improvements from there will I pivot to my next big project.

Over the coming days prior to an official release, I’ll be posting a series on some of the new features that are coming up, so please feel free to email me if you have any questions or comments and I’ll be sure to address it in the blog!

Just a quick tweak to fix a last minute thing I apparently broke. This release saw the introduction of a super cool mechanism for updating the Drillbridge repository automatically when Drillbridge gets updated, but I made a last minute change that slightly broke it so it was messing up the start of the service. I put things back all looks good again.

Sounds like people are having some issues. I think this might be related to the Java heap size needing to be adjusted lately. I’ll work on a fix ASAP.

Hot off the digital press is an updated Drillbridge. Version 1.0.2 brings several fixes, enhancements, and more. There’s still a few rough edges but I’m trying to keep a fast cadence with releases so I don’t get too bogged down. Of particular note is the following:

• New infrastructure in place that should make applying upgrades much easier. So when 1.0.3 comes out you should be able to pretty smoothly update. Note on this version: you CANNOT upgrade from 1.0.0 or 1.0.1 to 1.0.2. Sorry. You should copy and paste the contents of your connections/reports and recreate them. This should be the last time you have to do this.
• You can now create and modify server mappings, cube mappings, and deployment specifications. So you can create an entry for your main Essbase server, then a mapping for Sample/Basic, then a list of members that the drill-through report is valid for in Sample Basic, and then just deploy the report directly to the cube (no more need to copy and paste code using EAS!) Of course, you might still want to crack open EAS so you can validate that everything got setup properly.
• Very powerful expression syntax and convenience functions (please see previous post about how it looks). This should eliminate the new to setup mapping tables and views in a huge number of use cases. For example, if your cube has members like Jan, Feb, Mar, and the database has 01, 02, and 03, then you can use a new handy #monthAbbreviationToTwoDigits function to convert without a mapping table. This is super cool.
• The initial version of drilling from upper level members in an Essbase cube is now implemented (!). For example, drilling on Qtr1 will create a query with Jan, Feb, and Mar because Drillbridge will open the outline up real fast and read the members. Please note that this feature works but has not been extensively tested. I am thinking that future versions will need an outline cache module in front of this in case it just takes too long to ransack the outline. Please let me know how it goes for you.
• Note that row limit and debug mode are not implemented yet, they just have GUI elements so far.

The road to 1.0.3

This release packs a lot of great stuff that I’m proud of. As time permits I will be working on the next version, 1.0.3, which will further enhance and refine things. In particular I am looking to implement some or all of the following:

• Row limit
• Debug mode
• Create/edit/delete server variables
• Ability to use variables in queries
• More information when errors occur
• Example connections built-in to help get things started
• Result pagination

For now the only major feature on the horizon will be drilling further from an drill sheet – this is partially implemented right now but it’s a little tricky to get right so I’m taking my time on this. From there on out I predict that the majority of changes to Drillbridge in the future will be fixes and simple enhancements.

As always, please head over to the Saxifrage Systems Drillbridge page to get to the downloads. Please don’t hesitate to check out the support forums which I monitor regularly.

Thank you again to everyone out there that has downloaded and played with this thing. The feedback has been incredibly useful. Any problem or issue you run into I am happy to take a look at and fix up if possible. No issue is too small – literally anything you can report to me that I’m unaware of will result in me being able to make this tool better for everyone in the community. Also, if you deploy this to production, please tell me about it! Drillbridge is officially in production at a major restaurant chain (more info coming as soon as possible) and I would love to know about more.

Drillbridge 1.0.2 should be ready sometime next week. I’m putting some extra time into testing to make sure that some of the sharp edges are not so sharp. I successfully deployed Drillbridge to an enterprise client this week and learned a few things in the process (oh, and by the way, Drillbridge is now running in the enterprise!).

Some of the unique or new challenges that I bumped into were these:

1. Need to use integrated security on connection to Microsoft SQL Server
2. Source dimension in cube has space in name (“Business Unit”)

Solving the SQL Server integrated security thing isn’t too bad. I just had to include a DLL from the Microsoft SQL Server JDBC distribution that enables this, and setup the JDBC URL accordingly. Fortunately I had already relaxed the requirement on JDBC connections so that they aren’t required to have a password (which is the case for integrated security).

Secondly, a space in the dimension name was an edge case I hadn’t considered before. Because the new variable interpolation mechanism makes the source member from each dimension available as a variable (like #Years or #Scenario), it simply wouldn’t parse with s space. So now spaces are converted to underscores, so in this case you can use the #Business_Unit variable to get at the submitted value for the business unit.

New Functions

To this point it has been necessary to setup a view or a mapping table on the relational side that can help translate from the members in the outline to the data in the source table. In order to ease this process for some common transformations, some custom functions have been added to the expression parser:

1. #monthAbbreviationToTwoDigits – converts Jan to 01, Feb to 02, etc
2. #monthAbbreviationToDigit – converts Jan to 1, Feb 2 to, Dec to 12, etc
3. #removeStarting – strips a prefix off a member, if present, like changing BU001 to just 001

On top of these functions, the new expression language being used under the covers gives access to a huge library of Java functionality that can convert most members to their proper equivalents in the relational table. This should enable mapping from members to the source names in 90% of cases, if not more.

Speaking of the new expression language, let’s see what queries look like now:

SELECT PD, YR, AMT FROM Transaction WHERE PD IN ({{"name" : "Business Unit", "expression" : "#Business_Unit + 'BU'", "drillToBottom" : "true"}})

What’s going on here? A few things have changed. As with before, the token is enclosed in double curly braces ({{}}). Now inside is a simple JSON expression – for those not familiar this is an industry standard notation for writing complex data structures.  Between the curly braces is the following information:

1. name = “Business Unit”
2. expression = “#Business_Unit + ‘BU'”
3. drillToBottom = “true”

What’s going on here? This indicates that the name of this token is Business Unit (for reference purposes). Also, the value of the expression is the value of the #Business_Unit variable, then suffixed with the text ‘BU’. Note another twist though: drillToBottom is set to true. In this case, the associated Essbase outline to this report will have the member from Business Unit looked up, then get it’s level-0 descendants, then suffix them like in the normal expression. A little confusing to explain so here’s what the finished query sent off to the database looks like:

SELECT PD, YR, AMT FROM Transaction WHERE PD IN ('BU123-10BU', 'BU123-20BU', 'BU123-30BU')

Note that the children of the queried member (BU-123 in this case) are BU123-10, BU123-20, and BU123-30, then our expression suffixed them with the BU as from above, so that the query is mapped properly for our relational data source. Not all expressions will necessarily be this complex, but this shows the simple power of a few options combined together.

People have said they are really excited to drill to the lower-level members from upper-level members and I’m happy to say it’s working quite well. Version 1.0.2 of Drillbridge will come out next week. Unfortunately you cannot upgrade from 1.0.1 or 1.0.0 to this version. The simplest thing to do would be to copy all of your connection info to a text file and then re-input it. Sorry for the inconvenience, but the database schema migration stuff is just now being added which will make upgrades simpler in the future.

I feel like I’m repeating myself but I have to say again that the response to Drillbridge has been absolutely incredible. A lot of you are providing some awesome feedback. Perhaps one day this little tool will be up there with the venerable Outline Extractor.

In any case, I’ve actually been incredibly (actually, unbelievably) busy with other projects, but Drillbridge is like a hobby for me so I have a few updates planned for the next release. This will be version 1.0.2:

1. Paginate returned results if desired
2. Create, edit, and update server and cube mapping definitions, and deployment specifications (the members that are drillable)
3. Maybe (I thought this wasn’t in the Java API but apparently it is, at least for modern Essbase versions): deploy drill-through definitions to a cube mapping

I’m trying not to bite off more than I can chew for a single release. There are a number of things that are planned for an as-of-yet unnamed future release:

1. Drill to descendants of upper-level  member (i.e., drill on Qtr1 to do a drill using Jan, Feb, Mar)
2. Custom image/header/footer on results pages

And looking way down the road:

1. Drill from a drill – this is actually fairly complicated so I want to make sure I get this right. But basically you’d be able to keep drilling through data. A lot of people are interested in this for drilling to invoice PDFs, other tables, and so on.

Please keep the awesome feedback coming and know that I’m dedicating all the energy I can to making this a useful tool!