Protobi

← All blog posts View post

# Protobi SQL Database API - User Guide

## What is the Protobi SQL Database API?

Protobi is exceptionally good at handling complex survey data.  But your end clients may already be used to seeing their data in other leading dashboards that integrate other data sources.
You can now include Protobi dashboard data into any other visualization solution using Protobi SQL Database API.  Basically, Protobi can be just like any other standard PostgresQL database.

The Protobi SQL Database API allows you to connect business intelligence tools like Tableau, Looker, PowerBI, and other SQL-based analytics platforms directly to your Protobi projects using standard database connections.  Your BI tools can query Protobi data as if it were a PostgreSQL database, accessing all of Protobi's calculated fields, cross-tabulations, and statistical computations through standard SQL queries.

So you get the best of several worlds, your end clients can use the tools their used to and also use Protobi advanced survey analytics.

## Why Use This?

**Keep Your Existing Dashboards** - You don't need to rebuild dashboards or force everyone to learn a new tool. Continue using Tableau for executive dashboards while accessing Protobi's advanced survey analysis and weighting capabilities.

**Access Computed Data** - Query weighted data, recoded variables, computed metrics, and cross-tabulations directly from your BI tools without replicating complex calculations.

**Standard SQL Interface** - Use familiar SQL syntax (`SELECT`, `WHERE`, `GROUP BY`, `LIMIT`) to query your survey data.

## How to Connect

### Connection Details

Connect using any PostgreSQL-compatible client (Tableau, psql, TablePlus, DBeaver, etc.):

- **Host:** `localhost` (or your Protobi server hostname)
- **Port:** `5432`
- **Database:** Your Protobi project ID (e.g., `6744814e48513c0002f3d9d3`)
- **Username:** Your Protobi account email
- **Password:** Your Protobi API key
- **SSL:** Required

### Getting Your API Key

1. Log into Protobi
2. Go to Account Settings
3. Find or generate your API key
4. Copy and use as the password when connecting

### Example: Connecting with psql

```bash
psql "host=localhost port=5432 dbname=YOUR_PROJECT_ID user=you@example.com sslmode=require"
# Enter your API key when prompted for password
```

### Example: Connecting with Tableau

1. Choose "PostgreSQL" as the data source
2. Server: `localhost`, Port: `5432`
3. Database: Your project ID
4. Username: Your email
5. Password: Your API key
6. Check "Require SSL"

## What Can You Query?

### Elements as Tables

Each Protobi element (question, variable, computed field) appears as a table. Query any element by its key:

```sql
SELECT * FROM Q1;
SELECT * FROM region;
SELECT * FROM satisfaction_score;
```

### What You Get Back

By default, queries return **aggregated distributions** - cross-tabulations showing counts and statistics:

```sql
SELECT * FROM Q10a_cloud;
```

Returns a frequency distribution showing how many respondents selected each answer choice.

## Supported SQL

### Basic Query

```sql
SELECT * FROM element_key;
```

Returns the distribution for that element.

### Filter with WHERE

```sql
SELECT * FROM Q10a_cloud WHERE region IN (1, 3);
SELECT * FROM satisfaction WHERE score >= 4;
SELECT * FROM Q5 WHERE quarter = 'Q2' AND region = 'West';
```

Filters apply to the base respondent data before aggregation.

### Cross-Tabulate with GROUP BY

```sql
SELECT * FROM Q10a_cloud GROUP BY region;
SELECT * FROM satisfaction GROUP BY region, quarter;
```

Creates cross-tabs (banners) showing the element distribution broken out by the specified dimensions.

### Limit Results

```sql
SELECT * FROM Q10a_cloud LIMIT 10;
SELECT * FROM Q5 WHERE region IN (1,3) GROUP BY quarter LIMIT 20;
```

Returns only the specified number of rows.

### Combine Them All

```sql
SELECT * FROM satisfaction
WHERE region IN ('East', 'West') AND quarter = 'Q2'
GROUP BY region, age_group
LIMIT 50;
```

Filters respondents from East/West regions in Q2, cross-tabulates by region and age group, returns first 50 rows.

## What's Supported Now

✅ **SELECT * FROM element_key** - Query any Protobi element  
✅ **WHERE clauses** - Filter with `IN`, `=`, `>=`, `<=`, `>`, `<`, `!=`, and `AND`  
✅ **GROUP BY** - Cross-tabulate by one or more dimensions  
✅ **LIMIT** - Limit number of rows returned  
✅ **SSL/TLS** - Secure authentication with API keys

## Authentication & Security

- All connections require SSL/TLS encryption
- API keys are scoped to your user account
- You can only access projects you have permission to view
- API keys can be regenerated in Account Settings if compromised

## Examples

### Tableau Use Case

**Scenario:** Executive team has existing Tableau dashboards showing sales and operations metrics. They want to add customer satisfaction scores from your Protobi survey, weighted by customer segment.

**Solution:**
1. Add Protobi as a PostgreSQL data source in Tableau
2. Query weighted satisfaction scores: `SELECT * FROM satisfaction_weighted GROUP BY segment`
3. Join with existing sales data on segment
4. Dashboard now shows satisfaction alongside business metrics

### Quick Analysis in psql

```sql
-- How many respondents per region?
SELECT * FROM respondents GROUP BY region;

-- Satisfaction scores for premium customers
SELECT * FROM satisfaction WHERE customer_tier = 'premium';

-- Top 10 product preferences by region
SELECT * FROM product_preference GROUP BY region LIMIT 10;
```

Date

Status

Slug edit

Thumbnail

Categories Manage