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:
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.