Larry Sackett

SAP Business Objects Roadmap and MDX

The SAP Business Objects roadmap is now quite clear. The suite of Business Objects front-end BI tools gradually will replace most of the tools in the current Business Explorer (BEx) BI suite. Crystal Reports is the flagship for highly formatted, interactive, and print-optimized reporting. Web Intelligence fills a gap for self-service reporting and analytics. Xcelsius provides dynamic dashboards and interactive visualizations.  A new OLAP tool, Pioneer, will combine the features from Business Objects Voyager, The BEx Excel Analyzer, and the BEx Web Analyzer. Polestar has been renamed Explorer. Explorer provides search engine key word searches of SAP and non-SAP data sources, including direct access to data in the BW Accelerator.

These tools interface with NetWeaver BW through two primary interfaces: The BI Consumer Services, used by the BEx suite, and the MDX-based OLAP BAPI, which is the primary interface for the OLAP universes in the Business Objects Enterprise (BOE) XI system. Crystal Reports includes its own OLAP BAPI provider. Web Intelligence accesses NetWeaver BW InfoCubes or BEx Query Cubes through the OLAP universes and the OLAP BAPI. Xcelsius connects to NetWeaver BW by publishing BOE universes as a Web service, called Query as a Web Service (QaaWS). Pioneer intially will connect via the BI Consumer Services API.

BOE provides a semantic layer called universes on top of SAP and non-SAP data sources. Universes can be designed for relational (SQL) data sources and OLAP (MDX) data sources. The ultimate goal is to have all the Business Objects front-end BI tools interface to NetWeaver BW through the BOE OLAP universes. SAP Business Objects has made significant improvements in the connectivity between the OLAP Data Access (ODA) component in BOE and the NetWeaver BW OLAP BAPI, with query performance gains averaging 30 percent. Queries generated by the OLAP universes are executed in MDX. SAP Business Objects has also enhanced MDX in NetWeaver BW to the point where it's the equivalent of MDX in Microsoft SQL Server 2005.  It's clear from the roadmap that MDX is a core element of the SAP Business Objects roadmap.

OLAP universes can include MDX calculated members/measures and named sets. Up to now, MDX expressions have had to be handwritten and enclosed in XML <EXPRESSION></EXPRESSION> tags. There has been no MDX parser so it's been impossible to check the syntax of an MDX expression while desiging the OLAP universe. That will change in a soon-to-be released BOE service pack that will include a robust MDX editor that will provide drag-and-drop access to MDX functions available within NetWeaver BW. The service pack also will include an MDX parser. These are big improvements for the design of OLAP universes.

The prerequisites for enhanced SQL and MDX interoperability between NetWeaver BW and BOE are:

• BW 7.01 SP3

• BOE XI Fix Pack 1.2 or later

• SAP Integration Toolkit XI 3.1

• BOE Data Federator XI 3.1

NetWeaver BI with MS Analysis Services

I just finished the 10th chapter in my book about the use of MDX for reporting and analytics in NetWeaver BI.  The chapter discusses the data warehouse side of NetWeaver, with emphasis on the ETL processes from R/3 extractors, DataSources and PSA tables, to transforming the data, and finally to loading the data into InfoProviders such as DataStore objects and InfoCubes using the data transfer process.  I am struck by how open NetWeaver BI is, with several different APIs for importing and exporting data.  In addition to any SAP source transaction system, NetWeaver can extract data from flat file sources, other relational database systems using DB Connect, XML sources using Web Services, relational and OLAP data sources using UD Connect, and a Staging BAPI designed for third-part ETL tools. The BI system can upload data from one NetWeaver BI to another using the Data Mart interface, and to Third-Party systems using Open Hub Destination.

Microsoft and SAP have a long history of collaboration (and competition!), including Duet for MS Office, the XML/A connector for MS Reporting Services, and a relatively new Microsoft Connector for SAP BI. The connector enables companies to use MS Integration Services to develop ETL processes to upload data to NetWeaver BI, as well as use the Third-Party Open Hub Destination to download data to third-party systems such as MS SQL Server and MS Analysis Services, among other systems. 

Like NetWeaver BI, Analysis Services uses MDX for queries and calculations.  Unlike NetWeaver BI, however, Analysis Services lets you write MDX expressions for calculated members and named sets, and store those expressions on the server for reuse in any query.  For example, you can create a utility time dimension in Analysis Services and then write a host of server-based time calculations for this dimension using MDX functions such as ParallelPeriods, PeriodsToDate, LastPeriods, OpeningPeriod, ClosingPeriod, and .PrevMember.  These functions can be generalized to work in different time hierarchies, fiscal and calendar time for example, without any user intervention. 

MS Integration Services is extraordinarily powerful and flexible, including the use of fuzzy logic to merge customer records from multiple source systems. You can use T-SQL, C#, or VB.Net for complex transformations.  Integration Services has a really nice graphical user interface for designing data flow and transformation tasks, as well as error routines.  But the best part of both Integration Services and Analysis Services is that they are free if you already have a SQL Server license. 

 A very talented developer for Microsoft, Chunhui Zhu, wrote an excellent white paper, Using SQL Server 2008 Integration Services with SAP BI 7.0.  I highly recommend it.

SAP has gone to great lengths to ensure the NetWeaver BI is completely open to third-party data sources and down-stream applications.  The Staging BAPI and Open Hub Destination are two examples of this.

I've worked with Integration Services and Analysis Services for years.  They are great products, as is NetWeaver BI.  For SAP NetWeaver BI clients that want low-cost, but powerful alternatives to the BEx suite or Business Objects, the MS Connector for SAP BI opens up another opportunity.

Why BI Projects Fail: Eight Ways to Avoid Disaster

Over the past decade I've been directly responsible for the design and implementation of more than 20 full-scale data warehouse/BI applications.  Some have been more successful than others.  A few achieved impressive results, but one failed utterly and completely.  More about that later.  I recently worked on a reporting and analytics application for an SAP NetWeaver BI customer.  Senior management had regarded the BI project as a failure, yet I and my colleagues found that the system was quite well designed and implemented.  However, staffers weren't able to get the reports they wanted without huge amounts of manual effort, such as dumping data from BEx queries into Excel.  The CEO of another NetWeaver BI customer told me his company had abandoned the system and was implenting Cyrstal Reports directly from the R/3 systems. 

There are many reasons why a BI project fails, but surpisingly, incompetence is rarely a factor.  Well-meaning IT and business executives can fail just as easily as someone who doesn't know what she or he is doing.  Here are eight ways you can avoid failure and ensure that your project achieves its objectives:

Plan with the end in mind.  Many BI projects are started because senior managers recognize they have a problem with reporting and analytics.  However, projects can go off-course because specific, quantifiable goals are not set at the very beginning.  It's not sufficient to set generic goals such as "increase revenue," or "reduce expenses."  You have to determine up front how information will be used in a series of action steps to accomplish specific goals that ultimately result in accomplishing big-picture objectives.  My advice is to use "Balanced Scorecard" techniques to develop the appropriate strategies, tactics, and metrics right from the beginning.

BI Solutions for SAP

I recently came across an excellent TDWI (The Data Warehouse Institute) best practices report about the diversity of reporting and analytics options for NetWeaver BI (BI).  The report, written by Philip Russom, senior manager of TDWI Research, is based on a survey of 278 SAP customers in 2007.  While this is before the Business Objects acquisition by SAP, the report's conclusions are well worth reading.

Several items struck my interest:

• The majority of responders use on the average two reporting and analytics tools and most have at least one other data warehouse systems (beyond BI);

• Many customers use BI as a data source for an enterprise data warehouse (EDW) system from other vendors such as Cognos, Microsoft, and, of course, Business Objects;

• If your company acquires an SAP ERP (formerly R/3) system, you are more than likely to also use BI because:

1. BI leverages the hundreds of data extractors for ERP modules

2. SAP's pre-built Business Content is a proven way to jump-start a business intelligence project

3. BI is bundled with ERP solutions

• Some 68% of reponders said they integrate data from non-SAP sytems into BI;

• While static and parameterized reports are the "bread and butter if any BI solution," spreadsheet applications and ad hoc queries rank nearly as high in the survey;

• SAP users turn to 3rd party tools for enterprise reporting, predictive analytics, and advanced visualization;

• And, many SAP customers have yet to deploy a meaningful BI solutions for their SAP ERP systems.

Based on the results of the survey, it seems to me that there is still a viable and profitable market for 3rd party reporting and analytics tools, even with the acquisition of Business Objects.  I was disappointed that none of the major 3rd party BI tools vendors, other than Business Objects, exhibited at the most recent SAP Reporting and Analytics Conference in Las Vegas.  SAP customers deserve to have more options than just the BEx suite or Business Objects.

You can access the full report at:  http://pp.techtargetpromo.com/c.asp?740326&25665280a334aebc&1

Excel 2007 Connection to NetWeaver BI

SAP was one of the first to adopt the MDX-based OLE DB for OLAP (ODBO) standard in 1998.  SAP wrote its own MDX processor, but turned to Simba Technologies to develop the ODBO interface.  Simba has continued to work with SAP ever since.  Earlier this year Simba released an updated ODBO interface that provides a connection for Microsoft's Excel 2007.  Excel exposes data from InfoCubes, BEx query cubes, and MultiProviders as pivot tables, with the ability to drag and drop dimensions (characteristics) and measures (key figures) onto the pivot table.  Users can also slice dimensions to filter the data.  What's great about the Simba interface and Excel 2007 is that hierarchies (virtual time hierarchies and external hierarchies) are maintained, letting users drill down or up dimension hierarchies by clicking on plus or minus buttons. 

Many BI users create BEx queries and then export data to Excel, which requires them to manually create the pivot tables.  With the Simba interface and Excel 2007, that process is fully automated.  Simba wrote a well-documented and well-illustrated guide to using the ODBO interface, which you can get here:  http://www.simba.com/docs/Connecting-to-SAP-BW-with-Excel-PivotTables-and-ODBO.pdf . 

I did a presentation of the Excel 2007 connection at the recent SAP Reporting and Analytics conferences in Marseille and Las Vegas.  You can view it here:  

You will need to install the most recent ODBO updates, which are referenced in SAP note 1134226.  The note includes information about how to download and install the files.

BEx or Business Objects?

The SAP/Business Objects roadmap leaves the understandable (from SAP's perspective) impression that NetWeaver BI customers can use one or the other, or both, for reporting and analytics.  However, this ignores the real-world option of using a third-party BI suite such as from Cognos, Microsoft Excel/Reporting Services, MicroStrategy, or Panorama NovaView.  The BEx suite has a reputation, not always deserved, of being overly complex.  Business Objects via Crystal Reports has an excellent reputation for tabular, formatted, reports whether from R/3 (now ERP) systems or NetWeaver BI (formerly BW). 

The roadmap states that Crystal Reports is the suggested enterprise reporting solution and offers a future product, Pioneer, as the OLAP tool.  Both solutions, however, require the customer to license the Business Objects Enterprise (BOE) platform, which in turn requires extracting data from NetWeaver BI.  The BOE platform will remain independent of NetWeaver BI, according to the roadmap.

SAP clients should be aware of alternatives that don't require any data extraction and work directly with existing MultiProviders and InfoCubes using the MDX query and calculation language, a subject of many posts on this blog.  Any third-party BI tool that adheres to the ODBO or XML/A specification can connect directly to NetWeaver BI via those two interfaces.  Also, some third-party tools such as those from Cognos connect to a third MDX-based API, the OLAP BAPI.  By design, the NetWeaver BI development team intends to maintain the three open MDX-based interfaces because they recognize that some SAP clients already have substantial investments in third-party tools or may be looking for less complicated or expensive BI solutions than the BEx or Business Objects suites. 

Microsoft and SAP maintain a close collaboration with products such as Duet for the Microsoft Office suite and Microsoft Reporting Services (packaged with SQL Server 2005 and 2008).  Microsoft Excel 2007 can connect directly to NetWeaver BI via an MDX interfaced (ODBO) developed by Simba Technologies.  Many SAP customers use BEx to create queries and download the data into Excel for use with Excel pivot tables.  Excel 2007 can connect to a BEx Query cube, a MultiProvider, or an InfoCube with no need to export data from a BEx query to Excel.  Excel 2007 automatically generates pivot tables and enables the user to quickly swap or nest dimensions, drill down/up, and to slice by dimension members.  It also recognizes any virtual time or external hierarchies available in these data sources. 

Panorama's NovaView server connects to NetWeaver BI via the Simba ODBO interface.  Panorama rewrote its MDX query engine to work optimally with the NetWeaver BI MDX processor, the only third-party BI partner to go to that length according to SAP.  NovaView's entire user interface is built around the use of MDX and hides much of the complexity of MDX from casual users, while making advanced MDX functionality available to report authors and power users.

Ad hoc queries in the Cognos 8 BI suite connect directly to NetWeaver BI through the MDX-based OLAP BAPI.  Cognos was one of the first third-party vendors for NetWeaver BI to provide direct access to the MDX query and calculation language.  The Cognos 8 BI suite can integrate both SAP and non-SAP data. 

The SAP/Business Objects roadmap focuses on the BOE platform as the intermediate database for Crystal Reports, the Xcelcius dashboard, and the existing OLAP tool Voyager (which will be replaced by Pioneer).  If you are going to extract data from NetWeaver BI anyway, then another viable low-cost solution is to use Microsoft's OLAP server,  Analysis Services, also packaged with SQL Server 2005 and 2008.  Analysis Services can extract data from NetWeaver BI via BI's open-hub services.  SQL Server 2005 and 2008 also come pre-packaged with Microsoft Integration Services, a highly graphical ETL tool.  If you are running your SAP systems on Microsoft SQL Server, then you already own these BI tools, making them in effect free.   

SAP clients that are new to NetWeaver BI, or prospective BI customers, owe it to themselves to explore these and other third-party alternatives for reporting and analytics.  I like solutions that don't require extracting data from BI, that reuse BI structures such as aggregations and hierarchies, and take full advantage of MDX's powerful query and calculation capabilities.  BEx and Business Objects aren't the only SAP NetWeaver BI game in town.

Simple MDX Set Functions

MDX functions can be organized by the objects they return:

• Sets
• Members
• Tuples
• Conditions
• Numbers
• Logical
• Strings
• Time Series
• Statistics

In the next several posts I'll demonstrate a variety of set functions that can be used to specify axis definitions and named sets in an MDX query. Sets are a core component of MDX. Business reporting and analytics usually involves looking at groups (sets) of customers, products, geographic regions, or time periods. Set functions enable us to find the top or bottom X customers, all the stores in a particular region, products with net sales greater than or equal to a threshold value, or results for the past 12 months from a moving current date. There are a variety of ways to define sets. Let's start with the simplest, using curly braces { } and the comma separator.

In the SAP demo DalSegno Reporting Cube there are 11 products, ranging from Automatic umbrella to Multifunctional pen. Suppose you are a product manager for the DalSegno Company with reponsibility for just Automatic Umbrella, Candy tins, and CoffeMug. You can create a set for just these three products using this construction:

{[Automatic Umbrella], [Candy tin], [CoffeMug]}

Using the MDX Test Editor, we can find 2005 Net Sales for these three products (with their technical names):

SELECT
{ [Measures].[0D_NETSALES] } ON COLUMNS,
{ [0D_PH2].[PDS01], [0D_PH2].[PDS05], [0D_PH2].[PDS06] } ON ROWS
FROM [$0D_DX_M01]
WHERE [0CALYEAR].[2005]

Here's the result set:

 

You can also construct a simple set using the colon (range) operator. Let's say that we want to review sales from July 2004 throught Dec 2004. You would use this construction:

{ [July 2004] : [Dec 2004] }

This query returns the result set shown below:

{ [Measures].[0D_NETSALES] } ON COLUMNS,
{ [0CALMONTH].[200407] : [0CALMONTH].[200412] }ON ROWS
FROM [$0D_DX_M01]

 

Dimensions, hierarchies, and levels all contain members. You can retrieve these members using the .Members function with this syntax:

<dimension>.members

<hierarchy>.members

<level>.members

This query will return the members of the Product (technical name [0D_PH2]) dimension. The result set follows.

SELECT
{ [0D_NETSALES] } ON COLUMNS,
{ [0D_PH2].members } ON ROWS
FROM [$0D_DX_M01]

 

We'll explore the MDX Crossjoin function, as well as the NON EMPTY keyword in the next post.

Solve Order in MDX

 

It is not an uncommon situation to have overlapping or conflicting calculations when you have calculations on both the columns and rows axes. For example, we want to know the growth in [Avg Sales Price] from 2004 to 2005. We can formulate a query that includes two calculated members, one for [Avg Sales Price] in the measures dimension, and one for [YOY Growth] in the Calendar Year dimension, as in this query:

WITH

MEMBER [Measures].[Avg Sales Price] AS '

[Measures].[0D_NETSALES]/[Measures].[0D_INV_QTY]'

MEMBER [0CALYEAR].[YOY Growth] AS '

[0CALYEAR].[2005]-[0CALYEAR].[2004]'

SELECT

{[Measures].[0D_NETSALES],[Measures].[0D_INV_QTY],[Measures].[Avg Sales Price]} ON COLUMNS,

{ [0CALYEAR].[2004] , [0CALYEAR].[2005] , [0CALYEAR].[YOY Growth] }ON ROWS

FROM [$0D_DX_M01]

As you can see in the above figure, the calculations for [YOY Growth] and [Avg Sales Price] overlap in the bottom right-hand cell. In our query, the calculation for [Avg Sales Price] takes precedence and does not give us year-over-year growth, but rather the result of dividing [Net Sales] by [Billled Quantity] for the [YOY Growth] member. That's not the result we want.

To get the desired answer, we can assign a SOLVE_ORDER to each calculated member so that the [YOY Growth] calculation takes precedence over the [Avg Sales Price] calculation. We do this in the following query:

WITH

MEMBER [Measures].[Avg Sales Price] AS '

[Measures].[0D_NETSALES]/[Measures].[0D_INV_QTY]' SOLVE_ORDER = 0

MEMBER [0CALYEAR].[YOY Growth] AS '

[0CALYEAR].[2005]-[0CALYEAR].[2004]' SOLVE_ORDER = 1

SELECT

{[Measures].[0D_NETSALES],[Measures].[0D_INV_QTY],[Measures].[Avg Sales Price]} ON COLUMNS,

{ [0CALYEAR].[2004] , [0CALYEAR].[2005] , [0CALYEAR].[YOY Growth] }ON ROWS

FROM [$0D_DX_M01]

This gives us the correct result, as  shown in the figure below.

 

The expression with the higher solve order takes precedence over the one with the lower. Note that the SOLVE_ORDER clause is applied after the calculation and is enclosed within single quotes. If you've created a query with multiple calculated members and you are not getting the result you expect, make sure that you've correctly specified the precedence of each calcuation. The default precedence is 0.

MDX for NetWeaver BI Variables

Long a stapel of BEx queries, variables are used as a way to set parameters for a query, such as specifying a characteristic value, a hierarchy or hierarchy node, or formula variables (numbers). SAP added an extension to MDX to support these variables. Using a characteristic with a variable is similar to slicing a dimenion in the WHERE clause of an MDX query.  The SAP-enhanced MDX syntax adds the SAP VARIABLES keyword after the FROM clause or after the WHERE clause is there is one. Here's the syntax for the SAP VARIABLES clause:

SAP VARIABLES <sap_variables> INCLUDING|EXCLUDING <unique_member_name>

Here's an example of a variable called VAR_TEST defined in a BEx query based on the SAP Demo DalSegno reporting cube (you have to set the enable-for-OLAP flag in the BEx query properties to use it with MDX):

 

1. The numbers in the screenshot correspond to the following list:

2. The name of the SAP VARIABLES, Test, appears with the members of the dimension (characteristic) on which the variable is based. In the example, we used [0D_CHANNEL], which includes Internet, Fax, Other, Phone, and EDI.

3. The MDX statement with the SAP VARIABLES clause.

The result set shows the Net Sales for the Internet channel.

Here's the complete MDX statement:

SELECT

{[Measures].[0D_NETSALES] }ON COLUMNS,

{ [0D_PH2].[LEVEL01] .members} ON ROWS

FROM [0D_DX_M01/LBS_VAR]

SAP VARIABLES [VAR_TEST] INCLUDING [0D_CHANNEL].[0]

Calculated Members and Named Sets

Calculated Members are similar in concept to calculated key figures in a BEx query. They can be defined for any dimension in a cube, although they usually are defined for the Measures dimension. For example, you calculate [Avg Sales Price] as:

[Net Sales]/[Billed Quantity]

You can define this formula in an MDX query using the WITH MEMBER keywords.  The syntax is:

WITH MEMBER [Measures].[Avg Sales Price] AS

'[Measures].[0D_NETSALES]/[Measures].[0D_INV_QTY]'

The WITH MEMBER keywords specify the dimension to which you want the calculated member to belong. You only use the WITH keyword once in query; subsequent calcualted members only use the MEMBER keyword. In this case we are adding a new calculated member, [Avg Sales Price], to the Measures dimension. The name of the new member is always qualified by the dimension name. The formula itself is defined after the AS keyword, enclosed in single quotation marks.

Once the new member is defined, it can be used in the SELECT statement for an axis definition. Here's the full MDX statement, followed by the result set:

WITH MEMBER [Measures].[Avg Sales Price] AS '

[Measures].[0D_NETSALES]/[Measures].[0D_INV_QTY]'

SELECT

{[Measures].[0D_NETSALES],[Measures].[Avg Sales Price] }ON COLUMNS,

{[0D_PH2].[LEVEL01].members} ON ROWS

FROM [$0D_DX_M01]

WHERE ([0CALYEAR].[2005])

 

Named sets in MDX are also very useful. They can make MDX queries more understandable and improve query performance. For example, what if we only want to see the top five products by Net Sales for 2005? We can define a named set using the TopCount function to accomplish this. TopCount uses this syntax:

TopCount(<set>, <index>,<numeric expression>)

The TopCount function will iterate (loop) over each member in the set definition and return the top <index> members, based on the numeric expression. Once you've given a set a name, such as [Top 5 Products], you can use the name in an axis defintion or a calculated member. You define the named using the SET keyword and the name of the set, followed by the set definition. Here's an MDX statement incorporating both a calculated member and named set:

WITH MEMBER [Measures].[Avg Sales Price] AS '

[Measures].[0D_NETSALES]/[Measures].[0D_INV_QTY]'

SET [Top 5 Products] AS '

TopCount([0D_PH2].[LEVEL01].members,5,[Measures].[0D_NETSALES])'

SELECT

{[Measures].[0D_NETSALES],[Measures].[Avg Sales Price] }ON COLUMNS,

{[Top 5 Products]} ON ROWS

FROM [$0D_DX_M01]

WHERE ([0CALYEAR].[2005])

 

You only specify the WITH keyword once in an MDX statement. Calculated members must incorporate the dimension name in which they are created using the MEMBERS keyword. Named sets do not explicitly belong to a dimension and are specified with the SET keyword without using a dimension name.