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?

What can help?

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

Date Filter ConnectionString

01.01.2015

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’&

$select=CPOSTING_DATE,KCAMTCOMP,TFUNCAREA

02.01.2015

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’&

$select=CPOSTING_DATE,KCAMTCOMP,TFUNCAREA

03.01.2015

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’&

$select=CPOSTING_DATE,KCAMTCOMP,TFUNCAREA

   

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.

Advertisements

One thought on “Bulk extract data 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