Using SQL Server 2008 Reporting Services with SAP NetWeaver Business Intelligence

 

SQL Server Technical Article

 

Writers: Darryl Eckstein, Simba Technologies

Support Tips Section: Hermann Daeubler

Technical Reviewers: Craig Guyer, GuilhermeBoreki,

Project Editor: Deborah Dinzes

 

Published: April 2006

Updated: October 2009

Applies To: SQL Server 2008 Reporting Services

 

Summary: This paper discusses the integration of SAP NetWeaver BI ® with Microsoft ® SQL Server ® 2008 Reporting Services (SSRS). The paper provides an overview of using the Reporting Services SAP Netweaver BI data provider and related query designer, to build high quality reports against SAP NetWeaver BI sources. For Reporting Services users who are new to working with SAP NetWeaver BI, this paper will help you connect and build datasets. For users 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.

 

Introduction

Through a rich extensibility model, the report authoring and report deployment features of SQL Server 2008 Reporting Services can integrate with a variety of data sources. To answer the strong need for a rich reporting tool for SAP NetWeaver BI® and to bridge the gap between these two powerful business intelligence platforms, Microsoft has developed a .NET Framework data provider and query designer that allows SQL Server2008 Reporting Services to build reports against SAP NetWeaver BI. SAP Netweaver BI users can  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.

What’s New in this Release

New features in Reporting Services 2008 include:

·          New SAP connection string editor.

·          Single sign on.  While SSO is not officially supported with any version of SAP, the functionality is available (off by default).  It is available for testing and may be enabled and used at your organization’s discretion.

·          Improved metadata load times.

·         Enhanced variable support.

·         Display Technical Names option: shows the SAP technical name of elements beside their friendly name. Toggled on/off by a button on the toolbar.

·         Collections of cubes, dimensions and hierarchies are displayed sorted alphabetically for easy navigation.

·         Added support for cubes containing two SAP structures: the contents of each structure (key figures and characteristics) can be accessed transparently.

·         Simplified SAP variable support: each report is now automatically parameterized according to any SAP variable available in the cube.

 

 

Prerequisites

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

·         Microsoft SQL Server 2008 Reporting Services

 

The provider has been developed for and tested against SAP BW 3.5 and 7.0.

·         Support Package 20 for SAP BW 3.5 and 7.0

 

For Windows Integrated Authentication the provider was developed for and tested against the following systems:

·         SAP Portals 6.40 Support Package 20

·         SAP Portals 7.0 Support Package 11

·         SAP Duet 1.0

 

The Troubleshooting section describes some of the known issues related to the use of the provider against a SAP Netweaver BI server that does not have a sufficient service pack level.

Installation Overview

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

SAP NetWeaver BI Terms and Concepts

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

Metadata Objects

Some SAP BW terms for MDX metadata objects are the same as SQL Server Analysis Services 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, MDX 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 Netweaver BI terminology..

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 also frequently referred to as a QueryCube.

The Microsoft .NET Data Provider for SAP NetWeaver Business Intelligence communicates with SAP BW by using XML for Analysis (XML/A), which makes some InfoProviders available directly. XML/A provides direct access to QueryCubes and also to 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 also be configured 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 that it was built upon, followed by a forward slash and the query name. For example, a query QUERYCUBE1 built on the InfoCube $0D_DECU 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 QueryCube [0D_DECU/QUERYCUBE1] 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.

 

Figure 1   Cube Selection dialog box

Variables

SAP variables are a way of parameterizing a QueryCube that is similar to the parameter feature in Analysis Services. 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 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 Provider1.1 for SAP NetWeaver Business Intelligence 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.

Reporting Services 2008 reports created against a QueryCube that has variables defined will automatically be parameterized according to those variables. You can assign default values for those variables using the Variables dialog. Unlike the parameter feature for Analysis Services, a list of available values is not generated for the report parameter. To manually define a list of values, follow the instructions in the section of this paper on Populating Report Parameter Values.

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

 

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.		Hierarchy list box:
Formula variables	Formula variables are used to affect calculated key figures. Formula variables accept numeric values that are used in the calculations.		Text box:
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.		Single-select tree view:
Member variables (multiple value):	Multiple-value member variables are the same as Member variables, but they can accept one or more values.		Multi-select tree view:
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.		Range tree view:

 

Table 1   Variables dialog box

 

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, and instead opens a text box for you to manually enter a variable value.

BEx Query Structure Display

The metadata organization and display for cubes based on BEx queries containing two structures has been updated to remove the generated synthetic Key Figures dimensions. As an example assume that your BEx query contains two structures as shown in the diagram below.

Figure 2   BEx query with two structures

With SQL Server 2008 Reporting Services, the metadata display for this BEx query appears as shown in the picture below. Notice that there are two dimensions for Key Figures.

Figure 3   Metadata display for BEx query with two structures (SQL Server 2008 Reporting Services)

In SQL Server 2008 the metadata display will display the structure information from SAP as shown below. Notice that Key Figures now contains the members from the BEx structure instead of the synthetically generated members.

Figure 4   Metadata display for BEx query with two structures (SQL Server 2008 Reporting Services)

Tips and Tricks

This section provides some techniques for working with features of the Microsoft .NET Data Provider 1.1 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 Internet Explorer to open the XML/A provider’s URL. If you successfully connect to the provider, you should see the Web service’s XML description, similar to Figure 5.

Figure 5   Testing the SAP BW XML/A provider by using Internet Explorer

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.

Figure 6   Configuring a QueryCube for external access

 

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 the ReportDesinger 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 may 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 transaction mdxtest 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.

Setting up an SAP BW Connection

The SSRS connection string used to connect to an SAP BW system may be specified manually edited. It can also be generated using the connection string editor. After selecting the SAP NetWeaver BI connection type, press the Edit button to bring up the connection string editor.

 

Figure 7   Setting SAP BW connection properties

 

Here you must specify the URL for the XML/A web service. You can also optionally provide a URL used to generate SAP (SSO) tickets, such as an SAP Portals or Duet ticket issuer service URL. See Table 2 for example URLs.

Authentication information from this screen is propagated to the Credentials tab of the Data Source Properties window. If an SSO ticket-issuing URL is provided, then the authentication credentials are used to establish a connection to the service at that URL. The resulting SSO ticket will be passed on to the XML/A service. Otherwise the authentication information is passed to the XML/A service directly.

The Windows Integrated Authentication option is for anonymous authentication or to specify integrated (Kerberos) authentication against an SSO ticket-issuing URL. The Windows Integrated Authentication option requires both a XML/A URL and a SSO ticket-issuing URL. The SAP BW XML/A web service does not support Kerberos authentication directly so the URL of a SAP Java Application Server is required to perform the Kerberos authentication. If you have configured your XML/A web service to allow anonymous authentication then an SSO ticket-issuing URL is not required. The Basic Authentication option specifies username and password authentication against either URL. Use the Save my password option to ensure that the password is saved after leaving this window. To use the Basic Authentication option only a XML/A URL is required.

If you require secure communication to your SAP servers, which is highly recommended when using Basic Authentication, you should set up https support on your servers and provide https URLs, as discussed earlier in this document.

Note that SAP BW also allows credentials to be submitted by using the URL 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 definition (.rdl) file.

You can optionally provide an SAP BW client number and language in this editor. If you are using an SSO URL then you must specify a client number and language in the editor. Otherwise you may receive errors opening a connection to the SAP server. The language you choose corresponds to the sap-language parameter for SAP, and the client you choose corresponds to sap-client. The language entry can be a one-character SAP language specification or a two-character ISO 639 language specification. If you do not specify a client number or language, the server uses the default value configured on the server. After specifying your connection properties, you can test the connection by pressing Test Connection. Once a connection can successfully be established, you can optionally pick a specific catalog to associate with the connection.

The new connection string items are described in the table below.

 

Connection String Key	Description	Example
SAP Language	Override the configured default language for captions and texts.	SAP Language=EN;
SAP Client	Override the default client configured for the XMLA web service with SAP.	SAP Client=100;
SAP SSO Address	Ticket issuing URL on a SAP Java server to perform Kerberos authentication against.	For SAP Portal: SAP SSO Address= http://sap3:50100/irj/portal   For SAP Duet: SAP SSO Address= http://sap2:50000/osp/TicketIssuer

 

Table 2   SAP Connection String Description

 

Metadata Technical Name Display

The SAP technical names for metadata can be toggled via a button on the toolbar.

Figure 8   Toggle Technical Names Button

The metadata display with technical names is shown below. The technical names are shown after the texts.

Figure 9   Metadata display with Technical Names

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 is also available, and also 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 10.

Figure 10   Specifying alternate properties during report layout

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 11 illustrates the query designer after the Name property of the Material group has been**added to the query.

Figure 11   Including a member property in a query (note the Display Technical Names option is turned on in this screenshot)

To use the property that you have just added in your report, right click the text box on the layout surface and select Expression. Modify the expression to use the Fields!FieldName(“PropertyName”) syntax as shown in Figure 12.

 

Figure 12   Specifying a Member property during layout

You can also manually create a field to represent one of these properties. To do this, in the Report Data pane, right-click your dataset, and then click Add Calculated Field. In the Dataset Properties dialog box, type a name for the new field. Click the function button to build an expression. Figure 13 shows an expression that references the same property as the one in Figure 12, but in Figure 13, the technical name has been used in the field definition. A property can be accessed by using its friendly name or its technical name.

 

Figure 13   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 create a report against a  cube that contains variables, corresponding parameters are automatically created in your report. You may 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 assign a default value to a variable, select the desired value in the Variables dialog. To suppress user input for a particular variable select the corresponding parameter in the Report Parameters dialog box and enable the Internal option (Figure 14) to disable user input.

 

 

Figure 14   Disabling a report parameter

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 OK to close the query designer. If choose the same cube as in your original dataset then you have to either remove the parameters from the new dataset or rename the parameter to a new name. For optional variables you can remove the parameter. For mandatory variables you need to rename the parameter in the Parameters view so it does not conflict with the parameter from your original dataset.

8.    Click OK to close the dataset and 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 15. Note in this example the parameter for “VAR1_ParameterValues” dataset was removed and does not appear in the Parameters folder.

Figure 15   Dataset for a report parameter

9.    Right-click the new dataset and then click Add Calculated Field. 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.

10.In the Dataset Properties dialog box, specify the UniqueName property as the calculated field value for your new field. An example is shown in Figure 16.

Figure 16   Creating a unique name field

11.Double click on your parameter to open the Report Parameters Properties dialog box.

12.In the Available Values section, select Get values from a query. Then select the new dataset from the Dataset list. For the Valuefield select the unique name field that you just created. For the Label field select the original field name, as shown in Figure 17.

13.Click OK to close the dialog box.

 

Figure 17   Populating the available values from a dataset

The report user will now have a drop-down list populated with the possible values for the report parameter. Figure 18 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.

 

Figure 18   Report parameter populated with values from a query

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

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 19.

Figure 19   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, as follows.

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 20. 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.

Figure 20   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 Calculated Field.

2.    In the Dataset Properties dialog box, type a descriptive name for the field, for example, PM_COUNTRY_Parameter_Labels.

3.    Click the Expression Builder button in the Field Source text box to create an expression that selects the value from the level-2 column if it is not blank, or selects the value from the level-1 column if level-2 is blank. To do this, 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)

4.    Click OK.

5.    Follow steps 1-3 to create a field that consolidates all the unique names for the Value field.

In the expression text box, type the following formula:

a.  =IIf(Fields!PM_Country_Level_02.Value = "",

b.  Fields!PM_Country_Level_01.UniqueName,

c.  Fields!PM_Country_Level_02.UniqueName)

6.    Close Dataset Properties dialog box.

7.    Open the Parameter dialog box and click on the Available Values option.

8.    For the Available Values, assign Value field and Label field to these composite fields, as shown in Figure 21.

 

Figure 21   Assigning report parameter values using consolidated fields

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

 

Figure 22   Parameter selection list populated from separate levels of a hierarchy

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

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 function DRILLDOWNLEVEL to drill to the level 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 23.

Figure 23   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 in the Variables section, 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 multi-value parameter. To create a multi-value 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.1 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.1 for SAP NetWeaver Business Intelligence.

XML for Analysis and SAP BW

The Microsoft .NET Data Provider 1.1 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 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, and it was essentially 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.1 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 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.1 for SAP NetWeaver BI uses GZIP compression when communicating with the SAP BW server. This significantly reduces the required bandwidth and noticeably improves performance. You can disable transport compression as described in the earlier section of this paper, in Network Debugging and Transport Compression.

Integrated Authentication Overview

Figure 24 shows the systems involved when using Integrated Authentication.

Figure 24   Integrated Authentication with SAP

Using Integrated Authentication requires the Reporting Services machine to be on a domain controlled by an Active Directory Services (ADS) domain controller. The SAP server(s) do not need to be on this domain, but the SAP Java application server used for authentication must have a trust relationship established with the ADS server. Reporting Services will seamlessly pass the Kerberos credentials of the user (which were established when the user logged into Windows as a domain user) to an SAP Java application server. The application server will trust any credentials generated by one of the ADS servers it trusts as proof of identity for registered SAP user. It will return an SSO ticket that the Reporting Services can use to communicate with SAP NetWeaver BI for XML/A.

Your SAP NetWeaver BI server must accept SSO tickets from you SAP Java application server. You must also setup a URL on your SAP Java application server to issue single sign-on tickets. This could be via SAP Portal, Duet, or a custom application. Note that this authentication scenario is identical to the authentication requirements for Duet. Accordingly if you have installed Duet, then you can leverage your existing Duet installation to provide single-sign capability for Reporting Services.

Troubleshooting

This section provides solutions for issues encountered at customer sites when working with the Microsoft .NET Framework Data Provider 1.1 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.1 for SAP NetWeaver Business Intelligence is certified for the XML/A interface in SAP BW, you must first 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.

Figure 25   Starting the XML/A service in SAP Netweaver BI: Transaction ‘SICF’

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 SSRS connection will not work. The following figure shows the browser window successfully displaying the results of the service test.

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

Starting the XML/A service if the browser test fails

You may 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 these steps to start the service.

1.    Use transaction “SE37” and start the function named RSBB_URL_PREFIX_GET as shown in Figure 27.

Figure 27   Typing in function RSBB_URL_PREFIX_GET in transaction SE37

After you select the function, enter CL_RSR_MDX_SOAP_HANDLER as the handler class as shown in Figure 28.

Figure 28   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 illustrated in the next figure.

Figure 29   Viewing the output of function RSBB_URL_PREFIX_GET

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.

Checking SSO Connectivity

You can use Internet Explorer to verify connectivity to the SSO Ticket Issuing URL you entered in connection editor. For example, if you entered an SAP Portals URL you should see your portal homepage as shown below if Windows Integrated (Kerberos) authentication is enabled correctly. Your portal homepage will probably look different than the example shown.

 

Figure 30   Portal Homepage

If the portal logon page appears as shown below then either your Windows user account does not have access to the SAP Portal server or Windows Integrated (Kerberos) authentication for your SAP Portal is either not enabled or configured.

 

Figure 31   SAP Portal logon home

If you are authenticating against a Duet server then you should see a page containing XML returned within IE.

 

Figure 32   Successfully authentication against Duet server

You will receive an HTTP 401 error if your Windows user account does not have access to the SAP Duet server or Windows Integrated (Kerberos) authentication is not correctly configured.

 

Figure 33   401 Unauthorized error from Duet server

SSO Connection Errors

Within the SSRS connection editor you can test your connection parameters to ensure that you have entered the correct information. If you receive HTTP 404 errors or the test connection seems to take hang you should first check connectivity as described in the previous section.

You may see an HTTP 400 error when trying to test your SAP SSRS connection if you are trying to use SAP Portals for issuing SSO tickets (6.40 SP21 or higher and 7.00 SP13 or higher). This error occurs because SAP Portals is rejecting the user agent used by SSRS when it makes the HTTP connection. A workaround is to use another URL on your SAP Java application server (such as Duet) for issuing SSO tickets or to authenticate directly against your SAP BW server using Basic authentication.

Figure 34   400 Error returned from SAP Portals

If you receive an HTTP 401 unauthorized error then either you did not enter your user name and password correctly or you do not have authorization to use the SAP system you are trying to connect to.

Another source of errors can occur if the computer time on either your SSRS computer or SAP computer drifts out of sync with your domain controller. If the time differential is large then SSO via Windows Integrated (Kerberos) authentication can fail even though Basic authentication may still work.

If Kerberos authentication is working but you are receiving errors when connecting to your SAP BW server then specify a client number and language in the connection editor.

Using HTTP Compression

The .NET Data Provider 1.1 for SAP NetWeaver Business Intelligence uses HTTP compression by default. For debugging purposes, it can be useful to turn off HTTP compression. To do this, add Transport Compression=None  to the connection string. This is shown in the following figure.

Figure 35   Disabling HTTP compression in the connection string

After HTTP compression is turned off, it is then possible 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. The following figure shows a sample output of a network packet trace.

Figure 36   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 37.

Figure 37   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.

Timeouts

When the connection is working and you define a query in the SSRS query designer, you may get a timeout error such as that shown in Figure 38.

Figure 38   Timeout 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 timeout value parameter in the SAP profile:

icm/keep_alive_timeout=600   

 

In this example, 600 is the number of seconds for the timeout (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

The following figure shows how to select an existing query through transaction RSRT/RSRT2 in the SAP GUI.

Figure 39   Selecting an existing SAP BI query in the SAP BI Query Monitor: Transaction 'RSRT'

The following figure shows the results of executing an existing SAP BI query.

Figure 40   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.

 

 

 

Figure 41   Copying the MDX statement from the SSRS query builder

 

Start the MDX Testeditor via transaction mdxtest and select the appropriate catalog and cube in the MDX Testeditor. You can now run the MDX statement against it.

 

 

Figure 42   Running the MDX statement in the SAP BI MDX Testeditor

SAP BI Query Variables and Default Values

You may 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 43 shows the dialog box for defining SAP BI query variables.

Figure 43   SAP BI query variable definition

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

 

Figure 44   Using a mandatory SAP BI query variable without a default value

 

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. The following figure shows a connection string that specifies an initial catalog.

Figure 45   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.

Figure 46   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 trace functionality available via the RSRTRACE transaction. By using this feature, you can collect traces on a user basis.

To use RSRTRACE:

1.    Activate the trace for the relevant user (see Figure 47).

 

Figure 47   Activating a trace for user SAP_PERF

 

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

 

Figure 48   Viewing the trace

 

Select the callup. You will see the associated XML/A request (Figure 49).

 

Figure 49   Viewing XML/A requests in logged callups

 

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

 

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

 

An important class for debugging is CL_RSR_MDX_COMMAND. Set the breakpoint in the PARSE method of this class. The following figure shows you how to do this.

 

Figure 51   Setting a breakpoint in the CL_RSR_MDX_COMMAND class

 

Find the MDX statement that was sent by SSRS (Figure 52).

 

Figure 52   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. The following figure shows the CL_RSRS_MDX_SOAP_HANDLER class (which includes the Discover and Execute methods) in the ABAP debugger.

 

Figure 53   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’ (see Figure 54).

 

Figure 54   Calling program RRT_MDX_SCHEMA: Transaction 'SE38'

 

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

 

Figure 55   Selecting an XML schema

After you select an XML schema, it opens for viewing or editing as shown in the following example.

 

Figure 56   Maintaining the selected XML schema

Logon Usage

The Microsoft .NET Data Provider 1.1 for SAP NetWeaver Business Intelligence can open multiple connections to your SAP NetWeaver BI server especially when using the query designer to create queries. To view the logon usage you can use transaction 'AL08' as shown in the figure below.

 

Figure 57   Currently Active Users

Additional Issues

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

The metadata tree view displays an error node with the 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, 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 property ReturnCellProperties allows the format of the cell data to be specified by using the CELL PROPERTIES MDX 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 may 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 may be resolved in a future service pack. In the meantime, always reference at least one measure in your first axis.

SAP Transaction Summary

This section summarizes the SAP transactions used in the previous troubleshooting sections.

 

SAP Transaction	Description
SICF	Internet Connection Framework administration. Setup and configuration of services available via HTTP, HTTPS, and SMTP.
SE37	ABAP Function Builder and debugger.
SM50	Overview of currently running processes.
RSRT	SAP BI Query Monitor.
MDXTEST	MDX Testeditor for executing MDX queries directly within SAP GUI.
RSRTRACE	Trace tool for logging calls to SAP BI.
SE38	ABAP Program Editor.
AL08	View users that are currently logged into the SAP server.

 

Conclusion

SQL Server 2008 Reporting Services empowers report authors to create a rich reporting experience for their SAP Netweaver BI environment. This paper has provided an overview of using the SSRS ‘SAP Netweaver BI’ data provider and related query designer. For more information on SQL Server Reporting Services and related topics, please see the Additional Resources section.

 

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

·         Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?

·         Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

This feedback will help us improve the quality of white papers we release.

Send feedback.

Additional Resources

SQL Server Reporting Services home page:

https://msdn.microsoft.com/en-us/sqlserver/default.aspx

 

SQL Server 2008 Reporting Services on Microsoft TechNet:

https://technet.microsoft.com/en-us/library/ms166352.aspx

 

SQL Server 2005 Reporting Services on Microsoft TechNet:

https://technet.microsoft.com/en-us/sqlserver/bb331776.aspx

 

SQL Server Reporting Services Forum: https://forums.microsoft.com/Forums/ShowForum.aspx?ForumID=82&SiteID=1

SQL Server 2008 Reporting Services Books Online: https://msdn.microsoft.com/en-us/library/ms159106(SQL.100).aspx

XML/A Specification: http://www.xmla.org

XML for Analysis information:http://www.xmlforanalysis.com/

SAP Business Information Warehouse: http://help.sap.com/saphelp_nw04/helpdata/en/e3/e60138fede083de10000009b38f8cf/frameset.htm 

ODBO flattening algorithm: https://msdn2.microsoft.com/en-us/library/ms709733.aspx