Organizations around the world work with us to integrate data from SAP to a target analytical data platform. Beyond the requests to replicate data for the physical (‘raw’) SAP tables, we’ve experienced an increase in demand to source from (HANA) views.
In general, we advise HVR users to perform the data acquisition from only the physical tables to benefit from change data capture (CDC) out of the database logs. Data in views doesn’t end up in the database logs because they are virtual objects. Consequently, views should be recreated on the target if required. However, there can be several reasons for a company to decide to get data directly from the views.
For example, IT departments have invested heavily in developing views and want to leverage as much of this investment as possible. Additionally, customers can have hundreds of these views, and migrations can cost time.
This blog will discuss the different types of HANA views, the most commonly requested types—calculation views and CDS views, and how HVR sources data from HANA views.
Calculation (column) Views
Calculation views were developed by SAP to use HANA’s column store architecture and to allow backward compatibility with earlier SAP products. The main goal for calculation views was query and reporting, with a focus on optimizing performance and supporting business intelligence calculation scenarios, especially for SAP BW.
The calculation view is only used in specific analytical use cases and can be explored with special (SAP) products. After compilation, calculation views become column views in HANA. Special features and capabilities for calculation views include:
– Complex calculations
– OLTP and OLAP models
– Client handling, language, currency conversion
– Union, Projection, Aggregation, Rank, etc.
– Calculation views can be queried through HANA ODBC
CDS views (CORE Data Services views) are part of an SAP infrastructure created in the HANA database to ensure the SAP application uses the database features to its full potential. CDS views allow direct access to underlying tables in the HANA database.
SAP delivers standard CDS views as business content in S/4 HANA and is replacing the original BW extractor ABAP logic with CDS views. As a result, the views and CDS infrastructure can act as a business logic layer and export data to external (SAP) systems like SAP BW.
CDS views use SQL-based DDL (Data Definition Language), enhanced with additional concepts like annotations, associations, and expressions. This can give extra value, in particular for SAP products being able to ingest that information.
Advantages of CDS views include:
– CDS views can be exposed in OData Service
– CDS views are comparable to open SQL
– CDS views support automatic SAP client handling
– CDS views are SQL based
How does HVR source from HANA Views?
With a better understanding of the different SAP (HANA) views and why customers request them as a source, let’s focus on what options HVR can offer to get data from them.
HVR uses SQL to query the ‘special’ views. Consequently, HVR Refresh functionality can create the required compatible target structures and do data load. The refresh operation still leverages all HVR’s features for secure and performant transport of the data. On top of this, HVR provides capabilities to filter source data with parameters and load incremental changes from the source structure if there is a good change indicator.
Still, handling the different types of source views requires slightly different approaches or options.
Sourcing from HANA Calculation/Column Views
Calculation views are stored in a special schema on the HANA database called _SYS_BIC. This HANA schema needs to be specified while adding to the HVR replication selection.
Sometimes, the name of a source object cannot be used directly as a name for the target object. For instance, the name may be too long or use characters not allowed by the target platform. In that case, HVR can be configured to overrule the naming of the target table object.
Calculation views can be defined with input parameters. The HVR Refresh operation from such an object as a source would lead to an error because HVR cannot handle view parameters. A workaround is to create a view on top of the calculation view with auto-filled parameters. HVR can perform the refresh operation from such a view as a source.
Recreating a database view on top of the replicated raw tables can be a challenge. Calculation views could have several aggregate statements that the target platform may not support. If required to recreate functionality, a completely different object may be necessary.
Please check HVR’s whitepaper on HANA views for more detailed information on extracting data from calculation/column views.
Data Replication from HANA CDS Views
HANA CDS views are very much like standard views. For instance, the objects can be defined in each HANA database schema, and the DDL language is similar to standard DDL for view objects. To set up data acquisition through HVR requires the same steps as for regular views. (Remember here to also checkmark the show view option).
Since the logic of the CDS view doesn’t differ much from regular DDL, it may be worth recreating the view as a target object. With some basic conversions, it is possible to copy most of the view logic into the target system.
Companies are looking to use all their data to their full potential. With that, they also want to use data in HANA Calculation or CDS views. HVR has several options to allow you to use that data everywhere and anytime.
If you’re interested in learning more about HVR’s SAP capabilities, including sourcing from HANA views or its ability to source data from SAP applications to various target platforms, please check out our whitepaper or contact us directly.