Test automation with SQL in SAP Datasphere

In a previous article, "SAP Datasphere Command Line Interface as Project Accelerator", we presented various ways in which you can use the SAP Datasphere command line interface to shorten project runtimes. In this article, we will show you how you can use SQL or SQLScript as a project accelerator.

SAP Datsphere is based on a powerful HANA database that uses SQL and SQLScript as native programming languages. SQLScript is typically used in views and transformation flows, but can also be used to automate tasks. Below we present two possible areas of application.

Excursus: Native Datasphere modeling

With associations and semantic types/information, Datasphere offers a modern paradigm of data modeling, which brings many advantages in terms of performance, self-service and reusability. To use this paradigm, tables and views are either imported already semantically enriched (e.g. possible with S/4 HANA), or they are subsequently enriched within Datasphere. An analytical model (AM) is then developed, which can be consumed as a reporting object by a range of applications (SAC, SAC Add-in, ODATA) that are capable of evaluating this semantic information.

We also like to call this described paradigm "native" Datasphere development, as this is the recommended and future-oriented approach.

Converting objects

It is often necessary to combine the native type of modeling with classic database technologies that do not know and cannot evaluate any semantics. Third-party tools such as Tableau (and in some cases Power BI) require an ODBC connection in order to consume Datasphere data.

However, analytical models, i.e. the reporting objects in native modeling, cannot be consumed with ODBC capability, especially with their associations. This leads to a kind of dilemma in that the data model must also be modeled in different ways for different consuming systems and cannot be used universally.

To remedy this situation and minimize duplicate modeling efforts, we have developed an SQL-based tool that enables us to generate corresponding ODBC-capable objects from natively developed data models. A script analyzes the analytical model with its associations, linked dimensions, texts and hierarchies and translates these elements into their corresponding SQL representation. An SQL code is generated from this, which can be deployed as an ODBC-capable SQL view in Datasphere. 

Download the whitepaper and find out
which product is best for your data warehousing strategy!

Test automation with SQL

SQL can also be used for automated tests. One possible scenario would be the migration from SAP BW to SAP Datasphere via BW Bridge. BW Bridge enables you to transfer your old on-premise business warehouse to the cloud and connect it to SAP Datasphere. A separate BW Bridge Space is used for this, which can be used across the system.

After the migration, we naturally want to ensure that the source and target match. An SQL stored procedure can be used for this, which compares the tables and generates a detailed test report. The generated test report contains an overview of differences, value changes at field level and missing entries. The same principle can also be used for regression tests. You can compare data records with generated SQL.

Test automation with SQL - Our Summary

With SQL, you have a powerful tool at your fingertips to implement SAP Datasphere projects faster. Whether at the start of the project, when the on-premise BW system is migrated to the cloud, when connecting third-party tools or when regression tests are required - with SQL procedures you can save a lot of time.

Do you have questions about SAP Datasphere? Are you trying to build up the necessary know-how in your department or do you need support with a specific issue? Please do not hesitate to contact us. We look forward to exchanging ideas with you!