SAP Dashboard Query Browser Alternative – BIWS

If you need alternatives to query browser in SAP Dashboards, Consider BIWS (SAP Business Intelligence web services)

Background: Your company has invested millions of dollars and purchased the BI 4.0 suite for reporting and dashboarding.

Problem: You are trying to build a dashboard in the traditional way and now you cannot map Bex variables to excel spreadsheet.

unable to map bex var to excel

What does that mean to a dashboard design? SAP has restricted its customers from mapping the variables from BEx to excel spreadsheet. That means the only we can refresh a query is to use the “Query Prompt selector“. The query prompt selector itself works fine but its always a combo box for selection from user. If the user wants to click on the chart and refresh a query then its not possible. That is when you need to map the variables to the excel spreadsheet.

What can be done?

Creating BI Web Service in BO 4.0

BIWS (SAP BI web services) existed from BO XI 3.1 SP2 onwards. Now its in the lime light as we are encountering different integration challenges with BW and BO. Now I will demonstrate the way in creating the BIWS

step 1: Create a webi report and save it to public folder.(as you might end up sharing the web services)

Remember to be in Rich internet applicatio mode to create a web service.

create webi report based on Bex query

Step:2 Right click on the report block to publish and select on “publish as a web service“.

right click to create Web service

Step 3: Click next. remember the content cannot be duplicate.Click next to select/unselect the  variables to the web service(make sure you include mandatory variables).

 

Step 4: Provide a name to the report block you are publishing

give a name to the block for publishing

Step 5: Provide a name and publish the web service

click to add a name to the web services

Step 6:   Consume the web service in the SAP Dashboard (Xcelsius 2008) in data..>connections..>query as a web service.

import-the-biws

Step 7: Navigate to the input filters section to locate your bex variables.  and now you can MAP them :)

import-the-biws

 Step 8: Navigate to the input Values section and locate the Bex variables. Now you can map the value of the prompt to the excel sheet :)

map-bex-variables1

Remember to map the “refresh” to an excel cell and place a “True” in it.

Step 9

In the output values map the rows to the excel sheet for the output data.

output values

Step 10: Configure the usage options for the connection. Decide on whether you want the query to refresh on open or based on a trigger.

BIWS usage options

 Some Considerations: 

  1. Consuming data in cross tab formats was not possible in the past with QAAWS and Query browser(4.0) but is now possible with BIWS. As cross tab is just another report block in webi.
  2. For passing multiple values to the variables/prompts code the formula in excel as : Sheet 1!A1&”=”&Sheet 1!A2&”=”&Sheet 1!A3.
  3. Apart from variables/prompts coming from the web services, you can also filter the data set using the operators : <,>,<>,in etc…

Some Cons:

  1. Although you can publish multiple report blocks in one web service, you would cannot use just one connection to consume them all. You would need one connection per block for the 
  2. If you are planning to consume a cross tab block in webi report as a web service then make sure the width of the cross tab remains constant.
  3. each connection in the dashboard executes in serial order unlike other connection options (say Query browser, Live office). So you need to do a better design for performance.

 Conclusion: To overcome the integration challenges arising from BO query browser, BIWS can be a good alternative. Although the end to end design of the dashboard might not guarantee the optimum performance because of serial execution.  So judge your options before picking up a strategy.