Usecase 2, Integrating Excel data sources and utilizing the data for visualisations.

Purpose	The objective of this usecase is to show clients how to integrate data from an excel file into the Playbooks, store it and visulise it via tables and or insight panes.

Created	September 10, 2024

---

Components Used	Description
Raven Playbooks	Playbooks allow you to create automated workflows by dragging and dropping Bricks into a canvas and connecting them together. Meaning they automate actions based on events.
Raven Flows	Raven Flows is an app that helps users to perform Extract, Transform, and Load (ETL) operations on events.
Raven Tables	They enable you to structure the data in a tabular format, facilitating easy manipulation and retrieval.
Raven Query	A Query provides a means to index Tables and Brick data using PostgresSQL and PRQL languages. This querying process allows you to extract specific data subsets as needed.
Raven Insight Panes	provide a user-friendly way of visualizing the Tables you have created. Rather than displaying data in the traditional rows and columns format, Panes offer an attractive visual overview.
Send to Topic Brick	Acts as a bridge between two applications in the Raven Portal: the Playbook App and the Flows App. This Brick facilitates the seamless transmission of information from a playbook to a flow.
Webhook Brick	Webhooks allow you to trigger playbooks using HTTP. By using a webhook, you can send JSON data to a playbook.

---

Part 1

Flow setup

Create a Flow

Navigate to the Flow App, where you can view all existing flows and have the option to create a new one.

Add and configure endpoint Brick

Click the "Add Brick" button in the top left corner, use the search bar to find the HTTP-endpoint, and add it to the flow.

After saving the flow, additional information about the brick will be visible when you select it in the pane on the left side of the flow.

To view all stored data and data policy details, double-click the brick to open it.

Part 2

Playbook setup

Create a Playbook

Go to the Raven Portal and select the Playbook app. Here, you’ll see an overview of existing playbooks and have the option to create a new one. To start a new playbook, click the NEW PLAYBOOK button in the top right corner.

Name and Describe

When creating a new playbook, you will need to provide a name and, optionally, a description. Adding a description is recommended as it helps other users understand the purpose and details of the playbook.

Add and Configure Bricks

Webhook Brick: This brick allows you to integrate files into the playbook by using a webhook URL along with some Python code.

To send information to a webhook, you'll need to use a third-party application capable of making HTTP requests, such as Jupyter Notebook.

Code example

import requests 
import pandas as pd 
import json 

url = 'https://webhook.apps.raven.dtact.com/WjFMYoerWkdnbbu5xS06x' 

# You can find the URL in the Brick Inspector. Click on the Webhook Brick, and a pane will open on the left side, displaying the Brick Inspector.

df = pd.read_excel("/Users/carloscaffarogarcia/Desktop/country_information.xlsx", header = 1) 
r = requests.post(url, data = json.dumps(df.to_dict('records'))) 

print(r) 

Code explanation:

A. Importing Required Libraries:

requests: A library used for making HTTP requests in Python. It allows you to send HTTP requests like GET, POST, PUT, DELETE, etc.

pandas (pd): A powerful data manipulation library in Python, especially useful for working with structured data (like data in Excel or CSV files).

json: A module in Python to work with JSON (JavaScript Object Notation) data. JSON is a popular data format used for exchanging information between a client and a server.

B. Setting the URL:

This line defines the URL where the data will be sent. The URL seems to be an endpoint for a webhook, which is a way for one application to send real-time data to another application.

C. Converting the Data to JSON Format:

pd.read_excel(): Reads the data from an Excel file located at the specified path on the user's desktop.

df: A DataFrame object that holds the data read from the Excel file. This object is like a table, where data is organized in rows and columns.

D. Converting the Data to JSON Format:

df.to_dict('records'): Converts the DataFrame into a list of dictionaries, where each dictionary represents a row from the DataFrame. The keys in each dictionary are the column names.

json.dumps(): Converts the list of dictionaries into a JSON-formatted string, which is a common format for transmitting data over the web.

requests.post(url, data=...): Sends an HTTP POST request to the specified URL, with the JSON data as the payload. This means it’s sending the data from the Excel file to the server at the URL.

E. Printing the Response:

print(r): Outputs the response object from the server. The response object typically contains information about whether the request was successful, along with any data the server sent back (like a status code or a message).

Playbook tasks: To see the information going through the Playbook you can access the playbook tasks overview and open the event variable. To learn more about the Playbook Task overview follow link below:

Learn more about Playbook Task Overview

Part 3

Table setup

Create a Table

Navigate to the Table app under settings. Here, you can view all existing tables and create new ones. For this business case, you will need to create a Workflow table. Click the CREATE TABLE button in the top right corner.

Configure the Table

1. Name and Description: Enter a name and a description for the table.

2. Table Type: Choose the type of table required, in this case, "Workflow Table."

3. Topic: Select the topic from which the table will pull data.

4. Create Table: Click CREATE TABLE.

For this type of table, you can leave the schema field empty, as it will be automatically generated.

Part 4

Query Setup

To visualize the table you created, go to the Query app. Here, you can use PostgreSQL to retrieve the data from the table.

Query Example:

SELECT * FROM excel_data 

Part 5

Visualization Setup

In the Query app, you can visualize your table by choosing from various available graphs or panes. Go to the visualize section, select your preferred data display method, and configure the axis to plot the data.

You can save the pane for later use by selecting the three dotted button on the top right corner, and provide a name and a description.