Bulk extract data from SAP Business ByDesign

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).

Level: Advanced

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?

• SAP Business ByDesign system + user with ability to logon using Username and Password (Basic authentication)
  • For demo I use tenant from openSAP training: https://my336123.sapbydesign.com
    ( works at time of writing )
• Excel 2016 or Excel 2010/2013 + Power Query addin
  
• Sample workbooks, which dramatically simplify life – Metadata Workbook, Simple Report Workbook, Advanced Report Workbook (draft, for large data extraction)
  

What can help?

• OData for SAP Business ByDesign Analytics – great guide on SAP ByD OData
  
• SAP help page for 1605 release
• 64 bit version of MS Office, or 32 bit with patch mentioned by Ken Puls in his blog. More data – more memory, simple rule.
  
• Library of Power Query functions on GitHub

Massive data extraction from SAP ByDesign

Let’s start from the most interesting – extract of large data volume from SAP ByD.

If you, for example, required to transfer data from SAP ByDesign to MS SQL, you most probably have to transfer

• Master data; e.g. Account Master Data, Materials, Contacts, Employees, Organization Structure etc.
• Operational data; e.g. Invoices, Sales Orders, Service Orders, Activities, General Ledger etc.

Both types of data sources can contain huge amount of records. This makes impossible to export all data at once through SAP ByD reporting engine.

OData has limits as well, so do Web Services.

But we can make many queries, each for small portion of data – and in the end combine them.

Depending on report we need to choose field that can help granulate data so, that small portion of data does not reach limit.

For example, Posting Date is a good field to slice General Ledger.

Build connection strings

What we need to do is to build list of connection string, where each connection string represents request for a small portion of data, having different $filter

If you have no many transactions per month, you may use Accounting Period/Fiscal year field to slice data source.

Sample query that generates list of dates and connection strings can be found in Advanced Report Workbook

Add column with a custom function in Power Query

After watching Deep Dive into Power Query Formula Language I realized the true power of Power Query in work with data.

When we have a table with list of connection strings – nothing stops us from add column with function ByD.GetReportDataIDs.

PQ will apply this function for each row, each connection string – and in the end we have a column with Table objects in each row, where each Table represents data set for particular date. Left only to expand each table.

Sometimes, there is no data for particular date, and if you load result into PowerPivot Data Model you must load table with defined list of columns. For this purpose you may use custom function Table.FromString.

Checking PQ trace log I found that Power Query requests data in multiple threads – this helps to dramatically reduce refresh time. All small portions are collecting faster.

Described approach can be applied to any SAP ByD report. Using it you can download entire data source if needed, just find a good slicing field.

Keep reading if you interested in details of this solution. Below you may find step-by-step guideline.

How perform OData query from SAP using Power Query?

1. Prepare report in SAP ByDesign for OData
2. Create connection string for OData
3. Query report by Power Query function Web.Contents
4. Parse response in Power Query, do necessary transformations
5. Load result to worksheet (or Data Model)
   
   
   1. Prepare report in SAP ByDesign
   

   Why?
   

   This step is needed only for those reports that have restricted Initial Selection.

   For example, G/L Accounts – Line Items by default has initial selection restricted to “Current Fiscal Period”
   

   
   

   It means that you cannot get any data from other fiscal periods through OData, as it works only with Initial Selection.
   

   How to make un-restricted report?
   

   Find report in Business Analytics work center

   And create new as copy of existing report
   

   Provide a name that will allow to identify this report
   

   
   

   Remove tick from “Copy With Navigation” checkbox. Keep it only if you really need this.
   

   
   

   Next step is very important – select key figures

   Select “Show All” key figures (by default you may see Key Figure Groups)
   

   
   

   Key Figure Groups may contain key figures restricted by variable, that is mandatory in report.

   Usually, for data extraction we need only pure basic key figures.

   Un-select groups and select basic key figures
   

   
   

   Add characteristics missing in original report.
   

   
   

   If you plan to use this report not only for OData – add characteristics to Selection.

   Note: it is not required for OData! Through OData you may filter by all fields that are configured as “filterable”, even if they are not shown in Selection Area.
   

   
   

   Save new report and assign to corresponding work centers. I usually assign to the same work centers as original report to avoid data access issues.
   

   2. Create OData connection string
   

   Our goal is to get something like
   

   https://my336123.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPZ9398CB74255DBC51F208B2QueryResults?$top=100000&$select=

   COEDREF_F_ID,TOEDREF_OBJ_TC,CDOC_DATE,KCAMTCOMP,CPOSTING_DATE, TFUNCAREA,CGLACCT,CACC_DOC_UUID,CACC_DOC_IT_UUID&$format=json
   From the first sight it looks scarifying.

   For $select string we need ID of each field. This info can be retrieved from report metadata.
   

   Get SAP ByD Report Metadata
   

   As new report was manually created it got unique ID – find it in Business Analytics work center
   

   
   

   According to guide: OData for SAP Business ByDesign Analytics

   We should build following string to get report metadata (mainly for field IDs and names)

   https://my123456.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/$metadata?entityset=RPCRMSLOIB_MQ0001QueryResults

   String with our data

   https://my336123.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/$metadata?entityset=RPZ9398CB74255DBC51F208B2QueryResults

   In Firefox it shows me
   

   
   

   Actually, it is enough to configure $select string with such XML, but too tedious.

   To simplify this process I developed simple Excel workbook – Report String Generator, where I can enter tenant and report ID, and get metadata
   

   Then enter field names in column and Excel formulas will finish the task.

   3. Query report via Power Query

OK, we got parts of connection string, let’s use them.

For quick win you may take Simple Report template

Just input your report parameters and refresh Result query.

If you interested in details of the process behind – keep reading

We had built connection string:

https://my336123.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPZ9398CB74255DBC51F208B2QueryResults?$top=100000&$select=COEDREF_F_ID,TOEDREF_OBJ_TC,CDOC_DATE,KCAMTCOMP,CPOSTING_DATE,TFUNCAREA,CGLACCT,CACC_DOC_UUID,CACC_DOC_IT_UUID&$format=json

Note: this URL doesn’t contain keyword $filter ! It means that PQ will pull everything – entire data source (until reach limit set by $top=100000). According to size of data set such report can fail.

Use standard query from Web

Paste that ugly URL

Provide basic credentials of your user

As we requested, report came in json format (keyword $format=json is important)

By unknown reasons, PQ applied Lined.FromBinary function, instead of Json.Document (maybe related to PQ version).

Fix:

Open Advanced Editor and change the code

let

Source = Web.Contents(“https://my336123.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPZ9398CB74255DBC51F208B2QueryResults?$top=100000&

$select=COEDREF_F_ID,TOEDREF_OBJ_TC,CDOC_DATE,KCAMTCOMP,CPOSTING_DATE,

TFUNCAREA,CGLACCT,CACC_DOC_UUID,CACC_DOC_IT_UUID&$format=json”),

buffer = Binary.Buffer( Source ), // buffer to avoid possible repetitive requests

json = Json.Document( buffer )

in

json

After this you can just drill down, expand json till you get desired result (if you want to do these steps on your own).

I described how I had being doing this when did my first steps in this direction.

Then I simplified process by developing function ByD.GetReportDataIDs

This function expand result for you, just provide a connection string:

And then I came to life saving templates – such as Simple Report Workbook.

4. Parse response in Power Query, do necessary transformations

1. Simply pull data from SAP ByDesign usually is not enough for data model.We need to doRename fields

   Check data type of every field

   Convert date fields into normal date

   Convert time fields (if any)

   Probably – clean data, e.g. replace “Not Assigned” or # with null

   Use function Date.EpochToDate.m to convert ByD json date field into normal date.

   You may read here how to transform column in Power Query with a specific function.

   For quick renaming headers in Power Query – use rename technique described here
   

   5. Load result to worksheet (or Data Model)
   

Depending on purpose of data extraction, choose where do you want to place your data.

Btw, so far, I couldn’t find a way to connect SSIS to SAP ByDesign directly, maybe because of SAP security settings, maybe because OData in SAP ByDesign is not pure OData, maybe because it requires more professional knowledge.