A couple of weeks ago, a customer asked me how to setup BI capabilities in SharePoint and what were the different options they have available.  

Is really cool to start seeing customers moving forward with SharePoint capabilities, from basic sharing and collaboration, to more complex scenarios like Business Intelligence.

Business intelligence (BI) in SharePoint 2013 provides comprehensive BI tools that integrate across Microsoft Office applications and other Microsoft technologies.

These BI tools are:

• Excel 2013,
• Excel Services in SharePoint 2013,
• PerformancePoint Services in SharePoint Server 2013,
• Visio Services in SharePoint,
• SharePoint 2013, and
• Microsoft SQL Server.

Now, when evaluating SQL Server 2012 BI features to interact with SharePoint 2013 these are the options you have:  

Final adjustments to this table in contribution with Marcos Sánchez and Gonzalo González (Premier Field Engineers) PFEs from Venezuela and Chile.

So we started to work on a lab environment and I'll share what we did as follows:

• 1 SharePoint 2013 Server (no Service Pack by that time).
• 1 SQL Server 2012 with Service Pack 1 slipstream.

Originally the customer wanted to configure Power View and Power Pivot in a SP 2013 and SQL 2008 R2 infrastructure. Even though we know Power View and Power Pivot require the Add-in for SP from SQL Server 2012 with SP1 media, we thought it was going to be feasible to have the Backend in SQL 2008 and the Reporting Services DB's in another SQL 2012 engine, but when it comes to manage encryption keys it was not so easy at the end.

So, I'll divide this article in the following sections (this article covers Power View for Reporting Services, more reference is included at the end to finish the configuration also for Power Pivot with Analysis Services):

1. Prepare Infrastructure and SQL features
2. Provision Reporting Services (SharePoint) Service Application
3. Configure Reporting Services features at Site Collection level
4. Trying the Report Builder Model

NOTE (contribution) by Nick Vargas fellow PFE from US: All steps included in this article will enable support for Stored Credentials and Prompt for Credentials, and additional configuration will be required if Windows Integrated Authentication – Negotiate (link to this option at the end of this article), and for the fourth option "No Credential" will require the Execution Account to be configured as well. I recommend you to review the TechNet Article: Authentication Types in Reporting Services, and more information about the authentication types for SSRS data connections here.

Let's begin….

1. Prepare Infrastructure and SQL features (RS SharePoint Mode and Add-In for SharePoint)

• In Back-End (SQL Server). Download SQL Server 2012 and SQL Server 2012 Service Pack 1 (SP1), and do a slipstream installation, or Download and install SQL Server 2012 with Service Pack 1 from your Microsoft volume license portal.

  SQL Server 2012 SP1 is required - In SharePoint 2013 Excel Services to use Excel workbooks containing data model and Reporting Services Power View reports.  

• In WFE/APP Server (SharePoint Server). Install and Configure SharePoint 2013.
  This step helps you prepare for installation, and gives step-by-step installation instructions, post-installation configuration steps, and upgrade information for SharePoint Server 2013.
• Install SQL BI Features with SharePoint Server 2013.
  The following article will guide you through the installation requirements for the SQL Server BI components that you need to integrate your SQL 2012 SP1 and SharePoint Server 2013 BI features. Software and Hardware Requirements 

There are two fundamental installations needed for Reporting Services in SharePoint mode:  

If you install Reporting Services after SharePoint installation, remember the setup account has to be member of Farm Admin Group. The Report Service installation will register Reporting Services service for you.  

If you install Reporting Services before SharePoint installation, the setup account is not a member of the Farm Admins Group yet, so…You'll have to register the Reporting Services service manually:  

To do so run the following commands to install and start the Reporting Services SharePoint Service http://technet.microsoft.com/en-us/library/gg492249#bkmk_sharedservice_cmdlets (In multi-servers Farms, this is necessarily only in Application Servers where SSRS in SharePoint Mode is installed)

• Install-SPRSService - (To install the service)
• Install-SPRSServiceProxy - (To install the service proxy)
• Get-SPServiceInstance -all | where {$_.TypeName -like "Name of your SQL Server Reporting Service App"} | Start-SPServiceInstance  

IMPORTANT contribution by Gonzalo Gonzalez fellow PFE: If you are deploying a multi-server farm, remember that SharePoint Object Model is required to run within your SQL Server Reporting Services Installation in Sharepoint Mode (Could be installed in APP Servers) while, SSRS Add-in could be deployed into WFE Servers only. 

So proceed with the Setup from the slipstream or SQL 2012 with SP1 media on the SharePoint Server.    

Choose Reporting Services for SharePoint (1) mode and Reporting Services Add-In (2). If you want optionally you can also choose or select other features like, Database Engine (if is going to be used a new SQL Engine), Management tools, and Analysis Services for Power Pivot. Remember double check you DON'T HAVE checked the Reporting Services –Native mode checked or already installed.    

For Analysis Services (Power Pivot), you'll want to install also Analysis Services Feature in the SharePoint Server.    

Validate the installation of Reporting Services in SharePoint Mode and the Reporting Services Add-In for SharePoint are successfully installed in the SharePoint Server, you're not installing these components in the SQL Backend.    

There's also another option to install the SQL Server 2012 RS Add-In for SharePoint from an independent installation package different from the SQL media itself.  

At the end you can validate the Add-In has been installed in Add-Remove Programs    

2. Provision Reporting Services (SharePoint) Service Application  

So, finally, whether you did install SSRS in SharePoint Mode (1) and the RS Add-In for SharePoint (2) before or after SharePoint was installed, you'll be able to see the new SQL Server Reporting Services Serve listed in Services in Farm.    

Also, if you go to Central Admin, you can validate the Add-in in the General Application Settings section as well. It says 2008 and 2008 R2, for compatibility with previous version, however these links are not going to be used to configure the Reporting Services service, instead, were going to use the Reporting Services Service Application we'll provision to configure it in SharePoint.    

That will allow you to provision a SQL Server Reporting Services Service Application, to do it you can go to Service Application web page in SharePoint Central Administration.    

If you want to learn more about the process you can check the following article: Creating Reporting Services Service Applications at http://technet.microsoft.com/en-us/library/jj219068.aspx#bkmk_create_serrviceapplication.The SharePoint Farm service account needs to be local admin at this time.  

If you would like to do it through Central Administration follow this article http://technet.microsoft.com/en-us/library/b29d0f45-0068-4c84-bd7e-5b8a9cd1b538#bkmk_create_serrviceapplication    

Validate the service was been provisioned successfully (web service running in IIS).   

Or through PowerShell: http://technet.microsoft.com/en us/library/gg492278#bkmk_powershell_create_ssrs_serviceapp    

Once the Service App has been provisioned successfully (through Central Admin <UI> or PowerShell), click on the Service Application's name    

Then click on the first option: System Settings, you should see something like this:  

If you see the following message:  

Review these articles: http://support.microsoft.com/kb/329291/en-us and http://support.microsoft.com/kb/842421/en-us. 

Now in order to configure Reporting Services features at Site Collection Level (remember to validate the SQL Server Reporting Services Service Application is already associated to the Web Application that has the Site Collection were you want to use the Reporting Services features on).  

So go to your SharePoint Site -> Site Settings -> Site Collection Features and validate the corresponding features have been already installed (deployed to the Web Application and activated in the Site Collection) as the image below: (you might have different Features between these two, depending on the type of site you're using).    

For more reference please visit the following article: Activate the Power View Site Collection Features. - http://technet.microsoft.com/en-us/library/jj219068.aspx#bkmk_powerview  

In case you don't see the required features, install them: http://msdn.microsoft.com/en-us/library/jj219068.aspx#bkmk_full_script    

Install-SPFeature -Path "PowerView"

Install-SPFeature -Path "ReportServer"  

Then enable them to the required Site Collections:    

Enable-SPFeature -identity "PowerView" -Url http://server/sites/bi
Enable-SPFeature -identity "ReportServer" -Url http://server/sites/bi

Also validate that you have a new settings category in site settings:    

Now, create a new Document Library (adding an App) into your site.    

Open the document library and allow the use of content types.    

Select the three content types from the SQL Server Reporting Services Content Type category.    

Go back to the library and click the second half of the New Document button, optionally you could hide or remove the default content type (Document).    

In case you'd like to automate all through a Script, please refer to the following article: Script to automate all: http://technet.microsoft.com/en-us/library/jj219068.aspx#bkmk_powerview  

NOTE by John Desch, fellow Sr. PFE from USA: You can also set up a BISM connection library to build Power View Reports. Have in mind that with SQL Server 2012 SP1 CU4 or later (actually probably CU9 or later would be preferable), Power View can connect to a multidimensional instance of Analysis Service.

4. Trying the Report Builder Model  

Now, if you click on the Report Builder Report, you'll be downloading/launching the Report Builder client    

Once it loads, you'll have different options to work with    

Let's say we create a Blank Report and we put a custom title like "This is my new Report", then we publish it back to our Reports Library    

Choosing our Report's Library    

We click ok and we select Publish all report parts (first option)    

If we go back to our Reports Library and fresh it, we'll see our report…    

Just click on it to open it into the browser.    

In case you would like to try it, you could use the AdventureWorks package, here: http://social.technet.microsoft.com/wiki/contents/articles/14707.explore-the-adventure-works-multidimensional-model-by-using-power-view.aspx 

For those of you out there with Microsoft Premier Contracts, I strongly recommend you to talk with your TAM and request to attend a SharePoint 2013 BI Workshops, which includes more details around the Report Builder, type of reports, data source connections and managing authentication methods; or even better in case you have an Education as a Service (EaaS) for Premier Workshops subscription, you will be able to watch the training on demand very son.   

In case you'll need to configure SSRS to use Kerberos (Constrained Delegation) I strongly recommend you to review the post from my fellow Sr. PFE Ryan Bushnell from USA at http://blogs.technet.com/b/sharepoint_-_inside_the_lines/archive/2013/05/28/sharepoint-2013-with-ssrs-2012-and-constrained-delegation.aspx  

To proceed with Power Pivot Installation and configuration for SQL Server Analysis Services use the following article - Install Power Pivot for SharePoint 2013, http://www.microsoft.com/en-us/download/confirmation.aspx?id=35577  

I encourage you to also review: http://www.codeproject.com/Articles/615462/Configure-Power-View-Reporting-Services-Features-o and http://www.codeproject.com/Articles/576383/Install-Power-View-for-multi-dimensional-model  

    Additional Information

Hardware and Software Requirements (PowerPivot for SharePoint and Reporting Services in SharePoint Mode)  

Install or Uninstall the Reporting Services Add-in for SharePoint  

Add an Additional Reporting Services Web Front-end to a Farm  

Provision Subscriptions and Alerts for SSRS Service Applications  

SharePoint Server 2013 Business Intelligence Test Lab 

More references suggested by Zaheer Hussain fellow PFE from UK 

Configure the Secure Store Service in SharePoint 2013 – (Nice video demo included in this)

http://technet.microsoft.com/en-us/library/ee806866.aspx

Configuring Unattended Execution account using Secure store Service  

http://technet.microsoft.com/en-us/library/ee836145(v=office.15).aspx

Creating Data Connections, Data Sources, and Connection Strings (SSRS)

http://msdn.microsoft.com/en-us/library/ms156450.aspx

Tu instalación de SharePoint esta casi al 100% de memoria y no sabes por qué…

Is SharePoint using almost all or a lot of memory and dont know why? chances are that you’re using VMWare if your SharePoint has been vistualized…

Revisión de contadores de Performance

Revisando el siguiente artículo de contadores de performance: http://technet.microsoft.com/en-us/library/ff758658(v=office.14).aspx 

En ese articulo, tenemos los siguiente:

Memory

Si hacemos pruebas de estrés...los resultados deberían de dar:

Physical Bottleneck Resolution - Removing Bottlenecks

Memory

Haciendo pruebas de estrés entonces con un cliente, el valor de Cache Faults/sec se incrementó hasta un valor de 12, 783.

El monitoreo se realizó con la herramienta con Performance Monitor. Los contadores recomendados a incluir en la plantilla están en la página siguiente: technet.microsoft.com/.../ff758658(v=office.14).aspx
Por otro lado para estimar la capacidad de los servidores Front End, puede utilizarse el objeto ASP.NET y el contador Request Execution Time. Por ejemplo, si cada servidor Front End tarda 17 ms en atender una petición, entonces el tiempo de respuesta promedio será de 59 peticiones por segundo o 211 mil por hora (múltiplicado por el número de Front Ends). Este valor nos permitió confirmar que la granja podría cubrir el requerimiento de al menos 500 mil peticiones por hora.

Este comportamiento del contador de Cache Faults/sec es un efecto directo del manejo de mejoría, lo cual lleva a preguntarnos la configuración actual de los servidores ESX de VMWare donde se encuentran actualmente instalas las máquinas virtuales de SharePoint

Ahora, por default los servidores ESX pueden hacer uso de lo que se le conoce como Ballooning Memory lo cual consiste en que las Máquinas Virtuales  pueden ceder memoria RAM al host (ESX) donde residen, para que este a su vez pueda tomar la decisión de asignar esta memoria RAM "disponible" a alguna otra máquina virtual que lo requiera. NOTA: Esto ocurre cuando el Host (ESX) que hospeda las máquinas virtuales llega al MÁXIMO USO de memoria RAM.

Y da la casualidad que esta "capacidad que puede manejar VMWare no es recomendable para Máquinas Virtuales que tengan instalado SharePoint, o en otras palabras puede afectar el performance"

Podemos confurmar esta indicación desde este PDF disponible en Internet y publicado por VMWare,: The Role of Memory in VMware ESX Server 3. En este PDF se explica el concepto de ballooning Memory, y si leemos el último párrafo de la página 8, podemos leer lo siguiente (aunque este artículo es para SharePoint 2007, aplica para otras versiones de SharePoint, como 2010, si es su caso, inténtenlo y verán):

"...any memory reclamation through ballooning or swapping from this VM significantly degraded the SharePoint performance."

Otro dato interesante que se puede leer en la siguiente página: "The NUMA-aware resources manegement architecture improves throughput by 8% in the SharePoint VMs case when compared with the case where the NUMA-aware optimization is disabled.

En la página 10 de este mismo artículo (mencionado en unos párrafos arriba) tenemos la sección de mejores prácticas para implementaciones de SharePoint usando virtualización con VMWare. como por ejemplo habilitando NUMA para los procesadores compatibles con dicha arquitectura.

Créditos: Este artículo fue posible p7or la información recabada y compartida por las siguientes personas, mis compañeros y amigos Osvaldo Robles de Consultoría de Microsoft, Jahil Álvarez Technical Account Manager de Microsoft, y Victor Manuel Campos Toledo de Consultoría de Microsoft también.

Saludos!

Alguna vez has necesitado o buscado como puedes ejecutar una revisión de tu granja de SharePoint?

Como hacer un análisis o ver como se encuentra tu granja o instalación.

Ya sea instalada desde cero, o migrada de una versión anterior….

Ya sea que llevas bastante tiempo y no has hecho nunca una revisión proactiva o terminando un proyecto y estás por liberar o salir a producción con tu granja de SharePoint…

Total, lo que quieres ver es a grandes rasgos, como está tu granja de SharePoint…

Pues te platico que hay distintas opciones:

Hay herramientas de terceros gratuitas y de costo que puedes adquirir para que ten genera un diagnóstico de tu granja, respecto a las mejores prácticas de SharePoint acorde a Microsoft.

Por mencionar algunas: (siempre es recomendable probar todo en un ambiente de pruebas y luego decidir si lo harás en tu ambiente productivo).

SPFarmReport (http://spsfarmreport.codeplex.com/), disponible en CodePlex, recuerda que lo que descargues de CodePlex no esta soportado por Microsoft pero al ver quien desarrollo la herramienta te puedes dar una idea de si es confiable o no.

Lo interesante de esta herramienta es que funciona para SharePoint 2007 (O12), SharePoint 2010 (O14) e incluso SharePoint 2013 (O15) – recién actualizada. Y genera un reporte aunque básico, muy amigable sobre tu granja en HTML con información como, parche de SP, numero de Web Applications, número de zonas, etc (la última vez que lo hice di una recomendación en el foro que mostrara Site collections por Web Applications o en cada content dastabase, pues no lo hacía, pero aún así) es una herramienta interesante y gratuita.

Herramientas de terceros…cada una tendrá sus pros y contras

SPDiagnostic Tool

Microsoft Support Diagnostics. Para clientes que tienen contrato de soporte con Microsoft, hay un link donde el mismo cliente puede entrar a una página y es un listado de una serie de herramientas Self-Service, que quiere decir:

El cliente entra a la página, y selecciona el diagnóstico que quiere hacer sobre su Infraestructura, algunos ejemplos:

Azure PaaS VM Logs - Para clientes usando Azure
This diagnostic is designed to gather the most common log files and diagnostics information from an Azure PaaS VM, Collect data used to troubleshoot most of issues related to Windows Azure.

InfoPath Support Diagnostic
This diagnostic tool collects relevant log files, registry keys, client networking configuration information, event logs, and important file details that are used by Microsoft InfoPath.

Internet Explorer Data Collector
This article describes the information that may be collected from a computer when running Internet Explorer Diagnostics for Windows Client and Server as well as a descriptions of the Interactive t

Microsoft Online Services Diagnostics and Logging (MOSDAL) Package  - Usado en Troubleshooting para Office 365
The Microsoft Online Services Diagnostics and Logging (MOSDAL) Package collects information to troubleshoot Microsoft Office 365 issues. The Support Diagnostics Platform (SDP) diagnostic tool uses this diagnostics package to collect relevant log files, registry keys, client networking configuration information, event logs, and details about files that are used by Office 365 technical support.

SharePoint Engineer Troubleshooting Helper (SETH)
This SharePoint Engineer Troubleshooting Helper diagnostic is designed to detect certain problematic conditions that may exist in the configuration of the server that is running Microsoft SharePoint Server. The problematic conditions are checked only on the server on which this manifest is executed. To make sure that you have maximum coverage, we recommend that you run SETH on each computer in the Microsoft SharePoint farm.

SharePoint Performance Monitor Wizard Diagnostic
The SharePoint Performance Monitor Wizard diagnostic is designed to create user-defined data collector sets within the Windows Reliability and Performance Monitor tool.

El cliente genera el paquete y lo ejecuta en su infraestructura para recolectar la información.

El cliente ejecuta un .exe que viene con el paquete que en automático sube o manda la información a Microsoft para que un especialista de Soporte revise los hallazgos.

RAP (Risk and Health Assessment Program) as a Service – RaaS. Esta es una opción únicamente para clientes que cuentan con contrato de soporte Premier de Microsoft. https://services.premier.microsoft.com/raas

Sumamente interesante:

1. El cliente solicita el servicio a su Technical Account Manager (TAM)
2. Se genera la solicitud del servicio,
3. El cliente entra al portal y descarga la herramienta – https://services.premier.microsoft.com
4. El Administrador de SharePoint descarga la herramienta y la ejecuta.
5. La herramienta recolecta información y la envía al Data Center de Microsoft de forma segura (https), y un especialista de producto de soporte (Premier Field Engineer o PFE) es asignado para que analice la información.
6. Finalmente se programa una presentación con el cliente de los resultados.
7. Se programan fechas para llevar a cabo el plan sugerido de remediación.

Quieres ver un video sobre el servicio, has clic aquí: https://services.premier.microsoft.com/raas, hasta abajo de la página lo encontrarás…

Lo interesante de la propuesta de RaaS es que está disponible para las tecnologías principales: Active Directory, SQL Server, Exchange, SharePoint, Exchange, y Cluster, y no nada más se hace un análisis de la parte técnica, el RaaS también incluye el análisis de la parte de procesos orientada a MOF con parte de ITIL, lo cual agrega entonces la parte de procesos y operación y le agrega un mayor valor a las otras herramientas que existen.

Resumiendo…

Hay varias opciones, algunas gratuitas que te dan información básica y otras mucho más avanzadas que tienen costo pero te dan mucho más detalle de tu infraestructura…

Y a ti, cual es la que te parece más interesante?

Saludos!

Hola que tal, hace tiempo no había podido escribir un nuevo post con tanto trabajo y viajes, pero espero puede retomar la dinámica y conversación con ustedes de nuevo.

He estado en muchos nuevos proyectos, y hace tiempo no tengo la posibilidad de interactuar con clientes para ayudarles a hacer sus migraciones a la nube de Microsoft.

Y sobre este tema, para los que ya están usando Office 365, les comparto este artículo muy interesante que el equipo de Office 365 recién publicó en su Blog.

Ustedes sabrán que Microsoft ofrece sus servicios de la nube a través de Office 365 (http://office365.com) y dentro de estas opciones tenemos lo que es: SharePoint Online (Colaboracion), Exchange Online (Mensajeria), y otras capacidades.

Ahora quiero compartirles que el equipo de producto ha lanzado una nueva App para Windows Phone 8, desde la cual pueden administrarse los servicios en la nube de su empresa si tienen contratado Office 365, algo bastante cool, yo ya la descargue y la estoy revisando, y ustedes?

Les invito a probar la nueva Windows 8 App para Windows Phone 8, para administrar sus servicios en Office 365 aquí: http://blogs.office.com/b/office365tech/archive/2013/11/21/check-the-service-health-of-your-office-365-service-on-the-go.aspx

Saludos!

Que tal, una vez más te invitamos como cada año al siguiente Simposio Latinoamericano de SharePoint en México, el próximo Viernes 4 de Mayo en el auditorio (piso 7 o PH) de las oficinas de Microsoft México en Sta Fe.

Como cada año ya se ha vuelto una tradición, empezando con el primero en Costa Rica hace ya cinco años atrás, con los amigos de la Comunidad de Costa Rica Vielka Rojas y Ricardo Muñoz, y claro con el buen Hector Insua, todos MVPs de SharePoint, hemos logrado continuar replicando la idea que iniciamos allá en México.

Si quieres ver como han estado los Simposium anteriores te comparto los links de cada uno a continuación y puedes ver las fotos de cada uno así como descargar las presentaciones.

Primer Simposio de SharePoint en Costa Rica

http://blogs.technet.com/b/ldusolier/archive/2008/09/11/primer-simposio-latinoamericano-de-sharepoint.aspx

Segundo Simposio de SharePoint en México

http://sharepoint.com.mx/2009/04/29/segundo-simposio-latinoamericano-de-sharepoint-ndashen-meacutexico-23-abril-2009.aspx

Un video que hicimos del Segundo Simposio de SharePoint en México

Tercer Simposio de SharePoint en México

http://sharepoint.com.mx/2010/06/17/tercer-simposio-latinoamericano-de-sharepoint-en-mexico-todo-un-exito-aqui-los-detalles.aspx

Cuarto Simposio de SharePoint en México

http://sharepoint.com.mx/2011/05/23/cuarto-simposio-latinoamericano-de-sharepoint-2010-en-mexico.aspx

Un video que hicimos del Cuarto Simposio de SharePoint en México

Quinto Simposio de SharePoint en Costa Rica

http://blogs.technet.com/b/ccaitpro/archive/2011/05/30/5to-simposio-latinoamericano-de-sharepoint-costa-rica-2011.aspx

Y ahora el Sexto, con la invitación a continuación.

El Link para el registro si el botón dentro de la Invitación no funciona

https://msevents.microsoft.com/cui/EventDetail.aspx?culture=es-MX&EventID=1032511713&IO=CYmFUKJhuOBgIoH9yizzww%3d%3d

Recuerda imprimirlo y llevarlo al día del evento el registro impreso…

Pero si no puedes asistir de forma presencial, puedes entrar al evento y verlo desde esta sesión de Live Meeting

https://www.livemeeting.com/cc/microsoft/join?id=K3W8ZC&role=attend&pw=JGB%3B%5E%607df

Y la agenda para que vean los temas:

Si eres miembro activo de la Comunidad de SharePoint, al llegar búscanos a Luis Du Solier, o a Vladimir Medina y menciónanos la página de la Comunidad o explícanos lo que significa el Logo de la Comunidad y tendremos lugares preferenciales para ti hasta adelante

Te esperamos!!!