Having issue with Sql MA with two object types and references

Hi,

I have sql MA which brings two object types from sql view 1. Org and 2. User. I had to combine them to keep them in same CS to generate references. User has a reference multivalued atrribute called "AdministratorForOrganization" which points to org objects if a particulare user administor them.

This out of the box sql MA is taking too long. We have good sql DBA team and two views defined in database is tuned to get better results.

When I run "Full Import" Sql MA is picking each record from parent view(definition: OrgIDEmailID,ObjectType,OrgName,FirstNme,LastName) ignoring object type and querying child view(definition: OrgIDEmailID,AttributeID and AttributeValue).

Here WHY FIM is querying child view for org object type. In my Sql MA configuration I did not have mapping for "AdministratorForOrganization" for organization object type. I have "AdministratorForOrganization" attribute mapping for user type so it make sense.

Any ideas? Has anybody encoutered same/similar issue? I have FIM2010. Is FIM2010 R2 has any improvement around this area( mean batch reading instead of one record at a time)?

Thanks in advance for looking at it.

Thanks,
Bhavesh


June 28th, 2013 11:08pm

As far as I know there is no change in the implementation of the SQL MA when it comes to your comment about "querying child view" (i.e. for each row returned from the main view, return a row from the child view one-at-a-time) ... indeed this is a ridiculous processing model (compared with the alternative "batch reading" approach you describe) and one that I understand is being discussed by the PG.  So for the time being your best bet is to create your own SQL ECMA (extensible).

I have done this in 2 ways myself, with both achieving vastly superior results to the SQL MA (e.g. 12 hours reduced to 7 minutes for a FI/FS):

1. Using exactly the same views as used by the native SQL MA I have used the SQL.Net libraries to load DataTable objects in memory and use in-memory joins to construct an AVP file (ECMA 1 days);

2. Using SQL XML ("for xml path ...") to combine all tables into a single XML document structure, listing each object class, as well as child xml nodes for references of one object to another by a unique FK ID; then applying a (generic) XSLT to convert the XML into LDIF.

In both my examples I had no need to write any data back - these days if I was to need to do this I would be using UNIFY's Identity Broker product, but in your case your option would most likely be to write your own ECMA2.  If I needed only a read-only approach right now and I didn't have Identity Broker at my disposal, I would choose option #2 above every time.

Free Windows Admin Tool Kit Click here and download it now
June 29th, 2013 11:14am

In addition to bob's examples, you might look and see if loading the views into staging tables would improve performance
June 29th, 2013 7:40pm

Thanks Bob and Brian. Your advice matters a lot to me and I really appreciate you.

It is very strange implementation of out of the box Sql MA. :( I just have to read from database so as Bob pointed out I may have to develop ECMA if overall import time is not acceptable to our business.

Thanks,
Bhavesh

Free Windows Admin Tool Kit Click here and download it now
July 1st, 2013 5:17pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics