By TopDown Team
May 20th, 2015
In part 5 of this blog series, we are going to implement some additional business logic into the Essbase properties, and we’ll build an Export to publish our metadata to a database table for consumption by Essbase.
In the prior post, we learned how to create and use Imports, Blenders, and how to manage Essbase attribute dimensions in DRM.
In the prior post, we created the properties required to manage the associations of attribute dimensions. In addition to having an attribute dimension called Population, the Market dimension in Sample.Basic also has 3 UDAs, Major Market, Small Market, and New Market. Whereas we used the data type Global Node to manage the associations of the attributes, the UDA properties will have a data type of string. Below is a screenshot of the Major Market property.
This is a defined global node property, with a single possible value in the list.
Here you can see I have created the 3 properties and created the associations using an Action Script.
When you build your export tables and views, I would recommend creating fields for more UDAs than you are currently using. This allows for future growth and makes it very easy to add UDAs in the future.
Essbase Attribute Export Properties
In the prior post, we created the properties required to manage the association between base members and attribute members. These properties are using a data type called Global Node, which points to a different node within the same version. So when you create an association, the value of the property is the global node object that you have selected.
We could export those properties, but the problem is that every node in our version has a prefix to indicate its node type. When we publish this metadata to Essbase, we do not want to send the prefix, we only want to send the MidName (actually we will point to the Essbase.MemberName property, which itself points back to MidName).
Let’s break down the script line-by-line.
Line 1: Create a variable called AttrMbr containing the global node object that is associated.
Line 3: Check to see if the Product node being evaluated is a Leaf because attributes can only be assigned to leaf nodes (level 0 members in Essbase speak). If you recall from the last post, you could in DRM associate a parent-level node to a Caffeinated member, there is no built-in constraint to avoid that. Having this check in place is a safety precaution in case someone mistakenly assigns an attribute node to a non-leaf.
Line 4: Check the length of our variable. This is required for error handling, because it’s possible that not every leaf node will have this attribute assigned. If the length is equal to 1, then we know there is an attribute assigned.
Line 5: This command basically has 2 parts…
- node.NodeByAbbrev(attrMbr) = This is accessing the global node object which is assigned (in the Caffeinated hierarchy)
- PropValue(“Essbase.MemberName”); = this is accessing the property value Essbase.MemberName for the global node object we have referenced in the caffeinated hierarchy. We haven’t actually setup the MemberName property yet, but we will do that shortly, and it will point to the MidName property. Having an extra layer of logic in the MemberName property allows us to have special considerations for downstream Essbase applications versus other destination systems.
Line 6: If the length of our variable is not equal to 1, then there is no attribute assigned and we should return an empty string.
Line 7: If the node is not a leaf, we don’t care whether there is an attribute assigned or not, just return an empty string.
Create the other attribute export properties on your own. The only change to the script from one property to the next is in Line 1. Make sure you are pointing to the correct attribute property. For example…
Essbase Property Exports
In addition to having specific export properties for the attribute assignments, we also need export properties for the standard Essbase properties. For example, when we assign data storage to a node in DRM, we may assign it as “Store Data”. When we export our metadata to Essbase, rather than exporting “Store Data”, we need to export the property code “S”. We’ll be using the property type Lookup for Data Storage, Consolidation, Two Pass Calc, Time Balance, Time Balance Skip, and Variance Reporting.
Use the Migration Utility to extract the Essbase properties above, modify the XML file to change the property names and descriptions, and then load the file back into the application. If you need a refresher on the Migration Utility, refer back to Part 3 of this series.
Once you load the Export properties, go ahead and configure them within DRM. Here’s the finished product…
Essbase Property Business Logic
Our next step is to implement some business logic into a few of the Essbase properties. We aren’t going to go crazy, but rather I wish to demonstrate a small picture of what is possible.
The first property we’ll configure is Essbase.MemberName. This script is very simple as it is just pointing back to the DRM.MidName property (which if you recall is stripping off the prefix and suffix if there is one). The MemberName property will be used as the “Child” column for all of our Essbase exports. You may be thinking to yourself, why don’t we just export the MidName property? It’s a good question and I would argue that a good design principal in general is shoot for as few properties as possible. However, I always end up needing an extra layer of logic specific to downstream Essbase applications. Having this property allows you to keep your business logic for Essbase applications separate from other systems.
Next we’ll configure the Essbase.ParentName property. If you haven’t guessed, this property will populate the “Parent” column in our export tables.
In order to avoid errors at the Top Node (Level 1, since it doesn’t have a parent), we are checking to make sure that we are evaluating nodes at a level greater than 1. On Line 2 declaring node.Parent allows us to access the node object of the current node’s parent. We are pointing to the MemberName property rather than the MidName property in case we later implement additional logic.
In a real-world application we would typically have business logic implemented into the Data Storage, Consolidation, and Variance Reporting properties, but we’ll keep it simple for this application, as the goal is to learn the basics.
Property Filtering with Node Types
In the screenshot below, I’ve opened the Measures hierarchy. Properties are organized by category in the right pane of this screen. When I click on the Property Category drop down, you see only the categories that appear by default. This behavior is expected and is because we don’t yet have any properties assigned to any node types. In order for a property category to appear in this drop down, there must be at least one property associated to the node type of the node you have selected.
After we have all of our properties associated to node types, we will see the Essbase property category for all node types. However, we won’t see every property for every node type. For example, the Essbase property Variance Reporting is an Account-only property so there really is no reason to see that when you are working in the other hierarchies.
We can still access all of the properties by enabling the option below, Show All Properties. However, you still won’t see properties that aren’t assigned to any categories so make sure to assign your properties to categories as you create them.
After enabling the Show All Properties option, we now can see all of the property categories that exist, including Essbase.
In Part 3 of this series, we created 6 properties in the Custom.DRM namespace. We did not however assign these to any property category so we are going to do that now. When you create a property definition, you can assign it to any existing property categories. You can also open a property category and then add property definitions to it.
Navigate to the Administer section -> New -> Property Category
Enter a Name then select the property definitions to be included in this category. I used the shift key to select the 6 DRM properties, then click the right-arrow button to select them.
Save the new property category.
Go back to the Administer section and expand on Node Types.
We are going to setup the properties associated to the Measure node type together, and then you can setup the other node types on your own.
Select Measure and then click Edit.
As expected, there are currently no properties assigned.
The drop down for Glyph allows you to setup custom icons for nodes by node type. I prefer not to use glyphs because the default icons are very informative indicated leaf versus limb, and primary, non-primary, and shared. A custom glyph does not give this information but rather will show the same icon for every node of that type.
First add the relevant properties from the DRM category.
Then add the relevant Essbase properties.
When your finished, make sure to click Save as your changes will be lost.
Now jump back to the Measures hierarchy and disable the Show All Properties option.
Click the Property Category drop down list to see the 2 categories we’ve just added.
Here’s our filtered list of properties specific to the Measures hierarchy (measure node type actually as you could have multiple node types within a single hierarchy).
If you wish to re-order the property definitions, do so within the property category, not the node type.
Publishing Metadata with Exports
It’s time now to put the whole thing together. I’ve created a database called FinStage and a table called Meta_Sample_Basic that will be our target table.
We are going to export all hierarchies to a single table and then use a view for each dimension.
I’ve created space for up to 10 UDAs even though the Sample.Basic application currently only has 3. I highly recommend leaving room for growth because it will be much easier to add UDAs into your publish process later. I’ve also created space for up to 10 attribute assignments even though we currently only have 5.
We need to setup an external connection to allow DRM to publish information to the database table we have created.
Navigate to the Administer section -> New -> External Connection
Enter a Name for your external connection.
The Connection Type will be database table though you can also select server file if you want to save a file to a network location.
The Data Access Provider is SQL Server in this environment, and the Connection String is below.
Enter the credentials for a user with write access to the target database.
Click the test connection button to confirm DRM is able to connect to your database provider.
If your connection is successful, click the button to retrieve objects from your database.
Highlight the export table and then click the right arrow button to select it.
Save the external connection.
Build the Export
Navigate to the Export section and click on the New Export button.
We’ll be building a Hierarchy export which is the most common.
Select the version from which you will export. If you are using a “Current” version variable, it will have that selected by default.
Click the Add… button to select the top node you will export from.
Since we are using a single export table in this example, I have added all the hierarchies into this export. Once all the metadata is in the export table, I will use a view to create the source for each dimension build, which will each have its own rules file. However, I am assuming you know how to run a dimension build into Essbase from a relational source, so we are only going as far as to populate the export table.
Move over to the Style tab.
These are the default options and I am not going to change them.
Move over to the Filter tab.
I am not attaching a query to this export because the goal of this series has been to learn the fundamentals. However, in the real world applications that we develop, we always use export flags to indicate which nodes are being published to each downstream system.
For example, we would have an export flag for the Sample.Basic application as a Boolean property, which most likely had some business logic to avoid having to manually maintain all flags. We would then create a query that said, “where the Sample.Basic export flag is true”, and attach that query to our export. That would allow us to send some nodes to Sample.Basic, but not others.
Move over to the Columns tab.
This is where you tell DRM which properties you want to export into your target system whether it is a database table or flat file.
I’ve included the “Now” property we included to serve as a timestamp, as well as Version Name, Hierarchy Name, Record ID, Name (as in full node name with prefix), Primary node flag, shared node flag, and whether that node is a leaf. Some of this information is purely useful as a reference when looking back at a past publish, but the Hierarchy Name property will be used in the database to separate each dimension into its own view.
If you look back, the properties here correspond to the database table I built earlier. I have the properties in the same order, and although they don’t need to be, it makes the mapping exercise in the next step easier.
Move over to the Target tab.
The Device will be “Database Table”, and then select the external connection you created, and select your export table in the Database Table field.
In the lower part of the screen you will map your properties to the fields in your database table. If you click on the Edit button (pencil) on each row, you can select the target field and then save it.
You typically are going to want to Clear All Rows before the export runs.
Click the Save button then name your export, give it a description, and assign to an access group.
It’s time to test our export, click on the Run button (green triangle) to execute the process.
You will see a small popup window appear to indicate the status of your job.
if everything is configured properly, you will receive a successful confirmation message when the process is complete.
Check the export table to see your results.