Importing hierarchical entitlement data in OIA - a practical example

Oracle Identity Analytics (OIA) provides organizations with the ability to engineer and manage roles and automate critical identity-based controls and processes. In order to do so, we need to feed OIA with the relevant data such as typical HR data, e.g. business structures, users, etc., and account information from all the managed systems in scope, e.g. application user profiles containing common attributes like userid, firstname, lastname, employeeid but more interesting the authorization data or entitlements (which could be application roles, groups, permissions, privileges, responsibilities or whatever it is called within the system).

All of this data is stored within OIA in the so-called Identity Warehouse. This Identity Warehouse is a central repository that contains all of the important entitlement data for the organization (and of course also the other data as mentioned above). This data is imported from the organization's databases on a regular, scheduled basis. The Oracle Identity Analytics software has an import engine that supports complex entitlement feeds. The engine accepts either CSV flat files or XML files, and includes Extract, Transform, and Load (ETL) processing capabilities. Flat files require a schema for the import process. XML files are recommended for accounts with multi-value attributes or n-level hierarchies. Another way of feeding the Identity Warehouse is through an integration with a provisioning solution like Oracle Identity Manager (OIM). Such an integration will give OIA direct access to all of the entitlement data in the systems managed by OIM.

In this blog entry I want to focus on feeding the Identity Warehouse with multi-value attributes or n-level hierarchies through the import engine via XML files. As an example I will use some sample data that is coming from a Peoplesoft Financials system. This system is relatively simple in the sense that its authorizations are modeled 2-levels deep. Users are assigned to Roles and Roles are assigned to Permission Lists. Again, this is just a simple 2-level hierarchical example but could also be applied to any system having n-level (n > 2) hierarchical data.

The data that I want to import lives in 3 tables: a User table, a User - Role relationship table, and a Role - Permission relationship table. The Entity Relationship Diagram is as shown in the adjacent diagram.

Examples of table data for two user entries can be downloaded via the following files: user.csv, userrole.csv, and roleperm.csv.

The data in these tables needs to be joined and transformed to the right XML format. For that I have used an ETL tool called CloverETL. CloverETL is a Java based ETL framework which can be used to transform structured data, and to some degree free-form data. Of course, any other data integration platform like e.g. Oracle Data Integrator can be used for this purpose. Basically, we are reading the data, transforming it, and finally writing it to our final XML file. The graph in the screenshot below shows three so-called UniversalDataReaders (in green), reading each of the input tables. These are joined and written to XML through an XMLWriter component (in blue). This way, the data is joined and written to XML format.

However, the XML data coming out of this XMLWriter is not in the right format that we need to import it in Oracle Identity Analytics. This XML output is having specific tags based on the field names from the input tables, like:

<?xml version="1.0" encoding="UTF-8"?>
<rbacx component="XML_WRITER0" graph="PSFT" created="Mon May 09 01:34:05 CEST 2011">
    <Description>Janssen, J.</Description>

Therefore we use an XSLTransformer component to transform this XML to the more generic format that we need and finally we write this XML data to a file ready for import through a UniversalDataWriter component. This XML format is for the most part using the more specific tags <attributes>, <attribute>, <attributeValues>, <attributeValue>, and <value>.

<?xml version="1.0" encoding="UTF-8"?>
  <namespace namespaceName="Peoplesoft" namespaceShortName="PSFT" />
    <account id="P123456">
      <comments />
      <description>Janssen, J.</description>
        <attribute name="Last_Change">
	...several lines skipped...
        <attribute name="Last_Signon_Dat">
        <attribute name="ROLE">
                <attribute name="PERMISSION_LIST">

The XSL transformation file that I have used to transform the XML can be downloaded via this link: PSFT_01_accounts.xsl.

After defining a Peoplesoft resource type / namespace within Oracle Identity Analytics according to the screenshot shown here, we are now able to import the resulting XML file. After the hierarchical XML data has been successfully imported, the entitlement data can be used in attestation processes, for role mining, defining audit policies, etc. As an example how the account data is represented within OIA, we can take a look at the actual data for a user, e.g. P123456 within the identity warehouse. This results in the following two screenshots. When looking into the general details for this user's Peoplesoft account, we see in the left screenshot all the attributes as defined in this resource type's namespace. When clicking on the attribute named 'Role Name', we can see the list of roles for this account and also that this Role Name attribute also has 'sub attributes'. When double-clicking now one of the roles, we drill-down on its sub attributes. And obviously we find that the Role Name attribute has a sub attribute named 'Permission List'.

 Have fun, RenĂ©!



Thank you very much for the article. This is very helpful. I have a very basic question. How do I load the xml data into OIA. I tried to load it the sameway as a regular text file by putting the file in $RBACX_HOME/import/in directory and importing it using the provisioning server of type file. However the import is failing looking for a schema file.

Is there is different way of loading xml files? I did not find any documentation on it.

Thank you very much

Posted by guest on August 01, 2011 at 11:03 AM CEST #

The way you describe is the way it should work. Not sure what went wrong there...

Posted by René on August 24, 2011 at 11:36 AM CEST #

Thanks for your help Rene, but i have a comment. I got an error trying to import into OIA, so i delete "description" tag (i.e. <description>Janssen, J.</description>), and it worked. Do you know what's wrong?
Also, can you tell me how modify in OIA, for example permission_list, and automate this changes in Peoplesoft?
Thank you very much.

Posted by guest on October 25, 2011 at 04:01 PM CEST #


I copied your XML file and I tried to import it into OIA by placing the xml file inside import/in directory structure. Its throwing a XML Validation error no matter how I tweak the XML. So could you please answer the following questions.

1. Is there a schema file required? If so whats the format of it in this scenario.
2. Is there a dtd against which these XML's are validated if so where can we get the dtd file so that we can generate the xml based on that dtd.
3. Can you share with me the steps that you performed (like giving me the xml file, sql's for resource type and resource generation) so that I can try to import the same and make it work.

Please let me know.

Best Regards,

Posted by guest on November 30, 2011 at 11:56 AM CET #

Hi Rene,

I'm trying to properly define a schema file (rbx) for policies, in order to import Oracle eBusiness Suite "Responsibilities". I have integrated OIA-OIM. My requirement is import policies via csv files to OIA and then these policies gets exported to OIM automatically. I have done this with Active Directory, but, for OeBS is not working. I have posted in oracle forums (, also I have fired a SR in Oracle support but I'm still not able to get this working for OeBS. I hope you can give me some pointers on this. OeBS is different in the way that the "child form" for responsibilities has many parameters (ResponsibilityName, ApplicationName, SecurityGroups) AD has only the "groups"


Posted by Juan on August 31, 2012 at 08:52 AM CEST #


wonderful post about ETL in OIA.
I've tried doing something similar for a project of mine but it seems that OIA is having some trouble processing the XmlWriter component. I believe this component was not available in cloverETL 1.8.1 (the version that is recommended for OIA How did you manage to put OIA reading your graph? Did you use a more recent version of cloverETL and if so which one?

Many thanks!

Posted by Ruben on January 31, 2013 at 08:48 PM CET #

Hey Ruben,

Thanks. Correct about executing the graph... What I did was executing the graph outside of OIA, so within CloverETL... I just imported the XML output files into OIA after that.

Cheers, René.

Posted by guest on February 01, 2013 at 11:14 AM CET #

Post a Comment:
  • HTML Syntax: NOT allowed

This blog covers exciting things I encounter about Oracle's software and related; that is Identity & Access Management, SOA, Security, Desktop, etc. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.


« April 2014