SAP BW on SAP HANA

2.2 SAP HANA native modeling

SAP HANA native modeling relates to the activities associated with creating views on top of database tables to depict desired business scenarios or address reporting requirements. Figure 2.3 demonstrates SAP HANA studio’s modeler perspective and how a user would view his or her home screen. Notice the list of views that can be created. These views are information views in SAP HANA and can be created, changed, and deleted via the SAP HANA modeler perspective.

Figure 2.3: SAP HANA modeler perspective

Information views are therefore logical, non-materialized structures acting as a semantic layer over the SAP HANA index server, allowing reporting tools to access data stored within SAP HANA tables.

SAP HANA tables are organized by schemas under the catalog folder. Schemas are groups of tables, database views, and procedures of a database. Schemas can be seen as the blueprints of the database and are commonly used to restrict access to certain database content. The association of users to schemas results into proper segregation of access to content groups.

Information views are organized by packages under the content folder. They organize data as per business needs and do not follow technically defined schemas. An information view, for example, could be created associating tables from sales and finance schemas to address reporting needs of a sales department. An information view like this would probably be stored under a package with a suggestive name such as “Order to Cash Analysis” package.

In a nutshell, SAP HANA tables are organized by schemas under the catalog folder representing the technical view of the database. Information views are organized by packages under the content folder representing the business view of the database contents. Figure 2.4 demonstrates the file structure within SAP HANA studio.

Figure 2.4: SAP HANA studio catalog and content folders

The catalog, therefore, is the usual workspace for SAP HANA developers. A dedicated SAP HANA perspective named SAP HANA development is available for such users. The focus for SAP HANA modelers on the other hand is centered on the content folder. It is under this folder that information views can be maintained to slice, filter, and join SAP HANA tables to address specific business requirements. Three types of information views can be created within the content folder: attribute views, analytical views, and calculation views.

2.2.1 Attribute views

Attribute views are used to represent an entity relationship model at the attribute level. As an analogy, an attribute view could be seen as similar to an SAP BW master data model with a main entity combined with its attributes and texts. An attribute view is a self-contained unit, a dimension that joins SAP HANA tables and can be reused on any other SAP HANA model or consumed directly by reports.

Defining a data foundation is the starting point when creating an attribute view. A data foundation refers to the tables required for defining the view and can be comprised of a single table or several joined tables. Referential, inner, left outer, right outer, and text join types are available when joining tables within a data foundation.

The output of a data foundation may be comprised of all the fields of the underlying tables or just a subset of those. In addition to selecting the fields from each table, it is possible to add calculated, derived columns to the output. Key attributes are also identified at the output level.

The data foundation output is made available to the semantic layer under a list format. Within the semantic layer, columns can be further manipulated by being renamed, hidden, or set up on a hierarchical fashion for reporting purposes; the semantic layer becomes the consumable output of an attribute view. Figure 2.5 is an example of an attribute view.

Figure 2.5: Attribute view is comprised of a data foundation, based on a single table or several joined tables, and a semantic layer specifically formatted for reporting

2.2.2 Analytical views

The starting point of an analytical view is very similar to an attribute view. It starts with the definition of a data foundation. However, an important differentiation can be made. The analytical view’s main purpose is to allow data modeling that includes measures, also known as key figures in SAP BW terminology. As such, one of the tables within the data foundation must be the one carrying such measures. This table is defined as the central entity of the data foundation. All other tables within the data foundation are then deemed input tables. Naturally, if an analytical view is comprised of just one table, then this table is the central entity.

On a typical analytical model, texts and attributes are encountered alongside with measures. These elements bring meaning to measures, allowing subtotal calculations, hierarchical presentations, and aggregation of values and amounts around specific reporting business entities such as region, periods, and customer.

It is within an analytical view definition that SAP HANA modeling possibilities start to become even more interesting. Let’s make a parallel to the InfoCube models available in SAP BW. Figure 2.6 illustrates a star schema model of an SAP BW InfoCube. InfoCubes were known as extended star schema models being comprised of a fact table, the dimension tables surrounding it, and connected to or, let’s say, extended to master data tables.

Figure 2.6: Star schema model of an SAP BW InfoCube

An InfoCube is a very sophisticated modeling object for relational databases. It overcame the limitation of 255 fields of a single-table model by making use of the multidimensional modeling techniques. This meant that business entities could be grouped under dimensions and connected to a central table containing the measures. In SAP BW, an InfoCube is limited to 16 dimensions; however, 1 is dedicated to loading control (request dimension). The other 15 dimensions can grow up to a maximum number of 248 characteristics each. The characteristics would then be connected to a master data model that could contain another set of characteristics, called attributes, inherent to the main characteristic

The extended star schema model was a major improvement when it was launched. If compared to an analytical view, we can see similarities. An analytical view is also based on the multidimensional modeling technique. As explained before, its data foundation starts with the definition of a central entity that can be seen as the fact table of the model. This table contains the measures and attributes for slicing and dicing the data. However, no dimension is present. After all, dimension tables were a modeling technique to overcome limitations related to a maximum number of fields of relational databases.

Well, in SAP HANA, dimensions are redundant. There are fewer limitations to the number of columns that a table can have as these columns are considered “independent” columns. SAP HANA is a columnar database.

With this is mind, a graphical representation depicting a typical SAP HANA analytical view would be somehow similar to Figure 2.7.

Figure 2.7: Depicting a typical SAP HANA analytical view model

The model presented in Figure 2.7 could be entirely defined within the data foundation definition of an analytical view. But is this really desirable? Let’s consider the fact that information views such as attribute and analytical views are modeling objects that can be reused on other SAP HANA models, therefore acting as building blocks for more complex modeling objects.

If one follows this line of thought, the logical way of modeling in SAP HANA studio would be to start from its smaller building blocks, defining business entities such attribute views, first. For example, a sales department business entity with attributes such as address, sales region, logistics details, and description could be defined as one specific attribute view within SAP HANA. This sales department attribute view could then be used across several analytical views by joining it to the respective analytical data foundations.

That is why between the data foundation and semantic layer of an analytical view one encounters the logical join layer, as shown in Figure 2.8. This layer allows the data foundation of an analytical view to be joined with attribute views previously defined.

Figure 2.8: The logical join of an analytical view allows previously created attribute views to be joined to the analytical view model

As for the semantic layer of an analytical view, it has the same role as within the attribute views, representing the consumable output of the analytical view for reporting tools.

2.2.3 Calculation views

The third type of information view available within SAP HANA is the calculation view. This view offers the possibility of combining other views as composite providers. If attribute views can be seen as somewhat similar to SAP BW master data tables and analytical views to SAP BW InfoCubes, then SAP HANA calculation views would be the counterpart in SAP HANA to SAP BW multiproviders.

But that is where the similarities end. Multiproviders are primarily union-based objects. If joins are needed, then some data preparation is required. Maybe an InfoSet can be created first; thus enabling the join to be mapped at the multiprovider level. Calculation views on the other hand are much, much more powerful. They can use joins and unions when combining information views, work with projections of the original views where columns can be removed, and can be added and further manipulated via filtering and aggregation. Calculation views can also incorporate the use of SQL procedures or script-based calculations to support complex logic when combining data from different data foundations or tables.

The best way to describe a calculation view is to refer to it as a logic flow which is comprised of transformation steps. These steps combine data from different information views, or SAP HANA tables, to achieve a desired reporting output. As such, the definition of a calculation view does not start with a data foundation, but with an empty scenario panel, the semantics node, and an accompanying aggregate node, in the case of a graphical-modeling-based calculation view, a script view node, or a script-based calculation view (as shown in Figure 2.9).

Figure 2.9: Scenario panel for graphical modeling and script-based calculation views

For the graphical calculation views, the transformation steps available are: projections, unions, joins, and aggregations. A calculation view with such transformation steps can be seen in Figure 2.10.

Figure 2.10: Graphical-modeling-based calculation views with transformation steps

For a script-based calculation views, the options are SAP HANA SQL or calculation engine (CE) functions. Preference is given to CE functions as these are predefined logic steps that are executed in an SAP HANA calculation engine and therefore achieve better performance results.