Skip to content
NextLytics
Megamenü_2023_Über-uns

Shaping Business Intelligence

Whether clever add-on products for SAP BI, development of meaningful dashboards or implementation of AI-based applications - we shape the future of Business Intelligence together with you. 

Megamenü_2023_Über-uns_1

About us

As a partner with deep process know-how, knowledge of the latest SAP technologies as well as high social competence and many years of project experience, we shape the future of Business Intelligence in your company too.

Megamenü_2023_Methodik

Our Methodology

The mixture of classic waterfall model and agile methodology guarantees our projects a high level of efficiency and satisfaction on both sides. Learn more about our project approach.

Products
Megamenü_2023_NextTables

NextTables

Edit data in SAP BW out of the box: NextTables makes editing tables easier, faster and more intuitive, whether you use SAP BW on HANA, SAP S/4HANA or SAP BW 4/HANA.

Megamenü_2023_Connector

NextLytics Connectors

The increasing automation of processes requires the connectivity of IT systems. NextLytics Connectors allow you to connect your SAP ecosystem with various open-source technologies.

IT-Services
Megamenü_2023_Data-Science

Data Science & Engineering

Ready for the future? As a strong partner, we will support you in the design, implementation and optimization of your AI application.

Megamenü_2023_Planning

SAP Planning

We design new planning applications using SAP BPC Embedded, IP or SAC Planning which create added value for your company.

Megamenü_2023_Dashboarding

Dashboarding

We help you with our expertise to create meaningful dashboards based on Tableau, Power BI, SAP Analytics Cloud or SAP Lumira. 

Megamenü_2023_Data-Warehouse-1

SAP Data Warehouse

Are you planning a migration to SAP HANA? We show you the challenges and which advantages a migration provides.

Business Analytics
Megamenü_2023_Procurement

Procurement Analytics

Transparent and valid figures are important, especially in companies with a decentralized structure. SAP Procurement Analytics allows you to evaluate SAP ERP data in SAP BI.

Megamenü_2023_Reporting

SAP HR Reporting & Analytics

With our standard model for reporting from SAP HCM with SAP BW, you accelerate business activities and make data from various systems available centrally and validly.

Megamenü_2023_Dataquality

Data Quality Management

In times of Big Data and IoT, maintaining high data quality is of the utmost importance. With our Data Quality Management (DQM) solution, you always keep the overview.

Career
Megamenü_2023_Karriere-2b

Working at NextLytics

If you would like to work with pleasure and don't want to miss out on your professional and personal development, we are the right choice for you!

Megamenü_2023_Karriere-1

Senior

Time for a change? Take your next professional step and work with us to shape innovation and growth in an exciting business environment!

Megamenü_2023_Karriere-5

Junior

Enough of grey theory - time to get to know the colourful reality! Start your working life with us and enjoy your work with interesting projects.

Megamenü_2023_Karriere-4-1

Students

You don't just want to study theory, but also want to experience it in practice? Check out theory and practice with us and experience where the differences are made.

Megamenü_2023_Karriere-3

Jobs

You can find all open vacancies here. Look around and submit your application - we look forward to it! If there is no matching position, please send us your unsolicited application.

Blog
NextLytics Newsletter Teaser
Sign up now for our monthly newsletter!
Sign up for newsletter
 

How to improve the performance of your SAP HANA database

Without a doubt, our daily lives become increasingly data-driven and the amount of data is continuously increasing every day. Thus, the amount of data that is generated and processed in your company across all areas is also growing with each passing day. With this exponential data growth, the performance of a business warehouse is particularly important.

One way to increase the performance of your business warehouse is SQLScript. Especially if you use a lot of custom logic, SQLScript can really help in terms of performance. With the help of SQLScript in SAP HANA, data-intensive calculations can be carried out at the database level. Which has led to a paradigm shift - instead of loading the data from the database to the ABAP server to perform the calculations there ("data to code" paradigm), the calculation is now performed directly in the SAP HANA database, meaning the code is brought to data.

Calculation approach

SQLScript offers a wider range of functions than SQL and also allows flow control. It enables you to enhance the performance of your SAP HANA database without restricting yourself to the Open SQL functions. This is because HANA in itself represents a very smart database. In the following, I will discuss the components of HANA that make it so special.

SAP HANA is primarily known as an in-memory database. Instead of the hard drive, the computer's main memory is used to store the data, which enables much faster access. This means that even large amounts of data can be processed with high performance.

However, the core of HANA technology does not lie in massive working memory performance, but in the novel database architecture. The high performance is only made possible by the combination of four already known technologies: reading and writing in both row and column format, compression, encoding as well as insert-only principle. In the next chapters I explain these components in detail.

Line and column format brought under one roof

The HANA database can operate in both row and column format. While the column format enables fast read access, the row format is more efficient for write operations.

In the following, I explain the respective approach using an example. Let's look at the table below:

ID

Name

Department

Salary

1

Sebastian

Controlling

115.000

2

Patrick

Controlling

110.000

3

Dirk

IT

110.000

 

However, this table represents only the presentation view. In fact, the information is stored in the database in a different form. For example, the contents of the table shown above could be stored in a row-based database as follows:

1, Sebastian, Controlling, 115000;

2, Patrick, Controlling, 110000;

3, Dirk, IT, 110000;

In a column-based database, the same table would look like this:

1, 2, 3;

Sebastian, Patrick, Dirk;

Controlling, Controlling, IT;

115000, 110000, 110000;

The column-based approach leads to the following advantages. For example, if you want to evaluate salaries and see the sum of all salaries, the column-based database requires only one read access. Thus, only the last row with the salaries needs to be read.

A row-based database, on the other hand, would have to go through all the rows and read the salary information. In our example, it would correspond to three reads, which of course takes longer. Three times as long as the column-based approach.


Increase the performance of your BW with SQLScript

Neuer Call-to-Action


The same is true if you want to evaluate the total number of employees. While a column-based database gets by with one read access, the row-based database requires three accesses.

However, the column-based databases have a significant disadvantage. Let's assume that our company hires another employee, so our table looks like this:

ID

Name

Department

Salary

1

Sebastian

Controlling

115.000

2

Patrick

Controlling

110.000

3

Dirk

IT

110.000

4

Steffen

IT

115.000

 

For a row-based database, only one new row needs to be added. After that, the database view would look like this:

1, Sebastian, Controlling, 115000;

2, Patrick, Controlling, 110000;

3, Dirk, IT, 110000;

4, Steffen, IT, 115000;

With a column-based database, on the other hand, all four rows would have to be adjusted. After that, the database would have the following content:

1, 2, 3, 4;

Sebastian, Patrick, Dirk, Steffen;

Controlling, Controlling, IT, IT;

115000, 110000, 110000, 115000;

As you can guess, it takes longer than the row-based database. While the column-based databases score high in reading the data, the row-based databases are faster when updating the data. This is the reason why in the past the analytical systems, such as SAP Business Warehouse, tended to use column-based databases. The transactional systems, such as SAP ERP, on the other hand, ran on row-based databases. Thus, a segmentation according to application type took place.

An SAP HANA database combines the best of both worlds: information can be read column-by-column in the blink of an eye and new data can be added row-by-row just as quickly.

Compression

In addition to column and row access, the built-in compression mechanism also helps to increase the performance of a HANA database. This is because the information within a database is often repeated. For example, there are only just under 200 countries in the entire world, in Germany there are 16 federal states, and in a client-dependent database table there is often only one client. Therefore, in a database with millions of data records, the same characteristics occur several times.

For instance, let's look at our table. The controlling department appears twice in the data, as does the IT department.

ID

Name

Department

Salary

1

Sebastian

Controlling

115.000

2

Patrick

Controlling

110.000

3

Dirk

IT

110.000

4

Steffen

IT

115.000

 

If you were to simply write 

2x Controlling, 2x IT;

instead of 

Controlling, Controlling, IT, IT;

at the database level, you could reduce memory requirements enormously. Think, for example, of the gender characteristic in a database with millions of records.

Encoding

Coding is another approach to increase performance. For example, the characteristic Controlling in the column Department is replaced by an integer number. In this way, the same information can be stored in a much more space-saving manner than in an ordinary database, which further increases performance. Our example table could look like this.

ID

Name

Department

Salary

1

1

1

1

2

2

1

2

3

3

2

2

4

4

2

1

 

Partitioning

In addition, the information stored in a SAP HANA database is processed in parallel by several CPUs, whereby the cache is also used. This way, the last piece of performance can also be tickled out.

Insert Only

We expect from a database that a SELECT statement returns only the records valid at the time of the query. If a data record is being changed during the call, but the change has not yet been saved, the last valid status should be returned.Therefore, the original value must be stored somewhere temporarily.

Whenever a record is overwritten, the old state is copied to the rollback segment of the database. This approach has its price. Even a simple update requires two operations - the actual change and backup of the original version.

A different approach was chosen for a SAP HANA database. Instead of overwriting old records, new ones are added with a timestamp. During a query, only the new data records are read and displayed. This way, write operations can be performed more quickly. To prevent the database from growing indefinitely, the different versions of the data are consolidated during delta merge.

SAP HANA database - Our conclusion 

In themselves, the individual components of HANA do not represent a groundbreaking innovation. What makes SAP HANA so special is the combination of individual components. This maximizes the advantages and minimizes the disadvantages at the same time. One disadvantage remains, however: by using a HANA database, you are tied to SAP and can no longer use third-party databases such as Oracle. But the advantages clearly outweigh the disadvantages, because no other provider has a comparable product in their portfolio. Would you like to learn more about SAP HANA and the possible applications in the business warehouse environment? We will gladly support you! Contact us now.

Learn more about  SAP HANA SQLScript

avatar

Chris

Chris Fidanidis has been working in the SAP BW environment since 2007. During these years he has implemented several planning projects and used various SAP tools such as SAP BSP, SAP BW-IP, SAP BPC, SAP BW Embedded BPC. He has gained experience primarily as a developer, architect, project manager and team leader. He enjoys playing basketball and barbecue whenever possible.

Got a question about this blog?
Ask Chris

Blog - NextLytics AG 

Welcome to our blog. In this section we regularly report on news and background information on topics such as SAP Business Intelligence (BI), SAP Dashboarding with Lumira Designer or SAP Analytics Cloud, Machine Learning with SAP BW, Data Science and Planning with SAP Business Planning and Consolidation (BPC), SAP Integrated Planning (IP) and SAC Planning and much more.

Subscribe to our newsletter

Related Posts

Recent Posts