Dodeca Architecture, Part 2

In this part of the series, I am going to focus on the Data Tier and, more specifically, what interactions Dodeca has on this tier. Just a word of warning though; this article gets a bit technical as it digs into the details of the how Dodeca uses the Data Tier.

Legal Notice: This post includes some details that are the subject of our pending patents.

There are two commonly accessed data servers in a typical Dodeca implementation:

• Essbase Servers
• Relational Servers

The data servers are accessed from the Middle Tier via TCP/IP. Many of our customers have multiple Essbase servers and multiple Relational Servers. We also have Dodeca customers running every version of Essbase from 6.5.7 through 11.1.1.3. The common version at this time seems to be 9.3.1. On the relational side, we have customers using Dodeca to access Oracle, SQL Server, DB2, Teradata and MySQL.

In Dodeca, the Essbase server is used, obviously, to get Essbase data. It is also used as a source to drive member selectors. Optionally, the Essbase server is used to authenticate system users and filter the views or reports available to a user.

All communication to Essbase is via the Essbase Java API calls from the middle tier. One primary reason we use that API is that it features the Essbase Grid API which is the special sauce in Essbase that sets it apart from its competitors. The Grid API enables both Dodeca and the classic Excel add-in to have the Query-By-Example functionality so popular with users. If you don't know what Essbase Query-by-Example is, here is a blog post series I wrote that explains it:

• How Does the Essbase Excel Add-in Work, Part 1
• How Does the Essbase Excel Add-in Work, Part 2
• How Does the Essbase Excel Add-in Work? (Part 3: Why Dodeca is Easier and Better)

Dodeca uses, by default, Essbase security to authenticate users and to control access to data and metadata. Another option in Dodeca allows administrators to map the views or reports available to users based on their Essbase group memberships. This makes it easy for customers who have built robust security models in Essbase to leverage their work in the user interface.

There are two primary uses for relational servers in Dodeca. First, all configuration settings for Dodeca are stored in a relational database. Further, many systems need some relational component for controlling the system and/or for providing information beyond the Essbase data.

The configuration settings for Dodeca are stored in a fairly simple schema. When I was architecting the schema, I initially designed a highly complex, highly normalized schema. Before I completed that work, I decided the complexity would be hard to manage, particularly when we wanted to build more functionality into our product. I tried looking at the problem from many different perspectives and had a great idea. I asked myself, “Why not store everything in one table?” I asked myself. In the end, this simplified metadata storage approach was the one we chose and, in fact, became the subject of one of our patent filings.

There are multiple benefits to Dodeca customers using this approach. The schema is very easy to understand as there are only few tables. The design also makes upgrades very easy as most upgrades do not change the structure of the metadata storage even as we add significant new functionality to the product. As such, some of our customers have done significant Dodeca upgrades in less than an hour. The simplified design also makes backup and migration extremely easy; you can backup an entire Dodeca application, or individual configuration settings, into a zip file in a couple of minutes. You can then migrate your application to a new server, say your production server, by restoring from the backup zip file.

Here is a listing of the tables in the schema as they appear in Oracle Enterprise Manager.


The Dodeca tables fall into a few categories. Those categories and their purpose are listed in the table below.

Artifact Storage	Stores all system configuration information.
Commentary	Stores commentary entered by users along with accompanying key structures and file attachments.
Usage Log	Logs usage of artifacts used by users.
Data Audit Log	Logs changes to Essbase data.
Metadata Audit Log	Logs changes to configuration data.

The key table in the schema is the BINARY_ARTIFACTS table. This single table is used to store all configuration information including view/report definitions, Excel templates, Essbase connection information and literally everything else a Dodeca administrator can configure. Here is a listing of the columns in the table as they appear in Oracle Enterprise Manager.

The table has four columns in the primary key. The ARTIFACT_TENANT code enables customers to have several separate Dodeca instances deployed in the same database. A customer may have an application for Finance and an application for Marketing that never share any reports. In this case, the tenant code enables customers to use the same Dodeca instance to serve both user communities.

The ARTIFACT_CATEGORY identifies the type of metadata stored in a particular row. For example, a row with the category ESSBASE_CONNECTION is, obviously, metadata related to a defined Essbase connection used in the Dodeca system.

The ARTIFACT_ID and ARTIFACT_VERSION column values complete the unique identification of the metadata within a row. As the name implies, Dodeca supports multiple versions of certain types of metadata. Multiple versions are most typically used with Excel spreadsheet template artifacts during development. This capability allows administrators/developers to store multiple versions of a spreadsheet template and, with a mouse click, rollback to a previous version.

The most important column in this table is the ENCODED_ARTIFACT column. This column stores the real metadata in a zipped, encoded string format. Though most configurations stored in this column are actually XML files, Excel files and extension modules written in VB.Net or C# are also stored this way in Dodeca. The CHECKSUM of the encoded artifact is also stored and is used as part of the bandwidth efficiency algorithms in the product. As a result, we believe Dodeca uses a fraction of the bandwidth consumed by other products in our space.

Here is a screenshot showing a few columns of data from the BINARY_ARTIFACTS table.



The first row contains an Essbase connection definition. In this case, the ENCODED_ARTIFACT column contains XML that describes an Essbase connection. The decoded XML from that record is shown below.


As you can see, the XML includes the normal information you need to connect to an Essbase database: server, application and database. All you have to provide is the username and password (and those items can optionally be stored in TripleDES encrypted format). It also has a value for the ServletPath. The ServletPath is the URL the Client Tier uses to connect to the Dodeca Essbase services running on the middle tier.

You may be asking yourself if you have to understand and edit XML files to configure Dodeca. Absolutely not! The administrator edits the metadata on the client tier in our metadata editor forms. The forms write the XML on the fly when the administrator saves their changes.

Another artifact category stored in the table represents a special type of metadata called a SQLPassthrough Dataset. This category of metadata configures SQL connections and SQL statements that are used in Dodeca to bring any relational data, whether it is part of the Hyperion stack or not, into Dodeca. For example, many of our customers drill-through directly from Essbase to their general ledger system. We are currently working on some enhanced functionality in this area. I will post a more detailed blog entry on the SQL Passthrough functionality once the enhanced functionality is released.

As you can see, Dodeca has some unique features on the Data Tier. In the next posting in this series, I move the focus to the Middle Tier.