OperationalDataChat

Versions

3.0.0

v3.0.0

Basic Information

Class Name: OperationalDataChat

Title: Operational Data Chat

Version: 3.0.0

Author: Todd Trottier, Catherine Baker, Evan Rasmussen

Organization: OneStream

Creation Date: 2026-02-11

Default Routine Memory Capacity: 2.0 GB

Description

Short Description

Convert natural language into SQL queries that execute against a given database.

Long Description

The Operational Data Chat app allows users to convert their natural language conversations into SQL queries that will be executed against a database. This, in turn, allows for insights about the users' data to be surfaced without the user needing to be technical. This capability is optimized for accuracy, leveraging advanced LLMs to generate SQL queries and execute them against the right database tables. The routine is designed to streamline the data analysis process, enabling users to focus on data-driven decision-making.

Use Cases

1. Natural Language to SQL Query Generation

This use case highlights the routine’s ability to convert natural language queries into SQL queries, enabling seamless access to tabular datasets. For example, a business analyst may ask, "Show me the total sales for the last quarter by region," and the routine generates an accurate SQL query tailored to the dataset and the specified SQL dialect. By eliminating the need for technical SQL expertise, the routine enhances productivity and accessibility, particularly for non-technical users. By streamlining the query generation process, the routine empowers users to focus on data analysis and decision-making.

Routine Methods

1. Init (Constructor)

Method: __init__
- Type: Constructor
- Allow In-Memory Execution: No
- Read Only: No
- Method Limits: The size of the input data will drastically impact the compute required for data processing. In particular, the number of unique categorical values of columns in the dataset acts as the main bottleneck. The data_processing method is run during construction and is where most of the time is spent when this method executes. For a dataset with 5.7M rows, 52 columns (42 of which are nvarchars), and a total of 1M unique string values across all columns, this method is expected to complete in around 2.5 hours with 100GB of memory allocated.
- Outputs Dynamic Artifacts: No
- Short Description:
  - Initialize the Operational Data Chat routine.
- Detailed Description:
  - Constructs a new instance of the Operational Data Chat routine. This method will run the Data Processing method, which creates an embedding store on the source data.
- Inputs:
  - Required Input
    - Routine Data Store: The storage location of conversation data.
      - Name: routine_data_store
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: OADatabaseResourceConnectionParameter
    - Source Connection: The connection information for the source data.
      - Name: data_connection
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: TabularConnection
    - Dialect: The SQL dialect to use when generating the SQL query.
      - Name: dialect
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: SqlDialect_
    - Business Facts: Enter the Business Facts.
      - Name: facts
      - Tooltip:
        
        Detail:
        
        Examples:
        
        A fiscal year begins on March 1st.
        
        Profit is calculated as x - y.
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: list[BusinessFact]
    - Number of Facts: Dictates the number of facts included in the context per SQL generation.
      - Name: num_facts
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: int
    - Column Definitions: Descriptions of each of the table columns.
      - Name: column_definitions
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: list[TableColumnDefinition]
- Artifacts: No artifacts are returned by this method

2. Add Business Facts (Method)

Method: add_business_facts
- Type: Method
- Memory Capacity: 2.0 GB
- Allow In-Memory Execution: No
- Read Only: No
- Method Limits: There are no limits for this method. This should complete in less than 20 seconds with 2GB of memory allocated.
- Outputs Dynamic Artifacts: No
- Short Description:
  - Adds new business facts to the existing list of business facts saved on the instance.
- Detailed Description:
  - Add new business facts to the routine instance that may be utilized in all subsequent requests to the agent via the nl_to_sql method.
- Inputs:
  - Required Input
    - Business Facts: Facts containing business-specific information.
      - Name: business_facts
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: list[BusinessFact]
- Artifacts:
  - Business Facts: Contains the updated business facts.
    - Qualified Key Annotation: business_facts
    - Aggregate Artifact: False
    - In-Memory Json Accessible: True
    - File Annotations:
      - artifacts_/@business_facts/data_/data.json
        
        Stored json data.
      - artifacts_/@business_facts/data_/schema.json
        
        The json schema of the json object stored in the 'data.json' file

3. Configure Scope Permissions (Method)

Method: configure_scope_permissions
- Type: Method
- Memory Capacity: 2.0 GB
- Allow In-Memory Execution: No
- Read Only: No
- Method Limits: There are no known limits to this method, as it simply creates scopes and stores corresponding queries.
- Outputs Dynamic Artifacts: No
- Short Description:
  - Configure query-level user or group permissions for the Operational Data Chat instance.
- Detailed Description:
  - This method creates one or more new scopes tied to a specific data filter, allowing power-users to configure different permissions for different groups of users. As an example, this routine allows power-users to ensure members of Group A only have access to records in the source table where Region = 'Southwest'. This method allows power-users to create the scopes with the relevant filters associated with each, but does not allow specifics users or groups to be assigned to those scopes. In order to complete this setup, it is advised that power-users assign the correct users or groups to their respective scopes using Xperiflow Administration Tools (XAT).
- Inputs:
  - Required Input
    - Scopes and Queries: Create one or more Scope definitions.
      - Name: scope_definitions
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: list[ODCQueryPermission]
- Artifacts: No artifacts are returned by this method

4. Create Web App (Method)

Method: create_web_app
- Type: Method
- Memory Capacity: 2.0 GB
- Allow In-Memory Execution: No
- Read Only: No
- Method Limits: There are no limits for this method. This should complete in less than 10 seconds with 2GB of memory allocated.
- Outputs Dynamic Artifacts: No
- Short Description:
  - Creates the v2 web app with async run/cancel, combined text+table bubbles,
- Detailed Description:
  - column display formatting, and configurable chat title / agent display name. The run id from this routine method along with the routine instance id are used to create the URL for the web app.
- Inputs:
  - Required Input
    - Chat Title: The heading displayed at the top of the chat interface.
      - Name: chat_title
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: str
    - Agent Display Name: The name shown on system/agent message bubbles.
      - Name: agent_display_name
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: str
    - Input Placeholder Text: Placeholder text shown in the message input box when it is empty.
      - Name: input_placeholder
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: str
    - LLM Chat Model: The LLM chat model used for all query processing, conversation naming, and error explanations.
      - Name: llm_chat_model
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: LLMChatModel_
    - LLM Embedding Model: The embedding model used for vector similarity search on business facts and column values.
      - Name: llm_embedding_model
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: LLMEmbeddingModel_
    - Theme Primary Color: Primary brand color. Used for: header bar background, send button, selected conversation accent bar, input bar focus glow. Any valid CSS color.
      - Name: theme_primary
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: str
    - Theme Primary Light Color: Light variant of the primary color. Used for: user message bubble background, selected conversation background, header text color surface. Any valid CSS color.
      - Name: theme_primary_light
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: str
    - Theme Primary Hover Color: Hover variant of the primary color. Used for: selected conversation hover, button hover backgrounds. Should be between primary and primary_light. Any valid CSS color.
      - Name: theme_primary_hover
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: str
- Artifacts:
  - Operational Data Chat Web App: Contains an object that holds the Operational Data Chat web app.
    - Qualified Key Annotation: web_app
    - Aggregate Artifact: False
    - In-Memory Json Accessible: False
    - File Annotations:
      - artifacts_/@web_app/data_/data.appref
        
        json file of data relating to web app

5. Data Processing (Method)

Method: data_processing
- Type: Method
- Memory Capacity: 2.0 GB
- Allow In-Memory Execution: No
- Read Only: No
- Method Limits: The size of the input data will drastically impact the compute required for data processing. In particular, the number of unique categorical values of columns in the dataset acts as the main bottleneck. For a dataset with 5.7M rows, 52 columns (42 of which are nvarchars), and a total of 1M unique string values across all columns, this method is expected to complete in around 2.5 hours with 100GB of memory allocated.
- Outputs Dynamic Artifacts: No
- Short Description:
  - Convert dataframe to ProcessedStatistics object containing statistics for table columns
- Detailed Description:
  - This method processes the data from the tabular connection and calculates statistics for string, numeric, and datetime columns. It also saves the table schema, business facts, table dialect, and column definitions for the user. This method is used in the routine constructor and also as a method to manually update the routine data and connection as needed.
- Inputs:
  - Required Input
    - Routine Data Store: The storage location of conversation data.
      - Name: routine_data_store
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: OADatabaseResourceConnectionParameter
    - Source Connection: The connection information for the source data.
      - Name: data_connection
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: TabularConnection
    - Dialect: The SQL dialect to use when generating the SQL query.
      - Name: dialect
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: SqlDialect_
    - Business Facts: Enter the Business Facts.
      - Name: facts
      - Tooltip:
        
        Detail:
        
        Examples:
        
        A fiscal year begins on March 1st.
        
        Profit is calculated as x - y.
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: list[BusinessFact]
    - Number of Facts: Dictates the number of facts included in the context per SQL generation.
      - Name: num_facts
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: int
    - Column Definitions: Descriptions of each of the table columns.
      - Name: column_definitions
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: list[TableColumnDefinition]
- Artifacts: No artifacts are returned by this method

6. Get Business Facts (Method)

Method: get_business_facts
- Type: Method
- Memory Capacity: 2.0 GB
- Allow In-Memory Execution: Yes
- Read Only: Yes
- Method Limits: There are no limits for this method. This should complete in less than 20 seconds with 2GB of memory allocated.
- Outputs Dynamic Artifacts: No
- Short Description:
  - Get the results of the current business facts for the instance.
- Detailed Description:
  - This is a utility method to retrieve the current values of the business facts so end users may quickly see them.
- Inputs:
  - No input parameters
- Artifacts:
  - Business Facts: Contains the updated business facts.
    - Qualified Key Annotation: business_facts
    - Aggregate Artifact: False
    - In-Memory Json Accessible: True
    - File Annotations:
      - artifacts_/@business_facts/data_/data.json
        
        Stored json data.
      - artifacts_/@business_facts/data_/schema.json
        
        The json schema of the json object stored in the 'data.json' file

7. Get Column Definitions (Method)

Method: get_column_definitions
- Type: Method
- Memory Capacity: 2.0 GB
- Allow In-Memory Execution: Yes
- Read Only: Yes
- Method Limits: There are no limits for this method. This should complete in less than 20 seconds with 2GB of memory allocated.
- Outputs Dynamic Artifacts: No
- Short Description:
  - Get the results of the current column definitions for the instance.
- Detailed Description:
  - This is a utility method to retrieve the current values of the column definitions so end users may quickly see them.
- Inputs:
  - No input parameters
- Artifacts:
  - Column Definitions: Contains the existing column definitions.
    - Qualified Key Annotation: column_definitions
    - Aggregate Artifact: False
    - In-Memory Json Accessible: True
    - File Annotations:
      - artifacts_/@column_definitions/data_/data.json
        
        Stored json data.
      - artifacts_/@column_definitions/data_/schema.json
        
        The json schema of the json object stored in the 'data.json' file

8. NL To SQL (Method)

Method: nl_to_sql
- Type: Method
- Memory Capacity: 2.0 GB
- Allow In-Memory Execution: No
- Read Only: Yes
- Method Limits: This method generally takes anywhere from 5-45 seconds to complete. Memory usage is highly dependent on the source data as well as the input question being asked. In general, this method should only be called via the web dashboard artifact from the create_web_app method. Memory allocation for this method is dynamically determined via the web dashboard, so users should let that control its allocation. For a large dataset of 5.7M rows and 52 columns, an upper bound of 12GB of memory capacity is allocated for this method.
- Outputs Dynamic Artifacts: No
- Short Description:
  - Converts a natural language input to a SQL query using an LLM.
- Detailed Description:
  - Converts a natural language query into a SQL query using a specific SQL dialect. The natural language query will be parsed and transformed into a SQL query that is then executed on a tabular data source. The SQL query will be generated based on the tabular connection type and the SQL dialect specified in the parameters.
- Inputs:
  - Required Input
    - Input: The natural language query to execute on the data.
      - Name: user_input
      - Tooltip:
        
        Validation Constraints:
        
        The input must have a minimum length of 1.
        
        The input must have a maximum length of 500.
        
        This input may be subject to other validation constraints at runtime.
      - Type: str
    - Query ID: The unique identifier for the user query.
      - Name: query_id
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: int
    - Response Type: The type of response requested by the user.
      - Name: response_type
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: OperationsAnalystResponseType_
    - Conversation: The conversation history between the user and the system.
      - Name: conversation
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: list[ConversationParameterBaseModel]
    - LLM Chat Model Override: Optional chat model name override for query processing. If None, uses the routine default.
      - Name: llm_chat_model
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: Optional[str]
    - LLM Embedding Model Override: Optional embedding model name override. If None, uses the routine default.
      - Name: llm_embedding_model
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: Optional[str]
- Artifacts:
  - Generated Query: Contains the generated SQL query and additional metadata.
    - Qualified Key Annotation: response
    - Aggregate Artifact: False
    - In-Memory Json Accessible: True
    - File Annotations:
      - artifacts_/@response/data_/data.json
        
        Stored json data.
      - artifacts_/@response/data_/schema.json
        
        The json schema of the json object stored in the 'data.json' file

9. Perform Evaluation (Method)

Method: perform_evaluation
- Type: Method
- Memory Capacity: 2.0 GB
- Allow In-Memory Execution: No
- Read Only: Yes
- Method Limits: Method limits are dependent on the size of the source data connection utilized by the routine instance as well as the evaluation table input. Expect similar limits to the nl_to_sql method.
- Outputs Dynamic Artifacts: No
- Short Description:
  - Simulate questions being asked to the agent and compare accuracy of responses to expected responses.
- Detailed Description:
  - Provide a connection to a table where each row contains an ID and a question to ask the agent. This table may optionally include an expected response column that will have actual responses from the agent compared to them. Evaluation metrics will be calculated and returned through artifacts.
- Inputs:
  - Required Input
    - Simulation Data Connection: The connection to the source data.
      - Name: data_connection
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: TabularConnection
    - ID Column: The name of the ID column from the simulation data connection.
      - Name: id_column
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: str
    - Question Column: The name of the column that has the questions to ask to the agent.
      - Name: question_column
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: str
    - Expected Response Column: The name of the column that has the expected responses. If none is provided, then no accuracy will be measured.
      - Name: expected_response_column
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: str
    - Expected Query Column: The name of the column that has the expected query to be generated. If none is provided, then no accuracy will be measured.
      - Name: expected_query_column
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: str
    - Response Type Column: The name of the column that determines the response type to generate for the associated question.
      - Name: response_type_column
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: Optional[str]
    - Number of Iterations: The name of the column that has the expected responses. If none is provided, then no accuracy will be measured.
      - Name: n_iterations
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: int
- Artifacts:
  - Results Table: A table containing the responses from the agent as well as accuracy scores if requested.
    - Qualified Key Annotation: results_table
    - Aggregate Artifact: False
    - In-Memory Json Accessible: False
    - File Annotations:
      - artifacts_/@results_table/data_/data_<int>.parquet
        
        A partitioned set of parquet files where each file will have no more than 1000000 rows.
  - Overall Accuracy: A number that represents the overall accuracy of responses accounting for the SQL query and text response returned.
    - Qualified Key Annotation: total_accuracy
    - Aggregate Artifact: False
    - In-Memory Json Accessible: False
    - File Annotations:
      - artifacts_/@total_accuracy/data_/data_<int>.parquet
        
        A partitioned set of parquet files where each file will have no more than 1000000 rows.
  - Current Business Facts: The current business facts at the time of the method run.
    - Qualified Key Annotation: business_facts
    - Aggregate Artifact: True
    - In-Memory Json Accessible: False
    - File Annotations:
      - artifacts_/@business_facts/data_
        
        Folder containing inner artifacts
    - Nested Artifacts:
  - Business Facts: Contains the updated business facts.
    - Qualified Key Annotation: business_facts.business_facts
    - Aggregate Artifact: False
    - In-Memory Json Accessible: True
    - File Annotations:
      - artifacts_/@business_facts/data_/business_facts/data_/data.json
        
        Stored json data.
      - artifacts_/@business_facts/data_/business_facts/data_/schema.json
        
        The json schema of the json object stored in the 'data.json' file

10. Remove Business Facts (Method)

Method: remove_business_facts
- Type: Method
- Memory Capacity: 2.0 GB
- Allow In-Memory Execution: No
- Read Only: No
- Method Limits: There are no limits for this method. This should complete in less than 20 seconds with 2GB of memory allocated.
- Outputs Dynamic Artifacts: No
- Short Description:
  - Routine method to remove business facts that are selected.
- Detailed Description:
  - Remove any number of business facts from the routine instance that will no longer be utilized in subsequent requests. This action cannot be undone.
- Inputs:
  - Required Input
    - Facts to Remove: The list of facts to be removed from the currently included Business Facts.
      - Name: facts_to_remove
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: list[str]
- Artifacts:
  - Business Facts: Contains the updated business facts.
    - Qualified Key Annotation: business_facts
    - Aggregate Artifact: False
    - In-Memory Json Accessible: True
    - File Annotations:
      - artifacts_/@business_facts/data_/data.json
        
        Stored json data.
      - artifacts_/@business_facts/data_/schema.json
        
        The json schema of the json object stored in the 'data.json' file

11. Update Llm Model (Method)

Method: update_llm_model
- Type: Method
- Memory Capacity: 2.0 GB
- Allow In-Memory Execution: No
- Read Only: No
- Method Limits: No limits. Completes in under 5 seconds with 2GB memory.
- Outputs Dynamic Artifacts: No
- Short Description:
  - Update the LLM chat model persisted on the routine instance.
- Detailed Description:
  - Changes the model used for all subsequent nl_to_sql queries. The change is persisted as a MemberVar so it survives across sessions and affects all users of this instance.
- Inputs:
  - Required Input
    - LLM Chat Model: The new LLM chat model to use for all query processing, conversation naming, and error explanations. This change persists as a MemberVar on the routine instance.
      - Name: llm_chat_model
      - Tooltip:
        
        Validation Constraints:
        
        This input may be subject to other validation constraints at runtime.
      - Type: LLMChatModel_
- Artifacts:
  - LLM Model Update Result: Contains the previous and new LLM chat model names.
    - Qualified Key Annotation: result
    - Aggregate Artifact: False
    - In-Memory Json Accessible: True
    - File Annotations:
      - artifacts_/@result/data_/data.json
        
        Stored json data.
      - artifacts_/@result/data_/schema.json
        
        The json schema of the json object stored in the 'data.json' file

Interface Definitions

No interface definitions found for this routine

Developer Docs

Routine Typename: OperationalDataChat

Method Name	Artifact Keys
`__init__`	N/A
`add_business_facts`	business_facts
`configure_scope_permissions`	N/A
`create_web_app`	web_app
`data_processing`	N/A
`get_business_facts`	business_facts
`get_column_definitions`	column_definitions
`nl_to_sql`	response
`perform_evaluation`	results_table, total_accuracy, business_facts
`remove_business_facts`	business_facts
`update_llm_model`	result

Versions​

v3.0.0​

Basic Information​

Tags​

Description​

Short Description​

Long Description​

Use Cases​

1. Natural Language to SQL Query Generation​

Routine Methods​

1. Init (Constructor)​

2. Add Business Facts (Method)​

3. Configure Scope Permissions (Method)​

4. Create Web App (Method)​

5. Data Processing (Method)​

6. Get Business Facts (Method)​

7. Get Column Definitions (Method)​

8. NL To SQL (Method)​

9. Perform Evaluation (Method)​

10. Remove Business Facts (Method)​

11. Update Llm Model (Method)​

Interface Definitions​

Developer Docs​