Transforming SAP IDM Data

[former_member2987]

Data! Data! Data! I can’t make bricks without clay!

-Sir Arthur Conan Doyle

One of the issues that new administrators of IDM need to face understanding how data is organized in the identity store.

Certainly an administrator can look at the Schema Document and get an idea of that the attributes are.  Or you can look at the IDM online help to get an idea of how to  Use views to access identity store information. Even better you might review per.krabsetsve great Blog entry on IDM SQL Basics #1: Queries against the Identity Store, for even more information. Which will really tell you quite a bit about what's in the various IDM Tables and views.

However, what we are not really told about is how to arrange the data in such a way that it is usable by relational database tools.  At this point you might be wondering, aren't we using SQL Server, Oracle, DB2, or Sybase?  Well yes, you are, but the data is not quite arranged in a  typical relational matter. The IDM database stores information that is "pivoted" which provides a way to facilitate the many to many relationships that occur within the Identity Store.

For example, consider the following:

We see a listing that runs up and down identifying all of the information for the entry identifying each attribute and we are differentiating between entries based on a Unique Identifier, which we know as the MSKEY.  This is great since we can by combination of MSKEY and ATTRNAME SQL queries can identify any attribute for any user. Cool. Except when we want to represent this data in another application.

There are a couple of ways that we can make this happen.  One would be to use a ToAscii pass which would allow for the creation of a CSV file that could be processed in any number of ways. However, a more elegant way would be to use a SQL Query which would let us place the data nice and neat into a database.  I've done this before to make the data more easily accessed by applications such as VDS. Consider this query:

When we use this slightly more advanced query, we can "pivot" the information back into a format that is more familiar.  Now the data can be easily consumed by BI tools, VDS, or other database applications.

I've used this query on SQL Server and Oracle with no issues or conversions needed. I hope it is something that is of use to you all!