4.4Analytical Models

This section deals with modeled views that are used for analysis. You may be wondering why this topic plays such a big role in the context of SAP HANA. To answer this question, we’ll go back a little and briefly explain the underlying reasoning.

The business data of a domain are stored (usually in a normalized form) in a set of database tables that are connected via foreign key relationships (an entity-relationship model). Using this data model, single records can be efficiently created, selected, and modified. However, if data access becomes more dynamic and complex, or if certain analyses or checks are necessary, the data must be transformed.

The pattern most commonly used for these transformations is that the data are read from the database and used by a program for calculations before storing the results back in the database. This is referred to as materialization of the transformed data.

A simple example is the materialization of a totals calculation in a special column or totals table. In principle, the same pattern is used for data structures of a business intelligence system, where the original data is transformed into a form that can be used more efficiently for analyses (star schema). This materialization was primarily for performance reasons in the past because it wasn’t possible to perform the transformations on the fly at runtime when users submitted a query. However, because the different data structures had to be synchronized (which is usually done with some time offset), this performance gain also led to higher complexity and prevented a real-time experience for users. Using SAP HANA, this redundancy can now be eliminated in many scenarios. From a technical perspective, this means that the transformations are performed in real time using the original data. Consequently, database views are an important element—in this context, to express transformations for read accesses—and SAP HANA offers a powerful and user-friendly tool through analytical models.

In the scope of this section, we’ll create relatively simple analyses of flight bookings and the seat utilization of flights based on the SFLIGHT data model. In addition to some master data of a flight connection (airline, departure, and destination location), statistical information on seat utilization, revenues, and baggage will also be displayed per quarter. To create these analyses, we’ll use the different modeling options provided by SAP HANA and explain their properties and areas of use.

The following types of column views will be discussed:

In this chapter, we first show how you can define and test these views in SAP HANA Studio. Chapter 5 describes how to access them via ABAP programs.

4.4.1Attribute Views

Attribute views comprise a number of fields (columns) from database tables, which are linked through foreign key relationships. Moreover, attribute views provide a way to define calculated columns and hierarchical relationships between individual fields (e.g., parent-child relationships). They are significant particularly as components of other view types, especially as dimensions of analytic views (see Section 4.4.2) or for a more general purpose as nodes in calculation views (see Section 4.4.3).

In this section, we’ll create a number of such views to demonstrate different functional aspects. We’ll create several views because it’s not possible or useful to use all functions for all tables. Table 4.4 provides an overview of the views used in the example together with a description and the corresponding functionality.

Column

Description

Functionality

AT_PASSENGER

Simple view for table SCUSTOM (passenger data)

First basic example

AT_FLIGHT

Flight data plus information from the flight plan and information on the airlines

Different join types, calculated fields, and hierarchies

AT_TIME_GREG

Pure time hierarchy (year, quarter, calendar week)

Attribute view of type Time

Table 4.4Sample Attribute Views Used in this Section

We’ll reuse the views AT_FLIGHT, AT_PASSENGER, and AT_TIME_GREG in Section 4.4.2.

Basic Principles

Before describing how attribute views are modeled, let’s take a quick look at the most important concepts. Because attribute views can be used to create data views based on several tables that are linked via different types of joins, they can also be referred to as join views. Because joins play a major role when dealing with attribute views, accesses to attribute views are handled by the join engine in SAP HANA.

When modeling attribute views, the following concepts are important:

Creating Attribute Views

Attribute views can be defined via the Modeler perspective in SAP HANA Studio, which was introduced in Chapter 2, Section 2.4.3. To create a view, select New • Attribute View from the context menu of a package in the Content node. You first have to specify a Name and a Label (description) in the dialog shown in Figure 4.8.

In this dialog, you can also copy an existing view as the basis for a new attribute view. When selecting Subtype, you can create special types of attribute views (e.g., for time hierarchies, which are explained in more detail in Section 4.4.1). When you click the Finish button, the attribute view is created, and the modeling editor opens.

Creating an Attribute View

Figure 4.8Creating an Attribute View

The editor used to define an attribute view has two sections: Data Foundation and Semantics. These are displayed as boxes in the Scenario pane on the left-hand side of the screen (see Figure 4.9).

Definition of the Data Foundation

Figure 4.9Definition of the Data Foundation

By selecting each node, you can switch between defining the data basis (Data Foundation) and the semantic configuration (Semantics). The Data Foundation is used to add tables, define joins, and add attributes. Figure 4.9 shows a simple example based on table SCUSTOM.

By selecting the Semantics node, you can maintain further metadata for the attribute view, such as the following:

The layout of the Semantics section is shown in Figure 4.10.

Further Semantic Configuration of the Attribute View

Figure 4.10Further Semantic Configuration of the Attribute View

The selected columns from table SCUSTOM are marked as key fields. As described in Chapter 2, Section 2.4.3, you now have to save and activate the Attribute view to be able to use it.

If the view wasn’t modeled properly, an error will be displayed during activation. Typical errors are caused by missing key fields, invalid joins, or calculated fields that weren’t defined correctly. Figure 4.11 shows an example. The cause of an error may not always be as obvious.

Example of an Activation Error

Figure 4.11Example of an Activation Error

If the tables used are client-dependent, you can specify if the client should be automatically included in the filter condition based on the current context (Session Client). Alternatively, you can define the tables as Cross-client to access the data for all clients. It’s also possible to specify a static value for the client. Usage tips regarding this topic are provided in Chapter 5, Section 5.1.4.

[»]Determining the Client

There is a session context for every database connection that stores certain properties of the current connection. In particular, this information comprises the current client, which is set by the Database Shared Library (DBSL) in case of a connection via AS ABAP. When using the Data Preview or a connection via the SQL console in SAP HANA Studio, the client is determined from the user settings. When configuring these settings, you can specify a default client for a user. If no client is specified, there is no client context, which means that all data is displayed (cross-client) when using the Data Preview.

Following this initial simple example, let’s assume you now want to define a more complex attribute view. For this purpose, you want to define the SFLIGHTS view from the DDIC, which you’ve already seen in Chapter 3, Section 3.2.3, as an attribute view. To do so, you create a new AT_FLIGHT attribute view and add table SFLIGHT in the Data Foundation. You can either manually select those tables or have the system propose tables based on the metadata maintained in the DDIC. For the latter option, select the table, and then choose Propose Tables from the context menu. The selection dialog opens the screen shown in Figure 4.12.

Proposed Values for Defining Joins

Figure 4.12Proposed Values for Defining Joins

To reproduce the SFLIGHTS view, you’ll add tables SCARR and SPFLI and define the joins as shown in Figure 4.13. If you want to define a new join, simply drag a connecting line between the corresponding attributes of two tables while holding the mouse button down. To define the properties of a join, you first have to select the join and then configure it in the Properties section (Join Type, Cardinality). For our example, a referential join and a cardinality of n:1 are used.

[»]Attribute Views Support Only Equi-Joins

When formulating join conditions, you can use more expressions (e.g., <, >) in SQL that go beyond checking the equality of columns (equi-join), as shown in the following example:

SELECT ... FROM ... [INNER|OUTER] JOIN ... ON col1 < col2 ...

However, attribute views support only equi-joins.

In the next step, you add the desired attributes from the tables via the context menu of the output structure of the view. The selected attributes are highlighted and displayed in the Output section in the right-hand pane of the editor.

Attribute View Similar to the DDIC SFLIGHTS View

Figure 4.13Attribute View Similar to the DDIC SFLIGHTS View

For this example, choose the MANDT, CARRID, CONNID, and FLDATE columns as key attributes and then activate the view. The result shows the name of the airline and information on the departure and destination location for every flight (see Figure 4.14).

Result of the Attribute View

Figure 4.14Result of the Attribute View

As for normal SQL views, you can also specify filter values for columns when working with attribute views. To define the filter, you open the filter dialog for an attribute via the context menu item Apply Filter. Attributes with an existing filter are marked with a filter symbol, as shown in Figure 4.15. For this example, define a filter for the attribute PLANETYPE with an Equal operator and the Value “A330-300”. Alternatively, you can also try other comparison operators.

Filter for an Attribute

Figure 4.15Filter for an Attribute

Calculated Fields

Now that you’ve seen how an attribute view can be used to read data from different tables using different join types, you can go one step further and dynamically calculate some of the view columns. Compared to classic DDIC views, these virtual attributes (i.e., attributes that don’t belong directly to a column of one of the physical tables) are a new opportunity for expressing data processing logic. Chapter 6 introduces you to the new CDS views in ABAP, which offer this opportunity too.

As a first example, add a calculated attribute to the AT_FLIGHT attribute view (refer to Figure 4.13), which will contain the full flight connection (departure location and airport plus destination location and airport) as its value, for example, NEW YORK (JFK) – SAN FRANCISCO (SFO).

To do so, define a calculated attribute in the Data Foundation via the Calculated Columns node of the Output section, and specify a Name, a Label, and a Data Type (see Figure 4.16).

Definition of a Calculated Field

Figure 4.16Definition of a Calculated Field

Using the Expression Editor, you can specify an expression that will be used to determine the value. This provides a variety of functions (conversions, mathematical operations, string operations, date calculations, and even simple case distinctions). In our example, we’ll only use a simple concatenation of strings for now (see Listing 4.9).

"CITYFROM" + ' (' + "AIRPFROM" + ') – ' + "CITYTO" + ' (' + "AIRPTO" + ')'

Listing 4.16Example of an Expression for a Calculated Field

[ ! ]Attribute References and Constants in Expressions

When defining expressions for calculated attributes, you must make sure to use the correct type of quotation marks. For references to attributes of the view (e.g., "CITYFROM" in Listing 4.9), double quotes must be used. It’s recommended to use the drag-and-drop function via the formula editor. For text constants, by contrast, single quotes must be used (as shown in the parentheses in Listing 4.9).

Using the wrong quotation marks usually leads to an activation error.

After activating the attribute view, the calculated column is displayed in the output (see Figure 4.17). Calculated columns can be queried via SQL just like normal columns, which will be demonstrated in Section 4.4.4.

Output of the Calculated Field

Figure 4.17Output of the Calculated Field

Calculated fields are also supported for the other view types (see Section 4.4.2), where these fields are used especially for the calculations and conversions of currencies and units that we already mentioned.

Hierarchies

Many types of data have hierarchical relationships. The place of residence or principal office of customers is structured geographically by country, region, and city; the hierarchical structure of a creation date comprises the year, quarter, and month; a product catalog can consist of several categories; and so on.

Hierarchies play an important role in data analyses. You can start with an aggregated view of the data and then navigate within the hierarchical structures. This is referred to as a drilldown (or drillup when data is aggregated).

For attribute views, hierarchies are defined in the Semantics section. SAP HANA currently supports two types of hierarchies:

Existing hierarchies are displayed in the Semantics section, where you can also create new hierarchies. Figure 4.18 shows a level hierarchy based on the attributes of the departure location (country, city, airport) from table SPFLI. You can also define hierarchies for calculation views (see Section 4.4.3).

Hierarchy of an Attribute View

Figure 4.18Hierarchy of an Attribute View

There are various options for using the modeled hierarchies. This information is evaluated in particular by the supported business intelligence clients. One particular variant (access via Microsoft Excel) is shown in Section 4.4.5. SAP HANA thus provides basic support for simple hierarchies, but compared to the comprehensive hierarchy modeling that’s available in SAP Business Warehouse (SAP BW), for example, the options are rather limited.

Attribute Views for Time Values

Most business data have a time reference (e.g., a creation date or a validity period). These references are usually implemented as date fields or time stamps in the data model. The flight data model, for example, comprises the flight date in table SFLIGHT and the booking time in table SBOOK. For many analyses, this point in time must be mapped to a certain time interval. In the simplest case, this can be the corresponding year, month, quarter, or calendar week. However, there are also more complicated or configurable time intervals such as the fiscal year, which is the calendar to be used for certain scenarios.

[»]Customizing the Fiscal Year

Fiscal years and periods are configured via ABAP Customizing. Using ABAP Customizing, you can configure comprehensive settings or variants and also define special cases (e.g., a short fiscal year when a company is founded). These settings are configured via the Maintain Fiscal Year Variant entry of Transaction SPRO.

The SAP standard provides several function modules to convert a normal date (e.g., of type DATS) into the corresponding fiscal year or period.

From a technical perspective, the corresponding Customizing is stored particularly in tables T009 and T009B. These tables were previously pool/cluster tables and therefore not available directly in the database. Such tables are converted into normal database tables when performing a migration to SAP HANA (see Chapter 3, Section 3.2.1) so that such data can also be accessed natively in the database.

SAP HANA offers a special attribute view type for handling time hierarchies. Before you can use this variant, you must first create time data once in special technical tables in SAP HANA. You can select Generate Time Data on the initial screen of the Modeler perspective for this purpose. Subsequently, you specify the details for the calendar type and time period.

Figure 4.19 shows the generation of time data based on days in the normal (Gregorian) calendar for the years 2000 through 2020. You can also define data in the fiscal calendar. Here, however, you must additionally specify a variant that was set in Customizing (see the preceding information box).

You can then define an attribute view that uses this time data. To do so, you select the Time type and specify the desired details for the calendar when creating an attribute view. Figure 4.20 shows how the attribute view AT_TIME_GREG is created for a day-based Gregorian calendar.

Generating Time Data

Figure 4.19Generating Time Data

Attribute View for a Gregorian Calendar

Figure 4.20Attribute View for a Gregorian Calendar

Because the view contains the date as a key field, joins can be created for a date column in the business data. You use the attribute DATE_SAP of the attribute view for a join with a column of type DATS (e.g., FLDATE in table SFLIGHT).

This means that you can use these views as time dimensions in an analytic view if the date is part of the fact table. We’ll discuss this in detail in the next section.

[»]Technical Tables for Time Data

The time data is stored in special tables in the _SYS_BI schema, for example, tables M_TIME_DIMENSION and M_FISCAL_CALENDAR. These tables can also be used directly in attribute views (or general SQL statements).

4.4.2Analytic Views

Analytic views are special views in SAP HANA that are used to calculate and analyze key figures. If you’re already familiar with data warehouse or business intelligence applications, you can think of an analytic view as a star schema. We first provide a brief introduction of the most important concepts and then explain how you can create analytic views in SAP HANA Studio and define calculated key figures in particular.

In this section, two scenarios will be implemented as analytic views. In the first example (AN_BOOKING), we’ll model an analysis of the flight bookings based on attributes of the customer and the flight. In this analysis, the booking prices and the baggage weight are examined as key figures. For both figures, conversions must be considered due to different currencies and weight units. We’ll also define another calculated figure based on the baggage weight, which specifies whether we’re dealing with excess baggage (more than 20KG). In the second example, we’ll define an analytic view called AN_SEAT_UTILIZATION to analyze the seat utilization of flights.

Both AN_BOOKING and AN_SEAT_UTILIZATION use the attribute views from the previous section. In Section 4.4.3, the two attribute views are combined for analysis.

Basic Principles

When using analytic views, you should be familiar with the most important concepts from the Online Analytical Processing (OLAP) environment, that is, from the field of data analysis. We’ll therefore give you a short introduction based on an example in this section.

Analyses usually focus on transaction data (purchase orders, documents, invoices, etc.). The corresponding table is referred to as the fact table. This data includes one or several key figures or measures—for example, the invoice amount—which are relevant for data analysis. Fact tables usually contain a large number of entries. Moreover, fact tables can contain data from several database tables. The key figures must be from one table, and the attributes of the other table are, for instance, needed as foreign keys. Fact tables containing header data and line items are typical examples.

The transaction data includes associations with master data (e.g., via the customer number of a purchase order) and other data such as time stamps (e.g., the purchase order creation date). Because this associated data can also be used to break down the fact table into data slices, the data are also referred to as dimensions, and an analysis along these dimensions are called slice-and-dice operations. An example of this is the determination of the total revenue in 2015 for customers from the United States. Within the dimensions, the data are usually structured hierarchically (e.g., by geographical regions or time intervals). This makes it possible to analyze these hierarchy levels (drilldown, drillup) further; for 2015 revenue in the United States, for example, you could analyze the data by state or quarter.

Let’s now look at a concrete example based on our flight model. We’ll use table SBOOK as the fact table and the LOCCURAM column (flight price in the airline’s currency) as the key figure. The customer number, the flight date, and the flight connection comprise associations that allow you to perform the analysis based on several dimensions.

When looking at a graphical representation of the data model, it resembles a typical star schema (see Figure 4.21).

Example of a Star Schema with SBOOK as the Fact Table

Figure 4.21Example of a Star Schema with SBOOK as the Fact Table

The data are usually structured hierarchically within the dimensions. The geographical data of the departure location, the customers’ places of residence (country, city, etc.), and the flight date (year, month, day) represent the hierarchies in this example; the hierarchical relationship is defined by the columns, as shown in Figure 4.21. Because the flight data model doesn’t contain a database table with time data, the time hierarchy is marked as virtual.

A star schema provides different filter variants. On one hand, there can be restrictions for the transaction data. When analyzing the flight bookings, for example, we only want to consider the bookings that weren’t canceled. On the other hand, you can also define special key figures to directly apply restrictions within the dimensions (e.g., to consider only customers in the United States). These key figures are also referred to as restricted measures.

Creating Analytic Views

Similar to attribute views, analytic views are created in SAP HANA Studio via the context menu of a package in the Modeler perspective. After specifying a name and a description, the corresponding editor opens (see Figure 4.22).

Editor for Analytic Views

Figure 4.22Editor for Analytic Views

The editor for analytic views consists of three sections:

The first example implements the star schema shown earlier in Figure 4.21 as an analytic view. To do so, you add table SBOOK as the fact table, select the required fields as you did for the attribute view, and define the filter for the CANCELLED column.

Then switch to the Logical Join section, and add the AT_FLIGHT, AT_PASSENGER, and AT_TIME_GREG attribute views from Section 4.4.1 as dimensions. When doing so, you draw a connecting line from the fact table to the attribute views. Figure 4.23 shows the resulting diagram.

As the final step, you select the measures in the Semantics section. In this example, the flight price in the local currency of the airline (LOCCURAM) and the baggage weight (LUGGWEIGHT) are used for these measures. After the view is activated, you can use the Data Preview for a first simple analysis of the result set. Figure 4.24 shows a sample breakdown of the revenue by year, quarter, and airline, with a filter set for the year 2013.

Analytic View Based on Booking Data

Figure 4.23Analytic View Based on Booking Data

Data Preview with Breakdown by Year, Quarter, and Airline

Figure 4.24Data Preview with Breakdown by Year, Quarter, and Airline

Following the same procedure, you’ll now create a second analytic view, AN_SEAT_UTILIZATION, which uses table SFLIGHT as the fact table instead of the flight bookings, but also uses AT_TIME_GREG as the time dimension so that the seat utilization can be analyzed by quarter. Figure 4.25 shows the resulting star schema.

Second Analytic View, Based on Flight Data

Figure 4.25Second Analytic View, Based on Flight Data

Now that you know how to create and test analytic views, we’ll discuss calculated key figures in the next section.

Calculated Key Figures

As with attribute views, you can also define virtual columns for analytic views. The values of those columns are determined by a calculation. For analytic views, you usually define calculated key figures, that is, numerical values such as amounts or units of measurement. A special case of such calculated values are conversions between different currencies and units. You’ll calculate a key figure for each of the two analytic views from the previous section. For the AN_BOOKING view, you’ll use an expression to identify the bookings with excess baggage; for AN_SEAT_UTILIZATION, the relative seat utilization will be determined as a percentage value based on the number of available and occupied seats.

Calculated key figures are basically defined following the same procedure that is used for attribute views. However, you must also flag the new column as Measure (via the Column Type) and specify whether it’s to be determined before or after an aggregation. In many cases, the calculation must be done using the raw data (i.e., before aggregation). Figure 4.26 shows the determination of all flight bookings with a baggage weight value of more than 20. Ignore the fact that the weight might be specified using different weight units for now. If a summation is done on this column, the number of bookings with excess baggage is determined because the value of the calculated column is null for all other bookings.

Calculated Key for the Number of Flight Bookings with Excess Baggage

Figure 4.26Calculated Key for the Number of Flight Bookings with Excess Baggage

Now follow the same steps to define a calculated key figure UTILIZATION (data type DECIMAL) in the AN_SEAT_UTILIZATION view, and use the following expression as the calculation formula:

if("SEATSMAX">0, decfloat( "SEATSOCC" + "SEATSOCC_B" +
"SEATSOCC_F" ) / decfloat( "SEATSMAX" + "SEATSMAX_B" +
"SEATSMAX_F" ),0)

As you can see, you divide the sum of the occupied seats by the sum of the available seats in the three booking categories. For the result to be handled as a decimal number, the type is converted using the decfloat function.

Currency Conversion and Unit Conversion

As a special case of a calculated key figure, the analytic view supports the conversion of monetary amounts and units of measure. We’ll show you how this is done for the AN_BOOKING sample view to indicate the flight price in euros and the baggage weight in kilograms.

In addition to the modeled variant, you can also run a currency or unit conversion via the SQLScript CE_CONVERSION function or the SQL CONVERT_CURRENCY or CONVERT_UNIT functions, which you can also use in ABAP via CDS views (see Chapter 6).

There are two approaches for modeling currency or unit conversions: specify that the given conversion should be performed for every access on an existing column, or define an additional virtual column for the conversion result. When using the second variant, you can access both the original value and the converted value.

To use a calculated column for the conversion, you first define a calculated field of the type Measure and link it to the original column using the same data type. You can then configure the details on the Advanced tab.

You must specify whether the field contains a monetary amount or a quantity unit. Moreover, you must indicate the field where the corresponding currency or unit of measure can be found. Unfortunately, it’s currently not possible to evaluate the corresponding information from the DDIC, where this relationship is also defined (Currency/quantity fields tab in Transaction SE11).

The example in Figure 4.27 shows a currency conversion for the LOCURRAM column of table SBOOK into the target currency euro with the key date September 13, 2015. Here, the standard Exchange Type M is used.

Defining the Parameters for a Currency Conversion

Figure 4.27Defining the Parameters for a Currency Conversion

In many cases, you want the target currency and the key date to be parameterized for the conversion. Unfortunately, this can’t be done using the WHERE condition when accessing the view via SQL because it isn’t possible to access the query parameters during modeling. For this reason, you can define input parameters for an analytic view, which can then be used as parameters for the conversion. You can define input parameters via the Semantics section in the Parameters/Variables tab. These parameters can then be used as Target Currency when configuring the conversion in Figure 4.27. The same procedure can be used to parameterize the key date.

The same principle is used for unit conversion. In our AN_BOOKING sample view, the baggage weight should always be considered in kilograms (KG) to identify bookings with excess weight. For this purpose, in Figure 4.27, you select the Quantity with Unit of Measure setting as the Semantic Type, and define the parameters for the conversion.

4.4.3Calculation Views

In this section, we’ll introduce the last view type, the calculation view, which is used whenever the capabilities of attribute and analytic views can’t meet your requirements. This is especially the case in scenarios where it’s necessary to combine several views in a flexible manner. Chapter 5 provides recommendations on how to use the various view types.

There are two variants of calculation views. You can either model calculation views or implement them using SQLScript. This section describes the usage of both variants.

In this section, we’ll define two calculation views. We’ll combine the two analytic views from the previous section in the modeled CA_FLIGHT_STATISTIC view and create a combined data view on the seat utilization and number of bookings with excess baggage for a flight. In the implemented CA_SEAT_UTILIZATION_DELTA view, we’ll determine the average seat utilization and compare this result with the corresponding value from the previous year.

Basic Principles

The main difference between calculation views and the other view types introduced so far is that calculation views can combine any other types of views. In case of attribute views, you can only link database tables via joins. Analytic views are always based on a star schema consisting of a fact table and dimensions. Calculation views have no such structural limitations.

A calculation view is based on a calculation model that consists of nodes and operations. These nodes can be tables or any type of view.

Calculation views are modeled graphically in a tree structure, with the leaves representing tables or views. The other nodes define operations on the data. The following operations are currently supported: Join, Projection (definition of a field list), Aggregation (calculations), Union, and Rank (sorting). The root node represents the output structure of the view and thus its external interface.

SQLScript provides a built-in function for each of these operations so that each graphical model has a canonical execution plan in the calculation engine in SAP HANA. This execution plan can be displayed using the SAP HANA Plan Visualizer (PlanViz) tool, which will be introduced in Chapter 7.

Like attribute views or analytic views, calculation views support the definition of hierarchies and input parameters.

As with almost every graphical modeling approach, there are also certain limitations to modeling calculation views as described briefly here:

To avoid these restrictions, SAP HANA provides the options to implement calculation views also via SQLScript, which we’ll detail after the modeled variant.

Graphical Modeling of Calculation Views

Calculation views are created using the same procedure as attribute views and analytic views. To create a graphical calculation view, choose Graphical as the Type (Figure 4.28). The Data Category defines the topmost node of the model and thus the primary usage scenario. Choose Cube for a model that will be used as an analytical data source. You can leave this setting blank for a general view. Thus, the highest node in the model is a projection (see Figure 4.29).

Creating a Graphical Calculation View

Figure 4.28Creating a Graphical Calculation View

Figure 4.29 shows the resulting graphical calculation view.

Graphical Calculation View CA_FLIGHT_STATISTIC

Figure 4.29Graphical Calculation View CA_FLIGHT_STATISTIC

In the editor for calculation views, you can add tables and views as data sources and connect them using operations from the Tools Palette. These operations are Projection, Join, Aggregation, Union, and Rank. The editor displays both the data sources and the operations as nodes. The Semantics node represents the output structure of the calculation view.

For our example, we’ll combine the data from the two analytic views. The output should include the number of bookings with excess baggage (calculated key figure OVERWEIGHT from AN_BOOKING) and the seat use (calculated key figure UTILIZATION from AN_SEAT_UTILIZATION). Because the data from AN_BOOKING is based on bookings, you have to first aggregate the key figure OVERWEIGHT and then create a join.

To create this view, you first add an Aggregation and a Projection via the Palette and link them to the views as described previously. You must select the attributes needed for each of the nodes. Then select a Join node and link the nodes as illustrated earlier in Figure 4.29. Here, you define the join as you’ve done for the attribute views by selecting the node.

After successful activation, the resulting CA_FLIGHT_STATISTIC calculation view is displayed (see Figure 4.30).

Data Preview for the Calculation View CA_FLIGHT_STATISTIC

Figure 4.30Data Preview for the Calculation View CA_FLIGHT_STATISTIC

Implementing Calculation Views via SQLScript

As previously mentioned, calculation views can also be implemented using SQLScript.

Implemented calculation views are created following a similar procedure as used for the modeled variant; however, you choose SQL Script as the Type (see Figure 4.31) in this case.

Creating an Implemented Calculation View

Figure 4.31Creating an Implemented Calculation View

[»]Settings for SQLScript Views

The settings (Properties view in Eclipse) include three important parameters for calculation views that are implemented via SQLScript (the first two parameters have already been mentioned in Section 4.2.2):

After clicking the Finish button, the system opens the editor for calculation views implemented with SQLScript (Figure 4.32).

Editor for Implemented Calculation Views

Figure 4.32Editor for Implemented Calculation Views

The editor has two nodes:

For our example, you first define the columns of the var_out output parameter. The result is displayed in Figure 4.33.

Structure of the Output Parameter var_out

Figure 4.33Structure of the Output Parameter var_out

In the next step, you can insert the SQLScript coding from Listing 4.10 as the implementation. Here, you perform a join of the AN_SEAT_UTILIZATION view with the same view (called a self-join). This defines the two time slices (data of the current and the previous year) needed for determining the variance in the average utilization. By means of SQLScript, you can first store an interim result in the lt_data table variable and use it in the second expression.

/********* Begin Procedure Script ************/
BEGIN
lt_data = select mandt, carrid, connid, year,
avg(utilization) as utilization
from "test.a4h.book.chapter04::AN_SEAT_UTILIZATION"
group by mandt, carrid, connid, year;

var_out = select c.mandt, c.carrid, c.connid,
c.year, p.year as prev_year,
c.utilization as utilization,
p.utilization as utilization_prev,
c.utilization - p.utilization as utilization_delta
from :lt_data as c
left outer join :lt_data as p
on c.mandt = c.mandt and p.carrid = p.carrid and
c.connid = p.connid and c.year = p.year + 1
order by c.year desc;
END /********* End Procedure Script ************/

Listing 4.17SQLScript Implementation of the Calculation View

The two time slices are called c (current) and p (previous) in the SQL statement, and the essential connection is implemented via the c.year = p.year + 1 join condition.

In the next step, you define the output structure of the view via the Semantics section of the editor. You can select the columns of the var_out output parameter, which will be exposed by the calculation view. In addition, you specify whether the columns are exposed as attributes or as key figures. As with modeled calculation views, you can also create hierarchies and variables.

After successful activation, you can display the result in the Data Preview. Figure 4.34 shows the percentage increase or decrease of the seat utilization for a time period of several years for connections of the airline LH.

Data Preview for the CA_SEAT_UTILIZATION_DELTA Calculation View

Figure 4.34Data Preview for the CA_SEAT_UTILIZATION_DELTA Calculation View

4.4.4Runtime Objects and SQL Access

As described in Chapter 2, Section 2.4.3, column views are created in the _SYS_BIC schema when activating views from the SAP HANA repository that can be accessed via normal SQL. These column views also form the basis for ABAP access, as shown in Chapter 5, Section 5.1. The exact runtime objects depend on the view type and the concrete modeling. Usually, there is a leading object that serves as the primary interface for data access and additional technical objects for specific aspects.

Attribute Views

This section describes the specifics of attribute views. Every attribute view has a corresponding column view. In addition to this view, another column view is created for every hierarchy. For our AT_FLIGHT attribute view, the column views listed in Figure 4.35 exist in the database catalog in the _SYS_BIC schema.

Column Views Generated for the AT_FLIGHT Attribute View

Figure 4.35Column Views Generated for the AT_FLIGHT Attribute View

Note that the names of the runtime objects always contain the package names. This is necessary because you can create objects with the same name in different packages.

In addition, there is a public synonym that can also be used to access the views:

"test.a4h.book.chapter04::AT_FLIGHT"

Attribute views can be accessed using regular SQL. However, note that attribute views aren’t optimized for calculations such as column aggregations, but rather for efficient join calculations. In other words, not every SQL statement should be used for every view type in SAP HANA.

Analytic Views

As is the case for attribute views, a primary runtime object corresponds to the analytic view. In addition, several additional column views are created depending on the occurrence of hierarchies, key figures, and calculated fields. However, for application developers, these objects play only a minor role. The primary runtime artifact of the AN_BOOKING analytic view in the test.a4h.book.chapter04 package can again be addressed via a public synonym:

test.a4h.book.chapter04::AN_BOOKING

When accessing a view via SQL, remember that analytic views aren’t designed for single accesses but rather for aggregated accesses. For example, reading all rows using the following statement isn’t supported:

; The following access results in an error message
SELECT * from "test.a4h.book.chapter04::AN_BOOKING";

Instead, you must always use an aggregation (COUNT, SUM, etc.) and the corresponding grouping. Moreover, because grouping can only be done via columns of the analytic view, analytic views can’t be linked to other tables or views directly via SQL. We discussed this in Section 4.4.3 using an example.

If you defined input parameters for the view, you can pass them in an SQL query, as shown in the following example:

SELECT <Columns> FROM <View> ('PLACEHOLDER' = ('$$TARGET_CURRENCY$$', 'EUR')) WHERE ... GROUP BY ...

Calculation Views

As is the case for attribute views and analytic views, there is a primary runtime object with a canonical name for calculation views as well; for our CA_FLIGHT_STATISTIC view, this is a public synonym with the following name:

test.a4h.book.chapter04::CA_FLIGHT_STATISTIC.

Moreover, when dealing with calculation views, there are also special column views for the hierarchies and key figures.

For implemented calculation views, the system also creates a database procedure and a table type for the var_out output parameter of the database procedure.

4.4.5Accessing Column Views via Microsoft Excel

So far, you’ve only seen how the result of a view can be displayed using the Data Preview in SAP HANA Studio. While this is sufficient for first tests, the results aren’t complete (there is a maximum number of rows), and the Data Preview provides limited options for analysis.

SAP provides many tools for accessing SAP HANA views. In particular, you can use the SAP BusinessObjects Business Intelligence Platform for analyses, dashboards, and so on, based on SAP HANA views. There is also a data modeling integration of the SAP HANA views in SAP BW. These advanced options are explained in more detail in Chapter 9.

In this section, we’ll introduce a fairly simple method for accessing views from Microsoft Excel. To use this method, you only need the SAP HANA Client, which is part of SAP HANA. Installation details can be found in the corresponding documentation. After installing this package, the SAP HANA MDX Provider should be available as an OLE DB Provider (Object Linking and Embedding database interface) in the data import wizard of Excel (see Figure 4.36).

Data Import via the OLE DB Provider in Excel

Figure 4.36Data Import via the OLE DB Provider in Excel

Using this provider, you can import the data from an analytic view or a calculation view into a pivot table in Excel. After the OLE DB driver establishes a connection, a selection dialog with the available SAP HANA views appears (see Figure 4.37).

Importing SAP HANA Views into Excel

Figure 4.37Importing SAP HANA Views into Excel

You can then use the pivot table functions in Excel on the data from the SAP HANA view. Figure 4.38 shows a representation of the data from the AN_BOOKING analytic view created in Section 4.4.2.

Pivot Table in Excel Based on the AN_BOOKING Analytic View

Figure 4.38Pivot Table in Excel Based on the AN_BOOKING Analytic View