Overview
Drillbridge provides support for setting up drill-through from an Essbase cube to a relational database source. It accomplishes this by creating and executing a SQL query that is based on the point of view that a user drills from. For example, if a user drills from a member cell at the intersection of January, Sales, and Washington (such as from the Sample/Basic database), then Drillbridge understands that the following parameters exist and can be used to build the SQL query:
-
Year: January
-
Measure: Sales
-
Market: Washington
When Drillbridge goes to create the actual query used to query against the drill-through report’s associated connection, it’ll replace tokens with the values from the drill-through intersection.
Prerequisites
There are only a couple of things you need in order to install Drillbridge. The target server should be 64-bit, and Java 1.8 must be on the PATH.
Hardware Requirements
Target server should have at least 2GB of RAM, 2GB of disk space, and a modern CPU.
Java
Java should be available on the PATH. In other words, if you open a command prompt and type the following:
java --version
You should see output indicating that Java is installed (rather than some error message). Frequently Java is not on the PATH, in which case you can add it by finding the folder containing java.exe and adding that to the PATH environment variable.
Installation
Extract the Drillbridge zip file to the folder that it will reside in. A
recommended folder is C:\Drillbridge
or D:\Drillbridge
.
In the \bin
sub-folder, try running the following:
Drillbridge.bat console
This will attempt to run the Drillbridge service without installing it first. The point of doing this is to verify that the service works properly and to scan for any errors. If Drillbridge starts up successfully then you could be able to access it at the following URL:
http://localhost:9220
If the Drillbridge service comes up in your web browser, it should be okay to install it as a service. Stop the service by pressing Ctrl+C.
Now install the service:
Drillbridge.bat install
Now start it (you can also start it from Windows Services):
Drillbridge.bat start
The default username and password to log in to Drillbridge is "admin" and password "drillbridge".
Configuring Drillbridge to run in HTTPS mode
Instructions partially derived from [https://drissamri.be/blog/java/enable-https-in-spring-boot/]().
Overview of steps:
-
Generate a self-signed certificate (for testing purposes) using Java’s
keytool
program -
Update Drillbridge configuration to use the certificate
-
Restart Drillbridge
Generating a Self-signed Certificate
Java ships with a keytool
program that can be used to generate a certificate. The following is an example of the command (all one line) to run:
keytool -genkey -alias drillbridge -storetype PKCS12 -keyalg RSA -keysize 2048 -keystore keystore.p12 -validity 3650
You may need to specify a particular Java 8-based version of keytool:
/Library/Java/JavaVirtualMachines/zulu-8.jdk/Contents/Home/bin/keytool -genkey -alias drillbridge -storetype PKCS12 -keyalg RSA -keysize 2048 -keystore keystore.p12 -validity 3650
Note that on Windows you may need to start a Command Prompt using the Run as Administrator right-click menu in order for the certificate generation to work.
The following is an example of the interaction with the program:
C:\Program Files\Java\jre1.8.0_151\bin>keytool -genkey -alias drillbridge -storetype PKCS12 -keyalg RSA -keysize 2048 -keystore keystore.p12 -validity 3650 Enter keystore password: Keystore password is too short - must be at least 6 characters Enter keystore password: Re-enter new password: What is your first and last name? [Unknown]: Jason Jones What is the name of your organizational unit? [Unknown]: FIN What is the name of your organization? [Unknown]: Applied OLAP What is the name of your City or Locality? [Unknown]: Seattle What is the name of your State or Province? [Unknown]: WA What is the two-letter country code for this unit? [Unknown]: US Is CN=Jason Jones, OU=FIN, O=Applied OLAP, L=Seattle, ST=WA, C=US correct? [no]: yes
This generates a keystore.p12 file. On my system since I ran the keytool program from the
Java bin folder where it was located (C:\Program Files\Java\jre1.8.0_151\bin
), it
put the generated keystore.p12 file in that folder. I then moved that file to the root
of the Drillbridge installation folder (in this example, C:\Drillbridge\drillbridge-3.2.0
).
Updating Drillbridge Configuration
You will need to make updates to the /config/application.properties file in the Drillbridge installation folder. The following depicts a modified file:
# Change the following to add a context path for Drillbridge
# This will make it so that Drillbridge is at http://server:port/drillbridge
# instead of the normal location. This is primarily intended for people that
# need to put Drillbridge behind some sort of proxy or redirect. Otherwise,
# just leave this setting commented for normal operation.
#server.context-path=/drillbridge
# By default, Drillbridge runs on port 9220. This can be changed:
server.port=9221
server.ssl.key-store: keystore.p12
server.ssl.key-store-password: drillbridge
server.ssl.keyStoreType: PKCS12
server.ssl.keyAlias: drillbridge
... rest of the application.properties file ...
In the above example the port that Drillbridge runs on has been changed. This is not necessary, but may be useful. You will also need to update any deployed drill-through definitions to update them to use HTTPS and the updated port (or leave the port alone and just change the link definition from http to https).
Restart Drillbridge
You may want to test Drillbridge in "console" mode but opening a command prompt in the /bin
sub-folder of the Drillbridge installation. Then just type drillbridge console
to start Drillbridge in console mode, so you can verify that it starts up correctly.
You may then navigate to https://servername:9221 to verify that Drillbridge loads via HTTPS. Please note that at this time it is not possible to run Drillbridge to support HTTP and HTTPS at the same time.
You will likely receive a warning from your web browser about the certificate being unsigned. You can ignore it for the moment until such time that a "real" certificate is generated/provided by your IT team.
Uninstalling
If at some later point you need to uninstall, run this command (make sure not to delete the Drillbridge files first):
Drillbridge.bat remove
Configuration Overview
At a high level, the process of setting up a drill-through report is as follows:
-
Create a connection
-
Create a report
-
Configure Essbase cube to point to the report URL
Step 3 can be accomplished in a couple of ways. The manual approach is to open Essbase Administration Services, navigate to the cube that you want to set up drill-through on, and select the Edit > Drill-through Definitions menu. This menu allows you to set up one or more drill-through reports on a given cube.
Newer versions of Drillbridge can help with this process if you set things up properly. This process involves using the Drillbridge interface to define an Essbase server, cube mapping and "deployment specification", then using the Deploy option on a configured report to automatically deploy it.
Creating a Connection
A connection is created to a relational database. Out of the box, Drillbridge has support for Oracle, Microsoft SQL Server, and MySQL databases. Support for additional database types can be added (see Adding Third-Party JDBC Drivers section).
To create a new connection, you must specify the following values:
- Name
-
This is not seen by end-users — just provide yourself with a sensible name that makes selecting the connection from a report easy.
- JDBC URL
-
JDBC URLs are different depending on the type of technology used. They include the technology type, server name, database name, and possibly other parameters.
- Username
-
The name to use when connecting to the database.
- Password
-
The password for the user to use when making a connection to the database.
Creating a Report
A report is created with a name, a connection (selected from the list of connections), and a SQL query template. The name given to a report will be displayed when the report is created from a drill-through operation. The SQL query provided must be compatible with the connection technology. For example, if the connection is a Microsoft SQL Server database, then the query written must be valid for that technology. Here is an example query that has been parameterized:
SELECT
PERIOD, ACCOUNT, PRODUCT, MARKET, AMOUNT
FROM
SAMPLE_FACT_DRILL
WHERE
PERIOD = '{{ "name" : "Year", "expression": "#Year" }}' AND
ACCOUNT = '{{ "name" : "Account", "expression": "#Account" }}}}' AND
PRODUCT = '{{ "name" : "Product", "expression": "#Product" }}}}' AND
MARKET = '{{ "name" : "Market", "expression": "#Market" }}}}'
Be careful copying and pasting from this document to a report, the double-quotes may not be the right type — in this document you see "smart-quotes" whereas a normal query should have "regula" double-quotes. The preceding query has four parameters in it — Year, Measures, Product, and Market. This query will expect to be drilled to from a cube with at least these dimensions in it (for example, the Sample/Basic cube has all four of these dimensions as well as extras). If the cube used to drill to the query does not have all the required dimension, Drillbridge will report an error because it is unable to build the whole query. |
Report Tokens & Parameters
Curly braces are used to denote a parameter that Drillbridge should insert into the query before executing. The contents of the curly braces should be a valid JSON expression consisting of key/value pairs.
Creating Deployment Specifications
Deployment specifications are used when deploying drill-through definitions to an Essbase cube. The main thing to know about these are that each line in the region definition corresponds to a drill region that you’d see in the EAS drill-definition editor.
Name
Give a name to the deployment specification. Deployment specifications are separate from reports because you may want to use the same deployment specification for multiple reports.
If there are multiple drill-through reports for a given data cell, then SmartView will show the names of the deployment specifications that are available. For this reason, you will likely want to give your deployment specification a meaningful name for users.
Level-0
Turn this option on if the drill report definition should only be available for cells that are at level zero — that is, at level zero for every dimension. You still need to define a member definition.
Member Definition
The member definition is similar to a FIX
statement in a calc script.
It is used to define the members that should be active for a given drill
report. In EAS, you are able to define multiple drill regions by
creating different entries in a list box. In Drillbridge, when you edit
the deployment specification, the different entries go on different
lines in the text box. Drillbridge will process each line in the member
definition box and translate it to different drillable regions as if it
were different entries in the list box in EAS.
A way to think about the member definition is that you are defining the members from each dimension that will be active for drilling to the given report. For example, consider the following definition which for formatting reasons is multiple lines but should actually be thought of as just ONE LINE in a drill region definition:
@IDESCENDANTS("Time"), @CHILDREN("Years"), @RELATIVE("Location", 0), @RELATIVE("Dept", 0), @RELATIVE("Account", 0)
This example assumes that there is a five-dimension cube with dimensions Time, Years, Location, Dept, and Account. The level-0 option is turned off. We are effectively declaring that the given report is active for cells where all the following are true:
-
The member from the Time dimension is one of the members returned by
@IDESCENDANTS("Time")
-
It is a child of the member Years (meaning that it is one of FY10, FY11, and so on or whatever the children of the Years dimension are)
-
It is a level-0 member of the Location dimension
-
It is a level-0 member of the Dept dimension
-
It is a level-0 member of the Account dimension
Do note that it’s not always necessary to include a definition for each dimension. In fact, the drillable region definition will frequently be simpler. For example, if the cube mentioned above had a Scenario dimension, and we only wanted to enable drilling on the child Actual of the Scenario dimension, and level-0 combinations for every other dimension, then we could just turn on level-0 only and then set the drillable region definition simply as "Actual". If you don’t define a member definition for a particular dimension, then Essbase assumes that all members for that dimension are drillable. However, if the level-0 option is on then you end up with just the level-0 members of dimensions with no specification.
If you are unsure about your drillable region definition and getting it setup correctly, it is recommended that you change your SmartView options to highlight the drillable data cells in some obvious way (such as background color yellow), then drill into the cube to see what cells "light up" as being drillable. You may find that the definition of drillable regions for cells if "too loose" or "too tight" — in other words, you may find that more cells than you want are marked as drillable, or fewer cells than you want are marked as drillable. You can make changes to the drillable region in EAS, save them, then refresh in SmartView to check out your changes. You do not need to stop and start the cube.
Automatic Definition Deployment
Create a new Essbase Server definition inside Drillbridge, then create a new cube mapping to a cube such as Sample Basic. Next, create a Deployment Specification. Enter a name such as "Default" for the spec, then enter the list of members for the drill regions. Separate different regions with newlines, then check whether it should be a level-0 report only or not. Lastly, from the Report editor, choose the Deploy tab and then select the cube, deployment spec, and enter the server name to deploy to. After this is done you will likely want to verify the definition by following the instructions from the next section and logging in to EAS.
Server Name
The server name text box should be filled in with the network name of
the server with Drillbridge as well as the port that Drillbridge is on.
For example, if Drillbridge is installed on drill01.corp.saxifrages.com
using the standard Drillbridge port, then you would enter
drill01.corp.saxifrages.com:9220
in this box and then deploy. If you
need to edit this entry you can do so from the Drillthrough definitions
in EAS.
Manually Configuring or Adjusting Existing Definition
The Essbase Administration Services (EAS) Console is used to define drill-through definitions for a report. Login to EAS as normal, go to the cube to add a drill-through definition to, and the right click to select Edit → Drill-through Definitions. A screen will come up where you must give the drill-through report a name, configure the drillable members, and then define an XML definition.
The drill-through XML definition will generally look like the following:
<?xml version="1.0" encoding="UTF-8"?>
<foldercontents path="/">
<resource name="Assets Drill through GL" description=""
type="application/x-hyperion-applicationbuilder-report">
<name xml:lang="fr">Rapport de ventes</name>
<name xml:lang="es">Informe de ventas</name>
<action name="Display HTML"
description="Launch HTML display of Content" shortdesc="HTML">
<url>
<![CDATA[http://server:9220/drill/1/v2?sso=$SSO_TOKEN$&$ATTR(ds,pos,gen,level.edge)$]]>
</url>
</action>
</resource>
</foldercontents>
Note that this example has the option <name>
tags in it. Check the
Code tab on the Report editor though for the current definition. Current
versions do not include these tags but advanced users may wish to refer
to this and add them in.
Deploying Drill-through Definitions with MaxL
The automation sequence in your environment may necessitate needing to deploy drill-through definitions with MaxL. For example, your automation process may involve totally dropping a cube and creating it from a template or from scratch using an automated process, and the drill-through definitions are lost.
In this case, you can use the MaxL interpreter to add the drill-through definitions. Below sample MaxL code showing a drill-through definition deployment:
login "admin" "password" on "localhost";
echo "Adding drill-through definitions to cube";
create drillthrough Sample.Basic."Our Drill-through"
from xml_file 'drill-definition.xml'
on {'"Actual"'} level0 only;
echo "Finished adding drill-through definitions to cube";
exit;
Note that the drill-through statement makes reference to a local XML file containing the drill definition. For example:
<?xml version="1.0" encoding="UTF-8"?>
<foldercontents path="/">
<resource name="Assets Drill through GL" description="" type="application/x-hyperion-applicationbuilder-report">
<name xml:lang="fr">Rapport de ventes</name>
<name xml:lang="es">Informe de ventas</name>
<action name="Display HTML" description="Launch HTML display of Content" shortdesc="HTML">
<url>/fusionapp/Assetsdrill.jsp?$SSO_TOKEN$&$CONTEXT$&$ATTR(ds,pos,gen,level.edge)$
</url>
</action>
</resource>
</foldercontents>
The specific code for your environment can be found on the Code tab when editing your Drillbridge drill-through report. Do not copy and paste the above code as it will refer to the wrong URL and have the wrong report name.
Drillbridge Server Options
Global Stylesheet
Global stylesheet allows you to define CSS code that will appear inside
of <style></style>
tags on each generated report. This allows you to
globally adjust things such as table spacing, colors, and more. You can
also adjust styles for individual reports if need be (on the given
report’s configuration page).
Force check SSO token against server
Version Note: This feature is not turned on as of 1.3.0. It should be ready for future versions.
Turning this option on forces a stronger security check for drill operations.
Use Session Pinning for POV from User App
Turning this option on causes the server to associate the drilled-from POV from a SmartView/Planning drill operation to a uniquely generated session ID. Subsequent drill requests must then be for that POV.
Essentially what this option provides is the ability to lock down a POV
to a certain session so that a user can’t change parameters in the URL
and drill to data that they shouldn’t be able to see. For example, if
the original POV that is drilled from was for a certain location, an
intrepid user might notice part of the parameters in the URL as
containing Location=100
. They might then change it to be
Location=200
to try and cause a SQL request to be executed and fetch
data that they wouldn’t otherwise have access to. Session pinning
locks the POV to the session when it is generated from SmartView or
Planning.
Use Essbase Outline Caching
Version Note: This feature is not turned on as of 1.3.0. It should be ready for future versions.
For reports with a drill-to-bottom member (such as allowing the user to drill on Qtr1 and have a query be executed for Jan, Feb, and Mar), the outline must be opened and queried for the level-0 descendants of the given member. Normally this operation runs quickly, however, in some cases it may be needed or desirable to cache the results of the outline so that subsequent requests can skip opening the outline and instead just use the cache.
The results of the first Essbase outline query for each member that needs to be queried will be cached for the number of seconds specified in the configuration setting.
Future versions may offer the ability to cache the entire outline so that even the first query does not have a performance penalty associated with it.
Maximum Number of Descendants on Essbase Drill
This option limits the number of members that will be returned from an
Essbase outline query operation. This may be useful for relational
databases that limit the number of items that can be placed in an IN
clause. (Some versions of Oracle have a limit of 1,000 items).
No messages or information will be displayed if more than the
specified number of members is encountered. Drillbridge will simply
silently truncate the number of members and execute the query. So be
warned that this option is simply to prevent queries from failing that
would otherwise fail due to too many entries in an IN clause.
|
Procedures
The following section lists actions you may wish to perform on your installation of Drillbridge and how to do them.
Adding Third-Party JDBC Drivers
Out of the box, Drillbridge includes drivers for Oracle databases, Microsoft SQL Server, and MySQL. It is possible to support additional database types. Simply include the Java JDBC JAR file for your database under the /drivers folder, then restart the Drillbridge server.
Note that databases beyond Microsoft SQL Server, Oracle, and MySQL are not really tested much for compatibility with Drillbridge. If you run into an issue with your version of database please report it.
Change Drillbridge Server Password
Edit the configuration file to change the value for the dtb.password
parameter.
Upgrading Drillbridge
Unless other instructions are otherwise provided, upgrading Drillbridge should be done as follows:
-
Stop Drillbridge service
-
Make a backup of the contents of the Drillbridge folder (such as
C:\Drillbridge
orD:\Drillbridge
-
Uninstall the Drillbridge service by going to the bin folder and running
drillbridge remove
. Note that this command will vary depending on if you need to use the batch file (Windows) or the Bash shell script on Linux platforms. -
Rename the Drillbridge folder (such as to DrillbridgePrevious or some other name to make it obvious that it’s no longer the active installation)
-
Unzip new Drillbridge distribution to same location as previous Drillbridge installation
-
Copy Drillbridge.h2.db from old Drillbridge installation to new Drillbridge installation (replace existing)
-
Update config/wrapper.conf with any changes that have been made to this file from the previous installation. Typically, you will need to replace the "Java Additional Parameters" section completely with the contents from this section on the old wrapper.conf file
-
Optional: from the /bin folder, run
drillbridge console
to test the new version of Drillbridge from the console and to verify there are no fatal errors on Drillbridge start up. Once verified, use Ctrl+C to kill the console instance of Drillbridge -
In the new /bin folder of the new Drillbridge, run
drillbridge install
-
If you need to start the Drillbridge service after installing it, use
drillbridge start
if you have customized your application.properties file, you may need to manually edit the new application.properties file to reflect your changes. You should not copy your old application.properties file on top of the old. |