A question that we frequently receive is how can I filter out data before it gets to Microsoft Flow? The answer to this question is: OData filter queries. In this blog post we are going to cover some of the most popular OData filter queries using some of our most popular connectors including SQL Server, Dynamics 365 and SharePoint Online. If you are intending to use OData queries and expand query parameters, then here is a great post by Lin Zaw Win on how to do it: Power Automate List Records – Use Expand Query to Retrieve Related Data in flow; CDS (current environment) connector’s “List Records” action has a maximum buffer size configured.
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
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 )
- For demo I use tenant from openSAP training: https://my336123.sapbydesign.com
- 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
- 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
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’& http://www.durnareven1980.simpsite.nl/wizard-of-legend-download-for-mac. $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.
Odata Query Cheat Sheet Nintex
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
- 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 centerAnd create new as copy of existing reportProvide a name that will allow to identify this reportRemove tick from “Copy With Navigation” checkbox. Keep it only if you really need this.Next step is very important – select key figuresSelect “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 figuresAdd 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 stringOur goal is to get something likeCOEDREF_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 MetadataAs new report was manually created it got unique ID – find it in Business Analytics work centerAccording to guide: OData for SAP Business ByDesign AnalyticsWe should build following string to get report metadata (mainly for field IDs and names)String with our dataIn Firefox it shows meActually, 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 metadataThen 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
Snapchat for mac without bluestacks. 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).
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
Windows 10 taskbar not working after update.
Windows 10 taskbar not working after update.
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).
Odata Query Cheat Sheet
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.
Odata Filter Contains
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 fieldsCheck data type of every fieldConvert date fields into normal dateConvert time fields (if any)Probably – clean data, e.g. replace “Not Assigned” or # with nullUse 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 here5. 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.