Developing Data Models with SAP HANA Cloud

In a legacy application, the role of the database is to simply store and provide raw data. There is usually very little, or even no processing of the data in the database.

The raw data is sent from the database directly to the application. The application then begins to process the data by combining it, aggregating it, and performing calculations in order to generate something meaningful.

We can find ourselves moving a lot of raw data between the database and the application. When we move raw data to the application layer, we make the application code very complex. This is because it has to deal with the data processing and modeling tasks as well as managing all of the other process flow control, business logic, User Interface (UI) operations, integrating data from multiple sources, and so on.

With SAP HANA Cloud, we can build a sophisticated modeling layer on top of the database tables, right inside the database. This means we can first process the raw data and turn it into something meaningful in the database before passing it to the application for consumption.

With SAP HANA Cloud, the main type of modeling object is called a calculation view. A calculation view can combine data from multiple tables or other views, apply filters, calculate new data and aggregate the data to produce summarizations at any level. The calculation views are developed in SAP HANA Cloud using easy-to-use graphical modeling tools, and are stored in SAP HANA Cloud database. A calculation view, when generated into a run-time object, creates a column view that can be processed by SQL.

So, instead of the application including complex code to process the data, the application now calls the required calculation views and the processing is pushed down to SAP HANA Cloud database. This is efficient in the following ways:

• The application code is simplified, as it does not have to deal with many data processing tasks. These tasks are pushed down to SAP HANA Cloud database where in-memory processing takes place.

• The processing on the data is carried out where the data resides, so we do not have to move raw data from the database to the application. We only move the results of the data processing to the application.

• The calculation views can be reused in multiple applications so we avoid redundancy.

In traditional applications, there is a high degree of redundancy in the application code. Developers find themselves continually creating the same code to process data.

When dealing with highly normalized database models, such as those used with SAP Business Suite, there can be many individual tables that need to be called and combined using joins. These joins can usually be pushed down in most databases. However, SAP HANA Cloud database goes beyond helping with just the table joins. SAP HANA Cloud database can take over all data processing tasks from the application. SAP HANA Cloud database takes care of complex calculations and data flow logic, including executing aggregations and disaggregation.

SAP HANA Cloud calculation views can contain dynamic placeholders. This means that the applications can pass variables down to the calculation views from a response to user prompt. Many of the calculation views can also call procedures that have input parameters.

Calculation views can consume the results of other calculation views. This encourages a high degree of modularization and reuse of calculation views. A calculation view is usually part of a stack of calculation views that make up a complete data model. It is good practice to design a calculation view considering future uses of that view so that it can easily be reused. This avoids redundancy of models and encourages easier maintenance.

Watch this video to learn about the advanced data processing capabilities.

The calculation view is the main modeling object in SAP HANA Cloud.

There are three types of calculation view:

• Dimension

• Cube without star schema

• Cube with star schema

The purpose of a dimension type of calculation view is to define a group of related attributes. For example, a material dimension might include attributes such as material description, material color, material weight, and material price. Although this type of calculation view can be directly consumed by an application, it is most likely to be found as a consumable view in a calculation view of the type Cube with star schema to provide the cube's dimensions (see later for details).

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

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 joins if they are needed. You define filters, and then identify the output columns that are to be exposed by the view. 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. You could use an IF expression to check each weight and assign a category.

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

A dimension calculation view can consume other dimension calculation views. For example you could create a new dimension called partners that joins customer dimension and customer dimension.

The next type of calculation view is the type 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. For each measure you are able to define its aggregation behavior so that business users can slice and dice in an ad-hoc manner and all measures are aggregated according to their behavior. A Cube calculation view can consume other Cube calculation views or dimension calculation views. A cube calculation view is a relational model not a dimensional model.

Applications can directly access this type of calculation view. They can also be accessed via SQL 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 transaction 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. You can also merge transaction tables by implementing a union.

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

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

Now we come to the most powerful of all the calculation views: the Cube type, but with an additional setting — star join. This is how we model a star schema. This model is based on the cube type of calculation view, but with one or more dimension calculation views joined to the cube. This provides a very powerful and flexible dimensional 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 compared to cube type calculation views.

To get started, make sure that you set the data category to CUBE and 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 central hub of the star schema.

The next step is to form the star schema by joining suitable calculation views of type 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.

Cube with star join type calculation views can consume other Cube with star join calculation views and also simple Cube calculation views.

Procedures define reusable data processing logic that can be used to enhance a calculation view. Procedures are very similar to functions in that they are written in SQLScript and can have one or more inputs and they always have outputs. However, procedures can produce multiple output data sets of different structures, whereas a table function can only return one tabular output structure. Procedures have a limited role in data modeling. Procedures cannot be used as data sources to calculation views but they are used in other places in a calculation view, for example, to automatically derive values for an input parameter.

A procedure can be called directly from SQLScript, which means it can be called from a function or even another procedure.

Procedures used within modeling must be set to read-only. In that case they are called stateless (or side-effect free), because they don't alter any data or meta data in the database. However, procedures can also be used to update, insert, and delete data if required. These procedures are called stateful, and these type are not allowed when called from calculation views. Stateful procedures are more likely to be used by developers who build applications that change data, rather than modelers.