Working with Data from SAP BW

If you have access to data in an SAP BW system, you can use the SAP BW connector in Spotfire to connect to your OLAP cube.

When you configure a connection to an SAP BW cube, you can choose whether to analyze data in-database or to import it into your analysis. Note that you need to install a driver on your computer to get access to the SAP BW connector. See the system requirements at http://spotfi.re/sr/connectors to find the correct driver.

Note: Some MDX restrictions apply to SAP BW. Please refer to SAP Note 820925 - MDX - Restrictions for more information.

InfoProviders are the reportable entities in SAP BW, for example, InfoCubes and MultiProviders.

InfoCubes are multidimensional models in SAP BW. In Spotfire, the InfoCubes become cubes (that is, data tables). Standard InfoCubes are self-contained data sets for particular business subjects or processes, such as sales, lost deals, and plans. They consist of dimensions, characteristics, key figures and navigational attributes.

Because each InfoCube normally only supports an individual business subject or process, there is a need to create reports where data from different processes are joined. MultiProviders are used for this purpose. They exist as a logical definition only, so they do not physically store any data.

MultiProviders are also frequently used in cases where a single InfoCube would become very large due to high data volume. MultiProviders also become cubes (that is, data tables) in Spotfire.

SAP BW users can create Business Explorer (BEx) queries in the BEx Query Designer. BEx queries are intended to provide the end users with queries for reporting and analysis in SAP BW. In Spotfire each BEx query becomes a cube.

Note: For a BEx query to be visible in Spotfire, the query has to be configured to allow external access by OLE DB for OLAP. This is done by selecting the Allow External Access to this Query by OLE DB for OLAP check box in the Extended tab in the Query Properties for the query in the BEx Query Designer application.

When you select a SAP BW BEx query in Spotfire, you may have to specify desired values for predefined variables. The specified values will be used to limit the selected data. Once the BEx variables have been specified, you cannot change them.

An SAP message server can be used for load distribution and communication between individual application servers in an SAP BW system. When you create an SAP BW connection in Spotfire, you must specify whether you will connect directly to an application server, or if you want to connect via a message server.

To connect to an SAP BW data source via a message server, select Connection type > Group/Server selection in the SAP BW connection dialog. Different information is required to create a connection to an SAP BW data source via a message server, compared to when you connect directly to the application server.

Variables act as parameters to a Business Explorer (BEx) query. They are filled with values and processed at query runtime.

For example, you might have a BEx query that reports sales for the year 2014. When new data for 2015 becomes available, you do not want to change the year to 2015, but rather want the end user of the query to select which year the query should be executed for.

In that case, you modify (parameterize) the query so that it uses a variable for the year. Then, the end user can specify the year of interest by selecting a value for that variable when the Spotfire analysis is opened.

Note: When working with BEx queries with variables, avoid limiting characteristics using the Limit Data dialog. Data has already been limited by the selections in the BEx variables, so, generally, further limiting is not necessary. Because of restrictions in SAP BW, if you both limit a characteristic and define a BEx variable that filters out values for that characteristic, you might get unpredictable results when loading and analyzing data.

Variables are globally available, so that once they are defined, they are available for use in any BEx query. However, not all variables are available to Spotfire. The underlying SAP BW driver does not return any Text variables. In addition, only variables that have the processing types Manual Entry or Default Value are returned by the driver.

Note: Hierarchy variables are not supported by Spotfire. Hierarchy node variables, however, are supported.

Note: In data connections to some versions of SAP BW/4HANA, you might encounter an issue where you cannot enter values for certain variables. This can be due to an issue with the BAPI function BAPI_MDPROVIDER_GET_VARIABLES.

For example, typical key figures are sales revenue, or, quantity sold. That is, key figures are the measures of the cube and the are found under 'Key Figures' in the Spotfire Data in analysis flyout.

A characteristic usually denotes a business entity that is being evaluated or measured by a key figure. Characteristics can be either attributes or hierarchies. Attributes are further broken down into display attributes and navigation attributes.

SAP BW hierarchies are represented as hierarchies in Spotfire. Navigation attributes are represented as columns in Spotfire. Display attributes are currently not available in Spotfire.

Navigation attributes can be used in BEx queries for drilling and filtering and are thus visible to Spotfire (in the context of the BEx query).

Note: Navigation attributes appear as display attributes in the context of an InfoCube and are therefore not visible in Spotfire in that context. If you need to access these attributes in Spotfire, the workaround is to create a BEx query based on the InfoCube, and expose the navigation attributes in the BEx query.

For characteristics and hierarchies from SAP BW, you can decide how to display the characteristic in Spotfire. A characteristic in SAP BW can have many types of metadata, such as attributes and technical names. With the Value presentation settings in the Data Selection in Connection dialog, you can choose to use a combination of technical names, a certain attribute, or you can define a custom value presentation.

Tip: If you want to use all the metadata for a characteristic or hierarchy in Spotfire, you can do this with a custom value presentation. Then, if you want the metadata in separate columns, you can split the resulting column. Note that splitting columns is only possible when you import data.

However, not all characteristics belong to a dimension, so Spotfire does not show these dimensions. Instead, all characteristics are found under the 'Characteristics' dimension in Spotfire.

By default, there is a limit to how many cells of data can be returned in a single query from Spotfire to SAP BW. The MDX interface of SAP BW can generate a result set containing a maximum of 1M cells.

There is a preference setting, that a Spotfire administrator can enable, to make Spotfire use a special set of functions to query SAP BW data sources. Enabling the preference UseNativeSapFunctionsAllowingMoreThanOneMillionCells will allow you to work with larger amounts of data from SAP BW. For more information, see SAP Note 1232751.

When it comes to import of data from a cube, the most important thing to think about is to limit the data to import as much as possible by making sensible selections. This is because import of large amounts of data may not work.

Note: It is not possible to import multiple characteristics that belong to the same dimension.