Category Archives: dodeca

Improving MySQL JNDI Connection Reliability

I blogged quite some time ago about using JNDI to configure database connections in Dodeca. As I mentioned then, JNDI can bring some useful improvements to your configuration, management, security, and administration of your environment versus how you might be configuring normal JDBC connections. To be clear, this isn’t because JNDI connections are inherently better from a performance standpoint, it’s just that it might be a cleaner solution in various ways.

My original blog post looked at configuring a pretty typical MySQL connection in JNDI. As I have worked with this in the last few months, I have run into a few issues with the configuration as it related to connection timeout issues. I was occasionally getting some timeout issues like this:

Dodeca error dialog reporting a timed out MySQL JNDI connection

MySQL connection timeout when configured with JNDI

Helpfully enough (or perhaps unhelpfully) the error message itself reports that perhaps the autoReconnect=true setting would be of help. I’ve actually used that setting in the past and it seemed to help things out. But as it turns out, that setting is deprecated and should not be used. There are some alternative techniques that can/should be used to ensure the program gets a valid connection back.

One common technique is to specify a “validation query”. This is often something like SELECT 1 or SELECT 1 FROM DUAL depending on the particular database technology being used. You can use SELECT 1 for MySQL. What this essentially means is that before returning a connection via JNDI to the Java servlet to do things with, the connection pool manager is going to run the validation query to ensure that it is indeed a valid connection (able to connect, doesn’t error out, and so on.

Interestingly enough, MySQL in particular has added an optimization for this use case such that you can give it a sort of fake query (code: /* ping */) and it’s slightly more optimized than the overhead involved with a SELECT 1.

Together with this optimized test query, some additional attributes on the JNDI configuration (testWhileIdle, testOnBorrow, testOnReturn, and removedAbandoned, I’ve updated the overall JNDI configuration and it seems to be much more robust. Here’s the new connection JNDI code from my Tomcat context.xml:

<Resource name="jdbc/dodeca_sample" auth="Container" type="javax.sql.DataSource" username="dodeca" password="password" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/dodeca_sample?noDatetimeStringSync=true" maxActive="100" maxIdle="30" maxWait="10000" removeAbandoned="true" removeAbandonedTimeout="20" logAbandoned="true" validationQuery="/* ping */" testWhileIdle="true" testOnBorrow="true" testOnReturn="false" />
<ResourceLink name="jdbc/dodeca_sample" global="jdbc/dodeca_sample" type="javax.sql.DataSource"/>

Dodeca Zebra Striping with WBS Example

I wanted to punch up a Dodeca view the other day by putting a little zebra striping on some relational data. Although having built-in support for this is on my wishlist, for now a simple workbook script (WBS) gets the job done. This is also alternatively called “greenbar”… depending on what decade you were born in.

The technique itself is pretty simple. You can accomplish this in a few ways in Dodeca (as with everything), so here’s one way to go. First, when the view is opened, we have a workbook script to set a color index (that’s the first step in the following screenshot). In this case I am setting a very light grey to be color Index 2.

In the next step (the one that actually does the striping), I have defined a simple method that applies to a range named “Address” and just paint every other row depending on if it’s even or not. I’m just using the formula =MOD(@CRow(), 2) = 0, which is a normal Excel function (modulus), and a workbook script function (@CRow()) that returns the current row number being processed. If it’s even, then the cell should be painted. If not, nothing happens. So if you wanted two different colors you’d just add a new color set step and a new SetFill method that applied to odd rows.

Here’s a screenshot of the full WBS:

Screenshot of Dodeca Workbook Script editor showing how to implement a zebra striping technique

Dodeca WBS Zebra Striping Example

Webinar Tomorrow: One Stop Data Shop with Dodeca

I just wanted to plug a webinar that I am conducting tomorrow on Dodeca. I’m excited to do this webinar for a few reasons. Usually on our monthly webinar series we look at a specific feature and do a technical walkthrough. The focus of this webinar is a little different, though. This is more of a case study looking at how a business has a lot of existing processes and reports built around Essbase and the Excel add-in, but needs something more sophisticated to handle their needs, including:

  • Using Dodeca to build views based on existing Excel sheets that need to fetch data for multiple tabs and multiple data sources in one fell swoop
  • Facilitate user input directly to Essbase with commentary
  • Combine Essbase and relational data in a single view (and save user’s a trip to a tertiary system!)
  • Drill-through in Dodeca: not just from Essbase to relational but Essbase to Essbase or relational to relational
  • Batch reporting

Having consulted in the Hyperion/Essbase world for many years I can easily say that there were countless organizations with sophisticated and complicated (but tried and true) Excel/Essbase-based processes that would greatly benefit from a tool that helped automate, manage, and control things. So that’s exactly what I hope to convey tomorrow during the webinar. If you’d like to attend, please register and I’ll look forward to showing you Dodeca and taking your questions!

You Should Rethink Your Essbase/VBA Spreadsheets

Essbase is often described as getting its start in finance departments. It was finance’s secret weapon. There are a number of ways that Essbase tends to have evolved organically from this foothold. One common evolution is for the tool to gain critical importance to a company, and need to be more robustly managed by the IT department (in theory, anyway, I’ll save my thoughts on this common tragedy for another time).

One of the other common developments for Essbase in the finance department is that certain books/sheets evolve more and more functionality over time. They get augmented with automation, macros, VBA code modules, and more. This is all often on top of complicated formula references across sheets, and sometimes even across different books.

Continue Reading…

Dodeca Workbook Script documentation available online

I kind of posted this on the down-low earlier this week but got outed by my auto-tweet feature, where it got picked up by Oracle EPM Blogs and a few others, so I thought I should just write about this for real.

Earlier this week, the new documentation for the Dodeca Workbook Script functionality went online. In case you’re not familiar, Workbook Scripts are part of Dodeca Spreadsheet Management System’s event-driven extensibility model. You can kind of think of it as an elegant blend of the best aspects of Microsoft Access macros and Visual Basic, but designed from day 1 to make it easy to facilitate really sophisticated functionality in views that contain Essbase, SQL, or MDX data (or all three on the same sheet!)

The documentation contains a full index, all methods/overloads, events, and functions. It represents one of the first major steps towards my goal of making incredibly high quality documentation and online resources available for people that are developing with Dodeca. In the future I am hoping to get even more documentation online with samples and other resources that make developing even better.

Handy Essbase Data Audit Log Query for Dodeca Repository

I’m putting together a simple Dodeca view that shows information from Dodeca’s data audit log tables. One of Dodeca’s really nice Essbase-specific functionalities is that it logs all user inputted changes to Essbase data. This comes in handy for many organizations so they are able to tell when something changed, what changed, who changed it, and what the old value is. Because the same tables are used for every single cube in the system and every cube can have different dimensionality, Dodeca uses a flexible table structure to record all of the dimension names and members that changed. A naive query against these tables will give you multiple rows for the same data point (one for each member in the POV), but I wanted something quick and easy to consolidate to a single row per data item changed.

Most of my own Dodeca servers use MySQL for the repository database (although it’s common to use SQL Server, Oracle, DB2, and others). I discovered a really great MySQL-specific function called GROUP_CONCAT that is able to join fields from multiple rows, and it works great. Here’s my Data Audit Log query:

SELECT 
    AUDITLOG.SERVER,
    AUDITLOG.APPLICATION,
    AUDITLOG.CUBE,
    AUDITLOG.USER_ID,
    AUDITLOG.CREATED_DATE,
    GROUP_CONCAT(DP.MEMBER SEPARATOR ', ') AS MEMBER_NAMES,
    GROUP_CONCAT(DP.ALIAS SEPARATOR ', ') AS ALIASES,
    IFNULL(ITEMS.OLD_VALUE, '#Missing') AS OLD_VALUE,
    ITEMS.NEW_VALUE
FROM 
    DATA_AUDIT_LOG_DATAPOINTS DP, 
    DATA_AUDIT_LOG_ITEMS ITEMS, 
    DATA_AUDIT_LOG AUDITLOG
WHERE 
    DP.AUDIT_LOG_ITEM_NUMBER = ITEMS.AUDIT_LOG_ITEM_NUMBER AND
    ITEMS.AUDIT_LOG_RECORD_NUMBER = AUDITLOG.AUDIT_LOG_RECORD_NUMBER
GROUP BY
    ITEMS.AUDIT_LOG_ITEM_NUMBER
ORDER BY
    CREATED_DATE DESC

And here’s a view from a SQL tool:

A query for viewing Dodeca’s data audit log tables for Essbase data

I went ahead and used the concatenation function twice – once for the regular member names, and again for the aliases. Not every member of every intersection has an alias, but fortunately the function handles it just fine. So now I have everything on one line exactly like I wanted.

Jazz Up Those Static Dodeca Views With Advanced Essbase Features

Oftentimes when I am demonstrating or teaching aspects of Dodeca to people, they are amazed at the sheer number of options and configurations that are available on a view. Fortunately, I am able to tell them that yes, there are many, many options – and they are there if you need or want them, but they won’t get in your way. The defaults are very sensible and getting a basic Essbase-based Dodeca view running is incredibly easy.

Another thing to keep in mind is that for the most part, the extreme amount of options and flexibility we have on a single view is often available to us in lieu of code. So, tasks that typically required some non-trivial amount of VBA code are now completely code free. When we need some advanced functionality that isn’t available out of the box, we can use Workbook Scripts, which is an event-driven scripting technology that is particularly well suited to working with spreadsheets and the data contained in them.

That all said, today I want to walk through a bit of a cross-functional example that starts with a very typical Dodeca view based on an Essbase retrieve range, then enhance it to give our users the ability to zoom in on the different time periods in the view without having to rebuild the view. So we’re going to blur the line a bit between static and dynamic reports, and our users are going to enjoy some additional flexibility and convenience with regard to their reporting (and keeping users happy is always a good thing, right?).

Continue Reading…

JDBC and JNDI connections compared (with a Dodeca example)

Have you ever wondered what the difference between a JDBC and a JNDI connection is? If you’re familiar with at least one of these, it’s likely that you’re familiar with JDBC (but probably not JNDI).

JDBC connections come up often in the Oracle world (for good reason). It’s a standard model/framework for designing drivers that interact with relational databases. As it pertains to us in the Hyperion, Dodeca (and even Drillbridge!) world is that we often define connections in terms of specifying JDBC parameters. This typically means a driver class name (like com.mysql.jdbc.Driver for a MySQL driver), a JDBC URL (a URL specifying a server and optionally a database/schema and other parameters), and credentials (username/password). So if you’ve poked around in your infrastructure much at all, there’s a good chance that you’ve come across a JDBC connection.

You may have even come across something called JNDI and even vaguely known it was sort of an alternate way to configure a connection but never really had to bother with it. I’ll spare you the acronym details, but think of JNDI as a way of organizing database connections (and other objects actually, but we don’t need to worry about that at the moment) such that instead of our app/system having to know the server name and credentials, it just asks “Hello, can I have the resource that was defined for me with name XYZ?”

Continue Reading…

Dodeca Technique: Multiple Essbase Data Sources in View

When I’m talking about Dodeca features, one that very often comes up is that Dodeca views have great support for multiple data sources. I’ve seen customers and clients use this to give them a cutting edge in terms of developing reports that tie together information from disparate data sources in a flexible way that was previously very cumbersome or impossible with the tools at hand. Among other instances, this feature comes into play when it would be beneficial for a user to view data that happens to reside in multiple databases, but for the sake of the user experience, we don’t want them to have to run multiple reports.

So today I want to look at a very simple Dodeca view that taps into multiple sources. There are a couple of nuances to consider for this development scenario. Consider that a typical view with a single data source will just have its connection specified explicitly as a property on the view, and the selectors on the view (if any) will assume that they are to be populated based on that connection as well. For example, let’s say we have a view based on the Sample/Basic database, and we have two selectors that are dynamically generated: Time and Product. When Dodeca goes to generate the list of Products to display to the user to make their selection(s), it knows to use the Sample/Basic database. However, if we want to have multiple selectors and have their contents be based on a particular cube’s outline, then we need to simply associate the proper connection with the selector.

For today’s example, I’m going to build a simple view that has one tab based on Sample/Basic and another tab based on Demo/Basic (as a brief aside, Demo/Basic is Sample/Basic’s less popular, less-talked about sibling that is eagerly awaiting its day in the spotlight). Note that while this example will have multiple Essbase connections and multiple selectors (one on each database), this isn’t necessarily how a view will always need to be configured. If you have a selector whose contents aren’t dependent on a particular database, then you wouldn’t need to worry about the connection specification for that selector.

Continue Reading…

Top Posts of the Year 2016

Well, 2016 is almost behind us. I haven’t done this before but given that I’ve been doing a fair bit of blogging this year, I wanted to point out the “top posts of the year” on ye olde Jason’s Hyperion Blog. The subjects are diverse (as far as a Hyperion blog goes I suppose) and I think are an interesting reflection of what things people are interested in. Starting with the most popular:

Running MDX queries through a JDBC driver (for fun?): I got a lot of feedback on the MDX over JDBC franken-driver in JDBC. In retrospect, I think this goes to show how rich, diverse, and challenging the world of data integration around Essbase can be. People – developers, consultants, users, whoever – are constantly spending time, energy, and money getting data in and out of their EPM systems. The Thriller MDX-over-JDBC driver hit a real chord with some people that see it as a way to bridge the gap between EPM and other systems.

Drillbridge acquired by Applied OLAP: Probably the biggest news for me this year. Applied OLAP acquired all of Drillbridge (as well as myself) and added it to their portfolio of products, including the Dodeca Spreadsheet Management System, Dodeca Excel Add-In for Essbase, and the Next Generation Outline Extractor. Recently I announced that the enterprise/supported version of Drillbridge was officially named Drillbridge Plus and offers many compelling features, such as upper-level drill support from PBCS.

Kscope16 sessions I’m looking forward to: Interestingly, people were very curious as to what sessions I planned on attending at Kscope16. I’ll be sure to post thoughts on Kscope17 sessions when the time is right. I’ll have a single presentation at Kscope17, which will focus on “demystifying the PBCS REST API”. I hope it’s a crowd-pleaser that people will find useful.

Dependent Selectors in Dodeca: I blogged extensively about Dodeca this year, and apparently this was one the most popular article. Dependent selectors are a great feature in Dodeca that allow for narrowing down or otherwise dynamically generating the selection values for a user. For example, choosing a state could cause another selector to narrow its list of cities to just those in the given state. I’m both surprised and not surprised that this is the most popular Dodeca article. I think it’s cool because this is the type of feature that really enhances the user experience by respecting their time and making a system easier to use.

Data Input with Dodeca, part 1: Dodeca is great for providing a structured way to input data into a cube that is incredibly more robust than “we do lock and sends”. This was the first part in my data input series (six articles!) that covered inputting to Essbase, relational datasources, both at the same time, commentary, and more.

Camshaft MDX tool updated and available: Again with the MDX/data integration theme, people were very curious to find out more about a command-line tool that helps convert MDX queries to useable data files.

Essbasepy updated for Python 3: Surprisingly (to me), people the article on the Essbasepy library caught a lot of people’s attention. A lot of people are using Python to do integration/automation, and Essbase is definitely a part of the picture.

TBC Files for Bankruptcy: My tongue-in-cheek look at the woeful situation at everyone’s favorite beverage company!

Drillable Columns in Drillbridge: Lastly (but not least), one of my favorite features in Drillbridge and I think one of the standout features that you get when it comes to drilling into a web browser instead of a tab in your workbook: the ability to drill from a drill. With drillable columns, you can specify a subsequent view to drill to and the POV of the row (the global POV plus the key/values from that row) will be used to execute it. Many organizations are using this to drill into further/related journal detail, PDF files of invoices, and more. It’s a great feature!

Well, that’s the highlights from 2016. I’ll be looking forward to another productive blogging year with all sorts of exciting things regarding Dodeca, Drillbridge, the Next Generation Outline Extractor, Kscope17, and even a few secret projects I have been working on. Happy new year!