RS.exe and rss Scripts for Automation of SSRS 2008 Administrative Tasks

Download .rss script

Most people that work with SSRS (2005, 2008, 20080 R2) are aware of two ways to deploy Reporting Services catalog items (reports, data sources, shared datasets, images, etc.). These include deploying directly from BIDS (Visual Studio) and by using Report Manager to upload files. There are also a number of web services available that enable us to do this this in a programmatic, automated fashion that allow for things such as dynamic data sources so that we can deploy reports to any environment without having to modify data source properties manually and specifying where in the Report Manager folder hierarchy we want our reports installed - again without having to modify data sources or data sets or references to either.

The following web services are available with for administrative tasks in SQL Server 2008 R2. Note that there is also a ReportExecution2005 web service that provides methods for actually executing reports.

1. ReportService2005
2. ReportService2006
3. ReportService2010

ReportService2005 was used for SSRS instances that are running in native mode.
ReportService2006 was used for SSRS instances that are running in SharePoint integrated mode.
ReportService2010 ties 2005 and 2006 together so that one web service works with both native and SharePoint integrated mode.

While you can work directly with these web services using your programming language of choice, Microsoft was kind enough to create RS.exe. This console application acts as a wrapper for these web services. It is installed with SQL Server and like any other executable it can be called by tools like NANT, MSBuild, InstallShield, etc.

My need for this tool arose when I was developing a set of reports for a commercial product that required installation through InstallShield. The development environment included ANT which we also had call RS.exe for automated deployment in an automated build environment.

Now I'll show you how to use RS.exe from the command line. I won't go into detail about how to use it from specific tools as that should be straightforward for anyone that is familiar with the tool they are using.
Below is a sample RS call with three input parameters.
-i is the file that contains your rss script. More on that later.
-s is the address of your ReportServer web service
-e is the web service end point. Remember from above that there are multiple web services available and you can target any of these endpoints using RS.

rs.exe
  -i SSRSDeploy.rss
  -s http://localhost/reportserver
  -e Mgmt2010


Now that we know how to call RS, the key to making it useful is your custom .rss (Reporting Services Script). It is in these scripts that we can write custom code to deploy reports, data sources, images and create folders as well as delete items.

I have posted a sample rss script that has the following generic methods.
CreateFolder: Creates folders in Report Manager
PublishItem: Publishes reports and shared datasets
PublishImage: Publishes images
DeleteItem: Deletes any item

These methods are essentially wrappers around the rs.CreateCatalogItem, rs.DeleteItem, rs.CreateFolder, rs.SetItemDataSources, and rs.SetItemReferences methods. They add additional exception handling and deal with the nuances of publishing reports in locations different than specified in your Visual Studio project and .rdl files.

Note the use of what are referred to as Global Variables. You can pass values into your rss scripts by using Global Variables. You don't declare these variables you simply refer to a non-declared "variable" in your rss script and RS is smart enough to know that you want to set them at the command line. The way to set them is by using the -v argument. See below for another example.

rs.exe
  -i SSRSDeploy.rss
  -s http://localhost/reportserver
  -v rootPath="/Sample"
  -v DWServerName="localhost"
  -v DWDatabaseName="MyDataWarehouse"
  -v sourceFilePath="C:\SourceCode\SSRS"
  -e Mgmt2010

With the sample script you should be able to quickly and easily create an automated process for working with your Reporting Services instances.

Download .rss script

Nested Loops Join - No Join Predicate

I was recently troubleshooting a severe query performance issue when I ran into a query plan that used a nested loops join to combine data from two tables. The join was 97% of the cost. When I looked closer I saw that there were no join predicates specified. The join in the query looked pretty straightforward to me and used a single column on each side. Why would the optimizer do what equated to a cartesian join?

It turns out the query was a little more complicated than it looked. I was actually joining two views that were part of a view-based (non-persisted) dimension model. I was joining on logical primary and foreign keys which made sense.

When I dug into the views I found that one of the key columns was based on a user-defined fuction that was converting a datetime to an integer value so that it could be used to join to a date dimension view. Once I changed the underlying view to apply the same logic as the UDF at the view level, so that a call to the UDF was unecessary, the query executed within a second as expected.

Other behavior that I noticed was that if I changed the inner join to a left join, the optimizer came up with a different much more efficient plan. This appears to be a flaw in the optimizer but I would like to speak to someone at Microsoft before making that claim.

The lesson learned here is that if you have a poorly performing query due to a nested loops join with no join predicate(s) it's not ncessarily the case that you're missing join criteria in your query (as all the posts I was able to find seemed to point to). The culprit could be a UDF on a join column.

Querying System Views for Column Names

I often find myself needing a list of columns from a table or a view for a query, documentation, troubleshooting, etc. Easy enough to drag and drop them into a query window using SSMS but that's a pain. I have better things to do with my time. All of this information is available through SQL Server system views. The following query is also ANSI compliant.

SELECT

  ORDINAL_POSITION
  ,COLUMN_NAME
  ,DATA_TYPE
  ,CHARACTER_MAXIMUM_LENGTH
  ,IS_NULLABLE
  ,COLUMN_DEFAULT
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = 'Customer'
ORDER BY
  ORDINAL_POSITION ASC;

You can do something like the following to give you a comma separated list that can then be used for a query.

SELECT

  COLUMN_NAME + ','
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = 'Customer'
ORDER BY
  ORDINAL_POSITION ASC;

Simple. Straightforward. Time-saver.

OPTION (FORCE ORDER)

I was recently faced with a difficult performance issue. In an effort to save a client some time and money I convinced them that a view-based dimensional model was the way to go over a table/ETL based solution. I will blog about when this option is an option and the benefits to going this route some time in the near future. Anyhow, based on this decision I ended with a performance issue that I wouldn't have otherwise had.

The view-based dimensional database consisted of 30-40 fact and dimension views. As I created these views I made sure to test them for performance issues and tuned accordingly. I tested each view on its own and I tested individual joins between fact and dimension views.

Everything was working fine until I began creating sample queries to show end-users possible queries they could write against the new database. What I found was that when a particular dimension view was joined to a fact view along with one or more other dimension views I had unexpected performance issues. Queries that should take a few seconds to execute were taking 45-50 seconds.

In looking at the execution plans I saw that the optimizer was not doing what I had expected based on what I had seen for plans for the views executed on their own. I saw weird things like LEFT OUTER joins instead of INNER joins to my views resolving or partially resolving the issue.

I couldn't understand why the optimizer would treat the SQL in my views differently now that it was joined to other tables or views. To be honest, I'm still not sure. What I am sure of is that there is a query hint that forces the optimizer to create a plan for the view portion of the SQL irrespective of any other objects in the statement by forcing the optimizer to preserve the table/join order contained in the view definition. This hint is the FORCE ORDER hint and can be added to the end of the SQL statement using the following syntax.

    OPTION(FORCE ORDER)

Below is an example.

SELECT *
FROM table1 t1
    INNER JOIN view1 v1 ON t1.col1 = v1.col3

OPTION(FORCE ORDER)

Windows 7 DSN Creation Guide

Lately I've been getting a lot of requests for step-by-step documentation for non-technical end-users. Well actually not just lately. I always have. But lately I've been getting tired of writing the same document over and over so I've begun to create a little library of step-by-step guides as well as templates for standard database related documentation.

My latest request was for creating a SQL Server DSN on Windows 7. Attached are the steps for creating one. You can find these same instructions in an MS Word document that contains screen-shots for the entire process here.

Follow the steps below to create a DSN on a Windows 7 machine. These steps are also very similar for prior Windows versions.

Click Start->Run. Type ODBCAD32.exe and hit enter.

Another option would be to open the Windows Control Panel and then open Administrative Tools. You should see Data Sources (ODBC). Double click this icon.

Click on the System DSN tab.

Click Add.

Select the appropriate driver.

Click Finish.

The steps below are specific to setting up a SQL Server Native 10.0 DSN.

Enter the Name you wish to use for the DSN. This is usually the name of the database you’re connecting to or if you intend to use it for multiple databases you can use the server name.

Enter the name of the Server your database(s) reside on.

*If your database(s) reside on a local non-named instance of SQL Server your Server name should be localhost or the name of your machine. If you are running a named instance of SQL Server (which is the case for the default installation of SQL Server Express Edition) your Server name should be something like localhost\SQLExpress.

Click Next.

Choose your authentication type. If your Windows Active Directory account has the necessary privileges use Integrated Windows authentication. Otherwise use SQL Server authentication with a SQL Server user name and password that has the necessary level of permissions for the database you are connecting to.

Click Next.

Click the Change the default database to checkbox and select the database you want to connect to. This will be the default database when using the DSN. If you intend to connect to multiple databases you can leave this box unchecked.

Click Next.

Click Finish.

Click the Test Data Source . . . button to ensure that the DSN has been configured correctly.

Click Ok.

Click Ok.

Click Ok.

Visual Studio 2010 Database Projects - MSBuild Issue

I've recently begun using Visual Studio 2010 database projects (DBPro). I think it's a great tool and having been a .NET developer for many years it is nice to see SQL Server development get the respect it deserves in a full-fledged (well almost) Visual Studio project type.

While development productivity has been great with features like object level dependency checking, automated deployment with VSDBCMD, database dependency, data generation, pre and post build and deployment scripts, etc. I've recently encountered a rather glaring shortcoming that keeps Database Projects from having the full functionality of a C#, VB.NET, ASP.NET, etc. Visual Studio project.

The problem is related to the build process. Currently, to build a database project you need to have Visual Studio present on the machine that you are executing the build on. That might not seem like a big deal to many people since you might assume that any developer doing a build would have Visual Studio installed. Not the case. I'm currently working in a Java shop that uses SQL Server as the relational database platform. There are about 12+ developers that run any number of ANT targets to build their development environments on a frequent basis.

ANT can call MSBuild targets. That's not the problem. The problem is that to build a Visual Studio database project you must have certain MSBuild targets and a number of Microsoft assemblies in the GAC that are only installed with Visual Studio 2010. This is not the case for other Visual Studio project types. In most cases all that is needed is .NET 4.0.

While we're not dead in the water we have to rely on a solution that is less than ideal although not altogether terrible. What we have to do is build the project on one of the database developer's machines that has Visual Studio present. We then take the build output (.dbschema, .deploymanifest, etc.) and commit it to source control. From there any developer can run the ANT targets which then call VSDBCMD and deploy the database to their local development environment.

What we have works but it is a departure from how a standard Visual Studio project would work. It also requires the commital of "compiled" code which is never a good idea. The biggest struggle is making sure we the database developers build in the right configuration (Release) and commit the correct build output. It's an easy thing get wrong and can cause a lot of wasted troubleshooting time down stream if it is done incorrectly.

I have spoke to Microsoft about the issue. I am waiting to hear back from a Microsoft support escalation engineer that is in term getting in touch with the DBPro project manager for alternative options. Additionally, I have submitted a request on Microsoft Connect. Feel free to check if any progress has been made on the Microsoft Connect website.

Report Functional Requirements - Too Often Overlooked

A template for what follows can be found on my personal website.

Reports are often an afterthought and commonly believed to be an easy thing to create. As a result they are often overlooked during the requirements gathering and planning phases of a project and when they are considered they tend to be the red-headed stepchild and don't get the attention they deserve. This is usually a time consuming and costly mistake.

Reporting can often be complex and requirements interpreted many different ways by many stakeholders (e.g. business users, business managers, developers, architects, etc.). Reporting tools have come a long way and can do many different things many different ways. The tools can actually serve as a fully functioning UI (user interface) in many cases. Not to mention, data itself is just that - data. It can be manipulated in infinite ways to provide information that not only looks different but is actually different and leads to different values and therefore conclusions.

Most, if not all of the time, report writing will be an iterative process whether your an agile shop or not. The key here is to not iterate to the point that a single report costs $20k to create (yes I have seen this) and/or that the developers, business analysts, end-users, etc. are completely frustrated and just want to say it's done so they don't have to deal with it anymore (yes I have seen this as well).

I believe there are a few key here that can help avoid the $20k meaningless report.

1. Gather the minimum set of requirements necessary to create the fundamental pieces of a report. Use a template to collect this information. This will ensure that you have consistent information from which you can create a consistent set of reports.
2. Require the report requester to provide mock-ups. When possible, have them create the mock-ups in Excel and use real formulas for calculated columns. The greatest value mock-ups provide is that they force the requester to think about what they are asking for. They make the requester realize things like their idea was malformed and what they're asking for doesn't make sense and/or isn't possible. It also helps them think through things they wouldn't have thought about until the first iteration of report was delivered which helps cut down in the number of iterations and the need for developers and/or BAs to asked "stupid" questions that can annoy and embarrass the requester.

Minimum Set of Requirements
It doesn't matter what tool(s) you are using. It can be Crystal Reports, Cognos Report Studio, Jasper Reports, SQL Server Reporting Services, etc. They all have the same basic functionality and therefore the same requirements needed to develop a report using them.

1. A meaningful title for the report (e.g. Active Customers Name & Address)
2. A concise description of the report that includes
• The primary "thing" being reported on (e.g. customer)
• The subject of the attributes being report (e.g. name and address)
• The main criteria (e.g. status = active)
• For example, "The Active Customers Name and Address report includes the names and addresses of all customers with a Status of "Active" and is grouped by State of residence."
3. A list of the columns with
• Database table and column name if possible
• Label text for the report
• Any calculation
• If it's a link to another report or external source and if so a description of how the link should work
• If it's a sortable column
• Right, left, or middle alignment
• Value formatting (MM-DD-YYYY, YYYY-MM-DD, $100, ($100.00), etc.)
4. Layout
• Column order
• Landscape or portrait
• Cross-tab or row/column
• Headers and footers
• Image/logo
• Creation date
• Page number and format (p. 1, page 1 of 10, etc.)
• Selected parameter value
5. Grouping
• If grouping, what are the group levels?
• How should each group be sorted?
• Should the group have subtotals and if so which columns and what is the calculation if complex (e.g. weighted average)?
• Should there be a page break and/or line before/between/after the group?
6. Sorting
• How should the report be sorted (which column(s), in what order, and which direction - ascending or descending)?
• Should the user be able to specify the sort order (dynamic sorting)?
• If grouping is present, what order should the groups be sorted?
• Is run-time sorting allowed on a column after report execution?
7. Filter Criteria
• What filter criteria (WHERE clause) are there if any? For example, should only "active" customers be included. Anothe example would be "completed" orders.
• Should these criterion be evaluated using an AND operator or an OR operator?
• The column(s) that the filter is applied to should be clearly stated.
8. Parameters/Filters
• Label/text for the parameter
• Drop-down, text, yes/no
• Are multiple selections allowed?
• What are the values or where do they come from (static list of values or from a table)
• Should we assume that all criteria are applied to the result set or are the "OR" conditions?
• Does one parameter drive the value list of another parameter (e.g. Country changes values in state/region parameter drop-down)?
• What column or field in the result set should this parameter/filter be applied to?
9. Schedule
• If the report is deployed to a "report server" should it have one or more standard schedules on which it runs?
10. Export format
• If the report is deployed to a "report server" what is the default format (Excel, Word, CSV, PDF) that it should be written to? If there are scheduled instances, what format should those be written to?
11. Recipients (burst or data-driven - what is the logic?)
• If a scheduled report instance is burstable (Cognos) or a data-driven email subscription (SSRS) who are the recipients? Is it a static list or is it data-driven?
12. Misc
• Charts or graphs?
• Drill-downs?
• Links to other reports (drill-through)?

This isn't an exhaustive list but it is a good starting place to build a template from which you can more effectively develop reports. Open communication and an iterative approach is good but why waste iterations on requirements you could have known upfront? Save time, save money, and produce a quality product. Our job as developers is to deliver value. This is one way to help do that.