Monthly Archives: January 2012

Oracle Hyperion DRM Basics | Queries

We mentioned last week that we were going to work on exports next. However, before using export you must start to understand the use of queries. So this week, we are going to have a look at property queries.

A property query is as its name indicates a tool to query the hierarchies according to properties. I’m sure I don’t need to explain how this can come in handy.

To start working with queries, navigate to the query page of the home tab. The home tab will show you a list of saved queries (if you have any) and some options like “New Query” or “Open Query”. If you see some saved queries you will noticed that they are grouped by access level. There can be up to three groups:

  1. User – Objects under this group are specific to the user. This means that any other user will NOT be able to see, execute or modify it;
  2. Standard – Objects under this group are standard to all users. They are shared; or
  3. System – Objects under this group are only available to administrators.

You will see the same type of groups in exports, blends or imports.

 

 

 

 

 

 

 

 

 

 

 

To start creating your own query, click on “New Query”. A New Query tab will appear. On top of the tab you will see three buttons:

Save – Save your query;

Save As – Save your query under a new name or group;

Run – Run the query and retrieve the results.

Under these buttons are four tabs. These four tabs are the four steps to defining your query. First, define the source. Select the version, the hierarchy and the top node to define the scope of your query.

Then select the style. You can select to see the results in a list, by marking the nodes in the hierarchy, or both.

 

 

 

 

 

 

Then comes the useful part: the filter. Use this tab to define the criteria of your query.

Click on the “Add” button to insert the first line.

For simple queries, focus on the columns “Property”, “Operator”, “Value” and “Join”. The property column allows you to select the property you want to filter on; then select the operator and the value. For example, by selecting the system property “#Children” (calculates the number of children), the operator “Equal” and entering the value 1, DRM will return only the nodes that have only one child.

Operators available for the query depend on the type of the property selected. Independent of the property, the available operators are:

  • Equal – True if the value stored in the property is equal to the value in the query;
  • NotEqual – True if the property stores a different value;
  • GreaterThan – True if the property stores a value greater than the value of the query;
  • GreaterThanEqual – True if the property stores a value greater or equal than the value of the query;
  • LessThan – True if the property stores a value lesser than the value of the query;
  • LessThanEqual – True if the property stores a value lesser or equal than the value of the query;
  • In – True if the value stored in the property is one of the values stored in the list of values in the query (values should be separated by commas);
  • NotIn – True if the value stored in the property is not one of the values of the list;
  • Contains – True if the property value contains the value of the query;
  • NotContains – True if the property value does not contain the value of the query;
  • Above – True for all nodes that are above a node that has the value stored in the property equal to the value in the query;
  • NotAbove – True for all nodes that are not above a node that has the value stored in the property equal to the value in the query;
  • Below – True for all nodes that are below a node that has the value stored in the property equal to the value in the query;
  • NotBelow – True for all nodes that are not below a node that has the value stored in the property equal to the value in the query;
  • IsBlank – True if the property is blank;
  • IsNumeric – True if the property is numeric;
  • IsNotNumeric – True if the property is not numeric;
  • IsNotBlank – True if the property is not blank;
  • LenEqual – True if the length of the string stored in the property is equal to the value of the query;
  • LenNotEqual – True if the length of the string stored in the property is not equal to the value of the query;
  • LenGreaterThan – True if the length of the string stored in the property is greater than the value of the query;
  • LenGreaterThanEqual – True if the length of the string stored in the property is greater or equal than the value of the query;
  • LenLessThan – True if the length of the string stored in the property is lesser than the value of the query;
  • LenLessThanEqual – True if the length of the string stored in the property is lesser or equal than the value of the query; and
  • IsDefined – True if the property is defined. A property is defined if a user has entered a value against this property, even if it is a blank.

The “Join” column will allow you to specify the join between two lines. The join can either be “And” or “Or”.

In this example, the query will return the nodes that have one child or two children. You will see a summary of your filter below the criteria table.

 

 

Below the filter, some additional options are available. Usually, queries are defined to return only the nodes that match the filter, but you can change the query to retrieve the nodes and their ancestors or the nodes and their descendants.

 

 

 

 

The final tab is the “Columns” tab. It will allow you to define the columns you want to see in the list result. This isn’t useful if you chose to see the results marked in the hierarchy.

Once you have selected your columns, you can save the query or execute it immediately without saving.

If you choose to execute without saving, you can always return from the results to the query definition by using the
return button.

In this example, I chose to see the data both in a list and marked in the hierarchy. So I have two tabs presenting the results: the list and the tree. In the list, I can always press the “Go” arrow for a node to open the hierarchy and expand it to that specific node.

Quite a lot of detail this but important stuff. Next week we’ll have a look at the exports and you’ll see why talking about queries first was necessary. In the meantime you know where we are

Tagged , , , ,

Video | Using Masterdata Governance to overcome Business Challenges (Part 1)

This week Ross Gilfillan will be introducing the start of a series of video blogs that explore how masterdata governance, using Hyperion Oracle’s Data Relationship Manager (DRM) tool, can be used to overcome a number of common business challenges.

These challenges include ongoing governance of disparate and distinct chart of accounts; integrating acquisitions; implementing and updating charts of accounts; and mapping.

Over the next few weeks Ross will look in detail at how Oracle Hyperion DRM can be used to govern Oracle’s E-Business Suite, Hyperion Financial Management and Hyperion Essbase.

Please do contact us if you would like to explore these topics in more detail. Ross’s contact details are also included at the end of the video.

Tagged , , , , , , , ,

Oracle Hyperion DRM Basics | Node types

Now we are getting into what makes Oracle Hyperion Database Relationship Management (DRM) so flexible: node types. As we explained in an earlier blog, nodes in a hierarchy can be assigned a node type. Node types allow you to filter properties and validations that will be available for the node. Node types will also allow you to assign a different icon to your nodes.

There are three steps that you need to follow to assign node types. First you create the node types you need; second you create a property to assign the node type to a node, and thirdly, you associate this node type property to the relevant hierarchies.

1. Create a node type

Log in to Oracle Hyperion DRM then navigate to Administer > New >  Node Type. Oracle Hyperion DRM will open a tab similar to the following:

 

You must give the node type a name and a description. You can also assign a glyph to the node type. The glyph is an icon that will be shown against the nodes. In this example, I will not be associating any glyph to the node type, but to do so, all you have to do is load the icon by creating a new glyph then select it in the node type definition.

 
 

 

Once the node type has a name and a description you can associate properties and validations using the lower part of the screen. Only the properties that are associated to the node type will be visible against a node with that node type. Similarly, only validations associated to the node type will be executed against nodes with that node type. Once you have associated the properties and validations, you can save the node type.

2. Create the property to associate node types to nodes.

In the following example, we are just going to create a simple defined local string property with a list. Let’s say we only have two node types, “Account node type” and “Other”, we create and configure this list property with only two valid values “Account node type” and “Other”. This way, we are sure that users will only select a valid node type.

 

Note: The property must always store the exact name of the node type. If the property stores a value that does not correspond to any existing node type, Oracle Hyperion DRM will assign the default node type to the node, so all properties and validations will be assigned to the node.

Since we have just created this property and we want it to be visible against the node type we created, we have to go back to our node type definition and associate it to the node type.

 

3. Associate property to a hierarchy

Navigate to the version and right click on the hierarchy then select properties.

 

In the system property labelled “Hierarchy Node Type”, select the property you have just created then save.

 

 

 

 

 

 

 

 

 

 

Now nodes in the hierarchy will assume the node type you select in this property.

Hopefully this will have helped you understand how to work with node types but do contact us if you want any further clarification. In our next technical blog we will review exports.

Tagged , , , ,

Oracle Hyperion DRM Basics | Defining a Property

Firstly Happy New Year and we hope that one of your New Year’s resolutions will be to embrace and deploy Oracle Hyperion DRM!

We want to start it by continuing with our technical blog series and to take a look at Properties within Oracle Hyperion Database Relationship Management (DRM).

As you will have seen in our previous technical post when you are implementing Oracle Hyperion DRM, your business masterdata will be stored as nodes within hierarchies.  Each node, hierarchy and version can have any number of attributes, which are stored as properties in Oracle Hyperion DRM. For example, if your core business concept was customer, then each customer’s address could represent an attribute, or property, of the customer. Off the shelf, DRM is configured with some core properties like name, description and level. However, the implementation process will define numerous custom properties according to the business requirements.

The previous technical post indicated how you can navigate in your hierarchies to see the properties of any node, hierarchy or version. We are now going to focus on the definition of these properties.

To create properties, navigate to “Administer”, then “New” and finally “Property Definition”. The screen below should appear!

Here is a list of the information you will require when creating a property:

1. “Name” – the system name for the property. It will not be visible to end users and should be unique to this property;

2. “Label” – the identifier that end users will see when they use DRM. So, while the name must be a unique identifier, the label should be informative and help the users understand the information they should enter;

3. “Description” – the purpose of the property;

4. “Data Type” – this permits you to select the type of data to be stored

5. “Property Level” – this will allow you to define the level at which the property will be used. The options are:

a)     Version – use this if you need the property to be global for the version;

b)     Hierarchy – use this if you need to store an attribute for the entire hierarchy;

c)     Global Node – use this option if you need to store an attribute of a node that should always be the same wherever it is. For example, the description is a global property. If you insert the same node in three different hierarchies, the node will always have the same description; and

d)     Local Node – use this option if you need to store an attribute that is unique to a node in a given hierarchy. For example, the node’s parent is a local property. The same node in two hierarchies can have two different parents.

6. “Property Type” – this allows you to define how your property should be populated. There are three options:

a)     Defined – users will define manually the value of this property;

b)     Lookup – the property will be populated according to another property according to a simple lookup table. If you select this option, the lookup table tab in the lower part of the screen will be enabled; and

c)     Derived – this property will then be populated according to a formula you will have to define in the parameter tab. We will see formulas later in posts dedicated to formulas.

7. “Default Value” – give your property a default value if necessary;

8. “Minimum Length” – set the property minimum length;

9. “Maximum Length” – set the property maximum length;

1o. “List” check box – if you select the “List” check box, the property will not be free form. Users will have to select a value from a list of valid values. The tab “List Values” will be enabled and you will be able to create the list of valid values;

11. “Inherited” check box – a defined property can be set to inherit the value across a hierarchy. So if a user sets the property against the parent node, the child nodes will automatically inherit the same value except if the property is manually overridden against a descendant; and

12. “Overridable” checkbox – this check box is only available for derived properties and indicates if end users can manually override the value of the property at lower levels of the hierarchy.

Finally, the bottom level tab “Categories” allows you to assign the property you are creating to property categories.

In the next technical blog we will introduce node types, which can be used to allow you to control what users see in Oracle Hyperion DRM. Until then please do not hesitate to reach out if you want to investigate Oracle Hyperion DRM’s potential.

Tagged , , ,