July 22, 2016

Data Input with Dodeca, part 5 – Relational Database input

I have been really, really looking forward to writing this continuation in the Dodeca Data Input series, for a couple of reasons. For one, it’s a genuinely useful feature that Dodeca implements very well. But secondly, and perhaps more important, the ability to get and store this data from users is just an absolutely missing piece of functionality in the traditional Hyperion toolbox. So this is going to be a bit of a long article but will cover how relational data input in Dodeca works and why it’s so important.

As a quick recap, up to this point I’ve covered basic Essbase data input, cell/variance commentary, going under the hood to look at the audit log tables, and focused calc scripts that run after Essbase input. To this we will now add SQL/relational data input. To put it in context, relational database input is one of the tentpole Dodeca features, and stands next to other heavy hitter features such as Essbase input, comments, drill-through, and cascading reports. Now, all of the individual features of Dodeca are useful and interesting. And yet, I see relational data input as a feature that almost singlehandedly makes Dodeca greater than the sum of its parts.

Relational Data as Part of the Hyperion Toolbox

Before jumping in to the technical implementation of relational data input in Dodeca, I want to wax philosophical a bit on how important I think this feature is. It has the power to be a game changer for a lot of organizations.

My own experience with Hyperion/Essbase is from all angles: as a full-time Hyperion developer for multiple companies, as a consultant with multiple companies, working on dozens projects, and as an independent software vendor with a Hyperion product. Further, my computer science degree minor was in relational database algebra (yes, I’m a nerd). I wrote the innovative Drillbridge software that bridges the gap between Smart View, Planning, and Financial Reporting and relational data. I created an absolutely free version of the Drillbridge software that is fully functional and is downloaded daily and regularly put into production with zero assistance from myself.

So to say that relational data is near and dear or otherwise useful to me is an understatement. As with Dodeca’s robust and battle-tested middle tier component (the secret sauce/glue between the Dodeca client application and all Essbase/relational database servers), Drillbridge is written in 100% Java and contains a web interface for managing its configuration.

All that background is basically my long-winded way of saying that I’ve worked with Hyperion a lot, and if anyone should be qualified to find a way to get user input into a relational database, it’s the guy that programs in Java, writes CDFs for fun, and has created systems that literally take input from a user and put it into a relational database.

And yet, even with all of this experience, getting relational data from Hyperion users has traditionally been this absolute missing link. The situation with pure Essbase data has been a little better: you had lock and send or submit data with the classic add-in/Smart View. Of course, lock and send is not without it’s issues. It’s more of a power user thing, although as I’ve explored in the past, Dodeca can quite nicely provide some structure to the Essbase input process that makes things much more user friendly.

Essbase Relational Data Input Anti-Patterns

I seem to harp on this notion of anti-patterns a bit. An anti-pattern occurs when something is ostensibly designed incorrectly. This happens a lot in the Hyperion consulting world, for instance. A client might be having an issue with their system or performance, and they come to a consultant looking for assistance on that one particular symptom. Unfortunately, all too often, the performance or technical problem is essentially predicated upon a series of unfortunate business, technical, and design decisions (usually ones that can’t be easily/cheaply rectified). Or the company has otherwise accumulated a lot of technical debt – where band-aids have been put on a system in order to keep it hobbling along, without addressing the underlying design problem.

Armed with only Smart View or the classic add-in, but needing to get relational input from users, an intrepid (or masochistic) Hyperion developer might choose a few different routes to try and satisfy this, all of which are less than ideal for various reasons;

Dummy members/dimensionality for pseudo relational input
Text measures
Enter supporting details/data to Excel spreadsheet, and email to admin or store on share folder/drive
Custom VBA program/functionality to upload supplemental detail
Custom software/web service for user to input data

All of these approaches have issues. Adding dummy members or attributes to a cube is less than ideal and “pollutes” the cube. Some additional functionality might be needed to pull that data out of the cube and marshall into a relational database. Sending emails and saving spreadsheets off to the local share drive is a disaster waiting to happen. I’ve railed on VBA solutions before. They are a mixed bag. Speaking as a consultant, they all too often turn into spaghetti code maintenance nightmares, fraught with glitches, security issues, and more. Lastly, a custom web service or software package might fit the bill, but it takes time and money.

Dodeca Does It (#dodecadoesit)

Let’s explore some what-ifs:

What if users could input data using the same interface they are using for reporting and analysis
What if we didn’t need to make a single change to our cube dimensionality and cube get relational input from the user
What if it didn’t require any custom programming, save for the SQL statements themselves
What if we could work with almost any major relational database technology on the planet
What if this functionality was a first-class citizen in our software and worked out of the box?
What if we could format the data to our heart’s content using a spreadsheet model that we already work with day in and day out?

Here’s the thing: Essbase ostensibly started its life not really caring at all about SQL/relational data. As has been wistfully recalled time and again, Essbase was the secret weapon sitting under your desk. The classic Excel add-in could magically slice and dice data. Over the years, Essbase – and users, whether they realized it or not – grew to have an increasingly important relationship with relational data.

Even the most experienced of Hyperion developers is often at a loss when it comes to providing their users a cohesive solution that can seamlessly work with relational and multi-dimensional data (or OLTP/OLAP if you prefer). And yet, this is a bread and butter feature of Dodeca. It feels almost hyperbolic to say, but I just can’t stress this enough.

Okay, enough with the abstract and architectural. Now let’s move on an actual implementation inside of Dodeca that writes back to a SQL table of our choosing.

Implementing Relational Data Input With Dodeca

For the remainder of this exercise, we’re going to work with a table called EMPLOYEES. It’s a very simple table that contains a employee ID, first name, last name, and a comment about a given employee. The employee ID must be unique (it’s the primary key). The other fields are just made of text. Thy MySQL table definition would look like this:

CREATE TABLE `EMPLOYEES` (
    `EMPLOYEE_ID` int(11) NOT NULL AUTO_INCREMENT,
    `FIRST_NAME` varchar(25) NOT NULL,
    `LAST_NAME` varchar(25) NOT NULL,
    `COMMENTS` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`EMPLOYEE_ID`)
)

Also note that the EMPLOYEE_ID field is an AUTO_INCREMENT value. This is MySQL’s equivalent of a SQL Server identity column, or using a sequence in an Oracle table to generate the next unique value. Essentially what this means is that the database engine itself will take care of creating new values for us, so we don’t (in fact, we don’t want to) insert them manually or ourselves. However, we will be interested in the value that the database engine assigns to the rows we insert. You’ll see later how this is accomplished.

I went ahead and put in a couple of rows using a generic database tool and built a very simple Dodeca view that pulls back the data. Here’s a preview of that:

A basic Dodeca view with relational data

One of the blog posts leading up to this one was a quick crash course in how to put relational data into a Dodeca view, so if you’re fuzzy on that, then I suggest you take a look at that. But in a nutshell, here’s what is going on with respect to the template:

Basic template for relational view

Things to note:

The range that will be populated with data from the relational query is named EmployeeComments and contains four columns (one for each column we retrieve with the query)
I turned on the option to return the headers from the query; those will be populated into the first row of the range. This can be turned off and custom headers can be supplied, but in this case I want to just use them
I’ve applied some light formatting to spruce things up a bit: row 2 (the first row of the range) is grey with white text, and I added a spacer row/column to offset the table a little bit. I’m going to set the options on this view to not show row/column headers or the different tabs (again, just settings that I can easily update)

Note the Grid Properties settings that I’ve updated for the view, in order to enhance the visual appearance of the rendered view for the user. In particular, I’ve turned off grid lines (GridLinesVisible = False), headers for the cells won’t be displayed (RowAndColumnHeadersVisible = False), and tab names won’t be displayed (TabsVisible = False).

Updated Grid Properties for relational Dodeca view

Next we need to use the DataTable Range Editor to tell Dodeca a little about how the SQL Passthrough DataSet we defined earlier is rendered into the named range on our sheet. In the previous post looking at this functionality, we got to leave many of the configuration values as their defaults. This time we need to set a few more things in order to allow user input in addition to viewing the data.

DataTable Range Editor associated with the SQL passthrough dataset on our view

Of particular note in this editor:

The DataSheetRangeName is set to the named range from our Excel template (EmployeeComments)
The SetDataFlags configuration value includes a value of InsertCells (note that the SetDataFlags parameter can accept multiple values; in this case we are setting just one of of them).

Now let’s cut over to the Query Editor associated with our SQL Passthrough DataSet and take a look at the configuration there:

Query Editor editing the SQL passthrough dataset for employees

Now, it looks like there is a lot going on here but it’s not too bad. Let’s walk through all of the things that are set in this query. Also remember that the this query configuration is associated with the SQL Passthrough DataSet itself. In other worse, this is the type of logic that we only need to configure in one place and we can then reuse across multiple views if we want (as opposed to having to reinvent this configuration/logic for each individual view).

The important aspects of this query configuration are the SQLConnectionID, DataTableInfo/Columns, and the values in the SQL configuration (InsertSQL, SelectSQL, and UpdateSQL):

SQLConnectionID: this is the simplest item to configure. We simply use a dropdown box to choose from our list of SQL connections that have been mapped in previously. We set this regardless of writing data back to SQL or not (we need to set it even if we’re just reading data from SQL, obviously)
DataTableInfo/Columns: often these don’t even need to be set because Dodeca can figure them out dynamically. It depends on the JDBC driver in play. I went ahead and created mappings for the columns just to make sure that there would be no issues with reading the column names and types out. The editor for creating these is straightforward and is purely just a literal column name and a column type (int, varchar, datetime, etc.). Additionally, I also explicitly told Dodeca what the primary key for the table is (EMPLOYEE_ID).
The SelectSQL configuration is the exact same as before (when we we’re just reading data out of SQL), so nothing new to see there. What’s new is the configuration for the InsertSQL statement.

Let’s take a closer look at the exact configuration of the InsertSQL parameter, as it’s possibly one of the more interesting nuances in this whole configuration. The InsertSQL setting is ostensibly just the parameterized SQL code to insert a new row into the table, however, in this case we actually have two statements (one per line in the following screenshot):

The InsertSQL value for the employees query on the employee SQL passthrough dataset

The first statement is the parameterized INSERT. The full statement is INSERT INTO EMPLOYEES (FIRST_NAME, LAST_NAME, COMMENTS) VALUES (@FIRST_NAME, @LAST_NAME, @COMMENTS). I want to draw your attention to the fact that I am explicitly not mentioning the primary key (EMPLOYEE_ID) here. Recall that this is the primary key but also an AUTO_INCREMENT (similar to IDENTITY/sequence in SQL Server/Oracle respectively). I’m basically telling the relational database engine “Hey, I’m going to explicitly give you these three things, but you’re smart enough to figure out how to automatically generate the key for me, so please do that.”

Inside of the VALUES section of our insert statement, you’ll see that we have tokens starting with an @ symbol: @FIRST_NAME, @LAST_NAME, and @COMMENTS. When Dodeca goes to do the insert, it’ll dynamically place the values from the row into these placeholders and then execute the query. So to be clear, these aren’t part of the native SQL syntax. For instance, if I am inserting my own name and comments into the row and then have Dodeca save it, the resulting SQL statement that Dodeca generates and then hands off to the database for processing might look like this:

INSERT INTO EMPLOYEES (FIRST_NAME, LAST_NAME, COMMENTS) VALUES ('Jason', 'Jones', 'Awesome employee')

The next statement, and one that’s incredibly useful to our user experience, is the “post insert SQL” command. The code for this post insert command in this case and for this technology is the following:

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, COMMENTS FROM EMPLOYEES WHERE EMPLOYEE_ID = LAST_INSERT_ID()

Take special note that there is a semi-colon at the end of the first line that is separating the insert command from our special post-insert command. With respect to the post insert command, there are no special tokens in it, but it is specific to MySQL in this case. In particular, the LAST_INSERT_ID() function is a special function that returns the generated ID for the row that was just inserted in the previous statement. Effectively what I’m telling Dodeca is this: “After you insert the first name, last name, and comments to the relational database table, a primary key will have been generated. Here’s how you can use that generated primary key to fetch all of the details for that row, so that you can populate the key on my spreadsheet.”

Let’s go ahead and take a look at how this looks on the spreadsheet and the user experience. With my view all configured, let’s run it and take a look:

The Insert Row button on the toolbar is enabled when the cursor is inside a value input range

I apparently have two employees in this absolutely fictitious company. As you can see, there’s myself, and then there’s Cameron Lackpour. Apparently Cameron likes CALC ALL;. He also really likes load rules, low block density, and inputting to upper level members. But that’s neither here nor there. Anyway.

You can see in the spreadsheet that my cursor is located within the data table somewhere. Because of this, the Insert Row button is active. Take a look at the button toolbar and about in the middle you can see there are some table row-related icons. The Insert Row button is the third button to the left of the “100%” zoom indicator. I simply click on that to insert a new row to the table:

Finished entering data to be sent to the SQL database, but not saved yet

As you can see, I’ve added a new employee and comment. It’s Tim Tow and he apparently knows a thing or two about Excel. The row has not been sent to the database just yet. I will use the Save button on the toolbar (directly to the left of the 100% zoom indicator) to save this row.

Remember, behind the scenes, Dodeca knows that the value of FIRST_NAME is 'Tim', LAST_NAME is 'Tow', and COMMENTS is 'Excel ninja'. So it takes care of all of the ugly work of turning those raw inputs into a valid SQL query, using the statement we provided, talking to the relational database, getting the result back, and in this case, immediately executing the post-insert SQL statement. Immediately after pressing Save, our sheet looks like this:

New row saved and primary key value is automatically updated

It’s basically the same, save for one thing: the newly generated primary key for the row we entered has been pulled back (along with the rest of the new row) and dynamically updated into the view – with no refresh needed.

Conclusion

This was a very long article that covered aspects of architecture, Essbase anti-patterns, Cameron Lackpour’s love of load rules, and a real example of using Dodeca to write back to a relational table. There are many more nuances to the SQL data editing/updating that I’ll explore in future posts, such as updating existing rows, deleting rows, data grouping and more. But I wanted to give a practical crash course on the basics of this incredibly useful feature. Relational data input is an incredibly useful and important ability to have in so many organizations, and yet when the need for this type of capability arises on the Hyperion side of things in many organizations, all too often there isn’t a compelling, cohesive, and maintainable way to achieve it – but Dodeca does it.

July 19, 2016

Data Input with Dodeca, part 4 – Focused Calcs

Today’s article continues my series on data input with Dodeca. This post will be an elaboration on the basic data input to Essbase shown off in part 1. As a quick refresher, part 1 just looked at setting up a view that allows a user to input data to a given Essbase intersection. We made it a little more interesting by allowing the user to choose their Market (from our favorite database in the whole wide world, Sample/Basic). Now we want to take it a step further and run a calc script after the user inputs their data. This is pretty typical requirement because data in the cube often needs to be aggregated after lower level inputs.

Achieving this functionality is pretty straightforward. We also have some interesting possibilities because we aren’t limited to just running a static calc script on the server – we are afforded all of the normal Dodeca token replacement functionality so that we can focus the calc however we want. This can be incredibly advantageous for performance reasons. For example, rather than running a calc that refreshes all of the data across the cube, we can focus it on a particular cost center/region/functional unit based on the current POV. Why recalculate data that doesn’t need to be recalculated? Speed up the calc – speed up the user experience.

Cleaning up Anti-Patterns

This technique also let’s us cleanup an Essbase anti-pattern I have seen time and time again out in the real world. Imagine a company that has several managers that control different markets. For example, there are separate managers for New York, Washington, and California. Up until this point, the company has managed to get away with a process that involves doing a classic Essbase lock and send to the proper market, then choosing a calculation to run. The list of calculations might contain the following:

BdNewYrk
BdWash
BdCalifor

All of these calc scripts contain effectively the same script, differing only by that they FIX on. For example:

FIX ("New York", "Budget")
    CALC DIM ("Measures");
ENDFIX

The “run calc after data send” pattern in Dodeca lets us clean this up and consolidate down to a single calc that will simply plug in the POV from the user’s current Market selector. Let’s take a look at how to set this all up.

Introduction to Workbook Scripts

I’m going to leverage the exact same view as part 1 of the series, and simply add a Workbook Script to it. I’m going to get much, much deeper into workbook scripts in the future, but think of workbook scripts as the procedural side of Dodeca views. They are like a unique but approachable blend of Access macros and VBA functionality. Any view can have a workbook script attached to it. Inside of the workbook script, we can define sequences of procedures and attach them to particular events that can happen to our view.

In our case, what we want to have happen is that after the user submits data to Essbase (the AfterSheetSend event), we want to run a procedure that runs a calc script.

Tokenize the Calc Script

The very first thing we need to do is create the calc script that we want to run. This will be a normal server-side calc script, with a twist: replacing the market with a token. Here’s our script:

FIX ("[T.Market]", "Budget")
    CALC DIM ("Measures");
ENDFIX

Note that the market is replaced with a token, just like the tokens that are used on a normal Excel view. Also note that the token is enclosed in double-quotes. Dodeca will perform a full and literal token replacement. So we want to make sure that if the market is New York that it is put inside of the double quotes so we don’t end up with a syntax error. I’ll save the calc as BdMarket.

Create the Workbook Script

Now we head back over to Dodeca and create the workbook script. We can create a workbook script as with any other major object in Dodeca by simply navigating to Admin → Workbook Scripts, then selecting New. Nicely enough, the Workbook Script editor provides a rich environment where we can define most options and items by simply selecting them from a dropdown menu. Consider the following screenshot, showing everything we need:

Dodeca Workbook Script Editor

In particular, see in the Event Links pane that there is a definition that associates the AfterSheetSend event with the CalcMarket procedure. Next, look at the Procedures pane containing all of the procedures in this workbook script. There is just one, the CalcMarket procedure. In the workbook scripting world, there are many, many functions available to us to choose from. In Dodeca parlance, these are known as methods. For many methods within Dodeca, there are multiple versions of it available, these are known as Overloads. These terms are borrowed from the world of object-oriented programming. Think of the overloads as slightly different versions of a methods but with the same name.

In this current case, the method I’m using is the EssbaseRunCalc method. This particular method has several overloads available. These are General, TextBased, ServerBased, and DefaultCalc. Most use cases will probably be satisfied with TextBased or ServerBased. In the case of TextBased, we can define the entire calc script locally (inside of this Dodeca procedure) and run it on the server. With ServerBased, it’s a calc script that resides on the server, but we still get to perform token replacement on it.

I think what makes the most sense in this case is that we use a ServerBased calc script and include token replacement within it. Don’t be overwhelmed by the numerous options available to us. We can live with the defaults for just about everything. The only thing important that we need to specify is to tell it the name of the calc script (the ScriptName value), and to make sure that DoTokenReplacement is set to TRUE. These should hopefully be self-explanatory by now, but it’s worth pointing out that if we just wanted to run any given server calc script without worrying about tokens, we could just leave the token replacement value set to false.

With the workbook script created and saved, we now simply need to associate it to the view. This is set in the Workbook Script category:

Assigning a Workbook Script to a View in Dodeca

Lastly, after we change some data in the view and click on the Send button, we can go back out to our Essbase server and see what happened on the cube:

Viewing calc script execution results in EAS

You can see in the log that the current POV was used (New York) to replace into the script text, and the resulting script was executed. We can replace any number of tokens if need be, focusing the calc even more. This can frequently be a win for organizations with a wide/deep outline, and many forecasters that need to see aggregated data – but can’t wait for a more general calc to run. This technique can also frequently significantly streamline the technical side of things (fewer calc scripts) and the user experience (as compared to manual input with Excel spreadsheets). It can also potentially help you clean up your filter/calc security situation, in that you can let the user piggyback off their existing read-level access without having to dole out access to a particular calc script.

July 13, 2016

A primer on relational data views in Dodeca

I’m going to take a small detour from my series on data input in Dodeca so that I can lay the foundation for the next article. Lately I’ve talked about how we can get user input in Dodeca, how users can add comments to their input in Dodeca, and how we can audit the input data by tapping in to the Dodeca audit log tables. As a small preview of where the data input series is going, in the near future I’m going to look at how we can input data to a relational database from within Dodeca.

Prior to that, of course, I’m going to do a brief introduction to relational data in Dodeca. There are a handful of configuration items that need to occur. There’s a little more to it than just dropping in a SQL SELECT statement, but as you’ll see, there is a lot of power and flexibility that will be available to use with just a few clicks.

Define the SQL Connection

The first thing we need is to tell Dodeca about our SQL connection. This is about as standard as it sounds. It’s worth noting that Dodeca allows for an arbitrary number of SQL connections and supports a wide variety of databases, owing to the fact that the Dodeca middle-tier is written completely in Java. This means that, as with software such as Drillbridge, anything with a JDBC driver is fair game – including Oracle, Microsoft SQL Server, DB2, MySQL, and many others.

As with before in the Dodeca data input series, I am using a MySQL schema, since I like running my development instances son a lean and mean Linux VM:

Viewing SQL connections in Dodeca client

Note that SQL connections only need to be setup once and then used over and over again. You don’t need to redefine them every time you have a new view. Most organizations will have anywhere from one to a dozen or so different connections, many times to quite a variety of data sources that they are pulling together.

Create the SQL Passthru DataSet

Given a SQL connection that we want to query, we need to create a SQL Passthru DataSet (SPTDS). Try to think of think of this as a collection of SQL queries defined along with several configuration options. In other words, we’re not just dumping a SELECT statement into our view or system somewhere and ending up with an unmaintainable mess. For this simple example, when I create the SQL Passthrough DataSet, I’m configuring which SQL Connection (defined earlier) to use, and defining one or more queries associated with the data set. Note in this example I just have the one query I care about:

Dodeca SQL Passthrough DataSets editor

Add the Query to the DataSet

Now that I have my SQL Passthru DataSet created, I will add a query to it. The following editor is used to do this:

Query Editor window for a SQL Passthrough DataSet query

The main thing I am doing on this screen, clearly, is defining the query itself, which is accomplished by editing the definition for the SelectSQL property:

Editing actual query text in the query editor

This query is from my previous post on tapping in to the Dodeca audit log tables. Here’s the query for reference:

SELECT
    AUDITLOG.SERVER,
    AUDITLOG.APPLICATION,
    AUDITLOG.CUBE,
    AUDITLOG.USER_ID,
    AUDITLOG.CREATED_DATE,
    DP.MEMBER,
    DP.ALIAS,
    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;

Also note that there are a handful of configuration options relating to the primary key and columns. For this simple example I’m going to stay away from defining those since I don’t need them. In a future post I will go into what those options are and how they can be useful. The important thing to consider for now is that for the most part, Dodeca chooses sensible defaults for me and I can grab the functionality I need without having to worry about setting a million options first.

Create the View

Now I have my SQL connection, a SQL Passthru DataSet, and a query defined. This effectively takes care of all of the non-view specific functionality that I need. Put another way, nothing I defined so far was specific to the view that I’ll be creating in a moment. The objects created so far are all things that can and likely will be reused on other views, saving myself development effort down the road.

Now I want to create my simple view to show the data that I’ve modeled. For my purposes here, I can create a very simple view. Recall that I’ve created a SQLExcel view as opposed to the views I’ve shown earlier in this series that focused on Essbase (don’t worry, it’s possible to put Essbase and relational data on the same view – stay tuned for a future post on that).

For my SQL Excel view, I’m just going to define labels on my top row, apply some very light formatting (bold text), and then freeze the panes so that when I scroll down, my headers will be retained. I have also defined a named range that is as wide as the number of columns I have and is two rows tall. This named range is important because in a moment I am going to configure the view so that it knows to put the SQL data it retrieves there.

Dodeca SQLView Excel template

With the view template saved, I can now go over to the view editor and configure a few things so I can “glue” this view (so to speak) to the SQL data I defined earlier. The main property to consider is this SQLPassthroughDataSet Ranges category, which contains one item, DataSetRanges:

Upon editing it, I am presented with the DataSet Range Editor. All I have to do here is define my SQLPassthroughDataSetID to point to the dataset I defined earlier (helpfully, they are presented in a dropdown box so I just select it from a list), and then define a DataTableRange.

Dodeca DataSet Range Editor from Edit View screen

A Quick Note on Solution Architectures

Before going further, I want to step back for a moment and try to alleviate any qualms you might have in terms of the configuration we’ve done so far. If you’re feeling overwhelmed with all of these objects – SQL connections, SQL passthrough data sets, SQL queries, SQL data ranges – I can understand. You might be thinking “Why can’t I just drop in a SQL query and be done with it?”

Well, for a simple SQL Select example, that might seem simpler. But our solution is going to grow. And before long we’re going to want multiple SQL connections, queries, the ability to update rows, delete rows, sort data, group data, and more. And we’re going to have some absolutely incredibly power and flexibility in our hands – and it’ll be maintainable. We don’t want impenetrable walls of SQL code that breaks all the time, and this way of modeling things with connections/data sets/data ranges has been crafted incredibly carefully to offer performance, maintainability, and flexibility (just trust me).

Create the DataTableRange

In a lot of ways, the DataTableRange is where the magic happens. This is the last item we need to define before we can build our view. I don’t actually have to define much here in order to get things to work. I have to tell it where the data from the SQL query should go (my DataSheetRangeName, which corresponds to the defined name on the spreadsheet template), and a couple of other options. By default, the headers from the SQL query would come back along with the data, but I don’t want or need those in this case, because I put in my own “nice” headers on the template, so I can turn those off. This is the SetDataFlags option of NoColumnHeaders. Easy enough. You know what else I want? How about Filtering options that I know and love from Excel? Let’s turn that on with the click of a button by simply setting AutoFilteringEnabled to True.

Didn’t I just tell you that we would have some absolutely incredible power available to us with just a few clicks? That’s a prime example. No funky SQL code to write, no magic in the spreadsheet – just turn on that option and now I’ve got all of Excel’s powerful filtering abilities on any data set that comes back.

That’s all I want to configure for this data range for now. In total my options look like this:

Dodeca DataTable Range Editor screen

Build the View

We made it – we have our SQL connection, data set, and data range definition. Future views that use this data will be able to shortcut and jump right into the view definition since we’ll be able to reuse the objects we setup previously, saving us development effort. Time go go build the view:

A Dodeca SQLExcel view built with data from the internal Dodeca audit log tables

The data in this view should look familiar from the previous post on playing around with the Dodeca Audit Log. And again, note the filtering boxes in each header row, where I can, say, filter on the Member column in order to see only rows that were modified that involved Cola.

July 11, 2016

Data Input with Dodeca, part 3 – Data Audit Log

Welcome back to the Data Input with Dodeca blog series! We’ve already covered a good bit of ground already. To start things off, we looked at basic data input to an Essbase cube using Dodeca, then we looked at how to let users provide commentary on their Essbase data input. These are both incredibly useful features, but perhaps more importantly, form the cornerstone of many typical Dodeca applications.

Today I want to dive under the hood a bit and look at the Dodeca data audit log. Whenever data is input by a user, it’s logged. This one of the important legs of the data input stool (in addition to comments) and greatly complements data comments. Whereas data cell commentary might be thought of as being useful in a business context, the data audit log is probably more useful in an IT and SOX context. The rest of this article is going to focus on the technical details of the data audit log, while subsequent posts in this series will take a look at putting a friendlier face on it.

The Dodeca data audit log is comprised of three main tables that reside in the Dodeca repository itself. So there’s no additional setup to worry about – these tables exist out of the box.

Data Audit Log Tables

These tables are DATA_AUDIT_LOG, DATA_AUDIT_LOG_ITEMS, and DATA_AUDIT_LOG_DATAPOINTS.

DATA_AUDIT_LOG

The DATA_AUDIT_LOG table contains records of all the overall data input activities. A single data input operation may affect multiple cells of data; all of the affected cells of that are modified in a particular user action are grouped together. This table contains the audit log number (an integer primary key), the Dodeca tenant, the Essbase server/application/cube, the user, and the date the data was modified.

DATA_AUDIT_LOG_ITEMS

There are one or more audit log items associated to a single audit log. In other words, if the data audit log contains a list of transactions, then the audit log items are the list of cells (however many that my be) that were edited in that transaction. This table contains a unique ID (primary key), an association (foreign key) to the audit log table, the old value of the cell, and the new value of the cell. Note that it doesn’t not contain the members from the dimensions (that’s coming up next).

DATA_AUDIT_LOG_DATAPOINTS

The DATA_AUDIT_LOG_DATAPOINTS table contains the member names of cells that were modified. For example, consider our friend Sample/Basic. A sample intersection that was modified might be Sales, Budget, Jan, 100-10 (Cola), Washington. Each one of these would be represented as a single row in the data points table.

All Together, Now

The fully normalized format for storing modified data points tells us absolutely everything we want and need to know about data that is modified. We know the who (user), what (old value, new value), where (Dodeca app, Essbase app/cube, intersection), and when (created time). As for the why – that’s more of a commentary thing.

Given that we have all of this information, and given that it’s stored in a nice normalized form in a standard SQL database, we can query it and view/answer all manner of questions about the data. This opens up some very cool possibilities:

Query the database directly to see what changed, if anything
Setup an ETL process (ODI!) to provide a regular report of modified data (extra useful during the forecast cycle)
Build a view in Dodeca itself that will allow us to query the modified data using standard Dodeca selectors (coming to a future blog post)

For now, let’s take a look at some example queries to get an idea of what we’re working with. The Dodeca repository that I’m working with the moment will be a MySQL schema. MySQL is one of the many relational database technologies that Dodeca works with. The most common ones are Oracle, Microsoft SQL Server, and DB2. But I like my Dodeca servers on a nice compact Linux server, and MySQL fits the bill quite nicely. I’ve tried to write the SQL in the most generic way possible so that if you want to borrow it for your own repository it shouldn’t need any major modifications.

To start things off, let’s say we just want a list of all of the modified data, by user, by modification time, with all data points (this could potentially bring back a lot of data in a large repository, by the way):

SELECT
    AUDITLOG.SERVER,
    AUDITLOG.APPLICATION,
    AUDITLOG.CUBE,
    AUDITLOG.USER_ID,
    AUDITLOG.CREATED_DATE,
    DATAPOINTS.MEMBER,
    DATAPOINTS.ALIAS,
    IFNULL(ITEMS.OLD_VALUE, '#Missing') AS OLD_VALUE,
    ITEMS.NEW_VALUE
FROM
    DATA_AUDIT_LOG_DATAPOINTS DATAPOINTS,
    DATA_AUDIT_LOG_ITEMS ITEMS,
    DATA_AUDIT_LOG AUDITLOG
WHERE
    DATAPOINTS.AUDIT_LOG_ITEM_NUMBER = ITEMS.AUDIT_LOG_ITEM_NUMBER AND
    ITEMS.AUDIT_LOG_RECORD_NUMBER = AUDITLOG.AUDIT_LOG_RECORD_NUMBER
ORDER BY
    AUDITLOG.CREATED_DATE;

Note a couple of things:

Data is sorted by date, oldest to newest
There’s an inner join between the three tables, you must make sure that all tables are joined together
Data that was or became #Missing will be null in the table. For niceness I have used an IFNULL here to convert it to #Missing. Oracle’s equivalent is NVL. SQL Server uses COALESCE.
This table will contain one row per dimension per modified data point (as opposed to one row per modified cell).

Okay, that’s all well and good. How about we filter things a bit and we only want to see data points that were modified in the last 30 days? Just add a simple predicate:

SELECT
    AUDITLOG.SERVER,
    AUDITLOG.APPLICATION,
    AUDITLOG.CUBE,
    AUDITLOG.USER_ID,
    AUDITLOG.CREATED_DATE,
    DATAPOINTS.MEMBER,
    DATAPOINTS.ALIAS,
    IFNULL(ITEMS.OLD_VALUE, '#Missing') AS OLD_VALUE,
    ITEMS.NEW_VALUE
FROM
    DATA_AUDIT_LOG_DATAPOINTS DATAPOINTS,
    DATA_AUDIT_LOG_ITEMS ITEMS,
    DATA_AUDIT_LOG AUDITLOG
WHERE
    DATAPOINTS.AUDIT_LOG_ITEM_NUMBER = ITEMS.AUDIT_LOG_ITEM_NUMBER AND
    ITEMS.AUDIT_LOG_RECORD_NUMBER = AUDITLOG.AUDIT_LOG_RECORD_NUMBER AND
    AUDITLOG.CREATED_DATE BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
ORDER BY
    AUDITLOG.CREATED_DATE;

Please note that SQL languages differ wildly on their date math. I think the Oracle analogue here is relatively similar but SQL Server’s is a fair bit different.

Okay, how about if we’re only interested in a particular product being modified? Let’s filter on the member name/alias:

SELECT
    AUDITLOG.SERVER,
    AUDITLOG.APPLICATION,
    AUDITLOG.CUBE,
    AUDITLOG.USER_ID,
    AUDITLOG.CREATED_DATE,
    DATAPOINTS.MEMBER,
    DATAPOINTS.ALIAS,
    IFNULL(ITEMS.OLD_VALUE, '#Missing') AS OLD_VALUE,
    ITEMS.NEW_VALUE
FROM
    DATA_AUDIT_LOG_DATAPOINTS DATAPOINTS,
    DATA_AUDIT_LOG_ITEMS ITEMS,
    DATA_AUDIT_LOG AUDITLOG
WHERE
    DATAPOINTS.AUDIT_LOG_ITEM_NUMBER = ITEMS.AUDIT_LOG_ITEM_NUMBER AND
    ITEMS.AUDIT_LOG_RECORD_NUMBER = AUDITLOG.AUDIT_LOG_RECORD_NUMBER AND
    AUDITLOG.CREATED_DATE BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE() AND
    (MEMBER IN ('Cola') OR ALIAS IN ('Cola'))
ORDER BY
    AUDITLOG.CREATED_DATE;

Just to hit this home a bit, here’s a screenshot of the data that comes back for my local server, using one of my favorite SQL tools, RazorSQL:

Sample query on the Dodeca repository data audit log tables

As I mentioned earlier, one of the really interesting things we can do with Dodeca is to built a view in Dodeca itself that will allow us to easily filter and see what’s going on with the data, by tapping into Dodeca’s own repository. But in the meantime I hope you found this article helpful and saw some of the possibilities that are afforded to you. Invariably when I discuss this tool with people, there is a conversational progression of yes answers that lead to data audit logging:

Does it handle data input?

Does it handle data input comments?

Is there an audit log showing me which data was modified so that I can make my IT Risk/Compliance/SOX department happy, please say yes, please say yes?

Yes!

July 8, 2016

Data Input with Dodeca, part 2 – Comments

Yesterday, I kicked off my data input mini-series with Data Input with Dodeca, part 1. I’m going to take that example a small step further and put in comments that a user can edit as they add data input. Yesterday I also mentioned that in terms of data input to Essbase, you have several options, some of which include rolling your own in-house solution, such as with VBA (for the record, I recommend against rolling your own solution). It’s a lot of work.

But maybe you’re thinking: “You know what? Locking and sending isn’t so bad, we have a sheet we use…”. Fair enough. What about comments on the data? This question of comments and commentary comes up again and again – for good reason. It’s incredibly useful in the finance world to provide context to a data point, particularly when that data point appears out of the norm somehow.

Comments are a tentpole feature in Dodeca, and probably one of the biggest features in the product that goes to show its philosophy of being a best of breed tool for planning (with a lowercase P!), reporting, spreadsheets, and the best OLAP engine on the planet. Dodeca has extensive support for allowing commentary on any given cell. Today I’m going to talk about one of the simpler use cases for comments. I’ll do this by extending my example from yesterday so that in addition to allowing the user to input budget values for a given market, the user can now provide comments as well.

Setting up Comments in a View

The first thing we need to do is edit our Excel template to add cells for the comments themselves. You can see this in the following screenshot where I have enhanced the data input view from the previous article:

Dodeca input template comment range

Note that I have given the comments range a name, in this case Comments.Range.1. This will come into play in a moment when we configure the comments in the view. The next thing that I need to do is define key/value pairs for each comment. Essentially, the key/value pairs are where we use a particular cell to define a unique string of text that identifies a particular comment. As with so many other things in Dodeca, we define this in the cells/workbook itself. The simplest way to achieve this is with a formula that references cells containing members from the point of view (POV).

Excel formula showing the key/value associations for a comment

Check out the formula for the comment for the Sales item:

="Measure=" & B9 & ";" & "Market=" & C$5 & ";" & "Time=" & C$7 & ";" & "Product=" & C$6

This is just a normal Excel formula. The format that I want to achieve in this case is that I have a semi-colon delimited list of items that in the format Dimension=Member. So for the first cell, the resulting intersection is this:

Measure=Sales;Market=[T.Market];Time=Jan;Product=Cola

Because it’s just a normal Excel formula, when I fill down, the item for Measure will update based on the current row (after Sales will be COGS). Also note that in this case we just see the token [T.Market]. Remember that with Dodeca templates we often need to think a bit temporally, which is to say that we need to keep in mind that when the view is built by Dodeca, the token will be filled in with the user’s current selection for the Market dimension, and thus the formula and in turn the POV for the comment will be updated dynamically. Also note the absolute cell references in my formula. I want to make sure that when I fill down the correct cell references are maintained.

Before moving on, note just one more thing regarding the POV for our comments: we don’t need to match up with the Essbase dimensions. We typically will match up to some extent, but you don’t have to slavishly represent each dimension. For if there is, for example, a dimension that has no bearing on the comments, we don’t need to bother to represent it.

Since the comment key/value range is only meant for Dodeca to be able to determine what intersection the comments belong to, we don’t really want or need to show it to the user, so we simply hide that column on our sheet, giving us the following template:

The comment key/value associations are hidden so that users aren’t bothered with it

Now let’s go over to the view properties and tell Dodeca about the comment range in our view, so that it knows how to update and populate them. Under the options for our view, there is a Comments category with several options. In this simple case, we don’t really need to change any of them, except to go in to the CommentRanges item and define a specific comment range (Dodeca allows multiple comment ranges but for now we are just concerned with our one range).

Comments options in Essbase Excel view

Let’s take a look at the configuration needed for the comment range that we have been setting up in the template:

Main comment range configuration for Dodeca input template

Dodeca offers an incredible number of variations on the user comment experience and we can control most of that experience. For the moment, only consider the options in bold that I have specifically changed in order to make comments work on this sheet:

AllowDeleteString: True. I have specifically told Dodeca that I want to allow users to blank out a comment cell if they so choose, thus erasing the comment
InCellDisplayPolicy: MostRecent. Dodeca can track the comments for a given data point over time. In this simple case, I just want to show the most current comment
EditPolicy: EditInCell. Dodeca has a more featured comment explorer feature that I will get into in the future. For now we just want to edit the comments themselves in the cell
ThreadPolicy: OneCommentOnly. Again, there is quite a bit more enhanced functionality available here but I want to keep it simple
Address: Comment.Range.1. This address matches the defined name I have for the comments on the sheet
KeyItemsString: =OFFSET(@ACell(), 0, 1). This is probably the “trickiest” element to this entire configuration. In a nutshell, for a given comment range, we need to tell Dodeca about the cells that will contain the comments, and the cells that contain the POV for each comment individually. The formula in this cell represents a combination of an Excel formula along with a special Dodeca function @ACell(). The @ACell function returns the address of the current cell. Using the Excel OFFSET function, we can pass an address and a relative offset. In this case we are saying to offset by zero rows, and offset the column by 1. So this returns the value of the neighbor cell. If for whatever reason our comment POV cells were further to the right (such as one more column over), then I would need to increase this value to match.

Lastly, let’s run the view and see what happens:

The Market Input template as built by the user, with our new comment range

Now let’s enter some text in to explain the value for Colas:

Entering a comment to a cell

Given my input policy, the comment is sent up to the database right away. I can close and open this sheet and the comment will be loaded and shown. I can even develop other views and if I plugin the proper comment POV, I can show the comments on a totally different view. The comments are stored in the Dodeca relational repository (not as LROs or otherwise directly in the cube), which gives us fast access to them (and also explains why we don’t need to map every dimension from the cube if we don’t want to).

I hope this brief introduction to the comments functionality in Dodeca was useful and educational. Invariably when people (such as at the Kscope booth) ask about data input, the next question is whether they can get comments too. And the answer is yes; in fact, Dodeca makes it downright easy.

July 7, 2016

Data Input with Dodeca, part 1

I’m back from Kscope16 (recap coming soon!) and getting back into the swing of things. Needless to say, Kscope16 was another absolutely amazing conference and my three presentations all went pretty smoothly. While at the conference, I got to speak with a lot people about our products, what they do, and how they work. Along those lines, something that came up over and over again in one way or another was that many organizations are performing data input to Essbase using the classic Lock and Send technique or just using the Submit Data button on their Essbase toolbar.

From a purely technical perspective, this can work just fine. From a business perspective, there are numerous pain points. I’ve seen this play out at companies in a handful of ways:

Users have varying levels of Excel/Essbase skill. They are given pre-formatted Excel files and instructed to follow a very specific sequence to enter data into a sheet, connect to the Essbase server, select a range, press submit (or lock, then send), and perhaps run a calculation
Users are asked to fill in a template and email these to a power user or admin so they can properly load it into Essbase using their own template or process
Many hours are spent writing a mini-program in VBA and handing this file out to the users, wherein they are just asked for their username and password, but the automation otherwise hides all of the gory details of connecting and sending data to Essbase.

All three of these situations are rife with complications and things that can go wrong. Off the top of my head, note the following issues in play here:

Varying/extensive amounts of training are required to get users up to speed on how to use Excel, properly format data, and more
The power user/admin is burdened quarterbacking and marshaling significant amounts of data into the cube. This is often a highly paid individual whose time is better spent on other activities, such as development or system improvement
Resist the urge to spend significant amounts of time developing a custom VBA solution. It will always take longer than you think it will and it can quickly become a maintenance nightmare

That all said, one of Dodeca’s core features is the ability to handle data input from users. It excels at this in much the same way that data retrievals work, meaning that we can create an arbitrary spreadsheet to collect input, define what range(s) should be sent up to the server, using any Essbase connection we want, and then optionally performing some action, such as running a calc. For the rest of this post, I’ll be showing a very simple example of this to give you a feel for how this works.

I’m going to modify the simple template that I used for my blog series a couple of weeks ago. This template is based on the Sample/Basic database. In this example, I’m going to fix (hardcode) the product but allow the user to change the Market. This is a bit of a contrived example (normally we’d want the user to budget for multiple time periods) but it will demonstrate the basic functionality. I’ll expand on more complex examples in the near future.

To start things off, let’s take a look at our simple input template:

A simple Dodeca data input view template

In particular, note that I have used a normal selector [T.Market] to indicate that the market selected by the user should be plugged into the template. So you can think of data input in Dodeca as an elaboration on the normal Dodeca report/retrieve paradigm: we get to use the same spreadsheet/token/selector functionality as before, and simply extend it to send data back up.

In this particular example just to simplify things a bit, I have chosen to hardcode the product (Cola) and the time period (Jan). In a future example I will make those into tokens. As with before, I have decided that for visual/aesthetic reasons, I would like to take the current product and time period and use those values to put a “nice” title elsewhere in the sheet. This is accomplished with a simple Excel formula to concatenate the values using cell references, as shown here:

Dynamic template title using Excel formula

Now, one interesting difference in our input sheet versus our normal report sheet is that we are going to use an Excel formula to calculate data as it’s entered and provide the user some instant feedback on the values they are entering. Consider the following example, noting the formulas for Margin, Total Expenses, and Profit:

Regular Excel formulas dynamically calculate user-input as it is entered

If we were just creating a normal report of this data (meaning it was a retrieval, not a send), we probably wouldn’t have these formulas here, because the data would be coming directly out of Essbase. In the case of input, however, there is no harm in putting a formula here, and indeed, that’s exactly what we want. Remember, we get to leverage all of the power and expressiveness of Excel in our templates, so we can include just a normal Excel formula in these rollup rows (since the user won’t be entering a value for them anyway), and as the user enters data we can show them the dynamic total. This is a somewhat subtle nicety that I think is worth noting. Among other things, it obviates the need for the user to, say, enter a value, submit it, calculate the cube, retrieve, and see what their running total is. Again, remember, we are looking for that polished and intuitive user experience.

Given the way this template is constructed, we need to let Dodeca know what the range of cells is that contains data to be sent to the cube. This works exactly the same as it does for retrieval ranges, just with a slightly different name. Shown in the following screenshot is the special named range Ess.Send.Range.1 that let’s Dodeca know (in conjunction with our SendPolicy) where the Essbase data range is that should be sent to the cube.

With the template and view saved, I can now run it:

Our built input view showing current values for the Budget for this POV

Let’s see what happens when I type in a value for Marketing and hit enter:

Dodeca input view, shown with rest of Dodeca client for context

My Expenses value was dynamically updated via its Excel formula, and in turn, my profit value was updated.

Do you see the problem with this data? If you said, “Wait a second, you just increased your expenses but the profit went up, what gives?” – you’d be correct (and astute).

We aren’t limited to just addition or simple sums – again, we can use just about any Excel function that we want. In this case, a better formula to use would be one that subtracts the expenses from the margin and shows the value. I simply go back into my Excel view template, update the formula in cell C16 to actually be =C11-C15, save the template, and re-run it.

Now check out my dynamic totals:

Structure with Flexibility

I hope you enjoyed this simple example of user input directly to Essbase that is facilitated by Dodeca. In the coming weeks I’m going to show off some really interesting examples that are more involved, but I definitely wanted to start off with the basics. I think this is incredibly relevant due to the apparently huge number of people I have talked to (especially last week at Kscope) that have a very cumbersome input process fraught with Excel sheets flying back and forth via email, extensive user training, and sometimes performance issues. Even if I was the manager of a relatively small finance team (especially if including non-finance users), I would be looking for a tool that provided me enough structure to make the process streamlined and straightforward, while maintaining flexibility: adapting a process to my business rather than adapting my business to a process (or technology). In this regard, Dodeca delivers.

jason's hyperion blog

essbase from the trenches

Monthly Archives: July 2016