[Tutorial] Setup Your in House AI Slack Bot for Data Analytics in Natural Language
Tags: chatgpt, data, analytics, tutorial
Published: 2023-04-27 13:40:20

Learn more about our open source project on [Github](https://github.com/logunify/dsensei/)

In today's data-driven world, organizations are constantly looking for ways to make their data analysis process more efficient and streamlined.  The recent surge in Large Language Model technologies has revolutionized the field of analytics by enabling natural language interactions, which gave access to data analytics to everyone. DSensei is an open-source Slack bot that connects this technology with people by enabling 
them to request data analytics in plain English directly within their familiar instant messaging tool.

In this post, we'll walk through the steps to setup Dsensei in Slack connecting to BigQuery as its data source.

## Prerequisites

* A Slack account with admin access to the workspace to install Dsensei
* An OpenAI API key (you can generate from this [link](https://platform.openai.com/account/api-keys))
* A Google Cloud account with `roles/iam.serviceAccountCreator` IAM role to create a service account for BigQuery (see this [doc](https://cloud.google.com/iam/docs/service-accounts-create#permissions) for detail)

## Setup
Now let's start the setup process.

### Step 1. Create an Setup Slack App
First let's create a new Slack App and install it to the workspace:
* Sign in to your Slack account in browser and navigate to [https://api.slack.com/apps](https://api.slack.com/apps) 
* Click on the "Create New App" button to create a new app, and select "From an app manifest".
* Select the workspace you want to install the app into.
* Inside the "Enter app manifest below" dialog select the format to YAML and paste the following manifest:
 ```yaml
 display_information:
  name: sensei
features:
  app_home:
    home_tab_enabled: false
    messages_tab_enabled: true
    messages_tab_read_only_enabled: false
  bot_user:
    display_name: sensei
    always_online: true
  slash_commands:
    - command: /info
      description: Get information about DB
      usage_hint: /info [dbs] | [tables db] | [schema db.table]
      should_escape: false
oauth_config:
  scopes:
    bot:
      - app_mentions:read
      - chat:write
      - commands
      - im:history
      - files:write
      - files:read
settings:
  event_subscriptions:
    bot_events:
      - app_mention
      - message.im
  interactivity:
    is_enabled: true
  org_deploy_enabled: false
  socket_mode_enabled: true
  token_rotation_enabled: false
  ```
 

### Step 2. Setup BigQuery Credentials
Then let's create a Google Cloud Service Account Dsensei to access Bigquery:
* Sign in to your Google Cloud console in browser and navigate to [https://console.cloud.google.com/iam-admin/serviceaccounts](https://console.cloud.google.com/iam-admin/serviceaccounts).
* Select the project you want Dsensei to have access to and you should get into the service account management page.
* Click "+Create service account".
* Input "Dsensei" to the "Service account name" and "dsensei" to the "Service account ID", optionally you can also put some description. Click "CREATE AND CONTINUE".
![screenshot](https://www.dsensei.dev/blog/setup-in-house-slack-bot-for-data-analytics//gcp-service-account-step1.png)
* Grant the "BigQuery Data Viewer" and "BigQuery Job User" role to the service account and click "Done".
![screenshot](https://www.dsensei.dev/blog/setup-in-house-slack-bot-for-data-analytics//gcp-service-account-step2.png)
* Locate the newly create service account in the list, toggle the "Action" menu and click the "Manage keys" button.
![screenshot](https://www.dsensei.dev/blog/setup-in-house-slack-bot-for-data-analytics//gcp-service-account-locate.png)
* In the "Keys" tab click "ADD KEY" -> "Create new key" to open the key creation dialog.
* Inside the key creation dialog, select "JSON" key type and click "CREATE".
* It should download the json file, save the json file into the proper path and we will use it later.

### Step 3. Setup DSensei Service
Now with the Slack App and BigQuery access setup, let's setup the Dsensei service:
* Checkout the Dsensei repo: `git clone https://github.com/logunify/dsensei.git`
* Switch to Node 18. You can do that with `nvm use 18`, details about nvm installation and usage can be found in this [doc](https://github.com/nvm-sh/nvm#installing-and-updating).
* Inside the checked out dsensei folder, run `npm install` to install all the dependencies.
* Config credentials, we provide a template on the config, you can use it by renaming `.env.example` to `.env`:
  * Config slack credentials:
    * Goto [https://api.slack.com/apps](https://api.slack.com/apps) and select the app you just created.
    * On the sidebar, select "OAuth & Permissions", and find the oauth token under "Oauth Tokens for Your Workspace" section. It should start with `xoxb-`. Copy the token put it under `SLACK_BOT_TOKEN` in the `.env` file.

    ![screenshot](https://www.dsensei.dev/blog/setup-in-house-slack-bot-for-data-analytics//slack-bot-token-screenshot.png)
    * Then select the "Basic Information" on sidebar, and find the "Signing Secret" under the "App Credentials" section. Copy the secret and put it under `SLACK_SIGNING_SECRET` in the `.env` file.

    ![screenshot](https://www.dsensei.dev/blog/setup-in-house-slack-bot-for-data-analytics//signing-secret-screenshot.png)
    * Finally locate "App-Level Tokens" section under the "Basic Information" tab and click the "Generate Token and Scopes" button to generate a app token. Add the `connections:write` scope in the dialog and click "Generate" to generate the token. The Slack App Token should start with `xapp-`. Copy the token and put it under `SLACK_APP_TOKEN` in the `.env` file.
  
    ![screenshot](https://www.dsensei.dev/blog/setup-in-house-slack-bot-for-data-analytics//app-token-screenshot.png)

  * Config OpenAI API key:
    * Find your OpenAI API key in this [page](https://platform.openai.com/account/api-keys), copy the token and put it under `OPENAI_API_KEY` in the `.env` file.
  * Config BigQuery key:
    * In the `.env` file, set `BQ_KEY` to the path to the key file we generated above for the service account, like `/Users/foo/gcp/dsensei.json`
* [Optional] Whitelist dataset and tables.
  * You might want to limit the dataset / tables this tool can access, you can do so by list the dataset in a comma separated string in the `DATABASES` field, and / or comma separated `dataset.tablename` list in the `TABLES` field.

## Start DSensei and Verify in Slack!
That's it, now we should have everything setup, let's start Dsensei and verify it in Slack!
* [Optional] For the demo purpose, we copy a public sample [ecommerce dataset](https://console.cloud.google.com/marketplace/product/bigquery-public-data/thelook-ecommerce) into our BigQuery project and name it `ecommerce`:
  * Goto this [link](https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=thelook_ecommerce&page=dataset) and click "COPY" 
  * Select "CREATE NEW DATASET"
  * Change the Project ID to the project we generate the service account in Step 2
  * Click "CREATE DATASET"
* Run `npm run prod` to start Dsensei service. You should see Dsensei initialize and loading schema in the logs. In our example, in looks like below:
```text
2023-04-26T23:10:00.526Z [SlackApp] info: 
    ____    _____                                _  
   / __ \  / ___/  ___    ____    _____  ___    (_)
  / / / /  \__ \  / _ \  / __ \  / ___/ / _ \  / /
 / /_/ /  ___/ / /  __/ / / / / (__  ) /  __/ / /
/_____/  /____/  \___/ /_/ /_/ /____/  \___/ /_/

2023-04-26T23:10:00.529Z [DataSourceLoader] info: Use data source from BigQuery
[INFO]  socket-mode:SocketModeClient:0 Going to establish a new connection to Slack ...
2023-04-26T23:10:00.782Z [SlackApp] info: Sensei is up running, listening on port 3000
2023-04-26T23:10:00.986Z [BigQuery] info: Loaded databases: ecommerce
[INFO]  socket-mode:SocketModeClient:0 Now connected to Slack
2023-04-26T23:10:01.414Z [BigQuery] info: Loaded table: ecommerce.distribution_centers
2023-04-26T23:10:01.448Z [BigQuery] info: Loaded table: ecommerce.users
2023-04-26T23:10:01.452Z [BigQuery] info: Loaded table: ecommerce.events
2023-04-26T23:10:01.454Z [BigQuery] info: Loaded table: ecommerce.products
2023-04-26T23:10:01.456Z [BigQuery] info: Loaded table: ecommerce.inventory_items
2023-04-26T23:10:01.504Z [BigQuery] info: Loaded table: ecommerce.orders
2023-04-26T23:10:01.529Z [BigQuery] info: Loaded table: ecommerce.order_items
2023-04-26T23:10:01.530Z [BigQuery] info: All 1 databases are loaded.
2023-04-26T23:10:04.334Z [BigQuery] info: Enrichment finished.
```
* Now is the fun part, let's play around with Dsensei in Slack:
  * Create a new channel and add `sensei` to the channel (you can also locate the "sensei" under the "Apps" section on the left and ping it directly):
  ![screenshot](https://www.dsensei.dev/blog/setup-in-house-slack-bot-for-data-analytics/dsensei-slack-channel.png)
  * We start with commands to verify that schemas have been loaded. Send `/info dbs` to get all the database and here's the response:
    ```text
    Your databases: 
    --------------------
    ecommerce
    ```
  * Send `/info tables ecommerce` to get all the tables under `ecommerce` dataset and here's the response:
    ```text
    Your tables in ecommerce: 
    --------------------
    distribution_centers
    events
    inventory_items
    order_items
    orders
    products
    users
    ```
  * Send `/info schema ecommerce products` to get the detailed schema of the `products` table and here's the response:
    ```text
    Table ecommerce.products schema:
    name                    type     description
    --------------------------------------------
                        id  INTEGER             
                      cost    FLOAT             
                  category   STRING             
                      name   STRING             
                    brand   STRING             
              retail_price    FLOAT             
                department   STRING             
                      sku   STRING             
    distribution_center_id  INTEGER             
    ```
  * After verify the schemas are loaded, we will test with some data questions. Say we want to know the number of orders this month, we can simply just send "`@sensei how many new orders do I have this month?`" and Dsensei will respond in a thread:
  
    ![screenshot](https://www.dsensei.dev/blog/setup-in-house-slack-bot-for-data-analytics//response-in-thread.png)
  * We can follow up with Dsensei within the thread, for instance with the number of orders we have this month, we want to know how does it compared with last month. Dsensei will memorize context of all conversation with the thread so we can directly ask the follow up question by sending "`@sensei how does it compare with last month?`" in the thread, and we get:

    ![screenshot](https://www.dsensei.dev/blog/setup-in-house-slack-bot-for-data-analytics//follow-up-in-thread.png)

    pretty cool huh?
  * It can answer more advanced question that needs to conduct more sql query. For instance, we want to know the brands that process their more effectively and limit to only brands with more than 100 orders to reduce the bias, which entails the question as: "`@sensei for those brands having more than 100 orders, give me the top 5 brands that fastest at shipping their orders?`". 
  
    ![screenshot](https://www.dsensei.dev/blog/setup-in-house-slack-bot-for-data-analytics//complex-query.png)
* Now we have everything setup, enjoy your analytics with DSensei!
  
## Closing Thought
In conclusion, leveraging the power of chatbot to access and analyze data can greatly improve the efficiency and effectiveness of data analysis processes in organizations. DSensei is a great open-source Slack bot that can help you achieve this goal by allowing you to access and analyze data through natural language commands. By following the steps outlined in this post, you can easily set up DSensei and link it to your BigQuery data source to start benefiting from its features.

We’re passionately developing this project and would love for you to be a part of our community on [Discord](https://github.com/logunify/dsensei) where you can receive the latest news, report bugs, and make feature requests. Please also feel free to submit any feedback on github directly.

Try a live demo of DSensei in our [Slack Channel](https://join.slack.com/t/dsensei-demo/shared_invite/zt-1tt55ia3p-jMlofj8YHGnCMjuu30vJeg)

Learn more about our open source project on [Github](https://github.com/logunify/dsensei/)