Transform SAP BW Queries into OData Service: Part 1 – Generation and Activation
I guess until now you might have used SAP NetWeaver Gateway for building people centric applications by unlocking your transactional data stored in SAP backends. But, do you wanted to unlock the analytic content in SAP BW system and build people centric user interfaces for analytics? If yes, then this post will give you a quick overview. In this post I will show how to transform a SAP BW Cube of type Query which can be accessed using MDX into an OData Analytic service.
I have splitted the topic into 3 parts.
- Transform SAP BW Queries into OData Service: Part 1 – Generation and Activation
- Transform SAP BW Queries into OData Service: Part 2 – Analyze OData Analytics Service
- Transform SAP BW Queries into OData Service: Part 3 – Execute OData Analytics Service
Pre-requisites
SAP NetWeaver Gateway 2.0 SP05 – I would recommend using SAP NetWeaver Gateway 2.0 SP05. Nevertheless most of the features are supported from SP04. The examples that shall be shown in this series are from SP05.
SAP BW 7.00 – Well you could use SAP BW release 7.0 onwards for exposing Cubes of type query that can be accessed via MDX. The examples that shall be shown in this series are from SAP BW 7.30.
Recommended Deployment
Ok lets start with the first step …
Generation
OData analytics service can be generated using SAP NetWeaver Gateway Service Builder (Transaction – SEGW). The below image summarizes the steps.
— Click on the Image —
Generation Steps
- Run transaction SEGW
- Create a New Project in Service Builder
- Right click on Data Model and select from the context menu “Redefine->BW Service”
- A three step wizard pops up
First Step
In this step you
- Choose the Access Type as “Controller for MDX (SAP BW)”
- Provide the RFC destination pointing to SAP BW system. If you have deployed IW_BEP in SAP BW system keep the RFC destination as “NONE”
- Press F4 on field Catalog to select the BW catalog that has the cube of type query that could be accessed using MDX
- Press F4 on field Cube to select the Cube of type query that you wish to convert to an OData Service.
Second Step
You could choose to leave the screen with default values. Just fill in a meaningful description for the OData Model and OData service.
In this step you normally specify the OData Model Provider and Data Provider class names and OData Model and OData Service names.
Final Step
Select all Entity Types proposed by the system and press Finish.
The service should be successfully generated.
Activation
The next step is to activate the generated OData Analytic Service. Logon to SAP NetWeaver Gateway Hub and execute the transaction /IWFND/MAINT_SERVICE to activate the service. I assume you are familiar with the transaction /IWFND/MAINT_SERVICE. If not please visit http://help.sap.com/nwgateway -> SAP NetWeaver Gateway Developer Guide -> OData Channel -> Basic Features -> Service-Life Cycle -> Activate and Maintain Services.
Next – Transform SAP BW Queries into OData Service: Part 2 – Analyze OData Analytics Service.
Good One..
A very clear concise and useful reference.
Thanks for posting !
Hi,
Where we actually need to create Odata service?
Server where BI query is present or In Front end Odata
Hi Chandan,
Nice to see your blog. I've exposed my BEx query as Easy Query and would like this as a OData Service.
For this, first of all, I went to TCode: /IWFND/MAINT_SERVICE but didnt my query as service.
Then I tried TCode SEGW and created a project and tried to redefine BW query service with access type "Controller for Easy queries(SAP BW)". Unfortunately I do not find any catalog name using both RFC destination as "NONE" or the RFC destination to the BW system.
Can you please let me know what do i need to do here?
Just fyi, I'm on SAP BW 7.4 SP6
Thanks,
Jomy
Hi Chandan,
I have some queries related this topic. Detail about query is described in following thread :
http://scn.sap.com/thread/3625494
It will be really nice if you solve this query.
Hi All,
I'm trying to redefine BW query service, but here I need to understand what is catalog name.
I have BW query name but I dono where to get catalog name.
Please suggest..,
Regards,
Lokesh.
Hi Chandran,
Thanks for your great step by step tutorial.
I did the service creation from Bex query and consumed the service using SAP UI5.
Here i have some doubts
1.How the Extended Start Schema works here?(in Bex query we can drill down with any Chars but in UI5 we can display data only)
2.What is the advantage of consuming Bex queries using SAPUI5?
3.Restricted Key figures are not Woking while service consumption using SAPUI5?
Also please let me know what are the things can be done on SAPUI5 on BW query.
Thank you.
Hi Chandan V.A,
thanks, very nice article. i created the gateway service successfully, now i'm confused if i want to bind multiple BW Queries into one OData service. i have written a new post multiple bw queries into one Gateway service , would you please give me some advice on that? thanks in advance.
Regards
Billy
Dear Team,
Its really a nice article and i have a doubt here. When we are adding the service in /IWFND/MAINT_SERVICE, system alian for the service is showing as Gateway server. Is it so or we need to maintain BW system alias here ? or both ? which one should be the default one ?
i am maintaining BW system in system maintenance and removed the gateway hub system alias. Now when i am testing i am getting error as "In the context of Data Services an unknown internal server error occurred" any idea on this ?
Hi Chandan V.A,
Thank you for this series of blog posts. They are very useful and helped a lot.
I have a question whether BW Hierarchies with Hierarchy Node Analysis Authorizations are also supported by SAP Gateway 2.0 + MDX 2.0 + BEx Query 7.3 ?
If yes, could you please post an example of how the odata service looks like when a bex query contains hierarchies?
If not, are there some alternative solutions how to access BW Hierarchies via an OData Service? We do not have NW 7.40 so cannot use the option in BEx Query Designer "External Access By OData".
I have also created a separate question for this here: OData Service via MDX for BEx Query - Hierarchy Support?
Thank you very much for your help!
Kind regards,
David
Hello ,
When i tried going to the transaction SEGW, it shows ‘Transaction SEGW does not exist’. What can be the solution for this??
Dear Chandan,
Nice blog ,
But after 4th step , I am getting only one entity and I am not getting parameters or result entity and related association and navigation for the query.
I would like to know where I am going wrong.
Any help would be appreciated.
Hi,
Is it possible to connect 2 queries in a single SAP gateway?
Thanks,
Loed
Hi
I created an SEGW project for a BW query and it worked fine. To the same SEGW project I added another entity and regenerated the class but I am not able to see and Create/Update/get methods in DPC_EXT Class for that entity where as if I create a project without BW query then the methods do gets generated for entitysets in DPC_EXT. what is the best approach if we want to update some values back in Ztable using same SEGW project created with BW query?