Getting back to SQL - Copying Dodeca commentary

I have been working on a new feature in Dodeca that relies heavily on SQL processing on the server, so I had to brush up on some of the SQL skills that I had not used much since I started with Essbase 16 years ago. Only a few of my friends know that, before focusing exclusively on Essbase, I worked on an Oracle Financials project at the last 'real' job I had at Intergraph here in Huntsville, AL. I got pretty good at SQL in that position and even architected and implemented an entire royalty processing module that bolted onto Oracle Financials Inventory, interfaced with the Oracle General Ledger package, and was fed by a Microsoft Access driven user interface that talked directly to the Oracle tables (back in 1994!)

The SQL knowledge came in handy when I started working on the Dodeca feature which will allow users to copy comments from one set of key combinations to another. We have had a number of requests for this functionality over the years and I have read many requests from frustrated Hyperion Planning users who desperately needed that functionality.

In our case, we store commentary in a relational table on the Dodeca server. The comments table has the COMMENT_TENANT and the COMMENT_ID fields as the primary key. The tenant code is a unique identifier for a Dodeca application. The comment ID is a GUID (Guaranteed Unique ID) obtained from the operating system. Here is a screenshot of the COMMENTS table.

The key/value pairs that identify the datapoint, or partial datapoint, that identify the comment are stored in a separate table that has a one-to-many relationship. Here is a screenshot of the COMMENT_KEY_ITEMS table.

The SQL problem I needed to solve was ‘How do I get a list of comment ID’s that have, for example, both the Scenario equal to Budget and the Year equal to Apr? My initial thoughts focused on the fact that no matter how I wrote the where clause, I would still get two rows back if my query returned the rows that I wanted. Myopia nearly got the best of me until I remembered the SQL GROUP BY clause. Using the GROUP BY clause, I could flatten the two rows into one. I still needed to make sure the original query, however, returned two rows, so the SQL HAVING clause did exactly what I wanted.

The next complication is that we use Hibernate Object-to-Relational Mapping technology in our server in order to support many relational data sources. This technology handles the translation of Plain Old Java Objects (POJOs) to, and from, relational sources. So, I had to write my Hibernate Query Language, or HQL, using GROUP BY and HAVING clauses:

select c.CommentId 
from Comment c join c.keyItems i 
where c.Tenant = 'SAMPLE' 
and (i.Key = 'Scenario' and i.Value = 'Budget')
or (i.Key = 'Year' and i.Value = 'Apr') 
group by c.CommentId 
having count(c.CommentId) = 2

Of course, in my code this HQL is created on the fly based on tenant code and the variable number of key/value pairs passed into the routine. Hibernate, in turn, writes the SQL. Here is an example of the SQL in SQL Server syntax:

Select comment0_.COMMENT_ID as col_0_0_
from COMMENTS comment0_ 
inner join COMMENT_KEY_ITEMS keyitems1_
on comment0_.COMMENT_TENANT=keyitems1_.COMMENT_TENANT
and comment0_.COMMENT_ID=keyitems1_.COMMENT_ID
where comment0_.COMMENT_TENANT='SAMPLE'
and keyitems1_.KEY_ITEM_KEY='Scenario'
and keyitems1_.KEY_ITEM_VALUE='Budget'
or keyitems1_.KEY_ITEM_KEY='Year'
and keyitems1_.KEY_ITEM_VALUE='Apr'
group by comment0_.COMMENT_ID
having count(comment0_.COMMENT_ID)=2

This SQL gave me the comment ID’s I needed to copy which was just the start. To complete the project, I also had to update the comment ID with a new Java UUID, update the related KeyItem records with the new UUID, recompute the COMMENT_KEY_HASH that is used to reduce the network traffic and required server resources by allowing for a query filter based on a single column value instead of a one-to-many join, and track/update the comment ID’s in the PARENT_ID field which is used to track threaded comments.

The code is now complete and will be checked into our source code control system today. QA can then get started with their testing so this feature can be released in the next Dodeca release.