SQL MA join failures
All, We are bringing up a new FIM deployment in development. We have ILM 2007 in production and are transitioning to FIM. We have a fairly basic structure in place. A SQL MA that is basically a crosswalk of all our ID columns that projects to the MV, and the only thing that projects. This MA controls who is active and who isn't. We also have an HR feed in a SQL MA. An additional SQL MA that takes data from a mainframe feed and includes our legacy logic that will eventually go aways. These two SQL MAs (HR and Mainframe) work just fine, and join correctly against the Metaverse objects. We are attempting to bring another SQL MA on board, which includes identity data from 3rd data store. It includes a JOIN rule with three JOIN conditions, which are all failing for every record. The data in this MA contains valid data that should cause valid JOINs. The one difference in this 3rd MA is that the base table for the ID fields were defined as char(16) where its defined as varchar(16) in the other SQL MAs. I modified the view that we use to allow FIM to consume this data to CAST the char(16) as varchar(16), and that didn't make a difference. Should the char/varchar thing make any difference, since they are all stored as strings in FIM anyways? Any ideas why the JOIN fails? I'm running out of guesses. Thanks, Greg
October 10th, 2011 2:00pm
how did you configure these join conditions ? and where ? you can try various options for the join and simulate the synchronization result, by running preview on the connector in that MA.It's never too late in life ... to start living
October 10th, 2011 3:50pm
Could be that it is padding the extra spaces in the fixed length CHAR field wiht spaces. You could try cutting and pasting a CS row into another app like notepad and if the ID field is "99999999 ". That would not be a string match with "99999999". You could try resulving it with a custom Join rule extension with a TRIM function. Either that or replace that colume with a varchar field. EricEric
October 10th, 2011 4:06pm
Thanks for the suggestions. I did cut and paste the CS value into notepad to check for leading/trailing spaces, and didn't find any issues. I had three join conditions set up. The first on ID#1, second on ID#2, third on ID#3. I decided to play with those, using various combination, and ended up removing the ID#1 and ID#3 join conditions. At that point, with only ID#2 join in place, it joined correctly. But I don't understand why a multi-level join would fail, but one join within the group would succeed on its own. Could ID#1 join be somehow erroring out, but only silently? Thanks, Greg
October 11th, 2011 9:56am
the join conditions using the portal in inbound Synchronization Rule are "Anded", if you want to create "Ored" join rules, you need to use classic join rules in synchronization manager for your management agent, you can create multiple join rules there.It's never too late in life ... to start living
October 11th, 2011 10:07am
Hi Amer, Thanks for responding. I did create the Join rules on the MA in the Sync Service Manager, they were not created in the portal. No Sync Rules have been created. Thanks, Greg
October 11th, 2011 10:59am
I've configured the MA with just a single join, and it works. ID#1 by itself works. ID#2 by itself works. I have another MA with two joins, and it works. But this current MA with three join conditions fails. Any ideas why? I just changed the configuration to use ID#1 and ID#2. If that works, maybe it points to ID#3 as my problem?
October 11th, 2011 2:04pm
My MA works if the join is just ID#1 and ID#2. ID#3 is an important join, so I may just leave it off. But it would be good to know why ID#3 caused it to fail. Thanks for everyone's responses.
October 11th, 2011 3:26pm
Just a sanity check here...your three join rules are actually set up as 3 separate rules, not three conditions in a single rule, correct? What do you mean by "the join fails"? The connector space object just doesn't join? There's no reason that 3 join rules wouldn't work if 2 do, because they are evaluated sequentially. A screenshot showing your join rules and with a preview showing success and failure for you might help us spot the problem. Mspaint is good for covering over any sensitive/organizational/personal data. Chris
October 11th, 2011 8:15pm
Actually, it was three conditions on a single rule. Should they be separate rules? The failure was that everything was being resolved as a disconnector.
October 12th, 2011 4:25pm
Greg as I told you, you can create multiple Join rules "with Or", the conditions inside each Join rule are "with And" for sure they need to be separate join rules, and you can configure the order of these rules It's never too late in life ... to start living
October 12th, 2011 4:32pm
As Amer indicated, conditions within the same rule are AND-ed together. However, whether this is what you wanted or not depends on what you were trying to do, and I'm not sure we've gotten a clear picture of that based on what you've described of your situation. That's why I'm not so eager to say "for sure" which way you should go. If all three of your conditions have to be met for a join, then put them all in the same rule. If the third condition weren't behaving the way you wanted, you might make a simple rule with just that condition for troubleshooting purposes, but if that one condition ends up with multiple matches then you won't get a join anyway. The preview should at least show you what your matches were. If any one of the three conditions should produce a join, they each need to be in their own rule. Those can and should be prioritized according to reliability and likelihood of producing a good match. Remember that it is a best practice to have any metaverse attribute used for a join marked as "indexed" in the metaverse designer. It would work without it, but the best performance comes with indexing.
October 12th, 2011 4:56pm
Thanks all for the help.
October 12th, 2011 5:31pm