Accessing dataset information in User Import Preprocessor

[Ivan5533]

Hello everyone,

I have a question about how to access the data of a dataset in the User Import Preprocessor.

Use case: When we import the users from the HR system we have to store in the customproperty20 of the user the code of the continent he belongs to based on the country.

We have a dataset called 'countries_sites' in which we have already stored this information due to another use case, specifically in the attribute 1 is the country and in the attribute 4 the code of the continent to which the country belongs.

Following the information that I have found in the following post: https://forums.saviynt.com/t5/identity-governance/dataset-usage-in-user-import-preprocessor/m-p/5599...

I have seen that in ADDITIONALTABLES a global reference is made to the dataset '"dataset_values": "select attribute1,attribute2 from dataset_values"' and then in PREPROCESSQUERIES access the specific dataset mentioning the name of the dataset to access its information 'select CURRENTdataset_values.attribute2 from CURRENTdataset_values where CURRENTdataset_values.datasetname = 'FetchLocationDesc' and CURRENTdataset_values.attribute1 = NEWUSERDATA.location'

Is this a correct approach if I only want to access a single dataset in the User Import Preprocessor? Is it possible to access the specific dataset in ADDITIONALTABLES?
For example:

"ADDITIONALTABLES": {
"USERS": "SELECT username, country FROM USERS",
"DATASET_VALUES": "select attribute1, attribute4 from dataset_values where DATASETKEY in (select DATASETKEY from dataset where datasetname='countries_sites')"
}

and then directly access the dataset without having to call it again in PREPROCESSQUERIES

"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY20 = (select attribute4 from CURRENTDATASET_VALUES where attribute1 = NEWUSERDATA.country) END"
]

obtaining the following final result:

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,country FROM USERS",
"DATASET_VALUES": "select attribute1, attribute4 from dataset_values where DATASETKEY in (select DATASETKEY from dataset where datasetname='countries_sites')"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY20","CUSTOMPROPERTY20"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY20 = (select attribute4 from CURRENTDATASET_VALUES where attribute1 = NEWUSERDATA.country) END"
]
}

Additionally I would like to ask how is it possible that in the example post I have attached (https://forums.saviynt.com/t5/identity-governance/dataset-usage-in-user-import-preprocessor/m-p/5599...) you can access the dataset in PREPROCESSQUERIES by referencing 'DATASETNAME = 'FetchLocationDesc'' if when I try the following query "SELECT attribute1,attribute2,DATASETNAME FROM DATASET_VALUES" in the Data Analyzer all the DATASETNAME I receive have the value "REPLACE_MAPPING", is there something I am missing?

Thank you and regards,

Iván

[amit_krishnajit · Accepted solution]

Hi @Ivan5533 

In the additional tables, you can directly access the data stored in one dataset by adding SQL query similar to the following:

"DATASET_VALUES": "select ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3  from DATASET_VALUES where DATASETNAME='C_MAP'"

The above query will fetch the data only from one dataset. 

You are unable to see all the data in Data Analyzer which could be because Data Analyzer has a in-built limit of fetching only the top 100 rows based on the query. 

Try the query like the following to view data from the interested Dataset.

SELECT attribute1,attribute2,DATASETNAME FROM DATASET_VALUES WHERE DATASETNAME='<<NAME>>'

Hope this helps. 

[Ivan5533]

It works correctly and fits better to the use case we have.

Thank you very much for the clarifications Amit.

Regards,

Ivan