XML for Analysis Stefan Biedenstein Barbara Neumann SAP TechEd ‘03 Basel

SAP TechEd ‘03 Basel
XML for Analysis
Stefan Biedenstein
Development Architect BI, SAP AG
Barbara Neumann
Product Manager BI, SAP AG
Learning Objectives
As a result of this workshop, you will
be able to:
Understand the basics of Extensible Markup Language for
Analysis (XMLA)
Explain the benefits and typical application scenarios for
XMLA in general
Understand the specific features and tools of the SAP
Business Information Warehouse (BW) XMLA provider
Request OLAP metadata from BW with the Discover method
Run multidimensional queries in BW using the Execute
method
Understand the basics of Multidimensional Expression
grammar (MDX)
Create C# proxies from the XMLA Web Services Description
Language (WSDL) file
 SAP AG 2003, BW253, Neumann/Biedenstein / 2
© 2003 SAP AG
BW 253, Neumann/Biedenstein
1
SAP TechEd ‘03 Basel
Learning Objectives continued
What this workshop will not teach:
Details of Web (services) foundation technologies
XML (Extensible Markup Language)
SOAP (Simple Object Access Protocol)
SAX (Simple API for XML)
DOM (Document Object Model)
XSL (Extensible Stylesheet Language)
Details on how to consume XMLA on a specific platform
.NET
J2EE
Extensive MDX and OLAP domain knowledge
 SAP AG 2003, BW253, Neumann/Biedenstein / 3
Agenda
Introduction
XMLA defined, history of OLAP standards and XMLA
XMLA Versions, architecture, foundation technologies
BW XMLA provider
Demo sample client
Discover
Discover method, structure of XMLA metadata
Exercises: browsing dimensions, levels, members
Execute
Execute method, multidimensional expressions
Exercises: a simple ranking list
WSDL
XMLA WSDL
Exercise: Generating code with a Service Description:
Generating a C# proxy via WSDL which connects a
.NET application to a BW Web service.
 SAP AG 2003, BW253, Neumann/Biedenstein / 4
© 2003 SAP AG
BW 253, Neumann/Biedenstein
2
SAP TechEd ‘03 Basel
Introduction: What is XML for Analysis?
XMLA is a SOAP-based interface for exposing OLAP and Data
Mining data sources as Web services. It advances some of the
successful concepts of OLE DB for OLAP to a cross-platform
Web service API.
The XMLA API defines 2 methods:
Discover: Used to obtain metadata and information about a
Web service
Execute: Used to execute (M)ulti(D)imensional
E(X)pressions (MDX) or provider-specific commands
 SAP AG 2003, BW253, Neumann/Biedenstein / 5
Introduction: Simple Object Access Protocol (SOAP)
Provides a standard packaging
structure for transporting XML
messages over various low-level
protocols such as HTTP or SMTP.
Defines encoding rules for
mapping application data types
into an XML format as well an
RPC mechanism.
SOAP can be regarded as the
most important cornerstone of
interoperability, as it provides an
agreed-upon communication
protocol between heterogeneous
clients and servers.
SOAP envelope
SOAP header
SOAP block
..
SOAP block
SOAP body
SOAP block
..
SOAP block
 SAP AG 2003, BW253, Neumann/Biedenstein / 6
© 2003 SAP AG
BW 253, Neumann/Biedenstein
3
SAP TechEd ‘03 Basel
Introduction: XMLA Architecture
Client
XML for Analysis
Provider
Implementation
Discover
Execute
UI
Client
functions
Discover,
Execute
Calls
S
O
A
P
H
T
T
P
Internet
Internet
Or
OrLAN
LAN
H
T
T
P
S
O
A
P
Data
Discover,
Execute
Call Server
Any server (Solaris, Linux,
Windows) at any location
Any client (Windows, Linux,
Macintosh) at any location
 SAP AG 2003, BW253, Neumann/Biedenstein / 7
Introduction: Why XMLA?
There was still the need for a broadly applicable/accepted lowfootprint OLAP API.
Web services are gaining momentum:
Characteristics
XML-based (eliminates networking, OS or platform binding;
interoperability; developers have begun to know XML well)
Loosely-coupled (client is not tied to WS directly; WS can change over
time without compromising client’s ability to interact with the service)
Coarse-grained (structure of services is driven by a methodological
rather than technical mindset)
Benefits
Simplifies connectivity and reduces cost by agreeing upon standards.
Enables data integration through XML.
Provides robustness and unification of interface technology.
Reduces the need for customization.
Assembles applications from standardized functional building blocks.
Provides full visibility of the value chain to all participants.
Integrates Intranet and Extranet.
 SAP AG 2003, BW253, Neumann/Biedenstein / 8
© 2003 SAP AG
BW 253, Neumann/Biedenstein
4
SAP TechEd ‘03 Basel
Introduction: XMLA Versions
Version
Date Release
Changes
1.0
April 2001
Initial Release of the
Specification
1.1
November 2002
Minor Modifications after
receiving feedback from
actual use
1.2
TBD
Leveling???
mdXML???
 SAP AG 2003, BW253, Neumann/Biedenstein / 9
Introduction: XMLA application scenarios
Beyond E-commerce, there is an even greater need to share information.
Add value to your offering by providing self-service access.
Just-in-Time Inventory
Market/Environment data
Consumer provides to a Supplier just-in-time
inventory data reports exposed as Web
services.
Metrics represented in the reports alert about
low inventory.
The reports are interactive, so you drill into
historic data, comparing today's fluctuation in
stock levels with corresponding days in other
weeks or months.
An electric utility
company
exposes its BI platform, so
commercial and industrial
customers can better
understand their power
consumption and related
costs.
Nielsen and others offer their services in the
yellow pages.
Integration and consumption of such data
becomes much easier without requiring a very
close business relationship with such service
providers.
A financial services
company
A brokerage firm
provides BI Web services, so
auditors and regulators have
an appropriate level of
visibility into the company's
dealings.
supports BI Web services so
institutional investors can
analyze (in a
multidimensional way) the
performance of financial
instruments executed
through the brokerage.
 SAP AG 2003, BW253, Neumann/Biedenstein / 10
© 2003 SAP AG
BW 253, Neumann/Biedenstein
5
SAP TechEd ‘03 Basel
Introduction: BW Open Analysis Interfaces
OLE DB for OLAP
OLAP BAPIs
XML for Analysis
based on COM as
protocol
Business Application
Programming interface
based on XML as
exchange format
driver installation on
clients
based on RFC as
protocol
based on HTTP/SOAP
as protocol
Windows platform only
access library available
on any SAP platform
any platform
MDX as query language
available since BW 1.2B
MDX as query language
available since BW 3.0A
MDX as query language
available since BW 2.0A
 SAP AG 2003, BW253, Neumann/Biedenstein / 11
Introduction: Direct Access to InfoCubes
Definition of BW Query not necessary but recommended
Available Objects:
All basic InfoCubes and MultiProvider as CUBE
All characteristics and key figures as DIMENSION / MEASURE
But no navigational attributes and no restricted or calculated key
figures
SELECT
{ [Measures].[0D_DOCUMENT],
[Measures].[0D_OORVALSC]
} ON COLUMNS,
NON EMPTY
[0D_PLANT].MEMBERS
ON ROWS
FROM [$0D_SD_C03]
WHERE
( [0CALMONTH].[200101],
[0D_COUNTRY].[US] )
JAN 2001,USA
Number of documents Open orders
Frankfurt
224,000
$ 1.439.134,33
38,000
$ 93.539,90
Manchester 54,000
$ 187.235,32
Calgary
39,000
$ 187.772,97
Chicago
93,000
$ 970.586,14
 SAP AG 2003, BW253, Neumann/Biedenstein / 12
© 2003 SAP AG
BW 253, Neumann/Biedenstein
6
SAP TechEd ‘03 Basel
Introduction: SE37
SE37 RSBB_URL_PREFIX_GET, Service URL :
 SAP AG 2003, BW253, Neumann/Biedenstein / 13
Introduction: SICF
SICF Service Maintenance (sap/bw/xml/soap/xmla) :
 SAP AG 2003, BW253, Neumann/Biedenstein / 14
© 2003 SAP AG
BW 253, Neumann/Biedenstein
7
SAP TechEd ‘03 Basel
Introduction: MDXTEST
MDXTEST - MDX Testeditor :
 SAP AG 2003, BW253, Neumann/Biedenstein / 15
Demo Sample Client
 SAP AG 2003, BW253, Neumann/Biedenstein / 16
© 2003 SAP AG
BW 253, Neumann/Biedenstein
8
SAP TechEd ‘03 Basel
Discover: Method Signature
Discover (
[in] RequestType As EnumString,
[in] Restrictions As Restrictions,
[in] Properties As Properties,
[out] Result As Rowset)
RequestType
Specifies the structure and content of the Rowset returned
in the Result parameter (DISCOVER_DATASOURCES,
MDSCHEMA_CUBES, MDSCHEMA_DIMENSIONS…)
Restrictions
Sets restrictions for columns of the Result, in order to filter
the rows returned. Mandatory, but can be empty.
Properties
Specifies a collection of XML for Analysis properties
(return format of the Result set, timeout, locale in which the
data should be formatted)
 SAP AG 2003, BW253, Neumann/Biedenstein / 17
Discover: SOAP Example Discover Request
<SOAP-ENV:Envelope
xmlns:SOAP-ENV=
‘http://schemas.xmlsoap.org/soap/envelope/'
SOAP-ENV:encodingStyle=
'http://schemas.xmlsoap.org/soap/encoding/'>
<SOAP-ENV:Body>
<Discover>
<RequestType>MDSCHEMA_CUBES</RequestType>
<Restrictions>
<RestrictionList/>
</Restrictions>
<Properties>
<PropertyList>
<DataSourceInfo>DataSource=local
</DataSourceInfo>
<Format>Tabular</Format>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
 SAP AG 2003, BW253, Neumann/Biedenstein / 18
© 2003 SAP AG
BW 253, Neumann/Biedenstein
9
SAP TechEd ‘03 Basel
Discover: OLE DB Schema Objects
OLE DB for OLAP
SAP BW
Catalogs
InfoCubes
Schema
Not supported
Cubes
Queries
Dimensions (Measures)
Hierarchies
Characteristics(Keyfgs)
Hierarchies (external)
Numbered Levels or
Dummy level names
Levels
Members
Characteristic Values
Properties
Display Attributes
 SAP AG 2003, BW253, Neumann/Biedenstein / 19
Discover: Mapping of Query Objects
BW Query
QUERY_CUBE structure
Charact.1:Region
Dimension1:Region
Charact.2:Fiscal Year
Dimension2:Fiscal Year
Charact.3:Customer
Dimension3:Customer
Structure for Key Figs.
Measures Dimension
Member1: Cost
Member2: Sold Pieces
Measures
Member3: Revenue
Cost
Sold Pieces
Revenue
Variable1: Cost Area
SAP Variables Cost Area
 SAP AG 2003, BW253, Neumann/Biedenstein / 20
© 2003 SAP AG
BW 253, Neumann/Biedenstein
10
SAP TechEd ‘03 Basel
Discover: Exercise 1
Access cube metadata
Access dimension metadata
Access level metadata
Access Member metadata
User Name: BW253-01 to -##
Password: init
 SAP AG 2003, BW253, Neumann/Biedenstein / 21
Execute: Method Signature
Execute (
[in] Command As Command,
[in] Properties As Properties,
[out] Result As Resultset)
Command
Specifies a provider-specific statement to be executed.
XML for Analysis multidimensional providers must support
the mdXML language, but they can also support other
commands as needed.
Properties
Specifies a collection of XML for Analysis properties:
return format of the Result set
timeout
locale in which the data should be formatted
 SAP AG 2003, BW253, Neumann/Biedenstein / 22
© 2003 SAP AG
BW 253, Neumann/Biedenstein
11
SAP TechEd ‘03 Basel
Execute: MultiDimensional EXpressions (MDX)
XMLA is a linguistic interface that uses
MultiDimensional Expressions (MDX) for querying
multidimensional Datasets
Parts of MDX statements:
FROM clause
-> selects a cube
SELECT clause
-> defines the axes
ON COLUMNS
-> defines the columns axis
ON ROWS
-> defines the rows axis
WHERE clause
-> filter conditions
 SAP AG 2003, BW253, Neumann/Biedenstein / 23
Execute: MDX Example
SELECT
{ [Measures].[CKF_SI_PROFIT],
[Measures].[0D_DOCUMENT],
[Measures].[0D_OORVALSC]
} ON COLUMNS,
NON EMPTY
[0D_PLANT].MEMBERS
ON ROWS
FROM [0D_SD_C03/SAP_DEMO_ODBO]
WHERE
( [0CALMONTH].[200101],
[0D_COUNTRY].[US] )
JAN 2001,USA
Profit
Number of documents
Open orders
$ 702.571,00
33,000
$ 266.658,00
Frankfurt
$ 236.088,00
9,000
$ 26.664,00
Manchester
$ 95.286,00
6,000
$ 26.664,00
Calgary
$ 226.487,00
9,000
$ 46.665,00
Chicago
$ 144.710,00
9,000
$ 166.665,00
 SAP AG 2003, BW253, Neumann/Biedenstein / 24
© 2003 SAP AG
BW 253, Neumann/Biedenstein
12
SAP TechEd ‘03 Basel
Execute: More MDX
Basic Entities
Members – items representing the occurrence of data
Examples: [City].[Las Vegas], [Time].[Year].[2003]
Tuples – collections of members from multiple dimensions
Examples: ([City].[Las Vegas],[Time].[Year].[2003],[Event].[SAP
TechEd 2003])
Sets – collections of tuple elements
Examples: {([City].[Los Angeles],[Time].[Year].[2002]),([City].[Las
Vegas],[Time].[Year].[2003]),}
Values – numbers,strings
Examples: 5, “red”
Functions/Expressions that generate these Basic Objects
Member value expressions
Examples: <member>.PARENT
Set value expressions
Examples:
TOPCOUNT(<set>,<index>,<numeric_value_expression>)
Numeric value functions
Examples: MAX(<set>[, <numeric_value_expression>])
 SAP AG 2003, BW253, Neumann/Biedenstein / 25
Execute: Exercise 2
MDX Statement 1: Top five sales organizations
based on order value
MDX Statement 2: Sales organizations with
incoming orders of greater than five million
 SAP AG 2003, BW253, Neumann/Biedenstein / 26
© 2003 SAP AG
BW 253, Neumann/Biedenstein
13
SAP TechEd ‘03 Basel
Web Services Description Language (WSDL)
XML grammar for describing Web
services.
WSDL documents can be used for
generating implementation code,
which therefore allows clients to
automatically understand how to
interact with a given service.
<definitions>
<import>*
<types>
<schema></schema>
</types>
<message>*
<part></part>
</message>
<PortType>*
<input></input>
<output></output>
<fault></fault>
</PortType>
...
</definitions>
 SAP AG 2003, BW253, Neumann/Biedenstein / 27
WSDL: Exercise 3
Exercise:
Generating code with a Service Description:
Generating a C# proxy via WSDL which connects a .NET application
to a BW Web service.
 SAP AG 2003, BW253, Neumann/Biedenstein / 28
© 2003 SAP AG
BW 253, Neumann/Biedenstein
14
SAP TechEd ‘03 Basel
Summary
XMLA is a Web service API for OLAP and data mining
XMLA advances concepts of OLE DB for OLAP
The API consists of two methods: Discover, Execute
Discover is used to access a provider’s metadata
Execute is used to process MDX statements
The current version of XMLA is 1.1
XMLA is driven by the XMLA council
 SAP AG 2003, BW253, Neumann/Biedenstein / 29
Further Information
On the SAP Help Portal:
Open Analysis Interfaces, including XMLA:
http://help.sap.com/:
SAP NetWeaver
[Select Language]
SAP Business Information Warehouse
Open Analysis Interfaces
SAP BW 3.1/BI Content 3.2 Add-On
On the Internet:
XMLA council:
http://www.xmla.org/
World Wide Web Consortium (W3C) specification for SOAP:
http://www.w3.org/TR/SOAP/
W3C information on Extensible Markup Language (XML):
http://www.w3.org/XML/
W3C information on Web Services Description Language (WSDL):
http://www.w3.org/TR/wsdl
Related Workshops/Lectures at SAP TechEd 2003
BW204 – Universal Data Access via SAP BW Java Integration Functionality
Oct. 1, 2003, 4 pm, Room H11
 SAP AG 2003, BW253, Neumann/Biedenstein / 30
© 2003 SAP AG
BW 253, Neumann/Biedenstein
15
SAP TechEd ‘03 Basel
Questions?
Q&A
 SAP AG 2003, BW253, Neumann/Biedenstein / 31
Feedback
Please complete your session evaluation and
drop it in the box on your way out.
Thank You !
The SAP TechEd ’03 Basel Team
 SAP AG 2003, BW253, Neumann/Biedenstein / 32
© 2003 SAP AG
BW 253, Neumann/Biedenstein
16
SAP TechEd ‘03 Basel
Copyright 2003 SAP AG. 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®, NT®, EXCEL®, Word®, PowerPoint® and SQL Server® are registered trademarks of
Microsoft Corporation.
IBM®, DB2®, DB2 Universal Database, OS/2®, Parallel Sysplex®, MVS/ESA, AIX®, S/390®, AS/400®, OS/390®,
OS/400®, iSeries, pSeries, xSeries, zSeries, z/OS, AFP, Intelligent Miner, WebSphere®, Netfinity®, Tivoli®, Informix
and Informix® Dynamic ServerTM are trademarks of IBM Corporation in USA 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®, the Citrix logo, ICA®, Program Neighborhood®, MetaFrame®, WinFrame®, VideoFrame®, MultiWin® and
other Citrix product names referenced herein are trademarks of Citrix Systems, Inc.
HTML, DHTML, XML, XHTML 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.
MarketSet and Enterprise Buyer are jointly owned trademarks of SAP AG and Commerce One.
SAP, R/3, mySAP, mySAP.com, xApps, xApp 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 in several other
countries all over the world. All other product and service names mentioned are the trademarks of their respective
companies.
 SAP AG 2003, BW253, Neumann/Biedenstein / 33
© 2003 SAP AG
BW 253, Neumann/Biedenstein
17