SAP BW Open Data Access Strategy | Business Information Warehouse for SAP (Prima Techs SAP Book Series)

Team-Fly

To provide open access to third-party reporting and analytical applications, SAP has adopted Microsoft's standard for multidimensional data access, OLE DB for OLAP, or ODBO. Though ODBO is a widely accepted standard, when you take a close look at the specifications, it is very specific to Microsoft Plato OLAP server technology. Therefore, you will notice later in this chapter that there are some distinct differences between Microsoft and SAP BW ODBO implementations. The BW ODBO implementation is based on release 1.0 of Microsoft's OLE DB for OLAP Programmer's Reference of February 1998. This document is available at the Microsoft Web site, http://www.microsoft.com/data/.

OLE DB for OLAP Architecture (ODBO)

Unlike Microsoft Open Database Connectivity (ODBC), which was implemented to access universal relational databases, the ODBO standard is designed to handle data from multidimensional data sources. The ODBO standard does not, however, specify how data is physically stored. Data may be stored in a relational database, such as SAP BW, or in a proprietary multidimensional structure, such as Microsoft Plato OLAP Server or Oracle Express. Figure 15-1 shows ODBO architecture and implementation components. ODBO is a set of Microsoft Component Object Model (COM) objects and interfaces that extend OLE DB, Microsoft's universal data access framework, for multidimensional data.

click to expand
Figure 15-1: The ODBO Architecture and its Implementation in SAP BW.

At the application-server level, when an end-user data request is received, the OLAP API identifies the request origination type. When BEX Analyzer originates a request, it is directly handed over to the OLAP processor. However, when a query is issued over ODBO, the ODBO layer at the application server first parses the Multidimensional Expression (MDX) statements to SQL statements that are recognized by the OLAP processor and then hands over the incoming request to the OLAP processor (the next section describes MDX syntax and grammar). Because this is an extra step in query processing, ODBO-based queries tend to be slower than BEX Analyzer queries.

ODBO supports MDX, which is similar to SQL but incorporates multidimensional grammar, as shown in Figure 15-2.

click to expand
Figure 15-2: A Multidimensional Statement Syntax, Actual Query, and Results.

Figure 15-2 shows the syntax of a simple MDX statement. To describe this syntax, the example shows the results of the query. Notice that ODBO lists data in crosstab format, which is ideal for OLAP operations. Streams of several MDX statements are used for complex data analysis. For details on ODBO and MDX, visit the Microsoft Web site at http://www.microsoft.com/data/oledb/olap and http://msdn.microsoft.com/library/techart/intromdx.htm.

Note

Though it is commonly known that MDX means multidimensional expression, in reality it is called multidimensional extensions. It is called an extension because MDX extends relational SOL statements to a multidimensional view of data using statements similar to SQL statements.

As stated earlier in this chapter, ODBO very much reflects the Microsoft OLAP server architecture. Therefore, differences between ODBO object definitions and SAP BW objects are expected. Figure 15-3 shows ODBO equivalent objects in SAP BW.

click to expand
Figure 15-3: SAP BW Objects versus ODBO Schema Objects.

According to ODBO specifications, a schema is composed of a set of cubes. A collection of schemas constitutes a catalog. A cube is defined as a set of dimensions, and each dimension has properties. Dimensions can be rolled up and down along associated hierarchies. The hierarchies have levels that have one or more members. This simply describes ODBO at a higher level. For additional details on ODBO specifications, visit the Microsoft Web site at http://www.microsoft.com/data/doc.htm.

In SAP BW, a query object is equivalent to a cube in ODBO. This is somewhat inconsistent because a query usually is a subset of an InfoCube. When end users access SAP BW from third-party tools through ODBO, they see only query metadata and not the actual InfoCube. This limits the design of the analytical application against full SAP BW cubes. You can define all dimensions and key figures in a query against an InfoCube. Then third-party tools access this query, using ODBO to develop applications. This choice will degrade SAP BW performance because all ODBO users will have their own copy of the entire InfoCube at the application server. This is not acceptable.

Moreover, ODBO implementation in SAP BW does not support complex structures and variables defined in a BEX query. You should also limit data aggregation in the BEX query because end users might want to slice and dice data differently and need raw instead of summarized data. For this reason, you must design special BEX queries that generate raw, flat data for the tool. By doing so, you will have a large amount of data flowing to the end user; that could cause networking problems. Therefore, you need to model ODBO queries very carefully, knowing the limitations of ODBO and how performance impacts query execution and data delivery.

Not all queries defined in BEX Analyzer are visible to ODBO. To make a query visible to ODBO, you must check the ODBO option in the Query properties dialog box, as shown in Figure 15-4.

click to expand
Figure 15-4: Enabling a BEX Analyzer Query to be Visible to the ODBO Consumer.

Team-Fly