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).
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.
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:
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
- Make POST http request with XML
- Parse XML response
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…)