Exploring a Calculation View in SAP HANA

Modeling in SAP HANA is supported by a graphical browser-based interface with the name SAP Web IDE for SAP HANA.

You need to obtain the URL from your administrator to be able to launch the SAP Web IDE for SAP HANA. The URL includes the host and port where SAP HANA database and the SAP Web IDE for SAP HANA is installed. Google Chrome is recommended as the browser. You will need an SAP HANA logon with some privileges to get started. This is technically a different user from SAP BW/4HANA.

The Development view and the Database Explorer view are the main work environments in the SAP Web IDE for SAP HANA. You toggle between these views using the buttons on the left of the interface.

To get started with modeling you first need to create a Project in the Development view of SAP Web IDE for SAP HANA. To support the isolation of development environment, SAP Web IDE for SAP HANA allows developers to use dedicated spaces in XS Advanced for building and running their projects. A project needs to be assigned to a Space. Within your project you then create a Module to sub-divide the development artifacts. There are a number of different types of modules provided by SAP to organize your development objects. Modelers usually only work with HDB (HANA Database) module type. Within a module you develop source files that are allowed for the type of module. For example, in a HDB module you create database artifacts such as calculation views, procedures and functions.

Development in SAP Web IDE for SAP HANA is completely file-based, which means that each source artifact is a simple file (identified with an extension) that is easy to export and import within and also across SAP HANA.

Each file you create has its own extension, such as .hdbfunction or .hdbcalculationview, which makes it easy to identify its type. This is also how the SAP Web IDE for SAP HANA knows which editor to open for each type of artifact. Certain files types can be opened in only a text editor or a graphical editor. Some files type can be opened in both.

In order to generate a run-time object from the source file we need to execute a build. In SAP BW/4HANA this would be the equivalent of an Activation.

The Database Explorer view of SAP Web IDE for SAP HANA is used to explore all types of catalog objects such as tables, views, procedures and functions. The run-time objects that were created with the Development view can also be displayed here.

In case you are wondering why we didn't cover SAP HANA Studio or SAP HANA Web-Based Development Workbench we will briefly cover the reasons below:

SAP HANA was launched in 2011 and since then has undergone significant developments. A key development was the move away from XS Classic to XS Advanced which uses the HANA Deployment Infrastructure (HDI). Along with that came the introduction of new tooling which had an impact on calculation view modeling.

1. SAP HANA Studio is a client-side installation based on Eclipse. This is the same interface for modeling in SAP BW/4HANA where we use the special Eclipse add-in called SAP BW Modeling Tools, (BWMT). It is also the same interface used for ABAP development on-premise where we use the add-in ABAP Development Tools, (ADT). SAP HANA Studio can also be used for SAP HANA database administration, but modeling calculation views should only be done using the new SAP Web IDE. Calculation View modeling in SAP HANA Studio is still possible, but the interface only supports the weaker (XS Classic) version of the calculation views.
2. SAP HANA Web-Based Development Workbench provides a web-based interface to build development artifacts in the SAP HANA environment. It was introduced with SAP HANA 1.0 and is based on the obsolete SAP HANA XS, also known as SAP HANA XS Classic. This interface never reached the functional scope of SAP HANA Studio and was never widely in use. It was meant to provide the SAP HANA Studio developer with a lightweight browser-based development tool to replace SAP HANA Studio. Again, this should not be used to develop calculation views because this interface only supports the weaker XS Classic version of the calculation view.
3. SAP Web IDE for SAP HANA is a browser-based, integrated development environment (IDE) and is the recommended tool for native SAP HANA Modeling and database administration. It was introduced with SAP HANA 2.0 and works with the replacement of XS Classic which is called SAP HANA Extended Application Services Advanced (XS Advanced or simply XSA). The SAP Web IDE is the successor of the two UIs mentioned above. SAP Web IDE supports the development of the more powerful version of the calculation view.

In this learning journey, we follow SAP's recommendation and use the SAP Web IDE for SAP HANA to manage the SAP HANA database and for all native SAP HANA Modeling tasks.

Launch the next demo to explore SAP Web IDE for SAP HANA:

SAP BW has always provided a comprehensive modeling environment to develop virtual and physical data models. SAP BW/4HANA continues with that provision. As SAP BW/4HANA runs on the SAP HANA database, this means the powerful, native SAP HANA modeling environment is available to SAP BW/4HANA developers.

Unlike many databases, SAP HANA provides much more than an SQL console to create a views over tables. The SAP HANA modeling environment provides graphical development tools and different types of data models that can process many types of data in any location.

This means a SAP BW/4HANA expert who will work on an SAP BW/4HANA project should immediately develop SAP HANA native modeling skills. This is because SAP BW/4HANA models can consume HANA models and vice versa. This is called mixed modeling. It simply means we combine both types of models to create the most powerful hybrid solution utilizing the best of SAP BW/4HANA and the best of SAP HANA capabilities.

It is very important to recognize that native SAP HANA modeling does not replace SAP BW/4HANA modeling. Although there are quite a few conceptual overlaps between both, each environment also has its unique capabilities.

The key modeling object in SAP HANA is called a calculation view. To be able to differentiate these objects to the obsolete types of calculation views of SAP HANA 1.0, the data models developed and maintained by the SAP Web IDE for SAP HANA are also called HDI Calculation Views or XSA Calculation Views. We only focus on these newer models in this learning journey.

There are different types of calculation views. The settings are chosen when you first create the calculation view. The four data category types of calculation views are as follows:

• Dimension

• Cube

• Cube with Star Join

• SQL Access Only

The purpose of a calculation view with the data category Dimension, is to define a group of related attributes, such as material, material color, material weight, and material price. So it is similar to an InfoObject Characteristic in SAP BW/4HANA. Although this type of calculation view can be directly consumed by SQL, it is most likely to be found as a reusable component to support a calculation view of the data category Cube with Star Join.

Dimension data category type calculation views cannot contain measures, only attributes are allowed. This means that any numerical field such as days vacation remaining can only be defined as an attribute. This is still useful to provide supporting information about a main entity, in this case an employee. Because the field cannot be defined as a measure, you cannot calculate on it or aggregate the values.

Reporting tools usually cannot directly access calculation views of data category Dimension.

It might be helpful to think of calculation views of data category dimension as the central master data views. You define them once and reuse them many times in other views.

To get started with calculation views of type Dimension, you need to set the data category to DIMENSION.

You then proceed to define the source tables and the fields that should be exposed from the tables, define the joins or unions if needed, and add filters. It is also possible to define additional derived attributes. An example of this could be a new column to generate a weight category based on a range of weights, using an IF expression.

In the calculation view, you are able to rename any source columns to be meaningful to the business user. Remember that the column names originate from the database tables, and these names can often be meaningless to business users.

The next type of calculation view is the data category Cube. This type of calculation view is used to define a data set comprised of attributes and measures. This means they are ideal for transactional data modeling. With measures you are able to define aggregation behavior so that business users can slice and dice the data in an ad-hoc manner. It should be noted that a Cube calculation view is not a star schema as there are no dimensions included.

Reporting tools can directly access this type of calculation view. These views can also be accessed via SQL Select statements.

To get started, set the data category to CUBE.

Do not select the Star Join flag.

You then select the table, or tables, which are to be included in the model. Typically, you choose a transaction table so that you have columns from which you can define attributes and measures. It is possible to include more than one table. For example, you may need to include a header and a line item table to form the complete picture of a sales transaction. In this case, you simply join the tables using a JOIN node. You can also merge transaction tables using a UNION node.

Then, select the columns from the tables that are to be exposed. You can optionally set filters, and define additional calculated columns.

Then, rename any columns to provide meaningful names to the business user.

Now we come to the most powerful of all the calculation views: this is still based on the Cube type, but with an additional setting – star join. This is how we model a star schema. The foundation of this view is a cube type of calculation view, but we now join one or more dimension calculation views to the model. This provides a very powerful and flexible data model that enables drill-down, slice and dice analysis over any combination of attributes and measures. Adding multiple dimensions dramatically enhances the analysis possibilities versus the simple cube type calculation views.

To get started, make sure that you set the data category to CUBE but remember to select the Star Join flag.

First, select the transaction table(s) and create joins to combine the transaction tables if required. Then, choose the columns to expose, set any filters, and create any calculated columns. What you are doing up to this point is forming a fact table that is used as the center of the star schema.

The next step is to form the star schema by linking suitable calculation views of data category DIMENSION to the fact table.

Finally, improve the names of any columns by using the rename function in the Semantic node so that it becomes fully business user-facing.

The Private tab shows the attributes and measures from the transaction data.

The Shared tab shows the attributes from the Calculation View – Data Category Dimension used in the Calculation View – Data Category Cube with Star Join.

Calculation views are usually modeled on top of each other in a stack with each calculation view consuming lower level calculation views. The basic idea is to develop reusable views. Only the lower calculation views usually consume tables. The very top calculation view is the only view that needs to be visible to the consuming application or reporting tool.

SAP has provided a general purpose calculation view that has the data category SQL ACCESS ONLY. This type of view is simple and is not meant for multidimensional modeling. It produces flattened result sets that can contain attributes and measures. It is not a star schema and cannot contain dimensions. It is not visible to reporting tools and is consumed only by SQL or by other calculation views. Unlike other calculation views, this view does not expose its metadata to the consuming applications. It might help to think of this type of view as an internal view that is only meant to be used within a stack.

Calculation views can be consumed with any type of reporting tool, on-premise, and cloud-based. For example, Analysis for Microsoft Office, SAP Web Intelligence, and SAP Analytics Cloud.

Launch the next demo to explore SAP HANA calculation views using the SAP Web IDE:

SAP HANA 1.0 was launched in 2010 and underwent a major redesign in 2016 to becomes SAP HANA 2.0. You might assume that we can forget about the older version of SAP HANA and focus on the latest version. In the context of SAP BW/4HANA we cannot do that. SAP BW/4HANA makes use of SAP HANA components from the old and new versions of SAP HANA, so we should cover the key differences.

From its introduction in 2010, the SAP HANA platform has changed a lot and grown into the mature basis of SAP's current solution portfolio. Release 1.0 received the last support package SPS12 in 05/2016 and is out of general maintenance since 06/2021. In 2016, the more powerful SAP HANA platform 2.0 was introduced which has long time supported releases in parallel to standard releases.

SAP HANA 2.0 innovates the SAP HANA platform in many terms, including its modeling concepts. Modeling is now based on a modeling framework that provides new user interfaces, artifacts, and functions. Some of the key innovations are:

SAP HANA 1.0 is based on the XS Classic native source code repository and developments are structured using Packages, Delivery Units. Database objects are managed using simple schemas with a single schema used for all Calculation Views run time objects. SAP HANA 2.0 XS Advanced leverages the open GitHub services as its source code repository and developments are structured using projects and folders and the whole concept is called the HANA Deployment Infrastructure (HDI) which uses containers to isolate the database objects for each application.

In the past, several development tools were used for specific HANA development scenarios (Studio, web-based development workbench). To unify the development experience on SAP HANA, a new development tool has been introduced that integrates these former separate development tools into one. This tool is called SAP Web IDE for SAP HANA and runs in the XS Advanced (XSA) environment of SAP HANA 2.0. This means that SAP HANA Web IDE replaces the Eclipse-based HANA Studio perspectives for HANA native development. However, this does not apply to SAP BW/4HANA modeling: These objects are still developed and maintained in SAP HANA Studio / BW Modeling Tools, and there are no plans to change this in the future.

SAP HANA 2.0 only supports calculation views as modeling artifacts. They encompass all the functionality of the various types of views (Attribute View, Analytic View, Calculation View, scripted Calculation View) in XS Classic, so nothing is lost. The result is a simpler approach when we only have one type of modeling object. It also means optimization by the SQL engine is much more efficient when all modeling rules are built into one type of artifact. A HANA Studio migration tool is provided that supports to conversion of calculation views from XSC, HANA 1.0 to HDI calculation views XSA, HANA 2.0.

The calculation views of SAP HANA 2.0 are more powerful than their predecessors. There are additional modeling functions to support advanced method to combine data, for example, intersect and minus operators. There are powerful ranking features.

One of the most powerful features of the calculation view is that they can easily include custom SQL code to open up a huge range of opportunities for advanced processing.

For compatibility reasons, SAP HANA 2.0 includes all development and run-time support for SAP HANA 1.0. For this reason all artifacts that were developed in SAP HANA 1.0 can still be used. For example, you could continue to create new calculation views using the SAP HANA Studio even in a SAP HANA 2.0 system, and these would be deployed to the old XS classic environment. However, the recommendation is to leverage the new XS advanced environment and design applications based on HDI calculation views.

Calculation views created in the old tools (SAP HANA Studio or web-based Development Workbench) cannot be maintained in the SAP Web IDE for SAP HANA and vice versa. This is another reason to move to the new environment rather than have teams working across two development environments using different tools. There are built-in migration tools in SAP HANA Studio to move your developments over from the old environment to the new one.