/sap-datasphere

SAP Datasphere Best Practices and Patterns

Overview

This document consolidates best practices for SAP Datasphere development, covering architecture, modeling, performance, and operations. These patterns are derived from SAP recommendations and real-world implementations.

Community Best Practices: https://pages.community.sap.com/topics/datasphere/best-practices-troubleshooting

Architecture Patterns

Layered Data Architecture

Implement a medallion/layered architecture for clarity and maintainability:

┌─────────────────────────────────────────────────────────────────┐
│                      CONSUMPTION LAYER                          │
│  Analytic Models, Consumption Views, Business Entities          │
│  Purpose: Consumer-ready analytics, SAC integration             │
└────────────────────────────────────────────────────────────────┘
                              ↑
┌─────────────────────────────────────────────────────────────────┐
│                      HARMONIZATION LAYER                        │
│  Fact Views, Dimension Views, Master Data                       │
│  Purpose: Standardized business objects, associations           │
└────────────────────────────────────────────────────────────────┘
                              ↑
┌─────────────────────────────────────────────────────────────────┐
│                      STAGING LAYER                              │
│  Cleansed Views, Validated Tables                               │
│  Purpose: Data quality, standardization, deduplication          │
└────────────────────────────────────────────────────────────────┘
                              ↑
┌─────────────────────────────────────────────────────────────────┐
│                      RAW LAYER                                  │
│  Remote Tables, Local Tables (raw copies)                       │
│  Purpose: Source data landing, unchanged format                 │
└────────────────────────────────────────────────────────────────┘

Naming Conventions

Prefixes by Layer:
  Raw: raw_<source>_<entity>
  Staging: stg_<source>_<entity>
  Dimension: dim_<entity>
  Fact: fact_<subject>
  Analytic Model: am_<subject>
  Data Access Control: dac_<scope>

Technical Names:
  - UPPER_SNAKE_CASE
  - Max 30 characters recommended
  - Avoid special characters

Business Names:
  - Title Case with spaces
  - Human-readable
  - Match glossary terms

Space Organization

Space Strategy:

  Single Tenant Pattern:
    - INTEGRATION: Data loading and staging
    - HARMONIZATION: Business layer modeling
    - ANALYTICS: Consumption models
    - SANDBOX: Development and testing

  Multi-Tenant Pattern:
    - INT_<REGION>: Regional integration
    - CORE_DATA: Shared master data
    - ANALYTICS_<BU>: Business unit analytics
    - SHARED: Cross-BU shared content

Modeling Best Practices

View Design

DO:

Good Practices:
  - Use meaningful business names for all objects
  - Document views with clear descriptions
  - Define proper semantic usage (Fact, Dimension)
  - Create associations between related entities
  - Use calculated columns for derived values
  - Apply filters early to reduce data volume

DON'T:

Avoid:
  - SELECT * without column selection
  - Hardcoded schema names
  - Missing key definitions
  - Circular associations
  - Complex nested views (>5 levels)
  - Mixing semantic types in single view

Analytic Model Design

Analytic Model Checklist:
  Structure:
    - [ ] Clear fact source identified
    - [ ] All dimensions linked via associations
    - [ ] Key columns properly defined
    - [ ] Measures use correct aggregation

  Measures:
    - [ ] Simple measures mapped directly
    - [ ] Calculated measures use correct formulas
    - [ ] Restricted measures have valid filters
    - [ ] Currency conversion configured if needed

  Dimensions:
    - [ ] Time dimension included
    - [ ] Hierarchies defined where needed
    - [ ] Text associations for descriptions
    - [ ] Master data properly linked

  Variables:
    - [ ] Filter variables for user selection
    - [ ] Reference date for time-dependent analysis
    - [ ] Default values set appropriately

Association Patterns

-- Standard Association
-- fact_sales → dim_product
-- Join: fact_sales.PRODUCT_ID = dim_product.PRODUCT_ID

-- Text Association (for language-dependent text)
-- dim_product → text_product
-- Join: dim_product.PRODUCT_ID = text_product.PRODUCT_ID
--       AND text_product.LANGUAGE = $session.language

-- Hierarchy Association
-- dim_org → hier_org
-- Join: dim_org.ORG_ID = hier_org.NODE_ID

Performance Optimization

View Performance

Optimization Techniques:

  1. Filter Pushdown:
     - Apply WHERE clauses in innermost views
     - Use variables for dynamic filtering
     - Avoid functions on filter columns

  2. Projection Pushdown:
     - Select only needed columns
     - Avoid SELECT * in production views
     - Remove unused calculated columns

  3. Join Optimization:
     - Use appropriate join types (INNER when possible)
     - Order joins by selectivity
     - Consider denormalization for hot paths

  4. Aggregation:
     - Aggregate at lowest level possible
     - Use pre-aggregated views for common queries
     - Consider materialized summaries

Persistence Strategy

When to Persist:

  Always Persist:
    - Views used in multiple downstream views
    - Complex joins with >3 tables
    - Heavy transformations (string parsing, etc.)
    - External source views with latency

  Consider Persisting:
    - Views with >1M rows
    - Frequently accessed views
    - Views with calculated columns

  Avoid Persisting:
    - Simple single-table projections
    - Views with high change frequency
    - Small reference tables

Partition Strategy

Partitioning Guidelines:

  Time-Based (Most Common):
    Column: ORDER_DATE, CREATED_AT
    Granularity: YEAR, MONTH
    Use When: Historical data, time-series analysis

  Range-Based:
    Column: CUSTOMER_ID, REGION
    Granularity: Value ranges
    Use When: Known distribution, regional queries

  Best Practices:
    - Partition on frequently filtered columns
    - Balance partition sizes (avoid skew)
    - Consider query patterns
    - Monitor partition usage

Data Integration Best Practices

Replication Strategy

Full Load vs Delta:

  Use Full Load When:
    - Small tables (<100K rows)
    - No change tracking in source
    - Complete refresh acceptable
    - Initial load

  Use Delta Load When:
    - Large tables (>100K rows)
    - Source supports CDC/ODP
    - Near-real-time needed
    - Minimize transfer volume

Task Chain Patterns

Sequential Pattern:
  1. Replicate Source Tables
  2. Run Transformation Flow
  3. Refresh Materialized Views
  4. Send Notification

Parallel Pattern:
  1. Start
  2. [Parallel] Replicate Table A | Replicate Table B | Replicate Table C
  3. [Wait All]
  4. Run Aggregation
  5. End

Error Handling:
  1. Try: Main ETL Logic
  2. Catch: Log Error, Send Alert
  3. Finally: Update Status Table, Cleanup

Connection Best Practices

Connection Security:
  - Use OAuth 2.0 when available
  - Rotate credentials regularly
  - Use service accounts, not personal credentials
  - Enable connection encryption

Connection Management:
  - Name connections descriptively
  - Document connection owners
  - Test connections after changes
  - Monitor connection health

Security Best Practices

Data Access Controls

DAC Design Principles:

  1. Principle of Least Privilege:
     - Start with no access
     - Add permissions explicitly
     - Regular access reviews

  2. Reusable Controls:
     - Create generic DACs for common patterns
     - Use permission tables for flexibility
     - Avoid hardcoded values

  3. Performance:
     - Keep permission tables small
     - Index permission lookup columns
     - Test with production-like data volumes

Permission Table Pattern

-- Permission table structure
CREATE TABLE permission_region (
    USER_ID NVARCHAR(100),
    REGION_ID NVARCHAR(20),
    ACCESS_LEVEL NVARCHAR(20),  -- READ, WRITE, ADMIN
    VALID_FROM DATE,
    VALID_TO DATE
);

-- Sample permissions
INSERT INTO permission_region VALUES
('user1@company.com', 'EMEA', 'READ', '2024-01-01', '9999-12-31'),
('user1@company.com', 'APAC', 'READ', '2024-01-01', '9999-12-31'),
('user2@company.com', 'AMER', 'ADMIN', '2024-01-01', '9999-12-31');

Operational Best Practices

Monitoring

Key Metrics to Monitor:

  Storage:
    - Disk usage by space
    - Table growth trends
    - Unused object identification

  Performance:
    - Query execution times
    - Long-running statements
    - Failed executions

  Integration:
    - Flow execution status
    - Replication latency
    - Connection health

  Users:
    - Active user count
    - Query patterns
    - Failed logins

Troubleshooting Checklist

Slow Query Troubleshooting:
  1. [ ] Check View Analyzer for execution plan
  2. [ ] Verify table statistics are current
  3. [ ] Check for missing persistence
  4. [ ] Review join strategy
  5. [ ] Look for filter pushdown opportunities
  6. [ ] Check for engine mixing (row vs column store)

Failed Flow Troubleshooting:
  1. [ ] Check flow execution logs
  2. [ ] Verify source connection status
  3. [ ] Check target table constraints
  4. [ ] Review data type mappings
  5. [ ] Check for resource limits
  6. [ ] Verify credentials not expired

Documentation Standards

Object Documentation:

  Views:
    - Purpose and use case
    - Source systems
    - Refresh frequency
    - Key columns
    - Known limitations

  Flows:
    - Source and target
    - Transformation logic
    - Schedule
    - Error handling
    - Notification contacts

  Spaces:
    - Purpose
    - Team ownership
    - Data domains
    - Key objects
    - Related spaces

Anti-Patterns to Avoid

Common Mistakes

Architecture Anti-Patterns:
  - Flat structure without layers
  - Everything in one space
  - No naming conventions
  - Missing documentation

Modeling Anti-Patterns:
  - Overly complex single views
  - Missing associations
  - Wrong semantic usage
  - No key definitions

Performance Anti-Patterns:
  - No persistence strategy
  - SELECT * usage
  - Filter at wrong layer
  - Unnecessary complexity

Security Anti-Patterns:
  - Hardcoded credentials
  - Over-permissive access
  - No access reviews
  - Shared service accounts

Checklist Templates

Pre-Production Checklist

Before Go-Live:
  Code Quality:
    - [ ] All views have descriptions
    - [ ] Naming conventions followed
    - [ ] No hardcoded values
    - [ ] Error handling in flows

  Performance:
    - [ ] Performance tested with production volumes
    - [ ] Persistence strategy implemented
    - [ ] Statistics enabled

  Security:
    - [ ] Data access controls in place
    - [ ] Credentials secured
    - [ ] Access reviewed

  Operations:
    - [ ] Monitoring configured
    - [ ] Alerting set up
    - [ ] Documentation complete
    - [ ] Support contacts identified

Monthly Review Checklist

Monthly Tasks:
  - [ ] Review storage usage
  - [ ] Check for unused objects
  - [ ] Validate backup/transport
  - [ ] Review access permissions
  - [ ] Check credential expiration
  - [ ] Update documentation
  - [ ] Review performance trends
  - [ ] Update statistics