Getting Started with Oracle DRM v11.1.2.3—Part 2

Avatar

By
August 27th, 2014


In part 2 of this blog series, we’ll learn about Versions, Hierarchies, Nodes, and Properties. To read Part 1, Getting Started with Oracle DRM v11.1.2.3—Part 1, click here.

Versions

The following is taken from Oracle’s online DRM User Guide Documentation…

Data Relationship Management organizes sets of related data into versions. A version is a single, independent set of data that can represent a period of time or a business scenario. Data within each version is categorized into hierarchies, nodes, and properties. All data-related operations require selection of a version.

 Separate versions are typically used for the following purposes: 

  • To maintain historical copies of hierarchies, nodes, and properties for previous periods in time.
  • To create new data or model changes to existing data without affecting other users or processes.
  • To load data from different sources to be compared, rationalized, and blended.

Typically, a new version will be created for each fiscal period. This creates an audit trail and allows your users to easily see what has changed month-over-month.

Creating a New Version

To begin, we are going to manually create a version and review some of the system properties available for versions.

Log in to DRM, and if the application does not open in the Browse section, navigate there.

From the Versions drop down, mouse over New, then click on Version….


A new window will appear prompting you to name your new version, and to give it a description.

Enter a Name, a Description, and then click OK.

In a later post we are going to create a new version complete with hierarchies and nodes using an import. That version will be built by reverse-engineering the Sample.Basic Essbase database.

001 - name new version

We now have our first version created, however, this new version currently only exists in memory and has not yet been saved to disk. If we were to restart the application or the DRM Service, this version would be lost forever.

Load Status is another important property with the possible values of Initialized and Loaded. A Loaded version is currently loaded into memory, whereas an Initialized version is not. If you were to Unload an unsaved version, that version would be lost as it is not saved to disk. Versions need to be loaded in order to query and export them, but I recommend only keeping the versions loaded that you need, because each loaded version increases the memory footprint of the DRM application.

Right-click on your new version, click Save. You can also see the options to Load and Unload a version.

002 - save new version

After the version has been saved, you’ll notice a couple things have happened.

For one, we are now seeing a check mark in the Saved column. You can now restart the application or DRM service without risk of losing your data as it has been saved to disk.

003 - saved check mark

The second thing you’ll notice is that you can expand your new version to view its corresponding baseline version. A “Baseline” version is created at the time a new version is saved, and will never change. This allows you to roll a version back to its state when it was saved. The “Normal” version can be edited so we’ll be adding hierarchies and nodes to this one. You can also create what is called an “As-Of” version, which reflects the state of the version at the point in time the “As-Of” version was created. This can be useful if for example you were going to apply hundreds of changes to your version, and wanted to be able to fall back in case you made a mistake.

004 - saved new version
Next, we are going to open the Properties Pane to view all system properties associated with a version.

Right-click on your new version and click Properties.

005 - open properties
We can actually view most of the version properties without opening the properties pane, however, we cannot make any changes.

You can see that the Version ID and Version Name are grayed out, those cannot be changed. We can however change the Version Description, Version Status, and Version Owner.

The Properties Pane is used a lot and is contextual to what you’re working on. If you are looking at a version, you will see the properties associated with that version. If you have clicked on a hierarchy within a version, you will see hierarchy-specific properties. Within a hierarchy where you work with nodes, you’ll see node-specific properties.

006 - version properties
Version Status is an important property and describes the lifecycle of a version, starting with “Working” and ending with “Expired”. Here’s a description of each…

  1. Working – A working version is open to browse and edit by all users.
  2. Submitted – A submitted version can only be edited by users with the Data Manager role or the version owner.
  3. Finalized – A finalized version cannot be edited by any users. The idea is that the version is complete and ready to be published to downstream systems.
  4. Expired – A version will typically be set to expired once the new version for the current fiscal period has been creating and is in the working status. No users can edit an expired version.

007 - version status

Version Variables

Version variables allow you to dynamically refer to versions. For example, it is common practice to create a Current and Previous version variables. The Current variable would be utilized in most if not all system exports. If you do not have a version variable, you would have to open each export each month to switch them to the newest version. With a version variable, you can set the new version once, and then all of your DRM objects using that variable will be updated automatically.

A version can be assigned to multiple variables, but a variable can point to only one version. A version variable doesn’t need to have a variable assigned, but I recommend that you do for the sake of any other objects referencing that variable.

To create a version variable, click on the Version Variables button as shown below.

008 - version variables
When the version variables window opens, you won’t see any variables, as this is a new application.

Click on the New Variable button.

009 - new variable
There are 3 properties associated with a version variable.

For the Name, I’ve chosen “Current” as that is a common practice.

Enter a Description for your variable.

Lastly, select an Object Access level for this variable. Object Access is a concept we’ll see in most DRM objects including queries, imports, exports, and compares.

Users with the roles Data Creator, Data Manager, and Application Manager can create User variables for their own use. When you create a DRM object with the User Object Access Level, only you can see that object.

Data Managers can also create Standard variables for public use by all users.

System variables are managed by Application Administrators and should be used for downstream system integrations.

010 - variable properties
We are going to use this variable later in our exports, so I am going to create it as a System variable.

Click OK to save the variable.

011 - ok
Expand on System Variables to view our new variable.

012 - expand sytem
Now we can see the Current variable that we created.

Click on the Edit button to assign our sample version. Later we’ll update this variable once we import the metadata from Sample.Basic.

013 - edit
In the Assigned Version field, click on the drop down to select our Sample_Version.

014 - assign version
Click the Save button to save this assignment.

015 - update
Close this window.

016 - close
Now that we’ve successfully created a version variable and assigned our Sample_Version to it, we are going to assign that variable to a system preference called DefaultCurrentVersion.

Click on the Administer button.

017 - administer
Locate and click on the system preference DefaultCurrentVersion to select it.

You can click on the Edit button as shown below, or double-click on the system preference.

018 - edit default current
From the Value field drop down, select the version variable Current.

019 - set default
Click the Save button to save this system preference. When creating a new DRM object like an export, this version variable will be selected by default, and therefore the version referenced by this variable.

Some system preferences require the application to be restarted before they take effect; this one does not as you can see in the description below. Prior to restarting a DRM application, make sure to save any and all versions that you need because they will be lost forever otherwise.
020 - save

Hierarchies

The following is taken from Oracle’s online DRM User Guide Documentation…

A Data Relationship Management version can contain one or more hierarchies. A hierarchy can represent a business view, reporting structure, or a collection of related items. Hierarchies group and organize data into nodes, relationships, and properties.

Each hierarchy is assigned a top node and other nodes in the version can be added to the hierarchy by defining parent-child relationships between them. All nodes that are descendants of a hierarchy’s top node are included within the hierarchy. A node can exist in multiple hierarchies and will always have the same descendants in every hierarchy to ensure synchronization across hierarchies. A node can have the same or different parents in each hierarchy.

Hierarchies provide the main interface for a user when working with Data Relationship Management. Examples of hierarchies include Balance Sheet, Line Of Business, Geographic, Legal, and Management Summary.

Once we’ve reverse engineered the Sample.Basic Essbase database, each dimension (shown below) will represent a hierarchy in our DRM version, Year, Measures, Product, Market, etc.

001 - sample.basic outline

Often times, Account and Entity dimensions will contain alternate rollups containing shared members; the Product dimension (shown below) is an example of such. DRM also has a concept of sharing nodes so we will be able to replicate this structure in DRM.

002 - product dim

For now we’ll learn how to manually create a hierarchy. In a later post we will build a new version complete with hierarchies, nodes, and property values using an IMPORT.

Log in to DRM and navigate to the Browse section.

In the lower pane, click on the New Hierarchy button.

003 - new hierarchy

For this example, I’ve chosen the Name “Account” and the Description “Account Dimension”.

Group is asking whether you want to assign this hierarchy into a hierarchy group, we don’t have any created so I’ve left the value as “[None]”.

In the text from Oracle’s User Guide at the beginning of this section, it mentions a “top node”. In Essbase lingo, you can think of this as the dimension root, and as such I am naming it the same as the Hierarchy Name. In a later post, we are going to use prefixes on every node to control our node type (for example “acct!Account”), but we’ll discuss that in detail later.

The Description for the top node is not required and I have elected to leave that blank.

Click OK to complete the new hierarchy.

004 - new hierarchy properties

Now let’s take a look at some of the System properties associated with hierarchies.

Click on your new hierarchy to select it -> click on the Hierarchy Properties button.

005 - open hierarchy props

As you can see, there are more System properties associated with hierarchies than there are for versions.

We’ll review these hierarchy properties in more detail when we build our import file to load the Sample.Basic metadata.

006 - hierarchy properties

Hierarchy Groups

The following is taken from Oracle’s online DRM User Guide Documentation…

Hierarchy groups allow related hierarchies to be organized together for easy browsing and selection. Hierarchies may be grouped using one or more hierarchy group properties. A hierarchy can only be associated with a single group for each hierarchy group property. The core Hierarchy Group property may be used for default grouping purposes. Additional hierarchy group properties may be added to handle alternate grouping requirements. When browsing hierarchies, use the Group By drop-down list to select a different hierarchy group property to use for grouping.

As stated above, you might create hierarchy groups to organize related hierarchies. For our example, we are going to manage both primary and alternate rollups in a single hierarchy, but you could split those into separate hierarchies.

Another reason to place a hierarchy in a group is to create a constraint on a property definition with the data type Global Node. A Global Node data type is used for Essbase attribute dimension assignments. The property itself is pointing to another node within the same version. For example, in Sample.Basic, the Product dimension has 4 associated attribute dimensions; Caffeinated, Intro Date, Ounces, and Pkg Type. When you create a property with the Global Node data type, you can by default select any node within the same version. Placing the attribute hierarchy into its own group allows you to constrain the available nodes to only that hierarchy. In a later post, we’ll create a property called Custom.Sample.Attribute.Product.Caffeinated to manage the associations between Level 0 Product members, and members from the Caffeinated hierarchy.

To create a hierarchy group…

Click on Hierarchies -> Mouse over New -> Click on Hierarchy Group

007 - create hierarchy group

Give your hierarchy group a Name, and if you wish a Description but it is not required.

To assign a hierarchy or hierarchies to this new group, you can double-click on them, or select them and then press the right arrow button. I am going to assign the Account hierarchy into this group.

If you do not add any hierarchies into your new group, nothing will look different when press OK to save. You have to go into the Administer section to edit/delete.

008 - name and assign hierarchy

Click OK to save the hierarchy group.

009 - ok

Because we did add a hierarchy into this group, we can see the group in the hierarchy section.

Click the plus button next to the group to expand.

Now we can see the contents of the Account_Hierarchies group.

011 - expanded group

By default, the view will display your hierarchies grouped by Hierarchy Group.

If you don’t wish to view the groups but only want to see the hierarchies, click on the drop down next to Group By -> Click on [None].

012 - group by none

Now we see only hierarchies.

013 - no grouping

Nodes

A Hierarchy in DRM is made up of nodes. A node in DRM is the equivalent to a member in Essbase.

The following is taken from Oracle’s online DRM User Guide Documentation…

Master or reference data records used to describe, qualify, or summarize transactional data in a subscribing system are managed in Data Relationship Management as nodes. For example, within a hierarchy that represents an organizational structure, a node might represent a department or a cost center. Nodes in a version can have properties called global node properties. Nodes in a hierarchy can have hierarchy-specific properties called local node properties.

Within a version, a node may be a part of more than one hierarchy. A node can have many user-defined properties that store information about the node and control the use of the node within the information systems of an organization.

The following terms are used to define the position of a node and behavior of the node within a hierarchy:

Leaf – A node that cannot have children
Limb – A node that can have children
Child – A node directly below another node (if B is directly below A, B is a child of A)
Parent – The node directly above another node (in the previous example, A is the parent of B)
Descendant – A node below a specified node (including children and children of children)
Ancestor – All nodes between a node and the top of the hierarchy (including the parent, the parent of the parent, and so on)
Sibling – All nodes that share a parent node in a hierarchy
Orphan – A node not assigned to a hierarchy
Global Node – A node within a version
Local Node – A node within a hierarchy

We’re not going to spend too much time on nodes since the bulk of this series will be concerned with working with nodes, but we’ll quickly review how to manually add, delete, and remove nodes.

Log in to DRM and open the Browse section.

You can double-click on the hierarchy to open, or click once to select and click the Open button.

001 - open hierarchy

When we created this hierarchy, we had to define a Top Node, which we named “Account” the same as the hierarchy.

We’ll create a few nodes, 1 limb and 2 leafs in a very simplified balance sheet structure.

Right-click on the Top Node Account -> mouse-over New -> Limb

002 - new limb

I naming the node “Balance_Sheet” and am opting not to give the node a description.

Click OK.

003 - balance sheet

Now right-click on the node we’ve just created -> New -> Leaf

004 - new leaf

I am naming this node “Assets” and leaving the description blank.

Click OK.

005 - assets

Again right-click on “Balance_Sheet” -> New -> Leaf

006 - new leaf

I am naming this node “Liabilities_Equities” and leaving the description blank.

Click OK.

007 - liabilities equities

Notice that the icon for “Balance_Sheet” is different than that for “Assets” and “Liabilities_Equities”. This is a visual cue as to the node’s status as either a limb or a leaf. Remember in the beginning of this section that a limb can have children and descendants versus a leaf that cannot.

Now that we’ve added a few nodes manually, we’ll learn how to delete and remove nodes, and the difference between the 2.

Right-click on “Liabilities_Equities” -> Delete

008 - delete

You’ll be prompted to confirm this action.

Deleting a node is more straightforward in that this node will now be gone from this version in all hierarchies that it may have existed in (remember that a node can exist in multiple hierarchies). This action is irreversible so make sure you really want to delete.

Click Delete Node.

009 - confirm delete

Next, right-click on “Assets”-> Remove

010 - remove

You’ll be prompted to confirm this action.

Removing a node does not delete it, but rather removes this Local Node from its parent. If this node does not belong to any other parents (which this one does not) it will be become an orphan node. An Orphan node still exists in the version, but is not included in any hierarchies. Rather it is floating around in the ether, so to speak.

Click Remove Node to confirm this action.

011 - confirm remove

Navigate back to the Browse section.

Click on the Orphans tab.

You can see that the “Assets” node still exists and could be re-inserted to a hierarchy. “Liabilities_Equities” on the other hand is gone forever and would have to be re-created. We didn’t have any custom properties populated for this node so we didn’t really lose any information, but in a real-world application, you may very well lose some important information. For that reason, removing a node is a safer option and you should exercise extreme caution when deleting nodes.

012 - orphans

Property Definitions

The following is taken from Oracle’s online DRM Administrator Documentation…

Property definitions are used to manage the attributes of versions, hierarchies, and nodes in Data Relationship Management. Properties can store a variety of different data types including text, numeric, date, and references to other data objects. Properties can store explicit values, use inheritance to automatically assign values to descendant nodes, or be calculated based on a formula or lookup table. Property categories can be used to group and organize properties into related sets to simplify their usage and control user access.

System-defined properties that are available by default are used with standard product functionality. User-defined property definitions can be created by application administrators to manage additional attributes that are necessary to support business or system integration requirements.

In this post, we are going to build some properties used to manage the attributes for an Essbase database. For example, we are going to load property definitions to manage Member Name, Data Storage, Consolidation, Two Pass Calculation, etc. In a later post we’ll reverse-engineer the Sample.Basic Essbase application, and will build our first version in DRM.

Namespaces

The following is taken from Oracle’s online DRM Administrator Documentation…

Namespaces are used in property definitions to avoid conflicts where properties from different sources have similar names and need to remain separate for data integrity purposes. Property names are differentiated using a namespace prefixing convention.

There are special rules in Data Relationship Management that apply to namespaces to ensure that conflicts do not occur:

  • System-defined properties use the “Core” namespace.
  • User-defined properties use the “Custom” namespace.
  • Other namespaces are reserved for use by Data Relationship Management application templates for other Oracle products.

Two of the most commonly used system-defined properties are “Core.Abbrev” (which has the label “Name”) and “Core.Descr” (which has the label “Description”), which are used to store the node name and description respectively.

Sub-Namespaces

Property names can be further divided to help organize your properties. In a later post we are going to create a handful of properties that are custom but still applicable to the entire application in the “Custom.DRM” namespace. For example, we’ll have a property called Custom.DRM.NodeType that will be used to manage the node types in our application. We also recommend create a sub-namespaces for the various downstream applications that DRM will be publishing to. For example, we’ll have properties specific to the Sample.Basic application that will live in the Custom.Sample sub-namespace.

Create a new Property Definition

Property Definitions are managed in the Administer section of the DRM Web Client.

Click on Administer.

001 - administer

When you expand on Property Definitions, the properties will be grouped together by Namespaces. We have not yet created any additional properties, so we are only seeing the Core namespace, which contains system properties.

002 - prop defs

Expand on Core to see the system properties.

This is actually a small subset of all the System Properties. We are going to modify 2 of these.

003 - small subset of core

Double-click on DefaultDisplayBy to configure this system property.

We’ll discuss what these attributes mean shortly, but for now notice the Property Level is hierarchy. That means this property is relevant to only hierarchies, not versions or nodes.

This property controls the default properties you will see when you have opened a hierarchy and are viewing the nodes therein. By default, you will only see the Node Name. We are going to modify the default behavior so that we see both the Node Name & Description. In actual practice, you should add whichever properties you find relevant, including Custom properties that you’ve created.

Click the button next to Default Value.

004 - default display by

Locate Name & Description. Double-click them to move them into the selection pane on the right.

Click OK.

005 - add properties

Click Save.

Click on the x to close this tab.

006 - save

Next, double-click on EnableSharedNodes.

This is also is a Hierarchy Property Level.

Change the Default Value to “True”.

Making this change will set the default for all hierarchies to “True”. However, we also need to enable shared node maintenance at the application level.

007 - enable shared nodes

Click Save.

Click on the x to close this tab.

008 - save

Before we move onto creating our first custom property definition, we are going to enable shared node maintenance for this application.

Shared Node Maintenance maintains the relationship between “Primary” nodes, and their “Shared” counterparts. A “Primary” node can have an unlimited number of “Shared” iterations. Shared nodes can be identified by the suffix of the node name.

For example, we might have an account node acct!400120, which has a corresponding shared node acct!400120:Shared-001. Updating a global property on a “Primary” node will also update it’s “Shared” iterations. There are 5 other System Preferences related to shared nodes shown below, for which we’ll keep the default values.

  1. SharedNodeDelimiter – Separates the node name from the shared information, the default value is a colon “:”.
  2. SharedNodeIdentifier – This is a string of text used to identify a shared node, the default value is “Shared”.
  3. SharedNodeNamingType – This can be set to “Prefix” or “Suffix”, with the latter being the default value.
  4. SharedNodeSequenceLength – This can be assigned with an integer, with the default value of “3”. For example, in the example above, the sequence is “001”.
  5. SharedNodeSequenceSeparator – This is the delimiter between the SharedNodeIdentifier and the Sequence (i.e. “001”).

In the Administer section, expand on System Preferences.

Locate and double-click on SharedNodeMaintenanceEnabled to open for edit.

Change the value to “True”.

009 - Shared Node Maintenance

Click Save.

010 - change to true

This System Preference change will not take effect until the application is restarted as indicated.

To restart the application, open the Configuration Console.

*Make sure you have all of your required versions saved before doing this.

Right-click on your application -> Stop Application.

Wait until the icon change, then right-click on the application again -> Start Application.

011 - running value

Okay, now back to property definitions. To begin, we are going to manually create one and review the attributes of a property. In the next post, we are going to load the properties we need to manage Essbase applications using the Migration Utility.

Log in to the Web Client -> Click on Administer if you are not there already.

Click on New -> Property Defintion.

012 - create new prop def

A new tab will open and this is what you will see.

013 - new prop def

To begin, I’ve entered the Name to be “TestProp”. Once you move to another field, the Label will automatically populate with whatever you’ve entered for the Name.

Notice now the Fully Qualified Name is “Custom.TestProp”. Where “Custom” is the Namespace.

The Property Level field is a drop down with 4 values. Almost all of the properties you will create will either be “Global Node” or “Local Node”. To understand the difference between these, you need to first understand that a node can exist in more than one hierarchy, and within a hierarchy more than once (with shared nodes). A “Global Node” property means that property is set once and then is applicable throughout that version whether it exists in multiple hierarchies, or has several shared iterations. A “Local Node” property can be different and is specific to that node in that location. In Essbase language, Consolidation is an example of a “Local” property because a shared node can have a different setting from its base member. Alias on the other hand is an example of a “Global” property because a shared member cannot have a different value from its base member.

If you are still confused as to the difference, don’t worry; it took a little while for me before the light bulb went on.

014 - test prop

The next attribute is Property Type, which has the available values “Defined”, “Lookup”, and “Derived”. Here’s a brief of defined properties, we’ll discuss lookup and derived shortly.

A defined property can have a default value, or can be “overridden” in which case a record will be stored in a database table. Node Name & Description are 2 system properties that are defined and do not have a default value, but rather will be overridden for each node that exists.

015 - prop type

Each property definition has a data type with the most common being “String” and “Boolean”. We’re not going to review all of the data types now, but will discuss in more detail when we start configuring the properties.

016 - Data Type

A property definition can also have a list of acceptable values. Most properties aside from those that are free form like Node Name & Description should have a list of values associated to help maintain data integrity.

For example, consider the Data Storage property in Essbase. The acceptable values are “Store”, “Dynamic Calc”, “Dynamic Calc and Store”, “Label Only”, and “Never Share”. It wouldn’t be appropriate to allow your users to enter text for this attribute because inevitably there would be inconsistent values, and your interface to Essbase would fail.

To create a list of values, first enable the List checkbox, which will enable you to open the List Values tab. Click on the Add button to create your first value. Once created, you can edit the values, or re-order them.

017 - list values

The Property Type “Derived” allows you to build dynamic business logic into your properties. Derived properties can simplify application maintenance and reduce the overall storage of your application by reducing or eliminating the need to store (override) additional values.

When you select “Derived” for your Property Type, a new drop down will appear with the available options “Formula” or “Script”. Prior to this release of DRM, using the legacy formulas was the only option. DRM formulas are very Excel-like in that your entire formula will effectively span a single line. For example, this is an if statement with parameters…

If(condition, true command(s), false command(s))

Beginning in this release of DRM the “Script” options allows you to use JavaScript for your business logic. For those of you with experience writing JavaScript in the context of a webpage, keep in mind that the Document Object Model (DOM) of a browser is non-existent here, but rather DRM has its own object model that allows you to access the various DRM objects. JavaScript offers significant performance improvement over the legacy formulas, and in my opinion is easier to read as your scripts can contain carriage returns and indentation. This is a large topic, which we’ll discuss in more detail in a later post.

A derived property can be overridable or not. If a derived property is not overridable it is completely dependent on its logic to return a value. If it is overridable, your users can override the business logic to set exceptions.

018 - derived props

The Property Type “Lookup” must have an external Lookup Property with which it will perform a basic find & replace with the value of that property. When you select the Property Type “Lookup”, the Lookup Property drop down will appear, and the Lookup Table tab will be available.

With a lookup property, you’re saying, take this lookup property, if the value is A, replace it with B. If the value is C, replace with D. We’ll use a lookup property for the code to send to Essbase for data storage. For example, if we wish to set a member to be dynamically calculated, we have to send Essbase a string value of “X”. However, in our DRM application, we are going to use the “friendly names” for these attribute values (Store, Dynamic Calc, Label only, etc.). In this example, our lookup table will look like this…

Store S
Dynamic Calc X
Dynamic Calc and Store V
Label Only O
Never Share N

019 - lookup props

When creating a new property or modifying an existing, you need to explicitly Save your work.

020 - save prop

After saving this property, go back to the Administer section and expand on Property Defintions.

As you can see, in addition to the Core namespace, we now have the Custom visible as well. In the next post we are going to build some properties from an Oracle application template using the Migration Utility. Once complete, we’ll also have the Essbase namespace visible.

021 - custom namespace


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 2”

Leave a Reply

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