Introducing CouchRS – an SSRS Processing Extension for CouchDB

Unless you’ve been holed up on a desert island (or perhaps trapped in a legacy .NET 1.0 shop), odds are you have heard of the “NoSQL” movement by now.  Having a background that’s been nearly equally spread across application and database development, I was intrigued when my good friend (and talented developer) Alex Robson first explained CouchDB to me.  I had what are probably the “initial typical” reactions: “Wow, no ORM?  Is it fast?  What about transactions?  Wait, there’s NO schema?  Wow, NO ORM?!”  It was about that time that my data warehousing background piped up and said “Hey, I wonder what kind of analytics we could get out of it?”  As we began to use CouchDB more and more in our applications, we considered approaches that would allow us to leverage our existing BI infrastructure along side of it.  One option – viable, though loaded with more work – was to set up a message bus with listeners that subscribed to commands coming from the UI (or perhaps a ‘trigger’ service monitoring CouchDB for changes).  Those listeners would be responsible for populating a relational (or dimensional) database.  While this is technically viable, it does introduce more potential points for failure, and an overall increase in complexity.  The real world result, then, was we developed our applications against CouchDB, and delayed integrating our BI tools (SSRS, in this case) as long as possible, since we – like most other shops – were pointing SSRS at SQL data stores.

Enter CouchRS.  Several months ago I mentioned to Alex that I wanted to create a Custom Processing Extension that could allow our reports to simply talk to CouchDB directly.  Easy enough, right?  Well, not so fast.  Remember, CouchDB is a document-oriented database.  It will store anything you throw at it – in the same database.  Want to persist an object graph serialized to JSON?  Done.  Want to save the binary stream of a pdf?  Done.  Feel like storing a recipe list, complete with picture attachments?  Done and done.  With such a wide variance in the type & content of the documents in a Couch database, some general (and powerful) assumptions had to be made.

First – the documents are expected to be JSON.  No big leap there, since that’s standard with Couch.

Second – all the documents in a result set would have to be traversed, and a normalized set of “fields” generated in order to ‘present’ those documents as a relational data set.  Bear in mind that this includes flattening any document that represents an object hierarchy (i.e. – a customer, that has orders, that have order lines, etc.) by producing a Cartesian product of each object graph.  (Alex and Josh Bush worked serious magic in this area.  This project would not have been possible without their collaboration.)

Once we were returning results from Couch, Josh and I collaborated on creating a lightweight query syntax (“CouchQuery”) using “Irony” – a fantastic library created by Roman Ivantsov.  It’s SQL-esque enough to be intuitive and readable, without forcing “Couch” into a “SQL” bottle.  I’ll explain more about how to use this syntax in future posts, but suffice it to say that there are exciting implications in that it supports a “UNION” operator, and parenthetical logic (AND, OR, etc.) in the “WHERE” clause, meaning multiple requests can be made against a CouchDB in ONE query, and the results will be returned to the report as if they were one request.  This gets incredibly cool when you start “UNION”-ing the results of different views.

In upcoming posts, I will elaborate more on how to use the query syntax, discuss the necessity of being familiar with Couch views (and map/reduce in general) and present some sample reports.

How do I get it?

The source is located on GitHub and is available under the Apache 2.0 license (i.e. – it’s yours to download, modify, use in open & closed source applications…so have at it!)

How do I install it?

Alas, until I have time to write an installer you must endure this lovely process (it looks worse than what it is, don’t worry):

• Download the source, build and grab the CouchRS.dll from the bin/Debug or bin/Release
• For use with Visual Studio 2008 SSRS Designer:
  • Find your Visual Studio installation folder (usually C:\Program Files\Microsoft Visual Studio 9.0\ or C:\Program Files (x86)\Microsoft Visual Studio 9.0\ on x64 machines).  From there, browse to \Common7\IDE\PrivateAssemblies and paste the CouchRS.dll (along with its dependencies: Irony.dll, Newtonsoft.Json.dll, Symbiote.Core.dll & StructureMap.dll) file into that folder.
  • In the PrivateAssemblies folder, you need to edit two .config files: RSPreviewPolicy.config and RSReportDesigner.config.
    • RSPreviewPolicy.config:
      • Find the “CodeGroup” element that looks similar to:
        
        <CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="Execution" Description="This code group grants MyComputer code Execution permission. ">

      • Find the end of the children of this CodeGroup and paste the following element (be aware that the Url path needs to match where you pasted the CouchRS.dll:

        <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="CouchDBCodeGroup" Description="Code group for CouchDB data processing extension"> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\
        CouchRS.dll"/> </CodeGroup>

    • RSReportDesigner.config:
      • Find the “Data” element and add a new child element that looks like this:
        
        <Extension Name="CouchDB" Type="CouchRS.DataProcessingExtension.CouchDbConnection
        ,CouchRS"/>

      • Next, find the “Designer” element and add a new child element that looks like this:
        
        <Extension Name="CouchDB"
        Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,Microsoft.
        ReportingServices.QueryDesigners"/>
• For use with a SQL Server 2008 Reporting Services Installation:
  • Find your Report Server’s bin directory (located {SQL Install Directory}\{ReportServer Instance}\Reporting Services\ReportServer\bin – for example: C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin) and paste the CouchRS.dll file there (along with its dependencies: Irony.dll, Newtonsoft.Json.dll, Symbiote.Core.dll & StructureMap.dll).
  • in the “ReportServer” directory (one level up from the ‘”bin”), edit the following files:
    • rsreportserver.config
      • Find the Data element and add the following child element:
        
        <Extension Name="CouchDB" Type="CouchRS.DataProcessingExtension.CouchDbConnection
        ,CouchRS"/>
    • rssrvpolicy.config
      • Find the CodeGroup element that looks similar to this:
        
        <CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="Execution" Description="This code group grants MyComputer code Execution permission. ">
        

• Add a new child element that looks like this:
  
  <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="CouchDBCodeGroup" Description="Code group for CouchDB data processing extension"> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\
  Reporting Services\ReportServer\bin\CouchRS.dll" /> <IPermission class="SecurityPermission" version="1" Flags="Execution"/> </CodeGroup>
• You should recycle the SSRS Service at this point.
  

There is more to come!  If your interest is piqued, please leave a comment, Retweet this post and watch our repository on GitHub…