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”.
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?
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
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
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.*
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.
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
You may wish to refer the link below...
http://boxug.blogspot.com/2009/09/how-to-publish-xcelsius-dashboard-on.html
-Anil
You may wish to refer the link below...
http://boxug.blogspot.com/2009/09/how-to-publish-xcelsius-dashboard-on.html
-Anil
https://wiki.sdn.sap.com/wiki/display/BI/Expose+BI+Query+as+RESTful+Service
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.
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
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
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
Thanks for your feedback! 🙂
You need to configure this in the Webservice.
-->Refer page no. 31 (2.6 XML Query Result Set – Setup and Configuration)
https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/b5536a7f-0d01-0010-2990-834eed67d6e2
-->Clear Logon Data fields
That is it, next time when you run dashboard it will prompt for logon details.
-Anil 🙂