Even today, the topic of data warehouses is still evolving. New technologies and economic conditions require adjustments that outdated solutions are not necessarily able to provide.
However, gaining an overview of the wide range of modern data warehouse solutions available is no easy task. In this blog, we want to introduce you to SAP HANA SQL data warehousing and clarify what makes this DWH approach an attractive solution at present. To this end, we explain the key elements and advantages of SAP HANA SQL data warehousing and provide practical arguments in favor of the native SQL data warehousing approach based on the SAP HANA platform.
SAP HANA SQL Data Warehousing
SAP HANA SQL data warehousing is based on the idea of using the SQL-based tools of the SAP HANA platform to build a native SAP HANA data warehouse. Since the graphical tools and editors generate SQL and all communication with the SAP HANA database ultimately takes place in SQL, this approach can be described as SQL data warehousing. This feature is also one of the product's greatest strengths. SQL is the universal language of relational database management systems (RDBMS), which are still the leading systems despite all the hype surrounding big data and NoSQL. SQL, as a lingua franca of the data world, makes the solution flexible in many respects and enables extensive system openness, which promises a competitive advantage in the current times of disruptive technological progress and the increasing dynamics of business life. This applies above all in conjunction with other features of the SAP HANA platform, which we describe in the following overview.
Integration into the SAP HANA platform
data management
In recent years, data management has been particularly influenced by the term "big data," which emphasizes the characteristics that are most common today: large data volumes, great data diversity, and the speed of data production and processing (volume, variety, velocity). The in-memory technology of the SAP HANA database and comprehensive interfaces make SAP HANA SQL Data Warehousing well suited for this purpose. This makes it possible to connect to almost any data source via various connectors and to completely virtualize data access.
Advanced Analytics
Advanced analytics is a field that has become increasingly important over the past 10 years. Compared to conventional business intelligence approaches, corresponding measures differ in their focus on predicting specific events rather than just retrospective assessment. The SAP HANA platform offers the advantage of having corresponding analytics engines already fully integrated, making predictive evaluations of text, geo, or graph data, among other things, very easy within the framework of SAP HANA SQL data warehousing.
application development
A key step in SAP HANA's transition from database to platform was the introduction of its own application server (SAP HANA Extended Application Services, or XS for short). The basic idea behind this decision was to transform the usual three-tier software architecture, in which the database functions more or less as a pure data store, into a two-tier architecture. The role of the application server, as the central element for executing calculations and logic, was to be taken over by the SAP HANA database due to its high performance, thereby increasing the overall performance of queries. SAP HANA Extended Application Services Advanced (SAP HANA XSA), the second version of this application server, has been available since the end of 2015. It further optimizes full-stack development on the SAP HANA platform through a microservice architecture that supports multiple runtimes, its own development environment (SAP Web IDE), and the connection to the distributed source code management system Git. SAP HANA SQL Data Warehousing benefits greatly from these development options, as they also allow data warehouse structures to be developed in a highly agile manner (for details, see 1.2 Agile DWH Development).
Cloud
There are many options for operating SAP HANA in the cloud. In addition to setting it up in a self-managed cloud, such as Amazon Web Service, where only the infrastructure is provided (IaaS), SAP Cloud Platform offers the complete SAP HANA platform as an online service (PaaS). The offering is based on the open-source technology Cloud Foundry and does not differ in principle from the on-premise structure with the SAP HANA XSA application server. This results in great flexibility with regard to the development of a wide variety of applications, as the specific container structure of the SAP HANA platform allows them to be developed with both XSA on-premise and Cloud Foundry in the cloud and easily operated in the other environment. For SAP HANA SQL data warehousing, this means that you can first develop the DWH structures and objects on-premise and then simply move them to the cloud, or vice versa. In other words, you enjoy full cloud flexibility.
Agile DWH development
The favorable conditions for agile development of the SAP HANA SQL Data Warehouse are, in a sense, a byproduct of the continuously improved application development of the SAP HANA platform. The functions of the SAP Web IDE development environment and the underlying XSA/Cloud Foundry structure, with a differentiation between development and runtime environments, the delivery of development artifacts in containers, and the connection to the distributed source code management system Git, are ideal for the model-driven, incremental, and iterative construction of a data warehouse. Thanks to these development options, SAP HANA SQL Data Warehousing focuses on the DevOps philosophy, bringing the advantages of agile software development to the data warehouse.
Distributed source code management in Git
The source code for the database artifacts is stored in the distributed version control system (VCS) Git. This approach is common in software development because it improves the possibility of parallel development. In conjunction with the distinction between the development and runtime environments and the containerization of database artifacts, it also offers advantages for the development of the SAP HANA SQL Data Warehouse in terms of accelerating development processes.
continuity processes
DevOpsWhat is DevOps? The term DevOps is composed of the words... More First and foremost, it describes the closer integration of the areas of development and operations, or the development and runtime environment. Development artifacts that are as small as possible should pass through these two areas quickly and continuously in order to create operational added value at an early stage. In addition to specific phases, a number of continuity processes have therefore been established to support this goal (Figure 1.1). Continuous integration essentially describes the process of parallel development by several people using the Git repository, in which source code components are continuously integrated into the repository of the overall project. Continuous delivery pursues continuous delivery to production systems. Here, too, the Git repository performs essential tasks and enables automatic deployments of SAP HANA SQL DWH developments. Continuous and, in the best case, largely automated testing is also important, as is generally consistent feedback through communication between all parties involved.
Figure 01: DevOps phases and processes SAP HANA SQL data warehousing
Model-driven development
In SAP HANA SQL Data Warehousing, data modeling can be carried out in various forms, such as third normal form (3NF), multidimensional, or data vault. This freedom is experiencing ever-increasing demand. Data vault modeling, which we recommend for SAP HANA SQL Data Warehousing, has become particularly important in recent years because it offers greater flexibility in response to ongoing changes in the data model. Overall, the gradual creation of the data model is the most important task in the development of the DWH, which is why we refer to a model-driven approach. The gradual development of the models brings the experts from the business areas and IT closer together and creates a better basis for communication. In addition, the generation of objects in the related models offers potential for automation, for example through reverse engineering. The connections also create greater transparency in data usage and dependencies, known as data lineage. When developing models, but also other artifacts such as calculation views, graphical editors can be used for the most part, and the SQL code is generated efficiently in the background. This approach reduces complexity and makes it easier to get started with data warehouse development.
cloud capability
In this regard, SAP HANA SQL Data Warehousing can take full advantage of the cloud options offered by the SAP HANA platform (see 1.1 above). By containerizing developments in so-called MTAR archives, they can be easily moved back and forth between on-premise (XSA) and cloud environments (Cloud Foundry) (Figure 1.2). The SAP HANA SQL Data Warehouse is thus completely independent of the underlying technical infrastructure. This applies not only to database artifacts. Because the SAP HANA platform offers full-stack development capabilities, application logic and user interface developments can also be exchanged separately between on-premise and cloud platforms.
Figure 02: Cross-platform delivery of developments
system openness
The SAP HANA SQL Data Warehouse is a relatively open system in several respects. On the one hand, the SQL standard ensures universal connectivity options, as it is supported by most other systems and tools. This means, for example, that self-service data visualization tools, which are becoming increasingly important today, can be freely selected. Market leaders such as Tableau and Power BI, SAP solutions such as SAP Analytics Cloud (SAX), and others can be easily connected to the SAP HANA SQL Data Warehouse. In addition, there is also a high degree of openness to tools that support agile development methods. These include automation tools such as Jenkins or Bamboo and issue trackers such as Jira. These interact with the Git repository of the SAP HANA SQL Data Warehouse and respond immediately and, if desired, automatically to changes in the source code. Finally, the aforementioned flexibility with regard to on-premise or cloud platforms should also be mentioned in this context. Here, a cross-platform, open exchange of developments is possible.
Arguments from practice
The SAP HANA SQL Data Warehouse impresses with its flexibility and agility in terms of architecture and development approach. The universality of the SQL database language and the openness of the SAP HANA platform result in a high degree of freedom in terms of data structure design and data management processes. The SAP HANA platform comes with many useful tools and services that are already fully integrated. In addition, third-party software can be freely selected and integrated into the DWH landscape. With these features, the SAP HANA SQL Data Warehouse offers strengths that are currently in high demand among BI professionals looking to modernize their data warehouses.
The latest research study, "Modernizing the Data Warehouse: Challenges and Benefits,"conducted by the research and consulting institute Business Application Center (BARC), concludes that the most important trends in the BI environment are focused on more efficient, effective, and agile data management based on data control, automation, and self-service (BARC, Modernizing the Data Warehouse: Challenges and Benefits, 2019, p. 7). This thesis is based on a survey of nearly 400 experts, mainly from Europe, on the biggest challenges in data warehousing. Time-consuming development processes and limited support for self-service BI are cited as the biggest hurdles. In addition, the inability to adapt to new requirements/changes is considered a significant obstacle to more efficient data management. Our practical experience confirms these study results.
Our customers particularly appreciate the agile development options offered by SAP HANA SQL data warehousing with fast iterations and small, productive deliveries. The model-driven approach means that the specialist departments are heavily involved right from the start and, thanks to the short turnaround times, have the advantage of being able to make immediate corrections. Automated tracking with issue tracking systems such as Jira is extremely valuable here, as it allows operational decision-makers to easily track and manage the development of the data warehouse in one place. The at least partially automated delivery of developments in practice also saves effort and stress, which were previously often required on a large scale in the context of major deployments.
In addition, the open and flexible structure of the SAP HANA SQL Data Warehouse is impressive. Departments are pleased that adjustments are possible and that existing IT structures do not determine the business strategy. In practice, we generally find three scenarios in which SAP HANA SQL Data Warehousing can play to its specific strengths.
- The company has several data warehouses, as well as an SAP Business Warehouse (BW). SAP HANA SQL Data Warehousing is the perfect complement for integrating and harmonizing the entire BI landscape within the company.
- The company uses SAP software, but data warehousing was deliberately operated using other SQL approaches. Here, a switch to SAP HANA SQL data warehousing can be made with little effort in order to increase the integration of the remaining SAP software and also to leverage the performance and agility of the SAP HANA platform.
- The departments have small SQL databases that are operated by experienced users for analytical purposes. With SAP HANA SQL Data Warehousing, these departments can maintain their self-service capabilities because they can continue to use their familiar SQL expertise.
Conclusion
In this blog, we have explained the strengths of native SAP HANA SQL data warehousing and shown you what makes SAP HANA SQL data warehousing an attractive solution in the BI environment. The structural flexibility and openness as well as the cloud options of the SAP HANA platform on the one hand and the model-driven, agile development approach on the other open up new ways to redesign the DWH landscape in the company and reduce the organizational complexity of data management (Figure 3.1). This brings BI experts and business users closer together, enabling them to generate analytical insights more quickly, which are becoming increasingly important for steering business activities. For a more in-depth understanding of the individual aspects of SAP HANA SQL DWH, further blogs will be published on our website in the near future. If you have any questions in the meantime, please do not hesitate to contact us.
Figure 03: The strengths of native SAP HANA SQL data warehousing
Author: Dominik Fischer, Martin Peitz
Marius Wimmers
Service Manager
SAP Information Management
marius.wimmers@isr.de
+49 (0) 151/42205-434
