AICOMMAND command

Concept Information

Using AI with data analysis

Supports natural-language data analysis by integrating Analytics with an external AI service.

Note

To use AICOMMAND, you must install and configure a compatible version of Python on the computer where the command will run. You must also install two Python libraries: Pandas and OpenAI Python API. For more information, see Configuring Python for use with Analytics.

The external AI service is not provided by Diligent. It must be provided by your organization.

Only connect to an external AI service that has been approved for use by your organization. Make sure to follow all security protocols if you intend to transmit sensitive data from Analytics to the AI service.

AI results can contain inaccuracies or errors. Use an alternative method to verify results if absolute accuracy is required.

Syntax

AICOMMAND {<FIELDS> field <...n>|<FIELDS> ALL} <SCRIPT "path_and_filename"> TO table_name USER_PROMPT "user_prompt_text" <PASSWORD num> MODEL "model_information" URL "ai_service_api_url" <BATCHSIZE number_of_records> <IF test> <WHILE test> <FIRST range|NEXT range> <OPEN>

Parameters

Name Description

FIELDS field_name <...n> | FIELDS ALL

The fields or expressions from the source Analytics table to send to the AI service.

You must specify at least one field or expression.

  • FIELDS field_name include the specified field, fields, or expressions

    Separate field names with spaces. Fields are exported in the order that you list them.

    To export a related field, specify a fully qualified field name (table_name.field_name). For example: Vendor.Vendor_Name

  • FIELDS ALL include all fields in the table

    Fields are exported in the order that they appear in the table layout. Related fields are not exported.

Note

Limit the total amount of data sent to the AI service by specifying only those fields that are strictly necessary for your analysis goal.

Include a unique identifier field if you want to join or relate the table returned from the AI service with the original source table. For more information, see Create a unique identifier field.

SCRIPT "path_and_filename"

optional

If you use your own Python script with AICOMMAND, the file path and name of the script.

If you specify a script, PASSWORD, MODEL, and URL are optional. For more information, see Using your own Python script.
TO table_name

The location to send the results of the command to:

  • table_name saves the results to an Analytics table

    Specify table_name as a quoted string with a .FIL file extension. For example: TO "Output.FIL"

    By default, the table data file (.FIL) is saved to the folder containing the Analytics project.

    Use either an absolute or relative file path to save the data file to a different, existing folder:

    • TO "C:\Output.FIL"
    • TO "Results\Output.FIL"

    Note

    Table names are limited to 64 alphanumeric characters, not including the .FIL extension. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.
USER_PROMPT "user_prompt_text"

The text of the prompt to input to the AI service.

The prompt tells the AI service how to process the input data that you send and what output you want.

Note

You must enclose user_prompt_text with double quotation marks ( " ). However, do not include any double quotation marks in the body of user_prompt_text. Double quotation marks in the body of the prompt text cause the command to fail. You can use single quotation marks ( ' ) in the prompt text.

PASSWORD num

optional

The password definition to use.

You do not use PASSWORD num to prompt for, or specify, an actual password. The password definition refers to a password previously supplied or set using the PASSWORD command, the SET PASSWORD command, or the PASSWORD analytic tag.

num is the number of the password definition. For example, if two passwords have been previously supplied or set in a script, or when scheduling an analytic script, PASSWORD 2 specifies that password #2 is used.

For more information about supplying or setting passwords, see:

Note

AICOMMAND uses the AI API key saved in the local registry, assuming a key has been saved. If no key is saved, or if you want to use a different key, you must use PASSWORD. For more information, see Connection settings for an external AI service.

MODEL "model_information"

Identifying information for the specific AI model that you want to use.

For example:

MODEL "anthropic.claude-3-5-sonnet-20241022-v2:0"

An AI service may offer different models that you can use.

You can use any OpenAI-compatible large language model (LLM) – that is, an OpenAI model, or a model that uses the same API schema as OpenAI's models.

URL "ai_service_api_url"

The full URL for the AI service API.

For example:

URL "https://api.anthropic.com/v1"
BATCHSIZE number_of_records

optional

The number of records to include in each batch sent to the AI service.

If AICOMMAND fails, try specifying a batch size of 50 or 25.

If you do not specify BATCHSIZE, the default batch size of 100 records is used.

An AI service typically limits the amount of data it will process with each operation. Records in an Analytics table are sent to an AI service in batches, to improve the likelihood of remaining within the data limits. The AI responses for all batches are assembled in the single Analytics output table.
IF test

optional

A condition that must be met to process the current record. The data sent to the AI service contains only those records that meet the condition.

At least one record in the table must be included by the IF condition. If the condition excludes all records in the table, an error results and the command does not run.
WHILE test

optional

A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached.

Note

If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached.
FIRST range | NEXT range

optional

The number of records to process:

  • FIRST start processing from the first record until the specified number of records is reached
  • NEXT start processing from the currently selected record until the specified number of records is reached

Use range to specify the number of records to process.

If you omit FIRST and NEXT, all records are processed by default.

OPEN

optional

Opens the table created by the command after the command executes. Only valid if the command creates an output table.

Examples

Vendor assessment using AI

You want to perform a basic check of a vendor table to surface any possible fictitious vendors. You input the vendor name and address fields to the AI service and ask it to categorize the vendors as Common, Rare, or Unknown.

Any vendors categorized as Unknown require additional investigation to determine if they actually exist. 

OPEN Vendors
AICOMMAND FIELDS vendor_name address TO "ai_vendor_classification" USER_PROMPT "Is this a commonly known vendor? The output value should be COMMON if common, RARE if known but uncommon, UNKNOWN otherwise." MODEL "anthropic.claude-3-5-sonnet-20241022-v2:0" URL "https://hackfest-bedrock-proxy.diligentoneplatform-dev.com/api/v1"

Expense review using AI

You want to review P-card transactions and flag any expenses where a discrepancy exists between the item or service and the purchase price. You input the expense description and the cost fields to the AI service and ask it to assess whether the expense is reasonable.

OPEN PcardTransactions
AICOMMAND FIELDS Description UnitCost TO "ai_expense_cost_review" USER_PROMPT "Given the combination of Description and Unit Cost, indicate whether the cost is typical or unusual." MODEL "anthropic.claude-3-5-sonnet-20241022-v2:0" URL "https://hackfest-bedrock-proxy.diligentoneplatform-dev.com/api/v1"

Sentiment analysis of hotel reviews using AI

You want to perform a sentiment analysis and categorization of the free-text comments field in a table containing customer hotel reviews. You ask the AI service to categorize the comments by the six basic human emotions.

OPEN hotel_reviews
AICOMMAND FIELDS reviews_rating reviews_text TO "ai_hotel_reviews_sentiment_analysis" USER_PROMPT "Perform a sentiment analysis on the reviews_text field and categorize the reviews by the following basic human emotions: Happiness, Anger, Fear, Sadness, Disgust, Surprise." MODEL "anthropic.claude-3-5-sonnet-20241022-v2:0" URL "https://hackfest-bedrock-proxy.diligentoneplatform-dev.com/api/v1" BATCHSIZE 50 OPEN

Remarks

How integration between Analytics and an external AI service works

Selecting fields, creating a user prompt, and running AICOMMAND is the starting point for the round-trip integration between Analytics and an external AI service:

  1. Analytics AICOMMAND extracts the selected field or fields from an Analytics table and saves them to a CSV file.

  2. Analytics AICOMMAND makes the user prompt, and the connection information for the AI service API, available to the default Python script, or to a Python script that you create.

    Location of the default Python script:

    C:\Program Files (x86)\ACL Software\ACL for Windows <version>\acl_py_util\ai_main.py

    Caution

    Do not move the default Python script, or alter it in any way. Default operation of AICOMMAND depends on the default Python script being in the correct location and remaining unchanged.

  3. Python The Python script imports the CSV file and converts it to a Pandas dataframe.

  4. Python The Python script sends the dataframe and the user prompt to the AI service API. To remain within API data limits, the script sends the dataframe in batches.

  5. AI service The AI service processes the dataframe based on the instructions in the user prompt.

  6. AI service The AI service returns the dataframe to the Python script with the addition of the ai_response column, which contains the results of the AI processing.

  7. Python The Python script converts the returned dataframe to an Analytics table and exports it to Analytics using the output table name specified in AICOMMAND.

  8. Analytics You can open the output table in Analytics to view the results of the AI processing, or use the results as the basis for additional data analysis.

Using your own Python script

You can use your own Python script with AICOMMAND. Create the script with the logic that you want for interacting with the AI service API. In AICOMMAND, use the SCRIPT parameter to specify the path and file name of the script.

One possible approach is to copy the default Python script used by AICOMMAND as a starting point for your own script. The default script is located in the Analytics installation folder. If you installed Analytics in the default location, the script is here:

C:\Program Files (x86)\ACL Software\ACL for Windows <version>\acl_py_util\ai_main.py

Caution

Do not move the default Python script, or alter it in any way. Default operation of AICOMMAND depends on the default Python script being in the correct location and remaining unchanged.

The Python script used by AICOMMAND must contain the connection settings for the external AI Service, whether it is the default script or your own script. In your own script, you have two options:

  • Use the connection settings from Analytics Use the host, model, and AI API key values specified in the AICOMMAND parameters.

    To use the AICOMMAND values in your Python script, define three variables or copy the variable definitions from the default Python script.

    an_api_key = os.getenv("ACL_PY_API_KEY")
ai_base_url = os.getenv("ACL_PY_AI_BASE_URL")
ai_model = os.getenv("ACL_PY_AI_MODEL")

  • Use your own method Use your own method for providing the host, model, and AI API key values. If you use your own method, you do not need to specify the connection values in AICOMMAND parameters.

Create a unique identifier field

If you want to join the table returned from the AI service with the original source table, but lack a unique identifier field, you can use the RECNO( ) function to create one before you use AICOMMAND.

  1. In the source table, create a computed field named record_number that uses the following expression:

    RECNO( )

    For each record in the table, the RECNO( ) function adds the unique record number in the record_number field.

    For more information, see DEFINE FIELD . . . COMPUTED command.

  2. Use AICOMMAND to process data using an external AI service.

  3. Use the record_number field as the common key field to join the table returned from the AI service with the source table.

    For more information, see JOIN command.

Separate multiple components in the ai_response field

In the output table returned by AICOMMAND, values in the ai_response field may contain multiple components or elements. For example, the single value shown below contains four components: sentiment, sentiment_score, key_reasons, and themes. The themes component contains three individual values.

sentiment: Positive, sentiment_score: 0.6, key_reasons: clean rooms, friendly staff, breakfast issues, themes: cleanliness, staff, food

To support subsequent analysis, you can isolate the components and values in separate computed fields that you build using the computed expressions shown below.

For general information about how to build computed fields, see Defining computed fields.

Computed field name Computed expression Resulting value
sentiment 
REGEXREPLACE(ai_response, "sentiment\:\s(.*?),.*", "$1")
 Positive
sentiment_score 
VALUE(REGEXREPLACE(ai_response, ".*sentiment_score\:\s(.*?),.*", "$1"), 1)
 0.6
key_reasons 
REGEXREPLACE(ai_response, ".*key_reasons\:\s(.*?),\sthemes.*", "$1")
 clean rooms, friendly staff, breakfast issues
themes 
REGEXREPLACE(ai_response, ".*themes\:\s(.*?)", "$1")
 cleanliness, staff, food
theme_1 
SPLIT(REGEXREPLACE(ai_response, ".*themes\:\s(.*?)", "$1"), ",", 1)
 cleanliness
theme_2 
SPLIT(REGEXREPLACE(ai_response, ".*themes\:\s(.*?)", "$1"), ",", 2)
 staff
theme_3 
SPLIT(REGEXREPLACE(ai_response, ".*themes\:\s(.*?)", "$1"), ",", 3)
 food