Live Query

Live Query enables you to analyze data directly from your database or data warehouse without importing it into Scoop. This is ideal for large datasets, real-time analysis, or when data must remain in your infrastructure.

When to Use Live Query

Key Concepts

Star Schema

Live Query works best with a star schema architecture:

• Fact Table: Central table containing your transactional data (sales, events, transactions)
• Dimension Tables: Reference tables providing context (customers, products, dates, locations)

This structure allows Scoop's AI to understand relationships and generate accurate analytical queries.

How It Works

1. Connect: Establish a secure connection to your database
2. Define Schema: Select your fact table and dimension tables
3. Configure Joins: Specify how tables relate to each other
4. Query: Ask questions in natural language; Scoop generates optimized SQL

Getting Started

Step 1: Create a New Live Query

1. Navigate to Live Query in the sidebar
2. Click New Live Query
3. The setup wizard will guide you through configuration

Step 2: Configure Your Connection

Choose your database type and enter connection details:

• Server Host: Your database server address
• Port: Database port (auto-filled based on database type)
• Database: The database or schema name
• Username: Read-only database user
• Password: User password

Click Test Connection to verify your settings before proceeding.

Step 3: Add Dimension Tables

Dimension tables contain the attributes you want to analyze by:

1. Select a schema from the dropdown
2. Click Add Dimension Table
3. Select a table from the list
4. Choose which columns to include
5. Repeat for additional dimensions

Examples of dimension tables:

• Customer dimension (customer_id, name, region, segment)
• Product dimension (product_id, name, category, brand)
• Date dimension (date_id, date, month, quarter, year)
• Location dimension (location_id, city, state, country)

Step 4: Select Your Fact Table

The fact table is your central transactional table:

1. Select the fact table from your schema
2. Choose columns to include (typically measures and foreign keys)
3. Configure joins to your dimension tables

Step 5: Configure Join Relationships

For each dimension table, specify:

• FK Column (Fact): The foreign key column in your fact table
• PK Column (Dimension): The primary key column in the dimension table
• Join Type: LEFT (include all fact rows) or INNER (only matching rows)

Step 6: Name and Save

1. Give your Live Query a descriptive name
2. Add an optional description
3. Click Create Live Query

Querying Your Data

Once configured, you can analyze your data using natural language:

Example queries:

• "Show me total sales by region for last quarter"
• "What are the top 10 customers by revenue?"
• "Compare monthly sales growth by product category"
• "Which stores have declining performance?"

Scoop's AI understands your schema and generates optimized SQL queries automatically.

Best Practices

Database User Permissions

• Always use read-only credentials
• Grant SELECT only on necessary tables
• Avoid granting access to system tables

Performance Optimization

• Index foreign key columns in your fact table
• Index columns frequently used in WHERE clauses
• Consider materialized views for complex aggregations
• Use partitioning on large fact tables

Schema Design Tips

• Keep dimension tables denormalized where practical
• Use consistent naming for join columns
• Include a date dimension for time-based analysis
• Add descriptive columns (not just IDs) in dimensions

Supported Databases

Live Query supports all Scoop-connected databases:

Troubleshooting

Connection Issues

• Verify Scoop's IP addresses are whitelisted in your firewall
• Check that the database is accessible from the internet
• Confirm credentials have SELECT permissions on required tables

Slow Queries

• Add indexes on join columns and frequently filtered columns
• Review query plans in your database
• Consider reducing the data scope with partitioning

Incorrect Results

• Verify join relationships are correctly configured
• Check that foreign keys match primary keys exactly
• Ensure dimension tables have unique primary keys

Security Considerations

• Credentials are encrypted at rest and in transit
• No data is stored in Scoop; queries run directly on your database
• Read-only access ensures your data cannot be modified
• Query logging available for compliance requirements

For additional help, contact Scoop Support.