CouchRS – Building Your First Report against CouchDB using SSRS

In my introductory post for CouchRS, I explained where to get the code and how to install the custom processing extension.  Now that you have CouchRS installed, let’s look at a very simple example on how to use it.

In Visual Studio 2008, our first step is to create a new Report Server project and add a Shared Data Source to the project, pointing it to our CouchDB server.  The changes we made to our RSReportDesigner.config and RSPreviewPolicy.config (explained in my earlier post) enable the CouchRS processing extension to be displayed as one of the possible data sources when creating a new data source.  Below is a screen shot of creating this new shared data source:

Note that all we have to do is name it, select “CouchDB Connection” from the drop down, and for the connection string we simply provide the server, port and database name.  CouchRS *does* support credentials, but it does not support Integrated Security or Impersonation.  To include credentials with your CouchDB connection, click on the “Credentials” tab, enter a username and password and click OK.

With the shared data source now in place, we can create a new (blank) report.  In order for this report to utilize the project-level shared data source, we need to add a report data source as follows (note that you simply select the available shared data source next to the “Use shared data source reference” option):

Now we’re ready to create our first data set, but first we need to discuss “SeeNoSQL”.  “SeeNoSQL” is a very lightweight custom query language that Josh Bush and I created in order to accomplish the following:

• Provide SSRS developers with a readable, “almost-SQL-like-but-not-too-much” way to express queries.
• Enable complex parameter-driven constraints, including precedence and “AND/OR” chaining within those constraints in order to abstract the need for multiple HTTP requests away from the developer.
• Provide a concise way to enable users to set more advanced query options, while abstracting the unnecessary elements of it away from the developer.
• If possible, use the same query syntax against multiple NoSQL storage engines to provide consistency (time will tell on this one as I plan to release custom processing extensions for other NoSQL DBs).

If you’re even moderately familiar with CouchDB, you know that all interaction with CouchDB is done via HTTP.  Josh and I felt that it would be more intuitive for a developer to write in a declarative style rather than wiring up the raw HTTP requests in the data set’s command text.  So, let’s start with the simplest possible query in “SeeNoSQL”.

In the above screen shot, we’ve named our data set, selected the report data source we created earlier and we’ve entered this command:

FROM DOCUMENT(‘fff2aaa6-236c-44f2-b2bf-e8a37360768f‘)

Hopefully, you can infer what’s going on here.  The results being returned will be “FROM” a single “DOCUMENT” with the ID of “fff2aaa6-236c-44f2-b2bf-e8a37360768f”.  It really doesn’t get much simpler than this!  However, this report is not going to be terribly useful if we have the Couch document ID hard coded in our command text.  SeeNoSQL supports parameters, so we can parameterize like this:

(In future posts, we’ll discuss how to give the user a more meaningful parameter than one that takes an ugly GUID as input!)

So – what happens if we click on “Query Designer” and actually run this query?  Here’s the result:

“WAIT A SECOND!”, you say, “I thought we queried for one document!”

Yes, we did.  In my earlier post I explained that since we’re crawling an object graph, it must be ‘flattened’ into a relational structure (since Reporting Services is using a DataReader under the hood).  And by ‘flattened’ I mean that we have to find the Cartesian product of each document’s members.  This means if the document has no child collection(s), it’s effectively already flattened.  However, this document has a collection of “SalesOrderHeader” hanging off the root, and each of those items has a collection of “SalesOrderDetails”.  Our example customer has two orders, the first with one line item, and the other with three line items.  The first order and it’s one item can be flattened into one row alongside the Customer information.  The second order has to be flattened into three rows – one for each order detail, alongside the order and customer information – hence the four rows we get back.  The closest analogy to this would be a SQL JOIN causing the results to be “blown out” based on the “many” side of a “one-to-many” relationship.  The implications of this are obvious: To neatly display this data in a report, you need to be mindful of how to properly group it in a Table or List region to avoid unnecessary duplication.  “So”, you ask, “Is there a way to query for only the data I want, rather than getting whole documents back and having to group the records in order to eliminate duplication?”  YES!  You can use a view in CouchDB to customize what is returned so that you only have to deal with the data you want.  I’ll be covering CouchDB views in my next post (this is where “SeeNoSQL” shines, as well).

Based on the data returned for this document, I created a simple “Customer Summary” report which displays all of the orders (with detail lines) for a given customer:

It’s worth noting that the images are being dynamically rendered.  In the SQL version of AdventureWorks, these images are stored in varbinary fields, so I included them in my upload to CouchDB.  With the image binary data serialized to JSON, it’s as easy as telling the image control which field to point at for the data, and selecting the MIME type for the image.

This is a very simple example, but I hope it helps you appreciate the power of leveraging SSRS against CouchDB.  You can develop the same type of reports using CouchDB as a data source as you can against SQL Server while using a storage medium that’s more friendly to the actual structure of your domain model.

We’ve barely scratched the surface….coming up we’ll talk about using map/reduce in CouchDB views, and SeeNoSQL to produce complex requests against CouchDB.

[I plan to include the sample reports under the directory structure of my Git repository, but you will not see them in the VS2010 solution, since VS2008 must be used to create these reports as of the time of this post.  Look under the “src'” folder to find this sample project.]