Getting Started with Oracle DRM v11.1.2.3—Part 3

Avatar

By
November 6th, 2014


In part 3 of this blog series, we’ll learn how to build properties in bulk using the Migration Utility, and will create six application-specific properties in the Custom.DRM namespace. To read part 1, click here; and to read part 2, click here

Overview

In the prior post, we learned how to manually manage Versions, Hierarchies, Nodes, and Properties. In this post, we’ll use the Migration Utility to load the properties needed to manage the attributes of an Essbase application. The Migration Utility can also be used to migrate an application from one environment to another, using XML as the data medium.

Migration Utility

The Migration Utility is a web-based application that can be accessed through the Start menu, or directly by URL. The URL for my environment is below; substitute “Aphrodite” with the name of the server running DRM in your environment.

http://aphrodite/drm-migration-client/

As you can see, the Migration Utility has 4 basic functions that it can perform. In this post we’ll perform a Load. If we were migrating an application from one environment to another we would first perform an Extract (from the source), and then Load into the target.

The Migration Utility is also very handy if you need to update more than a few properties at a time within an application. As long as you’re not changing the name of the property, you can load an updated XML file to perform bulk updates. Having some familiarity with XML files will help you locate the appropriate fields to edit. First Extract the properties, update the attributes in the XML file you downloaded, then load that file back into the same application.

Getting Started with Oracle DRM (11.1.2.3) - 001 - Migration Utility URL

I’ve downloaded an application template from Oracle called epma-app-template.xml. Using this template is optional, but I like to have the “Essbase” namespace in the application rather than using “Custom.Essbase”. In a real-world setting, it’s likely that some members will have different attribute settings in one application versus another. For example, maybe you have a statistical account that is stored (input) in one application but dynamically calculated in another. For this reason, we’ll use sub-namespaces to manage application-specific properties, so that having different attribute values is possible. First we’ll have “Custom.Sample” to manage the properties for Sample.Basic, then we’ll create “Custom.Labor” used to manage the properties for a new Essbase application coming online to plan and report on labor expenses. In this approach, the “Essbase” namespace is used to centrally manage the default attributes for Essbase. In most cases, the application-specific properties will be derived and set to the value of the corresponding default “Essbase” property. So in most cases the values will match, but the capability to override exists which will be important as your environment grows. At a minimum, I would suggest creating application-specific properties for Data Storage, Consolidation, and Member Formula. UDAs can also often be application-specific so we’ll design a solution for managing UDA assignments.

Click on Load to get started.

Getting Started with Oracle DRM (11.1.2.3) - 002 - Import

Click Choose File.

Getting Started with Oracle DRM (11.1.2.3) - 003 - choose file

Locate the epma-app-template.xml file, select it, and click Open.

We’ll be taking certain elements from this template, but actually a fraction of all the metadata contained. If you were migrating an application, you’d select the file you downloaded when running the Extract on the source application. If using to perform a bulk update of properties, locate the XML file you downloaded and modified.

Getting Started with Oracle DRM (11.1.2.3) - 004 - select ok

Once you’ve selected your XML file, click Upload.

Getting Started with Oracle DRM (11.1.2.3) - 005 - upload

This screen is reading the XML file you’ve selected to give you an overview of the contents. The application template I’m using from Oracle has more informational content than will an Extract of your application. You can see quite a bit of content below regarding Purpose and Usage.

Click Next to move forward.

Getting Started with Oracle DRM (11.1.2.3) - 006 - next

If this is your first time running a Load and you don’t have the admin user password, make sure you have the correct application selected in the Connections field and click on Copy. Enter your username and password and then save the connection.

Enter your Password and click Log In.

There’s not much point in using the Test Connection button because it will do the same thing as Log In, but without the option to move forward in the process.

Getting Started with Oracle DRM (11.1.2.3) - 007 - password and login

Now we are viewing the contents of the XML file organized by object-type in a hierarchical arrangement. Although this application template contains Exports, Glyphs, Node Types, Property Categories, Property Definitions, and Validations, I am only going to load a single Property Category, and a handful of Property Definitions specific to the Essbase technology. Later we’ll create our own Node Types, Exports, and Validations.

Node Typing is a very useful feature in DRM. In the next section we’ll create a new property called “NodeType” (fully qualified name Custom.DRM.NodeType) to manage this functionality. There is a “Core” Hierarchy property called “Hierarchy Node Type”. The value of this property on all of our hierarchies will point to the property Custom.DRM.NodeType. Our Node Types will be controlled by the prefixes on our Node Names. For example, in the string “acct!500600”, “acct” is the prefix, “!”, is the prefix delimiter, and “500600” is the “MidName”, which you can think of as the member name in an Essbase application. A full node name will always have a “Prefix”, a “MidName”, and will have a “Suffix” only if it’s a shared node (i.e. “:Shared-001”).

Glyphs allow you to create custom icons on nodes (controlled by Node Type) but I prefer to use the default icons since it is easier to distinguish limbs from leafs. Node Types can also be used in derived properties as part of your business logic, and to filter certain types of nodes to see only certain properties when working in a hierarchy.

Expand Property Categories -> select “Essbase [Essbase]”

Getting Started with Oracle DRM (11.1.2.3) - 008 - essbase prop cat

Expand Property Definitions -> scroll down to the Essbase properties

You can see I have selected most but not all of the Essbase properties. Some of these are specific to currency cubes which I don’t use, and others are specific to EPMA so have omitted both. If you’re not sure which property categories you need, consult with an Essbase developer and keep in mind that you can easily add properties later if need be.

Getting Started with Oracle DRM (11.1.2.3) - 009 - essbase props

After you’ve selected all the properties you wish to Load -> scroll to the bottom of the page and click Next (there are still 2 steps before the Load is executed).

Getting Started with Oracle DRM (11.1.2.3) - 010 - next

This screen allows you to review your selections prior to running the Load process.

Click Next to proceed.

Getting Started with Oracle DRM (11.1.2.3) - 011 - next

Click Run Load.

Getting Started with Oracle DRM (11.1.2.3) - 012 - run load

Review the results of the Load process. My load completed successfully without any errors or warnings.

You can Download the log if you wish, you just Return to Main Menu.

 

Keep in mind that if you make changes to your XML file after this has run, and then click Start New Load, the utility will not pick up the changes you’ve made to the file as it is currently held in memory in its original state. For this reason, I formed the habit of always using Return to Main Menu.

Getting Started with Oracle DRM (11.1.2.3) - 013 - results

Jump back to the Web Client so we can review the results of the Load.

You can see the Essbase property category and the handful of Essbase properties that were loaded.

Getting Started with Oracle DRM (11.1.2.3) - 014 - web client results

One thing I noticed is that the VarianceReporting property is not setup as a Boolean property, but rather as a string with a list of values.

As you can see in Essbase, this is a Boolean attribute so we are going to modify this property in DRM.

Getting Started with Oracle DRM (11.1.2.3) - 015 - EAS Variance Reporting

Open this property for edit by double-clicking, or select it and click the Edit button.

Getting Started with Oracle DRM (11.1.2.3) - 016 - edit variance reporting

Here are the attribute values of VarianceReporting in their original state. You can see the Data Type is “String” with a List of values, and the “NonExpense” set as the default (the other option in there is “Expense”).

Getting Started with Oracle DRM (11.1.2.3) - 017 - before variance reporting

Change the Data Type to “Boolean” (true|false).

Uncheck the List checkbox.

Change the Default Value to blank.

After you’ve made these changes, be sure to Save the property.

For now we’ll leave the Property Type as “Defined”, but later we are going to change this to be “Derived” and will build in some business logic so this property doesn’t have to be managed manually. We may end up with an AccountType property we can use to derive this, or possibly even checking the first number of the account string.

Keep in mind as your go through the design and configuration of your application that loading and modifying properties is an iterative approach. You likely will not have your properties in their finished state on Day 1, but rather will be changing and fine-tuning throughout the project.

Getting Started with Oracle DRM (11.1.2.3) - 018 - after variance reporting

Pseudo-System Properties (Custom.DRM)

Up until this point, we’ve been reviewing the system functionality of DRM. In this section, we’re going to discuss some best practices that we implement on every project.

In the previous post, we discussed the use of namespaces and sub-namespaces as a means of organizing the properties within an application. In every application we develop, we create a sub-namespace called Custom.DRM. The properties we create in this sub-namespace are sort of like the system properties that come packaged with DRM in that they are application-specific (the whole application), rather than being specific to a downstream system.

We’ll create six of these pseudo-system properties that are consistent in every application we develop. We’ll likely have more than six of these properties, but the others are generally more specific a client’s master data.

Since we’ve already reviewed the procedure to create new properties, I am just going to provide screenshots of the configuration.

Custom.DRM.Now

This is a derived (read-only as apposed to read/write) property that simply returns the current date and time. This property will be used in all of our exports to create a timestamp in the export tables.

Getting Started with Oracle DRM (11.1.2.3) - 001 - Now

Custom.DRM.PrefixDelim

As we’ve discussed, we’ll be controlling our Node Types through the prefix in the Node Name. We’ll us an exclamation point (!) as the delimiter between the Prefix and the MidName (i.e. member name).

Before we create this property however, we need to modify a System Preference (InvName) because by default, the exclamation point is included in this system preference and therefore disallowed in Node Names.

Open InvName for edit -> Remove the exclamation point -> Save

There is another system preference called InvDescr with which you can manage the disallowed characters in Node Descriptions.

Getting Started with Oracle DRM (11.1.2.3) - 002 - InvName

PrefixDelim will be a derived property using JavaScript and will simply return an exclamation point. Having this attribute as a property makes it very easy to change in the future, versus having hard-coded in other scripts.

Getting Started with Oracle DRM (11.1.2.3) - 003 - Prefix Delim

Custom.DRM.Prefix

Now that we have identified the delimiter separating the Prefix from the MidName in our application, we can create a derived property to return the Prefix. I’ve created a node in our sample version called acct!Assets. We need our script to return “acct” as that is the Prefix in the Node Name.

We’ll have a later post dedicated to writing scripts and formulas for derived properties, so for now I’ve broken the script out into several steps and added comments. Using JavaScript print() function is very useful for developing and troubleshooting your scripts, but should be commented out in production because they add additional overhead.

In the lower pane Evaluation Results, you can see the correct value of “acct” being returned. The Warning panel shows you the results of your print() functions.

Getting Started with Oracle DRM (11.1.2.3) - 004 - Prefix

Custom.DRM.MidName

Think of the MidName property as the member name in an Essbase application. Each Node Name will have a prefix, which is used to identify the Node Type. Shared nodes will have a suffix (e.x. “:Shared-001”) describing shared information. The MidName property is the string between the prefix and suffix delimiters (if the node is not shared then just to the end of the Node Name).

This will be one of the most widely used properties in an application, as other properties will point back to this one. For example, when we publish our metadata for consumption by Essbase, we’ll export the Essbase.MemberName property rather than Custom.DRM.MidName. In most cases, the Member Name and the MidName will be the same, but having the separate property for Essbase allows us to embed any additional business logic not relevant to other downstream systems.

Getting Started with Oracle DRM (11.1.2.3) - 005 - MidName

Custom.DRM.Suffix

This property will return the suffix (shared information) for a shared node, and an empty string for a non-shared node.

Getting Started with Oracle DRM (11.1.2.3) - 006 - Suffix

Custom.DRM.NodeType

The last property we’ll be creating in this post will be used to control the node types in our application. This property will look at the prefix to determine the Node Type. Before we create the this property, we are going to create some DRM Node Types that our property will point to.

Go the Administer section -> New -> Node Type

Getting Started with Oracle DRM (11.1.2.3) - 007 - node types

For right now, I am going to create a node type for each standard dimension in the Sample.Basic application (Year, Measure, Product, Market, Scenario), and another node type for the attribute dimensions.

I’ve entered the Name “Measure” and have omitted a Description, as that is optional.

You can see that Glyph is set to “[None]” which means it will use the standard DRM node icons. We don’t have any glyphs in the application, and I am not going to create any because I prefer the standard icons having visual cues as to which nodes are limbs versus leafs (and also primary, non-primary, and shared).

Node Types are also used to filter properties. For example, the Essbase attribute Time Balance is only applicable to an Account dimension (tagged as type Accounts). So if we are working in the Market hierarchy, there is no point in seeing that property.

Getting Started with Oracle DRM (11.1.2.3) - 008 - measure node type

Repeat these steps to create all the Node Types you need and keep in mind that you can certainly add more later if needed.

Getting Started with Oracle DRM (11.1.2.3) - 009 - all node types

The last step before we create our NodeType property is to determine how to name our prefixes. We generally try to keep our prefixes the same length, usually 4 characters or so, but this comes down to personal preference. In this example I am going to use prefixes of varying length to demonstrate that our business logic on Custom.DRM.Prefix will work no matter then length of the prefix.

When we select the Property Type “Lookup”, a new field will appear called Lookup Property, which is a list of all the node properties in the application; I’ve selected the Prefix property we created a few moments ago.

In the Lookup Table tab, we build a table of values with the prefixes on the left, and the Node Type to be returned on the right. When we build an import file in the next post, each node will have one of the prefixes in the left section of this table. As you can see, each prefix must correspond to only one Node Type, but multiple prefixes can use the same. You can see I’ve created 5 prefixes that return “Attribute_Dimension” for their node type. Attribute dimensions in Essbase don’t have many attributes, so we are going to save ourselves some setup/maintenance by using the same node type for all of them. We could have gone a step further and used the same prefix in all of the attribute dimensions, but this is a safer approach, as it will help to maintain unique naming in our application. No 2 nodes in a DRM version can have the same name, so this will help to avoid duplication.

Getting Started with Oracle DRM (11.1.2.3) - 010 - NodeType

Stay tuned for the next post in which we’ll extract metadata from Sample.Basic, transform those extracts into a DRM import file, build an Import in DRM, and load this metadata into our first version.


Avatar

About TopDown Team

The TopDown Team includes members of TopDown Consulting who want to let the community know about webcasts, conferences, and other events. The team also conducts interviews on various EPM industry topics.

2 comments on “Getting Started with Oracle DRM v11.1.2.3—Part 3”

  1. Raj says:

    Hello Jordon,
    Thank you for your series of articles on DRM and these are exactly what I am looking for!!!!

    Unfortunately, In series-3, when I try to load epma-app-templete.xml am getting below error for all the chosen properties:

    Error processing object type PropertyDefinition:
    Essbase.varianceReporting[Variance Reporting]: The object was not loaded. ORA-01401: inserted value too large for column

    DRM VERISON: 11.1.2.2

    Can you please help me in this regard,

    Thank You,
    Raj

    1. Hi Raj,

      I have not run into this issue when loading the application template. I’m wondering if within that XML file, the default value is larger than the max value size; that is one possibility anyhow. I think you will need to open that XML file, locate the Variance Reporting property, and see if you can figure out the issue. Update the XML file and then try to load again, sorry I couldn’t be more help.

      Jordan

Leave a Reply

Your email address will not be published. Required fields are marked *