Analytical View

Analytical views are similar to star schema in SAP BW. They have a fact table linked with attribute views. As the name suggests, these are used for analytical reporting (to calculate and analyze key figures)

Sample Scenarios

In this post, two scenarios are shown to help you understand analytical views-

• AN_FLIGHT_BOOKING – This analytical view will be created to analyze customer bookings of the flights. In this scenario, the key figures cost of booking and baggage weight will be analyzed (For both of these key figures, conversions are needed as prices and weight are in different currencies and units). One more calculated key figure will be defined for the baggage weight to indicate whether it is excess baggage (more than 20 KG)
• AN_FLIGHT_SEATS – Another analytical view will be created to analyze the seat utilization of flights

These analytical views will use the attribute views created in the earlier post.

Flight model star schema

In our example, we will take SBOOK as the fact table.  The column LOCCURAM (flight price in airline currency) is the key figure.

Customer number, flight date, flight connection serve as foreign keys to the associated dimensions which are used to analyze the data (slice and dice, drill down and drill up)

Star schema with SBOOK as the fact table-

While analyzing in analytical views, we may have restrictions on the data. For example, the flight information is only analyzed for the bookings that were not cancelled.

Also, you can apply restrictions within the dimensions for certain key figures like show flight amount for US customers only. These are also known as Restricted measures.

Creating Analytical Views

Similar to attribute views, analytical views are created in the MODELER perspective of the SAP HANA studio. Right click on a package, and select New-Analytical view. Give name and description.

The Editor window opens.

The editor for analytical view contains three sections:

• Data Foundation – to define the fact table
• Star Join – To add the dimensions defined by the attribute views and to define the restricted and calculated measures
• Semantics – To simply enhance the selected attributes and define input parameters if any

Select the SBOOK table in the data foundation. Select the required fields for the table and add the restriction not cancelled.

Then, go to the logical join node and add the attribute views AT_SFLIGHT_1, AT_PASSENGER, AT_GREG_TIME. Join the fact table with the attribute views.

As a final step, select the measures in the Semantics section.

In our example, the flight price in local currency of the airline (LOCCURAM) and the baggage weight (LUGGWEIGHT) are used as measures.

Activate the view and see the data preview.

Similarly create the analytical view AN_SEAT_UTILIZ, use SFLIGHT as the fact table and attribute view AT_GREG_TIME as time dimension so that the seat utilization can be analyzed with respect to month or quarter.

Calculated Key Figures in the Analytical View

Calculated key figures act as virtual columns in the analytical view. You can define them in the Star Schema node.

For AN_FLIGHT_BOOKING analytical view created earlier, create a calculated key figure to determine excess baggage for bookings. Define it as LUGGWEIGHT>20. You need to first convert the LUGGWEIGHT to KG and store these values in a new column LUGGWEIGHT_KG.

For AN_SEAT_UTILIZ analytical view, create calculated column to determine the percentage  utilization (based on number of available and occupied seats).

Divide the occupied seats with maximum seats in all three categories. For the column values to be handled as decimal number, the type is converted using decfloat.

IF ("SEATSMAX">0, decfloat("SEATSOCC" + "SEATSOCC_B" + "SEATSOCC_F") / 

decfloat("SEATSMAX" + "SEATSMAX_B" + "SEATSMAX_F"), 0)

Calculated columns are defined in similar way as in attribute views. However, in analytical views you additionally need to specify the column as measure in the semantics and also specify whether it is determined before or after aggregation. Usually the key figures are evaluated before aggregation (using raw data).

Currency Conversion and Unit conversion

Analytical views support currency and unit conversions.

For our analytical view, AN_FLIGHT_BOOKING, we will convert the flight price to EUROS and baggage weight to Kilograms.

Following parameters are important for currency conversion – source currency, target currency, key date, exchange rate type and exchange rate.

Create an attribute view on the TCURR table. The dates in GDATE column need to be converted to correct format. Create a calculate measure and define it like below:

<>

The exchange rates will be taken from this attribute table from the column ‘UKURS’.

<>

Include this Attribute view in the analytical view AT_SFLIGHT_1 and define join on MANDT field.

Please note: If TCURR table does not have all the clients data, you may get error while conversion in the analytical view. If possible, insert missing entries into TCURR table.

<>

Create a new calculated measure and specify the details in the ‘Advanced’ tab.

<>

The desirable target currency and the key date can be enabled for user selection. This can be done using Input parameters in Analytical views.

In the star schema node, select the folder ‘Input parameters’ and select ‘new’. Specify the name, label, target currency default value, data type and length.

The same steps are used for unit conversion. Define a new calculated column LUGGWT_KG. Specify the unit column and target unit as shown below.

This calculated measure is used in another calculated measure to determine whether the weight of baggage in KGs is more than 20 KG. The value is ‘1’ if yes and ‘0’ if no. This is defined previously in the same post.

Runtime Objects and SQL Access for Analytical Views

A primary runtime object is created for analytical views. Also there are several column views created for hierarchies, calculated measures and key figures for the analytical view.

For a modeler, these runtime objects have no importance.

When accessing an analytical view using SQLScript, you cannot use single access, you have to always read rows along with aggregation like SUM, COUNT etc.

For example, the statement SELECT * FROM <view name> – is not supported.

Instead, use the statement SELECT columns.. FROM <view> where …  group by …

If you have used input parameters for the view, you can pass them in the SQL query like below:

SELECT columns.. FROM <view> (PLACEHOLDER = (<name of input parameter>, <value>)) where …  group by …

Please note: In ABAP, you cannot specify Input parameter using Open SQL. You need to use Native SQL.

To learn more, go for complete course in SAP HANA Modeling.

[simple-author-box]