SAP HANA SQL Data Warehousing offers a high degree of flexibility with regard to the use of different tools.
The SQL approach enables the use of many well-known (open source) software development tools, particularly in the context of the applied DevOps philosophy. However, the SAP HANA platform also comes with its own important tools for developing the native SAP HANA SQL Data Warehouse. In this blog, we would like to briefly introduce you to these and provide an overview of the SAP HANA SQL Data Warehousing tooling.
Table of contents
1.1 Data integration
1.1.1 Enterprise Information Management
1.1.2 Smart Data Access
1.2 SAP HANA XSA
1.3 SAP Web IDE
1.4 SAP HANA Cockpit
1.5 SAP HANA Database Explorer
3.1 Git
3.2 CI/CT/CD
3.3 Issue Tracking
1 SAP HANA tools
The SAP HANA platform has various tools for managing and developing the platform. These are important for setting up the native SAP HANA SQL Data Warehouse.
1.1 Data integration
The integration of data from different source systems is a core task for a data warehouse. After many years of classic Extract, Transform and Load (ETL) dominating this area of data warehousing, there is now an increasing trend towards transferring data to the data warehouse virtually. In addition, new NoSQL database technologies and big data are playing a greater role and must interact with an enterprise data warehouse solution. The SAP HANA platform as the basis for SAP HANA SQL Data Warehousing, which is native in this sense, has many solutions that enable needs-based and scalable data integration. The SAP Web IDE development environment (see 1 SAP HANA tools) is already sufficient for simple loading processes and can be used to graphically populate tables from source systems based on SQL commands. For more complex scenarios, the package solution Enterprise Information Management (EIM) and the SAP HANA Smart Data Access (SDA) service are available for integrating data.
1.1.1 Enterprise Information Management
For some years now, Enterprise Information Management has been a separate field of information technology that deals with the integration of the management of structured and unstructured information in a corporate context. SAP has combined specific data management and data integration solutions under this name. For data integration, these are the tools SAP HANA Smart Data Integration (SDI) and SAP HANA Smart Data Quality (SDQ). SDQ is an extension of SDI that cannot be used on its own. SDI is the universal ETL tool of the SAP HANA platform with which a large selection of data sources can be connected and data can be loaded into the SAP HANA database, if necessary after a transformation. The standard connectors include:
- ABAP
- SAP Business Warehouse (SAP BW)
- Database management systems (including IBM DB2, Oracle, Microsoft SQL Server, Teradata)
- SOAP and OData
- JDBC
- Appache Hive
- Microsoft Office
- Files (e.g. Microsoft Excel)
- Facebook and Twitter
SDI is fully integrated into the SAP HANA platform and can be managed via the SAP HANA Cockpit. To use it, all you need to do is install a data provisioning agent on the source system and connect it with the corresponding adapter (Figure 1.1). The SAP Web IDE can then be used to create flow graphs for data replication tasks, for example. SDQ helps with data cleansing in this process. This makes it easy to define data formats, identify duplicates and make other specific rules to improve data quality.
Figure 01: Data provisioning with Enterprise Information Management
1.1.2 Smart Data Access
SAP HANA Smart Data Access (SDA) is the tool that enables data virtualization and makes persistent replications on the SAP HANA database obsolete. In the context of SDA, SAP therefore also speaks of data federation technology, as the data remains in the source systems and a distributed data landscape with centralized access can be created. SDA is essentially based on the database standard ODBC (Open Database Connectivity), which means that all databases that support this standard are suitable for SDA connections. In addition to all common database management systems, this currently also includes Google BigQuery, Apache Hadoop and Apache Spark. Once a connection has been created in this way, the data can be retrieved in the SAP Web IDE development environment in virtual tables that adopt the metadata of the source tables. This results in the same functionality as with persistent tables, with the option of creating synonyms, functions and procedures.
1.2 SAP HANA XSA
SAP HANA Extended Application Services Advanced Model - XSA for short - is the integrated application server of the SAP HANA platform, which provides both the development and runtime environment for HANA applications. As the name suggests, it is a further development of the original SAP HANA XS version, which was added to the product two years after the release of the SAP HANA database and initiated the transformation to the platform. The basic idea has not changed since then. On the SAP HANA platform, the XSA application server and the SAP HANA database are located directly next to each other. This not only creates a development environment, but also brings data processing closer to the data in this architecture. This avoids the mass transfer of data between the database and application layer and the resulting potential speed disadvantages. In this respect, the SAP HANA platform represents a paradigm shift from the prevailing three-tier architecture to a two-tier architecture in which even complex calculations are taken over by the database layer, thereby increasing performance and efficiency. SAP HANA XSA is therefore of crucial importance for the tools described below, as the application server creates the structural prerequisites for the tool functions. Compared to its predecessor SAP HANA XS, the main difference is that XSA has seamless integration with the cloud foundry architecture of SAP Cloud Platform. This makes it possible to develop on-premise and immediately operate the developments in the cloud.
1.3 SAP Web IDE
SAP Web IDE is a browser-based development environment with which applications can be created full-stack, i.e. including user interface (UI), application logic and database artifacts. In this end-to-end development process, the entire life cycle of an application can be accompanied with the SAP Web IDE and the environment supports debugging, delivery and testing of software in addition to the possibility of development. The SAP Web IDE runs on-premise on the SAP HANA XSA application server or is part of SAP Cloud Platform. No additional installations are therefore required to use it. SAP Web IDE is the central tool for developing the database artifacts for the SAP HANA SQL Data Warehouse. The development environment comes with a series of graphical editors that can be used to easily prepare models, data logic and data flows, such as the SAP HANA-typical Calculation Views (Figure 1.2), Flowgraphs or Core Data Services. In addition to graphical editing, there is also the option of direct coding in the SAP HANA database language SQLScript.
Figure 02: SAP Web IDE with Calculation View
1.4 SAP HANA Cockpit
The SAP HANA Cockpit is the web-based administration tool for the SAP HANA platform, which can be used to perform classic administration tasks for the SAP HANA platform. It can be used to configure and monitor the entire SAP HANA system landscape as well as individual systems such as tenant databases or HDI containers. The system configuration includes the management of resources, services and performance. Another important aspect is the management of security measures. Settings for encryption, system warnings and backups can be made via the SAP HANA Cockpit. Sophisticated role and user management is also available.
1.5 SAP HANA Database Explorer
The SAP HANA Database Explorer links elements that are of interest from a developer and administrator perspective. It is therefore integrated into both the SAP HANA Cockpit and the SAP Web IDE. The Explorer contains the database catalog in the form of a tree structure, which can be used to explore the contents of the various schema or HDI containers in the database and to import and export content (Figure 1.3). An SQL console is also available for data queries and there are options for debugging and checking log files.
Figure 03: SAP HANA Database Explorer
2 SAP PowerDesigner
SAP PowerDesigner is a tool from the field of computer-aided software engineering (CASE), which involves the use of IT-supported tools to design software applications and implement them as automatically as possible. SAP Powerdesigner creates solutions for the graphical analysis and modeling of business processes and their implementation in various data models. SAP HANA SQL Data Warehousing relies heavily on corresponding modeling processes and places them at the heart of the data warehouse structures. SAP PowerDesigner is therefore an important tool in the context of model-driven SAP HANA SQL Data Warehousing, although it is not part of the SAP HANA platform and must be licensed separately. The functional scope of SAP PowerDesigner is basically divided into the areas of data, information and enterprise architecture. However, the area of data architecture with various data modeling techniques, such as conceptual, logical and physical data modeling, is particularly relevant for the SAP HANA SQL DWH (Figure 2.1). It is particularly important that different models and model types can be fully integrated using link-and-synch technology. This feature, which is summarized under the keyword data lineage, makes it possible to carry out impact assessments of changes across the various models and to make the entire data modeling process transparent. Updates to the database architecture are thus directly reflected in the corresponding upstream data models and vice versa. Accordingly, both a forward-looking development, which leads from conceptual considerations to the physical data model of the database, and a backward-looking process are possible.
Figure 04: Conceptual model in SAP PowerDesigner
3 DEvOps tools
Figure 3.1 shows the typical phases of a DevOps cycle with the connection between the areas of development/design time and operation/runtime, as well as the higher-level continuity processes. These work in particular towards the highest possible degree of automation of the processes. Specific DevOps tools, which we present in an overview especially for SAP HANA SQL Data Warehousing, support this.
Figure 05: DevOps tools SAP HANA SQL Data Warehousing
3.1 Git
Git is a distributed version control system that can now be regarded as the industry standard in the field of agile software development for the joint management of source code by development teams. Such a repository is also of central importance for the DevOps philosophy with the processes of Continuous Integration, Continuous Testing and Continuous Delivery. Git technology is offered by various commercial providers such as GitHub, GitLab or BitBucket. All of these variants are very easy to integrate into the SAP Web IDE and can be used to build the SAP HANA SQL Data Warehouse. Both pure cloud and on-premise solutions are possible.
3.2 CI/ CT/ CD
Continuous Integration, Continuous Testing and Continuous Delivery (CI/CT/CD) are among the core processes of the DevOps philosophy, which merge seamlessly into one another. In the DevOps cosmos, there are a number of tools for these processes that support a higher degree of automation in the development of software or, in our case, a data warehouse. These usually interact strongly with the aforementioned Git repository. The availability of Git in conjunction with the universal SQL standard of the SAP HANA platform means that there are basically no restrictions with regard to the selection of specific tools. In our projects, we have had good experiences with common tools such as Jenkins or Bamboo when setting up an automated deployment pipeline.
3.3 Issue tracking
Another category that provides tools for the successful practice of DevOps is issue tracking. Corresponding tools are used to track problems in the software development lifecycle. An issue or problem can be anything of relevance, such as bugs, errors or even specific functionalities and features that are not yet fully developed. Issue tracking tools can be used to record, evaluate and assign these problems to people. In some cases, this can even be done automatically in conjunction with the CI/CT/CD tools. In DevOps, this work is very important under the heading of continuous feedback. Feedback from users in particular must be incorporated into the development lifecycle time and again and processed properly. Here, too, there are a number of tools that come into question. And here, too, there is basically no restriction.
4 Conclusion
In this blog, we have provided you with an overview of the main SAP HANA SQL Data Warehousing tools. In addition to some native tools of the SAP HANA platform, a selection of other third-party tools is available to you. The list of helpers and automation promises is particularly long when it comes to supporting DevOps processes and is initially confusing, especially for users who have not yet had much contact with DevOps and agile software development methods due to their previous work in the data warehouse environment. You will therefore soon find more information blogs on this topic on our website. If you have any questions in the meantime, please feel free to contact us.
Author: Martin Peitz
Marius Wimmers
Service Manager
SAP Information Management
marius.wimmers@isr.de
+49 (0) 151/42205-434
