Composite provider.pdf

(1)

CompositeProvider

Silvia Bratz / Klaus Nagel, TIP IMP BW Version 1.1

(2)

Introduction

The CompositeProvider is a new InfoProvider type introduced with BW7.30. It offers the possibility to combine InfoProviders via UNION, INNER JOIN and LEFT

OUTER JOIN.

One characteristic of the CompositeProvider is, that one “initial” Provider has to be taken over to the CompositeProvider with a UNION connection as a basis.

CompositeProviders can only be used in combination with BW powered by SAP

HANA (and partially with BWA, which is not covered in this document).

UNION and JOIN operations are executed in HANA and not the application

server – if possible from a data consistency point of view – without join column information.

BEx Queries can be created on CompositeProviders as on any other BW

InfoProvider.

(3)

Use Cases

Currently exist three major use cases for leveraging a CompositeProvider

I. BW Workspaces – the CompositeProvider allows to quickly and easily combine

central BW data with uploaded local data (Excel, csv, …). This is done in the Workspace Designer.

II. Rapid-Prototype Models – IT can quickly and flexible combine

AnalyticIndexes created in APD or published SAP HANA Models). This is done in the transaction RSLIMO.

III. JOINs between InfoProvider – certain BW InfoProvider can be combined

using the JOIN operation allowing IT to create new scenarios not possible or very

expensive with standard techniques (MultiProvider, InfoSet). This is done in transaction RSLIMOBW.

While use cases I and II are clearly defined, use case III needs to be assessed carefully and scenario-by-scenario in order to achieve the expected results.

(4)

Considerations (1)

Transportability: A CompositeProvider can be transported – if it contains only standard, transportable BW InfoProviders.

Execution of JOIN: A CompositeProvider always has a corresponding HANA ColumnView that contains the modeled JOIN and UNION operations. Thus JOIN

and UNION are executed by the HANA CalcEngine, which (in general) offers a much better performance compared to the application server.

 Exception: If the JOIN condition is “non unique” (see “specifics”) the JOIN operation is

executed by respecting the JOIN column information to avoid wrong results. This, of course, has a negative impact on performance, especially if the JOIN column has a high cardinality, as more data has to be requested and transferred from the database.

(5)

Considerations (2)

INNER versus LEFT OUTER JOIN: An INNER JOIN is always faster – but this

kind of referential integrity may not always be given. If the performance

degeneration due to the OUTER JOIN is too big, it might be worth the effort to model referential integrity during staging.

CompositeProvider versus MultiProvider: Always model a MultiProvider if the

model only contains UNIONs. The BW AnalyticManager (aka OLAP Engine) makes sure the best execution plan is used.

CompositeProvider versus InfoSets: If the InfoSet only contains operations and

data that can be modeled also in a CompositeProvider, the CompositeProvider is most likely the better (faster) choice.

(6)

Considerations (3)

Temporal JOIN: If a temporal JOIN between the Providers is needed, a

CompositeProvider cannot be used (currently only possible with InfoSet).

Non-cumulative key figures: Non-cumulative key figures currently cannot be used in a CompositeProvider.

Queries with Exception Aggregation: Exception Aggregation in Queries based on

a CompositeProvider can only be executed on HANA (query option „6‟) when

standard, transportable BW InfoProviders are contained exclusively.

Additionally, it is only possible to use Exception Aggregation on InfoObjects for which SID-based processing is possible (see page 24).

Planning scenarios: Currently it is not possible to use a CompositeProvider as

(7)

Some CompositeProvider

specifics in detail

(8)

Specifics

Non-unique JOIN condition

Null Handling

Exception Aggregation

Analysis Authorizations

(9)

Specifics

Non-unique JOIN condition

When fields of Providers are linked in a CompositeProvider and key figures are used in the analysis, it is important to check whether the join conditions used are unique.

If join conditions are non-unique, this might result in a multiplication of key figures, which might show wrong query results to the user in sum lines.

Therefore this specific is explained with some slides:

- Page 10: Example to introduce the problem

- Page 11: CompositeProvider model with linked fields/join conditions

- Page 12/13: Where to set the flag in case join condition is non-unique

- Page 14/15/16: Consequences when flag is set or not

- Page 17: Select statements sent to HANA

(10)

Non-unique JOIN condition (1)

A non-unique JOIN condition is … – Data Example (1:N) and Problem

Header Data Item Data

NO_HDR = Key Figure

NET_PRICE = Key Figure

 When the data is joined via field

DOC_NUMBER the key figure value of NO_HDR is shown per field

ORDER_ITEMS.

 However it is not correct to sum up these values as the key figure NO_HDR only counts once per DOC_NUMBER.

Correct Values in SUM lines Incorrect Values in SUM lines (multiplication of key figures)

(11)

Non-unique JOIN condition (2)

A non-unique JOIN condition is … – Join defined in CompositeProvider

CompositeProvider

 Header Data is taken over as UNION in the CompositeProvider  Item Data is taken over as INNER JOIN in the CompositeProvider  Join condition defined on DOC_NUMBER

 A drill-down is executed on ORDER_ITEMS  Analysis of sum lines of key figure NO_HDR

(12)

Non-unique JOIN condition (3)

A non-unique JOIN condition is … – Option set on level of the joined Provider

CompositeProvider and Options – RSLIMO/RSLIMOBW

 Correct Values in SUM lines in case of non-uniqueness  Standard option in RSLIMO/RSLIMOBW

 Addressee: IT department

 Join column information respected  leads to the transfer of more data from the database

 Incorrect Values in SUM lines in case of non-uniqueness  Standard option in BW Workspace Designer

 Addressee: Analyst in the Line of Business

 Join column information not respected  leads to the transfer of less data from the database

(13)

Non-unique JOIN condition (4)

A non-unique JOIN condition is … – Option set on level of the joined Provider

CompositeProvider and Options – BW Workspaces

Unique Join Columns not set

 Standard option in RSLIMO/RSLIMOBW

Unique Join Columns set

 Standard option in the BW Workspaces

RSWSPW

 In the BW Workspace Maintenance you can allow to change the standard option

BW Workspace Designer

 In the Designer per CompositeProvider the standard option can be changed (by deselecting the flag)

BW Workspace Maintenance

(14)

Non-unique JOIN condition (5)

BW Infoprovider

BW Query

BW Infoprovider BW

HANA-DB Aggregation, group by query _{and join columns} Aggregation, group by query _{and join columns} Aggregation, respecting the join

columns information

Join and Aggregation, group by query AND join columns

Header Data JOIN on

DOC_NUMBER

Item Data

6 records transferred from DB

(15)

Non-unique JOIN condition (6)

BW Infoprovider

BW Query

BW Infoprovider BW

HANA-DB Aggregation, group by query

and join columns Aggregation, group by query

and join columns

Aggregation, without join columns information

Join and Aggregation, group by query

Header Data JOIN on Item Data

DOC_NUMBER

3 records transferred from DB

(16)

Non-unique JOIN condition (7)

 Aggregation of Provider data takes place before the join is executed

 The OLAP calculates the sum lines

 By setting the Unique Join Columns setting, the OLAP is told that a

multiplication of key figures cannot happen. If the Join Columns are „in reality‟ non-unique the OLAP would deliver wrong results in the sum lines

 More data has to be transferred when the join is non-unique (and the Unique

Join Columns flag is not set)

 The default in RSLIMO/RSLIMOBW is that ‘Unique Join Columns’ is not set

 Reason: Data correctness is more important than performance

 The default in the BW Workspace Designer is that ‘Unique Join Columns’ is

set  Reason: Good performance for easy models created by the business

(17)

Non-unique JOIN condition (8)

A non-unique JOIN condition is … - BW HANA request for drill-down

 Drill-down on field ORDER_ITEMS: Field DOC_NUMBER has to be in the select statement to deliver correct results (otherwise this leads to a multiplication of key figures in the sum lines)

(18)

Non-unique JOIN condition (9)

Correct Values in SUM lines – 1:N Incorrect Values in SUM lines (multiplication of key figures) – 1:N

A non-unique JOIN condition is … – Business example: Join of related business data (document flow)

(19)

Specifics

Null Handling

In the MultiProvider use case the OLAP processor is able to distinguish between an empty key figure due to the fact that it has not been linked and a booked zero value. This is not possible with a CompositeProvider.

(20)

Null Handling – example (1)

 The OLAP processor can deal with nulls and is able to show empty key figures

due to the fact that they are not mapped instead of their initial value e.g. 0,00 (German decimal notation).

 In the CompositeProvider use case it is not possible to show the nulls to be able

to separate between not mapped key figures or booked zero values.

 Example – MultiProvider

MultiProvider

(21)

Null Handling – example (2)

 Example – MultiProvider

(22)

Null Handling – example (3)

 Example – CompositeProvider

(23)

Null Handling – example (4)

 Example – CompositeProvider

(24)

Specifics

CompositeProvider and Exception Aggregation

Exception Aggregation in Queries based on a CompositeProvider can only be executed on HANA (query option „6‟) when standard, transportable BW

InfoProviders are contained exclusively. Additionally, it is only possible to use

Exception Aggregation on InfoObjects for which SID-based processing is possible. Exception Aggregation on HANA is e.g. be executed when InfoCubes, DSOs with BEx-Flag or Navigational Attributes are used in a CompositeProvider.

Exception Aggregation is not executed on HANA when e.g. Display Attributes, VirtualProviders on HANA Models or Analytic Indexes (which are non-transportable Providers) are used in a CompositeProvider.

(25)

Specifics

Analysis Authorizations

In a CompositeProvider scenario it can happen that analysis authorizations are

defined on the contained MultiProvider, the Providers contained in the MultiProvider as well as on the CompositeProvider itself.

Article https://scn.sap.com/docs/DOC-29740 shows an example on how the

Analysis Authorizations apply in such a scenario.

Nevertheless, with BW 7.30 SP8, it is possible to determine for a

CompositeProvider whether the Analysis Authorizations defined on top of it should exclusively be used, i.e. the Analysis Authorizations defined on top of the

(26)

Specifics

Modeling JOINs in HANA models

(27)

Modeling JOINs in HANA Models

Creating HANA models on BW InfoProviders/tables is not supported (see note 1682131).

SAP NetWeaver BusinessWarehouse is not aware of JOINs modeled in the underlying HANA model  risk of key figure duplication!

For more details on the topic ‘Reporting on HANA models in BW-on-HANA‘,

(28)

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, PowerPoint, Silverlight, and Visual Studio 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, z10, z/VM, z/OS, OS/390, zEnterprise, PowerVM, Power

Architecture, Power Systems, POWER7, POWER6+, POWER6, POWER, PowerHA, pureScale, PowerPC, BladeCenter, System Storage, Storwize, XIV, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, AIX, Intelligent Miner, WebSphere, Tivoli, Informix, and Smarter Planet are trademarks or registered trademarks of IBM Corporation. Linux is the registered trademark of Linus Torvalds in the United States and other countries. Adobe, the Adobe logo, Acrobat, PostScript, and Reader are trademarks or registered trademarks of Adobe Systems Incorporated in the United States and other countries. Oracle and Java are registered trademarks of Oracle and its affiliates.

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.

Apple, App Store, iBooks, iPad, iPhone, iPhoto, iPod, iTunes, Multi-Touch, Objective-C, Retina, Safari, Siri, and Xcode are trademarks or registered trademarks of Apple Inc. IOS is a registered trademark of Cisco Systems Inc.

RIM, BlackBerry, BBM, BlackBerry Curve, BlackBerry Bold, BlackBerry Pearl, BlackBerry Torch, BlackBerry Storm, BlackBerry Storm2, BlackBerry PlayBook, and BlackBerry App World are trademarks or registered trademarks of Research in Motion Limited.

Google App Engine, Google Apps, Google Checkout, Google Data API, Google Maps, Google Mobile Ads, Google Mobile Updater, Google Mobile, Google Store, Google Sync, Google Updater, Google Voice, Google Mail, Gmail, YouTube, Dalvik and Android are trademarks or registered trademarks of Google Inc.

INTERMEC is a registered trademark of Intermec Technologies Corporation. Wi-Fi is a registered trademark of Wi-Fi Alliance.

Bluetooth is a registered trademark of Bluetooth SIG Inc.

Motorola is a registered trademark of Motorola Trademark Holdings LLC. Computop is a registered trademark of Computop Wirtschaftsinformatik GmbH.

SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer, StreamWork, SAP HANA, 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, iAnywhere, 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.

Crossgate, m@gic EDDY, B2B 360°, and B2B 360° Services are registered trademarks of Crossgate AG in Germany and other countries. Crossgate 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.

Composite provider.pdf