Key Concepts Refresher

In the previous chapter, we saw how a generated rank column can be added to the list of output fields in a calculation view. In this chapter, we will learn about four more such additional output fields. We will see how to add calculated columns, restricted columns, counters, and currency conversion columns to the list of output fields in our calculation views.

In our discussions on these topics, see if you can identity which feature solves each problem described in the real-world scenario described above.

Let’s start with the first of our eight topics on how to enhance our SAP HANA information views.

Calculated Columns

Up to this point, we have created information views using available data sources. However, quite often, we’ll want SAP HANA to do some calculations for us on that data to, for example, calculate the sales tax on a transaction.

In the past, this was usually done using reporting tools. Now, SAP HANA has all the data available, and can do the calculations without having to send all the data to the reporting tool. It does the calculations faster than reporting tools, and sends only the results to reporting tools, saving calculation time, bandwidth, and network time.

Figure 7.1 illustrates how calculated columns work. When we create calculated columns in SAP HANA, it adds additional output columns to our information views. The calculated results are displayed in these calculated columns.

Figure 7.1 Calculated Columns Added as Additional Output

On the left side of Figure 7.1 is our source data from a table. We added two calculated columns on the right. The first calculated column calculates discounts and the second one calculates the sales tax.

[»] Note

The calculated columns are part of the information view. The results are not stored anywhere, but are calculated and displayed each time the view is called.

Calculated columns are available in the join, star join, projection, and aggregation nodes. They are not available in the rank and union nodes.

Creating Calculated Columns

In the SAP HANA studio, we create Calculated Columns in the Output area. In the context menu of the Calculated Columns, we select the New option, as shown in Figure 7.2.

Figure 7.2 Create a New Calculated Column in the Output Area

In the Create a Calculated Column dialog screen, shown in Figure 7.3, we first specify the name and data type of the calculated column.

Figure 7.3 Use the Expression Editor to Create the Formula for Calculated Columns

The Expression Editor takes up the rest of the space in the dialog screen. The focus is the formula (called the expression) in the middle.

The three areas at the bottom help us to create the expression:

• Elements
  The Elements area contains all available columns, previously created calculated columns, and input parameters (see the Input Parameters section). We can build our new calculated column on previous calculations, to build up more complex calculations.
• Operators
  The Operators area contains operators, for example, for adding, multiplying, comparing, or combining columns.
• Functions
  The Functions area contain various useful functions, like converting data types, working with strings, doing the basic mathematical calculations, and working with dates. We also have some extra functions, for example the if() function for if-then-else calculations.

All the examples here were using measures. However, we can also create calculated columns for attributes as well. The functions allow us to work with strings and dates as well, so we can create expressions using attributes.

The Language selection allows us to either use a normal expression, which SAP HANA will translate to SQL for us, or directly use SQL syntax.

[+] Tip

We recommend that you use the mouse to click on the various components. You can type the expression directly in the text area, but we found that many people either mistype a column name, or forget that SAP HANA column names can be case-sensitive. By using the mouse, SAP HANA studio assists you with the typing.

The Expression Editor also helps us with on-the-fly syntax checking. In Figure 7.4, we see an example of this. In the top example, the Expression Editor highlights the error, even with a tooltip. When we inserted the missing “>” sign, the Expression Editor displays the formula in green text to show that it contains no errors.

With most, if not all, of the enhanced features we discuss in this chapter, we have the ability to add comments. In Figure 7.4 we commented what the formula meant. This will remind us later what we did, or inform our team members why we wrote the expression the way we did.

Figure 7.4 On-the-Fly Syntax Checking in the Expression Editor

Calculate before Aggregation in Analytic Views

In analytic views we have an additional flag (tick box) in the Expression Editor called Calculate Before Aggregation, as shown in Figure 7.5. The Calculate Before Aggregation flag is only applicable for measures, and by default this is switched off.

As you know, one of our main modeling rules is “Calculate as late as possible.” With addition and subtraction we can delay the calculations to as late as possible. There are cases, for example, with multiplication and division, where we have to do calculations earlier than normal. An example would be when we want to aggregate totals for quantities and price per unit. If we add all the prices first, and all the prices per unit, and then multiply these two sums, we will get totally inaccurate results. In this case we have to do the quantity multiplied by price per unit calculations first for each record, and then we can add up the total costs. To do this, we need to manually enable the Calculate Before Aggregation flag under the Client Aggregation field (see Figure 7.5).

Figure 7.5 Calculate Before Aggregation Flag

The Calculate Before Aggregation flag is only in the Expression Editor for analytic views. In calculation views, SAP HANA knows when to do the calculations earlier for accuracy, and does not require us to set a flag.

Counters

In aggregation and star join nodes we sometimes want to know how many times in the result set a certain item occurs for an attribute. For example, we might want to know how many unique items were sold in each shop to find the shops with the largest variety. We do this using counters.

The New Counter option is found in the calculated column’s context menu in the Output area of aggregation and star join nodes (see Figure 7.6). In our example, we will put the counter on items sold.

Figure 7.6 Create a New Counter

We can also use multiple columns to expand our example to add item categories, for example, to show which shop had the largest variety of items for meats, the largest variety of items for the bakery section, etc.

Restricted Columns

The easiest way to explain restricted columns is to think of a reporting requirement that you frequently encounter. For example, let’s say we have a website where we work with the data of who visited the website. We use a view like the one shown in Figure 7.7. Our data from the source table is in the two columns on the left. We can see how many users connected with which type of device, organized per row.

We would like our report to be like the bottom right of the screen in Figure 7.7, where we have a column for each device, with the total number of users for each device. You might know this as a pivot table. We already saw in Chapter 4 that we could use a union with constant values to achieve a similar result.

We can also achieve this by creating a restricted column for each device. This adds a new additional output column to our view. When we create a restricted column for the mobile phones, we restrict our column to only show mobile phone data. Everything else in that column is NULL. When we now aggregate the restricted column, we get the total number of users visiting the website on mobile phones. We create a restricted field for each device to create our report.

Because we need aggregation for this to work properly, restricted columns are only available in the aggregation and star join nodes.

Figure 7.7 Creating Four Restricted Columns and Aggregating the Result Sets

[»] Note

We create restricted columns on attributes. For example, it doesn’t make sense to create a Reporting column with a heading of ‘25’ (measure). However, a Mobile phone column (attribute) provides more useful information.

Creating Restricted Columns

Creating a restricted column is similar to creating a calculated column. In the context menu, as shown in Figure 7.8, we choose the New option.

Figure 7.8 Create a Restricted Column

In the dialog screen for creating the restricted column, we specify the name of the restricted column and what data we want to display in the column.

In the Restrictions area, we can add one or more restrictions, for example, the year has to be 2016, the device has to be a mobile phone, or, as shown in Figure 7.9, if we only want to see the financial information for invoices.

If we add multiple restrictions, SAP HANA automatically creates the relevant AND and OR operators. For example if we have two restriction for the year 2015 and 2016, the Expression Editor will use year = 2015 OR year = 2016.

Figure 7.9 Create a Restricted Column for the Net Amount of Invoices

When we enter a value, like 2016 for the year, we can ask the Expression Editor to give us a list of existing values currently stored in the system. In Figure 7.10 we asked for a list of invoice statuses. In the list, we see I for Invoices, and D for Delivery notes. This popup list is called the value help list. You will find these value help lists in all the modeling screens.

Figure 7.10 Choosing a Restriction Value from a Predefined List of Values

In the restricted column creation dialog, in the Restrictions area, we can choose to use an Expression rather than specify individual Column values. We can then use the Expression Editor to generate the relevant portion of the SQL WHERE statement directly.

If you first filled in the values in the Column view, and then change to the Expression view, the Expression Editor automatically convert this for you, as shown in Figure 7.11.

Figure 7.11 Use the Expression Editor to Create Restrictions on Restricted Columns

Creating Restricted Columns Using Calculated Columns

You can also create a restricted column using a calculated column. Just use the expression if(“Device”=’Mobile phone’, “Number of users”, NULL). The if() function in the Expression Editor works similarly as the IF function in Microsoft Excel. If the condition (first part with the equal sign) is true, then show the second part, otherwise show the last part.

Just like with a calculated column, a restricted column does not store data. Each time an information view is used, the restricted column is created and aggregated.

[+] Tip

A restricted column differs from a filter or a SQL WHERE condition.

If you filter on the year 2016, all the data in the view will be trimmed to only show the data for 2016. When you create a restricted column for the year 2016, only the data in that column is trimmed for 2016.

Filters

Sometimes we do not want to show all the data from a specific table. For example, we might only want to show data for the year 2016. In this case, we can apply a filter. Filters restrict the data for the entire data source.

When we apply filters, SAP HANA can work a lot faster because it doesn't have to work with/sift through all the data. In the Scenario area, select the projection node of your view. Then, in the Details area, select the data source, select the field, and right-click for the context menu. From the context menu, select Apply Filter, as shown in Figure 7.12.

In the popup dialog box, we can select the Operator from the dropdown. Normally, it would be Equal, but there is a whole list of other operators available in the dropdown list. We then add the filter value by either selecting it from the dropdown or typing it in.

When the filter has been applied, you will see a Filter icon next to the field name in the table.

Figure 7.12 Apply a Filter to a Field in the Projection Node

Filter Expressions

In the Output area, we also see a Filter icon next to the output field, as shown in Figure 7.13. At the bottom of the Output area we find the filter Expression option.

Figure 7.13 Open Filter Expression Dialog from the Output Area

When we open the filter expression, we see the dialog screen shown in Figure 7.14. Just as with the Expression Editor for calculated and restricted columns, we can set the value for the columns, or use the SQL syntax.

Filters and filter expressions are only available in projection nodes.

Figure 7.14 Edit a Filter Expression.

Filtering for SAP Clients

Filtering of data can be implemented more widely than the examples we have just seen. In Chapter 5, of the Session Client section, we already discussed how to set the SAP HANA studio preferences to only show data coming from an SAP ERP system for a specific SAP client. In Chapter 6, of the Semantics Node section, we saw how to set an option for filtering to an SAP client in the semantics node. Chapter 8 and Chapter 13 also provide further details on filtering for an SAP client.

Domain Fix Values

SAP ERP uses the ABAP programming language, which has some powerful data dictionary features. In ABAP, we can define re-usable data elements (like data types). We can use these data elements in our table definitions.

Sometime we want these data elements to only allow a range of values. We define a domain with the range of values, and use this domain with the data element. In all the tables where that specific data element is used, the users can now only enter values that are in the specified range.

Instead of a range, we can also specify fixed values for the domain. This is known as the domain fix values. These are values that will not change, and the users can only choose from the available list when they enter data into the fields.

Let’s look at some examples: Maybe the users need to enter a gender. The domain values are fixed to male and female. This is very similar to the value help shown in Figure 7.10. Another example could be the possible statuses of a sales order. The sales order statuses could be quoted, sold, delivered, invoiced, and paid.

The lists of fixed values are stored in the DD07L and DD07T tables in the SAP ERP system. Because these tables are so valuable for business systems, many times we replicate (or import) these tables to SAP HANA. We can then use these values in SAP HANA for the value help dialog screens, similar to Figure 7.10.

These domain fixed values can now also be used to filter business data, for example we can filter the sales data to the domain fixed value of paid to see only the sales that are already paid.

Variables

In the previous section, we looked at filtering. These filters were all static, in the sense that we could not change them once we specified them. Each time you query the view, the same filters are used.

Frequently, however, we’ll want the system to ask us at runtime what we would like to filter on. Instead of always filtering the view on the year 2016, we can specify which year’s data we want to see. If we query the view from a reporting tool, we even expect the reporting tool to pop up a dialog box and ask us what we would like to filter on.

This dynamic type of filtering is done with variables.

We define variables in the Semantics node of a calculation view. In Chapter 6, we discussed the first two tabs available in the Semantics node. Now we will look at one of the remaining two tabs, called Parameters/Variables.

In Figure 7.15 we see the Semantics node. In the Parameters/Variables tab, we select the Create Variable option by clicking the plus icon to reveal the dropdown menu.

Figure 7.15 Create a Variable in the Semantics Node

In the Create a Variable dialog screen (Figure 7.16) we start by specifying the Name of the variable.

We have to specify the Attribute on which we want to create our variable. This is the field on which we want to implement our “dynamic filter.”

In the Selection Type we can choose what type of variable we want to create. There are three types of variables:

• Single value
  The variable only asks one (single) value, for example, the year is 2016.
• Interval
  The variable asks for from and to values, for example, the years from 2012 to 2015.
• Range
  The variable asks a single value, but combines it with another operator, for example, the year is greater and equal to 2012.

Figure 7.16 Create a Variable

We can specify a Default Value. If the user selects Cancel or presses in the Value Help Dialog, like the one in Figure 7.18, the variable will then use this default value.

When the Multiple Entries flag is enabled, we can use the key when selecting (multiple) entries. We can select, for example, the years 2011, 2014, and 2015 from the value help dialog.

By default the variable uses the view or table that it is based on to display a list of available values in the value help dialog. Changing the View/Table for value help option allows us to specify a different view or table for the list of available values in the value help dialog.

Ideally, the data source we specify should be in sync with the view we are using the variable on, else the users might choose a value from value help dialog which is not available in the current view, and therefore will not be shown any information. The reason we do this is to ensure we have consistent value help lists for all our different views by specifying the same value help data source for all the views. Sometimes this can also help to speed up the display of the value help.

With the Hierarchy option we can link a hierarchy to our variable. Users can then drill down using this hierarchy when they want to select a value from the value help dialog. We will learn to set up hierarchies later in this chapter.

Once we have created the variable, it will show up in the Columns tab of the Semantics node, next to the name of the attribute we selected. You can see an example in Figure 7.17.

Figure 7.17 Variable Shown in the Columns Tab of the Semantics Node

When we preview our calculation view, a Variable/Input Parameters Values dialog screen will appear in the SAP HANA studio. We have to supply these values before SAP will run our view, and return the results.

When we select the ellipsis (...) menu button, the Value Help Dialog will appear with a list of available values that we can choose from (see Figure 7.18).

Figure 7.18 Variable Value Help Dialog

Input Parameters

Input parameters are very similar to variables. However, while variables are specifically created for “dynamic filtering,” input parameters are more generic. For example, we can use them in formulas or expressions.

Let’s look at an example: An outstanding payments report asks you for how many months outstanding you want to see the results. This time period can then be used in a calculated column’s expression to calculate the results we asked for. The same report can be used by the debt collecting department for payments outstanding longer than two months, while the legal department will ask for payments outstanding longer than six months.

Input parameters are created in the semantics node (see Figure 7.19). We can also create input parameters in all the nodes types, except in a union node.

Figure 7.19 Create an Input Parameter

The Create an Input Parameter dialog screen is shown in Figure 7.20. The various dropdown menu options are shown on the right side of the screen.

We start with the Name of the input parameter. The Parameter Type specifies what type of data we expect to use this input parameter for.

There are five types of input parameters we can create:

• Direct
  The end users directly type in the value. We can specify the Data Type of the expected value. There are three data types we can use for Direct, namely Currency, Unit of measure, and Date.
• Column
  The end users can choose a value from all the possible values in a specified column in the current view.
• Derived From Table
  The end users can choose a value from data in another table.
• Static List
  The end users can choose a value from a pre-defined (static) list of values.
• Derived From Procedure/Scalar Function
  The end users can choose a value from a list of values generated by a scalar function or a procedure.

The Default Value can be typed in as a single value, or it can be an expression. Similar to variables, if the user selects Cancel or presses in the value help dialog, the input parameter will use this default value.

Figure 7.20 Create an Input Parameter with the Dropdown Menu Options

In Figure 7.20, we created an input parameter called DEMO_INPUT_PARAM. We can now use this input parameter, for example, in a calculated column. When we use the input parameter in an expression, we add a $$ at the start and end of the name of the input parameter. This makes it very clear that we are referring to an input parameter. The name of our input parameter will become $$DEMO_INPUT_PARAM$$.

In Figure 7.21 we use the input parameter in an expression. The expression,

daysbetween(“DELIVERYDATE”, $$DEMO_INPUT_PARAM$$)

calculates the number of days between the date that the end user supplies and the delivery date.

In this case, the input parameter has a Parameter Type of Direct, and a Data Type of Date.

Figure 7.21 Use an Input Parameter in an Expression for a Calculated Column

[+] Tip

We have $$CLIENT$$ and $$LANGUAGE$$ built into SAP HANA. These do not ask for values at runtime, but instead give the SAP client number filter currently in use, and the log-on language of the end user.

When we preview the calculation view in SAP HANA studio, and the input parameter has a Data Type of Date, a calendar is displayed where you can choose the date you want (see Figure 7.22).

Figure 7.22 Date Input Parameter

Currency

SAP is well-known for its business systems. An important part of financial and business information models is their ability to work with currencies.

In Figure 7.23 we can see the Output area of a calculation view. We have a few output fields that are currency fields, for example, GROSSAMOUNT, NETAMOUNT, and TAXAMOUNT.

When we look at the Properties of these fields, they are treated as numeric fields (measures). We can improve on that by changing the Semantic Type property to Amount with Currency Code.

Figure 7.23 Assign an Output Field to the Semantic Type “Amount with Currency Code”

When we change the Semantic Type of a field to currency, an Assign Semantics dialog screen like Figure 7.24 appears.

Most of this dialog screen is focused on currency conversion. Via the Internet, even small companies can do business internationally. Or a company can buy parts from different suppliers world-wide. Their taxes and financial reporting however happen in their home country.

When converting from one currency to another, we have a source currency and a target currency. Something else as equally important is the date, because exchange rates fluctuate all the time

But what date do we use? Do we use the date of the quote, the date of the invoice, the date of the delivery, the date the customer paid, the end of that month, or the financial year-end? All of the mentioned dates are valid. It depends on the business requirements.

In SAP systems, the exchange rate data gets stored in some tables prefixed with the TCUR name. There are many programs from banks and other financial institutions that help update the TCUR tables in SAP systems. These TCUR tables are used by the SAP business systems for currency conversions. SAP HANA also knows how to use the TCUR tables. You can easily get a copy of these tables by installing the SHINE package. It will create these TCUR tables for you to demonstrate currency conversions in its information models.

By default, most of the options in the Assign Semantics dialog screen of Figure 7.24 are unavailable. Enabling the Conversion flag opens up the entire dialog screen.

Figure 7.24 Output Field Setup for Currency Conversion

Let’s go through some of the options in this dialog screen to learn more about currency fields and currency conversions in SAP HANA:

• Currency
  We can specify in what currency the current field is.
• Decimal Shift
  Some currencies require more than two decimal places. This is indicated with this flag.
• Decimal Shift Back
  ABAP programs automatically do a decimal shift. When these ABAP programs work with SAP HANA information views, they might receive currency data from SAP HANA that has already been shifted, and a double decimal shift occurs. In those cases, we need to set this flag to “undo” the extra Decimal Shift.

Some of the currency conversion-specific options in the dialog screen include:

• Schema for currency conversion
  You have to specify in which schema the SAP HANA system can find the TCUR tables. SHINE automatically installs these tables in the SAP_HANA_DEMO schema.
• Source Currency and Target Currency
  We can specify the specific currencies codes, for example, EUR for euros and USD for United States dollars, directly in these fields. There are two other options as well:
• Column: If we are storing multiple currencies in the same column, we will have another column that specifies what currency each record is in. We can tell SAP HANA to read the currency codes from that column, and automatically convert each of the individual records.
• Input parameters: We can choose the currency code to use for the currency conversion at runtime using an input parameter. We can get a financial report to ask an end user if they want the report in euros or dollars, or pick any currency from the list of available currency codes in the value help dialog.
• We have to specify the input parameter as a VARCHAR data type with a length of 5 (see Figure 7.20 for an example).

• Conversion Date
  The date of the conversion is used to read the exchange rate information for that date from the TCUR tables.
• Generate result currency column
  The converted currency value is shown in a new additional output column with the specified name.

Some of the remaining options specify what type of currency conversion you want to retrieve from the TCUR tables, for example, are you using the buying rate or the selling rate.

Decision Tables

Decision tables enable us to easily implement business rules in SAP HANA.

The top table in Figure 7.25 shows what a typical business rule might look like: If the region is EMEA, and one of the following four conditions are true, for example, more than 100 orders are placed, then the discount is 5%.

The traditional way to implement business rules in business systems has been to hard-code the business rules into the application. This is very laborious, and has some disadvantages, including the following:

• Business users cannot update the business rules themselves. They have to always find a developer, and explain the new business rules.

  The developer then codes lots of IF statements into the application.

• New rules get tested in the development and quality assurance systems, and after a week or two finally gets deployed into the production system.
• Business users cannot do what-if analysis on production data because the process is very lengthy.

Decision tables in SAP HANA simplify and speed up the entire process. The process then becomes as follows:

1. Business users see a table with the business rules, as in Figure 7.25. They can update this table using Microsoft Excel.
2. In the background, SAP HANA converts the decision table to a procedure, with all the necessary IF statements.
3. The decision table can be used directly in a graphical calculation view. The users can immediately preview the data, and do a what-if analysis. They can tweak the business rules, using different values, and see the results very quickly in SAP HANA.
4. Once they are satisfied, they can deploy the final values for the decision table to the production system. They will need the correct authorizations in the SAP HANA system to allow them to update values in the decision table.

Figure 7.25 Create a Decision Table

The entire process becomes business-driven, rather than slowed down by the need for a developer and a database administrator. Payroll users can, for example, simulate the effects of changes in the tax rules for a new tax year in a few minutes.

We create decision tables in the same context menu where we create calculation views. From the context menu of a package, choose New, and select the Decision Table option.

A new decision table information view is created. In the Scenario area, we will see two nodes—a Data Foundation node and a Decision Table node. This is shown at the bottom left of Figure 7.25.

As always, we start working from the bottom up. In the data foundation we add any data sources that we need (the bottom right of Figure 7.25). These data sources are normally tables, but can also be calculation views, virtual tables, table functions, or table types.

The ability to use table functions or table types gives us great flexibility. We could have, for example, stores in the company that sell different products. These stores would have their own product inventory tables. Because the product tables have a similar structure, we can decide to re-use the same decision table on all the various product inventory tables. The same discount rules will thus apply to all our stores. At runtime, using the table function or table type, we can decide which of the store’s tables to send to the decision table.

Another example could be that we have moved our older 2012 data to an external database like SAP IQ. Using a virtual table, we can use the 2012 data from SAP IQ. At runtime, using the table function or table type, we can decide whether we want to use the latest data from SAP HANA, or the older data from SAP IQ. The table structures for the tables in SAP HANA and SAP IQ are the same, so we can re-use the decision table.

In the Decision Table node, we build the decision table as shown in the top part of Figure 7.25. We build this table in the Output area of the Decision Table node.

In the Data Foundation node we have selected our data sources and our output fields. These fields are available in our Decision Table node as Vocabulary Attributes. These are the five columns on the left in our example in Figure 7.25.

We want to use these attributes to decide what the discount should be for our customers. We have to create the discount column as a Vocabulary Parameter.

We can use the Vocabulary, which consists of the attributes and the parameters, to build Conditions and Actions.

From the context menu, as shown in Figure 7.26, we can choose to add the attributes as conditions and the parameter as an action.

Figure 7.26 Decision Table Node Output Fields

Once our output area is built, SAP HANA can generate a Microsoft Excel file for us. This spreadsheet will have exactly the same layout and column headings as the decision table shown in Figure 7.25. Business users can then fill in the Microsoft Excel spreadsheet, and upload it back into the SAP HANA system. When we activate the decision table, SAP HANA will automatically create a procedure for us.

We can use the decision table in a graphical calculation view, for example, in a join node, to add the discount field with all our business rules to our information view.

We can also call the generated procedure directly in SQLScript. We will look at SQLScript and procedures in Chapter 8. We pass all the conditions to the procedure using the SQLScript procedure’s input parameters.

[»] Note

Unfortunately, the phrase input parameters have two meanings in SAP HANA. The first meaning is for the input parameters we discussed in this chapter. The second meaning is that SQLScript procedures and functions can have input parameters and output parameters. In the above paragraph we specified the second meaning when consuming the decision table via SQLScript.

Hierarchies

Hierarchies are important modeling artifacts that are often used in reporting to enable intelligent drilldown. In Figure 7.27, we can see an example in Microsoft Excel. Users can start with a list of sales for different countries. They can then drill down to the sales of the provinces (regions or states) of a specific country, and finally down to the sales of a city.

Figure 7.27 Hierarchy of Sales Data from SAP HANA in Microsoft Excel

We have discussed the concepts of hierarchies in Chapter 4 already. We saw that we have two types of hierarchies, namely level hierarchies and parent-child hierarchies. We saw when we should use each type of hierarchy, and what the differences are between these two types of hierarchies.

You can use multidimensional expression (MDX) queries to use these hierarchies. Just like SQL has become a standard for querying relational databases, MDX is a standard for querying OLAP data. SAP uses BICS instead of MDX when communicating between SAP systems. BICS is a proprietary connection, so SAP can only use it for SAP systems. We use it because it is a lot faster than MDX.

Creating a Hierarchy

We create a hierarchy in the third tab of the Semantics node in a calculation view, as shown in Figure 7.28. Select the plus icon in the top-right menu on the Hierarchies tab. A Create new Hierarchy dialog box will appear.

Figure 7.28 Create a Hierarchy

After specifying the Name of the hierarchy, you can choose the Hierarchy Type. We have level hierarchies and parent-child hierarchies available (see Figure 7.29).

Figure 7.29 Hierarchy Type

To create a level hierarchy in SAP HANA, you specify the Hierarchy Type as Level Hierarchy, and then you add the different levels. On each level you select a specific field name in the dropdown. Each level uses a different column in a level hierarchy. You can also specify the sorting order, and on which column it has to sort.

In Figure 7.30, we can see an example of a level hierarchy, using geographic attributes. Level 1 is the country, level 2 is the state or province, and level 3 is the city or town.

Figure 7.30 Level Hierarchy

In Figure 7.31 you can see an example of a parent-child hierarchy. We have an HR organizational structure, with managers and employees. In this case, the manager has the “parent” role, and the employee has the “child” role.

We can also use parent-child hierarchies for cost and profit centers, or for a bill of materials (BOM).

Figure 7.31 Parent-Child Hierarchy

Time-Dependent Hierarchies

As of SAP HANA SPS 10, we can now create time-dependent hierarchies in calculation views. In the Create new Hierarchy dialog box we have another tab available for Time dependency, as shown in Figure 7.32.

The fields in the tab are grayed out, until we enable the Enable time dependency option. Once the rest of the fields become available, we supply the required Valid From and Valid To columns for the time periods.

Figure 7.32 Time-Dependency for Parent-Child Hierarchies

[»] Note

Time-dependent hierarchies are only available for parent-child hierarchies. With level hierarchies, the Enable time dependency option is grayed out.

Hierarchies in Value Help

As shown in Figure 7.16, we can use hierarchies in variables to drilldown into the list of available values in a value help dialog. In this case, the variable has to be created on the deepest level of the hierarchy, for example, in Figure 7.30 the variable has to be created at the city level.

Value help hierarchies can also be time-dependent hierarchies.