SAP HANA Calculation Views
A calculation view allows users to define more advanced slices on the data available in the SAP HANA database.
Calculation views are mainly used for analyzing operational data marts or running multidimensional reports on revenue, profitability, and so on. Calculation views consume various combinations of content data (that is, non-metadata) to model a business use case. You can classify content data as:
- Attributes: Descriptive data, such as customer ID, city, and country.
- Measures: Quantifiable data, such as revenue, quantity sold, and counters.
Calculation views simulate entities (such as customer, product, sales, and so on) and their relationships. Data visualization and analysis applications such as SAP BusinessObjects Explorer and Microsoft Office-based reporting tools consume these calculation views and help decision makers during their decision process.
You can create calculation views with layers of calculation logic, which include measures sourced from multiple source tables, or advanced SQL logic, and much more. The data sources in a calculation view can include any combination of tables and calculation views. You can create joins, unions, projections, and aggregations on data sources.
Calculation views can include measures and be used for multidimensional reporting, or can contain no measures and be used for list-type reporting. Calculation views can do the following:
- Support both OLAP and OLTP models
- Support complex expressions (for example, IF, Case, Counter)
- Support analytic privileges (for example, restricting a user for a certain cost center)
- Support SAP ER specific features (for example, client handling, language, currency conversion)
- Combine facts from multiple tables
- Support additional data processing operations, (for example, union, explicit aggregation)
- Leverage both column and row tables
SAP Web IDE provides a convenient graphical user interface for the creation of calculation views.
Data Categories Available for Calculation Views
Nodes Available for Calculation Views
Calculation View Nodes: Join
When using Join nodes, the SAP Web IDE calculation view UI provides a wide range of configuration options. Apart from the join definition itself, it is possible to change the output mappings, set up calculated columns and parameters, change the order of columns, and add filter expressions.
The Mapping feature in the Join node can be used to change the output of the node. This way, we can eliminate attributes which will not be needed anymore in subsequent nodes. For the reference architecture calculation views, for example, we only need the business keys and surrogate keys from the hub tables and the descriptive attributes from the satellite tables.
Calculation View Nodes: Star Join
Star Joins are very similar to Join nodes and offer identical configuration options.
Calculation View Nodes: Projection
Projection nodes in the calculation view UI are very similar to Projection nodes in the Flowgraph UI. Using projections we can execute transformations on attributes and rename them if required. Also, similarly to the Join node configuration options, we can set up calculated columns and parameters, change the order of columns, and add filter expressions
Calculation View Examples
In the Virtual Analytical Layer of the reference architecture, we use calculation views to join together fragmented entities of single business concepts (defined in the Business model) which - according to the Data Vault principle - are stored in hub, satellite and link tables. These calculation views are the analytical base objects that are later used to model the Data Mart. In the example, we join together the PRODUCT_H hub table, the PRODUCT_S satellite table, and all link tables that are connected to the product hub table: PRODUCT_PRODUCT_GROUP_L, PRODUCT_VENDOR_L. All joins are performed using the surrogate keys (not the business keys). As a result, we get a joined table which includes the surrogate keys of the business concept Product and the associated business concepts Product, Group, and Vendor.
In the Data Mart, the output from the VAL calculation views is used for dimensions which are joined together with a central fact table. Here, we use a special join called a Star Join. The result is called a data cube. It differs from calculation views in the Virtual Analytical Layer; the Data Mart is exposed to end users (for example to be used in a Business Intelligence frontend).