Data Warehouse Queries

The digital assistant may be used to ask natural language questions directly to the Retail Analytics and Planning (RAP) data warehouse and get a data-driven response back in near real time. When accessing the chat dialog, you are prompted to select an application context, and to use this feature you must select “AIF DATA” as that context. After the chat dialog confirms your selection, you may begin asking questions that require data from the data warehouse and it will attempt to write and execute queries to retrieve that data.

The digital assistant has different behavior depending on your Oracle Analytics version. When you have Oracle Analytics Cloud (OAC) as part of your environment, the assistant uses the metrics and attributes from the Retail Insights (RI) application to power the data warehouse queries. All RAP customers are granted access to relevant parts of the RI catalog as part of their subscription, but it is up to you if you want to implement RI (thereby enabling this feature implicitly). The digital assistant is able to make full use of RI’s data warehouse aggregate tables and pre-calculated measures as long as they are populated with your business data. Even if you do not load your data fully into RI’s tables, you may still be able to ask questions at a granular level of detail such as sku/store/week, as this level is commonly used across all of the applications.

When your environment is using Oracle Analytics Server (OAS), the digital assistant only has access to select database views which are transformed into an optimal layout for AI-assisted queries. The current list of views are:

Sales transaction history (W_RTL_SLS_TRX_IT_LC_DY_F_VW)
Current inventory positions (W_RTL_INV_IT_LC_G_VW)
Current price/cost values (W_RTL_PRICE_IT_LC_G_VW)

You may explore these views from Innovation Workbench to better understand all of the available fields that can be queried. They include all of the fact measures which are typically populated in the data warehouse tables for these functional areas, along with hierarchy ID and label columns to enable questions across a variety of different data levels.

Example questions you may ask to retrieve data from the digital assistant include:

How many stock on hand units do I have for item 98210004 at location 1001?
What is the average price and cost of items having the description “Graphic V-Neck T-Shirt” in the California district?
What were my total sales units on 5/6/2023 across all stores?

When asking multiple questions within a single conversation, the responses to all prior questions are retained as context and may be used implicitly. This can alter the responses provided by the assistant in unexpected ways if you are not aware of the behavior. For example, if your initial question is regarding a specific item and location (what is the unit cost and unit retail for item 30020 at location 99001?) and your next question leaves out some of that information (do I have any sales units for item 30020 on 5/6/2023?), it can cause the location used initially to be added to every query from that point on unless you override the prompt with new information. You can override a past input in different ways, such as by providing a new location number for the next question or by specifying aggregation keywords such as “total” to indicate you want the result over all elements instead of just one. If you are planning to ask a series of questions regarding the same set of identifiers, this can be a useful way to simplify the conversation. However, if you intend to ask a series of unrelated questions where the context of the prior question is not important, you can always close the chat window at any time and re-open it to start a new conversation.

If you have any doubts about the accuracy of a response, or you wish to analyze the data further outside of the chat dialog, use the “Show SQL” option to obtain the query used. For OAS-based queries, it will provide a SQL statement that can be run in Innovation Workbench. For OAC-based queries, it will provide an Oracle Analytics logical query that cannot be directly executed but can be reviewed by the user to ensure the correct metrics and attributes were used.

The assistant also has an Explain feature that can help contextualize the response to your questions using general business knowledge that the AI model was trained on. The answers given by the Explain feature are not aware of your entire retail dataset, they rely on generative AI models that take your questions and responses as input and produce a possible explanation for that data within a retail business context. For example, if you ask for the price and cost of an item and then ask the assistant to explain the result, it may calculate the profit margin for the item and explain why it is useful to know that information. It may also suggest similar areas of data analysis for you to explore, such as optimizing your costs to improve the margin for the item.