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.