Dataset usage in User Import Preprocessor

[fullmoon_rout]

Hi Team,

We are trying to call pre-processor query to update location, description and 2 more attributes, using  Dataset in Saviynt.

We have used below query in User Pre-processor Config JSON while importing user via CSV file. We are fetching the location description from dataset using the location code  from HR source. For eg. locationcode = SYD, location description from dataset = SYDNEY:
{
"ADDITIONALTABLES": {
"USERS": "SELECT username,statuskey,termdate,location FROM USERS",
"dataset_values": "select attribute1,attribute2 from dataset_values"
},
"COMPUTEDCOLUMNS": [
"termdate","CUSTOMPROPERTY46","locationdesc"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.termdate = case when NEWUSERDATA.statuskey = 0 and CURRENTUSERS.STATUSKEY = 1 then curdate() else CURRENTUSERS.termdate end",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY46 = CASE WHEN CURRENTUSERS.USERNAME IS NOT NULL THEN 'EXISTS' ELSE 'NEW' END",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.locationdesc = CASE WHEN CURRENTUSERS.location = NEWUSERDATA.location THEN CURRENTUSERS.locationdesc ELSE (select CURRENTdataset_values.attribute2 from CURRENTdataset_values where CURRENTdataset_values.datasetname = 'FetchLocationDesc' and CURRENTdataset_values.attribute1 = NEWUSERDATA.location) END"
]
}

However, the out of the computed columns, everything works fine except the location attribute because of the dataset. It is not working, and below error in logs.

"2023-10-08T23:51:52.636+00:00","ecm","","","","2023-10-08T23:51:51.67437447Z stderr F 08-Oct-2023 23:51:51.674 WARNING [http-nio-8080-exec-60] groovy.sql.Sql.executeUpdate Failed to execute: UPDATE TEMPNEWUSERS_481376 LEFT JOIN TEMPUSERS_481376 ON TEMPNEWUSERS_481376.USERNAME = TEMPUSERS_481376.USERNAME SET TEMPNEWUSERS_481376.locationdesc = CASE WHEN TEMPUSERS_481376.location = TEMPNEWUSERS_481376.location THEN TEMPUSERS_481376.locationdesc ELSE (select CURRENTdataset_values.attribute2 from CURRENTdataset_values where CURRENTdataset_values.datasetname = 'FetchLocationDesc' and CURRENTdataset_values.attribute1 = TEMPNEWUSERS_481376.location) END because: Table 'ssminlp.currentdataset_values' doesn't exist"
"2023-10-08T23:51:52.636+00:00","ecm","","","","2023-10-08T23:51:51.675040677Z stdout F 2023-10-08 23:51:51,674 [http-nio-8080-exec-60] ERROR services.ImportSAvDataUserService - Error while processing data: "
"2023-10-08T23:51:52.636+00:00","ecm","","","","2023-10-08T23:51:51.675044278Z stdout F com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'ssminlp.currentdataset_values' doesn't exist"

Could you please let us know if the usage of Dataset above is correct (we have referred below docs -

https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter03-User-Management/User-Impo...

Solved: How can I use datasets/custom tables to populate a... - Saviynt Forums - 2783

Regards,

Fullmoon

[Darshanjain · Accepted solution]

Hi @fullmoon_rout 

Can you use the upper case while using datasets

"DATASET_VALUES": "SELECT attribute1,attribute2 from dataset_values"

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.locationdesc = CASE WHEN CURRENTUSERS.location = NEWUSERDATA.location THEN CURRENTUSERS.locationdesc ELSE (select attribute2 from CURRENTDATASET_VALUES where datasetname = 'FetchLocationDesc' and .attribute1 = NEWUSERDATA.location) END

Thanks

Darshan

[fullmoon_rout]

Hi @Darshanjain 

Thanks for responding, so it is case sensitive.

It is working after I changed case and added few columns in select query:

Working query:

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,statuskey,termdate,location,LOCATIONDESC FROM USERS",
"DATASET_VALUES": "SELECT attribute1,attribute2,DATASETNAME FROM DATASET_VALUES"
},
"COMPUTEDCOLUMNS": [
"termdate","CUSTOMPROPERTY46","LOCATIONDESC"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.termdate = case when NEWUSERDATA.statuskey = 0 and CURRENTUSERS.STATUSKEY = 1 then curdate() else CURRENTUSERS.termdate end",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY46 = CASE WHEN CURRENTUSERS.USERNAME IS NOT NULL THEN 'EXISTS' ELSE 'NEW' END",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.LOCATIONDESC = CASE WHEN CURRENTUSERS.location = NEWUSERDATA.location THEN CURRENTUSERS.LOCATIONDESC ELSE (select attribute2 from CURRENTDATASET_VALUES where DATASETNAME = 'FetchLocationDesc' and attribute1 = NEWUSERDATA.location) END"
]
}

Thanks again.

Regards,

Fullmoon