Creating model queries in SQL

The SQL Query Builder in Oracle Unity Intelligence Workbench allows users to write and manage data science queries directly in SQL. This eliminates the need to use MCPS and simplifies model setup.

To get started, navigate to:

Intelligence Workbench → Queries, and begin creating SQL queries for your data science models.

Data queries for data science models

Queries are essential to provide the right data to a data science model. High-quality input data directly impacts the accuracy and business relevance of the model.

Before committing a query to the model, ensure the following.

  • The query retrieves all attributes required by the model

  • Appropriate filters and aggregations are applied based on the business use case.

  • Both training and scoring queries use accurate and relevant filters for consistent model performance.

Access & Navigation

Currently, only ‘Instance Admin’ users can save queries from the ‘Queries’ tab in ‘Intelligence Workbench’. Data science users, however, can view the queries.

Steps to access ‘Queries’ in ‘Intelligence Workbench’

  1. Select ‘Intelligence Workbench’ within the Oracle Unity menu

  2. In the ‘Intelligence Workbench’, go to the ‘Queries’ tab – This tab displays the list of available data science queries in your tenant

    1. If this query is not enabled in your tenant, follow the below steps to enable the same

      1. Navigate to expertConfig

      2. Run the following JSON with the respective values of ‘mcpstenantID’ and ‘cxunityTenantId’ (Run GET -> cxunity -> features to get the IDs)

        1. Switch to POST -> cxunity -> features

        2. {"mcpsTenantId": "<tenantId>", "cxunityTenantId": "<appId>", "name": "DSQuery", "enabled": true}

    Query List Columns

    1. Algorithm – the algorithm associated with the query

    2. Type (Training/ Scoring/ Both) – indicates whether the query is used for model training, model scoring or both

    3. Name – The name of the query

    4. Count – Number of record retrieved by the query. Hover over the number to see the exact count

    5. Last Modified – Shows the user and the timestamp of the last modification

    Query Actions

    1. Edit – to modify the query

    2. Duplicate – to create a copy of the query

    3. Delete – to delete the query. Important - Ensure that no models using this query before deletion to avoid broken models

Creating a new query

When you click ‘Create Query’, a drawer opens on the right.

Required fields:

  • Name (required) – Enter a name for the query.

  • ID (auto-generated) – Derived from the query name and cannot be edited.

    • This will be used to identify the query in the model creation flow.

  • Description (optional) – Provide additional context or notes.

  • Algorithm (required) – Select the algorithm to associate with the query

  • Type (required) – Choose whether the query is for Training, Scoring, or Both.

Validation

  • The Name field provides inline validation (e.g., invalid characters or duplicate names).

  • The Continue/Save button becomes active only when all required fields are valid.

Actions

  • Continue – This continues with query creation.

  • Cancel – Closes the drawer without creating the query.

Clicking on ‘Continue’ should open the query editor opens with the below layout

  • ‘SQL’ tab – Main editor for writing and validating SQL

    • Full SQL editor with formatting and validation support.

    • Inline validation feedback; the Save button is disabled until SQL is valid.

  • ‘Sample’ tab – Preview of sample query results

  • ‘Attributes’ pane – Lists mandatory fields and data types for the selected model.

    • The query must return all required attributes; otherwise, execution will fail.

  • ‘Edit Details’ - Allows editing of Name, Description, Algorithm, and Type.

  • ‘Save’ – Save SQL and metadata changes.

Editing a query

Enables the user to edit an existing query. Changes made will overwrite the original query upon saving.

  • In the Queries tab, select Edit.

  • The query opens in the query builder in an editable format.

  • Make changes to the SQL or metadata as needed, then save.

Duplicating a query

Duplicating a query allows you to create a copy of an existing query. Use this option when you want to build a new query with the same or similar design as an existing one, without modifying the original.

  • In the Queries tab, select Duplicate.

  • A drawer opens with the query details pre-filled.

  • Update the Name (required) and any other fields as needed.

  • Save to create the duplicate query.

Deleting a query

Enables the user to delete a query. Ensure the query is not being used by any active models before deletion to avoid breaking dependencies.

  • From the Queries tab, select Delete.

  • Confirm deletion.

Please note that queries marked as ‘default’ cannot be deleted.

Important - Usage guidelines

Follow these best practices for optimal performance and compatibility

Guideline Details OK to use Not OK to use
Supported Joins

Only Transitive Joins are Supported

Standard join chains (transitive joins) are supported

 SELECT cust.ID FROM mcpsdw_Customer cust JOIN mcpsdw_Event event ON event.CustomerID = cust.ID SELECT ID FROM mcpsdw_Customer cust JOIN mcpsdw_Event event ON event.CustomerID = cust.ID
Aliases Always use meaningful aliases (avoid single-letter aliases) SELECT cust.ID FROM mcpsdw_Customer cust JOIN mcpsdw_Event event ON event.CustomerID = cust.ID SELECT ID FROM mcpsdw_Customer cust JOIN mcpsdw_Event event ON event.CustomerID = cust.ID
Case sensitivity Table names, column names, and aliases are case-sensitive—maintain consistency throughout.    
Reserved Keywords Use ANSI_QUOTES for reserved words (e.g., Date, Order, Primary, Sequence). SELECT ev."Date" FROM mcpsdw_Event ev SELECT ev.Date FROM mcpsdw_Event ev

Supported Functions

Functions Details
Row-Level Functions

Includes most scalar and date/time functions:

ABS, ACOS, ASIN, ATAN, ATAN2, CEIL, CEILING, CONCAT, COS, CURRENT_DATE, CURRENT_TIMESTAMP, DATEDIFF, DATE_ADD, DATE_SUB, DAY, EXP, FLOOR, HOUR, IFNULL, INSTR, LEFT, LENGTH, LN, LOG, LOWER, LPAD, LTRIM, MINUTE, MOD, MONTH, POWER, REPLACE, REVERSE, RIGHT, ROUND, RPAD, RTRIM, SIGN, SIN, SQRT, SUBSTRING, TAN, TIMESTAMP, TIMESTAMPADD, TIMESTAMPDIFF, TRIM, TRUNCATE, UPPER, YEAR
Window Functions

DENSE_RANK(<val>) OVER (PARTITION BY <val> [ORDER BY <val> ASC|DESC]*)

FIRST_VALUE(<val>) OVER (PARTITION BY <val> [ORDER BY <val> ASC|DESC]*)

LAST_VALUE(<val>) OVER (PARTITION BY <val> [ORDER BY <val> ASC|DESC]*)

LEAD(<val> [, offset, default]) OVER (PARTITION BY <val> [ORDER BY <val> ASC|DESC]*)

LAG(<val> [, offset, default]) OVER (PARTITION BY <val> [ORDER BY <val> ASC|DESC]*)

SUM(<val>) OVER (PARTITION BY <val>)
Aggregate Functions SUM, MAX, AVG, COUNT, MIN, GROUP_CONCAT, STD, STDDEV, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, VARIANCE

Error Reporting & Troubleshooting

If you encounter an error during SQL parsing or conversion, review the following

Error Likely cause Suggested fix
Query parse exception (line X, column Y) Syntax issue Review SQL syntax
Invalid MySQL syntax Uses non-MySQL syntax Validate against MySQL standards
Missing alias Unaliased expressions or aggregates Add explicit aliases
Unquoted reserved words

Using MySQL keywords without quotes

See here.

 Use double quotes (" ")
Invisible whitespace Hidden spaces or tabs Reformat query
Multiple Source IDs issue One master ID with multiple source IDs Use MCPS for such cases

Steps to enable Queries in Intelligence Workbench.

  • Navigate to expertConfig.

  • Run the following JSON using your respective tenant IDs:

{"mcpsTenantId": "tenantId", "cxunityTenantId": "appId", "name": "DSQuery", "enabled": true}

  • You can retrieve the IDs by running GET → cxunity → features.

  • Then execute POST → cxunity → features with the JSON above.