Alteryx Connect Knowledge Base

Definitive answers from Connect experts.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Bulk Update of Data Assets in Connect

Alteryx
Alteryx
Created

In this article, you will learn how to update data assets in bulk using the import and export feature in Connect. In our examples we'll be updating data asset owners and changing the group membership of users by exporting, updating, and later importing an XLS file. But before we start with the examples, let's have a closer look on the asset fields we'll be working with.

Prerequisites

Product - Alteryx Connect
Product - Microsoft Excel

 

Asset Fields

Let’s start by explaining what the asset fields are, how they behave during the import and changes of data assets. As an example, let’s export the Glossary to view the asset fields. Go to main menu > Glossary. Use the option Export to Excel.

idea Skyscrapers

Open the downloaded XLS file. You can see different sheets with various asset fields (columns). The most important fields to be mentioned are: 

  • xid: A unique identifier of a data asset. 

  • parent: A data asset xid which is superior to the data asset specified in the column xid. 

  • description: More details about a data asset can be added in the description field. 

  • source: Xid of a data asset in the relations sheet. This is the data asset for which we’ll be changing the value of a certain field. 

  • target: Xid of a data asset in the relations sheet. This is the data asset that will be displayed as a value of a certain field. 

  • field code: A code that specifies the relation, such as entryOwner represents the data asset owner. 

Export and Import Restrictions

  • Import of XLS files: Restricted to Administrators only. 

  • Export of XLS files: Anyone with View access can export an XLS file.

Replacing the Data Asset Owner in Glossary
 

Let’s say you want to change the owner of data assets located in Glossary. First, you’ll need to identify the xid of the new data asset owner. 
 

Identify the Xid of the New Data Asset Owner

There are two ways to identify the xid of a person. Either directly in the People folder or exporting the People folder as an XLS file.
 

Identify the xid of a person directly in the People folder:
 

  1. Go to People folder and select the person to be the new data asset owner.  
    idea Skyscrapers

  2. Note down their xid visible in the URL. 
    idea Skyscrapers


Identify the xid of a person by exporting the People folder:
 

  1. Export the People folder. The file name is People.xls.  
    idea Skyscrapers

  2. Open the file People.xls. To identify xids of a new data asset owner, go to the person sheet. For example, let’s identify the xid of Annie A. Note down the xid as you will need it later.  
    idea Skyscrapers

  3. Export the data assets you want to update using the option Export to Excel. In our example, let’s export the whole Glossary.  
    idea Skyscrapers

  4. Open the XLS file and go to the term sheet. Identify the xids of those assets for which you want to change the owner. In this example we want to change the owner of these two data assets: Useful Life and Liability.  
    idea Skyscrapers

  5. Go to the relations sheet. Identify the xid of the data assets by searching them in the source column. The field_code that specifies the data asset owner is entryOwner.  
    idea Skyscrapers

  6. In the relations sheet, put the xid of the new owner to the target column. In our case, we want Annie A. to be the new data asset owner for these terms. Take the xid from the step 2 and put it to the target column for those data assets you want to change.  
    idea Skyscrapers

  7. In the relations sheet add a new column _flag. To change the data owner, you have to create this special column before uploading the XLS file back to Connect. If you change the owner in the XLS file without using a column _flag, the owner won’t be replaced but added. The original value will be kept in Connect and the new one will be added. It means the asset will have two owners. 

    Legal values of this field are “replace”, “remove” or empty field. “replace” means that all data about this relation will be replaced with value in this row. “remove” means that this entry will be removed. Empty field means the current entry will be left untouched.  

    idea Skyscrapers

  8. To replace the owner of data assets, add value “replace” to the column _flag for those assets you want to change.  
    idea Skyscrapers

  9. Save the file. Navigate to Administration Console and use the Upload section to import the file to Connect. 
    idea Skyscrapers

  10. Wait for import to finish. You will get a status message with more details about the import.  
    idea Skyscrapers

  11. After the import is finished, check the results of the import. In this case check if the data asset owner was changed. 
    idea Skyscrapers

Change the Group Membership of Users
 

In this example, we want to add users to the Certifiers group. We are not about to create or remove any entries, but we only want to change the membership of users. In such a case, we will be updating the relations sheet only.

  1. Export the People folder. The file name is People.xls.  
    idea Skyscrapers

  2. Open the file People.xls. To identify xids of people you want to assign to another group, go to the person sheet. For example, let’s identify the xid of Annie A.  
    idea Skyscrapers

  3. Paste those xids into the relations sheet, column target. Let’s paste the xid of Annie A. to the column target.  
    idea Skyscrapers

  4. Go to the group sheet to identify xid of group you want them assign into. In this case, we identify the xid of the Certifiers group. 
    idea Skyscrapers

  5. Paste that one xid next to the relations sheet, the source column.  
    idea Skyscrapers

  6. Add name of the relation to the column field_code. In this case we want to add Annie A. as a member to the Certifiers group.  
    idea Skyscrapers

  7. Save the file. Navigate to Administration Console and use the Upload section to import the file to Connect. 
    idea Skyscrapers

  8. Wait for import to finish. You will get a status message with more details about the import.  
    idea Skyscrapers

  9. After the import is finished, check the results of the import. In this case check the Certifiers group. Go to main menu > People > Groups > Certifiers > List of Users. 
    idea Skyscrapers

If you are experiencing any issues with a specific case regarding import or export to Connect, please contact the Customer Support .

Comments
Alteryx Certified Partner
Alteryx Certified Partner

One thing that I would like to add is when we are conducting updates frequently we are also do additions and updates at the same time (upserts) and if you leave an Xid blank it will actually populate a new one for you. This is also good to note if you happen to be copying and pasting and forget to address finding an existing record by locating the Xid, it will actually create a new record. 

 

Sometime when we are doing these bulk updates/additions we want to find out which ones were done in bulk by a user/process and we will create our own IDs with a suffix or prefix like "AUTOe634-202c-456c-8c3d-84487c031f2d". *Please note this only works with new assets.