By TopDown Team
April 17th, 2015
In part 4 of this blog series, we’ll learn how to use Imports and Blenders in DRM to create a new version containing metadata from the Sample.Basic Essbase application.To read part 1, click here; to read part 2, click here; and to read part 3, click here.
The following is taken from Oracle’s online DRM User Guide Documentation…
Using Data Relationship Management, you can import data extracted from external systems, relational database tables and views, or manually created by users using a multi-section, text file format. Imports are always performed on new, empty versions created as part of the import process. This approach allows you to verify that data was imported correctly and resolve any issues before saving the data to the database or merging with data in other versions. After you verify the data, you can use the blender to combine imported data with data originating from other sources.
The content for our Import will be a series of text files created by extracting and transforming the metadata from Sample.Basic. Each time you run an Import, a new version is created. This is a safeguard so you can review the result of your metadata without affecting a pre-existing version.
An Import can have up to 5 sections, though not all are required. In our example, we are going to be using a Version, Hierarchy and Relationship section. Here’s a brief description of each.
- Version – This section can be used to populate Version-level properties for the version being created by your Import. Only the Version Name property is required.
- Hierarchy – This section creates the hierarchies (and populates Hierarchy-level properties) that will exist in your new version. The Hierarchy Name & Top Node properties are required. In Essbase-speak, the Top Node is similar to the dimension root in an outline, it is the first parent from which all limbs and leaves will be created.
- Node – This section creates nodes in a new version, and can populate Global-level node properties only. The only required property is Node Name.
- Relationship – This section creates nodes and structures your hierarchy (based on Parent-Child relationships). Both global and local-level node properties can be populated. We’ll be using this section to create our nodes in the correct structure and populate all properties in a single pass. There are 2 required properties, Parent Node Name & Node Name.
- Hierarchy Node – This section can assign local-level node property values specific to a hierarchy. For that reason, if using this section, you must also have a Hierarchy section in your file. The required properties are Hierarchy Name & Node Name.
Extract & Transform Sample.Basic
Metadata for Essbase applications is stored and managed in a proprietary file format called OTL (for Outline). This is not the type of file you can open in a text editor, copy/paste and be on your way, and we certainly don’t want to fat finger all the members and properties, as we would undoubtedly have some errors.
We’ll be using a wonderful and free utility called the OlapUnderground Outline Extractor to create a text file for each dimension in the outline. The Outline Extractor was originally developed by a group of anonymous Essbase developers who called themselves OlapUnderground. Today, the Outline Extractor is maintained by a group of volunteers organized by Applied OLAP. Many thanks to all involved in the creation and maintenance of this utility as I have used it countless times. If you’d like to learn more about this tool or download, please go to the following URL…
I’ve opened the Outline Extractor Utility, logged in to Essbase, and have set it to extract all dimensions using pipes ( | ) for the field delimiter.
Click Export to run the extraction process.
The result you can see is a set of text files, one for each dimension and an extra file called DimensionInfo.txt, which is pretty self-explanatory.
I’ve opened each of the text files in Excel in a single document where we’ll perform the necessary transformations to get this metadata into DRM. Columns A, B, and C are Parent, Child, and Alias respectively. Column D has all properties concatenated into a single field.
Our task is to extrapolate these properties into their own fields, because they represent different properties in DRM. For example, the property code ‘X’ means ‘Dynamic Calc’ for the Essbase Data Storage property. For several of the Essbase properties, it is a manner of on or off. For example, Two Pass Calc and Variance Reporting, these properties are enabled or not, and you can tell by the presence of a ‘T’ and/or ‘E’ respectively. Data Storage is a little trickier because you don’t receive a value for ‘Stored’ or ‘Shared’ members. To get these properties assigned appropriately, we’ll use a combination of native DRM functionality and business logic in the Essbase.Bso.DataStorage property.
In another tab, I have created a template for our DRM Import (Relationship section). Even though some of these properties are only relevant to an Account dimension (TimeBalance, TimeBalanceSkip, and VarianceReporting), we are going to use the same format for all of the dimensions so we can use a single DRM Import for all of the import files.
To extrapolate the properties into their own fields, we are going to use the following mapping. We need to search the properties column for these values, and if they exist, replace them with the property value we have in DRM.
I’ve pasted the import file template at the end (right side) of each dimension tab in our metadata document.
You can see for the Parent Node and Name fields I have added the appropriate prefix based on which hierarchy/dimension we are working on; refer back to Part 3 if you need a refresher on node types/prefixing.
For Data Storage we are looking for X, V, N, and O (O as in Oscar, not zero). If none of these are found, we will leave the field empty and then use business logic in DRM to derive to Stored or Shared properly.
For Consolidation, we are searching for the mathematical operator, and then returning the “friendly” name for DRM. For example, instead of “+”, we have “Add”.
Time Balance is an account-only property so is only needed on the Measures tab; it can be left blank for the other dimensions.
Time Balance Skip is also an account-only property and works in conjunction with Time Balance.
Two Pass Calculation is relevant for any dimension (except attribute dimensions).
Variance Reporting is another account-only property.
This last column is actually building our import file (well actually only one section of the import file but we’ll cover that shortly). It is concatenating all of the fields and adding a pipe delimiter.
Now that we’ve completed the content for the [relation] (short for relationship) section of our import files, we need to build the [hier] (short for hierarchy) sections.
If you haven’t noticed by now, I like using Excel to build Import files, in addition to Action Scripts, which we’ll use later in this post.
We’ll be populating 6 properties in the Hierarchy section of our import files; Hierarchy Name, Top Node, Hierarchy Group, Enable Shared Nodes, Standard Hierarchy Sort, and Hierarchy Node Type.
You may have noticed that only the attribute dimensions are being placed into groups, and that they each have their own. To manage the relationship between base member and attribute member (e.x. Product Dim & Ounces Attribute), we’ll be using DRM properties with the Global Node data type. This type of property actually points to another node within the same Version. Placing the attributes in their own Hierarchy Group allows us to force a constraint. For example, we will have a property called Custom.Attr.Ounces used to manage the associations between Product/Ounces. Having the Ounces hierarchy in a Hierarchy Group will limit the user to select a node from only hierarchies in the Hierarchy Group you specify. This data type is also used for multi-currency applications to maintain the associations between Entity/Currency.
This is our empty Import File template, and again, we’ll be creating an import file for each of the dimensions in the Sample.Basic application. Text strings wrapped in square brackets identify the various sections of an import file. The tags you see below are the default, you can change them when you build the DRM Import if you like, but I would recommend keeping the defaults.
For the Version section, we will be populating 2 properties; Version Name & Version Description. You can specify the Version Name directly in the DRM Import, but I am placing this information into the import files so that I can use a single Import for all of the dimensions.
Here’s the finished product for the Year import file. The Version, Hierarchy and Relationship sections do not need to have the same number of columns, as they will be configured separately when we build the DRM Import.
I’ve created an import file for each dimension; we are now ready to build the DRM Import. Again, we could have loaded all dimensions into a single version using a single import file, but this way we will get to learn about blenders as well.
Login to DRM and navigate to the Import section.
For Device, keep Client File selected since we are loading a flat file from this computer.
Click Browse to select one of the import files. It doesn’t matter which one since we are going to run them all through this import, but you have to select one to be able to save the Import.
It is important to pay attention to the character encoding. I am running SQL Server and the default encoding is set to ASCII. DRM is going to default to UTF8, so make sure that your import files are in the correct encoding for your database, and that you select the correct encoding in your Import.
I changed the delimiter to pipe ( | ) since that is what I used in the import files. The sections of the import file don’t need to have the same number of columns, but they do need to be using the same delimiter or else DRM won’t be able to parse your file.
On the right side of this tab is where you can change the tags for the import file sections, but again, I would recommend leaving them default. You don’t need to uncheck any of them even though we aren’t using all 5 sections.
Move over to the Style tab.
I’ve left everything default except for enabling the option to Determine Leaf Nodes at the end of the Import Process.
The right side of this screen instructs DRM how to handle duplicate nodes in your import file. When a duplicate node is encountered, DRM will append a suffix to the node name (for example, product!100-20:Shared-001). In addition to the suffix being added, DRM will also enable the System node property Shared Node Flag.
Move on to the Columns tab, this is where we’ll tell DRM what our import files look like. For each section, we need to tell DRM which properties we are going to populate, and in which order they appear.
For the Version section, I’ve left the default properties, as this will match our import file.
Stay on the Columns tab, but switch the section to Hierarchy.
I’ve added the columns we have in the import file, and confirmed they are in the correct order.
Now switch to the Relation section for the last step on this tab.
Again I’ve added the properties from the import file and confirmed they are in the correct order.
Move to the Filters tab.
Typically you’re going to want all of these options enabled when running an import, but you need to think through it.
The options on the left side tell DRM how to handle empty property fields in the import file. These are all enabled by default.
The options on the right side instruct DRM how to handle property values in the import file that match either the default property value, inherited value, or derived value respectively. These are important because they will help you keep your database as small as possible. Using default values and business logic is very powerful because it can simplify application maintenance and reduce the storage footprint of your database.
Lastly, move over to the Target tab.
For the Version Name, I have just entered File. It really doesn’t matter what you put here since the version name will be coming from the import file, but you won’t be able to save your Import without putting something in this field.
I have enabled the option to Save Version to Repository.
Save the Import, give it a name and a description if you like. For the object access group I have chosen Standard.
Once your Import is saved, go back to the Source tab, click Browse to select one of your import files.
Click run to kick off the import process.
You can see the Import result at the bottom of the screen. If you configured anything incorrectly in your Import, DRM will let you know. The error and warning messages you receive are typically easy to understand and resolve.
One of the more common errors you might run into is if a section in your import file has a different number of fields that what you have in your import.
Without leaving this screen, click Browse again, select the next import file, and then click Run again. Run each of your import files.
Click on the Home tab, and then navigate to the Browse section.
Here you can see all of the versions you just created with your Import. Each of these versions contains only a single hierarchy, so now we need to merge all of these hierarchies into a single version, which we’ll accomplish using a Blender.
First, we need to create an empty Version, which we will blend all of the hierarchies into.
Click on the new version button.
Enter a name and a description, and then click OK.
I’ve chosen “Sample_Basic_” and today’s date.
Navigate to the Blender section.
Click the new Blender button.
On the Source tab, select one of the versions you imported for the source. The target will be the empty version we just created.
The option to Blend all Hierarchies is selected by default. We only have one hierarchy in this example so it is not relevant, but if you had multiple hierarchies and only wanted to blend a subset of those, you would uncheck that option and then select which hierarchies to blend.
Move to the Style tab.
Enable the option Allow Hierarchy Creation. This is required since the hierarchy does not already exist in our target version (because it is empty).
Move to the Filters tab.
You need to enable the Process Inserts option or the blender will not be able to create any nodes in the target version. The other options are relevant when you are blending together two hierarchies that already exist. In those cases, make sure you have well-defined test cases, and then carefully review the behavior of your blender to ensure the correct result.
Move to the Properties tab.
I have selected “All Excluding Val/Access” for the Property Selection option. This is just saying, take all of the property values with the Hierarchy.
Move to the Target tab.
For the Target Version option, we want to keep “Use Selected Target” enabled. In essence we are using this blender to copy our Hierarchies from the imported source versions, into our consolidated master version.
You can also blend into a new version, which is a good option when you are blending together 2 iterations of the same hierarchy. This allows you to review your result without affecting either the source or target version.
Save the Blender at this point, give it a name, and a description if you wish.
Run the blender and review the result at the bottom of the screen.
On the Source tab, select the next imported version (in Source Version) and click run.
Select the next imported version, and click run again. Repeat until all of the imported versions have been blended into the mast version.
Once you’ve blended all of the imported versions, click on the Home tab, and then navigate to the Browse section.
Select the master version to view the hierarchies that have been blended in.
All the hierarchies are in there, right-click on the version and save it at this point.
Take some time to look through the blended version, ensure that all your hierarchies, nodes, and properties are correct. Once I confirmed everything looked good, I deleted all of the imported source versions to clean up.
Whereas an Import always creates a new version when it is run, an Action Script allows you to modify nodes and properties within an existing version.
To learn about action scripts, we are going to create the associations of the attribute dimensions from Sample.Basic. The Product dimension has 4 attributes, Caffeinated, Ounces, Pkg Type, and Intro Date. The Market dimension has 1 attribute called Population. To create and manage these associations, we are going to need one property for each (5 total).
Below is the property for the Product attribute called Caffeinated. I had added this property into the Essbase category, and then assigned the constraint you see below. When choosing a value for this property, the user will be constrained to choose a node from the Caffeinated hierarchy only. This property will manage the association, but we’ll need another property for the Export because the nodes in the Caffeinated hierarchy are prefixed (for example, caff!Caffeinated_True). When we export metadata for Essbase, we do not want the prefix to be included, so we will create an export property to strip off the prefix. We’ll create the export properties in the next post when we learn about building exports.
Now that we have our Attribute properties created, it’s time to create the associations. An Action Script has a command, and up to 7 parameters. These are some of the most commonly used commands.
We are going to be using the ChangeProp command to populate our newly create attribute properties. This command requires 5 parameters in the following order; Version Name, Hierarchy Name, Node Name, Property Name, and Property Value.
These are the completed commands to create the association for each of the 4 Product attributes for the member 100-10. In column J, you can see I am using Excel again to create the content for the action script, which will be a flat file (TXT). Comma is the default delimiter so I will be using that.
This is a subset of the finished action script product.
Jump back to DRM and navigate to the Script section.
Click Browse and select the action script file you created.
Make sure you have the correct Character Encoding selected.
Make sure you have the correct Delimiter selected.
Click the Load button.
You’ll see all of the commands in your file loaded at the bottom of the screen.
Click Run to process the action script.
While the script is running, you will see a status window.
When the action script has completed, you will receive a notification. This script processed without any errors so there is no further action required.
Sometimes you will have errors after running an action script that may require additional action. You may have typed a node name or property name incorrectly, or possibly you were trying to send a property value that is invalid.
If you did have some records in your action script that resulted in an error, you’ll want to download the result so you can resolve the errors. The error messages you’ll receive are clear and self-explanatory so should be easy to resolve.
Our action script completed successfully, but let’s check some of the products to confirm.
Below you can see that product 100-10 does indeed have its attribute assignments assigned correctly.
We now have our metadata loaded into a version. In the next post, we are going to apply business logic into some of the properties and then build an Export, which will publish our metadata for Essbase.