Meilleur auteur de réponses

Having issue with Sql MA with two object types and references

Question

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)?

Réponses

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.

Toutes les réponses

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.

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.