(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!

 

Cancel the Save Event in Dodeca

This is the third of three posts on using relational data with Dodeca, specifically on the Data Block Range that can be used with your SQL ranges in Dodeca.  It is another example of how Dodeca makes it easy for you to customize your Reporting, Planning and Analysis application to deliver the exact solution for you.

In the first post on the SQL DATA BLOCK range, we examined how it works with your SQL data range and how it can be utilized in your Dodeca Views.  In the second post, we added more functionality to perform validations on your SQL data.  In that example, we only provided feedback to the user.  We indicated that there was an issue with the data the user was trying to save.  However, we did not prevent the save action from occurring if the user went ahead and hit Save.  If the user did hit save with these Validation issues still applicable, they would still receive an error on the View.

In this post, we will go one step further and actually stop the Save process, and also provide a message as to why the event was cancelled.  Sometimes you really need to hit users over the head to get the message across.

So how will we do that?  One of the many reasons Dodeca is the best tool on the planet for your Planning and Reporting systems is the amount of control developers have with what happens in a Dodeca View.  The key to this control is what the Dodeca architect team refers to as the “event driven extensibility” of Dodeca.  This event driven extensibility is defined in Dodeca using what are called Workbook Scripts.

Do not let the term Script throw you though.  We do not actually write scripts filled with code.  That work has already been done by the Dodeca team.  They have already done all the heavy lifting.  We just need to define what we need to do, and then configure the workbook script to do it.

So here is what is happening in our example from the previous blog post.

  1. The user is attempting to perform an Insert.  Specifically, the user is attempting to Save a Data Set range, which will do the insert of the data to a relational table.
  2. We know the table is defined such that it does not allow null fields.  All the fields must have data in order to save the new row of data.
  3. Thus far, we have put a notification on the View to let the user know that all fields should be there before saving.
  4. If the user attempts to save anyway, the user will get an error.  We want to stop this error.

Here is our mission:

  1. If the user attempts to save while there are validation errors, we will stop the “save” process to avoid the error.
  2. We will present a message box to let the user know that the data cannot be saved, and why.

So here is where the Workbook scripts (WBS) enter the picture.  We will use a workbook script to accomplish our mission defined above.

Before we get into the WBS however, let’s create an easy way to indicate that there are Validation issues.  Recall from the previous post how we set a formula in column G to evaluate to TRUE if there is an issue.  We know where we need to check.  Let’s do two additional things on our template.  We will create a named range for the column G rows we need to check, and then use another cell to count the number of TRUE’s.

We will name the range in column G CheckFields:

Named range “CheckFields”. Where to check for problems

And add a formula to cell G2 to count the number of TRUE’s in the range.  And we will name this field ErrorCount:

Count the number of issues

Now we have an easy way to know if there are issues.  If the value in the ErrorCount cell is > 0.

Back to the Workbook Script.

Remember how I said the Dodeca team has already done all the heavy lifting?  Well, there are many things that can happen in a Dodeca view.  These are called Events (hence, event-driven extensibility), and Dodeca has already defined over 100 of these events for us to use.  Events are captured, and then Procedures are tied to the event to do get things done.  What event do you think we want to capture here?  That’s right, it is the Save event.  Specifically, it is the Save Data Set Range event.  And we want to capture the event *before* it happens.  So we will look for a Before Date Set Range Save event.

Let’s open the Workbook Script editor for this View.

The Workbook Script editor in Dodeca

There are four sections: Definition, Properties, Event Links and Procedures.  We are not concerned with the Definition or Properties sections today.

I can right click on “On Event…”, select New, and be presented with a list of the available events to use.  Scrolling down I find “BeforeDataSetRangeSave”.  Bingo.  That’s the one we want.

The event we want to capture

I can select that, then hit tab twice to automatically create a procedure called OnBeforeDataSetRangeSave.  The procedure name is automatically generated, but you could create one with another name for this if you want.

Now that I have the procedure, I need to specify the Methods within the Procedure.  The methods are the vehicles for getting things done.  We want to Cancel the Save event, so scrolling down I find the CancelEvent method:

Select the Method within the Procedure

Now to configure this event.  Here are the properties:

The CancelEvent method Properties

We only want this method to be in effect if there are validation errors.  And we know if there are validation errors if our ErrorCount field value is > 0.  So in the MethodCondition property we enter

=@ValueNumber(ErrorCount)>0

Also set the CancelProcedure property to TRUE

Setting the method properties

This method also provides a Property to enter the name of a procedure to run if the event is cancelled.  We can use this to display a message box informing the user of the cancelled event.  We will create a Procedure called ShowCancelMessage, and we can utilize the ShowMessageBox method there.  Let’s enter the message as:

“The Save operation has been cancelled due to Validation errors on the data. Please check the Validation messages and correct the data before saving.”

The ShowMessageBox method properties

The last bit of cleanup we need to do is reset the View status.  There is method for that, called, you guessed it, SetViewStatus.   The View status gets set to working when the Save button is clicked.  We want to reset this back to Ready.

Set the View Status back to Ready

That’s it.  One event, two procedures, and three methods used to get everything done.

Now when the user attempts to save date before it is ready to go, the Save will be cancelled, and the User will get a message as to why.  Let’s see it in action.  I have opened the View and entered some data.  It is incomplete however.  I try to save anyway:

The View showing Message box

I hit the okay button, and we are back to the View.  No errors.  Voila!

 

Validating SQL Data Before an Insert in Dodeca

This is the second of three posts on relational data in Dodeca.  The first post discussed the Data Block range and how it can be used to add functionality to your SQL Views in Dodeca.

This post will extend that idea by discussing how you can use Excel formulas in the Data Block range to test input data before saving data on a SQL View.

As in the last post, I will not go into detail on setting up the Data Set range, or the basics of setting up your SQL Views.  Please see the last post for links on those topics.

We are going to have a look at a SQL View in Dodeca of a simple employee table that allows the inserting of additional rows.  Aside from the ID which is auto-generated, there are only three fields in the table: LastName, FirstName and Position.

We would like to ensure all the fields are there before sending to the relational database.  Checking for blank fields is a rather simple validation, but the method discussed could be used for other validations as well, such as proper data types, valid codes, a proper numerical range, correct dates, etc.

Here is a look at the columns and data in the table called HR.

HR table columns and data

There are only four fields, and, as stated above, the ID column is auto generated on insert.  The important thing to know about this table is that all fields are required.  Nulls are not allowed in any field, so all fields must be entered in order to properly save the data.

We have a Dodeca View called “HR Update” to view this data, and also allows users to add to this data, i.e., perform an Insert to the table.  The DataSet range will allow Adds, but not updates or deletes.  Our SQL Passthrough dataset will have a Select Statement and an Insert Statement only, not a delete or update statement.

The Insert statement of the SQL Passthrough Dataset

Here is the View in Dodeca:

The HR Update View in Dodeca

Suppose there is a new entry to add to the table, Leslie Knope.  But the individual entering the data does not know what Position to enter so it is left blank.  When attempting to save the data, we get an error.  Oops.

HR Update View with error on Save

Clicking on the details button of the error notice we see:

Error message on Save attempt.

Hmmm.  This is the information returned from the relational database, and it is not exactly helpful.  I’m sure most users could figure out that all fields need to be entered in this simple example.  But if there were more advanced validations needed, wouldn’t it be better to somehow verify the data before sending it to the server?  Perhaps we could enter a message on the View before sending to indicate that something is missing or wrong.

We can do this by adding some Excel formulas since they are valid in a Dodeca View, and then include the formula cell in the Data Block range.  Recall when I wrote that Dodeca brings together the best of Essbase and relational data?  Well, add Excel to that mix as well.  It brings together the best of Essbase, relational data, AND Excel.

We will enter a formula in the View to check if any of the three required fields are blank, but we will only check this if one field has been entered.  If any of the three fields has been entered, check the other two for blanks.  We are checking each field individually using OR statements.  The formula we need can condense down to this:

=AND(OR(ISBLANK(C6),ISBLANK(D6),ISBLANK(E6)),OR(NOT(ISBLANK(C6)),NOT(ISBLANK(D6)),NOT(ISBLANK(E6))))

The formula looks a bit long-winded, but basically checks any of the three fields for blanks if any of the three fields has data.  If this resolves to TRUE then we have an issue.

For now, we are just going to inform the user if there is a potential issue with saving the data.  We will do this by presenting a Validation message on the View.  In the next blog post, we will actually stop the SQL save process if there are any Validation errors.  We will do this by adding a bit of workbook scripting.

Here is the current Data.Range:

The Data.Range named range in the View

It is three rows long to include the SQL Column names, and to include a blank line for inserts.

Now we will add the validation formula above to the template.  We will add it to cell G4.

Validation check field in the View

Let’s also add a formula to display a message if this field resolves to TRUE.  The formula will be entered in H4 and looks like this:

=IF(G4,”Please enter all fields before saving”,””)

Yes, we could have combined the two formula fields, but let’s just keep them separate for now.  We will also format cell H4 as bold and red.

Field to display message if there is a Validation issue

Now we name the range on the View as DataBlock.Range.

The DataBlock.Range defined range in View

We will hide column G in the View template since it does not need to be seen in the View.  And, as I highlighted in the last post, we include this range in the Data Table definition of the View.

The Data Table definition in the View

All set.  Now let’s run the View and try to add a new row of data.  We will only enter the first two fields.

Validation message displayed

The message gets displayed after the first field is entered.  It is there to notify the user of an issue.  It will only go away after all the fields are entered:

All fields entered, no validation message needed

And because the range follows all the fields, the validation message will appear on any and all lines that are missing fields:

Message appears only where appropriate

All set.  Our user now gets a helpful message before saving.  But the user might still go ahead and hit save, resulting in the same error message we saw earlier.  In the next blog post, we will add a bit of workbook scripting to stop the save event, adding even more control to the process and eliminating errors on the View.

 

Using a SQL Data Block Range in Dodeca

One of the many reasons why Dodeca is the best tool on the planet for working with Essbase is how easily it integrates with your relational data.  It allows you to bring together the domains of both Essbase and relational data to create the exact system you need.

Often when people hear the words “Essbase” and “relational data” together they think of drill-through.  And yes, Dodeca makes it extremely easy to drill through to the relational source of your Essbase data.  But that is just tip of the iceberg.  Dodeca also provides an easy interface for WRITING back to your relational data systems.  This creates many more possibilities for your Planning, Reporting and Analysis systems that use Essbase.

There is some great information on the web describing the use of Relational data within Dodeca.  There are the Applied OLAP videos on their YouTube channel.  There are also some great blog posts by Tim Tow, Jason Jones, and others on this topic.

Here are some of great sources of SQL in Dodeca information:

Applied OLAP YouTube videos on Relational data in Dodeca

Jason Jones’ Primer on Relational Data Views in Dodeca

Tim Tow’s Fundamentals of SQL Writeback

Relational Drill-Through in Dodeca by Cameron Lackpour

Jason’s Relational Data Input in Dodeca

Jason’s Post on Dynamic Relational Grouping in Dodeca

Please note:  The post by Jason Jones on Relational Data Input does an excellent job of highlighting the game-changing nature of the ability to combine Essbase with relational data.  If this is an interest to you, and you haven’t read it already, I would urge you to have a look.

Into these waters I wade.  This blog will attempt to add to this fine collection of information with three posts on using SQL in Dodeca.  The first is a quick post on the SQL Data Block range.  The second will be a post on extending the functionality of the Data Block Range to validate SQL data before doing an insert.  The third will take a look using some Workbook scripting to actually cancel an SQL insert event if there are any validation issues.

Also note, I will not discuss the basics of setting up a SQL View in Dodeca.  Those basics are well covered in the information links above.

The SQL Data Block Range

The DataBlockSheetRangeName property

What exactly is a SQL Data Block Range?

There is a property in the DataTable Range Editor called DataBlockSheetRangeName.   In the editor, in the help section, the property is described as:

The name of an optional sheet range that contains the data range and additional columns that contain formulas that refer to cells within the data range.  The DataBlockSheetRangeName must be the name of a sheet-level defined range.

The data block range is used to define formulas within cells on a data row that refer to cells within the data range on the same row.  When the range is built and when rows are added, inserted, or deleted, the columns within the data block are maintained along with the data range. 

Simply put, this range defines formulas that can tag along with each row of data in the SQL data range.

For example, if you are retrieving monthly data in your SQL data range, you could sum the months into quarter and year values using formulas in the data block range.  If you brought back 3 rows of data or 3000 rows, the data block would follow along with the data range and calculate values for as many rows of data exist.

The data block range is an extension of the data range.  It contains the data range, plus additional columns.  See figures below.  The first is the sheet defined Data.Range and the second is the defined DataBlock.Range.  These are defined on the View Template in the View Template Designer.

The Data.Range defined on the View template
The DataBlock.Range defined on the View Template

You can see that the DataBlock.Range actually contains the Data.Range.

The SQL Passthrough Dataset for this View retrieves data for the first three months of the year.  These values will be entered in columns B, C, and D, as defined in the data range.  Column E is part of the data block and contains a formula to sum the months.

Formula defined within the DataBlock range

When the View is opened, we see that the data is retrieved, and the formula is there to sum the months for all the rows that are returned.

The View with SQL data returned, and with the DataBlock range to sum the data.

That’s it.  Using a Data Block range with your relational data in Dodeca comes in very handy.  In the next post, we will discuss using the Data Block range to do something a bit more sophisticated that adding columns together.