CDS view row-level security implementation impact on cost accounting reports

ERP SAP S/4HANA
, , , , , , , ,
1

We implemented CDS view security with row-level filtering for cost accounting reports in S/4HANA 2020 and seeing significant query optimization challenges. The goal was restricting cost center visibility by organizational hierarchy.

CDS view security uses ABAP CDS DCL (Data Control Language) to filter rows based on user’s assigned cost centers. Works perfectly from security perspective - users only see authorized data. However, reporting performance degraded 3-4x compared to unrestricted queries.

The row-level filtering adds WHERE clauses to every query that reference authorization tables (T77UA for org assignment). Database indexing on HANA doesn’t seem optimized for these dynamic filters. Execution plans show full table scans on cost center master data despite having indexes on KOSTL field.

Performance monitoring shows the issue: queries that took 2-3 seconds now take 8-12 seconds. For real-time dashboards, this is unacceptable. We’ve tried various query optimization techniques (additional indexes, view annotations, buffer settings) with minimal improvement.

Looking for experiences with CDS view row-level security at scale. How do you balance security requirements with query performance?

system 2

CDS DCL Performance at Scale — Cost Center Authorization Filtering

The 3-4x degradation pattern is well-documented with ABAP CDS DCL access controls when the access condition joins against authorization-relevant tables like T77UA (or similar org assignment stores) on every query execution. A few diagnostic and remediation angles:

Root Cause: Access Condition Join Pushdown

HANA’s optimizer sometimes cannot push the DCL-generated predicate efficiently when the access control join is non-deterministic per session. Check the PlanViz execution plan for:

  • ROW_ID_TABLE_SCAN on cost center dimension tables
  • Late filtering (filter applied post-join rather than pre-join)
  • Missing join cardinality hints in the CDS annotation layer

Optimization Approaches

1. Materialize authorization sets Rather than joining T77UA dynamically, pre-compute user-to-cost-center mappings into a custom authorization cache table (e.g., a Z-table refreshed via batch). Reference this in your DCL instead. Eliminates repeated org hierarchy traversal at query time.

2. Restructure the DCL access condition Ensure your @MappingRole condition uses IN list patterns rather than correlated subqueries. Correlated subqueries in DCL prevent predicate pushdown (verify in your version).

3. HANA Calculation Scenario partitioning If these reports run via Embedded Analytics / CDS-based ODP, consider partitioning the underlying column store by KOKRS (controlling area) before cost center filtering kicks in. Reduces scan volume before DCL filter applies.

4. Virtual Data Model layering Separate the authorization-sensitive CDS view from the analytical CDS view. Apply DCL only at the consumption layer, not on base/interface views — avoids redundant filter application at every VDM layer.

5. Buffer access control results @AccessControl.authorizationCheck: #NOT_REQUIRED on intermediate views, with explicit DCL only at the top-level consumption view, reduces repeated authorization evaluation.

Licensing Consideration

If performance pressure leads toward SAP Datasphere or BW/4HANA offloading for high-volume cost accounting analytics, those carry separate licensing implications. Similarly, SAP HANA native scenarios with row-level security via analytic privileges behave differently from CDS DCL and may require additional entitlements.

Verify with vendor for current pricing.

This draft is based on general SAP S/4HANA knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

CDS DCL adds authorization joins that HANA optimizer struggles with. We solved this by materializing user-cost center mappings in a separate calculation view with full replication. This turns the authorization check from a complex join into a simple IN clause against memory-resident table.

4

Have you considered pre-calculating authorized result sets? We implemented a batch job that runs nightly, generates cost center visibility sets per user, stores in custom tables. CDS views then join to these pre-calculated tables instead of doing real-time authorization resolution. Trades freshness for performance but works for daily/weekly reports.

5

The problem is HANA’s optimizer doesn’t push down authorization filters early enough in execution plan. Check SQL trace - authorization joins probably happen after aggregation instead of before. Try restructuring your CDS view to use INNER JOIN instead of LEFT OUTER JOIN for authorization tables. This gives optimizer better statistics for join order decisions.

6

Tried the INNER JOIN approach - helped marginally (10% improvement). The fundamental issue is T77UA table has 500K+ rows for our org structure and gets joined to every query. Even with indexes, that’s expensive at scale.

7

Use CDS view with parameters for authorization context instead of DCL. Pass user’s authorized cost centers as comma-separated parameter from ABAP layer. This eliminates the authorization join entirely - HANA can optimize the IN clause much better than complex joins. Requires wrapper logic in ABAP but performance gain is substantial.

8

Database indexing alone won’t solve this - the issue is cardinality estimation. HANA optimizer assumes authorization filters are non-selective (high cardinality) because it can’t predict user context at optimization time. Solution: implement authorization-aware column store hints. Add annotation @Analytics.dataExtraction.enabled: true and use authorization session variables. This lets HANA cache execution plans per authorization profile instead of recompiling for each user. We reduced query time from 15s to 3s with this approach. Also consider partitioning your cost center fact tables by fiscal year - reduces scan scope dramatically for typical period-based reports.

9

This is a common architectural challenge when implementing CDS view security at enterprise scale. Let me share a comprehensive performance optimization strategy:

CDS View Security Architecture: CDS DCL (Data Control Language) provides elegant row-level filtering but introduces authorization joins that HANA’s cost-based optimizer struggles with. The core issue: authorization resolution happens at query runtime, preventing effective query plan caching and index utilization. Your 3-4x performance degradation is typical for naive DCL implementations on large datasets.

Row-Level Filtering Optimization: The T77UA organizational assignment table is your bottleneck. With 500K+ rows, every authorization join becomes expensive. Implement a three-tier optimization strategy:

  1. Authorization Context Caching: Create a custom HANA table (Z_USER_COSTCTR_CACHE) that stores flattened user-to-cost-center mappings. Refresh this daily via background job. Your CDS view joins to this smaller, indexed table instead of T77UA. This reduces authorization join from 500K rows to ~50-100 rows per user.

  2. Partition-Aware Design: Restructure your cost accounting CDS view to leverage HANA partitioning. Partition fact tables by fiscal period (monthly or quarterly). Add partition pruning hints in your CDS view using @AbapCatalog.sqlViewAppendName. This ensures queries only scan relevant partitions.

  3. Authorization-Aware Indexes: Create composite indexes that combine authorization fields with query filters. For example: INDEX on (KOKRS, KOSTL, GJAHR, POPER) where KOKRS and KOSTL are authorization fields. This allows index-only scans for typical period-based reports.

Query Optimization Techniques: Your database indexing on KOSTL alone isn’t sufficient. HANA’s optimizer needs multi-column statistics that span authorization and business dimensions. Run HANA statistics update on your fact tables with explicit column groups: KOSTL + GJAHR + POPER. This improves cardinality estimation for authorization-filtered queries.

Implement CDS view annotations that guide HANA optimizer:


@Analytics.query: true
@Analytics.dataCategory: #CUBE
@ClientHandling.algorithm: #SESSION_VARIABLE

These annotations enable result set caching and session-based optimization.

Performance Monitoring Deep Dive: Your 8-12 second queries suggest multiple issues. Analyze HANA SQL trace (transaction SQLM) to identify:

  • Join order: Authorization joins should happen first (most selective)
  • Aggregation timing: Push aggregations after authorization filters
  • Column store unloads: Ensure frequently accessed columns stay memory-resident

Use HANA’s Plan Viz (EXPLAIN PLAN) to verify authorization filters are pushed down before expensive operations like aggregation or sorting.

Database Indexing Strategy: Create specialized indexes for authorization patterns:

  1. Covering index: (USER_ID, KOSTL, VALID_FROM, VALID_TO) on Z_USER_COSTCTR_CACHE
  2. Composite index: (KOSTL, BUKRS, GJAHR) on cost center fact table
  3. Partition-local indexes on time-series columns

Avoid generic indexes on single columns - they’re ineffective for authorization-filtered queries.

Balancing Security and Performance: The materialization approach (pre-calculating authorized result sets) works for static reports but breaks real-time requirements. Instead, implement hybrid strategy:

  • Real-time dashboards: Use parameterized CDS views that accept pre-resolved cost center lists from ABAP layer. ABAP code resolves authorization once per session, passes list to CDS via parameters. This eliminates authorization joins entirely for dashboard queries.

  • Batch reports: Use full DCL with optimized authorization cache. Nightly refresh ensures authorization data is current.

  • Ad-hoc queries: Implement query result caching at application layer (SAP Gateway cache, Redis). Cache authorized result sets for 15-30 minutes based on user + query parameters.

Alternative Architecture - Authorization Views: Consider splitting your CDS view into two layers:

  1. Base view (no DCL): Contains all data, unrestricted
  2. Authorization view (with DCL): Thin wrapper that applies row-level security

Application layer can choose which view to use based on user context. Power users with broad authorization use base view for maximum performance. Restricted users use authorization view.

Scaling Considerations: For organizations with complex hierarchies (>100K cost centers), even optimized DCL hits limits. At that scale, consider authorization-aware partitioning: physically separate data by organizational unit (holding company, region) and route users to appropriate HANA tenant. This eliminates cross-tenant authorization checks entirely.

Implement these optimizations iteratively, measuring impact at each step. Target 2-3 second query response for typical dashboard queries - achievable with proper authorization caching and index strategy.

expertops 11

Your 3-4x performance degradation is typical for naive DCL implementations on large datasets.