SAP HANA is an in-memory relational database and application platform, and one of its core capabilities is data modeling to deliver performance-optimized reporting and analytics. One of the data modeling components in SAP HANA is the “Calculation View.

Overview Calculation View in SAP HANA

1. Purpose:
   • Calculation Views are used to build complex calculations that involve measures and use them in a multi-dimensional reporting tool.
   • They can be seen as the final modeling object, which will consume one or more “Analytic” or “Attribute” views to get data and can even combine data from multiple sources.
2. Types:
   • Graphical Calculation Views: These are designed using the SAP HANA Studio’s graphical interface. Most of the data modeling needs can be met using graphical calculation views.
   • Scripted Calculation Views: These are written using SQLScript, SAP HANA’s development language. They offer a more flexible approach but generally are used when the graphical approach doesn’t meet specific requirements.
3. Features:
   • Projection Nodes: Allow you to select specific columns and filter data.
   • Join Nodes: Enable joining of tables or views.
   • Aggregation Nodes: Help to aggregate data, similar to the “GROUP BY” clause in SQL.
   • Rank Nodes: Used to rank data based on a measure.
   • Union Nodes: Allow the union of two result sets.
   • Input Parameters and Variables: They can be used to influence the data retrieval operations at runtime.
4. Layered Approach:
   • SAP recommends a layered architecture when creating calculation views. This is often referred to as the “L” model which consists of the “Reuse“, “Virtual Data Model“, and “Consumption” layers. This approach enhances readability, reusability, and maintainability.
5. Performance:
   • Calculation views benefit from SAP HANA’s columnar store and in-memory capabilities, leading to real-time, high-speed data retrieval. However, it’s crucial to design them properly to avoid potential performance bottlenecks.
6. Deployment:
   • Calculation views can be consumed by various SAP (like SAP Lumira, SAP Business one Objects) and non-SAP applications.
7. Versioning:
   • Starting from HANA 2.0 SPS 01, SAP internships introduced the concept of “Calculation View Versions.” This allows developers to work on a new version of a calculation view without impacting the active version being used by applications.

Graphical Calculations View In SAP HANA

A Calculation View stands as a pivotal information model within the SAP HANA migration database, facilitating intricate data segmentations. Notably, its versatility encompasses functionalities embedded within attribute and analytic views, extending to sophisticated analytic capacities.

Opt for Calculation Views in scenarios demanding nuanced data modeling techniques beyond the capabilities of analytic and attribute views. They permit layers of intricate calculation logic encompassing measures culled from an array of source tables, intricate SQL logic, and more. Their composition can integrate tables, column views, attribute views, and analytic views, enabling operations like joins, unions, projections, and aggregation on the data sources.

Distinguishing features of Calculation Views include:

• Facilitating both OLAP and OLTP architectures.
• Enabling advanced expressions (e.g., IF, Case, Counter functions).
• Seamless integration with Analytic views, Attribute views, and other Calculation Views (both Graphical and Scripted).
• SAP Implementation analytic privileges, such as user-based restrictions.
• Accommodating SAP ERP-specific functions (e.g., client handling, multilingual support, currency conversion).
• Merging facts across diverse tables.
• Executing enhanced data operations, including union operations and explicit aggregation.
• Employing niche languages like R-Lang.
• Compatibility with both columnar and row storage structures.

Create Calculated Columns

Create Unions

Creating a Union Node with Output Columns in the Graphical Calculation View:

1. Initiate the Graphical Calculation View:
   • Open the desired graphical calculation view in the editor.
2. Insert a Union Node:
   • Drag a union node from the tools palette and drop it into the editor.
3. Add Data Sources to the Union Node:
   • Select the union node.
   • Right-click and select ‘Add Objects‘.
   • In the ‘Find Data Sources’ dialog box, type the data source name and select it from the displayed list.
   • Click ‘OK’.
4. Define Output Columns:
   • In the ‘Details’ pane, highlight the desired columns.
   • Right-click and select ‘Add To Output‘.
     • Note: For including all columns, right-click the data source and choose ‘Add All To Output’.
5. Assign Constant Values to Columns:
   • This denotes source columns with set values in the output.
   • Under the ‘Target‘ section, choose an output column.
   • Right-click and select ‘Manage Mappings‘.
   • In the dialog, leave the ‘Source Column’ field blank.
   • Input your constant in the ‘Constant Value’ field.
   • Click ‘OK’.
6. Create a Constant Output Column:
   • In the ‘Target‘ section, click ‘+’.
   • Fill in the name and data type in the ‘Create Target‘ dialog box for the new output column.
   • Click ‘OK’.
     • Note: The default value for this constant output column is null.

Constant Column

Suppose you have two tables, ‘Actual Sales’ and ‘Planned Sales’, both representing product sales. You wish to merge this data for comparison but also want to differentiate the sources. You can achieve this by creating a union of the tables and introducing a constant column, like ‘Actual Planned Indicator’, which uses ‘A’ for Actual Sales and ‘P’ for Planned Sales.

Tables:

• Actual Sales
  • Sales: 5000, Product: A1
  • Sales: 2000, Product: B1
• Planned Sales
  • Sales: 3000, Product: A1
  • Sales: 6000, Product: B1

Resultant Union View:

• Indicator: A, Sales: 5000, Product: A1
• Indicator: P, Sales: 3000, Product: A1
• Indicator: A, Sales: 2000, Product: B1
• Indicator: P, Sales: 6000, Product: B1

This method provides a unified view while preserving the data source’s identity.

Calculation Views can either:

• Graphical Calculation Views: Constructed via a graphical interface.
• Script-Based Calculation Views: Defined using SQL scripting.

Script-Based Calculation Views

To create information views leveraging specific SQL (e.g., window functions) or predictive functions (e.g., R-Lang), use script-based calculation views. Mastery in SQL scripting and understanding of data model behaviors and optimizations are essential.

Steps:

1. Setup:
   • Open SAP HANA studio.
   • Navigate to SAP HANA System view > content node.
   • Choose the package for the new calculation view. Right-click > New > Calculation View.
   • Enter the name and description.
   • From the ‘Type’ dropdown, pick ‘SQL Script’.
   • Decide the naming convention sensitivity for output parameters by setting ‘Parameter Case Sensitive’ to either True or False.
   • Click ‘Finish’.
2. Default Schema Selection:
   • Click on the Semantics node > View Properties tab.
   • From ‘Default Schema’ dropdown, choose the default schema.
     • Note: Without a default schema, use fully qualified object names.
3. Define Output Structure:
   • Select ‘SQL Script’ node within the Semantics node.
     • Note: Avoid the ‘IN’ function for dynamic value lists in SQL script. Use ‘APPLY_FILTER’ instead.
   • In the Output pane, click ‘Create Target’ to define the output parameters.
   • To add multiple columns from existing views/tables:
     • Select ‘New > Add Columns From’ in the Output pane.
     • Input the object name containing desired columns.
     • Choose the relevant columns or objects.
     • Note: Rename any duplicate column names in the Target pane before adding them to avoid errors.
4. Scripting:
   • Compose the SQL Script statements to populate the output columns.
   • For quick SQL statements corresponding to information views, drag them from the navigator pane to the SQL editor.
   • Further Reading: For input parameters details, refer to SAP Note 2035113.
5. Activation:
   • In SAP HANA Modeler perspective:
     • ‘Save and Activate’ for the current view.
     • ‘Save and Activate All’ for the current view along with associated objects.
     • Note: Activation can also be done by right-clicking the view in SAP HANA Systems view.
   • In SAP HANA training Development perspective:
     • Right-click the desired object in Project Explorer > Team > Activate.
6. Post-Script Steps:
   • For content assist functionalities, use Ctrl + Space in the SQL Console.
     • Reference: SAP HANA SQLScript Reference.
   • In the ‘Select Change’ dialog, create or select an existing change ID for your modifications.
   • For detailed insights on change assignments, consult the SAP ECC To SAP HANA Change Recording in the SAP HANA Developer Guide.
   • Click ‘Finish’.

This provides a structured guide for creating and managing script-based calculation views in SAP HANA.

• Click the Semantics tab.
• Choose a value from the Semantic Type dropdown.

Calculation View Instantiation Mechanism

The core of SAP HANA, the Calculation Engine, is architected for unparalleled performance, employing an assortment of optimization methodologies. It’s imperative to recognize that the Calculation Engine occasionally deviates from conventional relational patterns, presenting unexpected behavior for typical SQL users.

A salient feature of SAP HANA’s Calculation Engine is the instantiation process. During instantiation, the Calculation Engine refines the calculation view, molding it into a model aligned with the query’s specifications. For instance, a calculation view encompassing Field_1 and Field_2 might be streamlined to only include Field_1 if the executed query exclusively demands this field.

This dynamic refinement of the calculation view transpires in real-time during query execution. Additional optimization techniques comprise pruning redundant joins. Further details on the instantiation process can be delved into in subsequent sections.

Also check my guide on on what is SAP Implementation

Technical Conclusion

In conclusion, Calculation Views in SAP HANA are instrumental in leveraging the power of HANA’s in-memory and computational capabilities. Properly designed calculation views can provide businesses with insights into their data at speeds previously unattainable in traditional databases. However, like all tools, they require an understanding of their features and best practices to use them effectively.