Tableau Test Drive for Raiser's Edge Reporting - Connecting to RE and the Planning

Now that we have Tableau Desktop installed, it is time to finish some basic setup, which includes establishing a new connection to The Raiser's Edge so that we can begin actually creating our dashboards and reports.  But to establish our connection we have to do something else:  Figure out what it is we actually want to report on.

To keep it pretty simple, I decided that I would create some dashboards and reports based on basic constituent demographics; stuff like preferred address details, primary constituent code, whether they were deceased, inactive, or an individual vs. organization, etc.  Then I said,what might I want to see and I spent about 5 minutes sketching out on a plain sheet of paper some demographic dashboard components (see the image below).  I recommend that you spend more time if you were planning your own.

My rough (very rough) sketch of the dashboards I wanted

Once that I established some rough estimate of what my dashboard and reports might look like, I then had to figure out how I was going to get that information.  As most of you know, getting info from The Raiser's Edge can, at times, be a bit complex.  So I knew I wanted preferred address.  I could have chosen all addresses but figured it made sense to keep it simple and only get the preferred address.  Then I decided that I wanted constituent codes, but again I decided I only wanted the primary constituent code (the one that is at the top of the list on the records Bio 2 tab).  And finally, some information from the record itself (deceased, full_name, etc.).

Now that I established what I am pulling from the database, I then had to figure out how I was actually going to get that info.  There were several ways to do it.  I could create a view in the actual Raiser's Edge database that pulls all of that together, then in Tableau I just pull the info from the view.  I could write my own custom SQL in Tableau (which is essentially writing the same SQL for the view option above).  And finally pull in all of the tables into Tableau and try to do some fancy joining in the tool.  To be honest, based on what I am doing, I am not even sure if Tableau could have done that - but I am going to assume that it could, but I chose not to.  I went with option 1, create the database view:  it seemed the easiest to do at this time so that I could focus on the dashboards and workbooks in Tableau.

Some code of the view I created on the RE Database

So now lets create a new connection in Tableau to our view.  From the home page, select the "Connect to Data" link on the left sidebar.  Then you will see a list of native connections that Tableau supports.  You will select Microsoft SQL Server from the list connections.  This is pretty basic connection information, just as if you were connecting to the Raisers' Edge server instance that the RE database exists on via SQL Management Studio.  Enter your details and select "connect".

Connecting to SQL Server in Tableau

From there you will then select the Raiser's Edge database in the dropdown at the left.  Once you do so, all of the Raiser's Edge database tables and views, as well as stored procedures are loaded for your selection.  This may take a few minutes to load. Once loaded, you can then select the tables, views, etc. that you want by dragging them to the top box in the main part of the form.  This is where you can bring multiple tables or views over and create joins on them as I mentioned earlier if you like.  For my case, I have 1 database view already created that I selected.  I also decided to add some gift information to my view just in case I need it for other dashboards.

Connected to the Raiser's Edge and selected the view to use

Once you have selected the tables and views that you want, you can then customize the connection by changing the field names, changing the datatype, etc. in the grid at the bottom panel of the main section of the form (as seen in the screenshot below).  Once you have everything setup for the data you want to be used in your workbook, click on the "Go to Worksheet" link in the middle orange section of the form and you are ready to build your dashboards.

You can also choose to have this connection setup to use the Live data or to Extract the data into the Tableau "Fast Data Engine" which allows for offline reporting and doesn't affect the performance of your live Raiser's Edge database.  This will be an area that hopefully I can explore - pending how much time I have left in my trial.

Again, all in all, pretty straight-forward and pretty easy.  Tableau continues to be very user-friendly in terms of installation and getting setup.  I thought the real fun would start after my last post (and designing your dashboards is fun), but in the next post we will create our first worksheet/dashboard, or something:)

Other posts in our series can be found at:
Part I - Installing Tableau
Part III - Our First Worksheet