Raiser's Edge Tables

Keisha Thomas

Hello!

My organization is using a data lake, over-laid with Tableau to create custom reports and dashboards. We are encountering an issue in linking Write-Offs back their original pledge. We have realized Pledge ID, System Record ID, etc. do not work. Has anyone had experience with this, or know what the missing link is? I feel like there has to be a field that links these two back together as a write-off is just a “type” of installment. ANY insight would be helpful.

Alex Wong

@Keisha Thomas
Not sure what you mean by data lake. How are you accessing the RE data?

If you have direct access to the RE database instance… then you can use the RE schema documentation found in the help folder on the machine that have RE installed. There are a few tables that specifically link pledge to installment, payment and writeoff

Keisha Thomas

@Alex Wong a data lake, is just a repository for data from multiple systems, that some orgs use to pull data into a single report/dashboard. Unfortunately, I don't have access the the RE schema because we are blackbaud hosted.

Alex Wong

@Keisha Thomas
Ok so similar to a data warehouse. Question is how are you getting RE data into the data lake? If you are blackbaud hosted.. there's really only 3 ways to do so. 1 is to get database backup that you restore. 2 is to call SKY API and then store the data into the data lake. 3 is to use REQueue to export and then store into data lake.

Depending on how you get data out of RE there's different method of getting pledge installment payment writr off data

Keisha Thomas

@Alex Wong Yes - basically the same as a data warehouse. We are dumping our nightly back-up to get the info. Any thoughts?

Alex Wong

@Keisha Thomas
if you are dumping the nightly backup that means you are accessing the SQL data directly. So same response I was giving as if you have direct access to SQL. Use the RE Schema help file. Or if you can't locate it (you need to install RE on a local machine to get it) you can ask Blackbaud support to profile that to you. I don't want to attach the file here as I don't know if it's ok to do so.

Table to look into:

INSTALLMENT - shows installment on a pledge (installment date, amount due, etc)
INSTALLMENTPAYMENT - shows what payment (this can be payment or write off) gift is paying which installment of a pledge (installmentid, paymentid, pledgeid, amount)

You just have to remember that 1 payment gift can be paying:

• multiple pledges (different pledges)
• multiple installment in a pledges

it is a massively many to many relationship, but once you get the hang of it, it is pretty easy to understand.

There are also “views” that was defined by BB to use that's pretty handy too, such as PLEDGEPAYMENTS, linking directly the payment id with the pledge id and the amount used to pay the pledge.