That annoying timeout error

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

If you've worked enough with report and database development you will have your share of timeout occurrences. There are many discussions on timeouts but I've seen very little to explain the layers of timeout settings that are enacted when you run a report. At its core, timeout settings from a report execution perspective occur at the following levels...

• At the dataset level
•  At the data source level.
•  At the database server level

Therefore, when you receive a timeout, these are the three areas that you need to check.

At the dataset level

As a report developer, this is one area that you have a lot of control on the timeout setting if the dataset is embedded. By default, this setting is 15 (in seconds). So if when running the report, you have issues connecting to the dataset, this is where the timeout will occur. This is generally not the real source of timeout issue because the connection to the data source should be instantaneously. Regardless, the setting is shown below.

At the data source level

Again, if the data source is not shared, this is an easy change by the report developer. By default, this setting is 0 (unlimited). You can find this location at the following location.

If you make the change in the advanced settings (thus changing the default setting), you will then notice that the connection string has added the timeout setting. It will be added as “Timeout Connection = X“ . This is the same as Timeout = X which you’ll commonly see in Connection Strings.

At the database server level

Typically not a report designer responsibility but it you need to change it (or inform your DBA) you can specify this at the SQL Server Level - meaning this will impact all databases on the server. You find this in the properties setting the SQL Server. Now if you are using another type of data source, you’ll need to find out where that is change (I only talk about SQL Server here).

Shared data sources and datasets

If you are using shared Data Sources or datasets, you will then need to have access to the location of these objects to check or change the timeout value.  The settings have the same principles as embedded versions but are simply stored at the SSRS Website. You simply have to specify Manage on the required object. For datasets you can change it here…

Or for the Data Source, use the following location.

Using this information should be all you need to isolate the source of failure for the Timeouts issues you experience when running and developing reports.

Self-Service BI with PowerView

 

In an attempt to empower the users, Microsoft has launched several tools that allow users to develop their own data visualization efforts. Excel, a familiar tool that has a large audience is one of the suite of tools that you can use to develop impressive reports and dashboards. Using Excel, you can create PowerView reports.  PowerView visualizations are authored in the Excel environment which helps remove  a “barrier on entrance” for users  who can be skeptical of using unfamiliar report development tools.  PowerView is one of Microsoft self-service BI tools (Power Query and Power Map are the others) that make up its self-service BI offering. It allows for some very impressive data exploration and intuitive ad-hoc reporting. 

Before embarking on these self-service BI tools, it’s important to first understand the importance of PowerPivot. PowerPivot has been available for Excel 2010 as an add-on and it is a core component of all Microsoft BI self-service tools. PowerView reports are based on PowerPivot models in Excel. In order to use PowerView, you must first establish a PowerPivot Worksheet. In order to create a report, open the Excel workbook that contains a model and on the Insert tab, click the Power View button

To help drive home the point of self-service BI ease of use, I have created a video that demonstrates producing a PowerView dashboard...

As you can see in just a few clicks,  I was able to create a relatively impressive representation of data without the assistance of IT staff or some data steward. 

Although its an easy environment to create reports, as with any reporting tool, it’s still important to understand how to best represent you data given business questions that need to be answered and audience. These tools won't make you report designer guru with just a few clicks. However, for those that know what they want from their data , PowerView will help you get there very fast and without much assistance.