Change of age field in ACC v7

[A_B_SE]

Hello, we are considering changing the @age field  in the Recipient table since we noticed that today we are using this expression in the Recipients (nms) data schema XML-code:

<attribute desc="Age in years" expr="YearsOld([@birthDate])" label="Age" name="age"
notNull="false" type="long"/>

Our @birthDate is not indexed as of today and this change in the schema would help our database performance since the current YearsOld([@birthDate]) expression is heavy to process and we use it in every wkf.

Is there any downside with removing this expression and instead calculate it and populate the @age attribute in a daily workflow?

[ParthaSarathy]

Hi @A_B_SE ,

Yes you can delete it, and create a new attribute with different name and create a dbIndex for it. And using daily workflow you can update the age.

To delete and create an index for a new age field,

Extend your recipient schema and add below line inside element tag:

<dbindex name="age">

      <keyfield xpath="@recipientAge"/>

  </dbindex>

<attribute name="age" _operation="delete"/>

<attribute name="recipientAge" label="Recipient Age" type="long"/>

--Update the database structure--

[A_B_SE]

Hello @ParthaSarathy  and thanks for your reply. I'll try to reformulate:

Do you advice against doing any changes to the age variable in general since it is an out of the box attribute? Or do you see any risks changing the attribute?

[ParthaSarathy]

Hi @A_B_SE ,

Generally, _operation="delete" we will be using it to remove OOTB attributes.

Before deleting, Just make sure you're not using it in any of your workflows.

[A_B_SE]

I do not want to delete any OOTB attributes, I was just asking if changing the structure of the one mentioned is approved or it would pose any kind of problems. Is it common to edit such OOTB attributes? Our company never did it and are not aware of the risks.

[A_B_SE]

This is what we want to do:

We want to remove the expression in the age attribute of the Recipients schema (this attribute is an out of the box one) and use the same expr/calculation in a ETL wkf instead in order to unload the CPU.

Current data schema (we use an extension of nms:recipients table) with the attribute we want to change:

We want to move the expr="YearsOld([birthDate])" from this table and use it in a ETL wkf instead:

Are there any risks in case for example of future ACC updates?

Thanks for your patience.

[ParthaSarathy · Accepted solution]

@A_B_SE , Yes.. You can do that and there won't be any impact.

But, @age is not an SQL field attribute. it is a 'SQL Calculated field'. So removing the expression in Extended schema and updating in ETL workflow wont work.

You need to follow below steps:

In your extended schema, add the below code inside element tag

<attribute _operation="delete" name="age"/>
<attribute label="Age" name="age" type="long"/>

This script remove the OOTB expression (there wont be any impact), and create a SQL Field in database with name as @age

Update the database structure > logout and reconnect

Now in your workflow, use the Update data activity to update the age as per your screenshot.

For the first time, fetch all the recipients in a query and update their age using your workflow.