Getting started with SQL Database Definitions
Overview
A grid is displayed, listing all existing SQL database definitions:
Within the grid, the following read-only columns are displayed (see below for property details)
Name
Contact Rules: tick or cross
Resolution
Definition: audience definition
Realtime Mode
Default: tick or cross
AI: tick or cross
State: one of Added, Edited, Deleted or Unchanged.
When no SQL database definitions have been configured, an advisory message is displayed.
SQL database definitions are listed alphabetically by name.
Selected SQL Database Definition Details
This section displays the details of the SQL database definition selected currently in the list. If no definitions have been configured, an advisory message is displayed.
The sections contains a tabset, which contains Details, Realtime, AI and Attributes tabs.
Details Tab
Name: the unique name used to identify the definition. Name is mandatory, can be a maximum of 100 characters in length, and must be unique across all definitions.
Description: an optional property that can be a maximum of 1000 characters in length.
Default definition: this checkbox is unchecked by default. It allows you to define one of the definitions as default. When creating a new basic selection rule, its Database Definition property will be set automatically to the default SQL database definition.
Enable contact rules: this checkbox, which is checked by default, allows you to specify whether contact rules are to be made available at basic selection rules using the current definition. Contact rules allow you to target on the basis of an individual having previously been contacted via RPI. When the property is unchecked, a Resolution level property replaces Audience definition. Note this property is not shown when configuring SQL database definitions in a NoSQL data warehouse/SQL auxiliary database environment.
Resolution level: this dropdown is shown when Enable contact rules is unchecked. It lists all resolution levels configured at the current RPI tenant. The default resolution level is selected automatically. Note that selection of a resolution level is mandatory if the property is shown.
Audience definition: this dropdown is shown when Enable contact rules is checked. It lists all audience definitions configured at the current RPI tenant. The default audience definition is selected automatically. Note that selection of an audience definition is mandatory if the property is shown.
Additional Database Keys: this property lists all database key column names that are present in the current definition's resolution table (excluding the default resolution key). Each is accompanied by a checkbox (unchecked by default). You can select additional database keys for use at a basic selection rule utilizing the current SQL database definition. A message is shown if no additional keys are listed.
Realtime Tab
The Realtime tab allows you to specify how a basic selection will be evaluated in a realtime context. It contains the following:
Realtime mode: this dropdown property allows you to specify whether rules using this definition will query the database directly, or use in-memory JSONPath queries to query the RPI Realtime cache. It exposes the following values:
Run as SQL (the default value)
Use standard attributes with JSONPath
Use SQL aux parameter attributes with JSONPath
Use NoSQL aux parameter attributes with JSONPath
NoSQL collection definition: this dropdown property is only shown when Realtime mode is set to 'Use NoSQL...'. It allows you to select a NoSQL DB collection definition from which realtime attributes will be sourced. The property is mandatory when shown.
Lookup key: this attribute property allows you to specify a lookup key to be used when selecting visitor details. It is displayed when Realtime mode is set to 'Use standard...' or 'Use SQL aux...', and is mandatory in the latter context. The selected attribute must be a database column attribute. If Realtime mode is set to 'Use standard...', the selected database column attribute must also be compatible with the must be compatible with the SQL database definition's Audience Definition's resolution level.
Always fetch data: this checkbox, which is checked by default, allows you to specify whether RPI Realtime should always fetch data from the database. It is only shown when Realtime mode is not set to 'Run as SQL'.
Time To Live In Cache: this integer property, which is only shown when Realtime mode is not set to 'Run as SQL', allows you to specify the length of time for which data can be persisted in the realtime visitor cache. It defaults to 1 day. If set to 0, data remains in the cache in perpetuity.
Allow run as database query: this checkbox is also only when Realtime mode is not set to 'Run as SQL'. It is unchecked by default. It allows you to specify whether a rule can run as a database query if realtime attribute mappings are missing. When checked, if attributes used in a rule are unmapped (thereby preventing direct querying of the cache), RPI will query the database directly.
Artificial Intelligence (AI) Tab
This tab allows you to specify whether the ability to generate criteria using AI within a basic selection rule will be enabled at rules using the current definition.
It exposes the following properties:
Enable AI: this checkbox allows you to specify that the ability to create basic selection rule criteria using AI prompts will be made available at rules based on the current definition. By default, the property is unchecked. When checked, and when no o/s changes are present within the definition, the Update AI Model button (documented elsewhere) is enabled.
Model Index Name: the read-only unique ID assigned to the definition's AI model.
Model Last Updated: a read-only property displaying the date and time when the model was last updated against the selected SQL Database Definition's attributes.
Attributes Tab
The Attributes tab allows you to define a hierarchical tree structure of the tables and attributes that are to be mapped within the current SQL Database Definition. Any attributes mapped in this context will be available to be added as criteria with a basic selection rule. Tables are used to organize criteria. For more information, please see the basic selection rule documentation.
The Attributes tab contains the following:
Toolbar: exposing the following:
Add Table: clicking this button adds a new, unconfigured table to the treeview. The new table is presented in Edit mode and is added after any existing child tables.
Remove: this button is enabled when a table or attribute is selected in the treeview. Invocation removes the selected item without displaying an 'Are You Sure?' dialog. Any child tables and/or attributes are also removed.
Treeview: the treeview is used to define the hierarchical structure of tables and attributes within the SQL database definition. A new definition contains a single top-level node called 'This Definition'.
You can add tables to This Definition, and nest tables within tables, using the Add Table button. You can roll up or expand tables as required.
You can add attributes to This Definition, or tables within the treeview, by dragging them from the toolbox and dropping them in an appropriate position (onto This Definition, an existing table or onto the list of attributes within This Definition or a table.
When you drop one or more attributes, the following dialog is displayed, allowing you to specify whether the attributes are to be added as standard attributes, or as placeholders.
For more information on using placeholder attributes in basic selection rules, please see the Basic Selection Rules documentation.
You cannot include Model Project, Exists in table or Parameter attributes. An informational tooltip is shown on hovering over an attribute.
It is optional for This Definition to contain tables and/or attributes. A definition without tables or attributes can be used to enable contact rules only at a basic selection rule.
It is mandatory for a table to contain at least one nested table or attribute.
The treeview is ordered with tables displayed first, in alphabetical order, with attributes, also in alphabetical order, afterwards. The same ordering applies within a table.
Tables can be shown in display or edit modes. When in display mode, the following are shown at a table:
Display name: read-only; if not provided, '[No name]' is shown.
Database table: read-only; if not provided, '[No table chosen]' is shown.
Edit: clicking this inline button places the table into edit mode.
Add child Table: clicking this inline button adds a child table to the current table. The new table is presented in edit mode.
When in edit mode, the following are shown at a table:
Display name: this mandatory text field can be a maximum of 100 characters in length and must be unique within This Definition.
Database table: you must browse for a table using the Choose Database Item dialog (or select a recently-used table from the list).
Exit edit mode: clicking this inline button returns the table to display mode. Dragging an attribute from the toolbox onto the table has the same effect.
Add child Table: clicking this inline button adds a child table to the current table. The new table is presented in edit mode.
When Realtime mode is set to one of 'Use SQL aux attributes...' or 'Use NoSQL aux parameter attributes...', an inline Edit button is available when hovering over an attribute.
Clicking the button displays the following in a dialog:
Realtime Attribute: this attribute property is used when executing in-memory queries. You can populate it by browsing for an attribute (the dialog is closed when you drag an attribute from toolbox, as, in doing so, RPI assumes you are adding another attribute to the list). Having populated the property, inline View File Information and Clear buttons are shown.
If the SQL database definition's Realtime mode property is set to 'Use NoSQL...', an attribute must be mapped to a NoSQL parameter attribute from the NoSQL collection definition selected in the Realtime tab. If set to 'Use SQL aux...', the attribute must be mapped to an map to attribute from same database as the supplied Lookup key.
List JSONPath: this text property is only shown when Realtime mode is set to 'Use aux...' and a Realtime Attribute's data type is List. It allows you to define JSONPath to identify the data element to use within the list attribute.
Exclude from AI Model: this checkbox is unchecked by default. When checked, the attribute is not passed to the definition's AI model at invocation of Update AI Model.
The Edit button is not shown if Realtime mode is set to one of ‘Run as SQL’ or ‘Use standard’…'.
Placeholder attributes can also be shown in read-only or edit mode. When in read-only mode, a placeholder attribute is shown as per the following example:
An Edit button displays the following:
The following properties are shown:
'[Attribute Name] 'Placeholder:' [Parameter Name]'
Parameter Name: this mandatory, updateable text field defaults to the value '[Attribute name] Placeholder'. Parameter Name must be unique within the current SQL database definition.
Description: optional.
Default Value: optional; set using a data type-specific control.
Use Sys Variable: this checkbox is unchecked by default. When checked, the Sys Variable property is enabled, and Default Value, Use Values from List are disabled.
Sys Variable: this Text property is blank by default. It is mandatory when enabled. You can use the property to specify the name of the system variable referenced by the placeholder attribute. More details on system variables are provided at the Framework documentation.
Use Values from List: this checkbox is only displayed if the attribute's data type is String. A checkbox, it is unchecked by default. When checked, the [List] dropdown is shown.
[List]" this dropdown is shown when Use Values from List is checked. It lists all currently-configured local string Value Lists. When displayed, selection of a list is mandatory.
OK button
Only a single placeholder attribute can be in edit mode at a time.
A Remove button is shown when hovering over the placeholder attribute. Clicking it removes the attribute without displaying an 'Are You Sure?' dialog.