Recommendations for connecting Spotfire to SAP BW

Purpose

Spotfire supports a few ways to connect to SAP BW in an optimal and performant manner.  Each option has its advantages and disadvantages. The purpose of this document is to outline these options.

Background

SAP BW/4HANA and SAP BW on SAP HANA are often very fast and well suited for interactive querying.  However, it's important to note that SAP BW was made for Business Objects reporting.  When importing data into Spotfire, Spotfire has to normalize SAP BW data that comes from a cube. This requires flattening of the BEx query results into Spotfire's in-memory engine, which takes additional resources and time to load as compared to a relational database like SAP HANA. That said, Spotfire does absolutely support visualizations using data from SAP BW. Please see our recommendations below for the best way to handle this.

Business Requirements

The following are common business requirements:

• Preference towards in-memory analytics for performance and analytic capabilities reasons.
• Access to row level data.
• Access to calculated columns and Spotfire custom expressions.

Recommendations

Recommendation 1

To best support the in-memory data source requirements, it's recommended that your Business team works closely together with your Spotfire center of excellence (COE) and support team. The COE team would be able to set up Automation Services to load the required data in batches during off-hours.

This solution would include one Spotfire dxp that is used solely for the purpose of extracting SAP BW data.  It would run on multiple connections where each connection imports a single data table.  Then the data tables would be added to each other using Spotfire's Add Rows/Union operation.  And finally, the Automation Services job would export the results into an sbdf file in the Spotfire library. Your analysis files are then built based on that sbdf file.  

The advantages of this option are that the data would then be available in-memory.

The disadvantages of this option are that it does take some time to set up and configure Automation Services to work correctly.

Recommendation 2

To best support calculated columns, it's recommended that the Business team works with the Spotfire COE team to configure BEx queries that satisfy the Business team's requirements directly in SAP BW.  Instead of reusing common BEx queries that are used across the organization, the Business team would provide more detailed requirements to the COE team, so that a specific BEx query could be created to meet these requirements.  Running calculations on already aggregated data has risks, and so it is best to satisfy these aggregation requirements with a specific BEx query that does the calculation directly in SAP BW. 

The advantages of this option is that the BEx query would be very performant and eliminate the need to create calculated columns, which behind the scenes creates the cross joins needed when visualizing data from a cube.

The disadvantages of this option is that the BEx query does need to be predefined ahead of time, therefore reducing the ability to dynamically analyze any previously unidentified anomalies in the data quickly.

Recommendation 3

Use in-database data tables and interactive push-down MDX querying for connecting Spotfire to SAP BW whenever you do not need calculated columns. This option may get the Business team very close to the desired performance and data requirements when combined with Recommendation 2 where the SAP BW BEx query is optimized to do the desired calculations.  With SAP BW/4HANA and SAP BW on HANA this solution would be very fast and performant.

General Recommendations when building new Analysis Files that connect SAP BW with Spotfire

For each new use case or analysis file you are about to create: 

1. Determine whether the dashboard can be based on already extracted data, or slight modifications of existing data extractions keeping in mind the risk of breaking existing dashboards.
2. Review the BEx query to see if additional calculations need to be added. If so, work with the Spotfire COE team to implement in SAP BW BEx.
3. Try to implement the dashboard using the optimized BEx query and in-database data tables and interactive push-down MDX querying. 
4. If external data is not possible, work with the COE team to request that the data be extracted using another Automation Services job, keeping in mind the setup and maintenance costs associated with that approach.

General Recommendations for connecting Spotfire to SAP BW using in-database data tables and interactive push-down MDX querying.

• Start your analysis with using aggregated visualizations (like a bar chart) rather than a table plot. Use the 'Create details plot' feaure from within the aggregated visualization to then drill down into the row level data where you see the table view. 
• Try to keep the number of visualizations down to about 4 visualizations per page. 
• Try to answer specific questions on each page by focusing on one analytic area/use case.
• Don't add more columns to each table plot than what you need to answer your current page's questions .
• Use BEx variables connected to Spotfire prompts to reduce the amount of data being loaded.
• Limit the number of characteristics/hierarchies.  Each additional characteristic increases the  number of crossjoins.

Additional options to explore:

• Explore use of the THEN keyword for post-aggregations: (documentation)
• Consider using SAP HANA as an alternative solution for SAP BW.