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

Tableau Test Drive for Raiser's Edge Reporting - Installing Tableau Desktop

With the advance in all of the BI and Analytics tools in the marketplace, I thought I would give one of the most popular a test drive:  Tableau.  Below are other posts in the series:
Part II - Connecting To RE and Planning our Dashboards
Part III - Our First Worksheet

Tableau has numerous offerings in their suite of products including Tableau Desktop, which appears to be the primary one you would 'develop' your dashboards and BI reports, Tableau Server and Tableau Online, which appear to be engines where your developed dashboards and reports can be published for consumption by others in your organization.

So lets see how easy it is to get started on Tableau Desktop.  The first step is going to the Tableau website and requesting a download of Tableau Desktop:  easy.  You can go to Tableau Desktop and click on the Try It Free link to start your download.

The Tableau Desktop webpage with the Try It Free link

The size of the download was only a little over 100 mb, so not too big.  Once you have downloaded the software, just go find the setup executable on your hard drive and double click on it. The Windows installer is launched and you get a rather simple wizard.  The first screen just allows you to view the License Agreement and to provide some minimal customization of the product.

After clicking the "Install' button, you get the typical "Installing" wizard form with a status label and progress bar.  It only takes a minute or two for the actual installation to take place.

Once the installer is completed, Tableau is launched and you can start your free trial, 'start trial later' (which I recommend if you know you can't start playing around with the tool immediately so you don't waste valuable days of your trial), or activate your product.

Wow, that was easy.  Installing Tableau Desktop was that easy.  Now comes the more difficult, and much more fun, part - creating BI and Analytics dashboards and reports.  So lets give it a spin and see how it performs and the cool features it may have.

When you first start your trial, Tableau was nice enough to have some sample "workbooks" which appear to be their "projects" that you create your dashboards and reports in.  I think these samples are actually pretty good.  In addition, there are valuable links to training videos that you can watch, which are pretty darn good as well.  

Sample workbook that  comes with Tableau Desktop

All in all, a pretty positive first impression from Tableau.  I did initially install on one of my laptops running Windows 8.1 and it would crash (the blue screen of death) due to various issues, mainly memory issues even though I have enough.  My Windows 7 laptop (ironically with less memory than the Windows 8.1) had no issues whatsoever.

Up next, lets plan out our first workbook and get connected to RE.

Reporting on Raiser's Edge Recurring Gifts with the RE Recurring Gift Reporter Excel Add-In. It is so easy!

Hi everyone.  Thanks again for coming to my blog.  Recently I posted about different ways to report on The Raiser's Edge.  Then I took a deeper dive into Microsoft Excel reporting.  In this edition I am going even further, focusing on one example of a pretty cool way to integrate The Raiser’s Edge with Microsoft Excel.  I am thinking that this will be one post to highlight what I did but I could see it turn into multiple posts.

Awhile back, I saw a post on some forum about how to report on recurring gifts.  This got me to thinking about one of the challenges of reporting on recurring gifts: that it is difficult to get a report of future recurring gift installments (or future revenue from recurring gifts).  At least I don’t know of a good way to get at this info right now.  And I would be willing to bet that many organizations would like to be able to see what their expected future revenues are, and particularly for future recurring gift installments.  Having this information could result in better future planning of future fundraising success.

OK, lets cut to the chase:  What did I do?   I used Visual Studio to create a Microsoft Excel Add-In which can be installed on a pc or laptop running The Raiser’s Edge and Microsoft Excel  (disclaimer versions must be compliant; e.g. this will not run on Excel 2007 as it was built for 2010 and higher;  however also note that it has not been tested with Excel 2013 yet).  This Excel Add-In hooks into The Raiser’s Edge API, prompting the user for a RE Login if they are not already logged in and then generates the raw data, along with reports and charts (if selected) automagically in Excel. 

Lets circle back around and discuss what this stuff looks like and how it behaves.   After the Add-In is installed, the next time you open Microsoft Excel 2010 you should see a new ribbon available named RE Recurring Gift Reporter. 

Once Excel opens and you navigate to the RE Recurring Gift Reporter ribbon, you are presented with several options.  Lets take a look at the View Future Recurring Gift Installments, which is really a forecasting report.  Clicking on the button reveals the following form:

As you can see it is pretty straight-forward.  But that is the point.  Keep it simple and if anyone wants more features they can easily be added to the form.  But lets take a closer look, the user can select to create pivot tables and charts or not.  If no pivot tables are selected, then just the future recurring gift installments are exported in raw data format.  In addition, the user can select an "End Date" for future installments.  As a result however far in the future you want to report on, you can.

After you click the "Create" button then what you selected will be exported into Excel, resulting in something like the worksheet below:

Again, it really is that easy!!

The second large button, View Recurring Gifts, functions just the same as the other button, but provides reports on the recurring gifts themselves, not future installments.  When those reports are run it may look like the spreadsheet below:

It is really that easy.  Open Excel, select a report to run, click the settings, (possibly log into RE), and the report is generated.  You can run it multiple times, day after day and begin to have some intelligence about your future revenues.

A few other notes about how the Add-In works:

It exports the raw data into a spreadsheet and creates a named range from which it creates reports.  You can run it to export only the raw data and you then work with the data yourself.  Or you could select it to create the pivot tables and charts, giving yourself a starting point for how to use the raw data.

In the event that your database has a large number of recurring donations and you select a very large time period to report on, it may take a while to load the data into the spreadsheet.  This could be improved and function slightly different should you need that capability. 

Only a subset of the available columns in RE are used in this Add-In.  I focused on the core ones for now.  For a reasonable fee I can customize the Add-In for your needs, including adding more columns available in the Add-In.

 It pulls gift split information, so it can report on campaign, funds, and appeals for split gifts.  In addition, the system record id for the gift is included so that you can group gift information together to report on total gift information (e.g. the total amount for the gift not just the split amount). 

     
So what you say?  I say great question, or is that really a statement.   I would turn it back around and ask you the following questions:
     1.  Can you tell me how much expected future revenue you have coming in per month from recurring gifts over the next X number of years?
     2.  Can you tell me how much money you have coming in for a specific campaign, fund, or appeal over the next X number of years?
     3.  Can you tell me how much money you have coming in for a specific primary constituency (from the constituent record) over the next x number of years?
     4.  Can you tell me how much money you have coming in for a specific fund and gift constituency (from the recurring gifts) over the next x number of years?
     5.   If you can do any of the above, how long will it take you to pull that information?

I think these are pretty great questions to ask.  We can do 1 through 4 plus produce all other kinds of reports slicing and dicing future recurring gift installments and we can do it in a matter of seconds, maybe minutes depending on how much data you have.  I think that is pretty darn awesome. 

Guess what else?  Your Executive Director, or Development Director, could get this information without having to ask their Database Manager to “produce the report and send it to me”.   It is that easy to use.  Plus you now have the data in a workable format and you can create a lot of other reports from the data presented to you.  This data is not static, like a Crystal Report would be.  You can trust the data as it is coming directly from The Raiser’s Edge (assuming that is that you require your employees to enter this information into The Raiser’s Edge).

OK – Now the sales pitch.   I know, I know, but it is necessary for me to continue providing these great tools for non-profits.  Go check it out and request a download to see for yourself.  Even better, we can customize it further to tailor it to what you need at a great rate.   We can even do something similar with other Raiser’s Edge data or  even data that exists in other databases.  Stop struggling with getting your data into a format that is difficult to use and extend, or takes forever to produce.  Reach out to us and we can tell you more about the product and discuss how easy it would be to make it even better for your needs.