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!

 

Dynamic Calendar with Comments in Dodeca

I keep telling myself that I’m going to do more blog posts that are short and sweet, instead of these epic 6,000 word monsters, but I’m just having too much fun. Today’s article is going to be a little bit of thinking outside the box. Outside the box – but inside the grid. This is actually inspired by a use-case I saw a Dodeca customer present on at Kscope this year.

The basic original idea was “Why not make a calendar view in Dodeca?” Those of us that are heavy in the Essbase/Excel world are used to modeling financial data, but spreadsheets are used for countless different activities. Create a workout plan. Create a list of your favorite movies – and even make a calendar.

In the context of Dodeca, a calendar, whether it be static or dynamic, is a really cool use of the tool, if a bit unorthodox. A lot of financial departments and companies have very complex but methodical financial processes, particularly around the “close period”, and keeping everyone on track and coordinated is important. And companies that have Dodeca already have a very quick and very easy way to make dynamic spreadsheets centrally available to their users without having to email around a bunch of Excel files.

For today’s post I am going to start off with a basic calendar, then absolutely turbocharge it. The user is going to be able to select a month and year from Dodeca selectors and the calendar will dynamically update. We’re going to make it so we can add comments to each cell of the calendar. The comments will be associated with arbitrary intersections of our choosing (a great feature of Dodeca comments that I’ll go into extensively in this post). We’re going to accomplish this using the built-in Dodeca comments functionality. Along the way, I’m going to show off some of the power and versatility of Dodeca comments and use practically every option available.

Continue Reading…

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…

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.