Expand my Community achievements bar.

Join us for Adobe Campaign Community Q&A Coffee Break on Thursday, March 7th at 8am PT with Eric Perrin, Gael Mouello, Chakravarthy Kalva, Bruce Swann and Sandra Hausmann.
SOLVED

Change of age field in ACC v7

Avatar

Level 3

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?

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

@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"/>

ParthaSarathy_0-1697220541453.png

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.

ParthaSarathy_1-1697220689519.png

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

View solution in original post

6 Replies

Avatar

Community Advisor

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--

Avatar

Level 3

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?

 

Avatar

Community Advisor

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.

Avatar

Level 3

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.

Avatar

Level 3

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:

A_B_SE_0-1697216724118.png

 

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

A_B_SE_1-1697217156069.png

 


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

Thanks for your patience.

Avatar

Correct answer by
Community Advisor

@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"/>

ParthaSarathy_0-1697220541453.png

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.

ParthaSarathy_1-1697220689519.png

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