In this blog post i want to show how to connect Microsoft Power BI to SAP data using OData services.

Mircosoft Power BI

In the Microsoft Power BI desktop app an OData-Feed must be added as a datasource.

The app will ask for the URL. The URL should include the entityset-name and filter-options when necessary like in the screenshoot above.

OData-Service implementation

The GetEntitySet-method must be implemented in the data provider class. For our customer service example it could look like:

CLASS zcustomer_dpc_ext DEFINITION
  PUBLIC
  INHERITING FROM zcustomer_dpc
  CREATE PUBLIC .
PUBLIC SECTION.
PROTECTED SECTION.

METHODS customerset_get_entityset REDEFINITION.
PRIVATE SECTION.
ENDCLASS.
CLASS ZCUSTOMER_DPC_EXT IMPLEMENTATION.

* ---------------------------------------------------------------------------------------+
* | Instance Protected Method ZCUSTOMER_DPC_EXT->CUSTOMERSET_GET_ENTITYSET
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_ENTITY_NAME TYPE STRING
* | [--->] IV_ENTITY_SET_NAME TYPE STRING
* | [--->] IV_SOURCE_NAME TYPE STRING
* | [--->] IT_FILTER_SELECT_OPTIONS TYPE /IWBEP/T_MGW_SELECT_OPTION
* | [--->] IS_PAGING TYPE /IWBEP/S_MGW_PAGING
* | [--->] IT_KEY_TAB TYPE /IWBEP/T_MGW_NAME_VALUE_PAIR
* | [--->] IT_NAVIGATION_PATH TYPE /IWBEP/T_MGW_NAVIGATION_PATH
* | [--->] IT_ORDER TYPE /IWBEP/T_MGW_SORTING_ORDER
* | [--->] IV_FILTER_STRING TYPE STRING
* | [--->] IV_SEARCH_STRING TYPE STRING
* | [--->] IO_TECH_REQUEST_CONTEXT TYPE REF TO /IWBEP/IF_MGW_REQ_ENTITYSET(optional)
* | [<---] ET_ENTITYSET TYPE ZCUSTOMER_MPC=>TT_CUSTOMER
* | [<---] ES_RESPONSE_CONTEXT TYPE /IWBEP/IF_MGW_APPL_SRV_RUNTIME=>TY_S_MGW_RESPONSE_CONTEXT
* | [!CX!] /IWBEP/CX_MGW_BUSI_EXCEPTION
* | [!CX!] /IWBEP/CX_MGW_TECH_EXCEPTION
* +--------------------------------------------------------------------------------------
METHOD customerset_get_entityset.

DATA(osql_where_clause) = io_tech_request_context->get_osql_where_clause( ).
SELECT * FROM kna1
WHERE (osql_where_clause)
INTO CORRESPONDING FIELDS OF TABLE @et_entityset.

ENDMETHOD.
ENDCLASS.

Trade offs in ABAP implementations

The trade offs are relevant when you need to transfer a huge number of entities to your BI system.
Huge range tables
You should avoid huge range tables in our code e.g. range tables, which are filled from a huge internal table. This leads to runtime exceptions CX_SY_OPEN_SQL_DB.
FOR ALL ENTRIES IN
The FOR ALL ENTRIES IN-clause splits a single Open SQL-SELECT-statement into multiple native SQL-SELECT-statements. Compared to a single SELECT-statement this approach often uses more processing time, when the FOR ALL ENTRIES IN-table is huge. The processing time is relevant as OData services will terminate with a timeout when SELECT-statements take to long to load. The timeout is typically configured in the rdisp/scheduler/prio_normal/max_runtime-parameter (rz11).

Disclaimer

Microsoft, Microsoft Power BI are trademarks of the Microsoft group of companies.