Technical

In this blog, we will have a look at Scripts which were called automators in some earlier versions. Scripts in Oracle Hyperion DRM (Data Relationship Manager) are a simple tool that execute sets of simple instructions in one bulk load. For example, if you need to change the value of one property against hundreds of nodes, the simplest way to do this is to create a Script. An effective way to increase productivity through the use of automation.

The basic rule in a script is that each line is a specific action. Each line also needs to be divided into columns by a delimiter. The first column should contain the action code. Oracle Hyerion DRM supports an important list of action codes that allow you to carry out a lot of different actions such as change property (“ChangeProp”), add a node (“Add”), insert a node (“Insert”), remove a node (“Remove”) and so on. The full list of actions available is described in the Oracle Hyperion DRM user guide. The following columns will contain the parameters for the action.

As an example in this blog, I am going to add a node into a hierarchy then change its description. This will require two actions and my Script will need two lines:

1. First add the node using the “Add” action code. This code takes 5 parameters:
   1. Version – This is the name of the version in which the node will be added.
   2. Hierarchy – Name of the hierarchy in which the node will be added.
   3. Node Name – Name of the node to be added.
   4. Parent name – Name of the parent of the node.
   5. Leaf – Flag indicating if the node is a limb or a leaf.
   6. In the second line we use the action code “ChangeProp” to modify the description. This action also takes 5 parameters:
      1. Version
      2. Hierarchy
      3. Node Name
      4. Property – Name of the property to be modified.
      5. Value – New property value.

So the file looks like this:

You can build this file using a simple text editor or Excel. In Excel, just save the script as a .csv file.

To execute the Script, navigate in Oracle Hyperion DRM to the Script section.

Specify the file characteristics like the column order, the column delimiter or the encoding in the “Source” section. Then browse to your file and press “Load”. Once the file is loaded, you will see the list of actions Oracle Hyperion DRM has recognized in the bottom section. Any action code that was not recognized will be ignored.

In the menu called “Script” there are some additional options. For example, you can substitute the versions in the Script for a different version present in Oracle Hyperion DRM. You can also select only a subset of lines you which to execute. Once you are ready, press the play button. This will execute all the selected lines.

Once the Script has been executed, you can see the result in the “Status” and “Return Value” columns.

“Status” will flag any error and “Return Value” will return the error message if there was an error or any other return value depending on the action code. These results can be saved by navigating into the Script menu then selecting download then the format.

While this was a simple example we hope that it was clear and useful. If you have any questions you know where we are and we would be delighted to help.

In last week’s first post on importing into Oracle Hyperion DRM (Data Relationship Management) we looked at how to build a file to import master data into Oracle Hyperion DRM. Now we will create the import in DRM. Browse to Oracle Hyperion DRM, then go to imports and click on “New Import”.

The first step when users configure Oracle Hyperion DRM to import requires selecting the file to be imported, specifying the character encoding, the field delimiter and the sections present in the file. You can also customize the headers for each section.

The second step when customising Oracle Hyperion DRM for imports is deciding what style to use for the import.

In this step you can configure the import so that it populates sort relations, leaf or limb as well as customising how it handles duplicate nodes. I will spare you too much details for this post. You can always play around with these options to understand how they impact the result of your import. So for our example, we will elect to leave everything to default and go to the next step.

Next you must select the columns for each section. In this example, I have a hierarchy section and a relation section so I must configure the columns for these two sections. First select the hierarchy section.

In this file I have three fields, the hierarchy name, the top node and the hierarchy description. So I select these columns by placing them in the box on the right. (Columns noted with a star are required in any hierarchy sections). The second section is the relation section. In my file, I had three columns: parents, child and description. So I select these columns as shown here:

Please note you need to be careful with the order of the columns, they need to be selected in the same order as they appear in the flat file.

The fourth configuration step is the filters. Filters will allow you to define the behaviour for properties. It allows you to configure the import to skip blank values and skip default values if that is required. For this example, I am going to leave all the filters to the default values.

The final step is to define the target. As I have no version section, I must define the target version name and description. In this step you can also define the “Max Errors” number. This number defines how many errors can occur before the import is aborted. I’ll leave it to 20.

Now you have defined your import. You can save it using the usual save button. Execute it with the green arrow. Once you execute the import, Oracle Hyperion DRM will show a log for the import. The log will return an error message for each error, which allows you to correct your file or import.

Now you can navigate to “Browse” and see your new version with the imported hierarchies.

These last two posts on the basic requirements for Importing, intended to give you the basics on how to import into Oracle Hyperion DRM but there is much other functionality that it is worth exploring and utilising in your deployments of Oracle Hyperion DRM. If you would like to Seismi to look at anything specifically in future blog posts then please let us know.
 

So now you know how to extract or export data from Oracle Hyperion DRM (Data Relationship Management) a logical next step is to understand how we get data into Oracle Hyperion DRM. Imports and scripts (or automators) are the two main tools available to get large amounts of data into Oracle Hyperion DRM. Imports are the best method if you need to import complete hierarchies with properties. Scripts are better to load specific property values, or changes, to a hierarchy that already exists. We will focus on imports in this post and then we will look at scripts in a future post.

To build your import you must:

1. Set the flat file in the correct format;
2. Set up the import;
3. Execute the import;
4. Check the log; and
5. Save the resulting version.

Getting the flat file in the correct format is quite simple. You can use whichever file editing tool you prefer, such as notepad++ or notepad. The first thing you must choose is your separating character. The typical choices are the comma or semi-colon. You can also choose tab, or any special character. Oracle Hyperion DRM is quite flexible so you should focus on choosing one that is never used in the masterdata you are importing. Second, you must start building your sections. Your flat file can be divided into up to 5 sections. Sections are delimited by headers.

The five sections that can be included in the import are:

1. Version – This section should contain a line with the version name and any property value to be associated to the version (default header: [version]);
2. Hierarchy – This section should contain a line for each hierarchy to be created. The required information is at least the name and the hierarchy top node (default header: [hier]);
3. Nodes – This section allows you to import a list of nodes and values for global properties (default header: [node]);
4. Relation – This section should contain the relation between nodes, in a parent child format and any additional property values. Any node in the child column that does not exist will be created (default header: [relation]); and
5. Hierarchy Nodes – This section should contain nodes to be imported and associated to a specific hierarchy. This will allow you to import nodes and the value for local properties (default header: [hiernode]).

A simple import file can contain only two sections: the hierarchies and the relations. So here is an example of a very simple import file:

[hier]

My hierarchy;TopNode;This is a new hierarchy

[relation]

TopNode;A;Node A

TopNode;B;Node B

B;B1;Node B1

A;A2;Node A2

A;A1;Node A1

This import file will create a new version (the name of the version will be required during the import as it is not specified in the file) with one hierarchy called “My hierarchy”.

In our next technical blog we will continue to explore how to import and specifically how to import the file we have just created.

In last week’s post we introduced the export feature for Oracle Hyperion Data Relationship Management (DRM).  Once you have configured your source, style and filter, you can select the columns of your hierarchy exports.

You can define the columns by selecting properties. The export will return a line for each node that meets the filter criteria and in each line, it will write the value of each selected property. In this example, we have selected the two basic system properties Name and Description.

There are some additional options per column in the tab Column Options.

The three options are:

1. Pivot – If a property contains a comma separated list of values, pivoting the column will repeat the line for each value of the list;
2. Skip Defaults – If a property has a default value that should be replaced by a blank, you can use this option; and
3. Primary Key – DRM will validate that the property has no repeated value. This should be used if you are exporting to a table that has a primary key.

The final configuration tab is called “target” and allows targets to be selected.

You can select one of three targets:

1. Client File – Oracle Hyperion DRM will send the result to the client that triggered the export;
2. Database Table – Oracle Hyperion DRM will write the results to a database table according to the connection, the table name and the column. You can also configure Oracle Hyperion DRM to delete the previous content of the table or partially delete the content if necessary; and
3. Server File – Oracle Hyperion DRM will write the results into a text file in a folder on the server. You will also need to configure a connection to the folder and set the file name. The other options are the same as for the client file.

In our case, we will use the client file. We would recommend that you always run an export to a client file first to review the results before writing to databases or server folders.

The options when writing to a file are:

• Column Headings – this option will add a line at the top of the file with the name of the columns;
• Quoted String – puts text in columns into quotes;
• Fixed Width – sets the width of the columns;
• Character Encoding – sets the encoding standard for the file;
• Replace Options – offers the possibility to replace any character with another during the export process;
• Header – the text written in the header section will be added at the top of the text file;
• Footer – the text written in the footer section will be added at the bottom of the text file;
• Field Delimiter – choose the character that Oracle Hyperion DRM will insert to separate columns; and
• Record Delimiter – choose the character that Oracle Hyperion DRM will insert to separate records. The default is the line terminator CRLF which means that Oracle Hyperion DRM will start a new line for each record.

Now that we have configured everything, we can run the export. As we created a client file export, Internet Explorer will ask you where you want to save the file that was generated by the Export.

Save it and open with Textpad or Excel to have a look at the results. Exports do not modify data in Oracle Hyperion DRM and by exporting to your client file, you are not affecting any system, so you can play with the export types and options without any wider consequences!

In our next technical post we will talk about importing data into Oracle Hyperion DRM. If you would like to explore any of these points in more detail then do reach out.

In our last technical post we saw how to create a query. We are now going to look at creating exports so that you can extract masterdat, which is a large topic that will require a couple of posts. Technically, exports are the tool you must use to send the masterdata out to the other systems. Oracle Hyperion DRM (Data Relationship Management) exports can only write to database tables or text files, so you should consider this when designing your solution.

Let’s start by opening Oracle Hyperion DRM Data Relationship Management (DRM) and navigating to the “Exports” section of the home tab. You will see some export options and a list of saved exports. As there were with queries, exports are grouped by access level (user, standard and system). In the following screenshot, there are no exports yet.

So we can create an export by clicking on “New export”.  Oracle Hyperion DRM will immediately start by giving you the choice between different export types.

The most useful and the one we are going to look at in more detail is the Hierarchy export. This feature allows you to export nodes and property values in a table format according to a given query. Selecting the Hierarchy Export option and Oracle Hyperion DRM opens a new tab. As for queries, you will notice the “save”, “save as” and “run” buttons on the top and some configuration tabs below.

The first configuration tab is the source. In this tab, you must select the version that will be the source of the masterdata, then the hierarchies and top node. You can select as many hierarchies as you need. Remember that the export will only return masterdata from the selected hierarchies.

The second configuration tab is the style. In this tab you can tweak some of the export options like only consider leaf or limb nodes. Leave these options as they are by default. The next tab is the filter tab.

The first filter you can set is a validation. This is a feature that justifies a blog of its own and we will do this this shortly. The second filter is a property query. You can select a query you have saved or create a new query. Have a look at our post on queries if you haven’t seen it yet. If you do not include a query, the export will return all the nodes of the hierarchy. If you include a query, the export will only include the nodes that respect the query criteria. Finally, if necessary you can also use a text file to include or exclude nodes.

We’ll build on this introduction to Exports from Oracle Hyperion DRM in the next blog post. In the meantime you know where we are if we can help.