Data virtualization is the process of retrieving data stored in remote sources for report consumption without the need of data persistency within the reporting application. The SAP BW application has offered different options for enabling data virtualization over the years, ranging from virtual providers that rely on direct access data sources, to the more recent hybrid providers that allow composite views of virtualized and persistent data within SAP BW.
Until now, however, all the data virtualization methods available in SAP BW have relied on the concept of integration prior to functionality. Integration in the context of SAP BW can be understood as having to map inbound data to SAP InfoObjects by means of transformation rules. Functionality, on the other hand, can refer to all reporting capabilities provided via SAP BW queries, which have always made use of SAP InfoObjects as their building blocks. This meant that in order to offer reporting functionality to virtualized data, SAP BW had to first integrate external data via mapping to InfoObjects. The implication here was that data-virtualization scenarios often required the development of dedicated SAP elements, such as SAP InfoObjects and transformations, simply to address the integration requirements.
This may not be seen as a major inconvenience, especially when addressing reporting scenarios based on established processes that require repetitive retrieval of a constant remote data structure. Scenarios such as retrieval of exchange rates and stock prices, among others, fall into this category. On the opposite side of the spectrum, however, are cases of ad-hoc retrieval requirements. One-time requests for pulling remote data to SAP BW have always been challenging when it comes to being agile. This is mainly due to the burden of having to create a whole set of dummy InfoObjects simply for the sake of enabling temporary queries to report on virtualized content.
With the introduction of Open Operational DataStore (Open ODS) views, integration prior to functionality is no longer a prerequisite. Open ODS views are queryable structures modeled using field definitions strictly aligned to the datatypes defined at remote data sources. This modeling approach (where no InfoObjects are required) is known as field-based modeling and is available on SAP BW applications running on SAP HANA database from version 7.4 Support Package 5 onwards.
Open ODS views can be consumed by SAP BW Analyzer as is. This means that Open ODS views are made available as any other InfoProviders to the SAP BW query builder. SAP BW queries can be defined by simply dragging and dropping the raw fields of an Open ODS view to the structure of a query. By acting as a queryable interface between the external data source and SAP BW queries, Open ODS views eliminate the need to create infoObjects, thus shortening the development cycle associated with ad-hoc requirements of virtualized data in SAP BW. External data can now be easily and quickly made available for SAP BW queries.
Setting Up an Open ODS View in SAP BW on SAP HANA
Open ODS views are configured using the SAP BW modeling perspective of SAP HANA studio or via the Administrator Workbench (transaction code RSA1) in the SAP BW application. With the SAP BW Administrator Workbench, the steps to configure an Open ODS view are very similar to configuring any other type of InfoProvider. You simply right-click in an InfoArea and, from the context-menu options that open, select Create Open ODS View.
If you use SAP HANA studio to create an Open ODS view, then the SAP BW modeling perspective should be used. To access the SAP BW modeling perspective, follow SAP HANA studio menu path Window > Perspective > Open Perspective > Other…, and select BW modeling from the list of available perspectives that opens.
This opens the BW Repository where you select an InfoArea. Then right-click to open the BW Repository context menu of the SAP HANA BW modeling perspective. Select New > Open ODS. (These are basically the same steps as the ones performed within the SAP BW Administrator Workbench.)
(Note: In this article, the step-by-step configuration for setting up an Open ODS view focuses on using the Eclipse environment provided by SAP HANA studio. However, the steps described here are the same when configuring an Open ODS view using the SAP BW Administrator Workbench interface [transaction code RSA1].)
Once the Open ODS view option is selected, a dialog window opens where you can specify a technical name and description for the Open ODS view (Figure 1). Two other important parameters are also defined on this screen: Semantics and Source Type.
Enter the Open ODS view Name, Description, Semantics, and Source Type parameters
The Semantics parameter can be set as Facts, Master Data, or Texts. This parameter specifies which element groups are available when defining the Open ODS view. If the Semantics parameter is set to Facts then element groups typical to InfoCubes and DataStore Objects (DSOs), such as Characteristics, Key Figures, Unit, and Currency, are made available for defining the Open ODS view.
If the Semantics parameter is instead set to Master Data, then the element groups presented for Open DSO definition are the ones typically associated with SAP BW master data tables, including Key Characteristics, Attribute Characteristics, Key Figures, and valid from and to dates, among others. Finally, if the Semantics parameter is set as Text, then element groups encountered on SAP BW InfoObject text tables are made available for defining the Open ODS view. Figure 2 lists the element groups per semantic type.
The available list of element groups for defining Open ODS views by semantic type
The Source Type parameter determines which adapter type is to be used as a connection mechanism between an Open ODS view and a particular source. The options for Source Types (Figure 1) are DataSource (BW), Database Table or View, Transformation, or DSO (advanced).
The DataSource (BW) source type enables an Open ODS view to use a direct-access DataSource to retrieve external data. Transformation source types are used when a certain level of data manipulation is required before the data reaches the Open ODS view. As for the DSO (advanced) source type, as the name implies, this is used when you want to connect an Open ODS view with an advanced DSO.
What is common to all three of these source types is that they are used within SAP BW itself to enable an Open ODS View to connect to other SAP BW elements. The Database Table or View source type, however, allows an Open ODS view to connect to external data sources such as non-SAP BW remote tables, via DB Connect. It is with this source type that field-based modelling shines by promoting agility to the overall process of data virtualization in SAP BW.
A Data Virtualization Scenario with Open ODS Views
To demonstrate this, let’s assume that an external table—in this case, named MATERIAL_LIST—is available in SAP HANA. This table contains a list of articles and related net weight in kilograms sold via online transactions. This table is located within an SAP HANA schema named CHRISTSA that is outside the SAP BW schema (SAPBIW). Since it’s outside the SAPBIW schema, this table is not known by the SAP BW application and its data cannot be consumed in SAP BW queries. It is, however, possible to virtualize this content for report consumption in SAP BW by defining an Open ODS view and mapping it to the table MATERIAL_LIST.
You can do this by creating an Open ODS view (in this case, named ZOODS_MAT1) with the Semantics Facts parameter, a Database Table, or View source type, and by providing the database object schema name. In this example, the schema name is CHRISTSA and the table name (DB Object Name) is MATERIAL_LIST (Figure 3).
Open ODS view ZOODS_MAT1 referencing SAP HANA table MATERIAL_LIST
Click the Finish button (not shown) and in the screen that opens, go to the Facts: ZOODS_MAT1 tab (Figure 4). Here you select which fields of the MATERIAL_LIST table should be part of the Open ODS view. The fields from table MATERIAL_LIST are made available under the Source Fields panel on the left side of the Open ODS view definition screen.
Define the Open ODS view fields by dragging and dropping the source fields
The Open ODS view is defined by dragging and dropping the desired source fields to the appropriate element from the Source Field panel to the group folder in the View Field panel. In this example, table MATERIAL_LIST only contains three source fields—one each for transaction number, material, and net weight (in kilograms). These fields are dragged from the Open ODS View Source Field panel to the Characteristics (Key) folder. This folder has a transaction number (TRANSACTION) specified as the Key Characteristic, material (MATNR) as a regular characteristic, and net weight in kilograms (NETKG) as the Key Figure.
Now that you’ve finished defining the Open ODS view, click the activate icon (or press Ctrl + 3) to activate the Open ODS view object (ZOODS_VIEW) on the SAP BW application side.
The Open ODS view is now available and active in the SAP BW Administrator Workbench and ready to be consumed on SAP BW queries, without having to use an InfoObject (Figure 5).
The new Open ODS view is available and active in the SAP BW administrator workbench
Users can now create SAP BW queries based on this newly created Open ODS view object, ZOODS_MAT1. This Open ODS view is seen like any other InfoProvider by the SAP Query Designer. (Follow menu path All Programs > Business Explorer > SAP Query Designer, which opens the screen in Figure 6.)
Open ODS views are made available as InfoProviders for the SAP Query Designer
As with any other type of InfoProvider, a query can be created on top of the Open ODS view. Simply drag and drop the desired fields from the InfoProvider panel to the appropriate section (Rows, Columns, or Free Characteristics) of the query definition (Figure 7). Note that this is done without the need of any SAP BW InfoObject. In other words, it is pure field-based reporting, which shortens the cycle for virtualization of remote sources in the SAP BW application.
Field-based SAP BW Query on top of the Open ODS view
Leveraging Virtualized Data by Associating it to Existing SAP BW Data
The virtualized data can also be associated to existing SAP BW data models if desired. In this case, fields from external sources are enriched with attributes or made authorization-relevant before consumption on SAP BW queries. This is done by using the Association feature when defining an Open ODS view.
To demonstrate this feature, let’s go back to SAP HANA studio and change the definition of Open ODS view ZOODS_MAT1. Click the MATNR data field in the View Fields section of the Open ODS view definition screen (Figure 8). Once opened, you can set the association feature to InfoObject 0MATERIAL. In my example, the keys for MATNR from the external source and the 0MATERIAL key match, allowing for straightforward association.
Enrich the Open ODS view fields by associating them with existing SAP BW data models
You can then select some of the attributes of InfoObject 0MATERIAL to be associated with the external field MATNR and, therefore, to be available for report consumption at the query level. To do this, click the Navigation Attributes button (Figure 8) and, in the Navigation Attributes pop-up window that opens, select the desired navigation attributes of InfoObject 0MATERIAL to be associated to Open ODS view field MATNR. To confirm the changes, re-activate the Open ODS view by clicking the activate icon.
Once you return to the Query Designer, you see that the association between the MATNR field and the SAP BW InfoObject 0MATERIAL enriches querying possibilities by making the pre-existing SAP BW navigational attributes available in conjunction with the virtualized data (Figure 9).
The enriched Open ODS view field MATNR shown at the BEx Query Designer level
(Tip: When you’re using a PC, you may switch between the SAP HANA studio and the Query Designer sessions by pressing CNTL+ALT; when using a Mac, press F3 to switch.)
Several companies are in the process of building what is known as Data Lakes or major repositories of unstructured data on less expensive hardware. The ability to provide easy reporting integration to such Data Lakes via data virtualization is critical to any reporting application. By acting as a queryable, acquisition-level layer, SAP BW Open ODS views promote agile modeling of data-virtualization scenarios and allow functionality prior to integration. This results in faster prototyping of ad-hoc reporting solutions and positions the SAP BW application to better address typical Big Data analytical demands.