Offline access to BW data is the best way to meet an information access requirement in certain situations. Perhaps you want to provide your customers with information about their sales, but you do not want the security and performance headaches sometimes associated with direct access to the data warehouse. Or maybe your sales reps need access to customer information throughout the week but are only connected to the system on Monday morning.

In circumstances like these, BW 3.x and its Reporting Agent are the way to go.¹ You can create “cockpits” of information in BW 3.x and deliver them via the Web. Offline Web cockpits, like their online counterparts, collect related information (underlying queries) about a specific business process or subject and make it accessible via simple Web navigation (buttons, tabs, drop-down boxes). The difference is that the offline version needs minimal system resources, but as a trade-off, it provides limited (but very effective) navigation.

I'll show you how to use BW to present a complex, integrated Web cockpit to
someone who is not connected to the BW server. You will also learn the benefits of advanced offline access, including:

• Decreased server load by saving data or generating output just once
• Quicker access to reports for users
• Having reports available to people not connected to the BW server

Step-by-Step Advanced Offline Reporting

To give this article a real-world feel, I'll use a common business scenario: Your company, Corny Chips, Inc., uses BW to generate sales reports for all levels in your organization. Users of sales information include the CEO, the sales managers, and all your roaming chip peddlers. This situation is ripe for improvement, as system response times are slow due to long-running reports executed multiple times. Access for your traveling users is non-existent. To further complicate the problem, access to sales information is strictly controlled and related to the employee's level within the company.

Let's use BW's WebDAV and precalculated Web pages to solve your problem and put a fresh chip in your bag. You can create a sophisticated offline BW reporting system in six steps:

1. Create a Web cockpit for online and offline data access.
2. Create a control query to facilitate offline navigation.
3. Use BW's Reporting Agent to schedule the creation of the WebDAV files.
4. Access the precalculated Web cockpit online.
5. Download the reports to your mobile desktop.
6. Access the Web cockpit offline.

Step 1. Create a Web Cockpit

Web cockpits are complex Web pages with reports and graphics that provide a central access point to information — e.g., a sales cockpit. Cockpits often have design parameters that allow all their components to change focus simultaneously. With a single click, all the reports and graphs can shift from the shoes division to the handbag division, for example. Like other BW Web templates, cockpits are created using BW's Web Application Designer (menu path Start> Programs>Business Explorer> Web Application Designer), an easy-to-use toolset that creates great-looking Web applications.

My example (Figure 1) is a simple sales cockpit with filter boxes on Sales Organization and Calendar Month. This cockpit contains just one query and one chart for ease of display, but it could be an elaborate cockpit of multiple queries from multiple InfoCubes and related charts and graphs.

You can include standard features like tickers and charts, but you do not want to miss one special feature: a drop-down filter box. This “Web item” combines with a control query (step 2) to allow your offline cockpit to navigate. In addition, robust queries, including the one that feeds this cockpit (Figure 2), normally use variables. Variables are just as important for queries providing data to Web pages. In this case, you have a variable for distribution channel that can be entered when the Web page executes.

Figure 1

Basic sales cockpit

Figure 2

Main query design view

Step 2. Create a Control Query

As the name suggests, a control query controls and limits the output of all the queries on the Web cockpit. The control query is executed before the desired cockpit is accessed. In my example, the control query returns a limited set of sales organizations and months (Figure 3). The output from this control query is then used to limit the amount of information in the cockpit, which will be available offline or via “stored data” access, as I'll discuss later. Each combination of sales organization and month causes the generation of a specific Web (HTML) page or Excel file. In addition to providing the means to limit information,² control queries provide special functions that help users navigate (using the drop-down boxes) while offline.

The requirements and options for your control query are:

• Variables to increase flexibility (although they are not necessary).
• The characteristics used in the main queries for the filter boxes: one in the rows and one in the columns.
• Just one key figure (to calculate the result totals — see below); more than one would be a waste of time, as you are only concerned with the characteristics output here.
• Totals rows (results) for both the columns and the rows. Although the control query calculates output for each combination of the characteristics in the rows and columns, one more run of the Web page is calculated, ignoring the controlled characteristics entirely. This happens only if the totals/columns/rows are on the control query. This is called the “all” data set, and it is like running the Web page without any control- query-based filters.
• Conditions like “top 10” add more meaning to the list of output
  characteristics.
• The source for the control query may differ from the queries feeding the Web page directly.

Figure 3

Control query to limit output

Step 3. Schedule Creation of WebDAV Files

Offline reporting involves the most advanced use of BW's Reporting Agent. This toolset was created in Release 2.0 for offline printing and creating exceptions as part of an “early warning” system. It now allows you to schedule your Web templates to execute in the middle of the night. This subsection of Reporting Agent provides the option to save the data set for use in rebuilding the Web page (HTML) upon user demand or to save the generated output, graphics and all. This is the goal of this article!

To create the Reporting Agent settings, execute transaction code RSA1 and click on the Reporting Agent button. Then select Web Templates from the right side. Right-click on your Web template in the list and select New Setting. You have a significant number of settings to consider, so I'll walk you through each tab on Reporting Agent's precalculated Web GUI.

Under the General tab, enter only the technical name and description of your agent. Under the Parameter tab, you need to make these decisions (Figure 4):

Figure 4

Reporting Agent user setting under the Parameter tab

What should the agent do for you? Should it precalculate just the data to allow faster access when the system creates the Web page, avoiding a trip to the ODS or star schema? Or better yet, should the system actually generate and store (for offline access) the completed output in HTML, Pocket IE, or Excel formats?

What languages should the calculated output be in? More languages mean more Web cockpit elements are generated in the case of pre-calculated HTML, and thus more resources used.

Which roles and individual users have access to download the cockpits to their PCs? Since the output is completed pages (HTML and Excel for my example) and not raw data, normal SAP authorizations do not apply. In this section of the GUI, the checkbox Precalculate User-Specifically could be dangerous in terms of resource consumption, but powerful for users. If this box is checked, the system runs the Web page as if each user were signed on, one at a time. This generates separate output for each user based on his or her personalized or authorization-filled variables and personalized Web navigation (drill-down) preferences. If you have a lot of users for this agent, you could be wallowing in this background job for a while.

Which control query do you use to assist in navigation and limit the data? If the control query itself has user-entered variables, then a variant must also be used to “enter” these variables as part of the Reporting Agent job (Figure 5). In this case, you created a variant Retail_2001 in Step 2. In BW 3.1 and later, it could be created in this GUI using the create icon in the control query section.

Figure 5

Reporting Agent Parameter tab settings for a control query

What additional variables will the Web page require during the overnight job? This Web variant section of the report allows entry of any variables that might be on the actual queries supporting the Web cockpit, as opposed to the control query variable (Figure 6). Behind the scenes, the user-entered variables that the Web page requires are supported via command URLs (Figure 7). This technical implementation is not important if the page is to be downloaded, but it is very important if the page is to be accessed from the server using data modes, as discussed later.

Figure 6

Web page level variable entry

Figure 7

Web page level variables and command URLs

Under the Preview tab, both the control query and the Web template itself can be displayed. Once you are satisfied, or at least not mystified, activate the agent for this example.

Wait, you need one more thing in this section! All Reporting Agent functions, including creation of output for offline access, require a scheduling package. The package incorporates one or more settings (agents), and it schedules them as part of a nightly, weekly, or monthly routine. Use the icon on the right of the screen to create a package. Then drag the agent under the package, right-click on it, and select Schedule.

Step 4. Access the Precalculated Web Pages While Online

Pretend that Reporting Agent has executed your Web cockpit overnight. You now want to access the cockpit while connected to BW (online). You can do so in one of five different data modes. You must specify the data mode you want using the command string “&data_mode = ???” as a suffix to the original Web cockpit's URL. Your Web page was calculated with an additional Web level variant, so the associated command URL must also be appended to the base URL.

To retrieve the precalculated HTML version of your Web page while
online, use the following URL: https://cdphl147.phl.sap.corp:1081/sap/bw/BEx?sap-language =EN&bsplanguage=EN&cmd= ldoc&TEMPLATE_ID=PRECALC_WEB&VAR_NAME_ 1=0DISCHAN&VAR_ OPERATOR_1=EQ&VAR_SIGN_1= I&VAR_VALUE_LOW_EXT_ 1=R1& data_mode=static. It uses the static data mode, which causes the Web cockpit to be generated for the user from completed HTML and image files using WebDAV technology. Figure 8 shows the resulting Web page.

Figure 8

Web cockpit generated using the static data mode

The other data access modes are:

new — Executes the underlying queries on the Web page at the time the page is requested with current data from the database server. This is the default mode, so you can leave the data mode command string off for this option.

stored — Executes the Web cockpit and generates the HTML at the time it is requested, based on the data set stored when the cockpit was precalculated overnight.

hybrid — Tries the stored mode first. If no data is found, it uses the new mode.

static-hybrid — Tries the static mode first. If no static files are found, then it uses the stored mode. If no data is found, it uses the new mode.

Using the static or static-hybrid data modes, the system accesses the stored pieces of the Web page to reconstruct it for you when you request it. The resulting performance improvement rates a high 10.

The output that supports the static HTML as well as Excel files is stored in special WebDAV directories on the BW server (Figure 9). You can access these directories via an icon in the top right corner of the Reporting Agent package section of the GUI.

Figure 9

WebDAV folders for static output

Step 5. Schedule the Download to Your Mobile Device

Offline reporting implies that the report is stored on your portable device and is not accessed via expensive and slow wireless means. Therefore, you need to transfer the generated images and HTML to your mobile device. You can do this with the Download Scheduler accessed from the Business Explorer program group on the Start bar of your PC (Figure 10). After you sign in using your BW user ID and password, the WebDAV directories you have access to are available for transfer to your local drive.

Figure 10

Download Scheduler basics

Step 6. Access the Cockpit Offline

You are almost to your goal: quick offline cockpits with focused navigation. To access the cockpit offline, you can use the downloaded directory's indexes. For Excel output, using these indexes and their hyperlinks is the only real way to “navigate” the completed Excel workbooks. For the offline Web cockpit, however, the index helps you find one of the generated HTML cockpit pages. Once you are in a cockpit, you can navigate using the filters in a manner similar to navigating through Web pages (Figure 11). In this case, you are accessing a different sales organization and month combination using the appropriate filter box.

From a technical perspective, navigating using the filter box while offline just moves you between the HTML pages that were downloaded to your PC without returning to the master index. But don't tell anyone this secret. The user will be happy that he or she is navigating BW reports without a connection to BW.

Figure 11

Offline cockpit downloaded to your PC

Ned Falk

Ned Falk is a senior education consultant at SAP. In prior positions, he implemented many ERP solutions, including SAP R/3. While at SAP, he initially focused on logistics. Now he focuses on SAP HANA, SAP BW (formerly SAP NetWeaver BW), SAP CRM, and the integration of SAP BW and SAP BusinessObjects tools. You can meet him in person when he teaches SAP HANA, SAP BW, or SAP CRM classes from the Atlanta SAP office, or in a virtual training class over the web. If you need an SAP education plan for SAP HANA, SAP BW, BusinessObjects, or SAP CRM, you may contact Ned via email.

You may contact the author at ned.falk@sap.com.

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