Bulk Update of Data Assets in Connect

[MattH]

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.

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.  
   

2. Note down their xid visible in the URL. 
   

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

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

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.  
   

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

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.  
   

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.  
   

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.  
   

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.  
   

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

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

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

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

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.  
   

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.  
   

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

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. 
   

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

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.  
   

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

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

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. 
   

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

[andrewdatakim]

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.

[BenMoss]

Hi @MattH 

We have created an automated process to consume an external business glossary (in the form of an xlsx file) and load it into Alteryx Connect.

When doing this we have identified that assets not included when we extract the dictionary for the 2nd time are not removed from Alteryx Connect. In cases where we would want to effectively do a 'full overwrite' is there a way to achieve this (for example, clearing the glossary before loading our file). Of course this would need to be automated rather than a manual task (as we already know how we can do this via the GUI).

Ben