Datasets & data exploration

​

Data exploration overview

​

Why data exploration matters

​

Dataset management

​

What are datasets?

• Logical abstraction - Represent tables, views, or custom SQL queries from your data warehouse
• Metadata rich - Include schema information, column types, relationships, and business context
• Performance optimized - Include caching and query optimization features
• Business focused - Organized and documented for business users

• Physical tables - Direct representation of warehouse tables
• Views - Pre-defined SQL views from your data warehouse
• Custom SQL - Virtual datasets created from custom SQL queries
• Semantic layer datasets - Business-friendly views from 5X’s semantic layer

​

Creating datasets

1. Navigate to datasets - Go to the datasets section in Business Intelligence
2. Select data source - Choose from your configured App Connections
3. Browse tables - Explore available tables and views
4. Create dataset - Click “Create Dataset” for your selected table
5. Configure settings - Set name, description, and basic properties

1. Access SQL Lab - Navigate to SQL Lab from the Business Intelligence menu
2. Write custom query - Create SQL query in SQL Lab
3. Test and validate - Execute query to verify results
4. Save as dataset - Use “Save as Dataset” option
5. Configure metadata - Add business context and documentation

​

Dataset best practices

​

SQL Lab

​

Introduction to SQL Lab

• Full SQL support - Write complex queries with joins, subqueries, and advanced functions
• Query history - Track and reuse previous queries
• Query sharing - Share queries with team members
• Result export - Export query results in various formats
• Query optimization - Built-in suggestions for query performance

​

Writing effective queries

-- Example: Well-structured query with clear formatting
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    region,
    COUNT(*) AS order_count,
    SUM(order_amount) AS total_revenue,
    AVG(order_amount) AS avg_order_value
FROM orders 
WHERE order_date >= '2024-01-01'
    AND order_status = 'completed'
GROUP BY 1, 2
ORDER BY month DESC, total_revenue DESC
LIMIT 100;

• Use appropriate filters - Limit data with WHERE clauses
• Select only needed columns - Avoid SELECT * for better performance
• Use proper indexing - Structure queries to leverage database indexes
• Limit result sets - Use LIMIT to control output size

​

Advanced SQL features

-- Example: Using window functions for advanced analytics
SELECT 
    customer_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY order_date DESC
    ) AS order_rank
FROM orders;

-- Example: Using CTEs for complex analysis
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date) AS month,
        SUM(order_amount) AS total_sales
    FROM orders
    GROUP BY 1
),
sales_growth AS (
    SELECT 
        month,
        total_sales,
        LAG(total_sales) OVER (ORDER BY month) AS prev_month_sales,
        (total_sales - LAG(total_sales) OVER (ORDER BY month)) / 
        LAG(total_sales) OVER (ORDER BY month) * 100 AS growth_rate
    FROM monthly_sales
)
SELECT * FROM sales_growth;

​

Troubleshooting

​

Common exploration issues