Desde PowerPivot para Excel podemos usar como fuente de datos una lista de SharePoint 2010. Para poder usarla la lista de SharePoint debe poder ofrecer los datos como Data Feed (en formato ATOM).Para esto SharePoint 2010 debe:
– Tener un servicio web capaz de ofrecer los datos en ATOM (se instala a través de PowerPivot para SharePoint)
- Uno ver los Pre-requisitos de SharePoint 2010 es ADO.Net Data Services que proporciona un servicio WCF REST llamado ListData.svc, el cual nos ofrece en formato ATOM todos los datos de las listas de SharePoint.
– Tener instalado el Update de ADO.Net Data Services SP1
Para comprobar que SharePoint 2010 es capaz de proporcionar datos vía Data Feed accedemos a http://mysitecollecion/_vti_bin/ListData.svc entonces debemos de ver un XML con todas las listas:
Si al intentar acceder a este servicio REST nos da este error:
Could not load type ‘System.Data.Services.Providers.IDataServiceUpdateProvider’ from assembly ‘System.Data.Services, Version=3.5.0.0, Culture=neutral, PublicKeyToken=XXXXXXXXX.
La Solución es:
Instalar el ADO.Net Data Services Update que tenéis arriba y reiniciar el servidor.
Fuente: Could not load type IDataServiceUpdateProvider when using REST with SharePoint 2010
[box type=”info”] NOTA: Este servicio WCF REST nos viene instalado en SharePoint independientemente de si tenemos instalado PowerPivot para SharePoint, lo único que cuando tenemos PowerPivot para SharePoint desde las configuraciones de las listas se nos ofrece la opciçón “Export as Data Feed” que nos proporciona la url de dicho servicio para una lista en particular encapsulada en formato atomsvc (lo veremos más adelante).[/box]
Una vez que tenemos funcionando el servicio de Data Feed de SharePoint 2010, entonces ya podemos usar PowerPivot para Excel para importar datos desde SharePoint para ello:
1. Obtenemos el fichero .atomsvc que nos dará la cadena de conexión. Para ello vamos a la lista de SharePoint y desde la pestaña “Lists”, pulsamos en “Export as Data Feed”.
Esta opción nos genera un fichero .atomsvc con este formato:
<?xml version="1.0" encoding="utf-8"?><app:service xmlns:a10="http://www.w3.org/2005/Atom" xmlns:app="http://www.w3.org/2007/app"> <app:workspace> <app:collection href="http://solidqsps/_vti_bin/ListData.svc/Tasks"> <a10:title type="text">Tasks</a10:title> </app:collection> </app:workspace> </app:service>
Si nos fijamos dentro del atomsvc tenemos la URL hacia el servicio REST ListData.svc y el título de la Lista de SharePoint.
[box type=”info”] NOTA: Tener en cuenta que aquí no se ha hablado de seguridad todavía, la seguridad o autenticación se configura más adelante en PowePivot para Excel.[/box]
2. Una vez tenemos el fichero .atomsvc, en la ventana de PowerPivot, pulsamos en en boton “From Data Feeds”:
En el campo Data Feed Url podemos poner dos cosas:
– Darle al boton Browse y seleccionar el fichero .atomsvc que nos ha generado SharePoint 2010.
– Poner directamente la URL del Servicio REST con el formato http://sitecollection/_vti_bin/ListData.svc/NombreLista.
En ambos casos va a funcionar, siempre que la aplicación web de SharePoint tenga acceso anónimo habilitado. Lo más probable es que la aplicación de SharePoint a la que queremos acceder tenga habilitado algún mecanismo de autenticación, y entonces al darle al boton de Test Connection, nos dará un mensaje “Cannot connect to the specified feed. Verify the connection and try again. Reason: The remote server returned an error: (401) Unauthorized.”:
3. Autenticación
En caso de necesitar autenticación hay que trastear en servidor web de SharePoint 🙂
Autenticación Básica en SharePoint
La forma más fácil y rápida de acceder a un Data Feed con PowerPivot para Excel es usando autenticación básica. Esta se puede configurar desde el IIS Manager en el servidor front-end de SharePoint:
Si configuramos así (como la imagen de arriba) la aplicación web de IIS correspondiente con nuestra aplicación de SharePoint entonces debemos de realizar la siguiente configuración en PowerPivot:
DNS: DomainName
Integrated Security: Basic
Password: xxxxxx
Persist Security Info: True
User ID: SharePointUserID
NOTA: Si hemos puesto directamente la URL estará relleno el campo Base URL, si hemos usado el atomsvc entonces estará configurado el campo Service Document Url con la ruta de dicho atomsvc.
NOTA2: Si usamos autenticación básica, entonces la contraseña no será encriptada, por lo que es recomendable usar el protocolo HTTPS para que todo se encripte antes de mandarlo a Internet.
Autenticación Integrada de Windows
Si necesitas usar la autenticación integrada de Windows para acceder desde PowerPivot y el Excel no está en el mismo servidor que el SharePoint y que el SQL Server, entonces necesitamos solventar el problema del doble salto.
Más información: http://blogs.msdn.com/b/securitytools/archive/2009/11/04/double-hop-windows-authentication-with-iis-hosted-wcf-service.aspx
4. Si le damos a OK y siguiente veremos como PowerPivot nos importa los datos correctamente.
Referencias:
– How to: Install ADO.NET Data Services to support data feed exports of SharePoint lists
– Using SharePoint List Data in PowerPivot
– Writing a Secure WCF Data Service for Excel PowerPivot
Índice de posts de la serie importando a PowerPivot:
- Copiar y pegar en PowerPivot
- Desde fuentes de datos externos:
- Importar ficheros de texto en PowerPivot
- Importar desde bases de datos relacionales a PowerPivot
- Importar desde Analysis Services a PowerPivot
- Importar datos desde la Nube (Azure DataMarket)
- Importar datos desde Lista de SharePoint 2010 con Data Feed
Espero que os guste!
Un saludo
JQ
