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).
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.
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.
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:
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
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. (more…)
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…)
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.
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.