report_execution.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# POC for ExecutionDefinition generation.\n",
    "\n",
    "This notebook explores options for generating the ExecutionDefinition using Large Language Models (LLMs) from written text.\n",
    "- It currently supports OpenAI's GPT 4 and 3.5 APIs.\n",
    "\n",
    "Most of the actual code is 'hidden' in the 'gooddata/agents/report_agent.py' file, making this notebook as user-friendly as possible.\n",
    "\n",
    "This solution works out of the box with GoodData CE. However, with minor tweaks, it should also be compatible with GoodData.CN/cloud instances."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Before You Start\n",
    "\n",
    "1. Set credentials in `.env` file.\n",
    "    `GOODDATA_HOST`, `GOODDATA_TOKEN`, `OPENAI_API_KEY`, and optionally `OPENAI_ORGANIZATION`\n",
    "2. Ensure your GoodData (GD) docker is running.\n",
    "3. If you're not using the Community Edition, be aware that larger workspaces might not work 'out-of-the-box' due to the 8K token limitations."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Usage\n",
    "\n",
    "I opted for simplicity in this design, focusing solely on attributes and metrics. I've ensured both are mandatory and hardcoded aspects such as `filter=[]`. I've also enriched the dimensions with `measureGroup`.\n",
    "\n",
    "No visuals are included since this `.ipynb` file is intended for a technical audience. To pose questions, you must first create an instance of the Agent class, and provide it with the parameters:\n",
    "- openai_model - use any of the supported OpenAI models, but not all are efficient or may not support all AIMethods\n",
    "- workspace_id - GoodData workspace ID. A semantic model from this workspace is used and reports are executed in this context\n",
    "- temperature - when setting a value higher than 0 (default), answers may be less deterministic but also more inventive\n",
    "\n",
    "Then agent method `process` is called with the following parameters:\n",
    "- method\n",
    "  - RAW - Calls OpenAI API chatCompletions, without any specialties.\n",
    "  - FUNC - Calls OpenAI API chatCompletions, with a pre-defined .json structure, using function calling.\n",
    "  - LANGCHAIN - Uses Langchain to create an embedding from provided documents in the \"./data/\" folder, which is then used to enrich the prompt.\n",
    "- question - the natural question asking for the report execution\n",
    "\n",
    "To generate a plot, simply ask a question in the format that you would use to name your visualization (assuming the target audience had an IQ of 80 - so GPT can understand it), such as `Clothing, Electronics and Home revenue per region`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions to try\n",
    "\n",
    "When posing \"questions\", aim for precision in the desired information. For example:\n",
    "- Revenue per month\n",
    "- Revenue per customer per year\n",
    "- Number of orders and revenue per product name\n",
    "\n",
    "You can also experiment with less conventional queries:\n",
    "- Faulty descriptions:\n",
    "    - Money by people\n",
    "    - Monthly money by user\n",
    "    - Revenue by customer\n",
    "- Non-English descriptions:\n",
    "    - Měsíční peníze na zákazníka\n",
    "    - Výdělek jednotlivých kampaní\n",
    "    - Výdělek a útrata jednotlivých kampaní\n",
    "    - Prachy na kampaň\n",
    "    - Prachy pro každý rok\n",
    "    - 每月收入 (Monthly revenue - traditional Chinese)\n",
    "\n",
    "\n",
    "### Disclaimer\n",
    "\n",
    "Since the entire process is dependent on LLM, the results might not always be accurate. The examples provided are not 100% reliable and may occasionally result in exceptions. If you encounter any bugs or typos, please contact us. We would be more than happy to fix them!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generate using function calls\n",
    "\n",
    "Function calls are a killer feature that enable programmers to specify the desired outcome in a pre-defined .json structure.\n",
    "\n",
    "The structure we are using in this approach is as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "{\n",
    "    \"name\": \"ExecutionDefinition\",\n",
    "    \"description\": \"Create ExecutionDefinition for data visualization\",\n",
    "    \"parameters\":{\n",
    "        \"type\": \"object\",\n",
    "        \"properties\": {\n",
    "            \"attributes\": {\n",
    "                \"type\": \"array\",\n",
    "                \"items\": {\n",
    "                    \"type\": \"string\",\n",
    "                    \"description\": \"local_id of an attribute\"\n",
    "                },\n",
    "                \"description\": \"List of local_id of attributes to be used in the visualization\"\n",
    "            },\n",
    "            \"metrics\": {\n",
    "                \"type\": \"array\",\n",
    "                \"items\": {\n",
    "                    \"type\": \"string\",\n",
    "                    \"description\": \"local_id of a metric\"\n",
    "                },\n",
    "                \"description\": \"List of local_id of metrics to be used in the visualization\"\n",
    "            }\n",
    "        },\n",
    "        \"required\": [\"attributes\", \"metrics\"]\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To try it for yourself, simply run the following snippet:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": true
    }
   },
   "outputs": [],
   "source": [
    "from gooddata.agents.report_agent import ReportAgent\n",
    "from gooddata.agents.common import AIMethod \n",
    "\n",
    "workspace_id = \"demo\"\n",
    "question=\"Monthly revenue\"\n",
    "\n",
    "ag = ReportAgent(\n",
    "    workspace_id=workspace_id,\n",
    "    openai_model=\"gpt-3.5-turbo-0613\",\n",
    ")\n",
    "df, attributes, metrics = ag.process(AIMethod.FUNC, question)\n",
    "display(df)\n",
    "df.plot.bar()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One significant advantage of this approach is its remarkable speed (4-5s with GPT-4) and reliability. Unlike the \"raw\" GPT-4, it can be designed to always provide a .json format output.\n",
    "\n",
    "However, like many aspects of life, there are some drawbacks:\n",
    "\n",
    "- Currently, it is not possible to fine-tune the model, which supports function calls.\n",
    "    - The context must be provided either in the prompt or in the system message.\n",
    "    - This requirement makes it difficult to use.\n",
    "- Despite its benefits, it is still quite expensive (approximately $0.03 per request with GPT-4).\n",
    "- You may need to disclose potentially sensitive information to a third party."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generate through \"raw\" chatCompletions\n",
    "\n",
    "This method is somewhat more complex, as we can't always guarantee a .json format response.\n",
    "\n",
    "I've experimented with various approaches and discovered three key strategies to maximize success rates and produce the best outcomes:\n",
    "\n",
    "- Inject the context directly into the prompt, enclosing it as `context:\"\"\"_the actual context_\"\"\"`\n",
    "- Label your content extensively with a distinctive identifier. I chose `AHAHA_workspace` for the workspace.\n",
    "- Ensure the entire .json format is thoroughly documented. If not, there may be comprehension difficulties.\n",
    "\n",
    "Now, let's put theory into practice and try it out!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from gooddata.agents.report_agent import ReportAgent\n",
    "from gooddata.agents.common import AIMethod \n",
    "\n",
    "workspace_id = \"demo\"\n",
    "question=\"Příjmy z prodeje oděvů, elektroniky a domácnosti podle států\"\n",
    "\n",
    "ag = ReportAgent(\n",
    "    workspace_id=workspace_id,\n",
    "    openai_model=\"gpt-3.5-turbo-0613\",\n",
    ")\n",
    "df, attributes, metrics = ag.process(AIMethod.RAW, question)\n",
    "display(df)\n",
    "df.plot.bar()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pros:\n",
    "- Even faster than function calls\n",
    "- Prompts can be easily enriched with vector DB, which makes this scale significantly better than function calls.\n",
    "\n",
    "Cons:\n",
    "- Still quite expensive (especially with GPT-4)\n",
    "- Less reliable (approximately 90% reliability?)\n",
    "- You may still need to provide potentially sensitive information to a third party.\n",
    "\n",
    "In conclusion, the raw chatCompletions method is comparable in quality to function calls, though it is somewhat less reliable.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generating with LangChain\n",
    "\n",
    "This method may be somewhat controversial. While LangChain has its merits, for a task as simple as this, it is the least efficient of the three options.\n",
    "\n",
    "The primary issue is the time it takes to execute. This is likely due to my decision not to persist the vector database, resulting in each snippet creating a new one from scratch.\n",
    "\n",
    "A second issue is that generation will always be slower, even with persistence. Since there's no way to force the agent to output only the .json format, the answers are longer, as they contain a lengthy explanation.\n",
    "\n",
    "However, occasionally it can return just the .json format, nearly matching the speed of the other methods when it does so.\n",
    "\n",
    "This approach would work best when combined with function calls. However, this combination isn't supported 'out-of-the-box' and is well beyond the scope of this proof of concept.\n",
    "\n",
    "The current workflow involves the entire \"intelligence\" residing in the `./data/` folder. LangChain then ingests this folder, enriches a very basic prompt, and sends it to the OpenAI chatCompletions API.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": true
    }
   },
   "outputs": [],
   "source": [
    "from gooddata.agents.report_agent import ReportAgent\n",
    "from gooddata.agents.common import AIMethod \n",
    "\n",
    "workspace_id = \"demo\"\n",
    "question=\"Ukaž mi příjmy v každém měsíci v roce\"\n",
    "\n",
    "ag = ReportAgent(\n",
    "    workspace_id=workspace_id,\n",
    "    openai_model=\"gpt-3.5-turbo-0613\",\n",
    ")\n",
    "df, attributes, metrics = ag.process(AIMethod.LANGCHAIN, question)\n",
    "display(df)\n",
    "df.plot.bar()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One significant observation from using LangChain is that most prompt engineering best practices fail in this context.\n",
    "\n",
    "With additional effort, it might be possible to enhance its performance and smoothness.\n",
    "\n",
    "Pros:\n",
    "- More affordable\n",
    "- Scales better\n",
    "\n",
    "Cons:\n",
    "- Slower\n",
    "- Even less predictable, than \"raw\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion\n",
    "\n",
    "The most shocking conclusion from this proof of concept (POC) is that the quality of the prompting is as crucial, if not more so, as the tooling that surrounds it.\n",
    "\n",
    "In fact, 'agent.py' contains as much prompt text as it does code. This can be attributed to the relative maturity of the OpenAI tooling, despite the recent inception of this technological boom. It also underscores the fact that when providing prompts, specificity is often required.\n",
    "\n",
    "However, the quality of the responses in this case was, at best, questionable. There is significant room for improvement, and if we were to launch this in its current form, it would likely be met with laughter.\n",
    "\n",
    "It's also important to note that the data this POC relies on is simplistic, and this simplicity is further magnified by the reduced number of options from which GPT can choose.\n",
    "\n",
    "In conclusion, while LLMs are far from perfect, they can still be useful for tasks of moderate complexity. Personally, I believe it's more important to acknowledge and highlight these flaws rather than indulge in marketing BS, as many sadly do, which could only backfire in the long run."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}