Cognos OLEDB Interface for SAP BW

(1)

 SAP AG; TechEd Kralsruhe; Integration of OLAP Frontends

Integrating OLAP Frontends

with OLE DB for OLAP

Guido Schröder

OLAP technology group

 SAP America, Inc. SAP™TechEd ‘98 Aug. 31-Sept. 3, 1998 sessioncode.ppt / 1

Talk Contents

~ BW architecture

~ What is OLE DB for OLAP?

~ OLE DB for OLAP Foundations

MD Schema

Dataset Object

MDX grammar

~ BW’s use of ODBO

Mapping of BW Objects to ODBO

Building QueryCubes

(2)

Non R/3 Production Data Extractor Non R/3 Production Data

Extractor Non R/3 OLTP Applications Non R/3 OLTP Applications

Architecture Overview

Business Information Warehouse Server R/3 OLTP Applications R/3 OLTP Applications OLTP Reporting OLTP Reporting Production Data Extractor Production Data Extractor BAPI BAPI Business Explorer 3rd party OLAP client

3rd party OLAP client3rd party OLAP client3rd party OLAP client3rd party OLAP clients3rd party OLAP clients

Browser Browser Administrator Workbench Operational Data Store Operational Data Store Staging Engine Staging Engine OLAP Processor OLAP Processor InfoCubes InfoCubes Meta Data Repository Meta Data

Repository Meta Data ManagerMeta Data Manager_{Meta Data Manager}Meta Data Manager _{Data Manager}_{Data Manager} OLE-DB for OLAP Provider

OLE-DB for OLAP Provider

ODBO ODBO Analyzer (hosted by MS Excel) Analyzer (hosted by MS Excel) InfoCatalog InfoCatalog Monitor Monitor Scheduling Scheduling Administration Administration

BW ODBO driver architecture

RFC Desktop with SAP

ODBO driver ( mdrmsap.dll; mdxpars.dll; scerrlkp.dll; saprfc.ini; librfc32.dll) Business Information Warehouse Server OLE DB-Objects ADO Consumer Consumer ODBO-Objects Dataset Object 3rd Party Client Tool OLAP Processor OLAP Processor Meta Data Repository Meta Data Repository Data Base Data Base OLAP API OLAP API

(3)

OLE DB: Universal Data Access

E-mail

E-mail SpreadsheetsSpreadsheets RDBMSRDBMS HTMLHTML Multidimensional data Multidimensional

data

OLEDB OLEDB OLEDB OLEDB OLEDB

for OLAP ActiveX Data Objects (ADO)

Applications

ADO MD

OLE DB for OLAP

~ Set of COM objects and interfaces

~ ODBO extends OLE DB for multidimensional data

~ Reuse of OLE DB

Common Connection Model

Rowset plays as an abstraction of tabular data plays a

central role

Command Interface for builing queries

~ ODBO adds

1 new object: Dataset

(4)

BW ODBO interface: Customer

Benefits

~ Protect previous investment in reporting tools

connect BW server to a broad spectrum of Applications

~ ADO/MD simplifies the task of custom application development

~ Extensibility

Provider can add specific interfaces

ODBO industry support

ISVs developing consumers

~ Arcplan, Inc.

~ Andyne Computing Limited ~ AppSource Corporation ~ Arbor

~ AutoDesk

~ AVOCA Systems Limited ~ Brio Technology

~ Business Objects ~ Comshare

~ Cognos

~ Hyperion Software

~ International Software Group ~ IQ Software

~ Panorama Software Systems ~ Sagent Technologies

~ SAS

~ Seagate

~ Simba Technologies ~ X-Tension

(5)

Terminology: BW vs ODBO

BW terms • Characteristics • Key Figures • Characteristic values • InfoObject Attributes • Query ODBO terms • Dimensions • Measures • Members • Dimension Properties • Dataset

ODBO Schema Objects

Meta- and Masterdata

Catalogs Schema Cubes Dimensions (Measures) Hierarchies Levels Members Properties CUBES Schema Rowsets DIMENSIONS MEASURES HIERARCHIES LEVELS MEMBERS PROPERTIES

(6)

Schema Rowset example:

DIMENSIONS

Rowset column name Description/Comment

CATALOG_NAME SCHEMA_NAME CUBE_NAME

Name of catalog, schema and cube CATALOG and SCHEMA not supported in BW

DIMENSION_NAME DIMENSION_UNIQUE_NAME DIMENSION_GUID

DIMENSION_CAPTION

UNIQUE_NAMEs are unambigous. BW provider follows two strategies : qualification and opaque tokens Use Unique Names for MDX Query SCHEMA for UNAMs!!!

DIMENSION_ORDINAL

DIMENSION_TYPE Time, Measure, Unknown, Other

DEFAULT_HIERARCHY

DIMENSION_CARDINALITY Number of members

DESCRIPTION Human readable description

Sample Dataset

Value type Actual Actual data

Country DE Germany

Cost SoldPcs Revenue

Berlin Qtr1 17.793.920,00 DM 3.561.920,000 ST 35.583.920,00 DM Qtr2 12.504.000,00 DM 2.504.000,000 ST 25.004.000,00 DM Qtr3 12.503.000,00 DM 2.503.000,000 ST 25.003.000,00 DM Qtr4 18.083.940,00 DM 3.619.940,000 ST 36.163.940,00 DM Stuttgart Qtr1 12.502.500,00 DM 2.502.500,000 ST 25.002.500,00 DM Qtr2 12.503.500,00 DM 2.503.500,000 ST 25.003.500,00 DM Qtr3 12.502.000,00 DM 2.502.000,000 ST 25.002.000,00 DM Qtr4 18.213.900,00 DM 3.645.900,000 ST 36.423.900,00 DM Columns Axis 1 dimension Slicer Axis 2 dimensions Rows Axis 2 dimensions Cell data

(7)

Structure of a dataset

Cubes

~ have dimensions

Dimensions have members

At the intersection of members are data cells

Datasets

~ have dimensions on axes

Sets on Axes have members

At the intersection of Axes coordinates are data cells

~ Datasets (in BW terminology Queries) have the same structure as Cubes

MDX I

~ MDX stands for MultiDimensional EXpression ~ Expression syntax for querying Datasets

~ Parts of MDX statements

FROM clause -> selects a cube

SELECT clause -> defines the axes

WHERE clause -> defines a slicer

SELECT <axis-specification>, <axis-specification>,… FROM <cube-specification>

(8)

MDX II: Example

SELECT CROSSJOIN({City.Berlin,City.Stuttgart}, {Quarters.MEMBERS}) ON COLUMNS, {COST,SoldPieces,Revenue} ON ROWS FROM CUSTOMER_ANALYSIS WHERE (Country.Germany,ValueType.Actual)

MDX III: Basic Entities and

Functions

~ Basic Entities Numeric Values Strings Sets Members Tuples: Collections of Members of different dimensions (Berlin,Qtr1) ~ Functions

numeric value functions: return numbers

set value expressions: manipulate/generate sets

member value expressions: return member

(9)

MDX IV: Set Value Expressions,

Numeric Value Functions

~ Controlling the order of tuples

ORDER(<set>,<value_expression>,[ASC|DESC|BASC|BDE SC]) ORDER(customer.MEMBERS,Revenue,DESC) ~ Ranking functions TOPCOUNT|BOTTOMCOUNT(<set>,<index>,<num_exp>) TOPCOUNT(customer.MEMBERS,5,Revenue) ~ Filtering FILTER(<set>,<search_condition>) FILTER(customer.MEMBERS,Revenue>1.000.000)

~ Numeric Value Functions: AVG,MAX,MIN,MEDIAN...

AVG(customer.MEMBERS,Revenue)

MDX V: Dimension Properties

~ Members may carry additional information e.g. a material has properties colour, weight, packaging information....

~ ODBO supports dimension properties on a per dimension, per level and per member basis

~ Dimension properties are selected using the DIMENSION PROPERTIES clause

SELECT material.MEMBERS

DIMENSION PROPERTIES material.colour, material.weight ON ROWS

(10)

Fetching cell data

IMDDataset::GetCellData

0 1 2 0 0 1 2 1 3 4 5 2 6 7 8 3 9 10 11 4 12 13 14 5 15 16 17 6 18 19 20 7 21 22 23 Columns Axis 3 coordinates 0..2 Rows Axis 8 coordinates 0..7 24 cells cell ordinals 0..23 Selection of cells ~ by cell ordinal ~ axis coordinates ~ member unique names

What are the properties of a cell?

~ CELL_ORDINAL

~ VALUE - this is a variant since cell data is not only numeric (date/time/string- Key Figures)

~ FORMATTED_VALUE - string representation of the value

~ optional cell properties defined by ODBO -FORMAT_STRING, FORE_COLOR,FONT_SIZE... ~ Optional provider specific cell properties

~ Mechanism for the selection of cell properties is the same as for dimension properties - use CELL

(11)

Fetching Axis “Masterdata”

IMDDataset::GetAxisRowset

Rowset column name Description/Comment

TUPLE_ORDINAL Axis coordinate

MEMBER_UNIQUE_NAME MEMBER_CAPTION LEVEL_UNIQUE_NAME LEVEL_NUMBER DISPLAY_INFO

For each dimension on the axis

MEMBER_NAME MEMBER_TYPE::::

Additional mandatory and user defined member properties

~ Axis Rowsets have at least 5n+1 columns (n is the number of dimensions projected along the axis)

Axis Rowset and Cell Data

Example

Berlin Qtr1 17.793.920,00 DM 3.561.920,000 ST 35.583.920,00 DM Qtr2 12.504.000,00 DM 2.504.000,000 ST 25.004.000,00 DM Qtr3 12.503.000,00 DM 2.503.000,000 ST 25.003.000,00 DM Qtr4 18.083.940,00 DM 3.619.940,000 ST 36.163.940,00 DM Stuttgart Qtr1 12.502.500,00 DM 2.502.500,000 ST 25.002.500,00 DM Qtr2 12.503.500,00 DM 2.503.500,000 ST 25.003.500,00 DM Qtr3 12.502.000,00 DM 2.502.000,000 ST 25.002.000,00 DM Qtr4 18.213.900,00 DM 3.645.900,000 ST 36.423.900,00 DM TUPLE _ORDI NAL [city].[MEMBER_ UNIQUE_NAME] [city].[MEMBER _CAPTION] [city].[LEVEL _UNIQUE_NA ME] [city].[LEVEL _NUMBER] [city].[DI SPLAY_I NFO] [Qua te rs].[ MEMBER_U NIQUE_NA ME] [Qua rters ].[MEMBE R_CAPTI ON]

0 [city].[berlin] Berlin [city].[citylevel] 1 0 [Quarters].[qt Qtr1 1 [city].[berlin] Berlin [city].[citylevel] 1 0 [Quarters].[qt Qtr2

CELL_ORDINAL VALUE FORMATTED_VALUE

0 17793... 3.645.900,00 DM 1 3561930.. 18.213.900,000 ST 2 35583... 35.583.920,00 DM ....

(12)

Dataset Flattening

Berlin Qtr1 17.793.920,00 DM 3.561.920,000 ST 35.583.920,00 DM Qtr2 12.504.000,00 DM 2.504.000,000 ST 25.004.000,00 DM Qtr3 12.503.000,00 DM 2.503.000,000 ST 25.003.000,00 DM Qtr4 18.083.940,00 DM 3.619.940,000 ST 36.163.940,00 DM Stuttgart Qtr1 12.502.500,00 DM 2.502.500,000 ST 25.002.500,00 DM Qtr2 12.503.500,00 DM 2.503.500,000 ST 25.003.500,00 DM Qtr3 12.502.000,00 DM 2.502.000,000 ST 25.002.000,00 DM Qtr4 18.213.900,00 DM 3.645.900,000 ST 36.423.900,00 DM [city].[M EMBER _CAPTION]

[Qua rters].[M EMB ER_CAPTION] [m e asures].[cost] [m e asures].[SoldPc s] [m e asures].[Re venue ] Berlin Q tr1 1 77 93 92 0 3 56 19 20 3 55 83 92 0 Berlin Q tr2 1 25 04 00 0 2 50 40 00 2 50 04 00 0 Berlin Q tr3 1 25 03 00 0 2 50 30 00 2 50 03 00 0 Berlin Q tr4 1 80 83 94 0 3 61 99 40 3 61 63 94 0

Mapping of BW Objects to ODBO

Schema Objects

Characteristics(Keyfgs)

Hierarchies (external) Numbered Levels or Dummy level names

Characteristic Values Display Attributes InfoCubes Not supported Queries BW Properties Catalogs Schema Cubes Dimensions (Measures) Hierarchies Levels Members ODBO

(13)

OLAP dataflow

„INFOCUBE“

Database

Database Server Server

„QUERY_CUBE (BEx: Query)“

BW Application

BW Application Server Server

OLAP Processor operates on ...

Datasets

Presentation

PresentationServer;ODBO client applicationServer;ODBO client application

mdx$ answer ODBO Stored in Star Star Schema Schema

Mapping of Query Objects

Structure for Key Figs.

Member1: Cost Member2: SoldPieces Member1: Revenue Charact.1:Region Charact.2:FiscalYear Charact.3:Customer BW Query Dimension1:Region Dimension2:FiscalYear Dimension3:Customer Measures Dimension QUERY_CUBE structure Measures Cost SoldPieces Revenue

(14)

Creating a QUERY_CUBE

~ Select from an InfoCube characteristics - these will be mapped to dimensions of the ODBO

QUERY_CUBE

~ Create one structure and drag Key-Figures from the InfoCube to this structure - this structure will be mapped to the measures dimension of the ODBO cube, the members of the structure are the members of the measures dimension

~ Enable the query for ODBO by setting in the Query properties dialogue ODBO support checkmark

Benefits of QUERY_CUBES

~ Leverage of existing BW OLAP Processor

~ Consistent definition of derived key figures on the BW server - no calculated members via MDX but on the BW server

~ Smaller entities for reporting purposes - BW

InfoCubes may contain large number of InfoObjects ~ Centralized Authorization concept!!!

(15)

~ Section “Elements” - no object qualification by catalog, schema...; numbered levels (dummy level names)

~ Section “Expressions” - #1,#2,#3; <property>.VALUE, case expressions not supported

~ Section “Set Value Expressions” - #3 (YTD, MTD, WTD..), #4 (nonmeasure dimensions in value expressions)

~ Section “Member Value Expressions” - COUSIN , PARALLEL/OPENING/CLOSINGPERIOD

~ Section “Numeric Value Functions” - RANK, AGGREGATE, COVARIANCE, LINREG*s

~ Section “MDX Statement” - #1 (formulas), #2 (slicer is single tuple), #3 (no joincubes), #4,5 (supported axes 0..2), #6 (create formula), #7

Application of Leveling Rules

Rollout Status

9/98 BW ODBO BW ODBO Pilot Pilot customer customer shipment shipment ( (relrel. 1.2. 1.2 GA) GA) 12/98 2/99 Pilot Pilot certi certi- -fications fications with with Arcplan Arcplan && Business Business Objects Objects General General availability availability with 1.2B with 1.2B

(16)

Where to find more information

~ Final OLE DB for OLAP Programmers Reference at:

www.microsoft.com/data/oledb/olap

~ Microsoft Data Access SDK 2.0

Summary

~ BW provides access to its data via OLE DB for OLAP compliant driver

leverage investment in front end tools via industry

standard interface

extensibility

~ Queries are mapped to QUERY_CUBEs via the ODBO

layer

~ MDX is the command language for querying MD data

powerful syntax for manipulation of sets,

(17)

Demonstrations and Q&A

~ Definition of a QUERY_CUBE

~ Applications :Arcplan inSight; Business Objects