In this blog post i want to show how to connect Microsoft Power BI to SAP data using OData services.
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.
The GetEntitySet-method must be implemented in the data provider class. For our customer service example it could look like:
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
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
Microsoft, Microsoft Power BI are trademarks of the Microsoft group of companies.
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.
- SAP Managed Tags:
