Module 12: HANA Data Provisioning

Master direct SAP HANA data provisioning using SLT for analytics and modeling.

1. HANA Architecture with SLT

graph TD
    A[ERP Source] -->|SLT| B[HANA Schema: SLTREPL]
    B --> C[Row Store Tables]
    B --> D[Column Store Tables]
    C --> E[Calculation Views]
    D --> E
    E --> F[Analytic Privileges]
    F --> G[End Users/Apps]

Data Storage Optimization

Table Type	Use Case	Performance	Example
Row Store	Small tables, OLTP	Fast writes	Config tables
Column Store	Large tables, Analytics	Fast aggregations	Transactional data
Partitioned	Very large (>100M rows)	Parallel processing	Historical data

2. Schema Design

Multi-Schema Strategy

-- Separate schemas for different purposes
CREATE SCHEMA SLTREPL;          -- Raw replicated data
CREATE SCHEMA STAGING;           -- Cleansed data
CREATE SCHEMA ANALYTICS;         -- Aggregated views
CREATE SCHEMA ARCHIVE;           -- Historical data

-- Grant permissions
GRANT SELECT ON SCHEMA SLTREPL TO _SYS_REPO WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA STAGING TO SLT_STAGING_USER;

Table Conversion

-- Convert row store to column store
ALTER TABLE SLTREPL.VBAP 
  ALTER TYPE COLUMN;

-- Add partitioning
ALTER TABLE SLTREPL.VBAP 
  PARTITION BY RANGE (ERDAT) (
    PARTITION P_2024 VALUES < '20250101',
    PARTITION P_2025 VALUES < '20260101',
    PARTITION P_OTHERS VALUES <= '99991231'
  );

-- Result:
Before: Row store, 50M records
  ├── Storage: 15 GB
  ├── Query time: 45 seconds
  └── Aggregation: Slow

After: Column store, partitioned
  ├── Storage: 3 GB (80% compression)
  ├── Query time: 2 seconds (22x faster)
  └── Aggregation: Fast

3. Calculation Views

Basic Calculation View

SAP HANA Studio → Modeler Perspective

Create: Calculation View
├── Name: CV_SALES_ANALYSIS
├── Type: Graphical
└── Data Category: CUBE

Projection_1:
├── Source: SLTREPL.VBAK
└── Output: VBELN, ERDAT, VKORG, NETWR

Projection_2:
├── Source: SLTREPL.VBAP
└── Output: VBELN, POSNR, MATNR, KWMENG, NETWR

Join:
├── Type: Inner Join
├── Condition: P1.VBELN = P2.VBELN
└── Cardinality: 1:N

Aggregation:
├── Dimensions: VKORG, MATNR, ERDAT
└── Measures: SUM(NETWR), COUNT(VBELN), SUM(KWMENG)

Advanced Calculation View with Parameters

-- Calculation view with input parameters
PARAMETERS:
├── IP_FROM_DATE (DATE)
├── IP_TO_DATE (DATE)
└── IP_SALES_ORG (NVARCHAR, Multiple)

-- Filter logic
WHERE ERDAT BETWEEN '$$IP_FROM_DATE$$' AND '$$IP_TO_DATE$$'
  AND VKORG IN ($$IP_SALES_ORG$$)

-- Usage in query
SELECT * FROM "CV_SALES_ANALYSIS" (
  'PLACEHOLDER' = ('$$IP_FROM_DATE$$', '20260101'),
  'PLACEHOLDER' = ('$$IP_TO_DATE$$', '20261231'),
  'PLACEHOLDER' = ('$$IP_SALES_ORG$$', '''DE01'', ''US01''')
);

4. Real-Time Analytics

Live Dashboard Architecture

graph LR
    A[ERP Change] -->|<1s| B[SLT]
    B -->|<1s| C[HANA Table]
    C -->|<100ms| D[Calc View]
    D -->|<500ms| E[SAC Dashboard]

Example: Real-Time Sales Dashboard

-- Calculation view for real-time sales
CREATE VIEW CV_SALES_REALTIME AS
SELECT 
  VKORG,
  VTWEG,
  TO_VARCHAR(CURRENT_DATE) as ORDER_DATE,
  COUNT(DISTINCT VBELN) as ORDER_COUNT,
  SUM(NETWR) as TOTAL_VALUE,
  AVG(NETWR) as AVG_ORDER_VALUE,
  CURRENT_TIMESTAMP as LAST_REFRESH
FROM SLTREPL.VBAK
WHERE ERDAT = CURRENT_DATE
  AND LOEVM = ''
GROUP BY VKORG, VTWEG;

-- Query refresh: Every 1 second
-- Data latency: <2 seconds from source

Performance Optimization

-- Create column store indexes
CREATE INDEX IDX_VBAK_ERDAT ON SLTREPL.VBAK (ERDAT);
CREATE INDEX IDX_VBAK_VKORG ON SLTREPL.VBAK (VKORG);

-- Pinning views to memory
ALTER CALCULATION VIEW CV_SALES_REALTIME PIN;

-- Pre-load frequently accessed data
LOAD SLTREPL.VBAK WHERE ERDAT >= ADD_DAYS(CURRENT_DATE, -30);

-- Result:
Query time: 3s → 200ms (15x faster)

5. Data Modeling Patterns

Pattern 1: Star Schema

-- Fact table
CREATE COLUMN TABLE ANALYTICS.FACT_SALES AS
SELECT 
  VBELN,
  KUNNR,
  MATNR,
  VKORG,
  ERDAT,
  NETWR,
  KWMENG
FROM SLTREPL.VBAP;

-- Dimension tables
CREATE COLUMN TABLE ANALYTICS.DIM_CUSTOMER AS
SELECT KUNNR, NAME1, ORT01, LAND1
FROM SLTREPL.KNA1;

CREATE COLUMN TABLE ANALYTICS.DIM_MATERIAL AS
SELECT MATNR, MAKTX, MTART, MATKL
FROM SLTREPL.MARA
JOIN SLTREPL.MAKT USING (MATNR);

CREATE COLUMN TABLE ANALYTICS.DIM_DATE AS
SELECT DISTINCT
  ERDAT as DATE_KEY,
  YEAR(ERDAT) as YEAR,
  MONTH(ERDAT) as MONTH,
  DAYOFYEAR(ERDAT) as DAY_OF_YEAR
FROM SLTREPL.VBAK;

Pattern 2: Data Vault

-- Hub: Business keys
CREATE COLUMN TABLE STAGING.HUB_CUSTOMER (
  CUSTOMER_KEY BIGINT GENERATED BY DEFAULT AS IDENTITY,
  KUNNR NVARCHAR(10),
  LOAD_DATE TIMESTAMP,
  RECORD_SOURCE NVARCHAR(50),
  PRIMARY KEY (CUSTOMER_KEY)
);

-- Satellite: Attributes
CREATE COLUMN TABLE STAGING.SAT_CUSTOMER_DETAIL (
  CUSTOMER_KEY BIGINT,
  LOAD_DATE TIMESTAMP,
  NAME1 NVARCHAR(40),
  ORT01 NVARCHAR(40),
  LAND1 NVARCHAR(3),
  HASH_DIFF NVARCHAR(32),
  PRIMARY KEY (CUSTOMER_KEY, LOAD_DATE)
);

-- Link: Relationships
CREATE COLUMN TABLE STAGING.LNK_SALES_ORDER (
  LINK_KEY BIGINT GENERATED BY DEFAULT AS IDENTITY,
  CUSTOMER_KEY BIGINT,
  MATERIAL_KEY BIGINT,
  ORDER_KEY BIGINT,
  LOAD_DATE TIMESTAMP,
  PRIMARY KEY (LINK_KEY)
);

6. Temporal Data Management

Tracking History

-- Create history table with temporal support
CREATE COLUMN TABLE ANALYTICS.CUSTOMER_HISTORY (
  KUNNR NVARCHAR(10),
  VALID_FROM TIMESTAMP,
  VALID_TO TIMESTAMP,
  NAME1 NVARCHAR(40),
  ORT01 NVARCHAR(40),
  LAND1 NVARCHAR(3),
  PRIMARY KEY (KUNNR, VALID_FROM)
);

-- Procedure to maintain history
CREATE PROCEDURE UPDATE_CUSTOMER_HISTORY()
AS
BEGIN
  -- Close current records
  UPDATE ANALYTICS.CUSTOMER_HISTORY
  SET VALID_TO = CURRENT_TIMESTAMP
  WHERE KUNNR IN (
    SELECT KUNNR FROM SLTREPL.KNA1
    WHERE CHANGE_FLAG = 'X'
  )
  AND VALID_TO IS NULL;
  
  -- Insert new records
  INSERT INTO ANALYTICS.CUSTOMER_HISTORY
  SELECT 
    KUNNR,
    CURRENT_TIMESTAMP as VALID_FROM,
    NULL as VALID_TO,
    NAME1,
    ORT01,
    LAND1
  FROM SLTREPL.KNA1
  WHERE CHANGE_FLAG = 'X';
  
  -- Reset change flag
  UPDATE SLTREPL.KNA1
  SET CHANGE_FLAG = ''
  WHERE CHANGE_FLAG = 'X';
END;

-- Schedule: Run every minute

Point-in-Time Query

-- Query data as of specific date
SELECT * 
FROM ANALYTICS.CUSTOMER_HISTORY
WHERE VALID_FROM <= '2026-01-15 10:30:00'
  AND (VALID_TO > '2026-01-15 10:30:00' OR VALID_TO IS NULL);

-- Result: Customer data snapshot at specific timestamp

7. Data Quality and Cleansing

Quality Checks

-- Create data quality view
CREATE VIEW STAGING.VW_DATA_QUALITY AS
SELECT 
  'SLTREPL.VBAK' as TABLE_NAME,
  COUNT(*) as TOTAL_RECORDS,
  SUM(CASE WHEN VBELN IS NULL THEN 1 ELSE 0 END) as NULL_KEYS,
  SUM(CASE WHEN NETWR < 0 THEN 1 ELSE 0 END) as NEGATIVE_VALUES,
  SUM(CASE WHEN ERDAT > CURRENT_DATE THEN 1 ELSE 0 END) as FUTURE_DATES,
  CURRENT_TIMESTAMP as CHECK_TIME
FROM SLTREPL.VBAK
UNION ALL
SELECT 
  'SLTREPL.KNA1',
  COUNT(*),
  SUM(CASE WHEN KUNNR IS NULL THEN 1 ELSE 0 END),
  0,
  0,
  CURRENT_TIMESTAMP
FROM SLTREPL.KNA1;

-- Monitor quality
SELECT * FROM STAGING.VW_DATA_QUALITY
WHERE NULL_KEYS > 0 
   OR NEGATIVE_VALUES > 0
   OR FUTURE_DATES > 0;

Data Cleansing

-- Create cleansed table
CREATE COLUMN TABLE STAGING.VBAK_CLEAN AS
SELECT 
  VBELN,
  ERDAT,
  VKORG,
  -- Cleanse negative values
  CASE WHEN NETWR < 0 THEN 0 ELSE NETWR END as NETWR,
  -- Standardize dates
  CASE 
    WHEN ERDAT > CURRENT_DATE THEN CURRENT_DATE
    WHEN ERDAT < '19000101' THEN '19000101'
    ELSE ERDAT
  END as ERDAT_CLEAN,
  -- Remove leading zeros
  LTRIM(KUNNR, '0') as KUNNR_CLEAN
FROM SLTREPL.VBAK;

8. Performance Monitoring

System Views

-- Monitor table sizes
SELECT 
  TABLE_NAME,
  RECORD_COUNT,
  MEMORY_SIZE_IN_TOTAL / 1024 / 1024 as SIZE_MB,
  DISK_SIZE / 1024 / 1024 as DISK_MB
FROM M_CS_TABLES
WHERE SCHEMA_NAME = 'SLTREPL'
ORDER BY MEMORY_SIZE_IN_TOTAL DESC;

-- Monitor query performance
SELECT 
  STATEMENT_STRING,
  AVG(EXECUTION_TIME) / 1000000 as AVG_TIME_SEC,
  COUNT(*) as EXECUTION_COUNT
FROM M_SQL_PLAN_CACHE
WHERE SCHEMA_NAME = 'SLTREPL'
GROUP BY STATEMENT_STRING
HAVING AVG(EXECUTION_TIME) > 1000000
ORDER BY AVG_TIME_SEC DESC;

Expensive Statements

-- Find slow queries
SELECT TOP 10
  STATEMENT_HASH,
  EXECUTION_COUNT,
  AVG_EXECUTION_TIME / 1000 as AVG_MS,
  MAX_EXECUTION_TIME / 1000 as MAX_MS,
  STATEMENT_STRING
FROM M_EXPENSIVE_STATEMENTS
WHERE SCHEMA_NAME = 'SLTREPL'
ORDER BY AVG_EXECUTION_TIME DESC;

9. Integration with SAP Analytics Cloud

OData Service

SAP HANA XS Advanced → Create OData Service

Service: /sap/hana/slt/sales.xsodata

Definition:
service {
  "SLTREPL"."VBAK" as "SalesOrders"
    navigates ("Items" as "SalesOrderItems");
    
  "SLTREPL"."VBAP" as "SalesOrderItems";
  
  "CV_SALES_ANALYSIS" as "SalesAnalysis"
    keys generate local "ID";
    
  association "Items" 
    principal "SalesOrders"("VBELN")
    multiplicity "1"
    dependent "SalesOrderItems"("VBELN")
    multiplicity "*";
}

URL: https://hana-server:4300/sap/hana/slt/sales.xsodata

SAC Connection

SAP Analytics Cloud → Connections → Add Connection

Type: ○ Live Data Connection
     ● SAP HANA

Connection Details:
├── Name: HANA_SLT_LIVE
├── Host: hana-server.company.com
├── Port: 30015
├── Authentication: SAML SSO
└── Default Schema: SLTREPL

Models:
├── Live Model: CV_SALES_ANALYSIS
├── Dimensions: VKORG, MATNR, ERDAT
└── Measures: SALES_VALUE, ORDER_COUNT

Refresh: Real-time (no caching)

10. Best Practices

Checklist

Use column store for large tables (>1M rows)
Partition tables by date (>100M rows)
Pin frequently used calculation views
Implement data quality checks
Set up temporal tracking for critical data
Monitor query performance weekly
Archive old data (>2 years)
Use analytic privileges for security

Performance Tips

1. Query Optimization:
   ✅ Push filters down to source tables
   ✅ Use calculation views instead of plain SQL
   ✅ Leverage column store aggregations
   ✅ Avoid SELECT *

2. Data Loading:
   ✅ Disable indexes during initial load
   ✅ Use MERGE for upserts
   ✅ Commit in batches (10K-50K records)
   ✅ Schedule delta merges off-peak

3. Memory Management:
   ✅ Unload unused tables: UNLOAD TABLE
   ✅ Set table preload: ALTER TABLE PRELOAD
   ✅ Monitor memory: M_HEAP_MEMORY

Summary

✅ HANA architecture with SLT ✅ Schema design strategies ✅ Calculation views development ✅ Real-time analytics setup ✅ Data modeling patterns (Star, Vault) ✅ Temporal data management ✅ Data quality and cleansing ✅ Performance monitoring ✅ SAP Analytics Cloud integration

Next: Module 13 - Non-SAP Source Systems

1. HANA Architecture with SLT​

Data Storage Optimization​

2. Schema Design​

Multi-Schema Strategy​

Table Conversion​

3. Calculation Views​

Basic Calculation View​

Advanced Calculation View with Parameters​

4. Real-Time Analytics​

Live Dashboard Architecture​

Example: Real-Time Sales Dashboard​

Performance Optimization​

5. Data Modeling Patterns​

Pattern 1: Star Schema​

Pattern 2: Data Vault​

6. Temporal Data Management​

Tracking History​

Point-in-Time Query​

7. Data Quality and Cleansing​

Quality Checks​

Data Cleansing​

8. Performance Monitoring​

System Views​

Expensive Statements​

9. Integration with SAP Analytics Cloud​

OData Service​

SAC Connection​

10. Best Practices​

Checklist​

Performance Tips​

Summary​