none
Having issue with Sql MA with two object types and references

    Question

  • 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


    • Modifié bhavesh001 vendredi 28 juin 2013 20:11 updated
    vendredi 28 juin 2013 20:08

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.


    Bob Bradley (FIMBob @ TheFIMTeam.com) ... now using Event Broker 3.0 for just-in-time delivery of FIM 2010 policy via the sync engine, and continuous compliance for FIM

    • Marqué comme réponse bhavesh001 lundi 1 juillet 2013 14:06
    samedi 29 juin 2013 08:14

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.


    Bob Bradley (FIMBob @ TheFIMTeam.com) ... now using Event Broker 3.0 for just-in-time delivery of FIM 2010 policy via the sync engine, and continuous compliance for FIM

    • Marqué comme réponse bhavesh001 lundi 1 juillet 2013 14:06
    samedi 29 juin 2013 08:14
  • In addition to bob's examples, you might look and see if loading the views into staging tables would improve performance

    My Book - Active Directory, 4th Edition
    My Blog - www.briandesmond.com

    samedi 29 juin 2013 16:40
  • 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

    lundi 1 juillet 2013 14:17