GenAI is clearly a hugely transformative technology. In this example I show how I used a GenAI model (in this case OpenAI) to process questions from the user of the OAC workbook and return both numeric and text responses which can then be visualised.
There is a really useful guide here which explains the steps we need to take in order to set up the access to OpenAI (and other such as Cohere). We can also use the OCI Gen AI facility which requires less set up as we do not need to set the ACL.
I performed the following for a “markd” user in an Oracle Autonomous 23ai database. Following the guide referenced above this was enough to get Select AI working for a set of tables (which we discuss later). I already had an Open AI account.
Firstly I gave my user execute on the DBMS_CLOUD packages
grant execute on DBMS_CLOUD_AI to markd;
grant execute on DBMS_CLOUD to markd;
Grant access to openai API
Begin
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'markd',
principal_type => xs_acl.ptype_db)
);
End;
Create Credential
Next create a credential using my Open AI account username and password so that I can connect to the service.
Begin
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OPENAI_CRED',
username => 'my username',
password => 'my password' );
End;
Create Profile
I created a profile where I specify the three tables I want the Open AI to reference, so that when I invoke it the metadata for these tables is sent across. The data itself is not transmitted, just the metadata.
Begin
DBMS_CLOUD_AI.create_profile(
profile_name => 'OPEN_AI',
attributes => '{"provider": "openai",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner": "markd", "sales": "sales"},
{"owner": "markd", "sales_locations": "sales_locations"},
{"owner": "markd", "salespeople": "salespeople"}]
}');
End;
Description of my sample tables
I have created 3 simple tables. The sales people and locations are dimension tables and I have a sales “fact” table containing information about sales amounts and who made the sale and when. All have primary and foreign keys.
Autonomous Database 23ai Functions
Once I had the “plumbing” in place I created a couple of functions to try it out. The first function MarkAskQ accepts a parameter and calls the API defined in the profile passing the question as the prompt and expects a text response once the returned SQL is executed.
Database Function to call dbms_cloud_ai for TEXT response.
create or replace function
MarkAskQ(lvText in varchar2) return Varchar2
is
lvAnswer Varchar2(4000);
lvSQL varchar2(4000);
BEGIN
lvSQL := (
dbms_cloud_ai.generate(
prompt => lvText,
action => 'showsql',
profile_name => 'OPEN_AI'
)
);
Execute Immediate lvSQL into lvAnswer;
Return lvAnswer;
End MarkAskQ;
The MarkAskF4 test function is basically the same as the previous one, but expects a numeric response.
Database Function to call dbms_cloud_ai for numeric response.
create or replace function
MarkAskF4(lvText in varchar2) return number
is
lnNumber Number;
lvText2 varchar2(4000);
BEGIN
lvText2 := (
dbms_cloud_ai.generate(
prompt => lvText,
action => 'showsql',
profile_name => 'OPEN_AI'
)
);
Execute Immediate lvText2 into lnNumber;
Return lnNumber;
End MarkAskF4;
Oracle Analytics Setup
Firstly I create a parameter to accept my question and we can store that in order to process it and return a response.
I then create a second parameter. I use these so that one will be used to call the function that returns a numeric answer and one will be used to process questions that have a text answer.
I can now “fuse” the parameter and the function together and create a custom calculation to take the parameter value for the question with the expected numeric answer and pass that to the function MARKASKF4 ( the ‘Who Sold The Most’ is the default question if the parameter is null).
Create another calculation for the parameter for which we expect a text response, this time calling the function that gets a text response.
We can lay out the parameters on the workbook like this in order to allow them to be populated. I can now now ask a question that delivers a numeric response and one which delivers a text response.
Results
Text Question
Let’s ask a question in a natural language such as “What is the name of my best salesperson”. The GenAI interprets the question and returns us the SQL which we execute in the function to get the answer “Alice Butters”.
To visualise that we can simply include the custom calculation to a text box visualisation. See below where we have a table visualisation which simply just contans the “GenAI Answer Narrative” custom calculatation we created.
I then build a “checker” visualisation of sales people and amounts to see if that is the correct answer in the text response.
Numeric Question
Let’s ask a simple question such as “total sales”. The GenAI interprets the question and returns us the SQL which we execute in the function to get the answer 5062.
To visualise that we can simply drag the custom calculation to a Tile visualisation, just like I did with the previous one.
Again, I created another “checker” chart to check the result (and the sum of the values does match).
Summary
This was quite a simple exercise, but starts to help us to understand new ways that the users can interact and explore their data, by asking natural questions and seeing results as well as having the ability to create their own visualisations.