Business Intelligence on Andrew Bancroft

Troubleshooting SQL Server 2012 Reporting Services and SharePoint 2010 Integration (Part 2)

Sat, 30 Jun 2012 19:44:36 +0000

You can visit Part 1 of this troubleshooting series by clicking here.

During our configuration of Reporting Services for SharePoint I ran into another common issue.

I consulted MSDN once again and followed the directions given at this MSDN article. Even after following all these instructions, when I would attempt to create a new BI Semantic Model Connection in the SharePoint library, I would receive the following error:

Cannot connect to the server or database.

Even after adding the Reporting Services service account to the SSAS Tabular instance as a server administrator, we still hit this error.

Once again I found little documentation out there as I was troubleshooting, presumably because SQL Server 2012 is still quite new. Here’s what we did to fix our problem:

The Analysis Services service must be running under a domain account. When I installed SSAS, I took all the defaults for the server installation. The SSAS service was running under some default built-in account which ended up being the problem.

Once we switched this to run under a domain account, I was able to create BI Semantic Model Connections using this library document type with no issues.

Troubleshooting SQL Server 2012 Reporting Services and SharePoint 2010 Integration (Part 1)

Sat, 30 Jun 2012 19:06:39 +0000

I recently worked with our SharePoint administrator to install SQL Server 2012 Reporting Services in SharePoint Integrated Mode to take advantage of PowerView. By following the installation instructions found at this MSDN article, we were able to painlessly install both Reporting Services and the Reporting Services Add-in from the SQL Server installation media.

Then began the configuration process. Since everyone’s environment is different, I found it difficult to troubleshoot some of the odd behavior that we were running in to. In Part 1 of this post I want to focus on what we did to overcome a specific error we were receiving:

After installation, I created a Business Intelligence Center site without any trouble. In the Connections library, I would attempt to add a Report Data Source and receive a SharePoint page that detailed the following error:

This SQL Server Reporting Services (SSRS) functionality is not supported. Use Central Administration to verify and fix one or more of the following issues:

A report server URL is not configured. Use the SSRS Integration page to set it.
The SSRS service application proxy is not configured. Use the SSRS service application pages to configure the proxy.
The SSRS service application is not mapped to this web application. Use the SSRS service application pages to associate the SSRS service application proxy to the web application.

Other times, I would receive a little bit more of a generic message saying something to the effect of “This SQL Server Reporting Services (SSRS) functionality is not supported” or “Unsupported Reporting Services Functionality”.

As it turns out, our specific problem was unrelated to any three of those bullet points.

Our solution?

Make sure to install the "Reporting Services Add-in for SharePoint Products” to all Web Front-End (WFE) nodes of your SharePoint farm.

On the Feature Selection screen of SQL Server setup, you can simply choose “Reporting Services Add-in for SharePoint Products” on your other WFE nodes. You don’t need to install the Database Engine Services or Reporting Services – SharePoint all over again…just the Add-in.

We began to suspect it was something related to missing components on the other nodes because as I was troubleshooting, I noticed that if I attempted to create a Report Data Source enough times, I would be able to proceed to different stages of the creation process. Sometimes I would hit the error right away. Sometimes I would get to the page where I could fill out the connection details but when I would attempt to save the data source I would get one of the error pages described above.

After we installed the Reporting Services Add-in on all WFE nodes, the problem stopped and we have not experienced any difficulty since.

From SQL Queries to Data Modeling

Sat, 16 Jun 2012 19:25:15 +0000

This past week I had the opportunity to attend Microsoft TechEd 2012. Lots and lots of great material presented – I’m pretty stoked about some of the amazing features of SQL Server 2012’s BI stack.

During the course of the week I discovered something that has been emerging in my thought process for a while, but sort of hit the level of practicality as I saw one particular feature demonstrated: the BI Semantic Model. I walked away from session after session thinking, “Man, this is so doable now!!”. This was the single most impressive and perspective altering concept that flooded into my brain this week.

There comes a time in every organization that necessitates a paradigm shift: One that involves a move toward data modeling and a away from SQL query writing for the purpose of supplying data sets to reports. By no means am I suggesting that writing SQL will disappear for database / BI Developers – but as a means to serving as the backbone of an organization’s reporting infrastructure, there will inevitably come a time that employing more and more people capable of writing SQL will be unsustainable.

The reasons for this are clear: As an enterprise continuously builds or purchases data collection systems, the users of those systems expect to be able to get information back out. So they ask questions. Unpredictable combinations of questions. They are too numerous and too diverse for even a dedicated team of reporting people to write SQL to a database every time a business person has a question they want to ask of their data. For a while, parameterized reports suffice for most questions. But still the organization grows and evolves, and decision makers expect that they should be able to analyze information at their convenience, not at the mercy of the IT Department’s work schedule.

Microsoft has worked hard to give BI developers effective ways to build models so as to enable us to write less SQL (at least when it comes to supplying answers to the multitude of questions that come our way) and empower our business folks to create their own reports that are based on the model we build. In my view, this is the direction that we must inevitably go. Without this, IT becomes a significant bottleneck to the progress that could be made from timely data analysis.

I’m personally looking forward to using SQL Server 2012 to promote better query-ability and visibility of the information my customers are dying to get their hands on!