TABLEINDEXES in USERS.sav

[RV]

We are using Schema User Job to import users into Saviynt through USERS.sav.   Can someone help me understand what TABLEINDEXES are used for?  We have been referring to Multiple columns (both standard columns and custom properties) in the 'PREPROCESSQUERIES'.   

1. Should we list all the columns referred including customproperties OR only the standard columns that has Indexes defined on USERS table?

2. How do we identify what columns are indexed on USERS table?

3. What is "currentusers" in the provided sample in documentation?  

  "TABLEINDEXES": {
    "currentusers": [
      "username",
      "location",
      "EMPLOYEECLASS"
    ],
    "CURRENTENTITLEMENT_VALUES": [
      "entitlementtypekey",
      "entitlement_value"
    ]
  },

[Darshanjain]

HI @RV 

TABLEINDEX's are used to indexing the columns ( Assuming you know why we use index ), Now to answer your questions

1. Should we list all the columns referred including customproperties OR only the standard columns that has Indexes defined on USERS table?-  These indexes will be applied on current users table ( its a temporary table which will be created before updating to users table ), now it depends if you are writing a complex sql query which you think you may need indexing you can put otherwise you can ignore this.

2. How do we identify what columns are indexed on USERS table?- for preprocessor actual users table wont be used, temp users table will be created and indexing can be done as per above

3. What is "currentusers" in the provided sample in documentation?  - Replica of users table ( Temp table )

Thanks

Darshan

[RV]

Thanks.  If my "PREPROCESSQUERIES" is referring both "currentusers" and "NEWUSERDATA",  then do you recommend adding both the tables and the columns referred from those tables?

[Darshanjain]

Hi @RV 

Currentusers and newuserdata tables are temp tables ( so it will follow currentusers index )

Note: Generally you can ignore the indexing part unless your queries are complex and time taking

Thanks

Darshan

[RV]

Okay.  We currently do not have any TABLEINDEXES in our USERS.sav and Saviynt is taking more than a minute to just process 3 lines/users from the file.   If the file has 700-800 user updates, it is taking hours to complete the User import and resulting a deadlock as other jobs/recons running at the same time.

[Darshanjain]

Hi @RV 

Okay thanks for the information, if its a simple update query and still taking time then it may be due to the rules as well, if there are many rules ( user update rule or tech rules ) and if you kept the condition to be true then it may take some time to check all rules for each user and then complete it, You can check the logs to see where exactly the time is being taken.

Thanks

Darshan 

[RV]

Yes.  We have below config values set to TRUE and Yes, we have 20+ Birthright Rules and another 15+ User update rules.     Based on what I am hearing from you, I think adding TableIndexes will not speed up processing Technical/User update rules.  Do you agree?   Any other way to speed up the process as we will be adding more Technical (Birthright) rules down the road.

#ZERODAYPROVISIONING=TRUE
#GENERATEEMAIL=TRUE
#CHECKRULES=TRUE

[Darshanjain]

No i am asking you to check the logs and see where the time is being taken, is it due to sql queries which you are using ( may be you can attach the preprocessor json being used ), if its the rules then there is no other way as it will check for users and then run, Once you clearly check the logs you should be able to determine that.

But with just 20+BR rules and 20+Update rules shouldn't take lot of time, anyways you can investigate accordingly 

Thanks

Darshan

[RV]

Sorry for distracting to other topic but it has something to do with the processing time.

 I do see the logs get very busy on the Technical rules that are configured with "Detective" and "Evaluate Manager Hierarchy" checkboxes ON.    When these rules are configured as "Detective",   does it check against all user population whenever the Technical rules is triggered from User update rule through "Re-run Selected technical rules" ?  Or do they check on all user population only when Detective Job is triggered?

[Darshanjain]

Hi @RV 

Thanks for notifying me that, Yes this is happening due to Evaluate Manager Hierarchy set as ON, Always keep this OFF, it will check for all users and this will anyways be deprecated.

You can keep the detective as ON and that will check for all users only when  Detective Job is triggered.

So you can remove that Evaluate Manager Hierarchy for all rules and set as NO,  ( it wont affect the functionality as well ) and try the schema user job which should be faster.

Thanks

Darshan