(Re)Using the Sample Audit Log View

Dodeca tracks every update made to your Essbase database automatically.  It keeps track of who made the update, when it was made, what the dimension intersection was, and the old and new values.  It stores this information very efficiently in three tables in the Dodeca repository:

  • DATA_AUDIT_LOG
  • DATA_AUDIT_LOG_DATAPOINTS
  • DATA_AUDIT_LOG_ITEMS

This is a terrific resource for organizations to use for understanding the who/what/when and where of the updates to their cubes.

You can query these tables directly and view the information in a Dodeca View.

The Sample application that is shipped with Dodeca has an example of this.  The View titled “Audit Log Search” and can be seen in the Hierarchy under Input:

The Dodeca Sample application

Once you open the View, you can use the selectors to identify the dimensional intersection and see the log of the updates:

The Sample Audit Log View

Do you have a View in Dodeca which is sending data back to the cube and do you want to see information on these updates?  If so, this post may serve to demonstrate a shortcut to setting up your audit log view.

In this blog post, I am going to show you how you can use the existing View from the Sample application to create an Audit Log specific for your environment.  Here are the steps we will follow:

  1. Export the metadata for the Audit Log Search view from the Sample application
  2. Import the metadata into my target Dodeca tenant, called FINOPS
  3. Update the metadata in the FINOPS tenant:
    1. Update the View tokens on the View to reflect the correct tokens for my application
    2. Update the SQL passthrough dataset for my specific essbase cube
    3. Update the View Selectors to match the dimensions in my cube.
    4. Update the View template and Appearance property for the correct dimensions
  4. Test!

Step 1:  Export the metadata for the Audit Log Search view from the Sample application

In the Sample application, use the Admin menu item and select “Export Metadata to Local Zip File…”

Export the metadata

When the list of metadata appears, click on the ID column to sort by name:

The list of metatdata objects, sorted by ID

Then scroll down to the Audit Log metadata objects.  We need the following four objects:

1.       DAL_Datapoint_SampleBasic – SQL PTDS

2.       DataAuditLogDatapoint_SampleBasic – General

3.       DataAuditLogDatapoint_SampleBasic – View

4.       DataAuditLogDatapoint_SampleBasic – WorkbookScript

Export these objects and save them to a file that we can access when we import them into the FINOPS application.  I saved them to a file called DALSample.zip on my desktop.

Select the objects for exporting

Step 2. Import the metadata into my target Dodeca tenant.

Now I go to my FINOPS application.  This application currently has an Essbase connection to my cube, imported dimensions from the cube, and a SQL connection to the Dodeca repository.  I currently only have one View in the hierarchy called Budget Input:

To import the Audit log metadata, go the Admin menu item and this time select “Import Metadata from Local Zip File…”  Navigate to the file, then select all items for input.  Hint:  you can simple check the box at the top to select all the items.

Select all objects to import

Once imported, there is no visible change in the application console because we haven’t added the View to Hierarchy yet.  If we navigate to Admin -> Views, we will see it there.

The View metatdata editor

An important note:  We imported 4 objects that have specific ID’s and Names.  The Names of these objects can be updated, but the ID’s cannot.  For example, the View listed above has the Name “Audit Log Search” and an ID of “DataAuditLogDatapoint_SampleBasic”.  For the purpose of this blogpost, we will simply keep these ID’s and update the names.  It we wanted the change the ID’s of the objects to better reflect the cube used, we could copy the objects to new ID’s and delete the originals.

Step 3:  Update the metadata in the FINOPS tenant

Step 3-1.  Update the tokens on the View to reflect the correct tokens in my application

While staying in the View metadata editor, click on the plus sign next to Tokens.  You will see there are 4 View tokens.  Click on the ellipsis to view and edit these tokens:

The View tokens

We see the four tokens and their current definition.  They are defined for the Sample application in my environment.

The original token definitions

We want to update them to use a new Tenant, Application, and Cube.  The Server token definition will remain the same.

Updated View token definitions

Step 3-2.  Update the SQL Passthrough Dataset for the specific essbase cube.

We imported a SQL Passthrough Dataset which retrieves the data from the Audit Log tables in the Dodeca repository.  But that SQL PTDS is specific for the dimensions defined in the Sample Basic cube.  We need to update the SQL PTDS for the cube used in this application.

There are tokens in the SQL PTDS that reflect the dimensions in the Essbase cube that is referenced.  The Sample Basic cube has 5 dimensions, and the PStores Plan cube has 6 dimensions.  We need to update the SQL PTDS for the dimensions in the cube we are tracking.

Navigate the SQL PTDS metadata editor via Admin ->  SQL Passthrough Datasets.  There is only one there, with the name DAL_Datapoint_SampleBasic.  (As noted earlier, we can update the name, not the ID).

The SQL Passthrough Dataset editor

Let’s rename it to DAL_Datapoint_PStoresPlan, then click on the ellipsis next to “1 query defined”:

Click to open Query Editor

Next click on the ellipsis next to SelectSQL.

The Query Editor

This will open the editor for our Select statement.

The original Select statement

We can see that there are 5 tokens listed in the middle of the statement that reflect the 5 dimensions in the Sample Basic cube.  We are going to update these to reflect the 6 dimensions in out Plan cube.  The six dimensions are

1.       Departments

2.       Location

3.       Measures

4.       Periods

5.       Scenario

6.       Years

We will also update the HAVING (COUNT(*) statement from 5 to 6.  See below:

The updated Select statement

All done with the SQL PTDS.

Step 3-3.  Update the View Selectors

We will open the View metadata editor again and look at the View Selectors:

The original View Selectors

There are 5 Selectors, but they match the dimensions from Sample Basic.  I can click on the ellipsis to update the Selectors for the 6 dimensions already defined in the application.

There is another step we need to complete here.  Since this is by nature a SQL View and not an Essbase View, we need to update the Connection Policy to “UseSpecifiedConnection” and then update the Connection Settings column.  Use the dropdown on the Connection Policy column to update to “UseSpecifiedConnection”, and then click on the cell in the Connection Settings to update the “EssbaseConnectionID” setting, and the EssbaseLoginServiceObjectTypeID setting for each Selector.

Updated Connection Settings

Step 3-4:  Update the Appearance property and the View Template.

While still in the View metadata editor, I want to update the Appearance property “CaptionAfterBuild”.  This is currently set to reflect the original tokens:

The Appearance Properties

I will update it for our current tokens:

Updated Property

The last step is to update the template, and this is again for, you guessed it, the correct tokens.  From the View metadata editor, click on View Template Designer.

Click on View Template Designer

Then update the tokens listed on line 4 of the template:

Update the tokens listed on the template

All done.  Now we are ready to test.

I can Preview the View from the View Metadata editor.  Once opened, I can use the selectors to specify the dimensional intersection where I want to audit the updates.

The completed Audit Log View

And it works!  If you like, you can add this view to the View Hierarchy, and you can also drill to this from the Budget Input View as described in this post here.

Summary.

Reusing the Audit log metadata from the Sample application saved us from creating the View and writing the complex SQL statement from scratch.  There was still a bit of configuration to do, but we saved a good amount of time.  Please let me know if you have any questions!

 

Adding a Right-Click Popup Menu in Dodeca

A pop-up menu is a convenient way to add functionality to your Dodeca View.  One way it is often used is to navigate to another view while bringing all the context relevant information with it from the original view.  Dodeca makes this very easy because it automatically captures all of this contextual information.

For example, if you are in a planning view, or any view where information is sent to an Essbase cube, Dodeca already knows all the Dimensions defined for a particular cell.  This makes it very easy to open another View with all of your relevant dimension intersections already defined.

In this post, we will add a pop-up menu to a View which will open the Audit Log Search view.  The Audit Log Search view provides information from the Dodeca repository on a single data point within your application, such as the old value, new value, who updated it, and when.  There is a sample provided with the Sample application.  This can be used with your application once it is modified to fit your specific environment.

Here is a look at the Sales and Margin Planning view within a sample application I have called OUTLOOK.  This view is used to update the Sales numbers and Margin % across products for a specific market.

Sales and Margin Planning View

Right now, there is no pop-up menu associated with the View.  If I were to right-click anywhere in the view, nothing would happen.

This application also has an Audit Log Search view which was mentioned above.

The Audit Log Search view listed in the View Hierarchy

Here is look at the Audit Log Search View:

The Audit Log Search View

As you can see, for a particular data point in the cube, the view captures old and new values, who made the update, and when.

We will add a pop-up menu to this view which will open the Audit Log search View when we click on any input data cell in the Sales and Margin Planning view.  The Audit view will open for the exact data point that we have highlighted on the Planning view, automatically!  This makes it very easy for any user to get information on any updates made in the application.

Here are the steps we will follow.

1.       Create a PopUp menu item called “Audit Log PopUp”

2.       Create and Configure Button tool called “Open Audit Log”

3.       Assign the Open Audit Log button tool to the Audit Log Popup.

4.       Assign the new PopUp menu to the View.

Most of these actions take place within the Toolbars Configuration metadata editor.  Please note: this metadata editor was also discussed in this post on Adding a Button to a Toolbar.

The source view, “Sales and Margin Planning” has a View toolbar called “Essbase View Standard Limited” .  You can see this in the View Properties under the UI category.  We will edit this toolbar directly, as opposed to creating a new one as we did in the other toolbar post.

The View Properties

An easy way to open the Toolbar Configuration metadata editor for this particular view simply to right click where the Property name, and select Edit “Essbase View Standard Limited”.

Opening the Toolbar Configurations metadata editor

This will bring you to the Toolbar Configurations metadata editor for this specific toolbar configuration.

The Toolbar Configurations metadata editor

Step 1.  Create the popup menu

Click on the Toolbars Designer on the bottom left section of the editor.  This will bring up the Toolbars Designer for this toolbar.  It has five tabs across the top.

The Toolbars Designer with 5 tabs across the top

Go to the Tools tab, then select New… at the bottom left.

Select the Tools tab, then click on New…

This will open the New Tool dialog box.  Select “Popup Menu” from the dropdown, then enter Audit Log Popup for the caption, and AuditLogPopup for the key, then press Add.  I like to take spaces out of the key field just as a preference.  We do not need to select a Category assignment at this time.  This dialog will stay open, so click on close when completed.

Adding a new tool

Step 1 completed.  We now have a new Popup menu item.

Step 2. Create a button called “Open Audit Log”

Click on New… again to open the New Tool dialog.  Select Button from the drop-down, and enter Open Audit Log for the Caption, and OpenAuditLog for the Key.  Press Add.

Adding the second new tool, the Open Audit Log button

Then close the Toolbars Designer.  We now have our button tool, but we need to specify what the button will do.  For this we open the Configure Tools dialog.  To open this, click on the Configure Tools… button in the Toolbars metadata editor.

Click to open the Configure Tools window

The Configure Tools window will list all the tools we have, listed by Key.  Select the new one we created called “OpenAuditLog”.

Select the new tool – OpenAuditLog

The right side of this window is where we will specify what the button does.  In the Tool Controller dropdown, select OpenViewForDataCellToolController.  This will then populate the Tool Arguments window.  Open the ViewID argument by clicking on the ellipsis on the right of the entry field.  This will open the list of Views in the application.

Select from the list of Views in the application

I will select the Audit Log view, which in this case has a view ID of the original view from the Sample application.  That’s all we need to do before committing.  Click on the “Commit Module, Toolbars Configuration, and/or Views” at the bottom of this dialog.  You will get a confirmation message, and also a message that you will need to restart the application.  We will not restart it just yet, as there are a few more updates we can make before restarting.

Step 3.  Assign the Open Audit Log button tool to the Audit Log Popup.

Open the Toolbars Designer window again and go to the Popup Menu Designer tab.  Select our “Audit Log Popup” from the dropdown list of available Popup menus.  Then select our Open Audit Log button tool from the list of tools on the right and drag it over to the left box.  Now the button tool is available in the popup menu.

Adding the button to the Popup menu

Close the window and click on the Commit button in the Toolbars Configuration editor.

Step 4.  Assign the new Popup menu to the View.

This is assigned in the Views metadata editor.  Open this editor for the Sales and Margin Planning view.  There is a property in the UI category named GridContextMenuID.  Click on the dropdown for this property and select our new popup called “AuditLogPopup”.  Then click on Commit.

Final step, add the popup menu to the View

That’s it.  All the updates are completed.  Now we can close and restart the application.

Now let’s test.  Go the Sales and Margin Planning view.  We will make an update and then test the popup.

The Sales and Margin Planning View before update

I will update the Sales number for Birch Beer from 480 to 700.  I will update this to the cube with the Send button on the top of the view.  Once the send is completed, I will right-click on the data cell:

Right click on the data cell to see the Popup menu

I see the Popup.  So far so good.  Now I can click on “Open Audit Log”

The Audit Log opens to the exact data point

Excellent!  Tested and verified.  I see the Audit Log view has opened and I see the update I just made.

Some key points:

  1. Dodeca automatically provides the auditing tracking.  Every update sent to the cube is tracked in the Dodeca SQL repository that comes with Dodeca.
  2. Dodeca KNOWS what cell you are on when you right click, and will automatically use that information when opening another view.  No matter what cell I right-clicked on, Dodeca knows to get the Audit information for that exact data point.
  3. We already had a working view for the Audit Log search.  So we simply needed a button to open that view the button.  I may to another post on setting up the Audit Log View.

Although there were a number of steps involved, I hope you can see that is was a relatively straightforward process to put this together.  Please let me know if you have any questions, and thanks for reading.