In this post you will find an idea how to pull data from SAP ByDesign directly to Power BI. This is a totally self-service solution, which can be built if you have literally nothing. Only you, Excel, free license of Power BI and SAP ByDesign tenant(s).
Samples based on data from test tenants that were opened for participants of openSAP course “Reporting with SAP Business ByDesign“.
Couple of words about SAP ByD for those who are not aware what is this. Cloud ERP system, has own real-time analytics engine, quite powerful, but poor. Poor calculation capabilities, poor visualization capabilities. Handles only basic things, obvious calculations.
Moreover, if you have several SAP ByDesign systems in responsibility you most probably have to consolidate data somewhere. Build sub-regional, regional reports etc.
In my case I work with a “farm” of SAP ByD systems, so for me the task is multi-tenant SAP ByD Reporting.
In such case single SAP ByD system cannot help with consolidation problem. You may say SAP BW, HANA, BusinessObjects is a way… yes, but here I talk only about self-service solution.
So, let’s start.
What can we reach in the end?
Typically would be great to have a dashboard(s) with some KPIs, refresh of which is scheduled.
SAP ByD is in own cloud, Power BI is in own cloud. Sky is common. So let’s make these two clouds be friends.
Architecture of solution must be very simple. It is a must condition for self-service solution, otherwise it is not transferable from one analyst to another.
Possible solutions:
OR
I will use Excel for demo solution. In the end of post you may find link to sample workbook.
Sorry, my version of blog doesn’t support iframe. I can share only link on page with interactive report – Result in Power BI
Visualization is not a goal of this post. What I would like to show – is an opportunity to use scheduled refresh
As sourcing system hosted in web, we don’t need Gateway.
User credentials can be used instead.
Three important points on this pic: Url, Basic, oAuth2.
oAuth2 – simply don’t know how to configure access on SAP ByD side. Good for you if in your organization IT department can help you with this type of access.
Basic – user name and password – perfectly match for self-service solution. Using basic credentials user has access to the same list of reports through OData.
Url – detailed link. Unfortunately, in a moment there is no opportunity to use one pair login / password per Dataset.
Also, would be great to have a choice how to apply credentials per dataset
Tenant level: https://my336124.sapbydesign.com/
(1) Or Odata level: https://my336124.sapbydesign.com/sap/byd/odata/
Because in the same dataset we can have request to web services
Like https://my336124.sapbydesign.com/sap/bc/srt/scs/sap/querysalesorderin3
https://my336124.sapbydesign.com/sap/bc/srt/scs/sap/querysalespricelistin
Etc.
In post Query SAP Business ByDesign Web Services via Power Query I described how to pull data through web services.
And as far as I remember, even although Basic type of Authentication method contains Login / Password, web service doesn’t accept them. It expects base64 string in http query header “Authorization” (not a 100% info, I just use this because SOAP UI works so).
If take this remark as truth, Web services should be accessed in Anonymous mode. In addition, pair login / password usually differs from business user’s.
Therefore, we need separate level of credentials application:
(2) e.g. https://my336124.sapbydesign.com/sap/bc/srt/scs/sap/
Note: option to choose level of credentials application was added to Power BI Desktop. Look forward to have it on Power BI and in Excel.
From my experience levels (1) and (2) sort out all problems.
Can’t wait to have this option in Power Query and Excel 2016.
OK, when credentials entered, we can turn on Schedule Refresh
If you don’t want to wait, know that data updated in the system and you want to update them on Power BI – press Refresh Now
Technical details of Excel solution
If you don’t want to read this – download sample workbook.
Key – is ability to pull data from SAP ByD using Odata. Here you may find comprehensive technical guide from Knut Heusermann. If need more – scroll through my blog to find more tricks.
What I already revealed, in case of SAP ByD “Web.Contents” function works faster than “Odata.Feed”.
Probably, because Odata.Feed pulls data in XML format, but when use Web.Contents we can request $format=json, which has much less size.
Ideal solution would be dataset on Power BI that can be set on scheduled refresh. Sounds very attractive “I created data model, loaded to Power BI, built visualization, report, dashboard and shared with colleagues. Then Power BI handles refresh of data model. Me and my colleagues see actual data every day without additional effort from our side”.
Don’t need to set up any refresher of Excel files, separate workstation, Power Update and so on. You don’t need this, almost…
// Drinking morning coffee General Manager asks laptop / tablet / mobile: “Hey, Cortana, show sales report, please… Top5 customers in current month… Gross margin year to date”. Oops, this is not ready yet. Look forward.
Let’s try to go through sample of Excel solution.
Try to think one-two steps forward. First of all, three simple questions
How Excel-model will be imported to Power BI: from local file / OneDrive ?
What if data model should be changed later?
What requirements Power BI has for usage of “Schedule refresh”?
Two important pages in Power BI documentation
https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/
https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-excel-file-local-drive/
As many people, I hadn’t read them until I faced issues that couldn’t solve. I don’t recommend use such practice. High-five if you use though :-).
Shortly: OneDrive is better than load from Local File because
What requirements Power BI has for usage of “Schedule refresh”?
Several things that makes development harder. Following points makes it unable to refresh dataset on Power BI
- Power BI cannot ignore Privacy levels
- cannot parse folded functions, e.g. Binary.Buffer( Web.Contents( … ))
- Web.Contents added through Table.AddColumns. This is really sad. When several systems should be queried in the same time it is not convenient to create separate query for each system. I suppose this restriction can be extended to rule “one query must contain one data source”, this is my feeling after several tests.
- If Power Query pulls data from worksheet of same workbook
OK, assume we already know this.
Let’s build ConnectionString that describes our query to SAP ByD.
I use for this purpose specific file, which simplify the process. Just enter Tenant ID and Report ID. Macro will refresh Metadata query.
Using metadata sheet we can, first of all, understand what fields already available in report. If something is not available – we can open report in web browser and use “Add Fields” option. Attributes of characteristics also become accessible through OData, and even filterable.
Copy field names from metadata to column “List of fields (Names)”
Ideal solution would be form with checkboxes, where user could select necessary fields, press OK and receive result. It is possible to do with VBA and Power Query, but I couldn’t find time to develop this yet. Use “lean solutions”.
OK, we got connection string and two columns
“SQL Name” – resulting name that we want to see after Power Query M-magic
Field ID – original ID of field in OData
Will use them in RENAME_TABLE.
Unfortunately, Power BI doesn’t support refresh from worksheet. Hence, we must build table somehow directly in code.
In Excel this can be done e.g. using Table.FromRecords
RENAME_TABLE = Table.FromRecords( { [OldName=”COEDREF_F_ID”, NewName= “Source Document ID”], [OldName=”TOEDREF_OBJ_TC”, NewName= “Source Document Type”], [OldName=”CDOC_DATE”, NewName= “Source Document Date”], [OldName=”KCAMTCOMP”, NewName= “Amount”], [OldName=”CPOSTING_DATE”, NewName= “Posting Date”], [OldName=”TFUNCAREA”, NewName= “Functional Area”], [OldName=”CGLACCT”, NewName= “G/L Account”], [OldName=”CREVERSEDIND”, NewName= “Reversed”], [OldName=”CREVIND”, NewName= “Reversal Journal Entry Indicator”], [OldName=”CBUS_PART_UUID”, NewName= “Business Partner”], [OldName=”CACC_DOC_IT_UUID”, NewName= “Journal Entry Item”], [OldName=”CACC_DOC_UUID”, NewName= “Journal Entry”], [OldName=”CACCDOCTYPE”, NewName= “Journal Entry Type”], [OldName=”TACCDOCTYPE”, NewName= “Journal Entry Type Text”], [OldName=”CDOC_CANC_IND”, NewName= “Cancellation Invoice Indicator”], [OldName=”CDOC_INV_DATE”, NewName= “Invoice Date”], [OldName=”TDOC_PROC_TYPE”, NewName= “Processing Type”], [OldName=”CDOC_UUID”, NewName= “Invoice”], [OldName=”CDPY_BUYER_UUID”, NewName= “Account”], [OldName=”CIP_SALES_UNIT”, NewName= “Sales Unit”], [OldName=”KCITM_NET_AM_RC”, NewName= “Net Value”], [OldName=”KCITM_TX_AM_RC”, NewName= “Tax”] } )
In Power BI Desktop – using static data tables. They actually also converted into binary after input… But easier to maintain table than such ugly string as above.
In my sample, I load General Ledger from two tenants and combine them.
In addition, I load Invoice Volume report.
In the end I have
 |
 |
 |
Data loaded to Data Model, where you may find simplest demo measures.
Link to sample workbook
Feel free to re-use this idea for your system(s).