Introduction
SAP HANA, for High-performance ANalytic Appliance, is designed to be a high-performance database management system. The data transformation tools it offers within its interface and the architecture of the software make it relevant to its users. This article looks at the design and key features of the tool, which make it a leading orchestra conductor for data management.
SAP HANA: optimised data management
SAP HANA uses in-memory storage, which consists of storing compressed data in RAM, making it possible to read and edit data and thus gain in efficiency compared with other disk-based systems. To store its data, SAP HANA has columnar storage instead of online storage. Explanations:
Stored online, the previous table would be stored as follows:
Once the data has been organised into columns, the attributes can be aggregated to further compress the data:
This also allows sequential access, which is also very advantageous. As online storage is sometimes more suitable – the data is completely re-sorted when it is updated – SAP HANA uses the most suitable storage method and transfers the data. When a table is created manually, it is possible to choose the type of storage for this table, bearing in mind that by default it is a table of columns.
In terms of managing calculations, SAP HANA operates them as far as possible at the data storage layer, freeing up the application layer to focus on its key processing functions.
Subscribe to the Rapid Views Newsletter !
Stay up to date with our latest blog posts, upcoming webinars and news!
Data analysis and performance
SAP HANA enables users to model analytical views. These consist of building a schematic data model that processes data persistently. Using this type of view as opposed to SQL queries has a number of advantages, particularly in terms of performance.
SAP HANA’s modelling reduces the number of joins compared with a traditional SQL model: the modelling supports calculations based on the fields available in the same table, unlike SQL, which requires you to go through nested sub-queries. This makes data manipulation operations considerably lighter. Pre-calculating tables using data persistence also reduces the number of joins, and means that data that has already been aggregated can be injected, saving on RAM allocation.
Best practices for building an optimised analytical view
SAP offers a number of recommendations for building a high-performance view.
Setting cardinalities for joins: although not mandatory, this parameter is required to speed up the search. The cardinality indicates the number of rows in a table which corresponds to the number of rows in the joined table. The match is made on one or more (n) rows. To identify the cardinality between two tables, the user can use the keys of each table, which indicate a unique record. In general, a left join between a fact table and a dimension table has a cardinality of N to 1.
Illustration: in a join between a customer order table (header data) and a customer description table (name, address, etc.), each customer reference corresponds to a single line of description. Conversely, each description can be assigned to several lines in the order table, since the same customer will place several orders.
The advantage of entering cardinality in terms of performance therefore lies in the fact that when the customer name (right-hand table) is assigned to the customer reference (left-hand table), the tool knows that it can stop browsing the other lines in the table. If the cardinality was not indicated, it would have scanned the entire table, uselessly, as no other results would have been found. This operation would have been performed for each record in the left-hand table.
The cardinality is set in the join editor:
Key management: also a non-mandatory parameter, it is nevertheless essential to associate keys in the semantic layer of the analytical view in the interests of performance. SAP HANA automatically creates indexes on these key fields, which is relevant given that SAP HANA does not take into account the creation of indexes on analytical views. The key fields of a table or view are those that enable a single row to be identified.
To understand this concept, we can take the example of the sales order table again, but this time at item level. Each line will be unique as soon as the order reference/item reference pair is read: the key fields are then identified.
A view’s keys are set at the semantic level:
Correct management of record quantities: it is vital to correctly manage the number of rows in the construction of a view. Two views modelled differently, but with the same input and output data, may require very different quantities of resources depending on how they are constructed. The designer must bear in mind that each modelling operation must be carried out on the smallest possible number of lines. This implies a number of recommendations:
Place filters as close as possible to the tables being queried
Encapsulate the tables in a projection to filter the data immediately
Perform calculations on the smallest possible number of records: in general, in a projection close to the semantics, after filters and aggregations.
Table joins should be performed as a priority on key or indexed columns to speed up the search.
Reuse of existing views rather than creating a model for each requirement. Thanks to the persistence of analytical views on SAP HANA, calculations do not need to be performed again. It is also with this in mind that SAP HANA makes available the Star Join object, which consists of making dimension views (including attributes, which are views without measurements) gravitate around a factual view. In addition to the obvious time savings at the design stage, the performance gains are immense thanks to the persistence of the views.
It is therefore advisable to build dimension views to model frequently required attributes, even if there are only fields: for example, a VAT code and its description. The use of star joins is strongly recommended by the editor.
Performance analysis
SAP HANA offers a diagnostic and performance analysis tool to confirm the correct management of modelled views: PlanViz. To use this tool, you need to switch to the SAP HANA PlanViz perspective.
To analyse a view, you need to query its data using the SQL script :
From the SQL console, right-click to run the performance analyser:
Performance information is then available at a global or detailed level, since it is possible to enter each operation detail:
Performance information is also provided in the ‘timeline’, ‘operator list’, ‘tables used’ and ‘performance trace’ tabs.
Warnings about the number of records can be set in the SAP HANA studio preferences (SAP HANA > Modeler > Performance Analysis), as can the colour codes for operations (SAP HANA > PlanViz > Graph > Appearance):
Conclusion
SAP HANA is an ideal tool for setting up a high-performance environment capable of efficiently processing very large volumes of data. The use of analytical views as an alternative to SQL queries is highly recommended in cases where performance is weakened. Good modelling practices are essential for obtaining a more efficient model.
The calculations and formulas available in the analytical view modelling engine are sometimes limited; to overcome the few blocking situations, the view designer may need to use function tables; these are also high-performance, because they are persistent, and above all, can be inserted into calculation views to take advantage of the benefits of analytical view modelling.
L’avis de l’expert
SAP HANA is a high-performance, efficient data management solution. Its in-memory architecture and columnar storage guarantee fast processing performance. It is important to use good modelling practices: one of the key but underused modelling features on SAP HANA is Star Join, which reduces joins and improves response times. Star Join has many advantages: thanks to the persistence of analytical views, calculations are optimised and performance remains constant even with large amounts of data.
SAP HANA stands out for its ability to deliver relevant results in record time. Its innovative approach enables users to take full advantage of their data and make decisions based on better-controlled information.
