Apr
21

Xcelsius and Business Intelligence Web Services (BIWS)

With the limited availability of scalable data connectivity options for Xcelsius, are you constantly seeking  to improve your dashboard performance?  If your answer is ‘YES’, then this post is for you.

Easily connecting to an ‘Enterprise database’ is a well known problem when using SAP BusinessObjects Dashboards (formerly known as Xcelsius). Let’s refer to it as ‘SCDD’ from here on. Traditionally you have two main data connectivity options, namely Live Office and Query as a Web Service, that is until the release of Business Objects 3.1 SP2 which included a 3rd option named BI Web Services.

With BI Web Services (referred to as BIWS going forward), you leverage the web service engine available in Web Intelligence and connect it directly to your dashboard utilizing the QaaWS connector in SCDD.

5 Reasons why you should consider using BIWS:

  1. You can gain simple access via the QaaWS data connection interface
  2. Your data can easily be filtered using the filter properties in the report
  3. You can connect to cached reports to facilitate faster response times and dashboard overall performance
  4. You can filter, drill and prompt from your dashboard without the need to go back to the universe
  5. It will also be available in BI 4.0 (which is what I used for these examples)

To provide insight into the process, I broke it down into a 2 part step-by-step process that includes screenshots.  Keep in mind this was done in a BI 4.0 environment but should work the same in BOE XI 3.1 which is where I originally tested it.

PART 1: SETTING UP YOUR WEBI REPORT AND WEB SERVICE

1.  Launch the Web Intelligence Rich Client. Create your query and basic report. NOTE: Don’t be too concerned with formatting the report at this stage, just make it viewable so that you can review the output.

2.  Save and export the report to the CMS.

3.  In the Webi Rich Client, Highlight a report block and right-click then choose Publish as Web Service. (Note: Report web services are mapped to a report block just like Live Office objects are.)

4.  The Publish Content wizard is then displayed.  Click Next.

5.  Enter the Name of your web service to and click Next.

6.  Define the name of the content block, Set your Filters then Click Next.

7.  Click Publish, and voila! Your web service is created.

8.  Copy the WSDL url and place in a Notepad.  You will need to paste this into the QaaWS data connection in SAP BusinessObjects Dashboards.

The next step is to access the web service from your dashboard.

The BI web service is exposed as the same SOAP interface used by QaaWS. To use the service in your dashboard, you will add a QaaWS connection, Import the wsdl, choose a method and then define the input and output parameters.

PART 2: HOW TO CONNECT TO THE WEB SERVICE FROM YOUR DASHBOARD

1.  Launch SAP BusinessObjects Dashboards.

2.  Open the Data Connection Menu and add a QaaWS connection.  Paste URL into the wsdl URL box and then click Import.

3.  For this example, choose the GetReportBlock_State_Sales_Service from the Method dropdown list.

4.  In the Output Values, bind the table cell to the same number of columns as your web service report block. (Note: Choose as many rows as you think you may need)

5.  Bind the Header cell value to a single row of cells to hold your report block header.

6.  Change the usage to refresh when loaded.

7.  Preview your dashboard.

CONCLUSION: In reviewing the Connection options, you see that using the BIWS is more powerful than using the basic Qaaws connection. You have two methods to use, one for retrieving the full report block the other is for drilling. We will cover drilling in a future post.

TIP: If you want to search for a specific row value, you can set the value to be read from your spreadsheet and bind the operator to the word ‘equals’. There are other options for operator such as ‘greater than’ and ‘less than’.

With BI Web Services and SAP BusinessObjects Dashboards you have a lot of flexibility to provide fast and robust data access to your dashboards, hence improving the performance which is your main goal.

Scott Strool is a Senior BI Consultant for Benchmarkers Business Intelligence, where he helps customers obtain their BI visions. He presented at the 2010 SAP BusinessObjects User Conference in Orlando, FL, and has led many dashboard initiatives. You may follow him on twitter at @scott_strool.

Tags: , , , , ,

45 Responses to “Xcelsius and Business Intelligence Web Services (BIWS)”

  1. Gabe says:

    Great content, Thanks for the step-by-step. Have you encountered and issue with an error that pops up when trying to publish a block from Richi? I got the first time trying to publish the block:

    “connection refused: connect”

    I fixed this by opening Rich Client and click on “Show BI services”. Add a manage server. Click ok and close. Now you will see the server listed under “Host Server”. Click on the dropdown arrow.

    [for the money shot] — UNCHECK “Use Session CMS”

    You are now able to Publish the block.

    Also check these posts for security level issues.
    http://forums.sdn.sap.com/thread.jspa?threadID=1696617
    http://forums.sdn.sap.com/thread.jspa?messageID=10201159&#10201159

    Gabe

  2. Sowmya says:

    Thanks for the step by step instructions. I am stuck in the second step however. When I enter the WSDL URL generated & published from Webi Rich Client in Xcelcius 2008, and hit import, I get “Unable to load URL” Can you please tell me what this means?

    Thanks!
    Sowmya.

  3. Rich says:

    This is a fantastic feature; I encourage everybody to consider this as a data source if they want to leverage WebI’s filtering, and drilling capabilities. See the section titled “BI service structure” in Chapter 27 from the book “Building Reports with SAP BusinessObjects Web Intelligence Rich Client” for more information.

  4. Scott Strool says:

    Sowmya,

    You can verify the wsdl url from the Web Service publisher by just clicking on it. If it is correct it will open your default browser and show you the contents. If you get something like a 400 error (page not found) then the server or port is incorrect. You will need to add a service server prior to creating your first BI web service. This is done from the same BI Services area in Webi RC. Also make sure that you setup the correct authentication. This is part of the url and looks like secAuthenticationType (secLDAP, secEnterprise, secWindowsAD).

  5. patrick delage says:

    Can’t wait to try this.

  6. Kapil Malik says:

    Currently in 3.1 Sp2 there is a huge drawback with this connections i.e you can not send multiple items as a parameter to filter the published block. Has any one else encountered this issue ? Is this fixed in 4.0 ?

  7. scott says:

    Kapil,

    If you want to filter for multiple items for a single dimension, use the Drill function instead of the GetReportBlock. The use the in operator. I will be following up my initial post with how to perform filtering on your queries, coming very soon.

  8. kapil malik says:

    I tested that and it works thanks ! There is very limited documentation on BIWS !

  9. kapil malik says:

    Hi Scott, Is there any time out setting for this service. Some times the query is timing out..

  10. scott says:

    Kapil,

    Actually there is a timeout setting that you can specify. It is done via the Web Service URL Look at the end to see the setting for this query. The default is 60, so if your queries are taking longer than that you may want to limit more data.

    http://MyBOEServer:8080/dswsbobje/qaawsservices/queryasaservice?&cuid=AevRIgO4ovhJjfdjWw3Ow8FQ&authType=secEnterprise&locale=en&timeout=60

  11. Kapil Malik says:

    Hi Scott,

    I have noticed that if the report block have 3 level of drill down for eg. Country>State>City and I consume that drill in BI Services via Drill_Block, everything works fine for first few navigation on the scorecard i.e drill up and down. However, after few minutes of navigating the BI Services just drill to two levels and does not goto the third level (i.e City). I have to restart the dashboard everytime to fix this. Have you come accrosss this issue before ?

  12. Scott Strool says:

    I took a look at this and it works fine in the WS test service.
    I have not implemented a deep drill function in Dashboards yet.
    Which versions are you using, perhaps it is fixed in 4.0

  13. Kapil Malik says:

    Hi,

    I am using Xcelsius 2008 SP3 FP3.4. and BO 3.1 SP2 FP2.7

  14. Nicolas P. says:

    Hello,

    I have a question about the WebI Tier Processing consumption when using BIWS.

    In an enterprise environment where hundreds of users would use an XCL dashboard, feeded by BIWS, what’s the impact on the WebI Processing servers?
    I’m convinced that BIWS is a good alternative to QaaWS but I fear performance issue when several users use the same WebI document via a dashboard. If I understand correctly, there is no cache between the WebI and the dashboard (as opposed to QaaWS with the 2 new servers enabled).

    Do you have any feedback on this potential performance issue? Is there any specific configuration parameters to set?

    Thx for your feedback
    Kind regards
    Nicolas Pirmez

  15. Scott Strool says:

    Nicolas,

    I don’t think a performance issue exists between BIWS and QaaWS, they use the same BI web service engine in BOE. There is also the added benefit of not going to the universe; so in my mind that provides an addtional increase in performance. We have seen a dramatic increase in performance when using the cached webi report and fetched rows from it instead of re-querying the data from the universe. This solution is not for every new dashboard solution. For example, if your solution requires row level security that only the universe can provide, then you will need to either use QaaWS or publish user/group specific webi reports and connect your dashboard to specific report instances rather than latest instance.

    Hope this clears things up a bit for you.

  16. [...] a follow up to my blog on BIWS, I created two videos to show you the concepts of creating and consuming BI Web Services. The [...]

  17. Ken Hartman says:

    We are trying to leverage BIWS for our dashboarding initiative. We have a BOBJ Enterprise 3.1 SP 3 / FP 3.5 clustered environment that sits beneath a Tivoli Access Manager for load balancing. We noticed that when we tried to input “https” when creating a new host server the connection did not authenticate. Has anyone else run into this issue? Also, if anyone is leveraging the LCM to migrate the QaaWS URL from one environment (DEV to QUAL) to another have you run into any issues with the WSDL?

  18. Joe Caparula says:

    The struggle I’m having is enabling QaaWS rights on the CMC. Even though I’m working with BIWS in WebI Rich Client, I still get the same rights error (QWS 2718) that I get when trying to set up a universe query in the QaaWS client. We are on BOE 3.1 SP3 . . . does anyone have any guidance on how to enable these rights?

  19. bobjboss says:

    Hi
    I am working on SAP BO XI 3 With SP3. I am unable to implement BIWS. Here are the stpes which i did , please corret me or can you send a good document to implement BIWS.

    1) I developed a WEBI RIch client report.
    2) Saved the report on my desktop.
    3) exported to CMS
    4)Then i selected entire table ( webi richclient report out put) -> right click there i cant see publish as a webserices option. there i am able to see publich block as a first option.
    5) i choose publish Block
    6) then it pop with one new wizard which is publish content
    7) then i click on next
    8) it was asking me to give a name and description under the report content , there i gave Test_BIWS as name and description.
    9) then i did filter setting,, there i am seeing host server as my host server name(default).
    10) clicked on next
    11) it was asking me to select a location where do you want to save webserices
    12) i selected a folder and clicked on create
    13) then it was asking me give a name and description for webservices name. Here i gave Test_BIWS as a name and description
    14) i choose my authentication
    15) click on ok
    16) then i got a pop saying that your webservices as created sucessfully
    17) then i click on finish
    18) then i went back to TOOL-> manage BI Services ->
    19) here i not able to view my webiservices which i created at step no. 16 ( Test_BIWS).

    Please let me know what is worng in my process. And can you help me.
    I think here i have to catche the Webserices URL and i have to use that URL in my dashboard using Qaaws connection.

    1) Is it not possible to implement on WEBI Report ?
    2) Is it not possible to implement on XI 3.0 ?

    If possible can you shoot me an email or steps document or best document to implement BIWS to my email please
    (email removed)

    Thnaks in advance.

    Thanks
    Bobjboss

  20. Femke Kooij says:

    Hi,

    Anyone found a solution for when you have a pivot table in WebI with variable number of columns?

    If I select eg. 12 cells (4 columns, 3 rows) , but my source pivot table consits of 3 columns and 3 rows, the first cell of the second row will be placed in the fourth column of the first row. :-(

    Thanks!

    - Femke

  21. Mico Yuk says:

    Femke,

    That’s the way it works unfortunately. Quite a pain.

    Mico

  22. Amit says:

    Hi Guys,

    Thanks for the article,

    I am using BI Web Services in WEBI Rich client to connect webi reports to xcelsius.

    My WEBI report is having two propmts and the prompt values I want to give from Xcelsius as an input parameter.

    In xcelsius I have taken two cells for the parameters so whatevr I will select in the list box the destination of these will go to each of the cells and I bind the cells to input values parameter in the QAAWS data connection in xcelsius in the main report.

    The input value of a prompt is having two things
    1. value of prompt
    2 index

    The method I am using is Get Report Block
    I have bind my excel destination cells to value of prompts respectively.

    But my data does not chnge when I change the values in my list box.
    I have refreshed the data using the option in data connection also using trigred cell option I have used, but noluck

    Am I doing any thing wrong?
    If we have any reference point on this topic plz provide me the links.

    Thanks in advance

  23. Seema says:

    Hi,

    What is the minimum Xcelsius version we need to utilize BIWS?
    I have Xcelius 2008 SP3 but I get error “Unable to load URL”.
    If I type the URL in a browser I see the BIWS details.
    I suspect that I need later Xcelsius version.

    Thanks,

  24. Mico Yuk says:

    You do not. Please read the comments above to see how others overcame this problem. Thanks!

  25. Seema says:

    Went through the thread. still getting Unable to load URL. I am able to publish and then even see the details when opening the link in a web browser.
    I can use other QAAWS created via QAAWS Designer.
    I am choosing QAAWS connection in the Xcelsius and then I provide the URL but I get the error.
    Any help. I am creating a POC for my company to start using this great feature.

  26. Scott Strool says:

    This message will also be displayed when the authentication type is wrong. Please check that the type selected is correct for your server configuration.

  27. Seema says:

    I found the issue. When I create BIWS, it shows the URL
    http://xyz123:8080/dswsbobje/qaawsservices/biws?def=1&cuid=Fmo8pk5bdAUAynUAAJBbIVwXACZVfVl4 in the properties.
    I was using this exact URL in Xcelsius and I wasn’t able to import it.

    Found the solution on one of the forum.
    I need to replace def with wsdl in the URL and Now i am able to import.

    http://xyz123:8080/dswsbobje/qaawsservices/biws?wsdl=1&cuid=Fmo8pk5bdAUAynUAAJBbIVwXACZVfVl4

    Now If anyone has details of all the input values in any form of document or link, can you please share?
    I want to experiment more with available options there.

  28. Patrick says:

    I am having issues getting data to come back when I use a prompt defined at the Business Layer (BI4.0) as an @Prompt. The report runs fine in WebI but when I try to use the BIWS to bring the data into Xcelsius I am having no luck at all.

    Thoughts?

  29. Jeff says:

    I am very frustrated with the way BI Web Services filters blocks of data, especially going againts WebI reports created off a BEx query, here’s my problem. In order to filter the block of data in your BI Web Service (and of course they don’t tell you this in the documentation) you must add the dimensions you want to filter on in the results set of the WebI query. My issue is that I don’t need the dimensions added directly to my block but since this is an OLAP connection the first time I filter the block of data I get #TOREFRESH error and no matter how many times I refresh after that the error still displays both when I’m testing the Web Service and on the DD side. The only way to get around that is to include all the dimensions I have in my WebI query in the block but then my block will display at the wrong aggregation level and it’s useless. We are on BI 4.0 SP2 and we have another client who is having the same issues. Not to mention any prompts that were built on the BEx side do not come through to the BI Web Service, to me this is a major disadvantage when trying to leverage BI Web Services and BW/BEx or any OLAP for that matter. I’m doing a pilot for a customer and we are about to open an SAP ticket so any comments or suggestions on the matter are greatly appreciated.

  30. Ramesh says:

    Scott,

    Very good post. Could you explain whether I can BIWS for the following need

    1) I need to fetch data using a OLAP universe (essbase) on a monthly basis for my KPIs used in Dashboards
    2) Due to no of connection restriction on essbase we do not want to send any request there after to DB
    3) I need to filter the data based on another webi report/query. ex: user login as usr1 need to pick up his corresponding role and location and fetch KPI data related to his role/location.

    Can this be acheived?

  31. Cheema says:

    Very useful Post!! Thanks for sharing your great work.

    One question though, is there any way that I can get the data on the dashboard from Latest Instance of the webi report instead of the published instance?

    The reason I am asking is because I developed and published one Monthly dashboard in Prod using BI web services and everything is working fine except that every month I have to manually open the backend webi report in Infoview and refresh the report and then save it as a new published instance. This is painful and not efficient.

    I created an event based on the database touch file and the backend webi report does get scheduled automatically on monthly bases and created historical instances… but the BI web services doesn’t display the data from the latest instance…instead it shows the data from the last published instance.

    Any Idea how can I achieve this? Any help will be appreciated.
    ?

  32. Femke says:

    Hi,

    Anyone know what type of value you have to bind to the index of a Webi prompt in BIWS?
    As I do I database ranking in my webi I can’t use scheduled report and need to filter the data on database level. So I use database ranking. This works well, however when I Built a BIWS and want to trigger it, it is asking me for promptValue and an Index. Wha’t is this index (a number? a value? an operator?, leave it blank?) I ttried multiple things but cannot get it to work, also I used the refresh = true but that does not trigger the prompt as well.

    Suggestions are appreciated here as a reply or by email femke at femkekooij.nl

    Thanks

  33. Senthil says:

    Thanks for usefull information posted.

    I have created Xcelsius dashboard using BIWS. In the current state, it takes more than 35 seconds to download the required data. There is need to bring the dashboard response time below 15 seconds. Is there any advise, to get still better performance (faster data reterival) from BIWS. I am using BOXI 3.1, and hardware is reasonably sized and can be scaled up if the performance would improve further.

  34. V says:

    Hi All,

    When i use the BIWS , unable to get the latest data from the WEB intelligence.
    I have refereshed the data and also save the WEBI document.

    the data is not getting refreshed.

    Can anyone please help.

    thanks

  35. sat says:

    Cheema,

    One question though, is there any way that I can get the data on the dashboard from Latest Instance of the webi report instead of the published instance?

    it gets data from latest instance only. Whenever you schedule webi to get latest instance, automatically that’s what you get

  36. Jorge says:

    hello,

    there any way to make DAAWS used the Xcelsius Cache, i don´t , maybe change xml of QAAWS, anybody know?

    any idea anybody?

    thanks everbody,

  37. Guido says:

    Hi all,
    i’m working with a BIWS to put data into an xcelsius dasboard. The original Webi hold about 600 rows by 16 columns.
    I’ve defined a “QAAWS” connection, using the url of my BIWS and using the method “GetReportBlock_blockname”.
    It seams as if there is a limit to the maximum number of “cell” (rows * columns) that may be retrived by the BIWS.

    Where may i found the settings that may help me to configure the BIWS?

    Thanks,
    Guido.

  38. Sylvia says:

    Hi,

    I have had success using the webservice, but it seems like there is a limitation. I need to use multiple web service connections on my dashboard, but I can only add 2 the add web service connection is disabled. Anyone else have this problem? We are using Xcelsius Engage 2008 5.4.

    Thanks,
    Sylvia

  39. amissa says:

    Can I use Universe Filters in Web Service and connect in Xcelsius, or do they have to be filters created in WebI?

  40. Alex says:

    I am using BIWS in dashbaord design 4.0 and BI 4.0. I often get error message when i re-open dashboard after opening it and leaving it for some time.
    The error message i get is ” Cannot Access External Date”
    “Web Intelligence does not exist or you do not have rights to view this document: Name=Dateparameter_LOV1, CUID=AwKaer,l….”

  41. shailaja says:

    Guido,

    Did you check your Xcelsius settings? It supports only 500 rows by default. You might have to increase that.

  42. shailaja says:

    Hi,

    Can anyone let me know how to change the default host server in BI services? I’ve configured my old server name as default and we are not using that anymore. So I end up editing the host server manually everytime.

  43. Amit Kadam says:

    Hi
    I am getting unknown error while trying to Publish block from webi rich client. This report is build from universe created on top of Bex query.
    I m able to use QaaWS without any error but with BIWS i m getting this error. I need to use BIWS since it has filter capability on scheduled reports thus will improve performance in my dashboard
    Please help!

  44. Jason says:

    Hi,

    I am having problems tring to use the LDAP authentication with the BIWS?

    Is there anything special you need to do to get Xcelsius to read the user name and password provided and use the domain of the server to connect to the web services?

    Any assistance would be great?

    thanks
    Jason

  45. pepelubarnus says:

    Hi,

    We are having some difficulties to retrieve last user instance webi report with BI Web Services. If the last instance is from that user we can retrieve it, but if it is not we get an error message “A document with this name does not exist”. We are using BOXI3.1 SP5. We think that it is related to advanced user right, but we don’t know exactly which rights we should grant to use this feature. SAP suggest us to add user to QaaWS Group Designer, but this does not resolve the issue.

    Do you have any suggestion?

    Many thanks.