Building a multi-source universe

(1)

Public

1

Building a multi-source universe

Pierpaolo Vezzosi

September 10-13, 2012

Orlando, Florida

(2)

Public

2

Learning points

Learn the details of the multi-source universe technology

See how to build a simple multi-source universe

(3)

Public

3

Agenda

Definitions and architecture

What is the multi-source universe (MSU) technology

Architecture

Differences with “Data Federator XI3.0”

Building a universe

Connections for MSU

Data foundations for MSU

Business Layers for MSU

Optimizing a multi-source universe

Optimization algorithms

(4)

(5)

Public

5

The multi-source universe value proposition

The new BI4.0 multi-source universe (MSU) technology provides the ability to

create a universe federating data across multiple data sources.

Built on the great technology of “Data Federator XI3.0”, it was designed for

No additional license: the functionality is included in the BI platform licenses

No additional servers: the technology is installed with the BI plaftorm and

administered along with other servers in the CMC

No additional desing tools: Information design tool replaces the workflow of Data

Federator designer. Multisource is just a radio button away

(6)

Public

6

MSU architecture

DB

(7)

Public

7

MSU architecture

DB

Information design

tool

WebIntelligence

Crystal Reports

Enterprise

Dashboards

Explorer

Query server

(8)

Public

8

MSU architecture

DB

CMS

session

(9)

Public

9

Architecture

DB

Information design

tool

WebIntelligence

Crystal Reports

Enterprise

Dashboards

Explorer

Query server

CMS

session

(10)

Differences with Data

Federator XI3.0

(11)

Public

11

Data Federator XI3.0 deployment

Universe

Other data

integration

needs

SAP BOBJ

tools

DB

(12)

Public

12

BI 4.0 platform

BI 4.0 Multi source universe deployment

UNX Universe

SAP BOBJ

tools

BI 4.0

MSU= multi-source Universe

DB

(13)

Public

13

BI 4.0 platform

Available deployments with BI4.0 and DF XI3.0

UNX Universe

SAP BOBJ

tools

BI 4.0

Other data

integration

needs

UNV Universe

MSU= multi-source Universe

Direct SQL access

Data Federator XI 3.0 is still shipped for existing customers and specific use

cases not yet covered by MSU

(14)

Public

14

Data Federator XI3.0 and MSU BI 4.0 differences

Technical details

DF 3.0

MSU

Separate license

Yes

No

Separate, standalone install

Yes

No

Access with SQL from external applications

Yes

No

Connect XI3.x universes (UNV)

Yes

No

Use web services or to XML files as data sources

Yes

No

Use complex mapping rules

Yes

Limited*

Use native database syntax

No

Yes**

Create BI 4.0 universes (UNX)

No

Yes

Design workflow in the information design tool

No

Yes

* Mapping rules, data cleaning and lookup mechanisms in MSU are limited to SQL statements

which can be inserted in derived tables or calculated columns

** Native database syntax can be used in derived tables defined over a single source in an MSU

universe

Migration constraints:

•In BI4.0 it is not possible to migrate an existing DF XI3.0 project to the new technology

•It is not possible to build UNX universes on top of a DF XI3.0 project

(15)

(16)

Public

16

Create a connection for a multisource universe

(17)

Public

17

Create a data foundation for a multisource universe

(18)

Public

18

Create a business layer for a multisource universe

(19)

The data federation

administration tool

(20)

Public

20

The data federation administration tool (DFAT)

DFAT is installed in the BI4.0 client toolset. You can use it to:

Find bottlenecks

Which database server

is running slow?

Is there something I can

do at the database server

level to improve

performance?

Which query is taking too much

time to execute? Why? Is there

something I can do to improve

(21)

Public

21

The data federation administration tool (DFAT)

DFAT is installed in the BI4.0 client toolset. You can use it to:

Find bottlenecks

Understand the query plan

Where is the query

executed?

Can I push more

computation down to

the database engine?

What database statistics

will improve the query plan

(22)

Public

22

The data federation administration tool (DFAT)

DFAT is installed in the BI4.0 client toolset. You can use it to:

Find bottlenecks

Understand the query plan

Fine tune the query server settings

Can I optimize the memory

of my machine for MSU?

(23)

Public

23

The data federation administration tool (DFAT)

DFAT is installed in the BI4.0 client toolset. You can use it to:

Find bottlenecks

Understand the query plan

Fine tune the query server settings

Perform trial and error tests

What happens to the query

execution if I change my SQL

(24)

Public

24

Fine tuning MSU with the Data federation administration

tool

(25)

(26)

Public

26

The MSU optimization algorithms

The MSU technology provides multiple built-in optimization algorithms

Two goals

Push execution to databases as much as possible

(27)

Public

27

Techniques to optimize MSU execution

Gather database statistics

Fine tune connectors and the query server

Define an optimized data foundation

(28)

Public

28

Techniques to optimize MSU execution

Gather database statistics

Fine tune connectors and the query server

Define an optimized data foundation

(29)

Public

29

Gathering statistics to let the optimization algorithm

understand how to work

Statistics can enable semi join optimizations

(30)

Public

30

Techniques to optimize MSU execution

Gather database statistics

Fine tune connectors and the query server

Define an optimized data foundation

(31)

Public

31

DFAT parameter example

MAX_CONCURRENT_MEMORY_CONSUMING_QUERIES

Defines the number of queries that consume memory that can run concurrently

If you have many large queries:

• Set a low value

memory will be dedicated to few queries; no disk memory swapping will

be necessary

If you have many small queries:

(32)

Public

32

Techniques to optimize MSU execution

Gather database statistics

Fine tune connectors and the query server

Define an optimized data foundation

(33)

Public

33

Optimizing the data foundation to set up “intelligent

connections”

Intelligent connections use a table field to chose on which database the query

has to be run. It improves performance as only the needed database are

queried.

1. Define a derived table retrieving data from all the necessary sources

2. Add a field with a fixed, discriminating value by source

3. When querying, use the discriminating field as a filter

US

JP

(34)

Public

34

Setting up “intelligent queries”

Example

Define a derived table as:

**Select *, ‘US’ as country from Source1.US_Customers**

Union

**Select *, ‘JP’ as country from Source2.JP_Customers**

Then define an object on the Country field and use it as a filter.

When filtering for US, only source1 will be queried, source2 will be skipped.

If no filter is applied, both sources will be used.

JP

Kaori

JP

Nobuko

US

Tim

US

Jack

(35)

Public

35

Where to find more information

In the data federation administration guide:

20+ pages of optimization techniques

(36)

Public

36

Learning points

In this session we have:

Learned the details of the multi-source universe technology

Seen how to build a simple multi-source universe

Discovered how to optimize a multi-source universe

(37)

Thank You!

0610

Building a multisource universe

Contact information:

Pierpaolo Vezzosi

(38)

Thank you for participating.

Please provide feedback on this session by

completing a short survey via the event

mobile application.

SESSION CODE: 0610

(39)

Public

39

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 and Java are registered trademarks of Oracle and/or 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.

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, 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.

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.

(40)

Public

40

Weitergabe und Vervielfältigung dieser Publikation oder von Teilen daraus sind, zu welchem Zweck und in welcher Form auch immer, ohne die ausdrückliche schriftliche Genehmigung durch SAP AG nicht gestattet. In dieser Publikation enthaltene Informationen können ohne vorherige Ankündigung geändert werden. Die von SAP AG oder deren Vertriebsfirmen angebotenen Softwareprodukte können Softwarekomponenten auch anderer Softwarehersteller enthalten. Microsoft, Windows, Excel, Outlook, und PowerPoint sind eingetragene Marken der 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 und Informix sind Marken oder eingetragene Marken der IBM Corporation.

Linux ist eine eingetragene Marke von Linus Torvalds in den USA und anderen Ländern.

Adobe, das Adobe-Logo, Acrobat, PostScript und Reader sind Marken oder eingetragene Marken von Adobe Systems Incorporated in den USA und/oder anderen Ländern.

Oracle und Java sind eingetragene Marken von Oracle und/oder ihrer Tochtergesellschaften.

UNIX, X/Open, OSF/1 und Motif sind eingetragene Marken der Open Group. Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame und MultiWin sind Marken oder eingetragene Marken von Citrix Systems, Inc.

HTML, XML, XHTML und W3C sind Marken oder eingetragene Marken des W3C®_{, World Wide Web Consortium, Massachusetts Institute of Technology.}

SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer, StreamWork und weitere im Text erwähnte SAPProdukte und

-Dienstleistungen sowie die entsprechenden Logos sind Marken oder eingetragene Marken der SAP AG in Deutschland und anderen Ländern.

Business Objects und das Business-Objects-Logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius und andere im Text erwähnte Business-Objects-Produkte und Dienstleistungen sowie die entsprechenden Logos sind Marken oder eingetragene Marken der Business Objects Software Ltd. Business Objects ist ein Unternehmen der SAP AG. Sybase und Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere und weitere im Text erwähnte Sybase-Produkte und -Dienstleistungen sowie die entsprechenden Logos sind Marken oder eingetragene Marken der Sybase Inc. Sybase ist ein Unternehmen der SAP AG.

Alle anderen Namen von Produkten und Dienstleistungen sind Marken der jeweiligen Firmen. Die Angaben im Text sind unverbindlich und dienen lediglich zu Informationszwecken. Produkte können länderspezifische Unterschiede

aufweisen.

Die in dieser Publikation enthaltene Information ist Eigentum der SAP. Weitergabe und Vervielfältigung dieser Publikation oder von Teilen daraus sind, zu welchem Zweck und in welcher Form auch immer, nur mit ausdrücklicher schriftlicher Genehmigung durch SAP AG gestattet.