Dodeca Technique: Essbase View with Cascaded Transaction Details Tabs

A support request came in the other week regarding some help on how to setup a particular report. The user wanted to create a view where the first tab would be a normal “bread and butter” Dodeca view that is based on Essbase retrieval ranges (and where the data shown is based on the values of different selectors. Additionally, when the report is built, for every item on the view (in this case, different products), create a separate tab within the workbook that has transactional details for that product.

So, just to visualize this a bit more concretely, check this out:

The built view!

The built view!

The first tab in this workbook is just a normal everyday Dodeca view with Essbase data. Note the series of additional tabs after the first tab, though: One for each product at the bottom of the Sample/Basic database. These tabs are all generated dynamically when the report is run.

Our Chief Software Architect (hi Amy!) wound up putting together an example that showed this technique off. After I took a look at it, I knew that I wanted to show this technique off (with a couple of twists), because it shows an absolutely amazing cross-section of functionality that highlight the power and flexibility of Dodeca. Even better, this report can be accomplished without any custom programming at all. This is all out of the box functionality that neatly ties together the ability to retrieve Essbase data, relational data, cascaded tabs, hidden selectors, Excel formatting, and more.

Continue Reading…

Creating Windows symlinks for Java installation

This is a note about configuring Java on Windows that is mostly for reference later. Usually when I set Java up on a Windows machine, one of the first things I need to do manually is make sure that JAVA_HOME is set and that the PATH variable contains the folder with the Java executables. The variable on PATH is typically a “bin” folder, whereas the JAVA_HOME variable is up one from that, or up a few more directory levels if it’s a JDK.

Recent versions of Java on Windows have changed the way that Java is installed. Now it creates a C:\ProgramData\Oracle\Java\javapath folder that is added to the PATH, then this folder contains symlinks to for each of java.exe, javaw.exe, and javaws.exe (the main Java executables) over to wherever they are installed on the file system (typically in C:\Program Files or perhaps C:\Java). This is a nice idea so that people are constantly fiddling with their PATH to configure it to a specific versioned Java install (complete with major, minor, and build numbers).

Yes, Windows supports symlinks. While more commonly seen or known in Linux filesystems, Windows’ NTFS filesystem has had support for file links for quite some time. They’re just not used a whole lot by end users themselves.

In any case, unfortunately, this install/configuration process doesn’t always seem to work in terms of creating/updating the symlinks. But they can be fixed pretty easily. Just navigate to the javapath subfolder:

Viewing symlinks in Java folder on Windows

Viewing symlinks in Java folder on Windows

Then run these three commands, altered of course to match up with your actual version of Java:

mklink java.exe "C:\Program Files\Java\jdk1.8.0_102\bin\java.exe"
mklink javaw.exe "C:\Program Files\Java\jdk1.8.0_102\bin\javaw.exe"
mklink javaws.exe "C:\Program Files\Java\jdk1.8.0_102\bin\javaws.exe"

Using Excel AutoCorrect to help type special characters

A client of ours has a member name with a superscript two in it and users re used to typing it in quickly themselves, using the standard Windows Unicode keyboard shortcut, except the Windows shortcut seemed to stop working. This intrigued me a bit, so I did some digging.

As best I can tell, Excel has quietly dropped support for typing in Unicode characters using the Alt + digits shortcut. There seem to be some articles about how this is possible with Excel 2007, but I couldn’t get it to work with Excel 2010 at all. It’s possible that newer versions of Excel fixed/brought back the support.

I even found some articles about tweaking a registry key in Windows to specifically enable the Alt keyboard shortcut, but didn’t have any luck with that making it work in Excel.

That said, since the only special character in question (at the moment) seemed to just literally be the superscript two, as a quick workaround, I recommended setting up an AutoCorrect shortcut in Excel called (super2) that will put in the needed character. The configuration for this is pretty straightforward, and I used it to manually type in a seemingly new offering at The Beverage Company, called Energy²:

Excel's AutoCorrect can be used to help type commonly used special characters

Excel’s AutoCorrect can be used to help type commonly used special characters

If anyone wants to confirm the behavior in Excel 2013/2016 I’d be curious to know what the official situation is when someone needs to type in special characters via their Unicode code.

Update to Thriller MDX over JDBC driver

I made a few adjustments and fixes to the experimental Thriller MDX over JDBC driver I have been playing with off and on. As a quick recap, Thriller is a normal JDBC driver that essentially passes MDX queries straight through to an Essbase server, and then maps the results into a normal JDBC ResultSet using a set of provided “hints” that tell it how to make George Spofford cry flatten the results.

There were a couple of issues related to how queries with various CrossJoins were handled that should now be fixed. Additionally, there are now a couple of new options to provide more configurability over how tuples are split or joined together. Things are definitely getting interesting for this concept.

Continue Reading…

Want to see a Dodeca Excel Add-In demo? Webinar on Tuesday!

Tomorrow (Tuesday, November 22nd) I am doing a webinar on Applied OLAP’s Dodeca Excel Add-In. The Dodeca Excel Add-In is an Excel plugin that works with Essbase to provide a modern, fast, and streamlined ad hoc experience. The add-in provides a thoughtfully designed native ribbon UI, many convenient UI enhancements, and a VBA compatibility layer that makes it straightforward to convert existing Excel solutions that use the old VBA API.

On the webinar I’m going to talk about why and your organization might want to use the add-in, how it compares to the Dodeca Spreadsheet Management System in terms of functionality, and then do a live demo to show various features off. At the end I’ll take questions from the audience

The webinar is tomorrow November 22nd, 2016 at 9AM PST. You can register here. If you’re interested, please attend!

 

 

Understanding Dodeca Tenants

Last week I introduced the concept of different applications in Dodeca. In short, a Dodeca application can be thought of as a completely configurable end-user application with its own authentication mechanism, view selector, and settings. The fact that we can literally spin up a new Dodeca application without having to setup a new database, a new server, or a new anything really is very powerful.

One thing to consider with the application is that the Dodeca objects that we can reference and use within it are limited to those objects that have the same tenant as that application. So let’s talk today about what a tenant is, exactly.

All objects in Dodeca, be they views, selectors, connections, variance commentary, and more, are stored in the Dodeca repository. This is a relational database that is configured when Dodeca is deployed in an organization. This database actually has a deceptively simple table structure, as shown here:

Overview of tables in a Dodeca repository

Overview of tables in a Dodeca repository

Most objects in Dodeca are encoded and then stored in the same table (rather than different tables depending on the type of object or what tenant/application it belongs to): the BINARY_ARTIFACTS table. We don’t really need to worry too much about how the objects are stored there, just that they are.

Let’s say that you are developing a new Dodeca application and building various views and connections in it that users will use. Whether you realized it or not, these views (and the application itself) are created inside of a distinct tenant. This tenant code is associated to every object in the Dodeca repository. Let’s take a closer look at the column definitions for some of these Dodeca repository tables:

Column definitions for various tables in a Dodeca repository

Column definitions for various tables in a Dodeca repository

Look at how there is an ARTIFACT_TENANT column on the BINARY_ARTIFACTS table (as well as BINARY_ARTIFACT_USAGES). This column is part of the primary key for these tables and serves as an absolute way to partition the different objects from each other all in the same repository. When we’re building an application in a given tenant in Dodeca, we can’t even use connections or views from another tenant because the application literally can’t see the objects in the other tenants.

As I mentioned, whether you realized it or not, each application you have run in Dodeca was actually associated with a particular tenant. Let’s actually take a look at the desktop shortcut for a sample Dodeca application in the lab:

Viewing the launch URL for an application in Dodeca

Viewing the launch URL for an application in Dodeca

Notice that the launch URL is a URL containing two parameters: a tenant and an application. When we need shortcuts or links to other applications, we can just change the tenant or artifact reference and jump directly to them. Since these are used in URLS it’s also why I recommend being consistent with them and just making them all caps with no spaces.

So, why is this whole tenant business important, anyway?

The Dodeca architecture (and the repository architecture) let you scale up in functionality very, very, easily. In many software systems, this ability to scale with applications and tenants would frequently involve deploying a new database, a new server, or both (rendering it quickly infeasible for many purposes). Here we can achieve scaling without a new server, without a new relational database, and without even so much as having to reboot the server.

If you need/want to create a new application for users that will be able to reuse connections/views from the existing tenant, then you likely want to just create a new application in the same tenant. If you need to completely partition things off (such as for a new group that is going to deploy Dodeca), you can simply create a new tenant. And of course, if you really need to cordon things off, such as for dev/prod separation, a completely different group, or whatever, you can always deploy a new instance of Dodeca. There are a lot of possibilities.

Multiple Applications in Dodeca

I want to talk about one of the more interesting aspects of Dodeca and its extensibility: applications. In some of my past posts, I’ve alluded to this notion of applications (and also something called a tenant) in Dodeca but haven’t really had a chance to dive into much detail. I’ll get into the notion of tenants some other day, so today’s focus is on what an application is.

In our world (the EPM world, that is), the term application has a lot of meanings. In Essbase-land, it’s a container for cubes or plan types. In the world of software, it usually means a single program. In the world of Dodeca, an application defines the views, behavior, look, and other properties that a user will see when they launch Dodeca.

More specifically, consider that when you login to Dodeca, you are actually logging in to a specific Dodeca application (whether you realized it or not). Typically on the left side of the Dodeca window is the list of views that you have access to. This list of views is called the view selector. Now, the view selector itself has several options that determine which views are shown to a given user, but for now just let’s just think of the whole thing (irrespective of content) as the view selector.

Additionally, when logging in to Dodeca, it’s quite common to use your enterprise credentials (your username and password) that give you access to a given Essbase server and other things around the company. Interestingly enough, the authentication strategy in Dodeca can be configured on a per-application basis. This means that you might want to use a particular cube to authenticate users in one app, but use a different cube, or LDAP or Active Directory, or something else entirely, to control authentication in another application.

If this feels like an amazing amount of configurability to wield, that’s because it is. Many (most?) software programs that allow for enterprise login capabilities (that is, logging in against an LDAP or similar server) tend to have that configuration buried in a configuration file somewhere, and they only support a global authentication provider configuration.

Now that we know a little about what an application is, let’s take a look at the process of creating an new application. As with most objects in Dodeca, Applications have their own menu item under Admin, which brings up a list of apps that can be edited. From there, it’s a simple matter of just selecting New and typing in some details:

Creating a new application in Dodeca

Creating a new application in Dodeca

Let’s give this application an ID of “OPS” – we’ll assume for this example that Dodeca has heretofore been used just in the Finance group, but that the folks in Operations saw how productive and happy the Finance people were and they want to get in on some of that sweet 12-sided action. Also, it’s customary for the application ID to be all capitals and not contain any spaces, as it will be referenced in URLs and shortcuts. We can give it a nicer name, however, which in this case will be “Operations Application”.

Next, we need to configure some essential properties for the application. There’s a lot we can configure, but for now we’ll stick to the basics. First up, let’s change the caption for the app (the text shown for the titlebar of the program window):

Configuring properties for the new application

Configuring properties for the new application

Remember, bold properties are the ones that we’ve changed from the default, so in this screenshot I’ve only changed the caption. Next, let’s set the authentication properties:

Configuring the authentication strategy for the app

Configuring the authentication strategy for the app

There are several choices here. One of the more common and useful is the EssbaseSimpleAuthentication service. This essentially means that a given cube (one of the defined Dodeca Essbase connections) will serve as the object which is logged into in order to validate a user. The EssbaseConnectionID setting is simply a dropdown where we can quickly select an existing connection.

Setting the view selector and hierarchy for the application

Setting the view selector and hierarchy for the application

Next, we configure a view selector. For this application we’ll just go with a ViewSelectorExplorerBar, then choose a HierarchyID (again as a refresher, hierarchies are also defined in Dodeca as with other objects). That’s actually all we need for now. Since I’m just using an existing hierarchy for demonstration purposes, I expect the contents of the hierarchy in the new application to be the same contents as what I see in this existing application used in the screenshots.

After committing the changes, the new application is ready to be used. In my environment, I can launch it with the following launching the URL http://dodeca:8080/dodeca/smartclient/AppliedOLAP.Dodeca.SmartClient.application?t=SAMPLE&a=OPS

Note that I just took an existing URL and changed the last parameter (...a=OPS) so that it points to the OPS application. Upon launching the URL, my new application comes up and I can run a view in there as I normally would:

The newly created application has been launched and a report built

The newly created application has been launched and a report built

You may notice that the look of this application is different than the one we were just in. This is just configured on the application properties. While I didn’t change the look of the new application from the defaults, as it turns out, the app we’ve been working in to this point does have a custom style, so that’s the reason for the difference.

Dodeca Applications Summary

Dodeca has an even greater form of partitioning functionality (called tenants) that I’ll cover in the near future, but I hope this gave you an interesting since of some of the power inherent in the Dodeca application model. I think it’s incredible that we’re able to rapidly spin up a completely distinct Dodeca application by just configuring a few properties. One thing to keep in mind is that this didn’t require setting up a new Dodeca server, a new Dodeca repository, installing software or anything. It’s just modularity that’s inherent to the design of the software. Applications can be used to provide specialized behavior for different groups, sub-groups, types of users, divisions, or whatever else makes sense.

Greet your Dodeca users with a custom landing page

Today’s Dodeca post will be on the lighter side, but is nonetheless a pretty useful feature. One of the interesting aspects of Dodeca is its support for multiple tenants and applications. This means that within just a single Dodeca repository (created when the Dodeca service is installed), we can have multiple distinct applications. This allows for creating different applications for different groups of users. It’s really quite powerful and something I will explore more in the coming weeks.

That said, since we can organize Dodeca into multiple distinct applications, that also means we can configure the properties of each application however we want. And one of those settings we can configure is the default view. The default view is a normal Dodeca view that is opened automatically when the application is launched.

Continue Reading…

Playing with the Thriller MDX/JDBC Driver in Drillbridge

Last week I talked about a new side project, which is a JDBC driver called Thriller for executing MDX queries against Essbase and mapping the results back into a normal relational database. And at the time, I said that this driver had some really interesting use cases, such as in Dodeca, Drillbridge, ODI, and other tools that work with JDBC drivers.

Speaking of Drillbridge – in the very near future I will be sharing Drillbridge’s official future direction, which I think is really exciting, but more on that later. In the meantime, let’s drop this baby into Drillbridge and see what happens!

The following walkthrough of using Thriller with Drillbridge will show off some features that are only available in the licensed version of Drillbridge, although this should in theory work with Drillbridge Community Edition (the free edition of Drillbridge), assuming you have the Thriller driver JAR file.

Continue Reading…

Running MDX queries through a JDBC driver (for fun?)

So there I am, sitting in front of the Alaska Airlines gate at Boston Logan airport, waiting for my flight home to Seattle. It’s not a particularly glamorous terminal – the divorce from Delta hasn’t been too kind to Alaska at BOS; Delta seems to have kept the house and kids while Alaska microwaves Lean Cuisine on a futon in its bachelor pad…

As I’m pondering why there are white rocking chairs in the terminal, my phone rings with a familiar name: Mr. Brian Marshall. We catch up and exchange pleasantries before pivoting over to more important matters (all things EPM of course!).

Brian: “So… Vess.”

Jason: “Oh boy…”

So we get to talking about accessing Essbase data through a Java database driver, á la Vess. And we get to talking about running MDX queries and dumping the output – á la Camshaft.

And as the talk goes on I end up saying something stupid like this: “You know what might work? Jjust pass an MDX query through the driver over to Essbase and map the output to a fake table… It’d be like an unholy combination of Vess and Camshaft. You could probably knock it out in a day or two.”

And at that moment I knew I wouldn’t be able to resist opening my laptop for the five plus hour flight home. Continue Reading…