Learn about enhancements that come with the release of SAP BusinessObjects Analysis, edition for Microsoft Office, versions 2.2 and 2.3.
Key Concept
SAP BusinessObjects Analysis, edition for Microsoft Office, is the successor to Business Explorer (BEx) Analyzer. It also now combines the enterprise performance management (EPM) plug-in for SAP BusinessObjects Planning and Consolidation (BPC)-based deployments. It is part of the overall SAP BusinessObjects BI toolset and is the only tool that enables developers to provide users with an experience inside the Microsoft Office environment.
This is a short summary of the new capabilities of BusinessObjects Analysis, edition for Microsoft Office, (from here on simply referred to as Analysis Office) versions 2.2 and 2.3, offering a quick introduction to the new functionalities offered. In addition, I review the new product roadmap to help you better understand what you can expect in the next releases for Analysis Office. I also provide some step-by-step instructions for how you can best use these new features.
An Overview of SAP BusinessObjects Analysis, Edition for Microsoft Office
Analysis Office is SAP’s flagship product for integrating BI capabilities into the Microsoft Office environment. It provides access to information stored in SAP BW and SAP HANA to your users who work in the Microsoft Office environment.
Figure 1 shows how SAP presents Analysis Office to their customers. Notice that Analysis Office is positioned as an environment for the Microsoft Office suite.
Figure 1
Analysis Office overview
Figure 2 shows one part of the roadmap for Analysis Office (which I discuss in more detail later). This figure illustrates how SAP plans to have Analysis Office consolidate the different plug-ins that are available today—enterprise performance management (EPM) and Live Office—into a single product. As a result, Analysis Office will provide access to all kinds of data sources.
Figure 2
Analysis Office, EPM, and Live Office
Analysis Office is a self-service environment, which means that you do not need your IT department to create workbooks for your users. This self-service environment allows IT to focus on setting up the data source, such as SAP BW and SAP HANA, leaving your users free to use Analysis Office to connect to the sources and create their own workbooks themselves.
Updates for Analysis Office Releases 2.2 and 2.3
With the 2.2 and 2.3 releases for Analysis Office, SAP has added some major enhancements, as well as a set of smaller additions that are very helpful to your users for analyzing the information from your SAP landscape. The following is a list of enhancements in Analysis Office that are made available to you with the 2.2 and 2.3 releases.
Calculated and Restricted Measures
Users now can create new calculated measures with SAP BW and SAP HANA as the data sources. They also can create new restricted measures in combination with SAP HANA.
Users no longer have to open the underlying source, for example, a BEx Query, and edit the query so that a new calculated measure is available for reporting. Instead, they can now add such calculations directly in Analysis Office using the new formula editor feature (Figure 3).
Figure 3
Calculated measures using the formula editor
Enhanced Table Design Options
With the new Table Design options, you have the option to add new lines and columns to your workbook in Analysis Office. In these new lines or columns, you can make use of standard Microsoft Excel formulas, and you can also define your own captions for these new calculations. The used formulas do not lose the overall context of your workbook. Therefore, a formula that might look like a formula using cell references in Microsoft Excel is in reality a formula that is based on the semantic of your workbook, and it adapts to the navigation status.
Figure 4 shows the context menu for the Net Value measure with the option to add new columns to the overall workbook.
Figure 4
Some new table design features
Integration with the Central System Landscape
Analysis Office can now leverage the new option of the SAP GUI to integrate with a central system landscape where you stored all the necessary configuration files for your server connections on a central server. In such a configuration, Analysis Office can use the configuration to provide your users with the centrally located list of connections.
Search Enhancements
Previously, if you had a large list of products or a large list of customers and you were looking for specific entries, you were only able to search for the members of a dimension based on the key or text value of the members. In the latest release of Analysis Office, however, you can also search for those members based on dimension attributes and their values. For example, you may search for your customers based on an available postal code in a form of an attribute, or search for your product based on attributes like product group or product category (Figure 5).
Figure 5
More and advanced member-search options
SAP HANA Data Access Enhancements
When it comes to accessing SAP HANA data in Analysis Office, there are several enhancements with releases 2.2 and 2.3. In addition to SAP HANA analytical and calculation views, it is now possible to access SAP HANA attribute views directly when the user requires the actual master data. You can now also create a filter by measure—for example, to restrict your revenue to the top five customers—directly on top of the SAP HANA data in Analysis Office. With release 2.3 it is now possible to open the SAP HANA View Editor directly from within Analysis Office by using the Launch Query Designer menu option.
Table Design Rules
Starting with release 2.3, Analysis Office offers a Table Design Rule Editor that allows users to set up different types of rules—for example, the ability to change the format of a cell, the text of a header cell, or the height or width of a column based on specific conditions.
Ability to Rename Dimensions and Measures
Analysis Office has always enhanced the abilities to change the settings and configuration in the underlying data source directly within Analysis Office so that, for example, your user does not have to open the BEx Query Designer to change the scaling factor of a measure. With the latest update of Analysis Office, several additional settings can be changed from within Analysis Office, including the option to change the text for dimensions and measures.
Enhanced Support for Report-Report-Interface
Analysis Office is now able to act as a valid receiver, which finally closes the loop on the Report–Report–Interface and makes Analysis Office a valid sender as well as a valid receiver. As a valid sender, Analysis Office can call target reports (for example, a BEx Analyzer workbook), and as a valid receiver, an Analysis Office workbook can be the target when it’s called from a BEx Analyzer workbook.
SAP BusinessObjects BI Platform Integration
On the integration with the SAP BusinessObjects BI platform, Analysis Office now allows caching to be activated on the SAP BusinessObjects BI platform for the workbooks. Analysis Office also now supports the auditing functionality of the BI platform, providing you with valuable information about who is accessing the Analysis Office workbooks.
In the next section I walk you through some of these new capabilities.
A Step-by-Step Review of the New Features of Analysis Office
In the following steps, I show you how to use some of the new features of Analysis Office, such as Table Design Rules, the ability to change properties for dimensions and measures, how to add new rows to the crosstab, and the option to create new formulas and calculations.
In this example scenario, I use a BEx Query based on the SAP NetWeaver Demo Model (https://scn.sap.com/docs/DOC-8941) with the following details:
Rows:
- Product Group (0D_NW_PROD__0D_NW_PRDGP)
Columns:
- Net Value (0D_NW_NETV)
- Costs (0D_NW_COSTV)
Free characteristics:
- Country (0D_NW_CNTRY)
- Region (0D_NW_REGIO)
- Sold-to Party (0D_NW_SOLD)
- Product Category (0D_NW_PROD__0D_NW_PRDCT)
- Product (0D_NW_PROD)
- Cal. Year / Month (0CALMONTH)
1. Start SAP BusinessObjects Analysis, edition for Microsoft Excel, and navigate to the Analysis tab (Figure 6).
Figure 6
Go to the Analysis tab of Analysis Office
2. Select Insert Data Source > Select Data Source. Depending on your landscape, you are asked either to log on to your SAP BusinessObjects BI system or to log on to the data source directly.
3. After you’ve logged on, select the BEx Query.
4. In this example, there is a crosstab that shows the Product Group dimension with two measures, Costs and Net Value (Figure 7).
Figure 7
The crosstab for the Product Group dimension
5. Select the column header for the Net Value measure and right-click it to open the context menu. From the list of options, select Add Advanced Calculation. This action opens a New Calculation (formula editor) pop-up screen (Figure 8).
Figure 8
Edit the formula
6. The Insert Function button is used to leverage functions from different categories, such as Percentage Functions, Data Functions, or Mathematical Functions. The Insert Member button allows you to select measures. In this example, click Insert Member and select the Costs measure from the drop-down list of options (not shown).
7. Then click the Insert Function button and, from the drop-down list of options (not shown), select the Percentage Share function from the Percentage Functions category.
8. Click the Insert Member button again, but this time select the Net Value measure and enter Costs in % as the name for the calculation. Click the OK button to save your changes.
10. If you want to change the calculation, select the column header to open it for the calculation and then select the menu Calculations > Edit (Figure 9).
Figure 9
Menu calculations
11. Navigate to the Display option in the Analysis tab (Figure 10) and activate the Show Property View option.
Figure 10
Menu display
12. As part of the navigation panel on the right side, you now also see the PROPERTIES view and can change properties for dimensions and measures (Figure 11).
Figure 11
The new navigation panel with the PROPERTIES view added to it
13. Expand the list of Measures under the Columns section and select the newly created calculated measure (Costs in %).
14. In the PROPERTIES panel on the right, set Decimal Places to the value 0 (Figure 12).
Figure 12
Change the measure’s properties
15. Now select the Product Group dimension in the Rows panel. In the PROPERTIES panel that opens on the right, set the Display field for Members to Text and activate the Show Totals option (Figure 13).
Figure 13
Change the dimension’s properties
16. Now select the last member of the Product Group dimension (TV, in this example) in the crosstab, and right-click it to open the context menu (Figure 14). Select the menu New Cells > Row after Members of Product Group. This action inserts a new row (cell) under the TV member.
Figure 14
Open the dimension’s context menu
17. Navigate to the new cell for the Costs column and enter =B6/B3 as the formula. Here cell B6 represents the costs for the Laptops product group, and cell B3 represents the total costs for the Product Group dimension (Figure 15).
Figure 15
Enter the new Excel formulas
18. Next, navigate to the new cell for the Net Value column. Here you enter =C6/C3 as the formula, where cell C6 represents the Net Value for the Laptops product group and cell C3 represents the total Net Value for the Product Group dimension.
19. Enter % Share of Laptops into cell A10 as the row label for the newly added row (Figure 16).
Figure 16
Add the new calculation and label for the new row
Note
In these last two steps, I used Microsoft Excel formulas to calculate the percentage share value for the Product Group compared with the total of all Product Groups and added a new row to the crosstab. Even though the row is added using Microsoft Excel formulas, the formula is translated by Analysis Office into a context-aware calculation.
Next, I explain how to add new rows to the crosstab and the option to create new formulas and calculations.
1. In the navigation panel, select the Sold-to-Party dimension from the list of dimensions. Use a simple drag-and-drop navigation and add the dimension to the Rows so that the Rows first display the Sold-to-Party dimension and then the Product Group dimension (Figure 17).
Figure 17
Add the Sold-to Party and Product Group dimensions, respectively
2. Because this new row is context-aware, the formula now calculates the percentage share for the values for each customer (Figure 18).
Figure 18
The new crosstab with the new row
3. To format the percentage values, you need to set up a new Design Rule. Select the newly created row, starting with the label text created earlier (% Share of Laptops), and include the calculations with your selection.
4. Navigate to the menu Format Cells and select the option to create a new format (Figure 19).
Figure 19
Create a new format
5. This action opens a Format dialog (Figure 20) in which you set the Scope field to New Line Members and Data and click the create new style icon .
Figure 20
Format a new style
6. This action opens a Style dialog in which you can define your own style (Figure 21). Enter a new Style name (% Share of Laptops) and click the Format… button.
Figure 21
Define a new style
7. In the Format Cells pop-up screen that opens, select the Percentage option in the Number tab, and set the number of Decimals places to 0 (Figure 22). Click the OK button after you’ve made your entries, and then click the OK button two more times in the screens that open (not shown).
Figure 22
Format numbers for cells
8. The newly added line (% Share of Laptops) is now formatted according to the new rule you created (Figure 23).
Figure 23
The crosstab with the new rule
9. Select any cell in the newly added row (% Share of Laptops). Navigate to the Design Rules tab in the navigation panel (Figure 24), where you can directly edit the rule or deactivate the rule.
Figure 24
Design or edit rules
Upcoming Product Enhancements to Analysis Office
In this section I provide a review of some of the upcoming enhancements to Analysis Office. Figure 25 shows the roadmap with the planned innovations and the future considerations.
Figure 25
The new product roadmap for Analysis Office (as of September 2016)
The items listed in the Planned Innovations section represent the enhancements for the next release of Analysis Office. Some highlights include:
- SAP is adding support for Microsoft Word for the EPM plug-in.
- The Table Design feature is being enhanced to also allow adding Analysis Office formulas as part of new lines or columns.
- Analysis Office integration with SAP HANA Identity Management enables single-sign on (SSO) across cloud-based SAP HANA environments.
- Analysis Office will allow restricted measures for SAP BW planning scenarios.
- The ability to define custom groups and custom hierarchies in Analysis Office.
As part of the Future Direction section, there are two important changes:
- Analysis Office will be more closely integrated with SAP Lumira and SAP BusinessObjects Cloud.
- Universe connectivity is planned for Analysis Office so that Analysis Office can also replace some of the workflows that are currently offered by Live Office.
Product Roadmap Disclaimer
The descriptions in this article of future functionality are the author’s interpretation of the publicly available product integration roadmap. These items are subject to change at any time without any notice, and the author is not providing any warranty on these statements.
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.
