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:
Web reports -> download to Excel
This is a way of standard user. Simply – run report in web browser – export to Microsoft Excel if needed. Always remember joke of Rob Collie regarding the third most popular button in applications after “OK” and “Cancel”. In SAP ByDesign “Export to Excel” is available almost on each form (and thanks God!).
Important advantage of this way
Web reports allow to download data source in CSV format. This way helps to overcome 60-character limit in reports.
For example, in Sales Order you may have long text in Internal Comment. The easiest way to export full text is using of “download data source in CSV format” feature.
Another example – notes in Activities. Same problem in reporting on this object.
Unfortunately, I don’t know how to automate such extraction using reporting engine.
Web reports and embedded reports are covered in openSAP course in details: https://open.sap.com/courses/byd2
Excel add-in: Insert Report
Another way of getting access to data
What is interesting in this method?
It is possible to insert as many reports as needed in workbook, even to one worksheet. Only need to care about overlapping of reports; place them wisely. All reports can be refreshed by “Refresh All” button.
Unfortunately, you cannot control order of execution in such case. But this can be solved with VBA programming.
Problems that we faced during work with SAP ByDesign Addin for Excel:
Firstly, it was manual Paging. In old version of addin users had to manually disable paging or set size of page – amount of lines that addin download from SAP ByD at once.
After upgrade to version with dynamic paging, life became much easier. Rarely, addin reset defined set of columns, or add new key figures, but we can live with it.
What we revealed recently – we again became trapped by old version of addin. Many workbooks developed on that version cannot be used with actual version of addin available in SAP ByD for download. Almost the same story as with Bex3 and Bex7.
I could write several posts about further usage of inserted reports, what is possible, what is not etc. However, as about “way of data extraction” there is nothing to add.
Excel-based reporting is covered in openSAP course in details: https://open.sap.com/courses/byd2
More advanced ways:
Broadcasting of Web reports (Business Analytics – > Broadcasts)
Not so far time ago good post was published on SCN about usage of this way – Using the Broadcasting Feature to Email Reports in SAP Business ByDesign. Only described scenario is strange. Usually Vice Presidents would fire reporting specialist that sends reports as CSV file :-).
Nevertheless, broadcasting is a good feature. I have been using it for a long time. However, I use it mostly for data transfer, and less for monitoring of issues.
For example, I use it to control perpetual cost method in materials. Depending on country method can be different. Some countries must use moving average, some – standard. If material manager creates material with wrong option then on next weekday he/she will receive a report in mailbox.
If everything is fine, no errors, then broadcast will not send anything to end-users, because “no report data available…”
Another usage is scheduled data export.
XML or CSV format match to the purpose.
! Be careful with CSV if you have text fields where user can input additional comma. As far as I remember, last time when I tried CSV, SAP ByD did not add quotes for text fields, hence it became impossible to split data on columns when additional comma appears. As a result I switched to XML.
I strongly recommend to use “ZIP” option for XML if you download quite big amount of data. Unzipped XML can be more than 100 MB in size (and SAP ByD will not send it), when zipped version is 1-3 MB.
Of course, there is a limitation in the system. You cannot broadcast full General Ledger detailed down to Journal Entry Items for a long time frame. “Selection” allows to make relative or direct query which can be saved and then used in broadcast. Create reasonable selection.
If you use mail program that supports events or rules, and can execute program code (e.g. MS Outlook), you can programmatically unzip attachments from coming emails, convert XML using XSLT and so on. If you want to go this way you should probably know how to handle XML.
Btw, SAP ByD sends XML marked in the same way as Excel spreadsheet, so Excel can open such file, but not every integration service can digest it, e.g. SSIS. So if Another problem, is size of XML, which sometimes cause of Excel cannot open it.
Therefore, it is better to apply XSL transformation and make XML file more simple. I did it with “old friend” msxml.exe.
And used this method for a long time, until discovered OData and PowerQuery way.
This way is mentioned in openSAP course: https://open.sap.com/courses/byd2
EOL data extraction
Can be reached via such links:
SAP test tenant from openSAP course:
This way is good for master data extraction. Unfortunately, cannot be automated for reporting purpose. Some data available through this way are not available in data sources and web services.
Usage is pretty simple
Select row with desired data
Press “Execute Data Export” and wait while execution in process
Check in window below (small “refresh button” no the right)
Download to Microsoft Excel.
This service generates XML having namespace office:spreadsheet
So easily can be opened by Excel as promised.
File contains migration template
Unfortunately, this extraction cannot be scheduled and used for automated reporting purpose.
Financial data extraction run
Extraction Run form offers only one Output format – SAP Audit Format
Populate necessary fields and press “Start Now”
After execution we can “View Log”
Now we can “Download” desired data
System offers to save result as .zip archive that contains two files
Txt file contains header of report
And file without extension contains data in “SAP Audit Format”
From the first glance, file contains Ending Balance per each G/L account and Accounting Period of selected period of data extraction (on the initial form).
To be honest, I have never used this in real life. Much easier to run old good Trial Balance report and get the same.
Export from UI forms
Real life scenario – export of Registered Products. Weird, but there is no other option in SAP ByDesign as export list of registered product using UI. No Data Source with Registered Products, no web service, no End-of-Life extraction.
Huge disadvantage of this method is limitation of information showed in table.
List of Registered Product standard fields that shown in table is limited to
Registered Product Object can be extended (UI extension field) by field with limited list of calculated rules.
Sorry, stepped aside of post title. Just Registered Products is a painful topic.
Back to data extraction – Export to Excel from UI form creates an .xlsx file
Simple and useful way when user have to quickly get data in Excel for analysis. No need to go to reports, populate necessary criteria in selection and so on.
As of now, list of available in SAP ByDesign web services is located here:
With each release we get more and more web services.
Main purpose of web services is integration with 3rd party applications, e.g. web site, or applications that automate mass change of some attributes. Because web services allow not only to read data, but also to update.
Knut Heusermann wrote great post about this almost year ago http://scn.sap.com/community/business-bydesign/blog/2015/05/15/mass-changes-of-byd-data-using-excel-and-byd-web-services
Some web services are essential for automated reporting solution as some data not available in data sources, e.g. Price Lists, Customer Specific Price Lists, Accounts Relationships and some other.
Some time ago I wrote quite detailed post covering the idea of pulling data using web services, there you may find samples of query and response – Query SAP Business ByDesign Web Services via Power Query
Odata – access to web-reports available in ByD through Odata protocol
Despite OData in SAP ByDesign doesn’t fully match to canonical OData, it is still very useful.
Because it allows to automate data export and further usage for reporting purpose.
Query string for JSON response
Light-weight JSON response
Query string for XML response
Heavy XML response
Both formats non-readable, scarifying.
However, for user of Excel 2016 or Excel 2010 / 2013 with Power Query installed this is not a problem.
It is easy to get data directly to worksheet as table
Step by step “how-to”:
Go to Power Query tab and find “From Web” button
Paste URL with $format=json
Change Tenant ID on your tenant if needed.
If you participated in openSAP course, you must know login and password to test tenants.
Use Basic authorization
In my case PQ decided that response is CSV file. It is easy to fix. Click on small gear (Settings of Source step)
Set parameter “Open File As” to “Json”
Drill down to Record loaded from Json
Drill down to list
Convert List to Table
And expand records choosing fields that really needed in resulting table
When table is expanded we can load it to worksheet
Select Table, New worksheet or Existing and then press Load
Data almost ready to be analyzed in Excel
I would rename columns.
The most easiest way is to rename manually in query.
But such way is not flexible.
Key idea to make this process simpler is defined in one of my previous posts – Dynamic Table Headers in Power Query (SAP ByDesign, Odata)
You can avoid step-by-step expanding of data each time. I offer advanced solution
Just use a function
Go to Power Query and make Blank query
Switch to Advanced Editor
Paste code and press Done
Now query becomes a function that can be Invoked
Only one argument is required – connection string. Put there link with $format=json
Same can be done for Master Data reports or transaction report – any report that available for user.
Another guide: Using SAP ByDesign OData in Microsoft Power Query
Comprehensive guide on SAP ByD OData from Knut Heusermann: OData for SAP Business ByDesign Analytics
In my next post I’ll try to show real-life solution based on OData.