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:
- Need to use integrated security on connection to Microsoft SQL Server
- 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:
- #monthAbbreviationToTwoDigits – converts Jan to 01, Feb to 02, etc
- #monthAbbreviationToDigit – converts Jan to 1, Feb 2 to, Dec to 12, etc
- #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:
- name = “Business Unit”
- expression = “#Business_Unit + ‘BU'”
- 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.
[…] 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 […]