Using SQL Server 2005 Reporting Services with SAP NetWeaver Business Intelligence

 

SQL Server Technical Article

Writers: Richard Mao, Simba Technologies

Troubleshooting Section: Hermann Daeubler

Technical Reviewer: Brian Welcker

Project Editor: Jeannine Takaki

April 2006

Revised May 2007

Applies to:
   Microsoft SQL Server 2005 SP1 and later

Summary: This paper discusses the integration of SAP NetWeaver Business Intelligence (BI) with Microsoft SQL Server 2005 Reporting Services Service Pack 1 and later. The paper provides an overview of using the data provider and query designer to build high-quality reports on SAP NetWeaver BI sources. For Reporting Services users who are new to working with SAP NetWeaver BI, this paper will help you get up and running quickly. For users who are already familiar with SAP NetWeaver, the paper will show how some of that system's notable features can be leveraged in Reporting Services reports. It also presents some tips and tricks that can help you make the most of the integration between the two products. (36 printed pages)

Contents

Introduction
Prerequisites
Installation Overview
SAP NetWeaver BI Terms and Concepts
Tips and Tricks
Technical Details
Troubleshooting
Additional Resources
Acknowledgements

Introduction

Through a rich extensibility model, the report-authoring and report-deployment features of Microsoft SQL Server 2005 Reporting Services can integrate with any number of business intelligence data sources. To answer the strong need for a rich reporting tool for SAP NetWeaver Business Intelligence (SAP BW) and to bridge the gap between these two powerful business intelligence platforms, Microsoft has developed a new .NET Framework data provider and query designer for SAP NetWeaver Business Intelligence. SAP BI users can now take advantage of the flexible, yet easy-to-use reporting capabilities of Reporting Services without migrating their data to another platform. Report authoring is performed in the familiar and easy-to-use Business Intelligence Development Studio, using a custom-built query designer, and deployment is a simple one-step process that targets the Web as the report-deployment platform.

Prerequisites

In order to use the provider, the following components must be installed:

  • Microsoft SQL Server 2005 Reporting Services Service Pack 1 or later
  • Microsoft .NET Framework 2.0

The provider has been developed for and tested against SAP BW 3.5. However, the provider should be compatible also with BW 3.1 and BW 3.0B servers that have been patched to a sufficient service-pack level, described here:

  • Support Package 30 for SAP BW 3.0B
  • Support Package 24 for SAP BW 3.1
  • Support Package 16 for SAP BW 3.5
  • Support Package 10 for SAP NetWeaver 2004s (BW 7.0)

The Troubleshooting section describes some of the known issues when you use the provider against a BW server that does not have a sufficient service-pack level.

Installation Overview

The assemblies required to use this provider are installed together with Service Pack 1 (SP1) in two separate locations: one location for the design environment (Business Intelligence Development Studio), and another for the runtime environment (Report Server). Set up for SP1 copies the files to the correct locations for each installed environment. Set up will install to both locations if both the runtime and design environments are on the same computer. If only one of these environments is installed when you set up SP1, but subsequently install the other environment, you must rerun SP1 Setup to install the required assemblies for the newly added environment.

After the provider is installed, you must register the provider with each environment in which it will be used. For the design environment, you must modify the RSReportDesigner.config file, located in <drive>:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies. For the runtime environment, you must modify the RSReportServer.config file, located in <drive>:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer. For configuration details, see SQL Server 2005 Service Pack 1 Books Online.

SAP NetWeaver BI Terms and Concepts

SAP BW multidimensional database terminology and concepts are similar but not identical to SQL Server Analysis Services (SSAS) terminology. The subtle differences warrant some clarification.

Metadata Objects

Some SAP BW terms for MDX metadata objects are the same as SSAS terminology. Members are members, levels are levels, and hierarchies are hierarchies. There are just enough differences, however, to confuse the unwary. For example, in the SAP BW universe, dimensions are called characteristics. Characteristics may belong to a logical grouping, which on the SAP BW system is called a dimension, but this grouping is not exposed through the OLAP view and is not manipulated through MDX. Therefore, to MDX users, SAP BW characteristics and MDX dimensions are equivalent. Another notable difference in nomenclature relates to cube measures. In the SAP BW world, measures are often referred to as key figures. Although you can generally consider them equivalent, the SAP MDX syntax parser refers to them as measures. This paper uses SAP BW terminology as much as possible.

QueryCubes and InfoProviders

SAP BW includes Business Explorer Analyzer (BEx Analyzer) as a reporting tool and Business Explorer Query Designer as a report-authoring tool. SAP BW data objects that can be reported against are called InfoProviders. You use Business Explorer Query Designer to build a query based on an InfoProvider before you can view the information in BEx Analyzer. The query represents some subset of the InfoProvider that it is built upon, and retains its multidimensional structure. A query is frequently referred to also as a QueryCube.

The Microsoft .NET Data Provider 1.0 for SAP NetWeaver BI communicates with SAP BW by using XML for Analysis (XML/A), which makes some InfoProviders available directly. XML/A provides direct access to QueryCubes, as well as InfoCubes and MultiProviders. InfoCubes are the native multidimensional data structure in SAP BW. MultiProviders are InfoProviders that consolidate data from multiple InfoProviders. ODS Objects are not accessible directly from XML/A; to use an ODS Object, you must create a QueryCube on the ODS Object. The QueryCube must be configured also for access from XML/A.

InfoProviders that can be accessed directly always have a cube name that starts with the dollar-sign ($) character—for example, $0D_DECU. QueryCube names always consist of the InfoProvider upon which it was built, followed by a forward slash (/) and the query name. For example, a QUERYCUBE1 query built on the $0D_DECU InfoCube would be named [0D_DECU/QUERYCUBE1].

In the SAP BW system, the OLAP concept of a catalog corresponds to the parent InfoProvider for QueryCubes. That is, a QueryCube always belongs to a catalog named after the parent InfoProvider. In the previous example, the [0D_DECU/QUERYCUBE1] QueryCube would be found in a catalog named 0D_DECU. Raw InfoProviders that are accessed directly and that have no parent InfoProvider are grouped into a pseudo-catalog named $INFOCUBES. This hierarchy is reflected in the Cube Selection dialog box of the query designer, as shown in Figure 1. The dialog box displays the friendly names of the cubes and catalogs; the technical names are displayed in tooltips and can be viewed by pausing your mouse on the friendly name.

Click here for larger image

Figure 1. Cube Selection dialog box (Click on the picture for a larger image)

Variables

SAP variables are a way of parameterizing a QueryCube that is similar to the parameter feature in Analysis Services 2005. Both allow the parameterization of MDX query results outside the standard MDX syntax. Analysis Services parameters operate by parameterizing the MDX queries, whereas SAP variables parameterize the QueryCube itself. This results in some key differences:

  • SAP variables are defined at the data source in the QueryCube itself. You can define variables by using BEx Query Designer. Analysis Services 2005 does not require any server-side configuration to enable parameters.
  • Because SAP variables can exist only in QueryCubes, only queries on QueryCubes can be parameterized. Queries on InfoProviders cannot be parameterized. In contrast, Analysis Services parameters are added to the MDX queries themselves, using the "at" sign (@) reserved for variable names; therefore, there are no limitations on the cubes for which parameterized queries can be created.
  • SAP variable values are specified in a proprietary MDX clause appended to the original query. Analysis Services parameter values are passed separately from the MDX query, and are evaluated at the server.

Microsoft .NET Data Provider 1.0 for SAP NetWeaver BI uses variables by mapping them to query parameters. The provider takes the parameter value specified by the user and modifies the query to assign the value to the corresponding variable.

When you open a QueryCube for which a variable has been defined, the Variables dialog box is enabled. This dialog box lists all the variables that have been defined, and lets you assign a default value to each variable. When you assign a value to a variable, the provider automatically generates a corresponding report parameter. However, unlike the parameter feature for Analysis Services, a list of available values is not generated for the report parameter. To define a list of values manually, follow the instructions in the Populating Report Parameter Values section, later in this paper.

Several variable types are supported. The Variables dialog box provides a different selection control for each variable type.

Table 1. Variables dialog box

Variable type Description Icon Selection control
Hierarchy variables Hierarchy variables accept hierarchies as valid values. Hierarchy variable values can affect the calculation of Customer Exit Variables that use them. Bb508810.tripodwhitepapericon1(en-US,SQL.90).gif Hierarchy list box:

Bb508810.tripodwhitepapercontrol1(en-US,SQL.90).gif

Formula variables Formula variables are used to affect calculated key figures. Formula variables accept numeric values that are used in the calculations. Bb508810.tripodwhitepapericon2(en-US,SQL.90).gif Text box:

Bb508810.tripodwhitepapercontrol2(en-US,SQL.90).gif

Member variables (single value) Member variables can be assigned values from the set of members of a dimension. If the parent hierarchy of the member variable is the default hierarchy, the variable is called a Characteristic variable. If the parent hierarchy of the variable is one of the alternate hierarchies, it is called a Hierarchy Node variable. Member variables can accept as valid values the members from their parent hierarchy. For Hierarchy Node variables, this includes all the non-leaf nodes.

When you use a Member variable, it has the same effect as specifying a member for the slicer axis of your MDX query.

Bb508810.tripodwhitepapericon3(en-US,SQL.90).gif Single-select tree view:

Bb508810.tripodwhitepapercontrol3(en-US,SQL.90).gif

Member variables (multiple value): Multiple-value member variables are the same as Member variables, but they can accept one or more values. Bb508810.tripodwhitepapericon4(en-US,SQL.90).gif Multiselect tree view:

Bb508810.tripodwhitepapercontrol4(en-US,SQL.90).gif

Member variables (interval value): Interval-value member variables are the same as member variables, but they can accept a range of values. If the second value is less than the first value, the selected range is empty. Bb508810.tripodwhitepapericon5(en-US,SQL.90).gif Range tree view:

Bb508810.tripodwhitepapercontrol5(en-US,SQL.90).gif

Note   The Variables dialog box tries to list all possible values for a particular variable. If a variable is built upon a very large hierarchy, retrieving the list of values could take a very long time. When the number of values is too large, the dialog box will not populate the list; instead, it opens a text box for you to enter a variable value manually.

Tips and Tricks

This section provides some techniques for working with features of the Microsoft .NET Data Provider 1.0 for SAP NetWeaver Business Intelligence.

Testing the SAP BW XML/A Provider with Internet Explorer

A quick way to verify that the SAP BW XML/A Provider has been configured correctly and that you have access to SAP data is to use Microsoft Internet Explorer to open the XML/A provider's URL. If you connect to the provider successfully, you should see the Web service's XML description, which is similar to Figure 2.

Click here for larger image

Figure 2. Testing the SAP BW XML/A provider by using Internet Explorer (Click on the picture for a larger image)

Enabling a QueryCube for XML/A Access

For a QueryCube to be visible through the SAP BW XML/A interface, the QueryCube must be configured to enable external access (outside BW).

To enable remote access to the QueryCube

  1. Open the BEx Query Designer.
  2. In the Query Properties dialog box, click the Extended tab.
  3. Select the Allow External Access to this Query check box, and then click OK.

Bb508810.tripodwhitepaper03(en-US,SQL.90).gif

Figure 3. Configuring a QueryCube for external access

Specifying Additional SAP Logon Parameters (Client and Language)

SAP BW can accept a client number and a language as logon parameters. To specify these parameters for your data source, you can embed them directly in your connection URL, as shown in the following example:

Data Source=http://sap8:8000/sap/bw/xml/soap/xmla?sap-client=
100&sap-language=EN

If you do not specify the client number or language, the server uses the defaults that were configured on the server. The sap-language parameter can accept a one-character SAP-language specification or a two-character ISO 639–language specification.

Note  SAP BW also allows credentials to be submitted by using the parameters sap-user and sap-password in the URL. However, you should not configure your datasets to pass credentials to the server by this method, because the string will be stored unencrypted in the report (.RDL) file.

Setting Up Your System for Secure Communications

We strongly recommend that SSL connections be used for all communications between Reporting Services and a SAP BW server. To use SSL, your SAP BW server must be configured to accept HTTPS connections, and you must use "https" in the connection string for your data source.

Note   By default, the SAP Web server configures the SSL connections to use a different port than the non-SSL connections. Make sure that you have specified the correct port number for SSL in your connection string.

All computers that use the SSL data source must have a client certificate installed. This includes the Report Server and computers used by the report authors to design reports using Reporting Services in Business Intelligence Development Studio. Report users who access reports through the Web do not need to have a SAP BW certificate installed.

To install a certificate on a client computer

  1. Open Internet Explorer, and type the URL that points to the XML/A SSL service for your SAP BW server—for example: https://sap8:8000/sap/bw/xml/soap/xmla
  2. Click Go to access the site that hosts the Web service. If a certificate has not been installed, the Security Alert dialog box appears.
  3. In the Security Alert dialog box, click View Certificate.
  4. In the Certificate dialog box, click the General tab, and then click Install Certificate.
  5. Follow the steps of the Certificate Import Wizard. The default options are usually acceptable.

After the certificate has been installed, the computer should be able to access the SSL data source.

Network Debugging and Transport Compression

Sometimes, it can be useful to examine the raw XML communications by using a network tracer or protocol analyzer. To disable transport compression so that the XML is readable, add this key-value pair to your connection string: Transport Compression=None. When doing this kind of testing, you will typically need to use a non-SSL connection; therefore, make sure that you are on a secure network or that you log on using an account that has the minimum required privileges.

MDX Debugging on the SAP BW System

Advanced users might be interested in testing raw MDX queries directly on the SAP BW system. This is especially useful if your query is not executing correctly. Running the query directly on the server can help to isolate the source of the problem. When you use the SAP GUI, use the mdxtest transaction to display an MDX editor that will enable you to execute raw MDX queries and view the results. This bypasses the XML/A interface and submits the MDX queries directly to the server.

Working with Properties

When you add a node to a query, the query builder generates an MDX statement that requests not only the default property (the one displayed in the data preview), but also some additional properties. For key figures, the default property is the raw numeric value; however, the formatted value of the property also is available, as well as the formatted string representation of the property. For dimension members, the default property is the user-friendly caption, but you can also use the member's unique name property.

The following cell properties are available for key figures:

  • VALUE (Default)
  • FORMATTED_VALUE
  • FORMAT_STRING
  • BACK_COLOR
  • FORE_COLOR

The following metadata properties are available for dimensions:

  • MEMBER_CAPTION (Default)
  • MEMBER_UNIQUE_NAME

When you arrange the fields on a report in the Layout tab of the Report Designer, you can include a field in the report by dragging it to the layout surface. By default, the field's Value property is added to the report. To use one of the other properties, you can edit the expression of the text box to reference one of the other properties by using the Fields!FieldName.PropertyName syntax, as shown in Figure 4.

Click here for larger image

Figure 4. Specifying alternate properties during report layout (Click on the picture for a larger image)

In addition to the properties that were mentioned earlier, some dimensions have additional dimension-specific properties that are defined in SAP BW. These additional properties appear as nodes under the Member Properties folder in the metadata tree view. To make these properties available in a report, drag the node onto the design surface. Figure 5 shows the query designer after the Name property of the Material group has been added to the query.

Click here for larger image

Figure 5. Including a member property in a query (Click on the picture for a larger image)

To use the property that you have just added in your report, modify the expression in the text box on the layout surface to use the Fields!FieldName("PropertyName") syntax, as shown in Figure 6.

Click here for larger image

Figure 6. Specifying a member property during layout (Click on the picture for a larger image)

You can also create a field manually to represent one of these properties. To do so, in the Datasets pane, right-click your dataset, and then click Add. In the Add New Field dialog box, type a name for the new field and select the Calculated field option. Type an expression that defines the field in the text box, or click the Expression Builder button to build an expression. Figure 7 shows a field that references the same property as the one in Figure 6, but in Figure 7, the technical name has been used in the field definition. A property can be accessed by using either its friendly name or its technical name.

Bb508810.tripodwhitepaper07(en-US,SQL.90).gif

Figure 7. Adding a new property field

After you have created the field, you can include it in a report by dragging it onto the report layout.

Assigning Variable Values Without Using Report Parameters

When you assign a value to a variable, the query designer automatically parameterizes your report based on this variable. You might find that for a particular report, you want to assign a static value to the variable, instead of giving the report user the ability to change the value. To do so, assign a value to the variable, and then open the Report Parameters dialog box and select the corresponding report parameter. Select the Internal option for the report parameter, and then select the Allow null value option, as shown in Figure 8. In the Default values group, select Null as the default value. Click OK to close the Report Parameters dialog box.

By doing this, you disable the report parameter, forcing it to use the variable value that you specified originally when you created the variable in the Variables dialog box. The user will not be given the option to specify a value for this parameter.

Click here for larger image

Figure 8. Disabling a report parameter (Click on the picture for a larger image)

Populating Report Parameter Values

By using report parameters in Reporting Services, you can specify a set of values for the users to choose from when the report runs. It is a good idea to set up a list of possible values for parameters, because it will greatly improve the usability of your report for users. You can enter the set of possible parameter values manually, by using the Non-queried option, or you can set up the report to query the server for a list of possible values. The following procedure shows how to populate a list of values by using a query. It assumes that you are working with a QueryCube that already has a variable defined.

To populate a list of values by using a query

  1. Click Variables to open the Variables dialog box.

  2. Assign a default value to the variable, and then click OK. This causes a corresponding report parameter to be generated.

  3. In the Dataset drop-down list, select <New Dataset...> to open the Dataset dialog box.

  4. Select the same data source as your original dataset. Give the dataset a descriptive name, such as "VAR1_ParameterValues".

  5. In the Metadata pane, click the cube that contains the parent hierarchy of the variable you are configuring. This is usually the same cube as the one in your original dataset.

  6. Drag the parent hierarchy onto the Design surface.

  7. Click Refresh Fields to populate the dataset field list. You should see a field or fields corresponding to the hierarchy that you just added. The fields appear in the datasets pane under the new dataset, as shown in Figure 9.

     

    Click here for larger image

    Figure 9. Building a query for a report parameter (Click on the picture for a larger image)

  8. Right-click the new dataset and then click Add. You will be creating a new field for each hierarchy member that contains the unique name of the member. The variables will be assigned values based on the technical names of the members.

  9. In the Add New Field dialog box, specify the UniqueName property as the calculated field value for your new field. An example is shown in Figure 10.

    Bb508810.tripodwhitepaper10(en-US,SQL.90).gif

    Figure 10. Creating a unique name field

  10. On the Report menu, select Report Parameters to open the Report Parameters dialog box.

  11. In the Available values section, select the new dataset from the Dataset list. In the Value field text box, select the unique name field that you just created. In the Label field, select the original field name, as shown in Figure 11.

  12. Click OK to close the dialog box.

     

    Click here for larger image

    Figure 11. Populating the available values from a dataset (Click on the picture for a larger image)

    The report user will now have a drop-down list populated with the possible values for the report parameter. Figure 12 shows the list in the preview screen of Business Intelligence Development Studio. This same list will be available to users when they view the report.

     

    Click here for larger image

    Figure 12. Report parameter populated with values from a query (Click on the picture for a larger image)

Populating Parameters Using Values from a Multilevel Hierarchy Node

In the previous procedure, the set of report parameter values can be populated only from one field in the dataset. However, within a dataset, the members of different levels of a hierarchy always appear in separate columns and therefore in separate fields. This is a by-product of the flattening algorithm defined in the OLE DB for OLAP specification. So, how can we populate the values for a variable with members from different levels of a hierarchy if the values from different levels always appear in separate fields? In SAP BW, Hierarchy Node variables can accept values from any level of the hierarchy.

The solution is to modify the MDX statement, so that the aggregated "All" values from the parent level are included in the result column, and then use Reporting Services functions to select the correct value. In this section, you step through the process for creating a parameter set for a multilevel hierarchy.

To understand this technique, you must understand the basic function of the flattening algorithm. Assuming only a single dimension property and a single hierarchy on the axes, other than axis-0, the flattening algorithm specifies that there will be a column for each level of the hierarchy, starting from the top of the hierarchy down to the level closest to the leaf. However, the default query that is produced by the query designer when you drag a hierarchy to the design pane selects all members from the lowest leaf level only. The following MDX statement represents the default query that was created by dragging the hierarchy to the design surface:

SELECT
NON EMPTY {[Measures].[1MEHY2C418T0QYBP2KX9KQBG6]} ON COLUMNS,
NON EMPTY {[0D_COUNTRY                    PM_COUNTRY].[LEVEL02].ALLMEMBERS
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [0D_PU_C01/ZD_PU_C01_VAR1] CELL PROPERTIES VALUE

Note   Because of an issue in the SAP implementation of the ReturnCellProperty XML/A property, you must reference at least one measure, even if you intend to ignore these values.

In the sample query, the result set returned contains one column for each non-ALL level of the hierarchy, down to the lowest specified level, as shown in Figure 13.

Bb508810.tripodwhitepaper13(en-US,SQL.90).gif

Figure 13. Result set from a default hierarchy query

Now, switch to MDX mode and modify the query, so that "All" members are included. Instead of selecting just the members from the leaf level, modify the MDX statement to select all members from the entire hierarchy.

SELECT
NON EMPTY {[Measures].[1MEHY2C418T0QYBP2KX9KQBG6]} ON COLUMNS,
NON EMPTY {[0D_COUNTRY                    PM_COUNTRY].ALLMEMBERS}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [0D_PU_C01/ZD_PU_C01_VAR1] CELL PROPERTIES VALUE

The result set for this query is shown in Figure 14. The blank entries in the leaf column (PM_CountryLevel02) represent the "All" members, which are aggregate values for the parent level. Of these blank entries, the first row, which contains two blanks, is the ALL member for the entire hierarchy. The second row has EUROPE in the first column and a blank in the leaf column, and is the member representing "All" for Europe. The sixth row has AMERICA in the first column and a blank in the leaf column, and represents "All" for America. This dataset gives us a separate row for all the members of the hierarchy that we want to make available as possible values for our Hierarchy Node variable.

Bb508810.tripodwhitepaper14(en-US,SQL.90).gif

Figure 14. Result set including All members

Next, we will create some fields that consolidate all the required information. First, create a label field.

  1. Right-click the parameter dataset in the dataset window, and then click Add.

  2. In the Add Field dialog box, type a descriptive name for the field—for example, PM_COUNTRY_Parameter_Labels.

  3. Select the Calculated field option.

  4. Click the Expression Builder button in the Value text box to create an expression that selects the value from the level-2 column if it is non-blank, and otherwise selects the value from the level-1 column. To do so, use the Reporting Services IIF function:

    =IIf(Fields!PM_Country_Level_02.Value = "",
    Fields!PM_Country_Level_01.Value, Fields!PM_Country_Level_02.Value)
    
  5. Click OK.

  6. Follow steps 1–3 to create a field that consolidates all the unique names for our Value field.

  7. In the Value text box, type the following formula:

    =IIf(Fields!PM_Country_Level_02.Value = "",
    Fields!PM_Country_Level_01.UniqueName,
    Fields!PM_Country_Level_02.UniqueName)
    
  8. Open the Report Parameter dialog box.

  9. In the Available values group, assign the Value field and Label field to these composite fields, as shown in Figure 15.

 

Click here for larger image

Figure 15. Assigning report parameter values using consolidated fields (Click on the picture for a larger image)

The report user can now select values from different levels of the hierarchy. For example, in Figure 16, the drop-down list contains nodes from the continent level (EUROPE, AMERICA), as well as nodes from the country level (Germany, France, USA).

Click here for larger image

Figure 16. Parameter selection list populated from separate levels of a hierarchy (Click on the picture for a larger image)

You can extend this method to a hierarchy with more levels. To do so, in the calculated field expression, nest multiple IIf statements to cover each level in which you're interested.

This method is applicable when you want the selection list to include all levels down to the leaf level. However, if you want selections only from some higher levels, you must modify the MDX statement; otherwise, your selection list will contain duplicate values. Instead of selecting all the members from the entire dimension, use the MDX DRILLDOWNLEVEL function to drill to the level that you want. Start from the highest level you are interested in, and drill down to the lowest level that you want. The following example of an MDX statement shows how to drill down twice from level 1, to obtain all the members from level 1 down to level 3.

SELECT
NON EMPTY {[Measures].[57FQA4HFVVTQPYLPVV4RP673N] } ON COLUMNS,
NON EMPTY {DRILLDOWNLEVEL(DRILLDOWNLEVEL([0D_DBSIC1
BRANCHE SIC_HIERARCHIE].[LEVEL01].ALLMEMBERS))}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [0D_DX_C02/Z_DX_TEST] CELL PROPERTIES VALUE

The resulting rowset looks like the one in Figure 17.

Bb508810.tripodwhitepaper17(en-US,SQL.90).gif

Figure 17. Result set from drill-down query

You can now define calculated fields in the report to consolidate the values from the different columns, by using the technique described earlier.

Specifying Value Ranges Through Report Parameters

As described earlier in the Variables section of this paper, you can create variables that take multiple values, or variables that take a range of values. Variables that have been defined as interval variables can accept a range specifier as a value. In a range specifier, you define a high and low value, and all the values in between are automatically used as values in the query. If you want the user to be able to specify a range of values, you must also specify that your report parameter is a multivalue parameter. To create a multivalue parameter, in the Report Parameters dialog box, select the Multi-value option.

If the provider receives multiple values for a variable that has been defined as an interval variable, it will take the first two values as the low and high range specifiers, respectively. If the provider receives a single value, only that value will be passed to the variable.

To define a variable as an interval variable, open the SAP BW Variables Wizard or the BEx Query Designer. In the SAP BW Variables Editor dialog box, select Interval as the value in the Variable Represents list.

Note   Another type of variable—a Selection Option variable—can accept any combination of multiple ranges and single values. There is currently no way to let users specify a complex variable value by using the Microsoft .NET Data Provider 1.0 for SAP NetWeaver Business Intelligence. If multiple values are specified for this kind of variable, they will always be treated as multiple single values, not as range specifiers.

Technical Details

This section discusses some of the technical details behind the Microsoft .NET Data Provider 1.0 for SAP NetWeaver Business Intelligence.

XML for Analysis and SAP BW

The Microsoft .NET Data Provider 1.0 for SAP NetWeaver BI communicates with SAP BW servers by using the XML for Analysis 1.1 (XML/A) protocol. XML/A is an XML standard for Online Analytical Processing (OLAP) using standard Internet protocols. XML/A was built on the OLE DB for OLAP (ODBO) specification and therefore contains many similar interfaces, structures and concepts. Support for XML/A is built into SAP BW, but a given server must be configured to use this feature.

Flattened Rowsets and Cell Properties

Reporting Services processes all data from the flattened rowset of the results. This provides many performance benefits, because of the way in which these rowsets are processed. The original algorithm for generating a flattened rowset was defined in the ODBO specification. However, this algorithm did not provide any support for cell properties. The specification was never clear about which cell property to return for the key figures; essentially, it was up to each implementer to decide on the appropriate value to return. In the SAP ODBO provider and in SAP BW's original XML/A provider, the flattened rowset returns the formatted string value for each key figure. As of SAP BW Service Pack 16, the XML/A provider supports a new XML/A property called ReturnCellProperties. The Microsoft .NET Data Provider 1.0 for SAP NetWeaver BI sets this property to true, which causes the SAP BW XML/A provider to process the MDX CELL PROPERTIES clause and return all the properties listed. The key-figure column names consist of the key figure concatenated with the property name. If you are connecting to an SAP BW server that is at a patch level before SP16, these additional CELL properties will not be processed; therefore, you will be able to access only the formatted value of each key figure. (See the Troubleshooting section, later in this paper, for discussion of a related issue.)

Transport Compression

The XML character-based format makes it easy to read, but can result in high network-bandwidth usage compared to binary protocols. Fortunately, the textual nature of XML also makes it an ideal candidate for compression. By default, the Microsoft .NET Data Provider 1.0 for SAP NetWeaver BI uses GZIP compression when communicating with the SAP BW server. This reduces the required bandwidth significantly and improves performance noticeably. You can disable transport compression as described in the Network Debugging and Transport Compression section, earlier in this paper.

Authentication Security

The Microsoft .NET Data Provider 1.0 for SAP NetWeaver BI transmits credential information using HTTP basic authentication. Therefore, we recommend strongly that you use only secure SSL connections for communications with the SAP BW server. For information about how to configure your report server and client computers to use SSL, see the Tips and Tricks section, earlier in this paper.

Troubleshooting

This section provides solutions for issues encountered at customer sites when working with the Microsoft .NET Framework Data Provider 1.0 for SAP NetWeaver Business Intelligence. These examples include instructions for using the features of both SAP products and Microsoft products to find the root cause of an issue.

Starting the XML/A Service in SAP BW

Because the Microsoft .NET Data Provider 1.0 for SAP NetWeaver Business Intelligence is certified for the XML/A interface in SAP BW, you first must start the service. You can maintain services through transaction SICF.

Starting the XML/A Service

Right-click the XML/A service and select Test Service.

Bb508810.tripodwhitepaper18(en-US,SQL.90).gif

Figure 18. Starting the XML/A service in SAP BI

A browser window displaying an XML schema opens. The URL is in the following format:

<server>:<port>/sap/bw/xml/soap/xmla

Example: http://comcc27:8000/sap/bw/xml/soap/xmla?sap-client=000

If you cannot see the XML output in a browser, your SQL Server Reporting Services (SSRS) connection will not work. Figure 19 shows the browser window displaying the results of the service test successfully.

Bb508810.tripodwhitepaper19(en-US,SQL.90).gif

Figure 19. Testing the SAP BI XML/A service by using a browser

Starting the XML/A Service if the Browser Test Fails

You might run into an issue where you don't get the XML response, because the URL that was generated by the service-test function is not correct. If this occurs, take the following steps to start the service.

  1. Use transaction SE37 and start the function named RSBB_URL_PREFIX_GET, as shown in Figure 20.

    Bb508810.tripodwhitepaper20(en-US,SQL.90).gif

    Figure 20. Typing in function RSBB_URL_PREFIX_GET in transaction SE37

  2. After you select the function, enter CL_RSR_MDX_SOAP_HANDLER as the handler class (see Figure 21).

    Bb508810.tripodwhitepaper21(en-US,SQL.90).gif

    Figure 21. Entering the appropriate function call parameters

    The function returns the URL that you need for the XML/A service in the E_URL_PATH export parameter, as shown in Figure 22.

    Bb508810.tripodwhitepaper22(en-US,SQL.90).gif

    Figure 22. Viewing the output of function RSBB_URL_PREFIX_GET

  3. Run the function twice—once with and once without the "X" for the message server. If the URLs are different, try to open each one in a browser. One of them should succeed.

Configuring the SSRS Data-Source Type

After you install SQLServer2005 SP1 (or later), a new SSRS data-source type named SAP NetWeaver BI should be available.

If you try to view a report and the report server returns the message "An attempt has been made to use a data extension 'SAPBW' that is not registered for this report server," you must add the data-source type manually to the following configuration file:

...\Program Files\Microsoft SQL Server\MSSQL.N\Reporting Services\ReportServer
RSreportserver.config

The following line must be in the <Data> ..... </Data> section:

<Extension Name="SAP BW" 
Type="Microsoft.ReportingServices.DataExtensions.SapBw.SapBwConnection,
Microsoft.ReportingServices.DataExtensions.SapBw" />

If the new data-source type does not show up in the Report Designer, manually add it to the designer configuration file:

...\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
RSReportDesigner.config

There should be two new entries in RSReportDesigner.config. One is in the <Data> ..... </Data> section:

<Extension Name="SAP BW" 
Type="Microsoft.ReportingServices.DataExtensions.SapBw.SapBwConnection,
Microsoft.ReportingServices.DataExtensions.SapBw" />

Another entry is in the <Designer> ..... </Designer> section:

<Extension Name="SAP BW" 
Type="Microsoft.ReportingServices.QueryDesigners.SapBw.SapBwQueryDesigner,
Microsoft.ReportingServices.QueryDesigners.SapBw"/>

Using HTTP Compression

The .NET Data Provider 1.0 for SAP NetWeaver Business Intelligence uses HTTP compression by default. For debugging purposes, it can be useful to turn off HTTP compression. To do so, add Transport Compression=None to the connection string, as shown in Figure 23.

Bb508810.tripodwhitepaper23(en-US,SQL.90).gif

Figure 23. Disabling HTTP compression in the connection string

After HTTP compression is turned off, it is possible then to use a network sniffer to trace the XML communication between SSRS and SAP BI. This might be useful in case there are issues related to the network or proxy servers. Figure 24 shows a sample output of a network packet trace.

Bb508810.tripodwhitepaper24(en-US,SQL.90).gif

Figure 24. Using a network sniffer to analyze the communication between SSRS and SAP BI

To use HTTP compression, HTTP 1.1 is required. If you run into issues related to proxy servers, make sure that HTTP settings in Internet Explorer are configured correctly. In Internet Explorer, check the Advanced settings for using HTTP 1.1 through proxy connections. Both options should be selected, as shown in Figure 25.

Bb508810.tripodwhitepaper25(en-US,SQL.90).gif

Figure 25. HTTP setting for proxy connections

If these simple solutions do not fix the problem, it might be necessary to turn off compression in the connection string—not only for debugging, but for general operation. This results in more traffic over the network.

Time-Outs

When the connection is working and you define a query in the SSRS query designer, you might get a time-out error, such as that shown in Figure 26.

Bb508810.tripodwhitepaper26(en-US,SQL.90).gif

Figure 26. Time-out error when accessing SAP BI

This error is probably related to the fact that SAP BI, by default, cancels any MDX statement that is sent through XML/A and takes longer than 60 seconds. You should easily be able to see the long-running process in transaction SM50 in the SAP GUI.

To avoid this error, change the time-out value parameter in the SAP profile:

icm/keep_alive_timeout=600

In this example, 600 is the number of seconds for the time-out; you can set it to a different value. After the SAP application servers are restarted, the MDX statement can run up to 600 seconds without an error in the query builder.

Verifying BW Query Results in SAP GUI

If you find issues with the result sets returned through SSRS, try running the BW queries directly through the SAP GUI to verify the output. Existing BW queries are presented as multidimensional cubes (also called query cubes).

Selecting the Query in the SAP GUI

Figure 27 shows how to select an existing query through transaction RSRT/RSRT2 in the SAP GUI.

Bb508810.tripodwhitepaper27(en-US,SQL.90).gif

Figure 27. Selecting an existing SAP BI query in the SAP BI Query Monitor

Figure 28 shows the results of executing an existing SAP BI query.

Bb508810.tripodwhitepaper28(en-US,SQL.90).gif

Figure 28. Checking the query result in the SAP BI Query Monitor

Copying the MDX Statement to the SAP GUI

Another option is to copy the MDX statement in the Report Designer query builder to the SAP GUI. SAP BI offers a test environment for MDX through the mdxtest transaction. First, copy the text from the query builder in SSRS Report Designer.

Bb508810.tripodwhitepaper29(en-US,SQL.90).gif

Figure 29. Copying the MDX statement from the SSRS query builder

Select the appropriate catalog and cube in the MDX Testeditor. You can now run the MDX statement against it.

Bb508810.tripodwhitepaper30(en-US,SQL.90).gif

Figure 30. Running the MDX statement in the SAP BI MDX Testeditor

SAP BI Query Variables and Default Values

You might run into one of two known issues with SAP BI query variables.

Specifying Default Values for Required Query Variables

If you define a mandatory query variable in the SAP Query Designer, but do not define a default value, an error will occur. Figure 31 shows the dialog box for defining SAP BI query variables.

Bb508810.tripodwhitepaper31(en-US,SQL.90).gif

Figure 31. SAP BI query-variable definition

In the SSRS query builder, if you were to select a query that has the settings shown in Figure 31, you would get an error message stating that you have not specified a required value. Figure 32 shows an error of this type.

Click here for larger image

Figure 32. Using a mandatory SAP BI query variable without a default value (Click on the picture for a larger image)

After you get this error, you can specify a query-variable value. However, this works for the query result but not for the metadata, shown in the left pane. Looking at the members of a certain dimension still returns the error. There is only one way to fix this: Specify a default value for the variable in the SAP Query Designer.

Using the "Can be changed in query navigation" Option

The second issue is the fact that the SAP query-variable dialog box in the SSRS query builder does not work correctly if you define a mandatory variable in the SAP Query Designer and select the Can be changed in query navigation option in the Variables Editor dialog box. There is no known solution for this at the moment, besides clearing the option.

Specifying InfoProvider in the Connection String

If there are many cubes or query cubes in the SAP BI system, it makes sense to specify one catalog (SAP BI InfoProvider) in the connection string for SSRS. Figure 33 shows a connection string that specifies an initial catalog.

Bb508810.tripodwhitepaper33(en-US,SQL.90).gif

Figure 33. Specifying the initial catalog in the connection string

After you have specified the initial catalog, only the query cubes for this InfoProvider are displayed, reducing the time it takes to open the dialog box.

Bb508810.tripodwhitepaper34(en-US,SQL.90).gif

Figure 34. Result of specifying an InfoProvider

Debugging SAP BI by Using RSRTRACE

An advanced option that you can use to investigate XML/A issues in SAP BI is the RSRTRACE feature. By using this feature, you can collect traces on a user basis.

To use RSRTRACE

  1. Activate the trace for the relevant user, as shown in Figure 35.

    Bb508810.tripodwhitepaper35(en-US,SQL.90).gif

    Figure 35. Activating a trace for user SAP_PERF

  2. In SSRS, run an XML/A query. You will see the user trace as a list of logged callups for the selected user, as shown in Figure 36.

    Bb508810.tripodwhitepaper36(en-US,SQL.90).gif

    Figure 36. Viewing the trace

  3. Select the callup. You will see the associated XML/A request, as shown in Figure 37.

    Bb508810.tripodwhitepaper37(en-US,SQL.90).gif

    Figure 37. Viewing XML/A requests in logged callups

  4. Start the debugger to look at the MDX functions at the ABAP level.

    Bb508810.tripodwhitepaper38(en-US,SQL.90).gif

    Figure 38. Starting the ABAP debugger by double-clicking a logged callup

  5. An important class for debugging is CL_RSR_MDX_COMMAND. Set the breakpoint in the PARSE method of this class. Figure 39 shows you how to do so.

    Bb508810.tripodwhitepaper39(en-US,SQL.90).gif

    Figure 39. Setting a breakpoint in the CL_RSR_MDX_COMMAND class

  6. Find the MDX statement that was sent by SSRS, as shown in Figure 40.

    Bb508810.tripodwhitepaper40(en-US,SQL.90).gif

    Figure 40. Looking for the MDX statement that was sent by SSRS in the PARSE method

If you still have trouble with SSRS connectivity after debugging, you should involve SAP support.

Setting External Breakpoints in NetWeaver2004s

SAP introduced a useful feature in NetWeaver2004s called external breakpoints. If you set an external breakpoint (for example, in the MDX PARSE method), and then run a query out of SSRS against SAP BI, an ABAP debugger window automatically opens at this breakpoint for further analysis.

Besides the CL_RSR_MDX_COMMAND class, another important class, CL_RSR_MDX_SOAP_HANDLER, is useful for analyzing SAP BI behavior. Use it to find all XML/A items, such as Discover and Execute. Figure 41 shows the CL_RSRS_MDX_SOAP_HANDLER class (which includes the Discover and Execute methods) in the ABAP debugger.

Bb508810.tripodwhitepaper41(en-US,SQL.90).gif

Figure 41. The CL_RSR_MDX_SOAP_HANDLER class

Maintaining SAP BI XML/A Schemas

SAP BI provides a program called RRT_MDX_SCHEMA that enables you to maintain different XML/A related schemas online.

To use RRT_MDX_SCHEMA

  1. Launch the ABAP Editor.

  2. Select RRT_MDX_SCHEMA in transaction SE38, as shown in Figure 42.

    Bb508810.tripodwhitepaper42(en-US,SQL.90).gif

    Figure 42. Calling the RRT_MDX_SCHEMA program

  3. Select an XML/A schema to view or edit it.

    Bb508810.tripodwhitepaper43(en-US,SQL.90).gif

    Figure 43. Selecting an XML schema

After you select an XML schema, it opens for viewing or editing, as shown in Figure 44.

Bb508810.tripodwhitepaper44(en-US,SQL.90).gif

Figure 44. Maintaining the selected XML schema

Additional Issues

This section describes some additional issues that you might experience when using the Microsoft .NET Data Provider 1.0 for SAP NetWeaver Business Intelligence.

  • The metadata tree view displays an error node with the following message:

    Error occurred retrieving child nodes: Guid should contain 32 
    digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).
    

    This is a known issue with SAP BW 3.5 servers at Service Pack level 15 (SAP Note reference: 893807). To resolve it, upgrade your SAP BW server to Service Pack level 16 or later.

  • I cannot perform arithmetic operations on my key-figure data, because the values are returned as formatted strings.

    The XML/A ReturnCellProperties property allows the format of the cell data to be specified by using the MDX CELL PROPERTIES clause. Support for this property was added as of Service Pack level 16 (SAP Note reference: 895234). After this service pack is applied, the default values that are returned to the extension are raw numeric values. However, you can still access the formatted value property by using the FormattedValue method in Reporting Services.

  • Queries that reference multiple dimensions fail on the server.

    There is a known issue with BW3.5 SP11 and SP12 that causes the server to process cross joins in a memory inefficient manner. Therefore, queries with large cross joins might fail when the server runs out of memory. This issue has been corrected as of SP13 (SAP Note reference: 836849).

  • In MDX mode, my query has at least one axis. My first axis contains an empty set. However, I am getting the error message "The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension."

    This is caused by an issue with the way SAP BW handles queries that do not reference the Measures dimension. This issue might be resolved in a future service pack. In the meantime, always reference at least one measure in your first axis.

Additional Resources

For more information:

Acknowledgements

Thanks to Brian Welcker, Jerry Povse, Albert Yen, Bill Faison, Lev Semenets, Cristoph Schuler, Jock Williams, Hermann Daeubler, Stefan Beidenstein, George Chow, Darryl Eckstein, and everyone else at Microsoft, SAP, and Simba Technologies who helped along the way.