Rogier Smit
Read all my blogsToday’s blog is about how to utilize the data workbench for importing and/or updating marketing attribute values in SAP Hybris C4C.
The data workbench functionality has been around for a while now; although little has been written about this specific topic. The purpose of this blog is to provide you some guidance through the process which could help you to execute your next data migration challenge with ease.
How to migrate attribute values
To get started right away, just open the HTML5 user interface and navigate to Data Workbench work center in the top menu. You should find 4 options there: Monitor, Import, Update and Export. In case you do not have the work center available or if you are missing options this should be configured in the business role access restrictions settings.
Migration Scenarios
In this tutorial, we cover 2 migration scenarios:
- Importing new attribute values to existing customers with no pre-existing values;
- Updating preexisting attribute values with new values.
For each scenario, it is required to follow a different approach.
Scenario 1: Import new Attribute values
Step 1:
Go to Import and select the Attribute Assignment object. Download the CSV file for both the Attribute Assignment and the Attribute Assignment Item object. Now you will have 2 import templates available in CSV format.
Step 2:
Open both CSV templates in MS Excel. These files are named by default:
- Template_BusinessAttributeAssignment;
- Template_BusinessAttributeAssignmentItem.
Template nr. 1 will be used for the Attribute assignment to the Business Partner i.e. Account or Contact Person. Template nr. 2 will be used to specify the Attributes and Attribute values that you want to import.
Step 3:
Open the first template and fill-in the 3 columns as follows. In the first column ‘ExternalKey’ a unique key should be entered to identify the line and assign this to the BusinessPartnerID in the third column.
The second column ‘ObjectTypeCode’ should be filled with value 147 by default, which is a technical code for the system to determine this is about attribute value migration. Save file as CSV file in Excel. Make sure that a comma (,) is being used as separator and no other character.
The first import file should look something like this:
Step 4:
Now open the second template and fill-in the 6 columns as follows.
- In the first column ‘ExternalKey’ again a unique key should be entered to identify the line of the attribute assignment value. Take notice that this should be unique thus different from the external keys that had be used in the first file.
- The second column should refer to the external key that was used in the first file. With this reference you will assign the attribute to the correct Account or Contact person.
- The third column will contain the Attribute values that you want to assign to the business partner. For this you use the technical keys as configured in the Attribute setup in case you are working with drop-down list values.
- Now the forth column is the technical name of the Attribute Set, as per the system configuration.
- The fifth column ‘ObjectTypeCode’ should again be filled with value 147 by default.
- Finally, the sixth column contains the technical name of the Attribute itself.
The second import file should look something like this:
Step 5:
After completing both import files now open the IMPORT menu again in the Data Workbench and first select the Attribute Assignment function. Now click on the Next button. Browse to the first import that you prepared and click on upload.
If uploaded successfully, click 3 times on the next button in the right bottom. Accept the proposed mapped, just proceed clicking to the final screen where you can click on the Import button. Proceed and click on the import button.
Step 6:
Check the results in the MONITOR section of the data workbench. If everything went successfully it should state status Finished with the quantity of Successful records. If failed then it should state the number of errors. You can download the imported file from here and review the error messages on the records.
Step 7:
Now repeat steps 5 and 6 for the second import file which contains the attribute value items. Just click through the same screens and review the results in the monitor. If you have successfully imported both files you can verify the results on the Accounts and/or Contact Persons.
Scenario 2: Update Attribute values to Existing attributes
This scenario is slightly different than the previous one. The reason for this is that we now want to update the records in the database that are already existing. This is a bit more complicated because we need to identify the database records with technical identifiers called ObjectIDs.
Step 1:
Go to the EXPORT function in the Data Workbench and again select the Attribute Assignment and the Attribute Assignment Item objects (one at a time). Now click on the next button which will show a screen with the number of total records and then click on Export Data. In case you have many records then you should limit your selection.
Step 2:
Go to MONITOR section and download both export files by clicking file names.
Step 3:
Open both files in MS Excel. In the first file, the attribute assignment, you will see almost the same structure as we have seen in the first import scenario. The only difference is that we see Unique Identifiers as ObjectIDs. By these technical identifiers we can locate the records in the database. For updating existing records we should use these identifiers.
File 1:
Now open the second file with the Attribute Assignment items. Here you will see the attribute values in the first column. In the second column the identifiers of the assignment items (BLUE) and in the third column the reference to the ObjectID of the first file (YELLOW).
This means you can link back the assignment item records to the business partner ID by matching the ObjectID in the third column of the second file with the objectID of the first column in the first file. Basically, match the yellow line of the first file with the yellow lines in the second file.
This can be done with VLOOKUP in excel or in case you have a huge amount of records (a million records) it would be better to use a tool such as MS Access. This tutorial does not cover the use of Excel nor Access.
File 2:
Step 4:
In case you want to modify (update) existing attribute items, just take the second file you exported in the previous steps and modify the values in the first column. Then save it again in CSV format.
Step 5:
Finally go to the UPDATE section of the Data Workbench. Now select Attribute Assignment Item and click on the Next button. Browse to your modified CSV file and click again on UPLOAD, then click on Next followed by clicking on Update. This should update the existing records with the modified values. Again review the results in the MONITOR section.
Step 6 (optional):
In case you want to add new records to Existing Assignments (instead of modifying existing values). Just follow the same steps as in step 3-4-5 but now you leave the second ObjectID column empty. Then C4C will automatically assign a new unique ObjectID to those records during the UPDATE. The ParentObjectID should still refer to the one of the Business Partner. Also make sure to use the correct references to the Attribute Set and Attributes in the last two columns when adding new entries.
Pro tip: it is also possible to delete records with the Update function in the data workbench. For this you add “ToBeDeleted” as the last column to the file. Then for the records to be deleted, set the value as “true” in “ToBeDeleted” column. Upload the file in the Update function and these records will be deleted from the system.
That’s it! Hopefully this information has been helpful to you. The principles of this migration method remain pretty much the same for other objects that can be imported and modified with the data workbench. So, this information can be applied to any object. Good luck! Cheers!
Receive our weekly blog by email?
Subscribe here:
2 responses to “Migrating Marketing Attributes With Data Workbench”
Thanks for sharing this info.
But it’s not working as expected. I’m working on the scenario (1), i downloaded the 2 files but the fields are different that you mention. I don’t have the ‘Object Type Code’. I have the following fields for the header file:
External_Key Registered_Product_ID Business_Partner_ID Business_Partner_External_Key Contact_External_Key
And I have the following for the Item file:
External_Key Business_Attribute_Assignment_External_Key Attribute_Set_ID Attribute_ID Value
In my case it does not work when the Accounts already have another attribute assigned.
I have the version 1904 in my tenant. Do you test this functionality recently ? I wonder if this has been changed in the last upgrades
Thanks in advance
Hi Dante,
Indeed, I checked your findings and it seems that SAP updated the format of the import files. So it does not fully match anymore the format mentioned in this blog.
Nevertheless, you should be able to proceed as normal with the updated file structure. Object Type Code is missing, but it was a fixed value anyways, so that might be the reason why it is not there anymore. I have not test it myself anymore recently as I did not run into these type of migrations on my last projects.
For about the case that you mentioned regarding updating already assigned records. For that, you should either remove the records first by using the “ToBeDeleted” parameter set to “TRUE” in your update file, or you can also directly update the existing records with the update function within the workbench. I recommend that you first export those records with the export function in order to retrieve the unique identifiers. Then edit that same export file and re-import this file through the UPDATE function as shown in this screenshot (click url): http://bit.ly/2KaL4iU
Hope this is helpful!
Kind Regards,
Rogier