Topic
  • 9 replies
  • Latest Post - ‏2013-08-23T19:46:53Z by Matthew_Simons
asg1
asg1
7 Posts

Pinned topic Optim Data masking service - Referential Integrity not maintained in execution

‏2013-07-25T17:32:47Z |

Hi,

I am trying to develop data masking services using Optim Designer version 2.2.3. I have two related tables, lets say :

  1. Table A (X(PK),Y,Z)
  2. Table B (L(PK),M,N,X,Y,Z)

As you will notice, attribute X is a Foreign Key in Table B and that all the attributes in Table A (X,Y and Z) are present in Table B.

I created a data access plan and in that I chose "Select entities based on relationship with start entity" and chose Table A as the start entity. The only other related table/entity was Table B. I went ahead and created a rule based privacy policy (Scramble Policy) for the attribute Y in Table A and then I created a data transformation service using this data access plan. Now when I test this service using the "Executor", only attribute Y in Table A gets masked and not attribute Y in table B.

Correct me If I am wrong here, had referential Integrity being maintained, attribute Y in Table B should also have the same (masked) values in Table A right? 

It will be very helpful if some one can help me with developing a service which masks data (in the scenario above) maintaining the referential integrity. 

Thanks

  • sundar_chinnagoundan
    11 Posts
    ACCEPTED ANSWER

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-02T20:33:44Z  

    You can use PROP function to propagate value from parent table in to child tables.

     

    Thanks,

    Sundar Chinnagoundan

  • sean_johnson
    sean_johnson
    3 Posts
    ACCEPTED ANSWER

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-09T17:28:17Z  
    • asg1
    • ‏2013-08-06T15:29:41Z

    Thank you Sundar for responding. But I do have a follow up question here.

    Could you please tell me how I would use the PROP function. Is it something I need to change in the Preferences in the Optim Designer or is it some configuration step that I should be doing while designing the service. It would be very helpful if you can elaborate a little.

    In your column map, presumably where your masking function is located ,replace the source column name (or masking function) with the propagate function to assign the value of the column in the parent table to all related tables.

    The syntax is as such: PROP( value)

    The value could be a string, expression or function. In your case you would use your data masking function.

    Ex.1  PROP( 42)                          // Assigns the value "42" to all related tables

    Ex. 2. PROP("Hello World")      // Assigns the string "Hello World" to all related tables

    EX3. PROP(SEQ(500,1))         // Assigns the value 500 to first related table, then increments this value for all subsequent tables

  • Matthew_Simons
    Matthew_Simons
    26 Posts
    ACCEPTED ANSWER

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-19T17:46:52Z  

    In your column map, presumably where your masking function is located ,replace the source column name (or masking function) with the propagate function to assign the value of the column in the parent table to all related tables.

    The syntax is as such: PROP( value)

    The value could be a string, expression or function. In your case you would use your data masking function.

    Ex.1  PROP( 42)                          // Assigns the value "42" to all related tables

    Ex. 2. PROP("Hello World")      // Assigns the string "Hello World" to all related tables

    EX3. PROP(SEQ(500,1))         // Assigns the value 500 to first related table, then increments this value for all subsequent tables

    To add on to what Sean said, using PROP is as simple as putting PROP(.....) in your column map where "....." is the masking function you already have in place - just add the word PROP and the ( and ).

  • sundar_chinnagoundan
    11 Posts
    ACCEPTED ANSWER

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-21T18:15:03Z  
    • asg1
    • ‏2013-08-21T09:53:53Z

    Thank you all for your responses.

    But I am not sure if we are talking about the Optim Data Masking Solution here because I do not see an option to use column map in there.I am using Optim Data Masking Solution v2.2.3 and not Test Data Management or Data Growth solution. Optim Data Masking Solution has the following components - Designer, Executor, Proxy, Management Server and Manager. I believe column map is a functionality available with Optim Data Growth Solution and Test Data Management Solution only.

    Please suggest a way I can resolve this issue.

    Thanks in advance for your time and effort.

    You should be able to see 'Column Maps' under "Masking On Demand"  in OPTIM Designer. 

  • Matthew_Simons
    Matthew_Simons
    26 Posts
    ACCEPTED ANSWER

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-21T19:38:34Z  

    Sorry, should have guessed that you were using the old school Designer from the version number.  I can't remember (and I don't have a 2.x sandbox anymore) where the exact placement of things is but I do know that the documentation doesn't mention PROP at all. So the rest of my answer is based on how things are right now, things might not line up perfectly but maybe it will give you a clue.

    After re-reading your original question,I noticed that the FK relationship between A and B is on column X and you wanted Y to be propagated.  In that case, the only data that Optim will be able to propagate would be column X - it can only push down values that it knows are related.  Is this truly how the tables are related, just X-->X?  Or is it X/Y/Z-->X/Y/Z?  If its the former, then that's the problem.  If its the latter, can you try to clear out the destination mapping for X, Y, and Z in the target mapping?  Optim usually wants that field to be blank in order to propagate.

    Finally, if none of those things work, can you try using a repeatable function on Y in both tables while disabling the constraints during processing?  You will wind up with matching values that way too.

     

    Hope this helps, 2.2.3 is not the friendliest tool in the world!

  • sundar_chinnagoundan
    11 Posts

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-02T20:33:44Z  

    You can use PROP function to propagate value from parent table in to child tables.

     

    Thanks,

    Sundar Chinnagoundan

  • asg1
    asg1
    7 Posts

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-06T15:29:41Z  

    You can use PROP function to propagate value from parent table in to child tables.

     

    Thanks,

    Sundar Chinnagoundan

    Thank you Sundar for responding. But I do have a follow up question here.

    Could you please tell me how I would use the PROP function. Is it something I need to change in the Preferences in the Optim Designer or is it some configuration step that I should be doing while designing the service. It would be very helpful if you can elaborate a little.

  • sean_johnson
    sean_johnson
    3 Posts

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-09T17:28:17Z  
    • asg1
    • ‏2013-08-06T15:29:41Z

    Thank you Sundar for responding. But I do have a follow up question here.

    Could you please tell me how I would use the PROP function. Is it something I need to change in the Preferences in the Optim Designer or is it some configuration step that I should be doing while designing the service. It would be very helpful if you can elaborate a little.

    In your column map, presumably where your masking function is located ,replace the source column name (or masking function) with the propagate function to assign the value of the column in the parent table to all related tables.

    The syntax is as such: PROP( value)

    The value could be a string, expression or function. In your case you would use your data masking function.

    Ex.1  PROP( 42)                          // Assigns the value "42" to all related tables

    Ex. 2. PROP("Hello World")      // Assigns the string "Hello World" to all related tables

    EX3. PROP(SEQ(500,1))         // Assigns the value 500 to first related table, then increments this value for all subsequent tables

  • Matthew_Simons
    Matthew_Simons
    26 Posts

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-19T17:46:52Z  

    In your column map, presumably where your masking function is located ,replace the source column name (or masking function) with the propagate function to assign the value of the column in the parent table to all related tables.

    The syntax is as such: PROP( value)

    The value could be a string, expression or function. In your case you would use your data masking function.

    Ex.1  PROP( 42)                          // Assigns the value "42" to all related tables

    Ex. 2. PROP("Hello World")      // Assigns the string "Hello World" to all related tables

    EX3. PROP(SEQ(500,1))         // Assigns the value 500 to first related table, then increments this value for all subsequent tables

    To add on to what Sean said, using PROP is as simple as putting PROP(.....) in your column map where "....." is the masking function you already have in place - just add the word PROP and the ( and ).

  • asg1
    asg1
    7 Posts

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-21T09:53:53Z  

    To add on to what Sean said, using PROP is as simple as putting PROP(.....) in your column map where "....." is the masking function you already have in place - just add the word PROP and the ( and ).

    Thank you all for your responses.

    But I am not sure if we are talking about the Optim Data Masking Solution here because I do not see an option to use column map in there.I am using Optim Data Masking Solution v2.2.3 and not Test Data Management or Data Growth solution. Optim Data Masking Solution has the following components - Designer, Executor, Proxy, Management Server and Manager. I believe column map is a functionality available with Optim Data Growth Solution and Test Data Management Solution only.

    Please suggest a way I can resolve this issue.

    Thanks in advance for your time and effort.

    Updated on 2013-08-21T12:00:05Z at 2013-08-21T12:00:05Z by asg1
  • sundar_chinnagoundan
    11 Posts

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-21T18:15:03Z  
    • asg1
    • ‏2013-08-21T09:53:53Z

    Thank you all for your responses.

    But I am not sure if we are talking about the Optim Data Masking Solution here because I do not see an option to use column map in there.I am using Optim Data Masking Solution v2.2.3 and not Test Data Management or Data Growth solution. Optim Data Masking Solution has the following components - Designer, Executor, Proxy, Management Server and Manager. I believe column map is a functionality available with Optim Data Growth Solution and Test Data Management Solution only.

    Please suggest a way I can resolve this issue.

    Thanks in advance for your time and effort.

    You should be able to see 'Column Maps' under "Masking On Demand"  in OPTIM Designer. 

  • Matthew_Simons
    Matthew_Simons
    26 Posts

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-21T19:38:34Z  

    Sorry, should have guessed that you were using the old school Designer from the version number.  I can't remember (and I don't have a 2.x sandbox anymore) where the exact placement of things is but I do know that the documentation doesn't mention PROP at all. So the rest of my answer is based on how things are right now, things might not line up perfectly but maybe it will give you a clue.

    After re-reading your original question,I noticed that the FK relationship between A and B is on column X and you wanted Y to be propagated.  In that case, the only data that Optim will be able to propagate would be column X - it can only push down values that it knows are related.  Is this truly how the tables are related, just X-->X?  Or is it X/Y/Z-->X/Y/Z?  If its the former, then that's the problem.  If its the latter, can you try to clear out the destination mapping for X, Y, and Z in the target mapping?  Optim usually wants that field to be blank in order to propagate.

    Finally, if none of those things work, can you try using a repeatable function on Y in both tables while disabling the constraints during processing?  You will wind up with matching values that way too.

     

    Hope this helps, 2.2.3 is not the friendliest tool in the world!

  • asg1
    asg1
    7 Posts

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-23T11:44:07Z  

    Sorry, should have guessed that you were using the old school Designer from the version number.  I can't remember (and I don't have a 2.x sandbox anymore) where the exact placement of things is but I do know that the documentation doesn't mention PROP at all. So the rest of my answer is based on how things are right now, things might not line up perfectly but maybe it will give you a clue.

    After re-reading your original question,I noticed that the FK relationship between A and B is on column X and you wanted Y to be propagated.  In that case, the only data that Optim will be able to propagate would be column X - it can only push down values that it knows are related.  Is this truly how the tables are related, just X-->X?  Or is it X/Y/Z-->X/Y/Z?  If its the former, then that's the problem.  If its the latter, can you try to clear out the destination mapping for X, Y, and Z in the target mapping?  Optim usually wants that field to be blank in order to propagate.

    Finally, if none of those things work, can you try using a repeatable function on Y in both tables while disabling the constraints during processing?  You will wind up with matching values that way too.

     

    Hope this helps, 2.2.3 is not the friendliest tool in the world!

    I cannot agree more. 2.2.x Designers are really tough to handle.

    To your question Matthew, what I meant in the example was X/Y/Z-->X/Y/Z relation.

    Anyways, I am getting the TDM 9.1 now and I assume all your inputs were pertinent to the Designer which comes with that.

    Thanks again Matthew, Sundar and Sean for your support. I'll keep these inputs in mind.

    Cheers!!

  • Matthew_Simons
    Matthew_Simons
    26 Posts

    Re: Optim Data masking service - Referential Integrity not maintained in execution

    ‏2013-08-23T19:46:53Z  
    • asg1
    • ‏2013-08-23T11:44:07Z

    I cannot agree more. 2.2.x Designers are really tough to handle.

    To your question Matthew, what I meant in the example was X/Y/Z-->X/Y/Z relation.

    Anyways, I am getting the TDM 9.1 now and I assume all your inputs were pertinent to the Designer which comes with that.

    Thanks again Matthew, Sundar and Sean for your support. I'll keep these inputs in mind.

    Cheers!!

    Excellent news - Designer 9.1 removes all the InfoSphere Data Architect requirements with building relationships and data access plans, etc, and uses more typical Optim language.  You will be able to just check the box in the column map for "Propagate" and it will automagically work.

    Enjoy!