Bulk extract data from SAP Business ByDesign

Post describes how Power Query (Get & Transform) can be used as ETL tool for SAP Business ByDesign (SAP ByD).

Purely Self-Service BI solution built on Excel for SAP Business ByDesign (SAP ByD).

Level: Advanced

How to extract master and operational data from SAP ByD using Power Query

Common information can be found in my previous posts, which I’d recommend to go through

Several ways of data extraction from SAP Business ByDesign

Query SAP Business ByDesign Web Services via Power Query

From all possible methods of data extraction, I chose Excel + Power Query as the most simple one for Modern Excel data analysts (non-IT pro).

What do we need to start this journey?

What can help?

Massive data extraction from SAP ByDesign

Let’s start from the most interesting – extract of large data volume from SAP ByD.


SAP ByDesign + Power BI = cloud friends

In this post you will find an idea how to pull data from SAP ByDesign directly to Power BI. This is a totally self-service solution, which can be built if you have literally nothing. Only you, Excel, free license of Power BI and SAP ByDesign tenant(s).

Samples based on data from test tenants that were opened for participants of openSAP course “Reporting with SAP Business ByDesign“.

Couple of words about SAP ByD for those who are not aware what is this. Cloud ERP system, has own real-time analytics engine, quite powerful, but poor. Poor calculation capabilities, poor visualization capabilities. Handles only basic things, obvious calculations.

Moreover, if you have several SAP ByDesign systems in responsibility you most probably have to consolidate data somewhere. Build sub-regional, regional reports etc.


Several ways of data extraction from SAP Business ByDesign

There are several reasons to extract data from corporate ERP system.

First one and the most important – reporting. Nicely visualized data, usually pre-calculated using specific logic before. We all want to see beautiful reports.

Second, I think, is a data transfer to: BI system(s), DWH etc. Maybe for some companies this is a first priority instead of reporting.

Third, rare, but happens in my practice – data migration due to system “end-of-life”. Yes, this happens. What to say? In a moment I’m involved in such project, second in my practice.

I would like to share my experience. Hope it will simplify life of many other reporting specialists working with SAP Business ByDesign.
First of all, basic ways of getting data:

Query SAP Business ByDesign Web Services via Power Query

In this post I would like to share my experience about work with web services using Power Query.

Unfortunately, some very important information is not available in data sources, e.g. Accounts Relationships and Pricing Conditions (price lists, customer specific discount lists etc.). Hope someday we will get this in reports.

However, currently, we can get this info only from

  • UI manually, almost impossible to do if you have X tenants and XXX customers with specific price lists
  • Web services – Jedi path, require knowledge and skills, or trustable tools, that can be checked

To our luck, with every new release we get more and more web services.

Basic idea of web service usage

  1. Make POST http request with XML
  2. Parse XML response


Dynamic Table Headers in Power Query (SAP ByDesign, Odata)

Another good example of how awesome is Power Query. And again I reference to one of well-known bloggers, Ken Puls’s post describes situation when we have pivoted data and need to unpivot them then rename columns. Fortunalety, in this example not so many columns used and unpivoting + join “rename table” is a solution.
However, in common case when we have table with 20 columns and 100 000 rows (or 2 million) unpivoting will be a very bad decision.
I faced with this challenge when I started to work with Odata protocol in SAP Business ByDesign.

Using Odata we have to provide string with list of fields ID in $select, for example query of Account Contact Data:



Reporting and modeling for SAP Business ByDesign vol.2

In previous post I described list of ways of data extraction from SAP ByD. Here I continue story about case “ByD to Excel” using SAP ByD Excel add-in.

Currently, I use version 135.0.2109.722 of SAP ByD addin for Excel
ByD Addin Version

In name mentioned that add-in for Excel 2010, but I’m using it for a long time with Excel 2013. From time to time have same problems that I had when used Excel 2010.

This is a basic way that SAP offers to SAP ByD consumers. However, when I opened for myself Power Query and Odata protocol in ByD, I use ByD Excel add-in only for temporary solutions or quick data exploration.

Assume, that you inserted report on worksheet. Say, Invoice Volume.

Reporting and modeling for SAP Business ByDesign

Last two years I closely was working with SAP Business ByDesign reporting area. Have built data sources, web / Excel-based reports, models, print forms, broadcasts etc. In my responsibility six productive tenants: three of them in Baltics (Latvia, Lithuania, Estonia) and three in CIS (Kazakhstan, Ukraine, Belarus). I had to build starting package of reports and models to support legal requirements and business processes and worked on improvement of operational / strategic reports, consolidation of data on regional level etc. This post starts series about reporting for SAP Business ByDesign (further: SAP ByD or just ByD). I’m going to tell about problems I had to solve, some tricks and ideas. Hope this will help someone who is restricted in access rights as me and not an SAP ByD developer but going to building efficient reporting solution. So, let’s start. (more…)

Need help?

We can help you with Excel, Power Query, Power Pivot and Power BI, automation of your reports, reporting with SAP ByDesign and SAP BW.

Let us know if you need support. How can we help you?

Excel Automation

Automation of repetitive actions is an important part of work of every advanced Excel user willing to stay productive. Sometimes it is a single-line macro performing one specific action, sometimes – massive VBA code developed to optimise certain business scenario.

There are plenty of good web sites and online courses helping to beginners.

I focus on automation of Excel-based reporting, when non-IT reporting specialist / business user has to update multiple Excel workbooks regularly with minimum effort.

I assume that each workbook is designed in a way that refresh can be done without manual interactions. With Power Query it is much easier now.

Schedule Refresh of Excel files

I worked in a company where Excel-based (Self-Service) reporting was the main way of reports preparation for all levels of users. Everything I designed colleagues wanted to see refreshed on regular basis. Those were different reports (Excel workbooks) – some with connection to internal SQL data bases / Excel files / CSV files, some – with Power Pivot and sophisticated Data Model and DAX measures, some – just a simple Power Query queries loaded to worksheets and so on.

That situation forced me to develop a solution that would manage refresh of multiple Excel workbooks with unique logic.

I mainly work with Excel and VBA, so I decided to stick to these two technologies. Firstly, because Excel gives flexibility for maintenance of parameters needed for reports (as any parameter value can be generated by formula), secondly, because VBA + Win API functions give enough flexibility to control refreshing process.

You can read more and download Power Refresh solution from its official page on Github.

Automation of SAP Analysis Excel add-in

Another solution has been developed to automate SAP BO Analysis add-in. In particular, refreshing workbooks with data sources linked to SAP BW / SAP BO. It can be downloaded from its Github page.

Big cake for BI-day

powerbi cake

No, it is not another birthday of PowerBI :-). It is me turned 31 today.

Every year my wife bakes a cake for my colleagues on my birthday.

And every year “birthday, b-day, bi-day, BondarenkoIvan-day” cake for colleagues is decorated by something related to the most interesting moment in my work during last year.

2013 I joined HILTI Russia (HILTI-case), my first experience of work on global scale, a place where I significantly developed myself, started blogging to improve English, met a lot of smart and friendly people, where many things happened with me, long list actually.

2014 we finished six implementations of SAP Business ByDesign, where I was responsible for reporting and workarounds for processes, that system doesn’t handle (cake with flags).

2015 I won HILTI Star Club reward as “best entrepreneur”, optimizing business processes, pro-actively helping everyone with automation of routine, reports, Excel related stuff, simply consulting on best practices, training colleagues and so on (2-floor cake decorated with stars). Deluxe trip to Cancun paid by company was absolutely awesome, unforgettable!

2016 we finally started an official project on PowerBI. Very significant moment.

powerbi cake

If you have some time to explore other sweet works – welcome to my wife’s collection of cakes.