Use GibsonAI's text-to-SQL capabilities to power external dashboards and visualization tools. Create database schemas and query them with natural language to feed data into tools like Retool, Grafana, or custom dashboards.

Key Features

Natural Language to SQL

  • Text-to-SQL Queries: Convert natural language questions to SQL queries
  • Complex Queries: Handle multi-table joins and aggregations
  • Safe Execution: Protected query execution with built-in safeguards
  • Flexible Results: Return results in formats suitable for dashboards

Database Schema Management

  • Schema Creation: Create database schemas using natural language
  • Table Management: Add and modify tables with simple prompts
  • Relationship Building: Define relationships between tables naturally
  • Data Type Handling: Automatically select appropriate data types

REST API Integration

  • Auto-Generated APIs: REST endpoints for all database tables
  • Query Endpoint: Dedicated endpoint for natural language queries
  • JSON Responses: Consistent JSON format for easy integration
  • Authentication: Secure API access with authentication

Implementation Examples

Setting Up Database for Dashboard Data

# Using Gibson CLI to create database schema for dashboard data
# Create analytics tables
# gibson modify page_views "Create page_views table with id, page_url, user_id, timestamp, session_id"
# gibson modify user_sessions "Create user_sessions table with id, user_id, start_time, end_time, device_type"
# gibson modify conversion_events "Create conversion_events table with id, user_id, event_type, value, timestamp"
# gibson modify user_metrics "Create user_metrics table with user_id, metric_name, value, date"

# Generate models and apply changes
# gibson code models
# gibson merge

Querying Data for Dashboard Widgets

import requests

class DashboardDataProvider:
    def __init__(self, api_key):
        self.api_key = api_key
        self.base_url = "https://api.gibsonai.com/v1/-"
        self.headers = {"Authorization": f"Bearer {api_key}"}

    def get_daily_active_users(self):
        """Get daily active users for dashboard"""
        query_request = {
            "query": "Count unique users by date for the last 30 days"
        }

        response = requests.post(
            f"{self.base_url}/query",
            json=query_request,
            headers=self.headers
        )

        if response.status_code == 200:
            return response.json()
        else:
            print(f"Query failed: {response.status_code}")
            return None

    def get_page_view_stats(self):
        """Get page view statistics"""
        query_request = {
            "query": "Show page views by URL for the last 7 days with total counts"
        }

        response = requests.post(
            f"{self.base_url}/query",
            json=query_request,
            headers=self.headers
        )

        if response.status_code == 200:
            return response.json()
        else:
            print(f"Query failed: {response.status_code}")
            return None

    def get_conversion_funnel(self):
        """Get conversion funnel data"""
        query_request = {
            "query": "Calculate conversion funnel from page views to conversions by event type"
        }

        response = requests.post(
            f"{self.base_url}/query",
            json=query_request,
            headers=self.headers
        )

        if response.status_code == 200:
            return response.json()
        else:
            print(f"Query failed: {response.status_code}")
            return None

    def get_user_engagement_metrics(self):
        """Get user engagement metrics"""
        query_request = {
            "query": "Calculate average session duration and pages per session by device type"
        }

        response = requests.post(
            f"{self.base_url}/query",
            json=query_request,
            headers=self.headers
        )

        if response.status_code == 200:
            return response.json()
        else:
            print(f"Query failed: {response.status_code}")
            return None

Integrating with External Dashboard Tools

# Example integration with Retool
class RetoolIntegration:
    def __init__(self, gibson_api_key):
        self.dashboard_provider = DashboardDataProvider(gibson_api_key)

    def get_dashboard_data(self, widget_type):
        """Get data for specific dashboard widget"""
        if widget_type == "daily_active_users":
            return self.dashboard_provider.get_daily_active_users()
        elif widget_type == "page_views":
            return self.dashboard_provider.get_page_view_stats()
        elif widget_type == "conversion_funnel":
            return self.dashboard_provider.get_conversion_funnel()
        elif widget_type == "engagement_metrics":
            return self.dashboard_provider.get_user_engagement_metrics()
        else:
            return None

# Example integration with Grafana
class GrafanaIntegration:
    def __init__(self, gibson_api_key):
        self.dashboard_provider = DashboardDataProvider(gibson_api_key)

    def get_time_series_data(self, metric_name, time_range):
        """Get time series data for Grafana"""
        query_request = {
            "query": f"Get {metric_name} values over time for the last {time_range}"
        }

        response = requests.post(
            f"{self.dashboard_provider.base_url}/query",
            json=query_request,
            headers=self.dashboard_provider.headers
        )

        if response.status_code == 200:
            # Format data for Grafana
            data = response.json()
            return self.format_for_grafana(data)
        else:
            return None

    def format_for_grafana(self, data):
        """Format data for Grafana consumption"""
        # Convert to Grafana time series format
        return {
            "target": "metric_name",
            "datapoints": [[value, timestamp] for value, timestamp in data]
        }

Creating Custom Dashboard API

from flask import Flask, jsonify, request
import requests

app = Flask(__name__)

class CustomDashboardAPI:
    def __init__(self, gibson_api_key):
        self.gibson_api_key = gibson_api_key
        self.base_url = "https://api.gibsonai.com/v1/-"
        self.headers = {"Authorization": f"Bearer {gibson_api_key}"}

    def execute_query(self, query):
        """Execute natural language query"""
        query_request = {"query": query}

        response = requests.post(
            f"{self.base_url}/query",
            json=query_request,
            headers=self.headers
        )

        if response.status_code == 200:
            return response.json()
        else:
            return None

dashboard_api = CustomDashboardAPI("your_gibson_api_key")

@app.route('/api/dashboard/users/daily')
def daily_users():
    """Get daily user metrics"""
    data = dashboard_api.execute_query("Count unique users by date for the last 30 days")
    return jsonify(data)

@app.route('/api/dashboard/conversions')
def conversions():
    """Get conversion data"""
    data = dashboard_api.execute_query("Show conversion events by type for the last 7 days")
    return jsonify(data)

@app.route('/api/dashboard/query')
def custom_query():
    """Execute custom query from dashboard"""
    query = request.args.get('q')
    if not query:
        return jsonify({"error": "Query parameter 'q' is required"}), 400

    data = dashboard_api.execute_query(query)
    if data:
        return jsonify(data)
    else:
        return jsonify({"error": "Query failed"}), 500

Dashboard Integration Examples

Business Intelligence Dashboard

# Create schema for business intelligence
# gibson modify sales_data "Create sales_data table with id, product_id, sales_amount, sales_date, region"
# gibson modify product_catalog "Create product_catalog table with id, name, category, price, cost"
# gibson modify customer_segments "Create customer_segments table with customer_id, segment, value_score"
# gibson code models
# gibson merge

def get_business_intelligence_data():
    """Get data for business intelligence dashboard"""

    # Revenue by region
    revenue_by_region = {
        "query": "Calculate total revenue by region for the last quarter"
    }

    # Top performing products
    top_products = {
        "query": "Show top 10 products by sales volume for the last month"
    }

    # Customer segment analysis
    customer_analysis = {
        "query": "Analyze customer segments by average order value and frequency"
    }

    return {
        "revenue_by_region": revenue_by_region,
        "top_products": top_products,
        "customer_analysis": customer_analysis
    }

Operational Dashboard

# Create schema for operational metrics
# gibson modify system_metrics "Create system_metrics table with id, metric_name, value, timestamp, server_id"
# gibson modify error_logs "Create error_logs table with id, error_type, message, timestamp, severity"
# gibson modify user_activity "Create user_activity table with id, user_id, action, timestamp, success"
# gibson code models
# gibson merge

def get_operational_dashboard_data():
    """Get data for operational dashboard"""

    # System performance metrics
    system_performance = {
        "query": "Show average response time and error rate for the last hour"
    }

    # Error analysis
    error_analysis = {
        "query": "Count errors by type and severity for the last 24 hours"
    }

    # User activity patterns
    user_activity = {
        "query": "Analyze user activity patterns and success rates"
    }

    return {
        "system_performance": system_performance,
        "error_analysis": error_analysis,
        "user_activity": user_activity
    }

Use Cases

Analytics Dashboards

Perfect for:

  • Web analytics and user behavior tracking
  • E-commerce performance monitoring
  • Marketing campaign effectiveness
  • User engagement and retention metrics

Business Intelligence

Enable:

  • Sales performance tracking
  • Revenue analysis by segments
  • Product performance metrics
  • Customer lifetime value analysis

Operational Monitoring

Support:

  • System performance metrics
  • Error tracking and analysis
  • User activity monitoring
  • Application health dashboards

Custom Visualizations

Allow:

  • Custom chart creation based on specific queries
  • Real-time data visualization
  • Interactive dashboard elements
  • Dynamic filtering and drill-down capabilities

Benefits for Dashboard Development

Rapid Data Access

  • Natural Language: Query data using natural language instead of complex SQL
  • Instant APIs: Auto-generated REST APIs for immediate data access
  • Flexible Queries: Handle complex analytical queries easily
  • Real-time Data: Access current data for live dashboards

Easy Integration

  • Standard APIs: REST APIs work with any dashboard tool
  • JSON Format: Consistent JSON responses for easy parsing
  • Authentication: Secure API access with built-in authentication
  • Documentation: Auto-generated API documentation

Scalable Architecture

  • Database Management: Easily modify schema as dashboard needs evolve
  • Performance: Optimized queries for dashboard performance
  • Security: Secure data access with proper authentication
  • Reliability: Robust database infrastructure

Best Practices

Query Optimization

  • Specific Queries: Use specific natural language queries for better performance
  • Time Ranges: Include appropriate time ranges in queries
  • Indexing: Ensure proper indexing for frequently queried data
  • Caching: Implement caching for frequently accessed dashboard data

Dashboard Design

  • Clear Metrics: Choose clear and meaningful metrics for visualization
  • Appropriate Visualizations: Select appropriate chart types for data
  • User Experience: Design intuitive and responsive dashboards
  • Performance: Optimize dashboard loading times

Data Management

  • Data Quality: Ensure high-quality data for accurate dashboards
  • Regular Updates: Keep dashboard data current and relevant
  • Backup Strategy: Implement proper data backup and recovery
  • Monitoring: Monitor dashboard performance and usage

Getting Started

  1. Create Database Schema: Define your data structure using natural language
  2. Generate Models: Create Python models with Gibson CLI
  3. Populate Data: Add sample or real data to your database
  4. Test Queries: Validate your natural language queries
  5. Integrate with Dashboard Tool: Connect your preferred dashboard tool to GibsonAI APIs

Gibson CLI Commands

# Create database schema for dashboard data
gibson modify table_name "description of table structure"
gibson code models
gibson merge

# Generate models for dashboard integration
gibson code models
gibson code schemas

Supported Dashboard Tools

  • Retool: Low-code dashboard builder
  • Grafana: Time series visualization
  • Tableau: Business intelligence platform
  • Power BI: Microsoft's business analytics tool
  • Custom Dashboards: Build your own using web frameworks

Ready to create database-powered dashboards? Get started with GibsonAI.