I’m still tracing this through, but there’s an occasional issue I’m seeing on Windows 2008 servers when it comes to starting Drillbridge. If you get an error when you try and start the service, check your logs and try to find the cause, as you normally would. If you see an error along these lines:

INFO   | jvm 1    | 2015/04/07 10:09:39 | org.springframework.context.ApplicationContextException: Unable to start embedded container; nested exception is org.springframework.boot.context.embedded.EmbeddedServletContainerException: Unable to create Tomcat tempdir

It could be a permissions problem such that the account running Drillbridge does not have high enough privileges to create a temporary folder for some web server files. If this is the case, you can actually specify the temporary folder yourself.

Open up /config/wrapper.conf and search for the text “additional”. You should find a commented our line like this:

#wrapper.java.additional.1=

You can leave that uncommented but just add in the following line:

wrapper.java.additional.1=-Djava.io.tmpdir=D:/Drillbridge/tmp

Then go make sure that you create the /tmp folder so that the folder path exists. You don’t have to use this folder name, drive, path, or anything. It just has to be a folder that exists and that the Drillbridge user has the ability to write to. Save the file and then start (or restart if Drillbridge was already running) Drillbridge. Everything should now start fine.

I’m still tracing down the root cause of this to try and make it so that users would never have to apply this fix themselves but until then, this fix works well enough. I haven’t seen this issue on Linux editions of Drillbridge – just Windows Server 2008 in environments that are relatively locked down.

The upcoming version of Drillbridge has some really exciting features in it. I’d like to talk about one of those features right now. Drillbridge now has support for custom functions. Drillbridge has a very powerful and flexible mapping system for easily translating between the members in your cube and related detailed data in a relational table. This is a big part of what makes Drillbridge such a compelling solution for quickly implementing drill-through.

One of the more popular functions is #removeStarting. This function strips a prefix off of a string (member name) if it has a certain prefix. For example, let’s say that the names of members in the Years dimension are FY14, FY15 and so on. You need just the numeric value in order to perform a query for the detailed data. You can use #removeStarting like this:

#removeStarting("FY", #Years)

And the value returned by the function will be 14, 15, or whatever the proper year is. The newest version of Drillbridge now supports adding custom functions by way of dropping in a Java file that contains them. This way if you have a complex mapping that needs to happen, you can use the full power of the Java language to accomplish it. For example, consider a function that needs to do special processing with respect to a combination of the year and month, in order to get a particular time value stored in a GL system.

public static int getJulianLastFromYear(String fiscalYear, String shortMonthName) {
  Integer year = Integer.valueOf("20" + DrFunctions.removeStarting("FY", fiscalYear));
  Integer month = Integer.valueOf(DrFunctions.monthToTwoDigits(shortMonthName, "en"));
  return getJulianLastOfMonth(year, month);
}

Then just compile this class as normal, drop it in the Drillbridge drivers folder, restart Drillbridge, and you can now use this function inside of your own expressions, just by calling it with whatever name you give it (in this case, it might be #getJulian).

This is a really exciting feature that will offer tremendous flexibility, particularly in cases where complex mapping is needed that might be difficult or impossible to perform with the relational database. Stay tuned – there are a few more very interesting features coming down the pike…

Just a quick note on the syntax for Drillbridge tokens that use drillToBottom. One of the more compelling Drillbridge features is the ability to “drill to bottom”. What this means is that a user can drill on an upper-level member such as “Quarter 1”, then Drillbridge will open up the outline, get the level-0 descendants, transform them according to the token expression, and then replace the whole token with the list of members combined into a string.

The string that Drillbridge generates in this case is almost always used in a SQL “IN” clause, like this:

SELECT * FROM Transactions WHERE Month IN ('01', '02', '03')

In this case, the tokens ’01’, ’02’, and ’03’ were perhaps generated because they were transformed with the #monthToTwoDigits function and processed the member names that are the children of Quarter 1 (“Jan”, “Feb”, “Mar”).

Earlier versions of Drillbridge required you to supply the starting an ending parentheses, such that your query text looked something like this:

SELECT * FROM Transactions WHERE Month IN ({{token definition}})

This changed back around version 1.5.0, however. Now Drillbridge by default will supply the starting and ending parentheses. This change was made to accommodate a few use cases where for performance reasons you might need or want to generate the query a little bit differently (such as using a sub-select or something fancy).

Given that current and future versions of Drillbridge supply these parentheses, you would instead write the query before more like this:

SELECT * FROM Transactions WHERE Month IN {{token definition}}

If you really want to supply the parentheses yourself for some reason, you can set the “suppressParentheses” parameter on your token to “true” and Drillbridge won’t supply them – meaning that you will write the query more like the first style. While I’m on the subject, also note that by default, Drillbridge surrounds your member values with single quotes (see the first example in this post where 01, 02, and 03 are all surrounded by single quotes). You can also turn this off if you need to (for example, if the items in the IN clause must be numeric) by setting the “quoteMembers” token parameter to false.