SAP Analysis for Office 2.5 and 2.6 – New Features
By: Peter Casanova
SAP has released two big updates to Analysis for Office (AO) in a relatively short time frame, making strides in responding to the user community’s requests, and continuously improving the product offering. There were roughly three months between release dates: AO 2.5 was generally available to the user community August 25th of 2017, and AO 2.6 available December 7th of 2017.
Users determining whether to upgrade should find this blog post helpful in discerning the incremental improvements to each version, and what changes they can expect from an administrative as well as end-user point of view.
Analysis for Office 2.6 – What’s New?
Analysis for Office 2.6 focuses mainly on improving functionality through formulas and macros to meet evolving business requirements, and adds useful administrative settings. The product name has now officially been changed to “SAP Analysis for Microsoft Office.” Here are the incremental updates added in the jump from 2.5 to 2.6:
- Styles Limit – New setting “StylesCountPerformanceThreshold” allows Admins to specify the number of cell styles that can be used in a workbook before a warning message is displayed. This is useful for companies who frequently run into slow performing workbooks, especially while launching the workbook, due to an abundance of cell styles in the report.
Select the dropdown indicated by the red arrow in the screenshot below to check the number of styles in your workbook:
- Statistic Writing – New setting “AlwaysDoApplicationSteps” allows Admins to specify when statistics are written to the table RSDDSTAT_OLAP. The table RSDDSTAT_OLAP allows us to gather information for BEx queries, so this new setting will be helpful troubleshooting performance and failures triggered by key events.
- EnableNativeFormatting – Enables correct handling of Excel time operations, like Year()
- Scheduling (BI platform 4.2 SP5)
- Schedule object with events – will run after specified event occurs in BI platform.
- Rescheduling an object – reschedule previously run object or making changes to existing.
- Report Bursting – select a report bursting method to determine how source documents are delivered in a publication (personalized, processed, delivered)
Features and Functionality
- Crosstab Table Design formulas
- Copy a table design formula to adjacent cells using Microsoft Excel Fill Handling.
- Reference outside crosstab in formula
- VLOOKUP functionality available in Table Design formula
- Member Filtering – Dialog filter available to open directly in the range view when filtering for a range of numbers. Avoids loading all master data when working with large master data tables.
- SAPSETFILTERCOMPONENT – formula with command MEMBERSELECTORBYRANGE allows users to define the standard filter dialogue range with range option is default for filter component.
- SAPEXECUTECOMMAND – API used with command NUMBEROFNEWLINES allows you to set number of new lines for a crosstab.
- SAPGETPROPERTY – API used with command NUMBEROFNEWLINES allows you to retrieve the number of new lines for a crosstab.
- SAPCALLMEMBERSELECTOR – API used with command MULTIPLEBYRANGE allows you to select a range of members for filtering.
Analysis for Office 2.5
Here is a selection of features and improvements that were added in the jump from 2.4 to 2.5. These features are all included in version 2.6 as well:
- User interface – Version 2.4 of Analysis for Office implemented helpful changes allowing users to personalize their user interface – AO 2.5 has continued enhancing certain areas of the UI for all plug-ins. Information for each plug-in is available in their respective guides – Analysis Plug-in User Guide, EPM Plug-in User Guide and BPC User Guide.
File –> Analysis –> Customize Analysis –> Customize User Interface –> Backstage View –> Help – Sends you to the most updated instructions for customizing the UI.
- Technical settings – can now be maintained directly in the Analysis add-in, as opposed to previous releases that required changing settings in the file system. A common ask from clients is increasing the output size of their query results (ResultSetSizeLimit), which can now be manipulated in the Excel workbook by the user, rather than manipulating file systems with a technical resource.
File –> Analysis –> Customize Analysis –> Technical Configuration
- Profiling statistics – can also be changed easily in the same fashion as the previous updates to UI and technical settings. Support mode and Profiling can be active concurrently, and Advanced mode can specify further options once selected – defining which log files are written, where the log files are written to, and BW statistical settings.
File –> Analysis –> Troubleshoot –> Select troubleshooting mode
Features and Functionality
- Prompts – Previously, there was no functionality to remove one or all members when filtering on 50+ members in AO2.4. AO 2.5 has incorporated a filter button that allows you to access those members after the initial refresh of 50+ members and either remove one, many, or all members.
- Recent Data Source Tooltip – When inserting a data source, hovering over the recent data source list will provide a tooltip for the Technical Name and System of each respective query.
- Editing crosstabs with Table Design
- Add formulas to new member cells
- Add formulas to input-enabled planning data cells
- Copy rules from one data source to another in design panel
- Keep the Table Design rules when resetting a data source
- Grouping Crosstabs – You can now group crosstabs to be one seamless crosstab. This should prove useful when comparing data side-by-side at a different level off dimensionality.
- Master Crosstab controls which dimensions can be filtered or moved in the new, dual crosstab.
- If you use or change the display in the master crosstab to Compact Display in Rows, dependent crosstabs behave accordingly.
- Hierarchies can be expanded in different directions .
- You can modify existing custom hierarchies – change group header (node name) and add/remove members in hierarchies using drag and drop.
- Conditional Formatting with Scaling Factor – Conditional formatting is now changed automatically when scaling factor is updated for a measure within an existing rule. Conditional formatting rules with earlier versions of Analysis will not change until using AO 2.5 to create the rule. Previously, if scaling factor was changed, the conditional formatting would be cleared out automatically.
- Macros – There is a new API method SAPCallMemberSelector with type FILTER_NEW_LINES_WITH_MASTER_DATA. This can be used to get a selector with all members of the master data for the selected dimension in leaf members view (leaf members have no child members).
Integration and Performance Improvement
- SAP BusinessObjects Cloud – integration with SAP BusinessObjects Cloud is now possible in Analysis for Office once the connection is configured. You will be able to use Analysis for Analytics-type models and Planning-type models.
- Lumira 2.0 – interoperability has been established in Lumira 2.0 with Analysis for Office. You can export from Designer directly to Analysis for Office once Lumira 2.0 has a technical component created for AO with a corresponding script to export.
- Planning data manually – using the clipboard, you can enter a large amount of new lines. You can paste more lines than configured in the design panel and Analysis will automatically increase the number of new lines temporarily.
- Scheduling – Grouped crosstabs and Microsoft Excel formulas can be pre-calculated.
- New Measures based on SAP HANA views – Using SAP HANA data sources you can use the following to calculate new measures:
- HANA 1.0 SP12 – calculate measures based on an available measure. Operators available are Rank Number and Olympic Rank Number.
- HANA 2.0 SP1 – define an exception aggregation for calculated measure based on free-form calculation.