If your organization is using Oracle Data Relationship manager for data governance and master data management, there is a good chance you are also using Oracle Hyperion suite for budgeting, reporting and consolidation applications. Some of the clients I worked for use Enterprise performance management architect to create and manage applications mentioned above. In a scenario where DRM is the source of record for downstream systems, and EPMA is used to manage applications, the master data flow can be illustrated as below.
The simplest way to populate EPMA with data from DRM is using an .ads file. This file is produced by running an “export” out of DRM, where the export type is specified as “EPM Architect Export”
However, using an .ads file out of DRM to load into EPMA is a manual process. We need to produce the file first by running the export in DRM, save the file on the disk, and then import that file into the EPMA application. For a step-by-step explanation of this manual process, which some people prefer, by the way, please read my blog post here.
This blog post is tutorial in how to automate the process of loading data from DRM into EPMA architect without having to produce the text file and having to load it manually. The key point to remember is the actual data being loaded will be exactly the same, it’s just the process that will be different, with fewer steps, and greater scope for automation.
Before going into the steps, we assume the following
- Oracle DRM has been configured to use Hyperion Shared Services for authentication
- DRM Webservice has been successfully deployed on WebLogic server ( We have to modify the policy sets attached to it, but I am not going to go into the deployment process itself)
- DRM version, hierarchies and exports have been created to produce a well formatted .ads file
- The .ads file is used to create an import profile, and the profile was successfully executed
To learn about deploying DRM Webservice on Weblogic, refer to Oracle’s instructions here. For instructions about DRM configuration and export profile, follow my earlier blog post here.
Once the above are in place, we can start working on integrating DRM and EPMA. The steps involved can be summarized as below.
- Registering DRM with Hyperion Shared Services
- Setting up relevant policy sets for DRM Webservice
- Creating a java key-store and certificate file on DRM machine
- Setting up key-store in WebLogic
- Importing certificate onto the Windows server hosting EPMA
- Create an import profile and import DRM data into EPMA
Registering DRM with Hyperion Shared Services
When EPMA users’ execute profiles to import data from DRM, the request from within EPMA uses an authentication account to communicate with DRM. This account has to be first setup in both DRM, HSS, and on WebLogic server. If HSS and WebLogic server use a directory based authentication, the same directory has to be used in both of them to make sure the user account is present in both. For simplicity, I am just using native directory for authentications, which means, I have to add the user account in HSS and WebLogic manually, making sure the password is the same in both. This is not really elegant but it bypasses the need for having active directory type of authentication setup in POC systems.
Make sure the account is a system account, and is not subject to password expiry rules. This same user account has to be also created in DRM, with “data creator” and “data manager” privileges. After making sure this user is setup properly, and then verified by logging into both DRM and Hyperion with the same credentials, we proceed to registering DRM with HSS. Since I am using Oracle’s demo DRM database which comes shipped with all the export profiles and user accounts, I am going to use the account EPMA for this purpose. Read here about setting up your own version of Oracle’s DRM demo application.
Setting up policy sets for DRM Webservice
We then need to setup a policy set for the DRM Webservice. Instructions on setting up policy set are in the Oracle blog post for DRM Webservice deployment. We just have to change the policy set to the one recommended for DRM-EPMA integration. The policy set is called “oracle/wss11_saml_or_username_token_with_message_protection_service_policy”. Once the policy set is updated, restart EPM server to make sure the policy set comes into effect.
Creating a java key-store and certificate file on DRM machine
Next step is to create a java keystore on the DRM server, and also create a certificate which has to be imported into the EPMA machine. Doing this is very critical as it allows the machine-to-machine communication between EPMA and DRM servers, which in turns enables the DRM EPMA integration. Let’s start with creating the java keystore first. Fire up the command line, and traverse through to the bin directory in the java folder.
Run the following command to produce the keystore file. Note that key alias is “orakey” and the keystore is “default-keystore.jks”. You will be prompted to enter password and make sure you note down the password you chose.
keytool -genkey -alias orakey -keyalg “RSA” -sigalg “SHA1withRSA” -dname “CN=orakey, C=US” -keystore default-keystore.jks
Once the keystore file is produced, we use it to produce the cert file using the following command.
keytool -export -alias orakey -file C:\DRMCert.cer -keystore default-keystore.jks
Note that the cert file DRMCert.cer is produced in the C:\ drive.
The steps mentioned here are fairly basic, you should check your company’s security policies and let a system admin create these files if you want to use them in production.
Setting up key-store in WebLogic
The keystore we have created earlier, it has to be setup in the WebLogic server. Login to the enterprise manager of your WebLogic server hosting the DRM Webservice, and navigate to the security provider configuration screen as below.
Click on configure on the keystore section
Fill out the details from our earlier exercise when we created the identity store and the certificate. If your WebLogic server is different from your DRM server (which is in most cases of distributed environments), you need to copy the file on to the WebLogic server. Enter that location in the keystore path, along with key alias and crypt alias. Save the settings by clicking on the OK button.
Importing certificate onto the Windows server hosting EPMA
Copy the cert file we have created in the previous steps on the machine running EPMA service. Once copied, the certificate needs to be imported onto that server. For this let’s fire up the MMC and open the add/remove snap-in.
Navigate to the folder below.
Open the file “EPMA_ServerEngine.exe.config”
Replace the “findvalue” key with the key alias we used in the certificate. Save the file, and restart EPMA service.
Create an import profile and import DRM data into EPMA
Login to Workspace and open dimension server.
Make sure you have already created the alias dimension, otherwise there will be a bunch of warnings about missing alias dimension when you do the imports.
From the file menu, select “Create new profile” and chose “Data relationship management” as the import type. Clock OK.
There is one other step we need to do to get rid of the alias related errors during the import process. Remember the alias dimension we have created earlier, we need to associate each of the dimensions to this alias dimension so that each member of the dimension will have a proper alias. This is only required as one-off, when we first create the dimensions. That’s why I didn’t mention in as part of the main steps.
We also need to create the currency dimension and associate it with the entity dimension. Again this is also a one-off step which we do when we first create the entity dimension.