Best Practices for the SAP BW Connector

When you work with SAP BW data in Spotfire, there are certain restrictions in SAP BW that you should keep in mind. The following information describes some common SAP BW limitations and suggestions for how you can work around them.

Do not use multiple hierarchies or columns based on the same characteristic

SAP BW does not support mixing hierarchies or columns that are based on the same characteristic. If more than one hierarchy or column is based on the same characteristic in your data source, make sure you only include one of those columns or hierarchies.

To avoid including more than one hierarchy or column from the same SAP BW characteristic:

1. In the Data Selection in Connection dialog, select the data you want to analyze.

2. In the Data selection in connection list, expand each characteristic to inspect associated hierarchies and columns.

3. If any characteristic contains more than one hierarchy or column, make sure you only include one, by selecting the redundant hierarchies or columns and then clicking <Remove.

Cell limit: Maximum 1 000 000 cells

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.

Note: For this preference setting to have any effect, you must have installed the BAPI driver (SAP NetWeaver RFC SDK) on your computer.

Working within the default SAP BW cell limit

If you are running the SAP BW connector with the ODBO driver (SAP BW OLE DB for OLAP provider), or if you have not enabled the preference setting for working with SAP BW data that exceeds 1M cells, consider the following recommendations for working within the restrictions of the SAP BW 1M cell limit.

To avoid exceeding the SAP BW cell limit for in-database data:

• Use filters to limit the data in your analysis.

• Include a key figure (measure) when you set up a visualization.
  Note: If a query to an SAP BW data source includes no key figure (measure), more data than intended might be returned from the data source.

To avoid exceeding the cell limit when you import SAP BW data:

• In the Data Selection in Connection dialog, reduce your data selection as much as possible. Only include columns and hierarchies that are relevant to your analysis. Define BEx variables and limit characteristics to only include data for values of interest.

• Click Estimate Import Size to evaluate whether the selected data can be imported.

• Include a key figure (measure) in the data selection.
  Note: if a query to an SAP BW data source includes no key figure (measure), more data than intended might be returned from the data source.  

If you want to analyze a very large amount of SAP BW data, which exceeds the limit of 1M cells, you can import it piece by piece into Spotfire. To import data in pieces, limit the data selection to only include data for a portion of the values in a characteristic or variable. Then, in your analysis, use Insert Rows to add more pieces of the data to the data table containing the imported data.

To import SAP BW data which exceeds 1M cells in pieces:

1. In the Data Selection in Connection dialog, select the data you want to import from the data source.

2. Identify a characteristic or variable that contains a suitable number of categorical values to limit the data to.
   Example: If you have a data source with sales data for the year 2016, the characteristic 'Calendar day' contains too many values for it to be practical to piece the data by. The characteristic 'Quarter' is more suitable, because it contains only 4 values. By limiting the data by 'Quarter', you can import the data in 4 pieces, each containing only the data for the specified quarter of the year.

3. Click the characteristic or BEx variable and limit the data to only one value.
   Note: You can limit the data to more than one value. For example, you could make an import of the data for 'Q1-Q2' first, and then add the rows for 'Q3-Q4' in a second import.

4. Click OK to close the Data Selection in Connection dialog.

5. In the Add data to analysis flyout, make sure that the data table that is added from your connection is configured for Import, for in-memory analysis. Then click OK.

6. On the authoring bar, click Data canvas .

7. On the data canvas, select the data table with your SAP BW data, and in the source view, click the plus icon between the nodes where you want to add rows, and select Add rows.
   

8. In the Files and data flyout, on the Recommended tab, click to open your SAP BW connection again. It is listed under Data connections in current analysis.

9. In the Data Selection in Connection dialog, select the same data you selected in step 1.

10. Click the characteristic or BEx variable that you used to limit the data import in step 3, and make a new selection to limit the data.

11. Click OK and, in the Add data to analysis flyout, make sure the data you are adding is configured for Import. Click OK.

12. Repeat steps 6-11 until you have imported all the data you want to analyze.

Navigational attributes

To use navigational attributes in Spotfire, the attributes must be exposed in a BEx query.

Do not limit BEx variable characteristics

If you are connecting to an SAP BW BEx query with variables, avoid limiting characteristics in the Limit Data dialog. 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.