Summary : The first part gives an idea to handle REST API like ODATA in SAP HANA from any external application and the second part will describe how to consume ABAP data via SOAP API in a reporting tool like power BI
Objective:
In this article, we will look at how to consume REST API call from SAP HANA to an external application to by creating input parameter in XSODATA inside HANA which in turns access data from a OLAP calculation view.
As I could not find any details in SDN or SAP help document regarding passing input parameters especially in following cases.
Now let us deep dive to see on above bulletins ..
How to call parameter ODATA services in HANA from external application?
Often, I see people asking in SDN community on how to pass date as input parameter in recognized format to ODATA service. I was also struggling initially until I found solution in ODATA official documentation website and with Google’s help found a way to pass input parameter to an ODATA service as well
Datetime is the recognizable format that we need to pass in ODATA service always, where timestamp ( HH:MM:SS ) is optional.
Whereas input parameter to XSODATA artifact is being implemented using a property “parameters via entity “ as below in HANA studio repository perspective.
ODATA webservice call in Browser as below, where Input_Param in the URL is the property that was defined in the above .xsodata artifact:
https://www.dummy.com:4300/PK_Sales_data/Webservices/SALES_DEMAND.xsodata/Input_Param(IN_LEAD = '3052',IN_DATE_FROM = datetime'2015-07-01',IN_DATE_TO = datetime'2015-08-31')/Results?
Reporting tool frequently query the back-end database to get results for multiple items. For example, the end user request sales history for set of specific items from the reporting tool via web-service call.
Multiple values to a column in input parameter is working only with graphical view (SPS12),when values are passed in web-service URL like below example
https//www.abcded.com:4300/PK_Pricing_data/Webservices/SALES_DEMAND.xsodata/Input_Param(IN_LEAD = '3052,7475',IN_DATE_FROM = datetime'2015-07-01',IN_DATE_TO = datetime'2015-08-31')/Results?
Trick : In order to make your HANA calculation view recognize multiple values coming from REST API input parameter ( webservice all ) , you have to use in () function in the filter expression of HANA graphical view . More importantly there should not be any quote for input parameter used in filter. Usually when the input parameter is dragged from the element box , they comes within single quotes.
There are many API testing tool available, POSTMAN is one such simple way to test REST API. It can be easily downloadable via Google. Create a request providing the API URL link with all required input parameter like in step 2 then give basic credential to authenticate under Authorization tab. Best way to test API functionality. More details can be seen in this article
Microsoft power BI is a visualization tool that is easy to analyze data from any device at any time and has free version to download and analyze the data.
After downloading the client, click on Home-> Get Data -> Odata feed to provide the API link along with the input parameter value to create a query . Once after the data is being loaded, you can create your own dashboard and publish it for daily usage.
COMING SOON Part 2 – Covering SOAP API ( SAP ABAP RFC FM ) into ODBC connection for reporting tool like power BI
Objective:
In this article, we will look at how to consume REST API call from SAP HANA to an external application to by creating input parameter in XSODATA inside HANA which in turns access data from a OLAP calculation view.
As I could not find any details in SDN or SAP help document regarding passing input parameters especially in following cases.
- How to call parameter ODATA services in HANA from external application?
- How to handle multiple values in ODATA services in HANA ?
- How to test the API using 3rd party tool before deploying in UI?
- How to consume REST API in Power BI for reporting?
Now let us deep dive to see on above bulletins ..
How to call parameter ODATA services in HANA from external application?
Often, I see people asking in SDN community on how to pass date as input parameter in recognized format to ODATA service. I was also struggling initially until I found solution in ODATA official documentation website and with Google’s help found a way to pass input parameter to an ODATA service as well
Datetime is the recognizable format that we need to pass in ODATA service always, where timestamp ( HH:MM:SS ) is optional.
Whereas input parameter to XSODATA artifact is being implemented using a property “parameters via entity “ as below in HANA studio repository perspective.
ODATA webservice call in Browser as below, where Input_Param in the URL is the property that was defined in the above .xsodata artifact:
https://www.dummy.com:4300/PK_Sales_data/Webservices/SALES_DEMAND.xsodata/Input_Param(IN_LEAD = '3052',IN_DATE_FROM = datetime'2015-07-01',IN_DATE_TO = datetime'2015-08-31')/Results?
- How to handle multiple values in ODATA services in HANA ?
Reporting tool frequently query the back-end database to get results for multiple items. For example, the end user request sales history for set of specific items from the reporting tool via web-service call.
Multiple values to a column in input parameter is working only with graphical view (SPS12),when values are passed in web-service URL like below example
https//www.abcded.com:4300/PK_Pricing_data/Webservices/SALES_DEMAND.xsodata/Input_Param(IN_LEAD = '3052,7475',IN_DATE_FROM = datetime'2015-07-01',IN_DATE_TO = datetime'2015-08-31')/Results?
Trick : In order to make your HANA calculation view recognize multiple values coming from REST API input parameter ( webservice all ) , you have to use in () function in the filter expression of HANA graphical view . More importantly there should not be any quote for input parameter used in filter. Usually when the input parameter is dragged from the element box , they comes within single quotes.
- How to test the API using 3rd party tool before deploying in UI?
There are many API testing tool available, POSTMAN is one such simple way to test REST API. It can be easily downloadable via Google. Create a request providing the API URL link with all required input parameter like in step 2 then give basic credential to authenticate under Authorization tab. Best way to test API functionality. More details can be seen in this article
- How to consume REST API in Power BI for reporting?
Microsoft power BI is a visualization tool that is easy to analyze data from any device at any time and has free version to download and analyze the data.
After downloading the client, click on Home-> Get Data -> Odata feed to provide the API link along with the input parameter value to create a query . Once after the data is being loaded, you can create your own dashboard and publish it for daily usage.
COMING SOON Part 2 – Covering SOAP API ( SAP ABAP RFC FM ) into ODBC connection for reporting tool like power BI
- SAP Managed Tags:
1 Comment
