I am happy to report that I have joined the team at Wysent, Inc. I’ve known Kevin Corlett for quite a few years now, and the group he is putting together over there is a phenomenal team, with great technical expertise focused on providing world class service to clients. Look for new Dodeca tips and tricks on the Wysent website. I’m very happy to be part of the company!
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:
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:
Once you open the View, you can use the selectors to identify the dimensional intersection and see the log of the updates:
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:
- Export the metadata for the Audit Log Search view from the Sample application
- Import the metadata into my target Dodeca tenant, called FINOPS
- Update the metadata in the FINOPS tenant:
- Update the View tokens on the View to reflect the correct tokens for my application
- Update the SQL passthrough dataset for my specific essbase cube
- Update the View Selectors to match the dimensions in my cube.
- Update the View template and Appearance property for the correct dimensions
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…”
When the list of metadata appears, click on the ID column to sort by name:
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.
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.
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.
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:
We see the four tokens and their current definition. They are defined for the Sample application in my environment.
We want to update them to use a new Tenant, Application, and Cube. The Server token definition will remain the same.
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).
Let’s rename it to DAL_Datapoint_PStoresPlan, then click on the ellipsis next to “1 query defined”:
Next click on the ellipsis next to SelectSQL.
This will open the editor for our 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
We will also update the HAVING (COUNT(*) statement from 5 to 6. See below:
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:
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.
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:
I will update it for our current tokens:
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.
Then update the tokens listed on line 4 of 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.
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.
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!
Did you know you could work offline with a Dodeca application to update your Essbase data?
You can export an excel file, send it to someone for updates, then import that file back into Dodeca and then update your data from the file. This comes in handy if someone who does not have access to Dodeca, or even access to Essbase.
Yes, it is true that people have been sending data to Essbase from Excel spreadsheets for as long as Essbase has been around. But this does add another layer of flexibility and convenience to your applications.
You can even update your relational data using this method. Dodeca provides read/write access to your relational sources as well as Essbase. There are many Dodeca applications that access and update Essbase data AND relational data in a single View.
Would you like to have the ability to send a spreadsheet to a remote user who does not have access to either Essbase or to your relational data, and have them make updates to both Essbase and your SQL data?
You can with Dodeca. This is just another in a long list of reasons why we say Dodeca Does It!
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.
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.
Here is look at 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.
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”.
This will bring you to the Toolbar Configurations metadata editor for this specific toolbar configuration.
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.
Go to the Tools tab, then select New… at the bottom left.
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.
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.
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.
The Configure Tools window will list all the tools we have, listed by Key. Select the new one we created called “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.
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.
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.
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.
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:
I see the Popup. So far so good. Now I can click on “Open Audit Log”
Excellent! Tested and verified. I see the Audit Log view has opened and I see the update I just made.
Some key points:
- Dodeca automatically provides the auditing tracking. Every update sent to the cube is tracked in the Dodeca SQL repository that comes with Dodeca.
- 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.
- 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.
First things first. Dodeca does not come with built in workflow or process management.
But what you CAN do is build process management, or workflow, into your Dodeca application. And you can build it the way you want it, and it can be done relatively easily. The key to this is that Dodeca is really an Application Platform for building the exact application that meets your requirements.
It is powerful and flexible. Powerful because there is an incredible amount of functionality that can be added to your application. Flexible because the functionality can be adapted to specific users and specific situations.
In this post, we will have a look at a sample application called Outlook. This is a company forecast application that is used multiple times throughout the year. It uses a version of the Sample Basic Essbase application.
This is going to be a basic example of Process Management that only involves the Submission and the Approval or Rejection of the entire scenario. We will explore the use of Planning Unit Hierarchies in a later post.
We will continue with the Planner and Reviewer roles that were demonstrated in those posts. As in the other post, I am using Andy Dwyer (userid = dwyera) as the test Planner, and Leslie Knope (userid = knopel) as the test Reviewer.
We will also use a relational table to hold information about the scenario status. It is one table structured like this:
So, we have our Essbase application, Dodeca and a single SQL table. With only these ingredients, we can demonstrate a basic forecasting submission workflow.
As shown in this previous post, we have assigned View Hierarchies based on Roles. Only users with the role of Planner will have access to the Views that allow data input. Here are the Input Views:
Roles are important in this process. Here are the specific actions permissible by Role.
A Reviewer can:
- Open a scenario for Input. This action will notify (via email) the Planning team and open the scenario for input on the Planning Views.
- Review a scenario.
- Accept a Scenario that has been submitted. This action will notify the Planning team of acceptance and prevent any more input.
- Reject a Scenario that has been submitted. This action will notify the Planning team and reopen the scenario for input.
A Planner can:
- Enter input to an open Scenario.
- Submit a Scenario to the Review team. This action will notify the Review team that the scenario has been submitted and prevent any more input.
After data for a scenario has been entered, Planners can to the “Review, Submit, Accept” View to submit the scenario for review, In an attempt to keep things simple, we will focus most of this post on this “Review, Submit, Accept” View. This is where the majority of the process management functionality takes place.
We have three buttons on the toolbar for this View and they are only enabled under certain conditions.
The Submit for Review button is enabled if:
- User has role of Planner
- Status of the Scenario is Open or ReOpened
The Accept Submission button is enabled if:
- User has role Reviewer
- Status of the Scenario is Submitted.
The Reject Submission button is enabled if:
- User has role Reviewer
Status of the Scenario is Submitted.
This is all controlled through the extensibility that Dodeca provides with Dodeca Workbook Scripts. We will not look closely at workbook scripts in this post, since the purpose of the post is to provide an overview of possible functionality as opposed to step by step instructions.
The buttons on the View toolbar are tied to specific functions in the Workbook script. When a user clicks on the Submit for Review button, the actions (methods) defined within the Submit procedure will be performed. If you would like more information on how a specific function is accomplished please let me know.
So let’s have a look at the Review, Submit, Accept View:
I am in the application as test user Andy Dwyer with a role of Planner, and the Status of the scenario is Open, so the Submit for Review button is enabled. Once I hit submit, a few things happen.
1. An email is sent to the Review team. Here it is on my phone:
(Quick note on testing user emails. Jake Turrell has on post on setting up multiple email addresses with one Google Gmail account: Using Test email accounts. I used that method here for testing multiple target emails.)
2. The scenario is set to Submitted. Since I am testing as a user with the role Planner, I cannot Accept or Reject the submission. Those buttons are still not enabled.
Now lets assume that Leslie Knope, as part of the Review Team, received the email and is eager to check it out. She can open the application and go to the same View. Since Leslie does have a role of Reviewer, and the status of the Scenario is Submitted, she will see the Accept and Reject buttons enabled.
Something is not quite right, so she will reject the Submission.
Upon this action, the Status is set to Reopened. And now, for Leslie, all three buttons are disabled because as a Reviewer, she cannot submit an Open or Reopened scenario.
When Leslie rejected the Submission, Andy, who is on the Planning team, received this email:
Andy was able to make some updates on the Planning input Views that increased the total year Profit margin and is ready to submit again. When he opens the Review, Submit, Accept view, he will see his new totals and can Submit again. Now Leslie can open the View and Accept the submission.
Once it is accepted, all three buttons are disabled for both Andy and Leslie as no further actions can take place until the Scenario is opened again during next year’s cycle.
So that’s it at a high level. The goal of the post was simply to highlight some things that can be done in Dodeca for Process Management. I did not want to delve into to specifics of updating the SQL table, sending the emails, setting the logic for enabling buttons, etc, but I certainly can describe those steps in more detail in a later post.
The key is that although Dodeca does not come with predefined workflow functionality, you can build it with Dodeca. And it can be built the exact way you want it.
This post will expand a bit on the last post regarding Roles in Dodeca.
Can a user have multiple Roles?
Yes, a user can be assigned to multiple roles. Simply use the drop down in the User Manager to check all or any roles that are applicable.
If the HierarchyToRole mapping is defined, what hierarchy is used if a user is not assigned to a role?
In the last post we saw how the user’s role determined the hierarchy that they are assigned in the application, but what if a user attempts to open the application without being assigned a role?
First thing to consider is the HierarchyID property. In the example in the previous post, this property was empty. But a HierarchyID could be defined here, for example Standard, and in that case, the it would be the Standard hierarchy that would be presented to the user.
If there is no HierarchyID defined, then there are more Application property settings to consider for this situation.
From the last post, we updated the AuthenticationServiceObjectTypeID property to DodecaUserRoles, and we also set the HierarchyToRoleMapping property for our two roles: PLANNER and REVIEWER.
There is another Application property to consider under the Security section. It is called AllowStartupForUserAssignedNoRoles. The default for this property is set to True.
This indicates that a user can still start the application even if they do not have a role assigned. But since the hierarchies are only available to users with roles, and the HierarchyID property is blank, then a user with no roles will not see any hierarchy, as below.
Please note: the application has a DefaultViewId which is set to the view called Dashboard. That view will open every time the application is started regardless of any role assigned.
A better option may be to update the AllowStartupForUserAssignedNoRoles property. We can set this to False. We can then also update two other properties to provide relevant information to the user:
MessageCaptionForUserAssignedNoRoles – Set to “No Role Assigned”
MessageTextForUserAssignedNoRoles – Set to “You must have a Role assigned to start this application. Please see your Administrator for more information.”
Now when a user attempts to open this application it will not start, and they will be presented with the following message:
Could there be separate Planner and Reviewer Applications, with User/Roles assigned to each?
Yes, if you would rather have totally separate Applications for Planners and Reviewers you could create them in the Application metadata editor and assign specific hierarchies to each. In this case, you can specify role for startup using the RolesRequiredForStartup property.
We can create a Planner application, and you can assign one Hierarchy (Plan) for all instances of the application with the Default HierarchyID:
Restrict Views and Categories within One Hierarchy using Roles
You and also use Roles to control specific Views and Categories within one defined Hierarchy. In this case all users are using the same Hierarchy, but some Views or Categories of Views could be assigned for specific roles. This is handled in the View Hierarchies metadata editor:
For a specific View or Category, you can set the AccessFilter property:
The options are:
- None (the default)
- AnyRole – any user with any role
- AnySpecificRole – Users with a specific role, or a specific user
- AllSpecificRoles – Users with all the roles
Once the FilterAccess property is set, for example set to AnySpecifcRole, you can then set the AccessFilter_SpecificRoles property. When you click on the ellipsis at the far right of this property, Dodeca will first check if there is more than one Smartclient application using roles, and if there is more than one, you will be asked to select which Application this will apply to. In our example, we have a USER application, and a PLANNER application, so we are presented with the following:
In this case, I choose USER, and then I get the following dialogue to select the roles or specific users:
I can make multiple selections, one per line:
Testing specific Users
Dodeca makes it easy to test specific users and their roles. See the Application property in the Security section: AllowUserArgument.
We can set this to true and then pass a User argument within the ClickOnce URL. For testing the users in the USER application example I am using the following URLs to start the Dodeca application:
You can see that in these URLs, the tenant (t=….) is OUTLOOK, the application (a=…) is USER and the user arguments (u=…) are dwyera and knopel respectively, for our two test users – Andy Dwyer and Leslie Knope. This is how they appear in the User Manager:
As you can see, there is much power flexibility within Dodeca to use Roles to define the exact application for your specific needs. Users can be assigned the proper roles, Applications can be tailored, and even access to specific Views can be managed the way you want them.
This power and flexibility is why we call Dodeca the Finance Accelerator!
Dodeca has built in support for Role definitions. Once roles are defined, you can assign users to roles and allow role specific functions in your Dodeca application. With the assignment of roles, you can do things like:
· Allow access to applications
· Control which Hierarchies are available to users
· Allow access to Views within common hierarchies
· Allow actions within workbook scripts based on roles
In this post we will run through an example of the second item in the above list. We will perform the following tasks for a sample Dodeca application:
1. Create 2 new roles: Planner and Reviewer
2. Create 2 new hierarchies: Plan and Review
3. Within the USER application, align the roles with the corresponding hierarchy.
Anyone who opens the application with the role of Reviewer will see the Review hierarchy, and anyone with the role of Planner will see the Plan hierarchy.
The OUTLOOK Application.
As the Admin, I have created the OUTLOOK Tenant and it has an ADMIN and a USER application. It is a simple forecasting application based on the Sample Basic Essbase application that allows the company to build forecasts throughout the year. The difference with Sample Basic is I have added a dimension for Years and have added additional scenarios to represent the forecast called Outlooks.
Here is a look at the View Hierarchy and opening View in the ADMIN application. There are 13 views.
There are different functions in this application to performed by specific individuals.
A Reviewer can:
- Perform scenario maintenance such as: open and seed a scenario for data input, close a scenario and publish a scenario back to the Reporting Cube.
- Review input from Planners
- Accept or Reject the forecast from the Planners
- See all reports
A Planner can:
- Enter data on input forms.
- See some reports.
For our simple example, the activities translate into what can be performed in the Views. We can control the activities of each role by controlling what views are available to each role. Here is the list of View and the corresponding access by Role:
Roles are defined within your Tenant. That is, they are available for use across any number of Applications defined in your Tenant. In the OUTLOOK tenant, we have an ADMIN application and a USER application. The roles and hierarchies in this example will apply to the USER application.
To define roles for the Tenant, go to User Roles under the Admin menu:
This will open the User Roles metadata editor.
We will create two new roles for this OUTLOOK Tenant: Planner and Reviewer. Click on the New button at the bottom of the editor to create the roles.
Now we have two defined roles, REVIEWER and PLANNER.
Next, we will create two new Hierarchies. Go to the Hierarchies metadata editor from the Admin menu.
We currently only have the Standard Hierarchy, which we can see below. We are going to create two new Hierarchies, and we will call them Review and Plan. Hierarchies require and ID and a Name.
Once committed, the hierarchies will appear in alphabetical order, like this:
Next, we can edit the Hierarchies to insert views and categories per the Access grid above. Once the Views and Categories have been added to the hierarchies they will look like this:
Great. We now have the roles and hierarchies defined. Our next step is to edit the USER application to set up the Role to Hierarchy mapping.
Open the Application metadata editor under the Admin menu, and click on the USER application:
The first property we need to update is under the Security category. Update the AuthenticationServiceObjectTypeID to DodecaUserRoles.
Next check the properties under the View Selector category. Under ViewSelector Properties, there is a HierarchyToRoleMapping property.
Click on the ellipsis on the far right to open the Hierarchy to Role mapping dialog box. We set the mapping as:
And save the application.
We have a couple of test users for this application. Andy Dwyer is a Planner, and Leslie Knope is a Reviewer. We can assign their roles in the User Manager in Dodeca:
All done! Let’s test the application for Andy:
We see that Andy has the correct Hierarchy for a Planner. Now let’s check Leslie:
Yep, Leslie has the correct hierarchy for a Reviewer. Voila! All set!
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.
- 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.
- 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.
- Thus far, we have put a notification on the View to let the user know that all fields should be there before saving.
- If the user attempts to save anyway, the user will get an error. We want to stop this error.
Here is our mission:
- If the user attempts to save while there are validation errors, we will stop the “save” process to avoid the error.
- 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:
And add a formula to cell G2 to count the number of TRUE’s in the range. And we will name this field ErrorCount:
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.
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.
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:
Now to configure this event. Here are the 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
Also set the CancelProcedure property to TRUE
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 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.
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:
I hit the okay button, and we are back to the View. No errors. Voila!
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.
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.
Here is the 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.
Clicking on the details button of the error notice we see:
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:
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:
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.
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.
Now we name the range on the View as DataBlock.Range.
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.
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.
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:
And because the range follows all the fields, the validation message will appear on any and all lines that are missing fields:
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.
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:
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
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.
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.
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.
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.