SSRS – Creating a Reporting Services Web Farm

Reporting Services performance problems can often be narrowed down to report design or resource constraints and sometimes a combination of the two.  In this post I’ll review the process of scaling-out Reporting Services so server resource constraints can be shared across multiple servers instead of relying on a single server for all report processing.

If you are new to understanding Reporting Services architecture it is important to understand the general steps that are occurring behind the scenes with Reporting Services. From the beginning of a user request the steps that happen are:

  • Client makes a request to the report server
  • Report server then makes a request to the report catalog
  • Report catalog sends back to report server report definition and tells the report server which data sources to hit.
  • Report server request data from source database
  • Source database sends data and the report server renders report metadata and data for the client

Understanding Scaling-Out Architecture

If you have ever installed installed SQL Server and the BI stack locally on your machine for testing purposes than you’ve likely done a single server install.  This means you have SQL Server, Reporting Services, Analysis Services and Integration Services engines all running on the same machine. 

image

While this might be fine running in a local testing environment on your machine this obviously doesn’t scale well on a server that requires thousands of users to connect.  In an environment that requires this many users a scaled-out deployment is ideal and can support a heavier workload. 

In a scaled-out deployment multiple Report Server instances are installed that all use the same repository or ReportServer database.  So when a thousand client machines make a request to the ReportServer the report rendering can be split the requests across multiple servers to do the work.  They still must use the same repository database because they still want to view the same report metadata but the report processing should be helped.  The screenshot below depicts a scaled-out deployment with three instances of Reporting Services installed and a single SQL Server database repository to return back report metadata.  This is an example of a common setup but not the only way to do this of course.

image

With this approach we could have our west coast customers using a URL that points to Server A, mid west customers using a URL that points to Server B, and all east coast customer use Server C. 

Another approach to this is to use a Network Load Balancer (NLB).  The idea behind a Network Load Balancer is that you provide a single virtual URL to the report server and the NLB sends the user to the Report Server that is least utilized.  So if you have two servers and Server A is only 20 percent utilized and Server B is 80 percent utilized then the NLB would choose Server A for the next incoming user instead of you manually providing a URL for each user.  The Network Load Balancer can either be a physical device on your network for making these decisions or you can enable the feature in Windows Server.

Setup and Configuration

The setup and configuration for scaling Reporting Services into a Web Farm is actually fairly easy.  In fact to oversimplify it here is an overview of the steps required:

  • Install SQL Server (Database Engine) on one server.
  • Install Reporting Services on two servers without configuring either.
  • Pick one of the Report Server installations and configure it using the Reporting Services Configuration Manager.  Go through each page of the configuration manager except the Execution Account and E-mail Setting, which are optional setting depending on you required setup.
  • Configure the second Report Server but use same report catalog database as the first and set URLs.  So on the database page ensure it points to the same database that was setup during the setup of the first Report Server

image

  • Go back to first Report Server you configured and click Add Server under the Scale-out Deployment page to join the instances as shown below.

image

Without overcomplicating this that is really all it takes.  This would be done for as many Report Servers as you would like to add to the farm.  Hope this helps.

SSRS – Embedding Refreshable Reports in Office Tools

Have you ever experienced this?

You have developed a set of Reporting Services report that your users love and they often show them off. The most common way users show off the reports is by taking screenshot images of the reports and embedding those images in a PowerPoint slide deck along with other information they plan to discuss.

I personally have seen this several times and I’ve always thought wouldn’t it be better to have a live version of the report available in the PowerPoint slide deck so it would always be up to date. This way new screenshots never have to be updated if visual aspects or data changes of the report occur because the slide deck would always have a live view of the report.

While this is a feature that has been touted in Power View you don’t have to wait because it is possible with Reporting Services now. Now of course the new Power View method of embedding reports is much more impressive because the reports remain interactive as if they were viewing on the SharePoint.

For this post let’s focus on how this can be done for Reporting Services because many people do not realize this is possible. Many of the Office products have the ability to embed images which can come from a URL. With this in mind we could also embed a URL of a report image and anytime the file is opened it the report will be updated.

Open a PowerPoint deck and go to the Insert menu in the Office Ribbon to insert a Picture. for the file name use the below template replacing the bold text with the relevant information about the report you want to embed.

http://<servername>/ReportServer?%2f<folder+name>%2f<report+name>&RS:Command=Render&RS:Format=Image&RC:OutputFormat=TIFF

**Note** The plus signs between folder name and report name are for any spaces you have between each word.

Then before you click Insert you will need to hit the dropdown arrow and select Insert and Link. Using this options retains the link to the image so that is can be refreshed each time the PowerPoint file is opened.

image

You should now see an image of your report embedded in your document and any time the files is reopened the view of the image will be updated to reflect new data and visualization changes. My example used PowerPoint but this could be done in other tools like Word or Excel too.

I hope this proves to be helpful to the reporting solutions you build.

Whitepaper – Extending Reporting Services with Custom Code

Today Pragmatic Works released a whitepaper I wrote and I thought I would share it with you.  Prepare for Marketing email…

Banner Image

A Whitepaper by Devin Knight

SQL Server Reporting Services is capable of creating eye-popping visualizations that can be consumed by a variety of business users. The tool has built-in functionality for developers to produce reports that users will find not only functional, but also visually appealing. This whitepaper will focus on how to overcome the gaps that expressions leaves by using custom code. You will also learn many other advantages of using custom code like creating consistency across all reports developed. As you read this paper, you will walk through many examples of using custom code. These examples will emphasize the use of custom code, and will therefore skip topics like creating data sources, datasets and basic report toolbox items.

Download the Whitepaper Here

Interested in more SSRS training? Sign up for Devin’s Master SSRS Online course

What You’ll Learn:

  • Overcome the gaps that expressions don’t cover by using custom code
  • Create consistency across all reports developed
  • Learn how to use Visual Basic and Reporting Services in real-world examples
  • And much more!

Training Image

SQL Saturday Jacksonville and Precon with Kevin Kline

I’m so excited to tell you about SQL Saturday 130 in Jacksonville coming April 28th.  This is the 5th year SQL Saturday has been held in Jacksonville and we have some exciting news about the event this year. 

If you have never attended a SQL Saturday before… WHERE HAVE YOU BEEN?! Smile All kidding aside you really are missing out if you’ve never been to these free events that occur all over the world now.  

This year we are happy to announce that Kevin Kline will be delivering a Preconference seminar on Troubleshooting & Performance Tuning SQL Server.  You can enjoy this full day seminar on Friday April 27th. 

Topics covered include:
- The “Rocks, Gravel, Sand” Troubleshooting Methodology
     o Understanding Wait Stats
     o Using beginner, intermediate, and advanced DMVs
     o Correlating Performance Monitor and Profiler
     o Extended Events
- Determining meaningful key performance indicators thru benchmarking
- Catch problems before your users do thru monitoring
- Preventative Maintenance Best Practices

Right now you can sign up for the Early Bird Special for $99 until March 27th when it goes up to $120.  Money raise from this event will go to pay for SQL Saturday event day needs.

image

Back to the main event.  Right now for SQL Saturday we are looking for:

Speakers

A major misconception about SQL Saturday Jacksonville is that because Pragmatic Works has it’s home office in Jacksonville that we don’t need speakers.  Well that is far from true.  We would love you to be a speaker at our event. 

Sponsors

SQL Saturday’s are a great way to get exposure to your business.  Last year we had 450 attendees and have continued to grow in numbers year over year.  This is a great way to get in person contacts with hundreds of SQL Server professionals.

Attendees and Volunteers

Why: It is a great opportunity to learn from Microsoft MVPs, SQL Server authors, and professionals who have been through and can relate to the obstacles you have day to day. This one day training event is unlike any other providing free food, training and networking.

When:  Saturday, Apr 28 2012.

Online registration is open now, but it is filling up fast so reserve your spot now.  Let your friends and colleagues know about the event as well!  Attendee check-in will begin at 8:00 am until 8:45 am with the first session beginning at 9:00 am.  A full list of session tracks will be available soon but in the meantime, please suggest a session you’d like to see!

Where: University of North Florida Campus, Main Campus, 1 UNF Drive, Jacksonville, FL 32224. Building 50.

Cost: FREE!!!

SSRS – Using Analysis Services Dynamic Management Views to Build Dynamic Reports

Dynamic Management Views are a very powerful tool that are part of Analysis Services in 2008 and 2008 R2.  They give you the ability to monitor Analysis Services usage and metadata. 

MSDN articles on SSAS DMVs – http://msdn.microsoft.com/en-us/library/ms126079.aspx

Good post on using SSAS DMVs – http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

A report developer could leverage the capability to read Analysis Services metadata to build a report that can dynamically change the type of information that is displayed on a report.

Let’s walk through a basic example of how we can Dynamic Management Views to build a dynamic Reporting Services report.  I’m starting with a basic report that has already been built off of an Analysis Services cube that looks like this:

image

As you can see it shows Internet Sales by category over several calendar years.  The MDX query used for this is pretty standard as you can see.

Select
    [Measures].[Internet Sales Amount] ON Columns
    ,NON EMPTY ([Date].[Calendar].[Calendar Year]
    ,[Product].[Product Categories].[Category]) ON Rows
From
    [Adventure Works]

I start by changing the query to use an in session calculation with the WITH clause so I can basically alias it to [Measures].[Generic Measure].  By aliasing the measure it will make since more later because the field name doesn’t identify with any specific measure.

With
    Member [Measures].[Generic Measure]
        AS STRTOVALUE("[Measures].[Internet Sales Amount]")

Select
    [Measures].[Generic Measure] ON Columns
    ,NON EMPTY ([Date].[Calendar].[Calendar Year]
    ,[Product].[Product Categories].[Category]) ON Rows
From
    [Adventure Works]

The next step is to create a parameter in the report so I can pass in the measure name to the query dynamically.  Eventually I will populate this parameter using a DMV but for now I’ll just have it as a text field that I can type in the measure name.

Now to make this query dynamic with my newly created @MeasureName parameter I replace the dataset query with an expression that looks like this:

="With "
+"    Member [Measures].[Generic Measure] "
+"        AS STRTOVALUE("""+Parameters!MeasureName.Value+""")  "

+"Select "
+"    [Measures].[Generic Measure] ON Columns "
+"    ,NON EMPTY ([Date].[Calendar].[Calendar Year] "
+"    ,[Product].[Product Categories].[Category]) ON Rows "
+"From "
+"    [Adventure Works] "

image

You’ll notice that I’ve basically changed the query to a string in the expression except for the measure name which I’ve changed to use my new parameter.  This report will now work dynamically but the user would have to type in the measure they would like to see in the report.  Not only would they have to type it but they must know the MDX for it.  For example, users would have to type [Measures],[Internet Tax Amount].  Obviously, that’s not going to happen so we need to make this simpler for the user. 

This is where Dynamic Management Views help.  We can use the mdschema_measures DMV to return back a list of measures from our cube.  The following query will return back all my measures names, MDX, and format: 

SELECT Measure_Name, Measure_Unique_Name, Default_Format_String
FROM $System.mdschema_measures
WHERE Cube_Name = ‘Adventure Works’
ORDER BY Measure_Name

Unfortunately, not all format strings translate to Reporting Services but this could be manipulated using expressions.  Using this query we can create a new dataset and have that dataset populate our @MeasureName parameter.  The dataset cannot be used in entered into a dataset using the Query Builder because it’s not really MDX.  So you must select to build an expression on the new dataset and paste in the query.  It does not require an actual expression so remote the equal sign before the query.

image

With the dataset completed go back to the @MeasureName properties and change the available values to use the Measure_Unique_Name and Measure_Name fields to populate the parameter dropdown box.

image

Now that the parameter dropdown box it populated with the DMV query go ahead and preview the report.  You’ll see you can select any measure front the dropdown box and it will change the information shown on the report as shown below:

image

Follow

Get every new post delivered to your Inbox.

Join 29 other followers