Enterprise Database Systems

Master enterprise-grade databases including SAP HANA in-memory computing, InfluxDB time-series analytics, and PostGIS geospatial queries. Work with SQL editors, visual builders, and specialized interfaces.

Enterprise Database Systems - Module 10

Enterprise databases with SQL editors, visual query builders, and specialized interfaces!

Lab 28: SAP HANA In-Memory Analytics
Enterprise / Expert
Scenario: Real-Time Financial Analytics
GlobalBank needs real-time analytics on millions of transactions using SAP HANA's in-memory columnar store. Write SQL queries in the editor to create column tables, calculation views, and optimize queries using HANA-specific features like text search and window functions.

Learning Objectives:

  • Column Tables: Create columnar tables optimized for analytics
  • Full-Text Index: Enable text search on structured data
  • Window Functions: Use HANA-specific analytic functions
  • Calculation Views: Build analytic views with aggregations

?? Step-by-Step Instructions

  1. Step 1: Create Column Table
    Create a columnar table for transaction data.
    Required SQL:
    CREATE COLUMN TABLE transactions (...)

    � Must use: COLUMN TABLE (not ROW TABLE)
    � Table name: transactions
    � Columns: trans_id, account_id, amount, trans_date, category, description
    ?? Tip: COLUMN TABLE is optimized for analytics, ROW TABLE for OLTP.
  2. Step 2: Create Full-Text Index
    Enable full-text search on the description column.
    Required SQL:
    CREATE FULLTEXT INDEX idx_desc ON transactions(description)

    � Must use: FULLTEXT INDEX
    � On column: description
    ?? Tip: FULLTEXT INDEX enables CONTAINS() predicate for text search.
  3. Step 3: Window Function Query
    Calculate running totals using window functions.
    Required Elements:
    � Must use: SUM() OVER window function
    � Must use: PARTITION BY account_id
    � Must use: ORDER BY trans_date
    ?? Tip: Window functions process rows without collapsing them.
  4. Step 4: Text Search Query
    Search transactions using full-text search.
    Required Elements:
    � Must use: CONTAINS() predicate
    � Search in: description column
    � Search term: any keyword
    ?? Tip: CONTAINS() is SAP HANA's full-text search predicate.
  5. Step 5: Create Calculation View
    Create an analytic view with aggregations.
    Required Elements:
    � Must use: CREATE VIEW
    � Include: SUM(amount) aggregation
    � Include: GROUP BY category
    ?? Tip: Views pre-aggregate data for faster dashboard queries.
  6. Step 6: RANK() Window Function
    Rank transactions by amount within each category.
    Required Elements:
    � Must use: RANK() OVER
    � Must use: PARTITION BY category
    � Must use: ORDER BY amount DESC
    ?? Tip: RANK() assigns same rank to ties, skipping next ranks.
SAP HANA SQL Editor
HANA 2.0 SPS06
Ready to execute SQL...
SAP HANA SQL Reference
� CREATE COLUMN TABLE - Analytics-optimized table
� CREATE FULLTEXT INDEX - Text search index
� SUM() OVER (PARTITION BY ... ORDER BY ...)
� CONTAINS(col, 'term') - Full-text search
Progress: 0/6 tasks completed
Score: 0/100
0%

Lab Completed!

Excellent SAP HANA mastery!

Lab 29: InfluxDB Time-Series Analytics
Time-Series / Expert
Scenario: Server Metrics Monitoring
CloudOps needs to monitor server metrics (CPU, memory, disk) across thousands of servers. Use the InfluxDB visual query builder and Flux language to create buckets, write data points, and build aggregation queries for dashboards.

Learning Objectives:

  • Buckets: Create time-series data buckets with retention policies
  • Line Protocol: Write data points using InfluxDB line protocol
  • Flux Queries: Query and transform time-series data
  • Aggregations: Compute averages, max, and percentiles over time windows

?? Step-by-Step Instructions

  1. Step 1: Create Bucket
    Create a bucket for server metrics with retention.
    Use GUI to configure:
    � Bucket name: server_metrics
    � Retention: 30d (30 days)
    � Click "Create Bucket" button
    ?? Tip: Retention automatically deletes old data to save storage.
  2. Step 2: Write Data Point
    Insert a metric using line protocol format.
    Line Protocol Format:
    cpu,host=server01,region=us-east value=85.5

    � Measurement: cpu
    � Tags: host, region
    � Field: value
    ?? Tip: Line protocol: measurement,tag=val field=val timestamp
  3. Step 3: Basic Flux Query
    Query data from the bucket using Flux.
    Required Flux Query:
    � Must use: from(bucket:
    � Must use: |> range(
    � Must use: |> filter(
    ?? Tip: Flux uses pipes (|>) to chain transformations.
  4. Step 4: Aggregation Query
    Calculate average CPU over 5-minute windows.
    Required Elements:
    � Must use: |> aggregateWindow(
    � Window: every: 5m
    � Function: fn: mean
    ?? Tip: aggregateWindow groups data into time buckets and applies fn.
  5. Step 5: Group and Pivot
    Group by host and pivot for dashboard display.
    Required Elements:
    � Must use: |> group(columns:
    � Must use: |> pivot(
    � Pivot on: _field
    ?? Tip: pivot transforms rows into columns for easier visualization.
  6. Step 6: Alert Threshold Query
    Find servers with CPU > 90% for alerting.
    Required Elements:
    � Must use: |> filter(fn: (r) => r._value >
    � Threshold: 90
    � Include host in output
    ?? Tip: Filter after aggregation to find threshold violations.
InfluxDB Data Explorer
Ready...
Progress: 0/6 tasks completed
Score: 0/100
0%

Lab Completed!

Excellent InfluxDB mastery!

Lab 30: PostGIS Geospatial Queries
Geospatial / Expert
Scenario: Delivery Route Optimization
LogiTrack needs to optimize delivery routes by analyzing geographic data. Use PostGIS extension with PostgreSQL to create spatial tables, index geometries, and write queries for distance calculations, spatial joins, and route optimization.

Learning Objectives:

  • Spatial Types: Create tables with geometry/geography columns
  • Spatial Indexes: Create GiST indexes for fast spatial queries
  • Distance Queries: Calculate distances between points
  • Spatial Functions: Use ST_Within, ST_Buffer, ST_Intersects

?? Step-by-Step Instructions

  1. Step 1: Enable PostGIS Extension
    Enable the PostGIS extension in your database.
    Required SQL:
    CREATE EXTENSION IF NOT EXISTS postgis;
    ?? Tip: PostGIS adds spatial types and functions to PostgreSQL.
  2. Step 2: Create Spatial Table
    Create a table with a geometry column for warehouses.
    Required Elements:
    � Table name: warehouses
    � Must use: geometry(Point, 4326) column type
    � Column name: location
    ?? Tip: SRID 4326 is WGS84 (GPS coordinates: lat/lng).
  3. Step 3: Create Spatial Index
    Create a GiST index for fast spatial queries.
    Required SQL:
    CREATE INDEX idx_warehouse_loc ON warehouses USING GIST(location);

    � Must use: USING GIST
    � On column: location
    ?? Tip: GiST (Generalized Search Tree) is optimized for spatial data.
  4. Step 4: Insert Spatial Data
    Insert a warehouse location using ST_SetSRID and ST_MakePoint.
    Required Elements:
    � Must use: ST_SetSRID(ST_MakePoint(
    � Coordinates: longitude, latitude
    � SRID: 4326
    ?? Tip: ST_MakePoint takes (longitude, latitude) - NOT (lat, lng)!
  5. Step 5: Distance Calculation
    Find warehouses within 10km of a delivery point.
    Required Elements:
    � Must use: ST_DWithin( or ST_Distance(
    � Distance in meters or use: ::geography cast
    � Filter: within 10000 meters (10km)
    ?? Tip: Cast to geography for accurate distance in meters on Earth.
  6. Step 6: Spatial Join with Buffer
    Find all delivery points within a warehouse's service area.
    Required Elements:
    � Must use: ST_Within( or ST_Intersects(
    � Must use: ST_Buffer( for service area
    � JOIN deliveries with warehouses
    ?? Tip: ST_Buffer creates a circle around a point.
PostGIS SQL Console
PostgreSQL 15 + PostGIS 3.3
Ready to execute SQL...
PostGIS Function Reference
� ST_MakePoint(lng, lat) - Create point
� ST_SetSRID(geom, 4326) - Set coordinate system
� ST_Distance(a, b) - Calculate distance
� ST_DWithin(a, b, dist) - Within distance
� ST_Buffer(geom, radius) - Create buffer zone
Progress: 0/6 tasks completed
Score: 0/100
0%

Lab Completed!

Excellent PostGIS mastery!