Relational Functionality of Dodeca 5.0 - How To, Part 1

In my last post about the relational functionality of Dodeca, I talked about why the relational integration is necessary and discussed some of the features.  In this post, I am going to walk through a simple how-to to show how easy it is to setup a relational view in Dodeca.  For my example, I am going to write a simple report from the new Dodeca Essbase Performance logging table that is part of the application.  In practice, however, you can use any relational data source that you can connect to via JDBC from our server.

There are four steps I need to follow to set up this relational view:

1.  Determine the SQL data you want to display, and then set up the corresponding SQL Connection and SQL Passthrough DataSet objects in Dodeca.
2.  Design a simple Excel template to display the data.
3.  Set up the view definition and any related objects.
4.  Deploy it on a View Hierarchy.

In this post, I will cover step 1 of the process.  For my relational view/report, I am going to use a table in the Dodeca metadata database that tracks Essbase performance.  Here is the SQL Query I created in SQL Server to get the data I want to display.

(click to enlarge)

To get access to the data in Dodeca, first I must create a SQL Connection object.  The SQL Connection object defines all of the parameters necessary to connect to the target relational database from the server and, as the server connects to the database using Java JDBC technology, the parameters are specified in JDBC format.  Below is a screenshot of the parameters in the Dodeca SQL Connection editor.  Note that the Username and Password are masked in the editor and are stored using industry-standard TripleDES encryption.

(click to enlarge)

The next step is to define a SQL Passthrough DataSet object in, of course, the SQL Passthrough DataSet editor.  This editor allows the Dodeca administrator to create new SQL DataSet objects.  Each SQL Passthrough DataSet  object may contain multiple Query objects which will be executed against the database in a single trip to the server.  In my example, I am going to create a single Query object the editor.  The SQL may optionally contain tokens which are string substitutions that are used a placeholders for user selections; in my case I did not choose to tokenize my SQL.  Further, if I were planning to allow users to write back to the relational tables, I could also provide insert, update and delete statements.  Here is a screenshot showing the SQL Passthrough DataSet definition including the SelectSQL property of the Query object.

(click to enlarge)

Once I have entered the SelectSQL and the SQLConnection property values, I can close the Query Editor and test it using, of course, the 'Test Data Set' button.  Here is what my test looks like:

(click to enlarge)

I have covered quite a bit in this post, so I will stop here for now.  In the next blog post, I will cover designing the Excel template for the Dodeca view/report.