Reference Models for the Standardization and Automation of Data Warehouse Architecture including SAP Solutions

CIplus Band 3/2018 Reference Models for the Standardization and Automation of Data Warehouse Architecture including SAP Solutions Regys Mene, Hartmut Westenberger, Hrvoje Husic   1 ReferenceModelsfortheStandardizationand AutomationofDataWarehouseArchitecture includingSAPSolutions  Authors:RegysMene,HartmutWestenberger,HrvojeHusic 1 Abstract Architecural aproaches are considered to simplify the generation of re-usable building blocks in the field of data warehousing. While SAP’s Layer Scalable Architecure (LSA) offers a reference model for creating data warehousing infrastructure based on SAP software, extented reference models are needed to guide the integration of SAP and non-SAP tools. Therefore, SAP’s LSA is compared to the Data Warehouse Architectural Reference Model (DWARM), which aims to cover the classical data warehouse topologies. 2 Introduction Growing information needs of fast changing business models require a stable but scalable and flexible data warehouse (DWH) infrastructure. Many companies started their DWH architecture development either with a centralized data repository and data marts built as described by Bill Inmon (Inmon, 2005) or with a dimensional data model as described by Ralph Kimball (Kimball, 2008). Since then, the infrastructures have evolved evolutionarily and have become complex and hybrid based on various architectural patterns. Here, reference models can help to frame the DWH strategy. Szwed, Komnata and Dymek published an ontology called DWARM describing classical DWH architecture styles (Szwed, Komnata, & Dymek, 2015). They propose a layer-based reference model with data containers like a temporary staging area, a central data repository and data marts and data containers like extraction, loading and data mart feeding (see Figure 1). DWARM uses taxonomies to organize the core components of the DWH infrastructure reference model. A container class is considered as an abstraction of components able to store data. A process class is seen as an abstraction of the data processing tasks. These two parent classes are used two build up two taxonomies. Both taxonomies are related because data containers are used as sources or targets for the data processing units.   2 Szwed and his co-authors point out how their reference model covers generic classical topologies like Kimball or Inmon. For example, the left model of Figure 2 reflects Inmon’s hub and spoke approach in a DWARM-based notation. Due to its generic character DWARM can describe hybrid variants, too. Limitations arise with the assumption of the order of the layers. For example, the Data Vault architecture style substitutes the ETL process by ELT and the business rule transformation are applied later in the data manipulation. Asif proposes an extension of the DWARM approach to cover Data Vault (Asif, 2017). The Central Data Repository should be separated in sub-layers Raw Data Vault and Business Vault as designed by Linstedt and Olschimke (Linstedt & Olschimke, 2016). The Figure 1: Layers (grey), data containers (rectangles) and processes (ellipses) building the core elements of DWARM (see (Szwed, Komnata, & Dymek, 2015))   3 transformation process has to be divided in a syntactical step assigned to the staging area and a syntactical step (business rules) shifted to the central data repository layer (see right part of Figure 2).  Figure 2: Inmon’s DWH Reference Architecture Model (left) and with Data Vault extension (right) according to the DWARM-concept (see (Szwed, Komnata, & Dymek, 2015)   4 3 ComparisonofSAP’sLSAtoDWARM SAP offers with the Layered Scalable Architetcure (LSA) a DWH architecture reference model, too. Based on this reference model SAP’s Business Warehouse solution promises a high degree of standardization and automation of the DWH/BI processes, while not losing the focus on the scalability and flexibility of the DWH/BI processes, see (SAP AG, n.d.- A), (SAP AG, n.d. - B). In a big picture, LSA is composed of two main areas: the Enterprise Data Warehouse layer and Architected Data Mart layer (see Figure 3.) Additionally, each of these two layers contains further sub-layers. In the Data Acquisition Layer, which is the entry point of the data in the SAP BW environment, the source data is stored in the so-called Persistent Staging Area (PSA). Data cleansing and conforming transformations are performed on it, before data are loaded in the target objects of the Data Propagation layer, Corporate Memory or even Operational Data Stores. The LSA’s Data Acquisition layer together with the Quality and Harmonization layer and their data loading process would align to the DWARM’s extraction and loading process. Furthermore, the LSA’s PSA provides the same function as the DWARM’s Temporary Staging Area (TSA). After the data loading process is executed in SAP BW, data is loaded from PSA to the Data Propagation layer. In fact, the main purpose of the Data Propagation layer is to integrate data that comes from multiple sources into the so-called DWH. Therefore, the Data Propagation layer is actually the DWH layer of the LSA architecture and is comparable to DWARM’s Central Data Repository layer, as both layers serve for the consolidation of the data uploaded to the DWH. On the other hand, before loading data to the Data Propagation layer, data could be also loaded in the Corporate Memory layer, which may keep the history of data extracted to the DWH for a longer period (Palekar, Patel, & Shiralkar, 2015). At the first glance a comparable element is not specified in DWARM. It can be argued that DWARM’s Central Data Repository layer can cover LSA’s Corporate Memory layer. However, a counter-argument would be that DWARM’s Central Data Repository Container is seen as the single source of truth for the DWH while the LSA’s Corporate Memory layer is just a reproduction of the staging area’s (PSA/TSA) storing data persistently for a longer period of time. Nevertheless, depending Figure 3: SAP BW Layer Scalable Architecture (LSA and LSA++) (SAP Online Library, 2017)   5 on the interpretation of this arguement, there might exist the possibility to extend the DWARM with an additional Corporate Memory layer. In order to support the operational reporting needs of the different managerial levels, data from the Data Acquisition layer can be also loaded directly to the Operational Data Store layer. This is similar to the Data Propagation layer. Therefore, particular transformations can be applied on it and make it ready to fulfill the daily decision-making processes. Here, a difference from the Reporting layer is that data have a shorter time span and serves rather to answer the real-time reporting requirements of the end users. In addition to that, another difference is that the objects on this layer do not have a star schema structure as they have in the Reporting (Data Mart) layer, but a flat table structure. This means that with big amounts of data the reporting performance can decline. Figure 4 visualizes the mentioned extensions of DWARM highlighted on blue . Figure 4: Mapping of LSA on an extended DWARM reference architecture   6 Going back to the LSA, further logical aggregations can be performed on the detailed data in the Data Propagation layer. These aggregated data is loaded in the reporting layer, where it can be further accessed by queries. Therefore, data on this layer is aggregated and stored persistently in different SAP BW objects (e.g. InfoCubes etc.), which consist of multidimensional (MD) structures. Seen from the DWARM layers perspective, the LSA’s Business Transformation and Reporting layers comprise of the same functionality with the DWARM’s Data Mart Feeding process and Data Mart container. At last, the virtualization layer supports the union/join of different reporting layer’s objects into a virtual one. Thus, the only difference of the virtualization layer with the reporting one is that data is accessed on the runtime virtually without being stored persistently. When considering this layer from the DWARM layers perspective, one can realize that it performs the same functionality of the DWARM’s data integration processes and Federated Data Repository container. Finally, DWARM contains the two additional layers Data Delivery and Customer Applications, which are not included in the LSA reference architecture’s figure above, but are actually equivalent to the required SAP BW queries (known as BEx Queries), which are then needed to expose the data to different BI/BO applications. Nevertheless, when placing against the SAP BW’s LSA and LSA++ architectures against each others, besides the structural differences that they have with each other, in overall, the difference in contexts between LSA and LSA++ are not that big. However, it is worth mentioning that the LSA++ is the type of reference architecture that aligns best to the agile DWH modeling. Therefore, with its upgraded methods of DWH modeling and its enhanced objects, it is now easier to rapidly build new data flows and at the same time, offer better data loading and reporting performance. As it can be seen in Figure 3 SAP BW has added further components and seem to have made the LSA++ a more advanced version of its initial LSA reference architecture. Moreover, from the implementation perspective, LSA++ uses enhanced SAP BW objects that have better loading and accessing performance. Particularly, when jumping to the Open Operational Data Store (ODS) layer, it performs the same functions as the LSA’s Data Acquisition and ODS layer. As such, the OpenODSView object, typically used in this layer, is a virtual object that does not store any data persistently. However, it may also be used to generate persistent Data Sources to stage data physically in SAP BW on HANA (SAP AG, n.d.- A). On the other hand, it can extract data virtually from the SAP HANA DB and bring it in the SAP BW on HANA environment. From there, this virtually extracted data can be then combined with persistent InfoProviders in the SAP BW using CompositeProviders, which can be latter then used for reporting. Furthermore, it is also possible to build queries for reporting directly on the OpenODSView object. This added functionality in LSA++ reference architecture enables then the real-time reporting of the data directly at the Open ODS layer without any data latency. Thus, the initial functionality of LSA’s ODS layer is now fulfilled by the LSA++’s Open ODS layer and its OpenODSView object. At last, this layer provides the necessary capabilities to share the SAP BW on HANA InfoProviders as views in the HANA DB environment and vice-versa, HANA DB views into SAP BW on HANA (Haupt, 2012). In this way, users have the opportunity to work with their DWH models using the hard-coded SQL queries in HANA DB or the provided SAP BW on HANA interfaces.   7 Following the upcoming data flow modelling, when comparing further LSA++ layers to the ones of LSA, not so many differences are identified among them. Up to the Architected Data Mart layer (corresponding to the Reporting layer in LSA) everything is almost the same. On the other hand, the main differences here reside in the enhanced SAP BW on HANA objects used in the SAP BW on HANA (LSA++). As such, the ADSO is considered as a universal object that can be used in multiple layers such as the Corporate Memory, Data Propagation layer etc., but with different settings in its definitions for each layer. Furthermore, in the LSA++’s BW Virtual Data Mart layer, the CompositeProvider object substitutes the functionalities of the previously used objects (e.g. MultiProvider etc.) for the Virtualization layer in the LSA. Moreover, an additional improvement of the LSA++ here is that the CompositeProvider is able to virtually integrate and report on SAP BW InfoProviders from any layer of the LSA++, without losing the reporting performance, as it would normally happen in the SAP BW on any DB (LSA) using MultiProvider objects. At last, the LSA++`s Flexible Data Marts/BW Workspace layer, signifies the agile capabilities offered by LSA++. Thus, data from the actual SAP BW models, can be further exposed and extended using local or departmental data to fulfill a department’s agile and ad-hoc reporting needs (Peleshuk, 2015). After having a deep overview of the DWARM and LSA/LSA++ reference architectures it is of interest to evaluate the degree of standardization and automation provided by SAP BW towards generating different DWH architectural models. Table 1 provides an overall comparison between DWARM and LSA/LSA++ reference architectures. Generally speaking, the LSA and LSA++ reference architectures have a high similarity with the DWARM one. However, the LSA particularly has two more layers (Corporate Memory and Operational Data Store) compared to the DWARM. On the other hand, the LSA++’s Flexible Data Mart and BW Workspaces layer is not appended to the DWARM layers, as it is too specific in comparison to the overall purpose of DWARM itself. Nevertheless, regarding the above-suggested extensions in the DWARM layers, the authors of it also admit the fact that their reference architecture model is mainly provided on the generic level and has place for further customization with regard to specific DWH architectures (Szwed, Komnata, & Dymek, 2015). On the other hand, an argument for the extension of DWARM with regard to the LSA and LSA++ layers may follow from the fact that the actual purpose of LSA and LSA++ is to fit at best the capabilities supported by the SAP BW. Therefore, in comparison to DWARM, one could see the SAP BW’s LSA and LSA++ reference architectures in a level down in granularity. Nonetheless, the following suggested layers to be extended to DWARM are not only found in the LSA and LSA++, but may also be useful in the cases of the above-mentioned DWH architectures. As such, in their book about their DWH approach, Kimball & Caserta (2004) identify the functionality of the LSA’s and LSA++’s Corporate Memory layer with the ones of long-term archiving of the staged data. Indeed, this is also the purpose of the LSA and LSA++’s Corporate Memory layer. Thus, the authors suggest that staged data should be archived in a long-term repository (in this case, in the Corporate Memory layer) up to the moment that it is sure that it will not be needed anymore (Kimball & Caserta, 2004, S. 8). 