Drillbridge 3.4.6 Token Enhancements: Join Character and Line Removal

Drillbridge 3.4.6 will be publicly released soon. It contains a couple of enhancements to give developers even more control over the way their queries are generated.

For each token, you can now optionally specify the “token join text” as well as certain “line removal members”.

Token Join Text

Drillbridge has supported upper-level drill for many years – the ability to drill on an upper level such as YearTotal or Q1 or Total Entities and generate a SQL query based on all of the members below it. This has even worked with PBCS for years as well, providing an easy way to implement drill-through from upper-levels from PBCS too. How this generally works is that Drillbridge will fetch the level-0 descendants of the drilled member and then concatenate them together to use in you SQL IN clause. For example, consider the following Drillbridge query:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = '{{"name":"Market","expression":"#Market","sampleValue":"Washington"}}' 
    AND MEASURE = '{{"name":"Measures","expression":"#Measures","sampleValue":"Sales"}}'
    AND SCENARIO = '{{"name":"Scenario","expression":"#Scenario","sampleValue":"Actual"}}'
    AND PRODUCT = '{{"name":"Product","expression":"#Product","sampleValue":"100-10"}}'
    AND SUBSTRING(TX_DATE, 6, 2) IN {{"name":"Year","expression":"#monthToTwoDigits(#Year, 'en')","drillToBottom":true,"sampleValue":"Year","overflow":"OR TX_MONTH IN %%OVERFLOW%%","overflowAt":3}}
ORDER BY
    TX_DATE

There’s nothing too fancy going on in this query. Well, okay, a little bit. This query is based on our favorite database, Sample/Basic. You can see that the query from the SAMPLE_BASIC_TRANSACTIONS table takes into account the value from the Market, Measures, Scenario, Product, and Year dimensions (recall that in Sample/Basic, Year is the time dimension and its children are quarters).

This query has really simple mappings for all of the dimensions except the Year (time) dimension, which is where things get interesting. In this example, the contents of the TX_DATE column are values such as ‘2019-10-22’ – that is, a pretty standard year-month-day format with hyphens. The incoming values from the cube, however, will be month names such as Jan, Feb, Mar. This example uses the built-in Drillbridge function #monthToTwoDigits to convert the month names to two digits. This is a function that has been built-in since the earliest versions of Drillbridge. Yes, you can convert dates using SQL, but every database has its own particular syntax. Having this built-in makes report development much easier, particularly in this example where we are also doing a drill to bottom operation. Let’s take a look at what happens when we drill on some intersection that includes Jan for the member in the Year dimension:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
    AND SUBSTRING(TX_DATE, 6, 2) IN ('01')
ORDER BY
    TX_DATE

Since drillToBottom is enabled for this token, Drillbridge gets the level-0 descendants of the drilled member (in this case it simply yields the drilled member itself as it has no children and is itself a level-0 member), applies the expression/function to each item (converting Jan to 01), surrounds each item with quotes, then surrounds the whole thing with parentheses. We end up with a valid SQL query that combines native SQL (the SUBSTRING function) on the TX_DATE column (extracting the two digit month) and checks if it’s IN the specified values. Now let’s drill on an upper-level member (Qtr1) and see what we get:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
    AND SUBSTRING(TX_DATE, 6, 2) IN ('01', '02', '03')
ORDER BY
    TX_DATE

Drillbridge was given Qtr1, went out to the Essbase outline, saw that the level-0 descendants were Jan, Feb, Mar, applied the expression/function, then joined them all together. With the new custom join text option turned on (and some custom join text specified, such as a semicolon in the following example), we get this:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
    AND SUBSTRING(TX_DATE, 6, 2) IN ('01'; '02'; '03')
ORDER BY
    TX_DATE

Historically, Drillbridge has always assumed the text to join things together with was a comma, because that’s what all SQL IN clauses take. The custom join text gives you full control over this now, though. This probably won’t be useful in SQL queries, but it is useful for the Forwarding Link report type in Drillbridge, which generates a URL to send the user to rather than a SQL query. This report type is generally used to send a request over to an image server, OBIEE, or some other system. Now that you can specify custom text, many more options are opened up and accommodated.

Line Removal Members

Building on the previous example, let’s say that you drilled on Year instead of Qtr1. You would get the following query:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
    AND SUBSTRING(TX_DATE, 6, 2) IN ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12')
ORDER BY
    TX_DATE

As with before, Drillbridge went out to the outline, found the descendants, mapped them with the expression, and generated the query for us. The interesting thing about this case, though, is that now in this context, the filter on date is essentially useless, since all of the months are included in 01 through 12. I’ll point out that this example is for demonstration purposes and while it’s not incredibly contrived, I have seen many datasets with a 00 or 13 month (to represent starting/ending balances) that this wouldn’t work on – but the principle still applies. In any case, Drillbridge had to go to the work of consulting the outline for the members (not a huge deal), and then made the SQL database do the work of applying the IN clause. For only 12 members, this isn’t necessarily a huge performance consideration. But many organizations performing drill to bottom on doing it on dimensions with thousands of members.

Drillbridge now supports a new option per token called “line removal members”. The way it works is that if a member is drilled on is in the list of line removal members, then Drillbridge will remove the entire line with the token on it, as well as skip the member resolution process. For instance, considering the previous example, if the user drills on the Year member, then we just don’t even care about that particular clause in the WHERE statement. For instance, let’s say that the line removal members in the Year token is the comma-delimited list of “Year, Test, All Periods” – meaning if the drilled member in the Year dimension is either a member named Year, a member named Test, or a member named All Periods, then kill the whole line. And sure enough, if we drill on Year we get this query:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
ORDER BY
    TX_DATE

Properly setting up this feature may involve crafting your query in a slightly different way than you might otherwise. Since the whole line is removed, you may need to consider the placement of your AND clause. In this case, since AND was the first part of the line, we’re good remove it (as opposed to AND being on the end of the prior line). Also, if we only had one predicate in the WHERE clause then it might be necessary to add the “dummy” clause (1=1) – this is a pretty common thing to see in SQL as it always evaluates to true.

The line removal members feature can only be used in certain circumstances but in cases where your users may be drilling from the “top of the house” it could offer potentially enormous performance benefits for your query execution time.

Drillbridge Feature: Advanced Date Formats

The latest shipping version of Drillbridge Plus (3.4.2+) has a few new tricks up its sleeves for advanced handling of date values coming back from your SQL query. 

Generally when you retrieve a date value with a relational query, the “right thing” happens automatically – i.e., the date value is displayed how you want it to be displayed. For those cases where you want more control over the output, you can use your database’s string/date formatting functionality to convert the date to a string and display it however you want. 

The slight problem here is what if the date doesn’t come back how you want it and you then format it how you want it, usually what has happened is that the DATE/TIME/DATETIME/TIMESTAMP column is now just a string (such as VARCHAR). This looks fine on the normal Drillbridge results page, but when you download your report as an Excel file, Excel will just think it’s a string as opposed to a date. 

Continue Reading…

Drillbridge Plus 3.3.0 Release & Features

Drillbridge Plus (the licensed/supported version of Drillbridge) is officially released. This release introduces some great new features, enhancements, and bug fixes, including the following:

  • User Parameters
  • Download File Name
  • Analytic Provider Services support
  • Various drill report changes/enhancements
  • UI/bug fixes/enhancements

User Parameters

The headline feature in this release of Drillbridge is support for the new “User Parameters” feature. This feature is configured on a per-report basis and provides a mechanism to prompt the drill-through user for additional input before executing the report. The values that the user provides are accessible as with any other variable in the final Drillbridge query syntax (in addition to their original drill-through POV). The user parameters feature is useful when you want your query to use additional detail/parameters that aren’t present in the dimensionality of the source system being drilled from.

A report can have an arbitrary number of user parameters associated with it. Each parameter has the following options available to configure:

  • Name – defines the name of the parameter. This is shown to the user on the user parameter input page next to the input area.
  • Description – description of the parameter. Shown to the user as the “help text” below the user parameter input area.
  • Variable name – the variable that will be tied to the user’s input in the Drillbridge query. For example, if prompting the user for a particular account, the variable name might be “Account”, and usable in your Drillbridge expressions as the #Account variable.
  • Input type – choose from a textbox input type or a drop-down selector. Another option is “textbox with auto-complete” which is a normal textbox input with auto-complete enabled, which will use the text or SQL query in the “possible values” definition.
  • Preset value – a value to pre-fill or pre-select for the user, depending on the input type. Textboxes will be pre-filled with this value and drop-downs will pre-select it.
  • Optional – whether the parameter is optional or not. If using textbox input, the user will not be required to enter a value. If using a drop-down, then one of the valid options for the user will be “nothing”.
  • Default value – if the user parameter is optional and no value is specified, you may specify a default value (you may also elect to just handle null/empty values in your SQL/query, which should be more or less the same).
  • Secure input – if using a textbox to input parameters, its input will be masked (as with a password input box).
  • Possible values/query definition – You may define a list of values to place into the drop-down or to serve as auto-complete suggestions. You may also define a Drillbridge query that returns values to be used.
  • Connection – If the “possible values” specification is a Drillbridge query, then you must set the SQL connection to use here, otherwise, leave it blank.

Analytic Provider Services support

Support for connecting to Essbase via Analytic Provider Services is now provided. On the Servers editor, define an APS server name or leave it blank to use the default embedded mode.

Download File Name

You can now use a Drillbridge query expression to define the name to use when a user downloads their drill-through results as an Excel or CSV file. By default, the name of the Drillbridge report itself with any spaces replaced by underscores is used as the download file name (appended with .xlsx or .csv as the case may be).
The Download File Name option allows for defining a normal Drillbridge expression that can be used to customize the file name to include tokens.

For example, the download file name for a Drillbridge report named “Transaction Detail” may have been Transaction_Detail.xlsx, but using the download file name feature in conjunction with tokens from the drill-through POV may now result in a download file name such as Transaction_Details_Jan_2017.xlsx.

Various drill report changes/enhancements

  • Query row limit and query timeout options have moved to the general options page.
  • You can now edit the internal description of a report
  • You may now specify http:// or https:// as part of the server name when deploying a report. Previously, https:// was assumed and orgnanizations using Drillbridge over HTTPS had to manually edit the drill-through definition
  • Enhancements to drillable columns. There is a new rendering type for drillable columns that renders with an arrow instead of a link. This is useful for reports with drillable columns where there are multiple drillable column definitions in the same column
  • New Inline CSV file download. New option to turn on “inline” CSV downloads such that CSV output is shown directly in browser instead of being a download
  • Autosum rows: new option to automatically sum all or some of the columns in the drill-through report

UI/bug fixes/enhancements

  • You can sort connections/servers/reports by various columns, such as name, connection, and description
  • Enhanced descriptions on various text fields in UI
  • Fixes when deleting a server entry

There are no changes to the community edition of Drillbridge at this time. If you’d like a Drillbridge Plus demo or more information on how Drillbridge can help your organization, please don’t hesitate to contact Applied OLAP.

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…