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).
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
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)
- 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
|CSETOFBKS eq ‘9011’ and KCAMTCOMP ne 0 and CPOSTING_DATE eq datetime’2015-01-01T00:00:00′||https://my336123.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPZ9398CB74255DBC51F208B2QueryResults?$top=100000&$skip=0&
$filter=CSETOFBKS eq ‘9011’ and KCAMTCOMP ne 0 and CPOSTING_DATE eq datetime’2015-01-01T00:00:00’&
|CSETOFBKS eq ‘9011’ and KCAMTCOMP ne 0 and CPOSTING_DATE eq datetime’2015-01-02T00:00:00′||https://my336123.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPZ9398CB74255DBC51F208B2QueryResults?$top=100000&$skip=0&
$filter=CSETOFBKS eq ‘9011’ and KCAMTCOMP ne 0 and CPOSTING_DATE eq datetime’2015-01-02T00:00:00’&
|CSETOFBKS eq ‘9011’ and KCAMTCOMP ne 0 and CPOSTING_DATE eq datetime’2015-01-03T00:00:00′||https://my336123.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPZ9398CB74255DBC51F208B2QueryResults?$top=100000&$skip=0&
$filter=CSETOFBKS eq ‘9011’ and KCAMTCOMP ne 0 and CPOSTING_DATE eq datetime’2015-01-03T00:00:00’&
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?
- Prepare report in SAP ByDesign for OData
- Create connection string for OData
- Query report by Power Query function Web.Contents
- Parse response in Power Query, do necessary transformations
Load result to worksheet (or Data Model)
1. Prepare report in SAP ByDesign
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
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)
String with our data
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:
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).
Open Advanced Editor and change the code
Source = Web.Contents(“https://my336123.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPZ9398CB74255DBC51F208B2QueryResults?$top=100000&
buffer = Binary.Buffer( Source ), // buffer to avoid possible repetitive requests
json = Json.Document( buffer )
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
- 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.