first guidance virtual integration of external data in sap...
TRANSCRIPT
SAP First Guidance
SAP BW 7.4
SAP HANA™ Appliance
Applicable Releases:
SAP HANA 1.0 SPS 09
SAP BW 7.4 SP10
and higher
Version 1.0
March 2016
Ulrich Christ, SAP SE
First Guidance... Virtual Integration of External Data in SAP BW powered by SAP HANA
© Copyright 2016 SAP SE. All rights reserved.
No part of this publication may be reproduced or transmitted in any form
or for any purpose without the express permission of SAP AG. The
information contained herein may be changed without prior notice.
Some software products marketed by SAP AG and its distributors
contain proprietary software components of other software vendors.
Microsoft, Windows, Excel, Outlook, and PowerPoint are registered
trademarks of Microsoft Corporation.
IBM, DB2, DB2 Universal Database, System i, System i5, System p,
System p5, System x, System z, System z10, System z9, z10, z9, iSeries,
pSeries, xSeries, zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390,
OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power
Architecture, POWER6+, POWER6, POWER5+, POWER5, POWER,
OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS,
HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex,
MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and
Informix are trademarks or registered trademarks of IBM Corporation.
Linux is the registered trademark of Linus Torvalds in the U.S. and other
countries.
Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either
trademarks or registered trademarks of Adobe Systems Incorporated in
the United States and/or other countries.
Oracle is a registered trademark of Oracle Corporation.
UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open
Group.
Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame,
and MultiWin are trademarks or registered trademarks of Citrix Systems,
Inc.
HTML, XML, XHTML and W3C are trademarks or registered trademarks
of W3C®, World Wide Web Consortium, Massachusetts Institute of
Technology.
Java is a registered trademark of Sun Microsystems, Inc.
JavaScript is a registered trademark of Sun Microsystems, Inc., used
under license for technology invented and implemented by Netscape.
SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP
BusinessObjects Explorer, StreamWork, and other SAP products and
services mentioned herein as well as their respective logos are
trademarks or registered trademarks of SAP AG in Germany and other
countries.
Business Objects and the Business Objects logo, BusinessObjects,
Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other
Business Objects products and services mentioned herein as well as their
respective logos are trademarks or registered trademarks of Business
Objects Software Ltd. Business Objects is an SAP company.
Sybase and Adaptive Server, Anywhere, Sybase 365, SQL Anywhere, and
other Sybase products and services mentioned herein as well as their
respective logos are trademarks or registered trademarks of Sybase, Inc.
Sybase is an SAP company.
All other product and service names mentioned are the trademarks of
their respective companies. Data contained in this document serves
informational purposes only. National product specifications may vary.
The information in this document is proprietary to SAP. No part of this
document may be reproduced, copied, or transmitted in any form or for
any purpose without the express prior written permission of SAP AG.
This document is a preliminary version and not subject to your license
agreement or any other agreement with SAP. This document contains
only intended strategies, developments, and functionalities of the SAP®
product and is not intended to be binding upon SAP to any particular
course of business, product strategy, and/or development. Please note
that this document is subject to change and may be changed by SAP at
any time without notice.
SAP assumes no responsibility for errors or omissions in this document.
SAP does not warrant the accuracy or completeness of the information,
text, graphics, links, or other items contained within this material. This
document is provided without a warranty of any kind, either express or
implied, including but not limited to the implied warranties of
merchantability, fitness for a particular purpose, or non-infringement.
SAP shall have no liability for damages of any kind including without
limitation direct, special, indirect, or consequential damages that may
result from the use of these materials. This limitation shall not apply in
cases of intent or gross negligence.
The statutory liability for personal injury and defective products is not
affected. SAP has no control over the information that you may access
through the use of hot links contained in these materials and does not
endorse your use of third-party Web pages nor provide any warranty
whatsoever relating to third-party Web pages.
SAP “How-to” Guides are intended to simplify the product
implementation. While specific product features and procedures typically
are explained in a practical business context, it is not implied that those
features and procedures are the only approach in solving a specific
business problem using SAP NetWeaver. Should you wish to receive
additional information, clarification or support, please refer to SAP
consulting.
Any software coding and/or code lines / strings (“Code”) included in this
documentation are only examples and are not intended to be used in a
productive system environment. The Code is only intended better explain
and visualize the syntax and phrasing rules of certain coding. SAP does
not warrant the correctness and completeness of the Code given herein,
and SAP shall not be liable for errors or damages caused by the usage of
the Code, except if such damages were caused by SAP intentionally or
grossly negligent.
Disclaimer
Some components of this product are based on Java™. Any code change
in these components may cause unpredictable and severe malfunctions
and is therefore expressively prohibited, as is any de-compilation of these
components.
Any Java™ Source Code delivered with this product is only to be used by
SAP’s Support Services and may not be modified or altered in any way.
Typographic Conventions
Type Style Description
Example Text Words or characters quoted
from the screen. These
include field names, screen
titles, pushbuttons labels,
menu names, menu paths,
and menu options.
Cross-references to other
documentation
Example text Emphasized words or
phrases in body text, graphic
titles, and table titles
Example text File and directory names and
their paths, messages,
names of variables and
parameters, source text, and
names of installation,
upgrade and database tools.
Example text User entry texts. These are
words or characters that you
enter in the system exactly
as they appear in the
documentation.
<Example
text>
Variable user entry. Angle
brackets indicate that you
replace these words and
characters with appropriate
entries to make entries in the
system.
EXAMPLE TEXT Keys on the keyboard, for
example, F2 or ENTER.
Icons
Icon Description
Caution
Note or Important
Example
Recommendation or Tip
Table of Contents
1. Introduction ............................................................................................................................ 1
2. Background Information ........................................................................................................ 1
2.1 New CompositeProvider................................................................................................... 1
2.2 Open ODS View................................................................................................................. 2
2.3 DataStore Object (advanced) ......................................................................................... 2
3. Sample data model and integration scenarios ..................................................................... 2
3.1 Integration Scenarios ....................................................................................................... 2
3.2 Sample Data Model .......................................................................................................... 3
4. Considerations for integration .............................................................................................. 4
4.1 Origin of data and source types ...................................................................................... 4
4.2 External master data ........................................................................................................ 5
4.3 Adjusting the model to SAP BW standards .................................................................... 1
4.3.1 Data Type Conversions ....................................................................................... 1
4.3.2 Data Formats ....................................................................................................... 5
4.3.3 Parameters .......................................................................................................... 5
5. Architecture considerations .................................................................................................. 5
5.1 Reusable Components .................................................................................................... 5
5.2 To virtualize or not to virtualize… ................................................................................... 6
6. Summary and decision help .................................................................................................. 7
1 | P a g e
1. Introduction SAP BW 7.4 powered by SAP HANA provides a variety of innovations in various areas. One of the
focus areas are extended and simplified ways to integrate non-SAP data into SAP BW. To enable such
scenarios, SAP BW provides new objects (CompositeProviders, Open ODS Views) which allow a
flexible and rapid integration of such data for direct access into the SAP BW model and process
world. Hence, the focus of this paper is on “virtual” integration of external data into SAP BW powered
by SAP HANA as a complementary and new approach to the traditional loading of external data to
SAP BW.
In this context, we talk about various levels of integration:
1) Leveraging SAP BW’s OLAP capabilities on external star schema models 2) Combination of external data with SAP BW master or transaction data 3) Moving from virtual access to SAP BW persistence
It is important to understand that the integration requirements might change along the life cycle of a
scenario. Whenever a new dataset has to be integrated quickly into the data warehouse, virtual ways
of integration are obviously the preferred approach. It may, however, later turn out that certain
service level requirements cannot be fulfilled with a virtual approach. This can range from
performance considerations over availability issues with the source data to questions like ownership
of the source – after all, the data warehouse is just a consumer of the data and will have to react to
changes of the source structures potentially without prior notice.
SAP BW 7.4 provides techniques to create data warehouse architectures that are flexible enough to
react to such changes with reasonable impact by decoupling data providers from data consumption.
However, various aspects have to be understood and taken into consideration when working with
non-SAP data in SAP BW. This paper describes a number of these aspects in detail and tries to give
guidance how leverage the new SAP BW objects in this context.
This paper describes the technical capabilities of SAP BW 7.4 SP10. An update covering new
developments planned with SAP BW 7.5 SP04 will follow.
2. Background Information
2.1 New CompositeProvider A CompositeProvider is an InfoProvider able to combine data from SAP BW InfoProviders such as
InfoObjects, DataStore Objects, SPOs and InfoCubes, or SAP HANA views such as Analytical or
Calculation Views using join or union operations to make the data available for reporting. The used
union and join operations in the CompositeProvider are pushed down to SAP HANA leveraging the
power of the calculation engine inside SAP HANA. BEx queries can be created on
CompositeProviders as on any other SAP BW Info Provider. SQL access is possible by creating an
SAP HANA view to the CompositeProvider.
With SAP BW 7.4 SP05 on SAP HANA the CompositeProvider has been renewed with a deeper
integration in SAP HANA leveraging the capabilities of the SAP HANA calculation engine. The
CompositeProvider consolidates a number of InfoProviders types and harmonizes the modeling of
mixed scenarios with SAP BW powered by SAP HANA.
http://help.sap.com/saphelp_nw74/helpdata/en/63/f3c416bcbf4730bb86e62ef8a54e17/frames
et.htm
2 | P a g e
2.2 Open ODS View Open ODS Views enable you to define SAP BW data models for external objects like database tables,
database or SAP HANA views, virtual tables or SAP BW DataSources (for direct access). These data
models allow flexible integration without the need to create InfoObjects. This flexible type of data
integration makes it possible to consume external data sources in SAP BW without staging, combine
data sources with SAP BW models and physically integrate (load) external data sources by creating
DataSources.
The Open ODS View is a SAP BW metadata object that provides a structure description with
attributes (fields) and data types. It represents a view on a source and adds analytic metadata to this
source.
The Open ODS View does not have separate storage for transaction data or master data. This means
persistency and analytic modeling are decoupled for the Open ODS View.
http://help.sap.com/saphelp_nw74/helpdata/en/3c/2501f075cb42b1aef74b6b4e5fa9de/conten
t.htm
2.3 DataStore Object (advanced) The DataStore Object (advanced) – from now on called Advanced DataStore Object for better
readability – will become the central object for modeling persistences and replace InfoCubes and
DataStore Objects. Nevertheless, the classic InfoProviders will still be available in addition to the
Advanced DataStore Object and will be supported.
If the required properties are set accordingly, the Advanced DataStore Object can be used in various
layers of the data warehouse. To simplify the modeling of the Advanced DataStore Object, we provide
templates that can be used to generate the required properties. The Advanced DataStore Object can
contain both fields and InfoObjects. With its new Request Management, it is particularly well suited
to deal with frequent loading and large amounts of data.
In context of this guide the object will be in focus since it can be generated as persistency by an Open
ODS View.
http://help.sap.com/saphelp_nw74/helpdata/en/25/3a8dd2746045ea9b84fedd8b966609/fra
meset.htm
3. Sample data model and integration scenarios
3.1 Integration Scenarios Among the new capabilities with SAP BW 7.4 powered by SAP HANA the integration of external data
is especially important because it allows creating “mixed scenarios” – combining data models from
SAP BW and the native SAP HANA side. The integration of data from the SAP HANA side can be
achieved in two ways. One option is to leverage the capabilities of the new CompositeProvider in SAP
BW 7.4:
3 | P a g e
This is the easiest and most straight forward approach when it comes to transaction data. However,
it has a few prerequisites, e.g. the external data has to be within the same SAP HANA instance and
needs to be provided via an SAP HANA Analytic or Calculation View.
Another option is to integrate external date into the SAP BW data model via Open ODS Views. This
introduces an additional model layer (since the recommendation is to introduce a
CompositeProvider on top) and hence some complexity, but we will see that it also adds a number of
services which simplify dealing with external data. For example, it opens up the reach of SAP BW to
external databases (via SAP HANA Smart Data Access) and can directly work on SQL structures like
database tables or views.
To make this discussion more concrete, let us consider a specific scenario with sales and customer
data from an external source that does not conform to the classic SAP data model and then discuss
various topics that have to be addressed when integrating this model into SAP BW.
3.2 Sample Data Model Below you see the definition of tables containing sales order and customer information. Assume we
want to leverage SAP BW OLAP capabilities on this data in a first step and then combine the external
data with customer master data from an InfoObject.
How to integrate external data into SAP BW powered by SAP HANA
4 | P a g e
Already a first glimpse at these structures shows some of the problems we have to deal with:
we have to integrate transaction and master data in a virtual way
data types: fields like SalesOrderID, RevisionNumber or CustomerID are locigally characteristics but of type INTEGER, so they do not fit to the SAP BW InfoObject world.
Other issues that are maybe not as obvious include:
format conversions: when combining external data with a SAP BW InfoObject we might have to perform ALPHA conversions of the external data, depending on the settings of the InfoObject.
transformations: there are various attributes containing dates, like OrderDate, DueDate, etc. all stored as NVARCHAR(23) which will require transformation in order to leverage date functionality of SAP BW.
4. Considerations for integration
4.1 Origin of data and source types The first consideration obviously is whether the source model resides in a separate database or just
in a different schema of the SAP HANA database on which your SAP BW systems is installed.
Open ODS Views support a variety of source types including federated data via SAP HANA Smart
Data Access, database tables or views, and SAP HANA Views (which can be consumed via their
generated column view in the _SYS_BIC schema). CompositeProviders require an SAP HANA View
(e.g. calculation view) as their source.
The illustration below shows the possibilities:
So, from a technical perspective the recommendation is as follows:
1) If data is located in an external source use connection technologies like Smart Data Access or DB Connect together with Open ODS Views
If the data is contained in the same SAP HANA database, we have options 2 and 3:
2) Open ODS View as integration layer 3) Integrate SAP HANA Information Models in the CompositeProvider
How to integrate external data into SAP BW powered by SAP HANA
5 | P a g e
4.2 External master data External master data can be easily integrated with Open ODS Views. They support time
dependency as well as language dependent and language independent texts. Also SAP BW Analysis
Authorizations can be used with master data Open ODS Views to define reusable authorizations.
Moreover, just like InfoObjects, they can be associated to fields of a CompositeProvider.
SAP First Guidance – Document Title
April 2016 1
4.3 Adjusting the model to SAP BW standards From a technical perspective, mapping the external data model to SAP BW is certainly the most critical
aspect. Differences in naming conventions, data types and data formats have to be taken into account. In
this section, we will go through the details of this integration exercise for both options 2) and 3) above.
Data Type Conversions Since SAP BW is based on ABAP technology, it works with the data types of the ABAP server and ABAP
dictionary. The classical, InfoObject based SAP BW metadata model, works with a smaller set of data types.
For example, characteristics have to be of type CHAR or NUMC, while types like INTEGER are not
supported. Therefore, in many situations type conversions are required before SAP BW OLAP functionality
can be leveraged on external data structures.
There are various ways to do these conversions. Open ODS Views perform the most frequent conversions
implicitely out of the box (see 4.3.1.1). This service reduces the conversion efforts for non-SAP data quite a
bit, but some more advanced conversions might still require the implementation of an explicit conversion.
This can be done using SAP HANA Views (see 4.3.1.2), SQL Views or even SAP BW transformations. Of
course it makes sense to combine explicit and implicit conversions, allowing to focus on the more “tricky”
aspects while letting the system take care of the straight forward conversions.
Before SAP HANA SPS 10, we do not recommend implementing explicit conversions in SQL Views because
they cannot be included in a SAP HANA Calculation Scenario which is required for SAP BW Query runtime
optimizations.
When using SAP BW transformations for explicit conversions, we recommend checking if SAP HANA
execution is possible – otherwise query performance will of course suffer.
Now let’s go through these options for our sample data model.
4.3.1.1 Implicit conversions via Open ODS View
Open ODS Views do automatic conversions of external data types to InfoObject types, depending on the
field semantics. For example, an INTEGER field modeled to be a characteristic of an Open ODS View will
automatically be converted to CHAR(11) with conversion exit INTCU, taking into account the maximal
number of 10 digits for a 4 byte integer and the sign in case of negative integers and filling in leading zeros
if necessary.
From the screenshot below we can see that without any additional effort INTEGER fields like SalesOrderID,
Status, etc. can be used as characteristics.
SAP First Guidance – Document Title
April 2016 2
Since the internal data types used by Open ODS Views are not relevant for most modeling tasks, they are
not shown in the Eclipse Modeling Tools. The backend service transaction RSODSVIEW offers a tab
“Preview for Query” which provides details on the internal representation of an Open ODS View.
4.3.1.2 Conversion via SAP BW Transformation
A second option to implement conversions is using a SAP BW Transformation in a SAP BW dataflow. Such
a dataflow, consisting of a DataSource, an InfoSource based on fields and a Transformation can easily be
created from an Open ODS View and adjusted according to the specific requirements.
Below you see an example of such a dataflow. Some data types have been adjusted in the target InfoSource
– hence e.g. SALESORDERID is converted to NUMC(15) automatically. Of course, more elaborate
conversions are possible, too. For performance reasons it is, however, strongly recommended to ensure
SAP First Guidance – Document Title
April 2016 3
that SAP HANA Execution of the Transformation is possible by leveraging standard transformation
capabilities and functions.
Note that this conversion option is only possible in combination with Open ODS Views – a
CompositeProvider cannot use an InfoSource/Transformation as a source object.
4.3.1.3 Conversion via SAP HANA View
As a third option, conversions can be implemented in a SAP HANA View by creating calculated columns. In
the screenshot below you see two calculated columns SalesOrderID_BW and Status_BW of type
NVARCHAR that contain string converted values of the original INTEGER columns.
SAP First Guidance – Document Title
April 2016 4
This SAP HANA View (in our case an Analytic View) can now directly be used in a CompositeProvider. Below
you can see that the converted columns can be used in the CompositeProvider while some of the original
ones cannot.
All three options are viable and supported. The implicit conversion handling of Open ODS Views certainly
simplifies implementation and provides standard conversions for most data types out of the box. On the
other hand, there will be situations where they do not fulfill the specific requirements of a scenario.
In such cases, it makes sense to combine the implicit logic with explicit conversions via SAP HANA Views
or SAP BW Transformations. This can be achieved by consuming a SAP HANA View in an Open ODS View,
separating the more advanced conversions in the SAP HANA View from standard conversions in the Open
ODS View.
Just like in a SAP HANA View, calculated fields can also be added using InfoSource and Transformation.
The example below shows how time stamps can be converted to date fields using a SAP HANA enabled
Transformation.
SAP First Guidance – Document Title
April 2016 5
Data Formats Besides data types, also the exact data format is important, especially when combining external data with
SAP BW master or transaction data. SAP BW InfoObjects frequently contain format settings, e.g. the
ALPHA conversion exit. When master data from such an InfoObject is combined with external facts, SAP
BW’s analytic engine requires the external data to be provided in the same format. Consequently, there is
frequently a need for additional conversions to ensure the correct data format.
In our example, let’s assume we want to associate our sales data with customer master data from a SAP
BW InfoObject for which the ALHPA conversion exit is active. Again there are several options to address
the issue.
Again, Open ODS Views offer such conversions as a service. When the external facts are integrated via an
Open ODS View with associations to the required InfoObjects, the necessary conversions are automatically
generated. It should be noted, that the Open ODS framework takes a “conservative” approach –
conversions are always applied even if the source delivers data in the required format. Again, there can be
situations, where more specific conversions are necessary.
In such cases, it is of course also possible to implement the required format conversions using SAP HANA
Views or SAP BW Transformations.
Parameters As of SAP BW 7.4 SP10, SAP HANA Views with parameters can only be integrated into CompositeProviders
directly. Open ODS Views do not support parameters. See also SAP Note 2142344 - Open ODS views and
SAP HANA Views with parameters.
5. Architecture considerations Besides the technical aspects discussed in the previous sections, also architecture considerations should
be taken into account when integrating external data with SAP BW.
5.1 Reusable Components A fundamental architecture principle is to create and leverage reusable components. Of course, there may
be situations where a pragmatic approach needs to be taken and architecture considerations are not of
high priority. However, in general it makes sense to spend some additional effort in order to build more
flexible and adaptable solutions.
SAP First Guidance – Document Title
April 2016 6
Open ODS Views are well suited to create such reusable components. In the case of master data, they can
be used to create one layer which prepares master data with all relevant semantics, like time dependency,
(language-dependent) texts, associations to other master data or texts etc. and also authorizations. These
Open ODS Views can then, just like InfoObjects, be used in context with multiple transactional data
structures. When combining external transaction data with InfoObjects, Open ODS Views again allow to
model an integration layer, which encapsulates the fundamental type and format conversions and can be
consumed in virtual data marts via CompositeProviders.
CompositeProviders also allow a certain degree of reuse. With SAP BW 7.4 SP10 it is possible to explicitly
enable a CompositeProvider to be used within other CompositeProviders. Certain restrictions exists with
respect to join scenarios in this case, e.g. only the left part of a JOIN can be a CompositeProvider.
5.2 To virtualize or not to virtualize… While virtualization provides the leanest and fastest to implement solution for integration of external data
– and should therefore certainly be considered first – not all the service levels associated with a data
warehouse can be provided in a virtualized architecture. Besides the obvious performance impact (for
more information please refer to SAP Note 2271658 - Design Considerations for Composite Provider),
requirements like the need for stable data snapshots (e.g. sales data from March 31, 6pm), data
harmonization, etc. might be relevant – or become relevant over time. Therefore, a flexible architecture
should be built in a way such that these service levels can be achieved at any time in an incremental way –
without having to rebuild the whole data model.
Open ODS Views provide basic mechanisms to adapt a solution to such changes in requirements. Via the
Generate Dataflow button in the screenshot below, a data flow including Transformation, DTP and an
Advanced DataStore Object with fields can be generated and adjusted according to specific needs.
SAP First Guidance – Document Title
April 2016 7
6. Summary and decision help In the decision table below, we try to summarize the various aspects discussed in this paper. The general
recommendation is to choose an architecture consisting of an Open ODS View for integration and a
CompositeProvider for modelling the virtual data mart layer. This architecture provides flexibility and a set
of standard services ranging from type conversions to generation of persistencies. For the type conversion
tasks it is key to ensure that they are executed in SAP HANA directly. This can be ensured by using SAP
HANA Views for those specific conversions that go beyond the standard capabilities provided by Open ODS
Views. The alternative of using a SAP BW Transformation is an option, too, but it needs to be carefully
checked that SAP HANA execution is possible so that this technique meets reporting performance
requirements.
For reference, we have listed and rated several decision criteria below.