Catalog Search is fast again

Hello

My name is Tomas Särnhammar and I am a developer at Agresso HRM.

We are now rolling out release 4.4 to the first customers and last week it was time to upgrade the Market Place (http://provider.nextlearn.net/) used by many Swedish Customers like Telia and Saab to search for and enrol on courses by commercial providers such as AddSkills, AchieveGlobal, Lexicon etc.

The really big news i 4.4 is that the organization hierarchy can be arbitrarily deep. This is used in contracts and filtering so that contracts and filters on a higher level and valid on lower levels unless an organization on a lower level has its own contract or filter. This makes contracts an filtering both simpler to use and more powerful. We recognized early on in the development that this may be a performance problem if we where not careful. The reason is the that the rules for which filter and contract that is valid for a given seller and customer organization can be complicated:

1. The amended filter rules can be complicated, by filtering on category and activity type etc.
2. Both filters and contracts are typically done on a high level in the organization hierarchy and this means that for most organizations we must search up in the hierarchy to find the first organization with a contract and filter.

For filters we saw during development that evaluating the rules "in run time" was too slow, and we solved that by creating the system job "B2bAccessUpdateJob" that evaluates all filters (all recent changes to filters actually) and stores the result in a way that is fast to use in the catalog search, an indexed table in the database. This job must be scheduled to run periodically in order to keep the filters updated.

Initially we thought that the organization hierarchy search for contracts was fast enough as we store the hierarchy itself in a search-friendly way (again a separate indexed table). That was not the case.

When we upgraded the Market Place the database server almost stopped. The Market Place is an Agresso HRM talent management portal configured to accept catalog searches via a web-service interface from other portals. This means that the catalog search load is much higher than on a typical (or even large) installation.

It turned out that the contract search was the culprit. It was implemented in a view in the database, and it was too expensive to select from in the search. Last week we changed that using the same technique we used for the filters. We created a table that is the "expansion" of the view, meaning that the table has the same columns as the view, and it should contain the same rows. On the the table we created indexes that are relevant for the search. The same system job that keeps the filter tables updated, was amended to update the new contract table too. Every time the system jobs runs, the rows in the table are updated to match those of the view.

This update returned the load on the database server from 100% to a healthier 15%. The catalog search time for individual searches went from sometimes 30 seconds to near-instant in all cases.

regards

Tomas Särnhammar

Release notes 4.4.3815

Today we released 4.4.3815 with bug fixes for 4.4 the most noticeable improvement is the speed of activity search but there is a lot of other fixes as well.

If you would like to have a patch for you version, send a note to support.hrm@agresso.se stating your current version.

The version number can be found on the System Administrators start page.

The release notes are both available in our Agresso AB extranet if you don't have an account yet I would recommend you to sign up for one there will be much more information there in the future.

 

/Christian

Configuring system jobs

There are several system jobs in Agresso HRM talent management portal that are really powerful and useful. In this blog I will walk thru each of them and what their general functionality is.

First go to the jobs under System Administrator/General Schedule System Jobs to get the list of available jobs in your portal.

Click on Add Schedule

Under type you can se a list with the jobs:

• NextLearn.CompetencePortal.Action.Competence.SignOffNotifier
• NextLearn.CompetencePortal.Action.Competence.UserCompetenceExpirationNotifier
• NextLearn.CompetencePortal.Action.News.NewsMailJob
• NextLearn.CompetencePortal.Action.Reports.RSOrphanSubscriptionsRemover
• NextLearn.CompetencePortal.Integration.FileIntegrationJob
• NextLearn.CompetencePortal.Model.Competence.ProcessCompetencePrecondition
• NextLearn.CompetencePortal.Model.Contracts.B2bAccessUpdateJob
• NextLearn.CompetencePortal.Model.Evaluations.EvaluationReminderJob
• NextLearn.CompetencePortal.Model.Learning.ReservationReminderJob
• NextLearn.CompetencePortal.Model.Learning.OccasionReminderJob
• NextLearn.CompetencePortal.Model.Learning.NoticeSenderJob
• NextLearn.CompetencePortal.Model.Mpsync.MpSync
• NextLearn.CompetencePortal.Model.Survey.SurveyTestMailNotifier

NextLearn.CompetencePortal.Action.Competence.SignOffNotifier, KP

This job notifies the the manager of the employee that needs a signoff on a competencies, with a email and attachment that there is signoff awaiting for that person. The look of the of the attachment can be customized with a reporting services report and the report that is to be run is specified in the job parameter.

NextLearn.CompetencePortal.Action.Competence.UserCompetenceExpirationNotifier

This job sends out notification email when a competence is about to expire, the recipients for this are:

• user
• Manager

You can specify a number of parameters on this job apart from the standard “when” to run it.

Parameter	Description
Report name	The file attachment on the email this file contains the actual competencies about to expire
Used for system role	What users should get reminders?
Executes X days before expiration	How long before the competencies is about to expire should the notification go out.
Stored procedure	What data should we look at, when sending out this notice?
Email body/Email text	textual content of the email, only one language

NextLearn.CompetencePortal.Action.News.NewsMailJob

The newsmail job processes all the news that you have chosen to send out as email when you created the news under News and information. This job doesn't have any properties.

NextLearn.CompetencePortal.Action.Reports.RSOrphanSubscriptionsRemover

If you are using reporting services subscriptions, the SQL server and Reporting services engine adds System Sql server jobs that you might want to clear after execution. If you want the talent management portal to handle that for you enable this job.

NextLearn.CompetencePortal.Integration.FileIntegrationJob

This job automates the process of importing files, if you i.e want a integration with SAP or Agresso to synchronize users you can either set a ftp or a network share and the portal will automatically import any files ending up in this folder.

Files that can be automated are any file you can manually import in the portal The file structure for each of these files should be as a manual import and I refer to those guides for the actual file contents.

Parameters when importing:

Setting: You can chose what setting to use, default or any other installed (these settings are usually developed by us, and large enough for its own blog). Standard will suffice for the majority of you.

File format: The type of file you want to automate and that the job can expect, excel (2003), csv, or XML.

XSL parameters: if the setting supports parameters you can set them here (default has none). I.e specific orgid etc.

File path: Physical path of the file to be imported, i.e c:\integrationfiles\users\

Windows username/password: If IIS doesn't have access to the above path, specify user and password for a user that has.

Email reports to: If you want an email summarizing the result of the import specify you email here.

NextLearn.CompetencePortal.Model.Competence.ProcessCompetencePrecondition

This is a job that you can set up to run if you i.e change preconditions for a competence, have competencies/courses that expire. This job will then go thru all preconditions at intervals you specify. Process competence preconditions by calling job_processcompetenceprecondition.

• CompetenceCode and UserName are optional. If set only that competence and/or user will be processed
• if SetMissingInactive is set; competence elements will be set as inactive if preconditions are not fulfilled and was previously fulfilled and element has inactive flag.
• if SetFulfilledActive is set; competence elements will be set as active if preconditions are fulfilled and was previously not fulfilled and element has inactive flag.
• if SetFulfilledValidDates is set; valid from and to dates will be set if preconditions are fulfilled and was previously not fulfilled and element has valid dates. Valid from will be set to today's date, and valid to will be set according to expiration time on element.

NextLearn.CompetencePortal.Model.Contracts.B2bAccessUpdateJob

If you are using filter in your configuration this job should be set to handle organizations changes etc.

NextLearn.CompetencePortal.Model.Evaluations.EvaluationReminderJob

Scheduled Job that emails user and manager X days before an Evaluation meeting

Parameters:

• Numberofdays: The number of days before the meeting that the mail is sent out.
• NextLearn.CompetencePortal.Model.Learning.ReservationReminderJob

Sends out reservation reminder job, all the mails and content of the email is with the other Reservation email settings in application config. The property for this job is the expression editor where you can specify What, When and to whom you want to send emails just like the activity groups or user groups.

NextLearn.CompetencePortal.Model.Learning.OccasionReminderJob

This job is a very good feature where you in essence can specify your custom report to run on a reservation and mail the result to either a specific email address and/or keywords:

• enrolled
• enrolledby
• manager
• supplier
• activity (aka activity.email)
• administrator
• enrolledbyunlessmanager
• cancellationprocessor
• enrolledbyunlessmanagerorsupplier
• topmanager
• assistant

NextLearn.CompetencePortal.Model.Learning.NoticeSenderJob

This job automates the process of sending out reservation/booking documents you find under Documents.

It has the same expression editor, and recipient logic as the other notice mails.

NextLearn.CompetencePortal.Model.Learning.processcompletedactivities

Processes completed activities:

The processing includes:

• setting expiration date on reservation (all booked acts.) and applicationuser (non-booked web-based acts.) tables
• assigning goal competences and profiles to users (including expiration date)
• calculating fulfillment for users profiles (including expiration date) Activity Code and Supplier Code are optional.

If set only that activity will be processed. Note that setting only Supplier Code does nothing.

NextLearn.CompetencePortal.Model.Mpsync.MpSync

This job is only needed if you are integrating with the Competence Marketplace. If its enabled it will sync users and organizations up to the marketplace.

NextLearn.CompetencePortal.Model.Survey.SurveyTestMailNotifier

This job sends out the reminders that you should conduct a survey that you have been assigned. The actual mail contents and format is configured in application config.

Summary

While this was a pretty long blog, it summarizes the different jobs in Talent Management Portal and job scheduler interface is pretty easy to implement and is an excellent way of extending/customizing the portal even further. Next blog will be about exposing our goldmine user and organization data to any application thru a programmable interface.

Good luck with your configuration