Building Real-Time SAP Dashboards with ERPL and DuckDB

Summary

Learn how to build real-time SAP dashboards using ERPL and DuckDB. This comprehensive guide covers everything from data extraction to dashboard creation, including practical examples and best practices for monitoring your SAP systems in real-time.

Introduction

Real-time dashboards provide immediate insights into your SAP systems, enabling faster decision-making and proactive problem resolution. Traditional approaches often involve complex ETL processes and delayed data updates. ERPL changes this by providing direct, real-time access to SAP data through DuckDB's powerful analytical capabilities.

Why Real-Time SAP Dashboards?

Business Benefits

• Immediate Insights: See data changes as they happen
• Faster Decision Making: Respond quickly to business events
• Proactive Management: Identify issues before they become problems
• Improved Efficiency: Reduce time spent on manual reporting

Technical Advantages

• Direct Data Access: No intermediate storage or ETL delays
• High Performance: DuckDB's columnar storage and vectorized execution
• Scalability: Handle large datasets efficiently
• Flexibility: Easy to modify and extend dashboards

Architecture Overview

Traditional Approach

SAP System → ETL Tool → Data Warehouse → BI Tool → Dashboard
     ↓           ↓            ↓           ↓         ↓
   5 min      30 min       1 hour      2 hours   3+ hours

ERPL Approach

SAP System → ERPL → DuckDB → Dashboard
     ↓         ↓       ↓        ↓
   Real-time  <1 sec  <1 sec   <1 sec

Setting Up Real-Time Data Extraction

Step 1: Install ERPL

-- Install ERPL extension
SET custom_extension_repository = 'http://get.erpl.io';
INSTALL 'erpl';
LOAD 'erpl';

Step 2: Configure SAP Connection

-- Connect to SAP ERP system
SELECT sap_connect(
    host = 'your-sap-server.com',
    system_number = '00',
    client = '100',
    user = 'your-username',
    password = 'your-password'
);

Step 3: Create Real-Time Data Views

-- Create view for real-time sales data
CREATE VIEW real_time_sales AS
SELECT 
    sales_document,
    customer,
    material,
    quantity,
    net_value,
    currency,
    document_date,
    CURRENT_TIMESTAMP as extraction_time
FROM sap_read_table('VBAK')
WHERE document_date >= CURRENT_DATE - INTERVAL '1 day';

Dashboard Components

1. Sales Performance Dashboard

-- Sales performance metrics
WITH sales_metrics AS (
    SELECT 
        DATE(document_date) as sales_date,
        COUNT(*) as order_count,
        SUM(net_value) as total_sales,
        AVG(net_value) as avg_order_value
    FROM real_time_sales
    WHERE document_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY DATE(document_date)
)
SELECT 
    sales_date,
    order_count,
    total_sales,
    avg_order_value,
    LAG(total_sales) OVER (ORDER BY sales_date) as prev_day_sales,
    (total_sales - LAG(total_sales) OVER (ORDER BY sales_date)) / 
    LAG(total_sales) OVER (ORDER BY sales_date) * 100 as growth_rate
FROM sales_metrics
ORDER BY sales_date DESC;

2. Inventory Monitoring Dashboard

-- Real-time inventory levels
SELECT 
    material,
    plant,
    storage_location,
    unrestricted_stock,
    stock_in_transit,
    stock_in_quality_inspection,
    CASE 
        WHEN unrestricted_stock < 100 THEN 'LOW'
        WHEN unrestricted_stock < 500 THEN 'MEDIUM'
        ELSE 'HIGH'
    END as stock_level,
    CURRENT_TIMESTAMP as last_updated
FROM sap_read_table('MCHB')
WHERE plant = '1000'
ORDER BY unrestricted_stock ASC;

3. Financial Health Dashboard

-- Financial health indicators
WITH financial_metrics AS (
    SELECT 
        account,
        SUM(debit_amount) as total_debits,
        SUM(credit_amount) as total_credits,
        SUM(debit_amount) - SUM(credit_amount) as net_balance
    FROM sap_read_table('FAGLFLEXT')
    WHERE fiscal_year = '2024'
    GROUP BY account
)
SELECT 
    account,
    total_debits,
    total_credits,
    net_balance,
    CASE 
        WHEN net_balance > 0 THEN 'POSITIVE'
        WHEN net_balance < 0 THEN 'NEGATIVE'
        ELSE 'BALANCED'
    END as balance_status
FROM financial_metrics
ORDER BY ABS(net_balance) DESC;

Advanced Dashboard Features

1. Alert System

-- Create alert conditions
CREATE VIEW inventory_alerts AS
SELECT 
    material,
    plant,
    unrestricted_stock,
    'LOW_STOCK' as alert_type,
    'Stock level below minimum threshold' as alert_message,
    CURRENT_TIMESTAMP as alert_time
FROM sap_read_table('MCHB')
WHERE unrestricted_stock < 50;

-- Check for alerts
SELECT * FROM inventory_alerts;

2. Trend Analysis

-- Sales trend analysis
WITH sales_trends AS (
    SELECT 
        DATE(document_date) as sales_date,
        SUM(net_value) as daily_sales,
        LAG(SUM(net_value)) OVER (ORDER BY DATE(document_date)) as prev_day_sales
    FROM sap_read_table('VBAK')
    WHERE document_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY DATE(document_date)
)
SELECT 
    sales_date,
    daily_sales,
    prev_day_sales,
    (daily_sales - prev_day_sales) / prev_day_sales * 100 as daily_growth,
    AVG(daily_sales) OVER (ORDER BY sales_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as weekly_avg
FROM sales_trends
ORDER BY sales_date DESC;

3. Comparative Analysis

-- Year-over-year comparison
WITH current_year AS (
    SELECT 
        MONTH(document_date) as month,
        SUM(net_value) as monthly_sales
    FROM sap_read_table('VBAK')
    WHERE YEAR(document_date) = 2024
    GROUP BY MONTH(document_date)
),
previous_year AS (
    SELECT 
        MONTH(document_date) as month,
        SUM(net_value) as monthly_sales
    FROM sap_read_table('VBAK')
    WHERE YEAR(document_date) = 2023
    GROUP BY MONTH(document_date)
)
SELECT 
    c.month,
    c.monthly_sales as current_year_sales,
    p.monthly_sales as previous_year_sales,
    (c.monthly_sales - p.monthly_sales) / p.monthly_sales * 100 as yoy_growth
FROM current_year c
JOIN previous_year p ON c.month = p.month
ORDER BY c.month;

Dashboard Implementation

1. Python Dashboard with Streamlit

import streamlit as st
import duckdb
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go

# Connect to DuckDB
conn = duckdb.connect('sap_dashboard.db')

# Dashboard title
st.title("Real-Time SAP Dashboard")

# Sales metrics
st.header("Sales Performance")
sales_data = conn.execute("""
    SELECT 
        DATE(document_date) as sales_date,
        SUM(net_value) as daily_sales
    FROM real_time_sales
    WHERE document_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY DATE(document_date)
    ORDER BY sales_date
""").fetchdf()

# Create line chart
fig = px.line(sales_data, x='sales_date', y='daily_sales', 
              title='Daily Sales Trend')
st.plotly_chart(fig)

# Inventory levels
st.header("Inventory Levels")
inventory_data = conn.execute("""
    SELECT 
        material,
        unrestricted_stock,
        stock_level
    FROM inventory_monitoring
    WHERE stock_level = 'LOW'
    ORDER BY unrestricted_stock ASC
""").fetchdf()

st.dataframe(inventory_data)

# Financial health
st.header("Financial Health")
financial_data = conn.execute("""
    SELECT 
        account,
        net_balance,
        balance_status
    FROM financial_health
    ORDER BY ABS(net_balance) DESC
    LIMIT 10
""").fetchdf()

# Create bar chart
fig = px.bar(financial_data, x='account', y='net_balance', 
              color='balance_status', title='Top 10 Accounts by Balance')
st.plotly_chart(fig)

2. R Shiny Dashboard

library(shiny)
library(DBI)
library(plotly)
library(DT)

# Connect to DuckDB
con <- dbConnect(duckdb::duckdb(), "sap_dashboard.db")

# UI
ui <- fluidPage(
    titlePanel("Real-Time SAP Dashboard"),
    
    sidebarLayout(
        sidebarPanel(
            selectInput("plant", "Select Plant:", 
                       choices = c("1000", "2000", "3000")),
            dateRangeInput("date_range", "Date Range:",
                          start = Sys.Date() - 30,
                          end = Sys.Date())
        ),
        
        mainPanel(
            tabsetPanel(
                tabPanel("Sales", plotlyOutput("sales_chart")),
                tabPanel("Inventory", DT::dataTableOutput("inventory_table")),
                tabPanel("Financial", plotlyOutput("financial_chart"))
            )
        )
    )
)

# Server
server <- function(input, output) {
    
    # Sales chart
    output$sales_chart <- renderPlotly({
        query <- paste0("
            SELECT 
                DATE(document_date) as sales_date,
                SUM(net_value) as daily_sales
            FROM real_time_sales
            WHERE document_date BETWEEN '", input$date_range[1], "' AND '", input$date_range[2], "'
            GROUP BY DATE(document_date)
            ORDER BY sales_date
        ")
        
        data <- dbGetQuery(con, query)
        
        plot_ly(data, x = ~sales_date, y = ~daily_sales, type = 'scatter', mode = 'lines') %>%
            layout(title = "Daily Sales Trend")
    })
    
    # Inventory table
    output$inventory_table <- DT::renderDataTable({
        query <- paste0("
            SELECT 
                material,
                plant,
                unrestricted_stock,
                stock_level
            FROM inventory_monitoring
            WHERE plant = '", input$plant, "'
            ORDER BY unrestricted_stock ASC
        ")
        
        data <- dbGetQuery(con, query)
        DT::datatable(data, options = list(pageLength = 20))
    })
    
    # Financial chart
    output$financial_chart <- renderPlotly({
        query <- "
            SELECT 
                account,
                net_balance,
                balance_status
            FROM financial_health
            ORDER BY ABS(net_balance) DESC
            LIMIT 10
        "
        
        data <- dbGetQuery(con, query)
        
        plot_ly(data, x = ~account, y = ~net_balance, 
                color = ~balance_status, type = 'bar') %>%
            layout(title = "Top 10 Accounts by Balance")
    })
}

# Run the application
shinyApp(ui = ui, server = server)

Performance Optimization

1. Query Optimization

-- Optimize queries for real-time performance
CREATE INDEX idx_sales_date ON real_time_sales(document_date);
CREATE INDEX idx_inventory_plant ON inventory_monitoring(plant);

-- Use materialized views for complex calculations
CREATE MATERIALIZED VIEW sales_summary AS
SELECT 
    DATE(document_date) as sales_date,
    COUNT(*) as order_count,
    SUM(net_value) as total_sales
FROM sap_read_table('VBAK')
WHERE document_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY DATE(document_date);

2. Caching Strategy

-- Implement caching for frequently accessed data
CREATE TABLE sales_cache AS
SELECT * FROM sap_read_table('VBAK')
WHERE document_date >= CURRENT_DATE - INTERVAL '1 day';

-- Refresh cache periodically
CREATE OR REPLACE FUNCTION refresh_sales_cache()
RETURNS VOID AS $$
BEGIN
    DELETE FROM sales_cache;
    INSERT INTO sales_cache
    SELECT * FROM sap_read_table('VBAK')
    WHERE document_date >= CURRENT_DATE - INTERVAL '1 day';
END;
$$ LANGUAGE plpgsql;

3. Parallel Processing

-- Use parallel processing for large datasets
SELECT * FROM sap_read_table('VBAK')
WHERE document_date >= CURRENT_DATE - INTERVAL '1 day'
USING PARALLEL 4;

Monitoring and Alerting

1. System Health Monitoring

-- Monitor system health
CREATE VIEW system_health AS
SELECT 
    'SAP_CONNECTION' as component,
    CASE 
        WHEN sap_ping() = 'OK' THEN 'HEALTHY'
        ELSE 'UNHEALTHY'
    END as status,
    CURRENT_TIMESTAMP as check_time
UNION ALL
SELECT 
    'DATA_FRESHNESS' as component,
    CASE 
        WHEN MAX(extraction_time) > CURRENT_TIMESTAMP - INTERVAL '5 minutes' THEN 'HEALTHY'
        ELSE 'STALE'
    END as status,
    CURRENT_TIMESTAMP as check_time
FROM real_time_sales;

2. Performance Monitoring

-- Monitor query performance
CREATE VIEW query_performance AS
SELECT 
    query_text,
    execution_time,
    rows_returned,
    memory_used
FROM duckdb_query_log()
WHERE execution_time > INTERVAL '1 second'
ORDER BY execution_time DESC;

Best Practices

1. Data Freshness

• Real-time Updates: Refresh data every 1-5 minutes
• Incremental Updates: Only update changed data
• Fallback Mechanisms: Handle connection failures gracefully
• Data Validation: Verify data quality and completeness

2. Performance

• Query Optimization: Use indexes and materialized views
• Caching: Implement strategic caching for hot data
• Parallel Processing: Use multiple threads for large datasets
• Resource Management: Monitor memory and CPU usage

3. User Experience

• Loading Indicators: Show progress for long-running queries
• Error Handling: Provide meaningful error messages
• Responsive Design: Ensure dashboards work on all devices
• Customization: Allow users to personalize their views

4. Security

• Access Control: Implement proper user authentication
• Data Encryption: Encrypt sensitive data in transit and at rest
• Audit Logging: Log all dashboard access and queries
• Compliance: Ensure compliance with data protection regulations

Troubleshooting

Common Issues

1. Slow Query Performance: Optimize queries and add indexes
2. Connection Failures: Implement retry logic and fallback mechanisms
3. Data Staleness: Check extraction schedules and connection status
4. Memory Issues: Monitor memory usage and implement pagination

Debugging Tips

-- Enable query logging
SET log_level = 'debug';

-- Check system status
SELECT * FROM system_health;

-- Monitor performance
SELECT * FROM query_performance;

-- Test connections
SELECT sap_ping();

Conclusion

Building real-time SAP dashboards with ERPL and DuckDB provides significant advantages over traditional approaches:

• Real-time Data: Immediate access to SAP data
• High Performance: DuckDB's optimized execution engine
• Simplified Architecture: Direct connection without ETL complexity
• Cost Effective: Reduced infrastructure and licensing costs

The combination of ERPL's SAP connectivity and DuckDB's analytical capabilities creates a powerful platform for real-time business intelligence.

Next Steps

Ready to build your own real-time SAP dashboard? Here's how to get started:

1. Install ERPL: Get started with installation
2. Explore Examples: Review key tasks
3. Build Your Dashboard: Use the provided examples as templates
4. Contact Us: Get help with your specific requirements

Additional Resources

• ERPL Documentation
• DuckDB Documentation
• Streamlit Documentation
• R Shiny Documentation
• Contact Our Team for personalized guidance