Dodeca Data Audit Log Setup Part One

blog main pic dataAuditLog | Applied OLAP

All of the data that is submitted to Essbase using Dodeca is logged and auditable. It doesn’t matter if the data is sent to an ASO or BSO cube, it gets tracked. Sometimes it’s usefully or even necessary to know who updated a number and when it happened. In this series I’ll explain how to set this up.

Part One will focus on creating a SQL connection to the Dodeca Repository and a SQL Passthrough Dataset to return the correct audit log data.

Part Two will show how to set up the view to display the data.

Part Three will show how to create a right-click menu item that will automatically look up data audit log values based upon the intersection of an Essbase view.

Let’s get started by creating a SQL connection to the Dodeca Repository. This is a connection that might already exist in your environment. If it is, you can skip to the next section. Creating a connection to a SQL database can sometimes be tricky, involving a database driver installation. It’s unlikely you’d have to do that to access the Dodeca repository but if you do need assistance in this part don’t hesitate to send an email to Support@AppliedOLAP.com.

Creating the SQL Connection

  • Select Admin->SQL Connections.
image001 | Applied OLAP
  • Select “New” in the bottom left.
image002 | Applied OLAP
  • Add a name for the connection. In this case I’m calling the connection “Dodeca”.
image003 | Applied OLAP
  • Fill in the connection information. You might need to check with a Dodeca Admin or DBA for connection and driver information. In this case I’m connecting to my local SQL server instance.
image004 | Applied OLAP
  • Test the connection.
image005 | Applied OLAP
  • Hopefully the connection is ok.
image006 | Applied OLAP
  • Commit the connection. Now we can use this connection in our SQL Passthrough Datasets.
image007 | Applied OLAP

Data Audit Log Tables Overview

The next step is to create a SQL Passthrough Dataset that can return audit log information. Let me begin with a brief overview of the data audit log tables in the Dodeca repository.

The DATA_AUDIT_LOG table contains AUDIT_LOG_RECORD_NUMBER along with Tenant, Server, Essbase App/DB, user information and created date. The record number can be used to join to the DATA_AUDIT_LOG_ITEMS table.

image008 | Applied OLAP

The DATA_AUDIT_LOG_ITEMS table contains the AUDIT_LOG_RECORD_NUMER, before and after data values and the AUDIT_LOG_ITEM_NUMBER. The AUDIT_LOG_ITEM_NUMBER can be used to join to the DATA_AUDIT_LOG_DATAPOINTS table.

image009 | Applied OLAP

The DATA_AUDIT_LOG_DATAPOINTS table contains the Essbase dimension number, the Essbase member name and alias and the AUDIT_LOG_ITEM_NUMBER.

image010 | Applied OLAP

These three tables, used together, provide the ability look up any Essbase data value that is changed using Dodeca.

The following query, written for Sample.Basic, can be altered for nearly any Essbase database. Notice that the Products dimension is set to use an Alias rather than a member name. You can use this option for any dimension you’d like using that example.

query img | Applied OLAP

In order to customize this code for any Essbase database you’d need to do the following:

Lines 1-5: Change these to match your dimension names. Add additional lines for each dimension you have past the fifth one.

Lines 14-18: Change these to match your dimension names. Add additional lines for each dimension you have past the fifth one.

Lines 20-24: Change these to match your dimension names. Add additional lines for each dimension you have past the fifth one.

Lines 25-29: Change these to match your dimension names. Add additional lines for each dimension you have past the fifth one. Make sure they are in the order that they appear in your Essbase outline and number from 1 to n.

Lines 34-38: This is where the tokens will be set that specify the exact Essbase intersection to query. Customize this section using your own dimension names. Notice that the Products dimension is set up to look for a Member or Alias.


Creating the SQL Passthrough Dataset

Now that we’ve written our query, we can create a SQL Passthrough Dataset to be used in a Dodeca View. Here are the steps to do that.

 

  • Go to Admin->SQL Passthrough DataSets.
image011 | Applied OLAP
  • In the lower left, select “New”.
image012 1 | Applied OLAP
  • Choose an ID and Name for the SQLPTDS. In this case I’ve made them the same: Sample.Basic.AuditLog.
image013 | Applied OLAP
  • Click on the ellipsis for the Queries property.
image014 | Applied OLAP
  • Press the “Add” button to add a new query.
image015 | Applied OLAP
  • In the SQLConnectionID, choose the Dodeca connection.
image016 | Applied OLAP
  • In the DataTableName property, enter a shorter name like DTN1.
image017 | Applied OLAP
  • Click on the elipses for the SelectSQL property to open the query editor.
image018 | Applied OLAP
  • Enter the SQL query for the view in the editor then click on the “Edit TestTokens” button.
image019 | Applied OLAP
  • Enter a value for each token so you can test the query. Prior to this step you should have submitted some data to the Essbase cube for the member intersection you use here.
image020 | Applied OLAP
  • Press the “OK” button.
image021 | Applied OLAP
  • Press the “OK” button again.
image022 | Applied OLAP
  • Select “Test Data Set”.
image023 | Applied OLAP
  • You can now see the results of the query using our test tokens.
image024 | Applied OLAP

Commit the SQLPTDS to the Dodeca repository. You will now be able to use this in a View.

image025 | Applied OLAP

At this point we have our SQLPTDS created and ready to go. In Part Two I’ll show how to create a Dodeca View that displays Data Audit Log information.