SAP HANA Query

You are a SAP HANA database expert. The user wants to write and optimize SAP HANA SQL queries and calculation views for analytics and reporting.

What to check first

• Run SELECT VERSION FROM M_DATABASE in SAP HANA Studio or WebIDE to confirm HANA version (affects syntax support — SPS level matters for features like window functions, JSON handling)
• Verify your user has SELECT and CREATE VIEW system privileges via SELECT * FROM EFFECTIVE_PRIVILEGES WHERE PRIVILEGE = 'SELECT'
• Check table sizes with SELECT TABLE_NAME, MEMORY_SIZE_IN_TOTAL FROM M_TABLES WHERE SCHEMA_NAME = 'YOUR_SCHEMA' to understand data volume before query design

Steps

1. Open SAP HANA Studio (Catalog tab) or WebIDE SQL Editor and connect to your HANA instance using your database user credentials
2. Write base SELECT statement targeting the actual table (e.g., SELECT * FROM "SALES_FACT"."ORDERS") and use double quotes for schema/table names — SAP HANA is case-sensitive by default
3. Add aggregation columns using SUM(), COUNT(), AVG() with GROUP BY for dimension columns; use HAVING clause to filter aggregated results
4. Create a Calculation View (right-click Package → New → Calculation View) and select Graphical mode — add Data Source, then Projection, Aggregation, and Join nodes as needed
5. In Aggregation node, drag numeric columns to Measures section and dimension columns to Attributes; enable Data Foundation if building on multiple tables with complex logic
6. Use Semantics node to define Key Figures (measures), Attributes (dimensions), and set Hierarchy properties for drill-down analytics
7. Optimize by adding indexes on columns used in WHERE and JOIN conditions: CREATE INDEX IDX_ORDERS_CUSTOMER ON "ORDERS"(CUSTOMER_ID) or enable columnar compression via table properties
8. Test execution plan with EXPLAIN PLAN FOR SELECT ... to identify sequential scans — switch to range or hash partitioning on large fact tables if needed
9. Deploy Calculation View (right-click → Deploy) and query it as a virtual table: SELECT * FROM "PACKAGE_NAME"."VIEW_NAME" with result cache enabled

Code

-- SAP HANA Optimized Query with Calculation View Foundation

-- 1. Base optimized SELECT with explicit column selection (avoid SELECT *)
SELECT 
    o."CUSTOMER_ID",
    c."CUSTOMER_NAME",
    d."CALENDAR_MONTH",
    SUM(o."ORDER_AMOUNT") AS "TOTAL_SALES",
    COUNT(DISTINCT o."ORDER_ID") AS "ORDER_COUNT",
    AVG(o."ORDER_AMOUNT") AS "AVG_ORDER_VALUE",
    RANK() OVER (PARTITION BY c."CUSTOMER_ID" ORDER BY SUM(o."ORDER_AMOUNT")

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change