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

This entry was posted in Technical and tagged , , , , . Bookmark the permalink.

Comments are closed.