Data Sets
Important
Information and features vary according to the roles to which you belong and the permissions associated with those roles. For more information, contact your module manager or your campus support team.
Data sets are used across the system to generate reports, run processes, select information you want to view and work with, determine communication recipients, tailor content, and more. Jenzabar delivers commonly used data sets with the system, and you can also create custom ones using InfoMaker and SQL queries as well as source code (e.g., imported contact lists). This allows you to leverage your own queries and data.
The Data Sets page provides a centralized location where all these Jenzabar-provided and custom data sets can be managed. On the Data Sets page you can:
Upload queries and select sources to create new data sets.
Control access to data sets.
View and download query columns and syntax to evaluate the query fields and create other similar data sets.
See interaction/automation plan associations to determine where and how a data set is being used and what interactions/automation plans would be impacted by changes.
Note
J1 Web does not offer SQL editing capabilities. Once uploaded, query fields cannot be changed; however, you can download the query syntax, make changes, and then upload it as a new data set.
In addition to the data sets Jenzabar provides, you can create custom data sets from SQL files, InfoMaker PBL queries, and imported files used as sources.
Data sets are categorized as either recipients and data merge options.
Recipient data sets can be used to group things, individuals, organizations in the database. For example, groups of students, groups of communication recipients, groups of account statements, and groups of courses.
Sample recipient query:
Data merge options data sets can be used to make database fields available for customization. Field placeholders are replaced with actual information when applied. For example, if you insert <<FIRST_NAME>> and <<MAJOR>> placeholders in a communication, they are replaced with the recipients’ first name and major.
Jenzabar provides the following queries that include frequently used data fields.
Candidate Details
Name Details
Student Details
Sample data merge query:
To use sources as data sets, upload communication contacts files from the Communication Contacts page in .csv, .xls, and .xlsx formats that include the following:
|
|
|
|
|
|
|
|
|
|
|
If the file is added to the system as a source, it shows up as an option when creating a data set. This can be helpful when an imported file includes a group of contacts you want to use repeatedly as a communication recipient list. Additional sources will be available in future releases.
Because data sets can include confidential information, you can limit who has access to them. When you create and edit custom data sets, you can select the modules you want to be able to access the data set. Anyone in a role with access to a selected module can see and use the data set when working in J1 Web.
From the Actions button, you can view detailed query syntax information for SQL files and InfoMaker queries. This shows you the table and field information the data set uses. You can troubleshoot issues, download a SQL file version of the query that can be used as a template for creating new data sets, and see which modules have access to the data set.
Also from the Actions button, you can use the Download Columns to Excel option to export SQL and InfoMaker PBL data set columns to Excel. These can be used to evaluate columns in the query, set up templates for uploading source files, and to determine database field names for use in data merge placeholders.
Recipient data sets can be used to select groups of information. This can be groups of any data in your database and can be used when selecting students, courses, holds, recipients, account statements, etc. in different areas of the product.
Note
If you want individuals to be able to select different data sets, be sure they are classified and permissioned correctly. Module access permissions are managed on the Edit System Role page.
Recipient data sets can be used to determine who receives a communication. For example, a data set based on a query with student names, years, and majors lets you send targeted emails to incoming students majoring in Science.
The data sets are selected when you send/apply an interaction, or an automation plan.
You can leverage Jenzabar-provided data sets or create your own using SQL and InfoMaker queries that let you use specific data fields for recipients. For example, a data set might include the MAJOR column that you can use as criteria to only send a communication to students in that specific field of study.
One data set is based on a query with student names, years, and majors, which lets us send targeted emails to incoming students majoring in Science.
Users can only select the data sets they can access. Access is granted through roles and module access.
Data merge queries can be used to make certain database fields available for customizing your communications. Fields are shown with placeholders that are replaced with actual information when the interaction is applied/sent. For example, if you insert <<FIRST_NAME>> and <<MAJOR>> fields in an email or text, the placeholders will be replaced with the recipients’ first name and major when the message is sent.
When you add the interaction that uses a data merge query to an automation plan, the same fields can be used to add rules to the interactions. These rules define criteria that determines who receives the interaction. For example, a “Welcome Back Students” automation plan might include a Computer Science club email. With rules and the merged data fields, you can ensure only students with a Computer Science major receive the email.
Select data sets to tailor the information shown when viewing details about an individual or organization. For example, a registrar can select a data set that includes enrollment and hold fields while an HR administrator can select a data set that includes benefits fields.
Users can only select the data sets they can access. Access is granted through roles and module access.
Data set information can be shown as real-time lists in the Data Set List block. For example, a course catalog data set can be used to show a list of all available courses.
 |
When the Data Set List block is configured, a simple query uploaded to the Data Sets page is selected. Simple query limits:
SELECTS only
Single statement
Display only
Simple queries can include HTML to link to websites, email addresses, etc.
Sample simple query with HTML:
SELECT firstname+' '+lastname, <a href="mailto:'+email+'">'+email+'</a>;' from AlternateContactMethod where email is not null
There are several ways to find a query:
From the Queries for drop-down, select Merge Data Fields or Recipients.
Access filter options by clicking the filter icon or link.
Select the checkboxes for the filters you want to use and then select or enter your search criteria.
Click the Apply button.
Note
You can save and reuse commonly used filters.
Click the Name or Type column header to sort.
Note
This option is only available for SQL and PBL data sets.
A detailed view of the data set lets you see the SQL statement details, modules with access to the data set, and interactions/ automation plans associated with the data set. This information lets you confirm the data set includes the fields you want and shows you where and how a data set is being used and what interactions/ automation plans would be impacted by changing or deleting it.
From the Queries for drop-down, select Merge Data Fields or Recipients.
Find the data set using the Quick Filters and/or by clicking on a Name or Type to sort.
From the Actions drop-down, select View Details. View Details window appears.
Note
Data Merge queries do not show associated automation plans because they can only be associated with interactions.
To download a SQL or InfoMaker data set as a SQL file, click Download query syntax. You can evaluate the query fields and use it as a basis for creating other data sets.
From the Queries for drop-down, select Merge Data Fields or Recipients.
Find the SQL File, InfoMaker, or Bold Reports data set you want to download to Excel.
From the Actions drop-down, click Download columns to Excel. Your .xlsx or .zip file is saved in the file location that you have set in your browser settings.
Warning
Use caution when editing a data set that is in use. Changes can impact recipients or the merged data fields used for customized content. To see a list of interactions and automation plans associated with a data set, select View Details from the Actions drop-down.
From the Queries for drop-down, select Merge Data Fields or Recipients.
Find the data set you want to update.
From the Actions drop-down, click Edit.
If the query is associated with any interactions or automation plans, a confirmation pop-up window appears. Click Yes, edit data set to proceed.
In the Edit Data Set window, make the needed changes. You can rename the query, select a different classification, add/remove modules with access to the data set, or select a different source, SQL file, or PBL query.
Remember data sets are used across the system and changes can impact interactions, automation plans, and other users utilizing it.
Click Update. Your changes are saved and the Edit Data Set window closes.
Note
You cannot delete data sets that are associated with interactions or automation plans. You can reassign the interactions/automation plans associated with the data set to another data set and then delete it.
From the Queries for drop-down, select Merge Data Fields or Recipients.
Find the query you want to delete.
From the Actions drop-down, click Delete.
Click Yes, delete query in the confirmation pop-up.
Click Yes, delete.
Not entirely. Desktop only uses InfoMaker queries. Data sets in Jenzabar One Web can not only be created from InfoMaker, but can also come from SQL queries and source codes.
Yes, your IT Administrator can upload them into the system and ensure you belong to a role with access to them. They may need to make some modifications to get the columns the system expects.
SQL queries must be created outside of J1 Web and uploaded into the system from the Data Sets page.
Query files are stored in the database.
Typically your IT Department or System Administrator creates the SQL query files. You can collaborate with them on the fields you’d like to use for customizing interaction content or recipients.
Yes, you can select a query in an InfoMaker PBLs.
This means the PowerBuilder Services URL isn't entered correctly in the System Administration hub, System Settings, Product Installs and Sign In page, PowerBuilder Services URL. Contact your campus support team for more information.
Yes, just like Desktop, Jenzabar provides commonly used data sets for J1 Web. We’ll provide more and more data sets with each release so be sure to check in.
Edit and Delete options are only available for queries you uploaded. You cannot edit or delete the Jenzabar-provided queries. You also cannot Delete queries associated with interactions or automation plans as this might impact functionality.
If a query isn't associated with any interactions or automation plans yet, the View option is unavailable.
The View associated interactions and sets option is available for recipient queries because those queries can be associated with both interactions and automation plans. The View associated interactions option is available for merge data fields queries because they can only be associated within the body and text of an interaction.
You can use the following query to see the data sets an interaction was applied to:
select distinct d.Code, d.Description, d.AppID from ManagedTaskSetDefinition s join ManagedTaskSetManagedTasks t on s.AppID = t.ManagedTaskSetDefinitionAppID join ManagedTaskDefinition d on t.ManagedTaskDefinitionAppID = d.AppID where ManagedTaskTargetListDefinitionAppID = '[The App Id Of the Data Set]'
To see the data fields included in the Jenzabar-provided data sets, select View Details from the Actions menu.