SAP BusinessObjects XI 3.1 Service Pack 03 provides several enhancements to your SAP BusinessObjects XI 3.1 platform. It is available through the SAP Service Marketplace (https://service.sap.com/swdc) in the Service Packs and Patches area. Service Pack 03 includes fixes up to XI 3.1 FixPack 1.10 and XI 3.1 FixPack 2.5.

In this article, I discuss the three most important functional enhancements for your SAP BusinessObjects XI 3.1 platform:

• Query stripping with SAP BusinessObjects Web Intelligence
• New server components for Xcelsius
• Changes to the use of personal data providers such as CSV files in combination with SAP BusinessObjects Web Intelligence

For some important items to consider before upgrading to Service Pack 03, refer to the sidebar “Upgrade Considerations for XI 3.1 Service Pack 03.” 

Query Stripping with SAP BusinessObjects Web Intelligence

Up to this point, most organizations implementing SAP BusinessObjects Web Intelligence on top of their SAP NetWeaver BW data warehouses had to make some compromises during the implementation phases. In addition, SAP BusinessObjects Web Intelligence defined data requests based on the elements used in the SAP BusinessObjects Web Intelligence query panel. This led to a situation in which you created a rather large query to provide your end users with a larger data set for analysis. The downside of such a wide query panel definition was decreased performance. To put it simply, SAP BusinessObjects Web Intelligence did not use the concept of free characteristics that BEx Analyzer does.

With Service Pack 03 for the XI 3.1 release, this situation has changed. You can decide now how SAP BusinessObjects Web Intelligence should behave. In the next couple of steps, I show you how to use this new option, called query stripping. In my example, I will use an OLAP universe based on a BEx query from SAP NetWeaver BW (Figure 1). I created a standard OLAP universe based on this BEx query with the Universe Designer (Figure 2).

Figure 1

BEx query

Figure 2

OLAP universe created with the Universe Designer

In the example, the underlying BEx query and universe contain 10 characteristics and four key figures. I’ll use SAP BusinessObjects Web Intelligence to create a new SAP BusinessObjects Web Intelligence query and a new report. Then I will show you the effect of the new query stripping feature.

In the first scenario, I am creating a new SAP BusinessObjects Web Intelligence report by dragging the following elements into the Web Intelligence query panel (Figure 3):

• Country
• Region
• Product Group
• Product
• Calendar Year/Month
• Product Costs
• Net Value

Figure 3

SAP BusinessObjects Web Intelligence query panel

By default, the query stripping functionality is not enabled. I execute my query and create a simple report by showing a table that summarizes the Product Costs and the Net Value per country (Figure 4).

Figure 4

SAP BusinessObjects Web Intelligence report

In the standard SAP BusinessObjects Web Intelligence situation, I just created a report that asks for data for a total of five characteristics and two key figures. SAP BusinessObjects Web Intelligence would then execute the complete query as it is defined in the query panel. For those who are familiar with MDX, Figure 5 shows the MDX statement for the report.

To use the new query stripping functionality, you need to enable this option in two places:

• In the SAP BusinessObjects Web Intelligence query
• In the report’s document properties

Navigate back to your SAP BusinessObjects Web Intelligence query and open its properties by using the option Edit Query and opening the Properties panel (Figure 6). In the query properties, activate the option Enable query stripping for the query panel.

Figure 6

Query properties

Now navigate back to your report with the Edit Report option and open the properties of the report. In addition to the property for the SAP BusinessObjects Web Intelligence query, you must activate the property for the report itself (Figure 7).

Figure 7

Document properties

Refresh your report. The most visible change to the report is that the characteristics that are not used in the report are highlighted in bold in the Data panel (Figure 8). The bolding helps the end user visually understand that the bolded items could result in extra data in the data retrieval request. On the data retrieval side, you can view the MDX statement and clearly see the change (Figure 9).

Figure 8

Unused characteristics appear in bold font

Next, add the characteristic Region to your report (Figure 10). As you can see, the report now shows #REFRESH for the L01 Region column. With the option to enable the query stripping functionality, you are automatically enabling the user to decide when to refresh — and in that way execute — the report. For example, you could add the characteristics Product and Product Group to your report without having to refresh the report (Figure 11).

Figure 10

The report with the Region characteristic added

Figure 11

The report with the characteristics Product and Product Group added

After finishing all the changes, the user clicks the Refresh Data button. SAP BusinessObjects Web Intelligence uses the report definition to retrieve the data from SAP NetWeaver BW based on the report design. The report is no longer based on just what is in the query panel.

New Server Components for Xcelsius

Many companies use Xcelsius with Query as a Web Service (QaaWS) to create dashboards with real-time BI data. QaaWS allows you to create a Web service on top of a universe. This enables you to provide Xcelsius with the information based on the universe as a semantic layer.

One common request from companies was the need for a better performance and a higher scalability of this approach. As a result, SAP introduced new Xcelsius server components. These components improve scalability and performance by introducing query caching for those dashboards in which you are using QaaWS as the data source for Xcelsius.

Figure 12 shows how the new components improve scalability. Web service requests are now routed to the new Xcelsius back end, which contains the Xcelsius processing server and cache server. The Xcelsius processing server provided a thin processing layer between the data source and the dashboard front end. It replaces the SAP BusinessObjects Web Intelligence server for SQL query execution, although SQL generation remains in the SAP BusinessObjects Web Intelligence processing server. The Xcelsius cache server caches query results as set in the security profile.

Figure 12

Xcelsius server components

Up to Service Pack 03, User A would follow this workflow:

1. Look at an Xcelsius dashboard based on QaaWS as a data source

1. Look at revenue by calendar year in the form of a bar chart
2. Filter the information for the US
3. Filter the information for Germany
4. Navigate back to the filter for the US

Before Service Pack 03, steps 3, 4, and 5 would result in data retrieval requests, even though step 5 is basically a repetition. With Service Pack 03, the Xcelsius server components cache the QaaWS information based on security profiles. In my example, step 5 would no longer be a data retrieval request against the underlying source system. Instead, this step would be fulfilled by the query caching from the new Xcelsius server components. To use these new server components, you need to perform a set of manual steps after installing Service Pack 03.

Note
The new Xcelsius server components that are part of Service Pack 03 for the XI 3.1 release only work for QaaWS as a data source with the limitation that only universes based on relational data sources are supported.

Step 1. Xcelsius Server Configuration

If you completed the Service Pack 03 full install you can skip this step, as this is only required if you are using the Service Pack 03 patch installation. On each server that is going to host an Xcelsius server component, you need to run the following command line to activate the new server:

Program Files Business ObjectsBusinessObjects Enterprise 12.0win32_x86scriptsAddNote.bat –name <NODENAME> -update –cms <CMS NAME>:<CMS PORT> -user <USER> -password <PASSWORD>

This command line is based on the default installation path on a Windows system. You need to replace the placeholders with the following values as shown in Table 1.  

Table 1

Values for the placeholders in the command line

Step 2. Add the New Services to Your SAP BusinessObjects Enterprise Landscape

Log on to the Central Management Console (CMC) from your SAP BusinessObjects Enterprise system. Select Servers from the drop-down menu and follow menu path Manage > New > New Server. In the Create New Server screen, select Xcelsius for the Service Category and Xcelsius Cache Service for the service (Figure 13).

Figure 13

Select the Service Category and Service

Click the Next button at the bottom of the screen, and then click the Next button on the following screen. This brings you to the screen in which you select your SIA node from the Node drop-down menu (Figure 14). The system populates the Server Name field automatically. Click the Create button at the bottom of the screen. Then repeat the process, but this time select the Xcelsius Processing Service (Figure 13).

Figure 14

Select the SIA node

Step 3. Enable and Start the Server Components

After you add the two new Xcelsius services, navigate to the Servers area in the CMC. Click the enable and start icons, circled in Figure 15, to enable and start your new services.

Figure 15

List of servers in CMC

Then open the folder Program Files (x86)Business ObjectsTomcat55webappsdswsbobjeWEB-INF (assuming a default installation on Windows). Open the file web.xml and add the lines in Figure 16 to the end of the file.

Save your changes and restart your application server, which is the Tomcat server in a default installation. With the new services in place and the manual steps completed, make any changes to the actual Xcelsius file.

Xcelsius Server Settings in Service Pack 03

As part of the Xcelsius Services in your SAP BusinessObjects Enterprise landscape, you also have a couple of settings that you can use to influence the behavior. The first setting, Session Time-out, helps with caching of Web services. This setting is part of the definition of each Web service in the QaaWS too. You can find the Session Time-out field in the Advanced Parameters of the QaaWS tool (Figure 17).

Figure 17

Advanced parameters

The Session Time-out setting influences how long the Xcelsius cache server stores the cache. The default value is 60 seconds. The actual value you should use depends on how often your data is updated. It can vary from seconds for real-time data up to a full day for information updated less frequently.

The second set of settings is part of the properties for the Xcelsius cache server. You can find these settings in the CMC. Select the Servers category and double-click the Xcelsius cache server entry. Three of the key settings shown in Figure 18 are:

• Cache Files Directory: Specifies the cache files’ location
• Maximum Cache Size (KB): Specifies the cache size. The default value is 256000 KB (256 MB). If the cache is getting full, the system removes the oldest cache files first.
• Idle Connection Timeout (minutes): Specifies the timeout in minutes. The value you use should be slightly higher than the longest running query in the system to ensure the data is being retrieved.

Figure 18

Xcelsius cache server properties

For the Xcelsius processing server, you have a similar set of properties that you can use to define the settings for situations in which the data is not available in the cache. The important settings in Figure 19 include:

• Temporary Directory: Specifies the location of the temporary files
• Maximum Concurrent Jobs: Specifies the maximum number of concurrent jobs. If you use the default value, you can have up to 500 concurrent jobs
• Maximum Lifetime Jobs Per Child: The Xcelsius processing server creates a set of child processes that execute the request. This parameter allows you to specify the maximum number of jobs each child process will be able to process before the process is restarted. If you are using small data, you should be able to use the default value of 1000.
• Maximum Number of Prestarted Children: Specifies the number of pre-started child processes for the Xcelsius processing server. You can leave the default value because the system starts additional child processes based on the demand for viewing the data.
• Idle Connection Timeout: Specifies the timeout in minutes. The value you use should be slightly higher than the longest running query in the system.
• Idle Job Timeout: Here you can specify the timeout in minutes. The value being used should be slightly higher than the longest running query in the system to ensure the data is being retrieved.

Figure 19

Xcelsius processing server properties

The last couple of steps show how you can enable the new Xcelsius services as part of your SAP BusinessObjects XI 3.1 landscape and how you can influence some of the settings.

Personal Data Providers

The Web Intelligence Rich Client allows you to create reports on personal data providers such as a Microsoft Excel spreadsheet or a simple text file. In the past, it wasn’t possible for a colleague to create such a report, share it, and make it refreshable. All the other users did not have access to the local file on his computer, which was the source for the personal data provider. Now with Service Pack 03 of the XI 3.1 release, you are able to store those files on your SAP BusinessObjects server and allow all users to share and refresh them.

To enable this functionality on your SAP BusinessObjects Enterprise system, follow the next steps. In the registry branch HKEY_LOCAL_MACHINESOFTWARE Business ObjectsSuite 12.0defaultWebIntelligenceCalculatorPDP, are subfolders XLS and TXT for the two different personal data providers. In the following steps, I assume a spreadsheet is the source.

• In the registry branch HKEY_LOCAL_MACHINESOFTWAREBusiness ObjectsSuite 12.0defaultWebIntelligenceCalculatorPDPXLS, set the value of the key RefreshOnServer to YES or ENABLE. If you would like to disable this option set the value to NO or DISABLE.
• In the registry branch HKEY_LOCAL_MACHINESOFTWARE Business ObjectsSuite 12.0defaultWebIntelligenceCalculatorPDP, use the key Server_Path to set the path to the shared files

In addition to these registry settings, there is a set of prerequisites:

• You need to store the files that you use for the personal data provider on the SAP BusinessObjects Enterprise server
• If you are using a clustered system, the file needs to be stored on each node
• The SIA of your SAP BusinessObjects Enterprise system should be managed by a domain account so that access to the file is guaranteed

After you complete these configuration steps, you can create an SAP BusinessObjects Web Intelligence document with a Web Intelligence Rich Client based on a Microsoft Excel file, set it to Refreshable, and save the SAP BusinessObjects Web Intelligence report to your SAP BusinessObjects Enterprise system (Figure 20).

Figure 20

SAP BusinessObjects Web Intelligence Rich Client report

You can then copy the spreadsheet file to the location you specified in the registry, which allows you to view and refresh the report in InfoView (Figure 21).

Figure 21

View the SAP BusinessObjects Web Intelligence report in an InfoView

With this new option, you can now share reports based on or using personal data providers with all your users in a more secure manner and make those reports shareable for all users.

Ingo Hilgefort

Ingo Hilgefort started his career in 1999 with Seagate Software/Crystal Decisions as a trainer and consultant. He moved to Walldorf for Crystal Decisions at the end of 2000, and worked with the SAP NetWeaver BW development team integrating Crystal Reports with SAP NetWeaver BW. He then relocated to Vancouver in 2004, and worked as a product manager/program manager (in engineering) on the integration of BusinessObjects products with SAP products. Ingo's focus is now on the integration of the SAP BusinessObjects BI suite with  SAP landscapes, such as SAP BW and SAP BW on SAP HANA, focusing on end-to-end integration scenarios. In addition to his experience as a product manager and in his engineering roles, Ingo has been involved in architecting and delivering deployments of SAP BusinessObjects software in combination with SAP software for a number of global customers, and has been recognized by the SAP Community as an SAP Mentor for SAP BusinessObjects- and SAP integration-related topics. Currently, Ingo is the Vice President of Product Management and Product Strategy at Visual BI Solutions, working on extensions to SAP’s product offering such as SAP BusinessObjects Design Studio and SAP Lumira. You may follow him on Twitter at @ihilgefort.

You may contact the author at Ingo@visualbi.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.