Creating SQL Sets

SQL Sets are SQL queries that return a collection of resource objects from the EmpowerID Identity Warehouse, such as all people who have been hired in the last week. You can add these Sets to Query-Based Collections (SetGroups) and use them to make dynamic RBAC delegation assignments.

You cannot create SQL sets with queries that delete, drop, or update objects in the Identity Warehouse. For example, DELETE FROM PersonCommunicationType WHERE CommunicationTypeID = 2 is not acceptable. If you create a SQL set with such a query, EmpowerID cancels the operation and displays a message stating that the SQL Filter (query) is invalid.

The Set Compiler Job must be enabled on at least one EmpowerID Server hosting the Worker Role service, and the person creating the Set must have certain Access Level assignments. The job is enabled by default on all EmpowerID application (back-end) servers.

To create a SQL Set

In the navigation sidebar, expand Identities and click Query-Based Collections (SetGroups).
From the Set Groups management page, click the Actions tab and then click Create Query (Set).
In the General section of the Set Details form that appears, select SQL Query from the Query (Set) Type field. This enables you to write a SQL query in the form.
Enter a name, display name and description for the Set in the Name, Display Name and Description fields, respectively.
Select whether the query returns a collection of people.
Click the Object Type drop-down and select one the appropriate object type for the query. For example, if the Set is to return a list of people, select EmpowerID Person.
In the Filter section of the Set Details form, in the SQL Query field, enter a SQL query that returns the desired objects. In this example, the Set returns all people hired within the last five days.
```
Select PersonGUID, FriendlyName FROM Person (NOLOCK) WHERE CreatedDate BETWEEN DATEADD(day,-5, GETUTCDATE()) AND GETUTCDATE(); 
```
Clicking Choose opens a dialog of SQL Query templates that you can use as a starting point for your query.

Queries must begin by returning the GUID and FriendlyName of the objects or they will fail when compiled.
Select Is Enabled to enable EmpowerID to compile the Set (selected by default).
Click the Schedule Interval drop-down and set the schedule for when and how often the Set compiles. In this example, the Set compiles once every day at 8 am. To do this:
- Set the Start and End dates
- Set the Interval to Daily
- Set the Iterations to 1
- Set the Times (compilation time) to 8:00 am
Select Do Not Allow Delete if you want to prevent users from deleting the Set in the Web interface.
Select Create Matching Set Group if you want EmpowerID to create a Set Group (Query Based Collection) when the Set is created.
Click Save.