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:

• 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
  

  End-of-life extraction.

  Can be reached via such links:

  https://myTENANT-sso.sapbydesign.com/sap/bc/webdynpro/sap/dmc_ext_eol_data

  https://myTENANT.sapbydesign.com/sap/bc/webdynpro/sap/dmc_ext_eol_data?saml2=disabled

  SAP test tenant from openSAP course:

  https://my336122.sapbydesign.com/sap/bc/webdynpro/sap/dmc_ext_eol_data

  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
  

  Several sheets
  

  
  

  General:
  

  
  

  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.
  

• Web services
  

  As of now, list of available in SAP ByDesign web services is located here:
  

  http://help.sap.com/saphelp_byd1602/en/PUBLISHING/IntegrationServices.html
  

  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
  

  https://my336124.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPBPCSCONTB_Q0001QueryResults?$top=100&$select=CBP_UUID,CCONT_MAIN_INDICATOR,CCONT_WPA_EMAIL_CONTENT,CCONT_WPA_FUNCTIONAL_TITLE,CCONT_WPA_MOBILE_NUMBER,CCONT_WPA_PHONE_NUMBER,TBO_ROLE_CODE,TBP_STATUS_CODE,TBP_UUID,TCONT_FUNCTIONAL_AREA_CODE,TCONT_UUID,TCURR_EMPL_RESP_PARTY_ROLE_CODE,TCURR_EMPL_RESP_UUID_CONTENT&$format=json
  

  Light-weight JSON response
  

  
  

  Query string for XML response
  

  https://my336124.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPBPCSCONTB_Q0001QueryResults?$top=100&$select=CBP_UUID,CCONT_MAIN_INDICATOR,CCONT_WPA_EMAIL_CONTENT,CCONT_WPA_FUNCTIONAL_TITLE,CCONT_WPA_MOBILE_NUMBER,CCONT_WPA_PHONE_NUMBER,TBO_ROLE_CODE,TBP_STATUS_CODE,TBP_UUID,TCONT_FUNCTIONAL_AREA_CODE,TCONT_UUID,TCURR_EMPL_RESP_PARTY_ROLE_CODE,TCURR_EMPL_RESP_UUID_CONTENT&$format=xml
  

  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

  https://raw.githubusercontent.com/IvanBond/pquery/master/ByD.GetReportDataIDs.m

  Copy code
  

  
  

  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
  

  
  

  And voilà
  

  

  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.