Skip to Content
Actions

Xcelsius and SAP BI – How to pass variable value using Web Service Connection

June 16, 2009 | 42 Views |
 anil bavaraju
Follow

In continuation of my article on “How to Integrate Xcelsius 2008 with SAP NetWeaver BI without using Integration kit”, in this blog you will learn how to pass variables values to SAP BI Query from your dashboard using Web Service Connection.

Create a Query:

Create a simple query with a User input Variable.

For example:

Infoprovider name (Cube): ZSDN_DEMO

Query: ZSDN_DEMO_Q001

Variable: ZDM_CMON (Calendar Year/Month – Mandatory)

Testing Web Service Connection:

  • Test the connection by passing query, Infoprovider and Variable details.

Refer to WEB API commands (Slide No 42)  Web Services within SAP NetWeaver 2004s BI

Creating Web Service Connection in Xcelsius:
  • Click Data Manager                       
  • Add Web Service Connection.                   
  • Pass the WSDL URL and click IMPORT
  • In the Usage tab of the connection check the box Refresh on Load

    Input Values

  • Add 3 Items by clicking “+” (GetQueryViewData –> Parameter –> Item)

           

  • In Excel sheet (Xcelsius) the provider name, query name and Variable name are maintained.

        

  • Infoprovider (Select the cell with Infoprovider details by clicking ):  ZSDN_DEMO
  • Select Name (under 1st Item) and select the “VAR_NAME_1”                    

          Value: Select the cell by clicking (ZDM_CMON)

  • Select Name (under 2st Item) and select the “VAR_OPERATOR_1”           

          Value: Select the cell by clicking (EQ)

  • Select Name (under 3st Item) and select the “VAR_VALUE_EXT_1”           

          Value: Select the cell by clicking (200810)

  • Similarly select Query and select the cell with Query Name by clicking ): ZSDN_DEMO_Q001

Output Values

Follow steps….as described in Article (for Output Values).

Developing dashboard:

Three components are used:

  • Input Text: Check the “Insert data on load” box and map the cell that has the Variable value (i.e. Cell B6), so when the user inputs value on dashboard and clicks on refresh, this value will be passed to the query.
  • Spreadsheet Table

          Display Data: Sheet1!$E$3:$H$18 (as shown below)

  • Refresh Connection button

           Properties: Check the connection box (General tab) 

        

  • Click preview/Export the visualization to the required format.
  • Enter BI login credentials.

         

Dashboard 

 

 

Notice that the aesthetics of the dashboard were not given lot of attention. In this example, more time was spent building the interface and troubleshooting the errors than visual presentation.

To check if the dashboard is getting real time data, enter different calendar year and click on “Refresh”.

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  1. John Skrabak
    This is good stuff.  I am a a little confused on thw web servie side of things.  Though I haven’t looked into it closely, I thought I had read that the web service created on a BW query was “too complex” to be consumed by Xcelsius, but obviously if you are doing it successfully, that can not be the case.

    At his point thoug, I’ll probably wait, as Xcelsius 2008 SP2 is supposed to have a BW query web service data connector.  Haven’t seen much in the way of details other than the Xcelsius SP2 User Guide.  Based on some other stuff I have seen, I am wondering if the BEx query web service that Xcelsius will consume in SP2 requires that BW EHP1 be installed.  Any insight?

    (0) 
    1. Ingo Hilgefort
      Hello John,

      your assumption is correct that the standard web services from BI are too complex for Xcelsius to understand them as is / out of the box.

      the exact requirements for the new Xcelsius connectivity will come out when it is available but EHP1 as a minimum is for sure a safe assumption.

      Ingo

      (0) 
      1. Ray Li
        Ingo,

        In fact we had done an initiative in SDC to access BW data directly from Xcelsius via web service.
        Besides using BW’s own web service (QUERY_VIEW_DATA etc), we also wrap ABAP calls as well as BW web service in a custom web service, which can be directly access by Xcelsius.

        Ray

        (0) 
        1. Anil Bavaraju Post author
          Yes, it is complex for the Xcelsius to understand and even to develop, however after banging our heads we could achieve lot of things. It is more than a year that our team had been working and providing solutions based on this approach, and now we feel this approach is very easy.
          1)We could show real time data in the dashboard.*
          2)User can input variable value to refresh data.
          3)We could show hierarchy data.
          4)Published on the portal.
          5)Users personalized dashboard, user has access only to the data that is relevant to him/her.*
          (0) 
          1. Brinto Roy
            Hi Anil,
            Great to know that you have succeeded so far!
            Just a few questions:
            1)You have written,u can show hierarchy data..in hierarchical fashion? How?We have similar requirement in our project.
            2) How to u publish Xcelsius DB to portal?by creating IViews?
            3)How to show only authorization relevant data to a user?If we have BI authorizations on the base query,will those authorizations also reflect in the Dashboard based on those queries?

            thanks in advance,
            Brinto.

            (0) 
            1. Anil Bavaraju Post author
              1)You have written,u can show hierarchy data..in hierarchical fashion? How?We have similar requirement in our project.
              Anil- I will try writing a blog/article on this…it is tough for me to explain it over here.
              2) How to u publish Xcelsius DB to portal?by creating IViews?
              Anil- There are different options to do publish, one is through i view (static dashboards). However we wanted to show live data, so we embedded it into a web template and then published the webtemplate on to the portal.
              3)How to show only authorization relevant data to a user?If we have BI authorizations on the base query, will those authorizations also reflect in the Dashboard based on those queries?
              Anil- You need to define authorizations at query level, when user runs the dashboard it will prompt to enter the user ID and password. Then it will fetch data that is relevant to the user.

              -Anil

              (0) 
  2. H. van der Haar
    Hi Anil,

    On july 20 you wrote:
    “…. so we embedded it into a web template and then published the webtemplate on to the portal.”

    How did you embed a Xcelsius Model into a SAP WebTemplate?

    Henk.

    (0) 
    1. Anil Bavaraju Post author
      Henk,
      There is a workaround for this.
      I will post a blog/artile on this very soon…and will update this space as soon as it is published.

      -Anil

      (0) 
  3. Jennifer Tran
    Hi Anil,

    Very helpful blog.  In your example, when you click on preview mode for xcelsius – it is prompting you for a login & password to establish the web service connection.  In our environment, when I click on preview mode in xcelsius, it is not prompting me for a login & password.  It expects that a connection has already been estalibhsed for my query to run successful.  Is there some configuration on our side that we need to tweak to turn on login id & password prompt?  Any helpful hints would be apprecicated.

    Jen

    (0) 
    1. Julius von dem Bussche
      Perhaps you are using SSO or logon tickets?

      I guess your login module stack is configured differently or in the ABAP system is used as UME store then you are not forcing the login screen (which is optional).

      Cheers,
      Julius

      (0) 

Leave a Reply