MA Extension Coding

Our compnay is developing a User Self Service Database. The user will be 
populated via ILM into a SQL Table.  The database developers want me to write 
out two fields whenever ILM updates a field.  For example, when last_name 
changes I write to last_name and last_name_tk.  That way the DB knows whet 
fields have been updated without looking at then entire row.

I assumed I could do this and it would required advanced rules for every 
attribute.  But I've found that I can't write to two CS fields in a rule.  I 
was hoping to simply compare cs.lastname to mv.lastname and if different 
write cs.lastname and cs.lastname_tk  this does not seem possible?

Hi JPH,

You've got more than 1 challenge here.  First off, you are correct that in 
ILM, you can only write to a single attribute on EAF (this is actually a good 
thing as it helps to keep designs somewhat sane).  

In this scenario, this isn't a problem for you as calculating a change time 
for an attribute in EAF is not something I would recommend.  The primary 
reason for this is that you need to keep in mind what will happen when you 
perform a Full Synchronization.  If you have logic in your EAF rule that 
populates a value based on something like Now(), then a Full Sync will result 
in a modification of all objects and will make it look like all of your 
attributes changed, whether they did or not.

Instead, consider doing the time calculation in your connected system.  
Since you say the connected system is SQL, this is very easily handled with 
triggers applied to the full table that ILM is connecting to.  When changes 
are applied to the full table, you could have the trigger generate records in 
an audit log table that contains the old value, the new value, and the 
date/time of the change.   

Kind regards,

Mike 

"JPH" wrote:

> Our compnay is developing a User Self Service Database. The user will be 
> populated via ILM into a SQL Table.  The database developers want me to
write 
> out two fields whenever ILM updates a field.  For example, when last_name 
> changes I write to last_name and last_name_tk.  That way the DB knows whet

> fields have been updated without looking at then entire row.
> 
> I assumed I could do this and it would required advanced rules for every 
> attribute.  But I've found that I can't write to two CS fields in a rule. 
I 
> was hoping to simply compare cs.lastname to mv.lastname and if different 
> write cs.lastname and cs.lastname_tk  this does not seem possible?
> 

Mike Dube wrote:
(...)

> Instead, consider doing the time calculation in your connected system.  
> Since you say the connected system is SQL, this is very easily handled with

> triggers applied to the full table that ILM is connecting to.  When changes

> are applied to the full table, you could have the trigger generate records
in 
> an audit log table that contains the old value, the new value, and the 
> date/time of the change.   
(...)

Yes, triggers are in general good solution for such problem however 
sometimes such solution can sometimes causes the problems when such 
table is being updated. It all depend on the nature of these triggers.

-- 
Tomasz Onyszko
http://www.w2k.pl/ - (PL)

Tomasz Onyszko wrote:
> Mike Dube wrote:
> (...)
> 
>> Instead, consider doing the time calculation in your connected 
>> system.  Since you say the connected system is SQL, this is very 
>> easily handled with triggers applied to the full table that ILM is 
>> connecting to.  When changes are applied to the full table, you could 
>> have the trigger generate records in an audit log table that contains 
>> the old value, the new value, and the date/time of the change.   
> (...)
> 
> Yes, triggers are in general good solution for such problem however 
> sometimes such solution can sometimes causes the problems when such 
> table is being updated. It all depend on the nature of these triggers.
> 

Maybe I should clarify a bit from where my concerns about using triggers 
comes. It happened in few deployments for me and my colleagues that when 
we have used complicated triggers on tables which were used to 
communicate with SQL DB that OleDB driver used by SQL MA comes to 
conclusion that table is not writable. If I remember correctly this was 
a case when trigger on a table was updating another tables in database.

However in most cases triggers approach presented by Mike should work 
fine. Just if You will do this and You will come across this problem You 
will have to  do this in other way.

-- 
Tomasz Onyszko
http://www.w2k.pl/ - (PL)

Hello Mike,

We faced the same problem as you and, believe me, the triggers DEFINITLY are 
the best solution. But, if you really can't use triggers (for example, if 
you're not dealing with a database, but an LDAP), here's the way we solved 
the problem :

Let's assume your users comes from an Active Directory and are provisionned 
in your SQL Database. Both attributes to monitor are "Name" and 
"NumberOfNameChanges"

1. In the Initialize method of the "AD MAExtension.dll", create an
empty 
IList "ModifiedNames"

2. In the IMPORT rule "Name" of AD MAExtension, use the following
concept :
    2.1 If
!csentry["Name"].StringValue.Equals(mventry["Name"].StringVa
lue) 
--> the name has changed, goto 2.2, else exit
    2.2 If 
!csentry["NumberOfNameChanges"].StringValue.Equals(mventry["Numbe
rOfNameChanges"].StringValue) --> the change has not been recorded, goto
2.3, else 2.4
    2.3 Add the csentry["Name"].StringValue to the ModifiedNames
IList.
    2.4 mventry["Name"].StringValue =
csentry["Name"].StringValue (of course)

3. In the IMPORT rule "NumberOfNameChanges" of AD MAExtension, use the

following concept :
    3.1 If
!csentry["Name"].StringValue.Equals(mventry["Name"].StringVa
lue) 
--> the name has changed, goto 3.3
    3.2 ModifiedNames contains csentry["Name"].StringValue --> the
name has 
changed, goto 3.3
    3.3 mventry["NumberOfNameChanges"].Value = 
csentry["NumberOfNameChanges"].Value + 1 (of course)


Both export rules from MV to your SQL table are Direct. With this algorithm, 
you can assure both your attributes will be correctly managed, but, as you 
can see, it's really complex to master, mainly because you can never be sure 
which import rule will run first...

Hope it helps.

"Tomasz Onyszko" wrote:

> Mike Dube wrote:
> (...)
> 
> > Instead, consider doing the time calculation in your connected system.
 
> > Since you say the connected system is SQL, this is very easily handled
with 
> > triggers applied to the full table that ILM is connecting to.  When
changes 
> > are applied to the full table, you could have the trigger generate
records in 
> > an audit log table that contains the old value, the new value, and the

> > date/time of the change.   
> (...)
> 
> Yes, triggers are in general good solution for such problem however 
> sometimes such solution can sometimes causes the problems when such 
> table is being updated. It all depend on the nature of these triggers.
> 
> -- 
> Tomasz Onyszko
> http://www.w2k.pl/ - (PL)
> http://blogs.dirteam.com/blogs/tomek/ - (EN)