Best method for pulling data out of SharePoint to use with a Business Intelligence application

Question

Asked this first on serverfault, and someone recommended that I ask here.

I'm looking for advice from anyone out there who has experience integrating SharePoint with a business intelligence application like Cognos.

Our BI team wants to be able to report on data stored in SharePoint. Their tool of choice is Cognos. What's the best way to get the data they're looking for OUT of SharePoint and into Cognos BI for analysis?

To clarify I'm NOT looking for a way to display Cognos reports in SharePoint. We want to take the list data from SharePoint and use Cognos to report on it.

Answer 1

Since the SharePoint database itself is extremely complex it is not recommended to access it directly. You do however have to alternatives to pulling the data out.

List RSS Feed

The simplest and easiest way would be to enable RSS on the lists you want exported and then pulling the RSS feeds into a seperate database using an external tool.

List WebService

The second option is to use the SharePoint List Web Services. These are standard ASMX webservices that expose the data inside any list to an external source. You can access any list as a Web Service as follows:

[Sharepoint Site Url] + _vti_bin/Lists.asmx.

The details on using the List Web Service is on MSDN here

Answer 2

Diago is right, never touch the DB. In answer to your BI question I recently responded to a similar one here http://stackoverflow.com/questions/1433306/combining-data-from-project-server-and-sharepoint-into-a-single-report/1434173#1434173

Answer 3

I ran across the same problem and looked at several applications that sync a Sharepoint list with a SQL database. Once you have it in SQL the job is easy. the tool I ended up using was SQList from Axio Works. The functionality provides a one way sync to SQL at a reasonable price point.