SAP BO Analysis Automation

Intro

SAP BO Analysis for Office is an Excel add-in from SAP. And like any good Excel add-in, it has API, which allows to use VBA macros to execute certain methods of add-in.

Main purpose of this add-in is to pull data from SAP BI systems into Excel, where end user can use Excel functions to analyse data.

Quite often users have many queries (data sources) in their workbooks and updating takes significant amount of time, especially, if user has to change variables in each query. Typical situation – need a report for another day / another month. Or, simply, need to automate preparation of certain report, to make it running during night time without manual interaction.

BO Analysis Automation Tool

I created a tool that allows to automate refresh of workbooks with BO Analysis data sources (queries).

Tool helps to automate

• change of Variables (Prompts), dynamically set by formulas
• dimension Filters (Background Filters)
• refresh process itself.
• distribution of resulting files – as PDF, Excel, CSV files.

On top of this, user can configure additional actions (after refresh of queries is finished) like

• “Refresh All” – to refresh pivot tables, data model (aka PowerPivot), Power Query.
• “Save” in place, “Save As”,
• “Save As & Email”,
• Run specific macro etc.

Entire solution consists of only one worksheet – ‘Control Panel’ (VBA code is inside it).

User doesn’t have to re-create existing reports from scratch.

‘Control Panel’ sheet can be easily moved to the existing workbook using standard “Move Worksheet” Excel action.

Then user has to ‘collect variables’ – information about data sources and filters applied to variables (prompts) and dimensions.

Values for filters can be defined with a standard Excel formula or UDF. In this way user can achieve dynamic update of variables and avoid manual changing each time.

When variables are set, left to hit “Refresh” to trigger refresh process.

What about Username / Password?

In order to fully automate process, you need to store credentials somewhere. Currently, I propose to store this info in txt file. ‘Control Panel’ has a parameter where user can provide path to txt file with credentials in required format. Read comment to “Path to file with passwords” parameter.

Download

Solution is published on Github under MIT license.

https://github.com/IvanBond/SAP-BOA-Automation

Feel free to download, use, share with colleagues etc.

Any feedback is highly appreciated.

 

Video Guide

Work in progress, not ready yet.

 

Advanced scenarios

In real life user can face many complex scenarios.

• Pull data from multiple systems
• Refresh all pivot tables, Power Query queries, Data Model (aka PowerPivot) after refresh of data sources is done
• Try refresh, if no new data in a cube – wait 10 min, then try again
• Use specific macro before refresh of data sources
• Use specific macro after refresh of data sources
• Use specific macro after certain data source, then continue refresh of remaining data sources
• Refresh all data sources daily, but one only in the beginning of each new month, e.g. on 4th day.
• Data extraction – repeat refresh of certain data source multiple times, run specific macro that saves data into CSV after each refresh.

Use pre-configured relative time periods (relative to Report Date)

Wide range of saving scenarios

• Only certain sheets
• Or without certain sheets
• Converting formulas to values on certain sheets
• Setting Read-Only Recommended flag to resulting file

Multiple emailing options

All of these can be configured in my tool without any VBA coding required from user.

Scheduling Refresh of Excel Workbooks

If you have many Excel workbooks that you need to refresh regularly, on schedule, consider my another tool – Power Refresh. It’s main purpose is to orchestrate update of Excel files. It is a great alternative to standard Windows Task Scheduler, as ‘Power Refresh’ supports scheduling refresh of Excel workbooks on working days, sorting tasks, control over number of tasks running and much more.