Datasets

Datasets are SQL-based data sources that power blocks and visualizations in the Analytics module. A dataset defines a query that retrieves and transforms data from your database, making it available for charts, tables, and reports.

Overview

The Datasets module provides:

• SQL Query Editor - Define custom queries to retrieve data
• Column Detection - Automatic identification of column types (numeric, text, date)
• Security Groups - Control which users can access the data
• OData Exposure - Make datasets available to external tools via OData v4
• Reusability - Use the same dataset across multiple blocks

Note

Datasets are consumed by Blocks to create visualizations. See Blocks for visualization configuration and Analytics for dashboard creation.

Accessing Datasets

Navigate to Settings > Data Management > Datasets to view and manage datasets.

Tip

Click the Actions menu (three dots) next to the page title to access the Add Dataset option and a link to this documentation for quick reference.

Datasets List

The main view displays all datasets in your company.

Table Columns

Column	Description
Name	Dataset name (click to edit)
OData	Checkmark indicates OData access is enabled
Description	Dataset description

Searching Datasets

Use the search box in the header to filter datasets by name or description. The list updates as you type.

Creating a Dataset

1. Click the Add button in the header
2. Enter the required information
3. Write your SQL query
4. Optionally configure security groups and OData access
5. Click Save

Dataset Fields

General Information

Field	Required	Description
Dataset Name	Yes	Unique name to identify the dataset
Description	Yes	Explain what data this dataset provides
Query	Yes	SQL query that retrieves the data
Security Groups	No	Control which users can access this dataset

SQL Query

The query field accepts standard SQL syntax. Your query should:

• Return the columns needed for visualizations
• Include appropriate filtering and aggregation
• Use meaningful column aliases

SELECT
    DATE(created_date) as order_date,
    COUNT(*) as order_count,
    SUM(total_amount) as revenue
FROM order_header
WHERE created_date >= NOW() - INTERVAL '30 days'
GROUP BY DATE(created_date)
ORDER BY order_date DESC

Caution

Avoid SELECT * queries. Explicitly select only the columns needed for better performance and clarity.

Column Type Detection

When you save a dataset, the system executes a sample query to detect column types:

Type	Detection	Example Values
Numeric	Numbers, decimals	42, 3.14, 1000.50
Text	Strings, identifiers	"John Doe", "ORD-001"
Date	Date/time patterns	2024-01-15, Jan 15, 2024

Column types determine:

• Available aggregation options in blocks
• Filter input types (text search, date picker, numeric range)
• Display formatting options

Editing a Dataset

Click a dataset name to open the edit panel.

Query Validation

When saving, the system validates your query by executing it with LIMIT 1. Common validation errors:

Error	Cause	Solution
Syntax error	Invalid SQL	Check query syntax
Table not found	Typo in table name	Verify table exists
Column not found	Invalid column reference	Check column names
Query returned no results	Empty data or filter too restrictive	Adjust WHERE clause

Save As

Use Save As to create a copy of a dataset with modifications:

1. Make changes to the dataset
2. Click Save As
3. Enter a new name
4. The new dataset is created and selected

This is useful for creating variations of a query without modifying the original.

Security Groups

Assign security groups to control access to sensitive data.

Access Rules:

• Users in any assigned security group can access the dataset
• Users without matching groups cannot see blocks using this dataset
• Empty security groups = accessible to all users

Tip

Apply security groups at the dataset level for granular data access control. This prevents unauthorized users from seeing sensitive data even if they have access to a dashboard.

OData Access

Enable OData to expose the dataset to external tools like Excel, Power BI, or custom applications.

Enabling OData

1. Open the dataset for editing
2. Toggle Enable OData Access on
3. The endpoint URL is generated automatically
4. Copy the endpoint URL for use in external tools

OData Endpoint

The endpoint follows this format:

https://your-domain.com/odata/{dataset-slug}

For versioned datasets:

https://your-domain.com/odata/{dataset-slug}_v2

Authentication

OData endpoints require Basic Authentication. Share the endpoint URL along with your API credentials with authorized users.

Caution

OData endpoints are read-only. External tools can query data but cannot modify it.

Deleting a Dataset

1. Open the dataset for editing
2. Click the Delete button (trash icon)
3. Confirm the deletion

Caution

Deleting a dataset will break any blocks that reference it. Review block dependencies before deleting.

Dataset Relationships

Dataset
    ├── Used by → Block(s)
    │       └── Displayed on → Dashboard(s)
    ├── Security Groups → User access
    └── OData Registry → External access

Viewing Dependencies

Before modifying or deleting a dataset, consider:

• Which blocks reference this dataset
• Which dashboards display those blocks
• External tools connected via OData

Best Practices

Query Design

• Be specific - Select only needed columns, not SELECT *
• Filter early - Apply WHERE clauses to limit data at the source
• Aggregate wisely - Use GROUP BY for summary data
• Use aliases - Give columns clear, readable names
• Order results - Include ORDER BY for predictable output

Performance Optimization

• Index key columns - Ensure filtered/joined columns are indexed
• Limit date ranges - Use parameters or hardcoded limits for large tables
• Avoid subqueries - Use JOINs when possible for better performance
• Test with production data - Verify query performance with realistic data volumes

Security

• Apply security groups - Restrict access to sensitive financial or personal data
• Use row-level filtering - Include WHERE clauses that filter by organization or user context
• Audit regularly - Review security group assignments periodically
• Limit OData exposure - Only enable OData for datasets that need external access

Naming Conventions

• Use descriptive names: Daily Order Summary not orders_v2
• Include the time range if applicable: Last 30 Days Revenue
• Indicate aggregation level: Monthly Sales by Product
• Prefix with category for organization: Finance - AR Aging

Troubleshooting

Query Errors

Error	Cause	Solution
”Invalid Query”	SQL syntax error	Check for typos, missing commas, or quotes
”Query returned no results”	No matching data	Verify date ranges and filter conditions
”Column not found”	Referenced column doesn’t exist	Check table schema for correct names
”Permission denied”	Database access issue	Contact administrator

Block Display Issues

Issue	Solution
Block shows “No data”	Verify dataset query returns results
Wrong data displayed	Check query logic and block configuration
Slow loading	Optimize query with indexes and limits
Block not visible	Verify user has security group access

OData Issues

Issue	Solution
401 Unauthorized	Check Basic Auth credentials
404 Not Found	Verify OData is enabled and endpoint URL is correct
Empty results	Check that query returns data
Timeout	Optimize query for faster execution