Connecting Web Services to SQL and then SQL to Cognos Framework

[CalebMF]

Hello!
I current have 2 data sources that I do not own the intellectual property of the data on the servers they are stored on. When speaking with the companies who do own the servers, the gave me the option to get to the data through Web Services.
I have read little on this and was curious if there is anyone here that can help me with first hand experience.
My goal is to connect web services to SQL Server, so I can then connect SQL Server to Cognos Framework like normal.
Has anyone done this? What would be my first step in connecting Web Services to SQL Server?
Thank you

[blom0344]

Basically you need to perform a call to the webservice. MS SSIS has components that allow reading webservices. This basically means using an ETL process to populate a database with data from a webservice. With a SQL server license you get SSIS for free, but it quite often not installed (check this)  A tool like SOAP UI (http://www.soapui.org/) can help you to determine how the XML from the Webservice is structured. Some webservice operations expect Id's as input for the call, others may generate all data in one go. Reading data from a webservice is SERIOUSLY slow compared to a traditional query against database tabels, but with small datasets this may not be a problem.
I personally use the Community Edition of Pentaho Data Integrator for ETL and this also allows reading webservices. As anything is possible with XML it is mostly a matter of finding out the structure of the data.  This can take some doing..

[MFGF]

There's also the option of using Cognos Virtual View Manager. This allows you to consume data presented via web services (among other things) and to model it and present it as tables and columns. Cognos 10 connects to VVM via an ODBC connection, and you can then incirporate it into your framework like any "normal" data source.

Cheers!

MF.

[CalebMF]

There's also the option of using Cognos Virtual View Manager. This allows you to consume data presented via web services (among other things) and to model it and present it as tables and columns. Cognos 10 connects to VVM via an ODBC connection, and you can then incirporate it into your framework like any "normal" data source.

Cheers!

MF.

From what the other user above said, this seems like an easier option than WebServices. How would I go about using VVM? This tool is new to me. Do you know of any tutorials or instructions on this? Any and all direction is very much appreciated. I am new to Cognos and learning on my own (Came from BO).
Thanks guys!

[MFGF]

Hi,

Probably the best place to start is by reading the documentation. The Getting Started guide is here:

http://pic.dhe.ibm.com/infocenter/cbi/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.vvm_getting_started_guide.10.1.0.doc%2Fvvm_getting_started_guide.html

and the User Guide is here:

http://pic.dhe.ibm.com/infocenter/cbi/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.vvm_user_guide.10.1.0.doc%2Fvvm_user_guide.html

Good luck!!

MF.