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.

Advertisements

One thought on “Several ways of data extraction from SAP Business ByDesign

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s