Benefits to SQL?
So -- what can you do with SQL that you can't do in RE itself, and how does that benefit your data integrity and maintenance?
Comments
-
Hi Faith,
The benefits are of SQL revolve around relational algebra. SQL semantics gives an unfiltered access to expressing even the most complicated relationships within data.
The problem with the query/export tool is that while simplifying relational algebra to be user friendly in turn RE sacrifices some of the depth that the Structured Query Langauge (SQL) gives the user.
I'm not able to find a way to SQL in RE 7, we are hosted by Blackbaud. It's been frustrating because I can't find a 'how-to' or a definate answer if it is possible.
2 -
Faith Murray:
Hello all you SQL-users out there! I'm looking for input on what the benefits are, to a database manager, for learning SQL (if any). I'm always looking to expand my career skills, and that seems like an obvious next step since many other types of database software types require SQL usage. However, right now our IT is the only one who uses SQL for RE, and if I expressed an interest I'm sure they would ask, "What for?"
So -- what can you do with SQL that you can't do in RE itself, and how does that benefit your data integrity and maintenance?Vincent described the concept well, so let me give you an example.
We do a ton of reporting based on proposals. In export, RE only allows you to limit which proposals you're exporting based on purpose. So if you want to report on specific proposals, you have to run a constituent export and either finagle the purpose field to work for you, or export everything and filter in your reporting software. So the procedure basically goes: select the constituents that have proposal X, then export all those constituents' proposals, then select proposal X for each of the constituents and report on that. The exports can take a really long time because you need to pull lot of info out of the database, when you only want to report on some of it.
If you're writing it in SQL directly off the back end, you just say, give me all proposal Xs with their associated constituent information. It runs much much more quickly since you're directly querying for the data you want instead of working backwards into it, and it doesn't actually involve exporting data out of RE.
Writing queries directly from RE can also get you information you can't otherwise get to. An example: we have a constituent bio that we run. We were requested that, if the person's primary business was a corporation, and that corporation was a corporate member, to show the corporate membership level and giving. That's just not available in Export. So we had to set it up with two exports -- one with all the constituent info, and one with all the corporate info -- and join them up in Crystal. That means the end user can't run the bio, we have to run it for them. So, rather than doing that, you can pretty easily write the SQL to pull that information directly, and it's available to you.
That's just a start. If you know SQL and have power query installed in Excel, you can do relatively sophisticated financial reporting directly from the database (I'm working on converting our campaign reports to this model). If you have Tableau or some other BI/data visualization software, those generally can use SQL queries as a data source.
I'm sure there are a million other specific examples, but the point is, once you learn how databases work, and learn some SQL, your options for interacting with RE become much less limited than they are when you use only the front end.
9 -
Hi Vincent, according to knowledgebase article 75705 the Read Only Database Assistance (RODBA) plug-in, which let's you query the backend within RE, is not available when hosted. (When not hosted, I have heard that you should be able to query the backend tables directly through SQL Server, but have no idea if that is accurate or not. I always thought that you needed the RODBA plug-in to do that.) I believe Blackbaud's answer to SQL if you are hosted is to use the API instead, which I think they are still building and only have certain areas of data available so far.
Faith, as for SQL when it comes to your career I can say I learned the hard way that SQL would be good to know. After over 10 years as a RE "dba" I found myself unemployed and really unable to apply or find a job as a DBA since most of them required SQL. I was stuck searching for another RE position (which isn't such a bad thing but knowing SQL would have opened a lot more opportunities).
As for using SQL with RE I haven't really used it much other than to check some counts using the RODBA plug-in. One thing I happen to be working on now though is trying to export Ratings records. Once you know SQL you still need to figure out the table structure and links. It isn't that difficult but it takes some getting used to to figure out the help files and how tables link together. One example, if I remember correctly, was that I needed to pull phone numbers from the primary address and I think it required six to eight different tables just to pull that info with constituent IDs. Note that you cannot update records using SQL as it would void your maintenance contract. If you can query on the database using the Query module then certainly start there. There have been some other times where a certain field isn't available in Export and I had to use SQL.
With all that said, I would recommend learning MS Access first if you don't already know it. It's query tool is easy and you can view the SQL it creates, which is how I learned most of the SQL I know. Also, it wouldn't be a reply by me if I also didn't recommend learning Crystal Reports too. :)
Oh, also, there are some others on the Community that probably use SQL a lot more than I do so hopefully they will chime in with how they use it (John? James?).
Josh
7 -
See, just as I was posting that James was also replying and also mentioned Proposal/Rating info!0
-
Joshua Bekerman bCRE:
Oh, also, there are some others on the Community that probably use SQL a lot more than I do so hopefully they will chime in with how they use it (John? James?).
Josh
To answer a couple things you referenced: we have RODBA, but I'm pretty sure that even without it you can interact with the DB from SSMS (SQL Server Management Studio, if anyone's not familiar with the term). RODBA is just the plugin that allows you to write your queries from within RE and gives you that Ctrl-Q function to see the machine-generated SQL behind a query you build in the Query module.
If your DB administrator gives you access to the database itself, you can write your queries from pretty much anything that can interact with a database via ODBC -- SSMS, SSRS, Crystal, Tableau, Power Query, Access, whatever.
Your comment about getting the phone number also reminds me that RE has a ton of views and functions built into it that you can reference when you're querying the DB, saving you a lot of time and effort. There are functions to get preferred address blocks, build salutations, get the primary phone, etc. There are views that pull gift info by hard and soft credit and combinations thereof, that link constituents to primary addressees, that pull campaign and fund links, etc. So instead of writing all these subqueries into your queries, you can access the views that are already there.
Another example of its usefulness: there are no adjusted gift reports that really nicely present all the information from the original and adjusted gift, and show how things have changed. I wrote one in Crystal that pulls directly from RE using SQL, runs in just a few seconds and highlights the adjustment made. It separates the adjustments into ones that affect RE only and ones that actually affect the GL. It even (mostly) shows the GL lines that are credited and debited so Finance has a double-check (that part is still under construction).
5 -
Thank you three for your replies, especially James and Joshua! That really helps put it into perspective. I am somewhat familiar with using Access, but not as fluent as I would like to be (certainly not with the SQL statements) but that seems like it would be a good place to practice that won't alarm my friends in IT.
Based on your replies, I think I can make a good case for learning SQL after I am finished learning Crystal. Thanks much, very helpful! 2 -
Faith Murray:
Thank you three for your replies, especially James and Joshua! That really helps put it into perspective. I am somewhat familiar with using Access, but not as fluent as I would like to be (certainly not with the SQL statements) but that seems like it would be a good place to practice that won't alarm my friends in IT. Based on your replies, I think I can make a good case for learning SQL after I am finished learning Crystal. Thanks much, very helpful!FYI if you're going to learn SQL through Access, I really recommend the access SQL editor by field effect. It's the first thing that comes up when you google Access SQL editor, and it will make your life much less of a living hell than using Access's native editor. I have no affiliation with the dude who wrote it, I just know it's been a godsend for me.
3 -
Faith Murray:
Hello all you SQL-users out there! I'm looking for input on what the benefits are, to a database manager, for learning SQL (if any). I'm always looking to expand my career skills, and that seems like an obvious next step since many other types of database software types require SQL usage. However, right now our IT is the only one who uses SQL for RE, and if I expressed an interest I'm sure they would ask, "What for?"
So -- what can you do with SQL that you can't do in RE itself, and how does that benefit your data integrity and maintenance?Hi Faith,
Learning SQL is a great opportunity to expand your skills and provide a higher level of service to your end users as outlined by many previous posts. One of the biggests areas we use it for is our ability to provide fiscal year giving across multiple years. It is very easy to build that is SQL, vs tryiing to do that in RE. I would agree with using SSMS, as it provides many more capabilities, as you will have access to see all the functions that are provided. Generating the Constituent name is easy to get using the built in Function.
We have built our own ODS to provide greater reaporting to our users with Fiscal Years, Year to Date Comparisons, and provide several layers of Gift Reporting (Own Credit, Legal Credit and what we call Funds Raised which gives certain credit to the donor based on the gift type). This is all possible with SQL.
One benefit that I don't beleive I saw posted was that SQL is pretty universal. I had been in Oracle shops for the last 15 years, and once I came over to RE, I had to move over to SQL Server. The syntax between SQL Server and Oracle are pretty similar, but it will certainly open up a larger group of technical opportunities for you. If you learn SQL, then learning SSRS and other reporting tools will become more straighforward. We have branched out to using QlikSense for all our reporting and visualizations, and it would not have been possible without SQL.
Good luck!
David
2 -
Hi everyone - I'm constantly hectored by my son to learn SQL as a career enhancement, but I can't say it's high on my list. A couple years ago, I asked our consultant/trainer what he thought, and his response was that it might be useful for really intense users with giant databases, but that if your data is clean enough, there's hardly anything you can't get in some combination of View, Query, or Export. I've focused on cleaning up the messes of my predecessor and getting the data nice and shiny. Admittedly, our database isn't that large and I can find more "useful" data than anyone has ever requested! I vote for learning SQL as opportunity offers, but focusing on getting every penny's worth out of RE as it stands - and that's alot of pennies!
Gracie0 -
I'll confess that I often cheat and use the visual query building tool in Access to create a query and then copy/paste/adapt that into the required native SQL (even for non-MS SQL databases, like MySQL).Joshua Bekerman bCRE:
Hi Vincent, according to knowledgebase article 75705 the Read Only Database Assistance (RODBA) plug-in, which let's you query the backend within RE, is not available when hosted. (When not hosted, I have heard that you should be able to query the backend tables directly through SQL Server, but have no idea if that is accurate or not. I always thought that you needed the RODBA plug-in to do that.) I believe Blackbaud's answer to SQL if you are hosted is to use the API instead, which I think they are still building and only have certain areas of data available so far.
Faith, as for SQL when it comes to your career I can say I learned the hard way that SQL would be good to know. After over 10 years as a RE "dba" I found myself unemployed and really unable to apply or find a job as a DBA since most of them required SQL. I was stuck searching for another RE position (which isn't such a bad thing but knowing SQL would have opened a lot more opportunities).
As for using SQL with RE I haven't really used it much other than to check some counts using the RODBA plug-in. One thing I happen to be working on now though is trying to export Ratings records. Once you know SQL you still need to figure out the table structure and links. It isn't that difficult but it takes some getting used to to figure out the help files and how tables link together. One example, if I remember correctly, was that I needed to pull phone numbers from the primary address and I think it required six to eight different tables just to pull that info with constituent IDs. Note that you cannot update records using SQL as it would void your maintenance contract. If you can query on the database using the Query module then certainly start there. There have been some other times where a certain field isn't available in Export and I had to use SQL.
With all that said, I would recommend learning MS Access first if you don't already know it. It's query tool is easy and you can view the SQL it creates, which is how I learned most of the SQL I know. Also, it wouldn't be a reply by me if I also didn't recommend learning Crystal Reports too. :)
Oh, also, there are some others on the Community that probably use SQL a lot more than I do so hopefully they will chime in with how they use it (John? James?).
Josh3 -
James Andrews:
Joshua Bekerman bCRE:
Oh, also, there are some others on the Community that probably use SQL a lot more than I do so hopefully they will chime in with how they use it (John? James?).
Josh
To answer a couple things you referenced: we have RODBA, but I'm pretty sure that even without it you can interact with the DB from SSMS (SQL Server Management Studio, if anyone's not familiar with the term). RODBA is just the plugin that allows you to write your queries from within RE and gives you that Ctrl-Q function to see the machine-generated SQL behind a query you build in the Query module.
If your DB administrator gives you access to the database itself, you can write your queries from pretty much anything that can interact with a database via ODBC -- SSMS, SSRS, Crystal, Tableau, Power Query, Access, whatever.
Your comment about getting the phone number also reminds me that RE has a ton of views and functions built into it that you can reference when you're querying the DB, saving you a lot of time and effort. There are functions to get preferred address blocks, build salutations, get the primary phone, etc. There are views that pull gift info by hard and soft credit and combinations thereof, that link constituents to primary addressees, that pull campaign and fund links, etc. So instead of writing all these subqueries into your queries, you can access the views that are already there.
Another example of its usefulness: there are no adjusted gift reports that really nicely present all the information from the original and adjusted gift, and show how things have changed. I wrote one in Crystal that pulls directly from RE using SQL, runs in just a few seconds and highlights the adjustment made. It separates the adjustments into ones that affect RE only and ones that actually affect the GL. It even (mostly) shows the GL lines that are credited and debited so Finance has a double-check (that part is still under construction).The views are documented and easy to use, but I've never found any documentation on the SQL functions.
RODBA is great, but it's a pain to get installed.
2 -
James Andrews:
Faith Murray:
Thank you three for your replies, especially James and Joshua! That really helps put it into perspective. I am somewhat familiar with using Access, but not as fluent as I would like to be (certainly not with the SQL statements) but that seems like it would be a good place to practice that won't alarm my friends in IT. Based on your replies, I think I can make a good case for learning SQL after I am finished learning Crystal. Thanks much, very helpful!FYI if you're going to learn SQL through Access, I really recommend the access SQL editor by field effect. It's the first thing that comes up when you google Access SQL editor, and it will make your life much less of a living hell than using Access's native editor. I have no affiliation with the dude who wrote it, I just know it's been a godsend for me.Oooooh! I'd never seen that before. So beautiful and dirt cheap!! Thanks!!
0 -
John Heizer:
The views are documented and easy to use, but I've never found any documentation on the SQL functions.
RODBA is great, but it's a pain to get installed.
I only use a few of the functions, and I only figured out how because of RODBA. dbo.GetSalutation is the one I originally discovered, which I use to pull the primary addressee. You can right click in SSMS and choose "modify" and it'll show you the function, but I don't know enough about SQL programming to fully understand what's going on. But what I can understand is choosing the primary addressee in the output of a query, hitting Ctrl-Q, and seeing how it got there.2 -
John Heizer:
Oooooh! I'd never seen that before. So beautiful and dirt cheap!! Thanks!!
The dev is pretty responsive to bug reports, etc., too. It's much quicker than using Notepad++ and copying/pasting, which is how I'd always done it before.
The other thing to keep in mind about using Access to learn SQL is that Jet SQL is weird and old fashioned, and doesn't work the same as T-SQL or Oracle. It's not too hard to switch between them, but things just work differently.
The final bit of recommendation I have is to use Blackbaud -- you can search the knowledgebase for SQL stuff by starting your search with "write a SQL statement to..." There's not a ton of it in there, but there's a decent amount. I've also had some success with opening a case and just asking for code. A couple of times the code I've gotten back has been really strangely written, but it always at least gave me the answer I needed.
2 -
To answer a couple things you referenced: we have RODBA, but I'm pretty sure that even without it you can interact with the DB from SSMS (SQL Server Management Studio, if anyone's not familiar with the term). RODBA is just the plugin that allows you to write your queries from within RE and gives you that Ctrl-Q function to see the machine-generated SQL behind a query you build in the Query module.
If your DB administrator gives you access to the database itself, you can write your queries from pretty much anything that can interact with a database via ODBC -- SSMS, SSRS, Crystal, Tableau, Power Query, Access, whatever.
Your comment about getting the phone number also reminds me that RE has a ton of views and functions built into it that you can reference when you're querying the DB, saving you a lot of time and effort. There are functions to get preferred address blocks, build salutations, get the primary phone, etc. There are views that pull gift info by hard and soft credit and combinations thereof, that link constituents to primary addressees, that pull campaign and fund links, etc. So instead of writing all these subqueries into your queries, you can access the views that are already there.
Another example of its usefulness: there are no adjusted gift reports that really nicely present all the information from the original and adjusted gift, and show how things have changed. I wrote one in Crystal that pulls directly from RE using SQL, runs in just a few seconds and highlights the adjustment made. It separates the adjustments into ones that affect RE only and ones that actually affect the GL. It even (mostly) shows the GL lines that are credited and debited so Finance has a double-check (that part is still under construction).Using SSMS to create queries and views on the Raiser's Edge database is incredibly advantageous if you feel comfortable doing it. You can create and save your own custom views and functions and then use them in reports (Crystal Reports) to make reporting much faster and easier. Also, cleaning up the database and manipulating the data is a pretty cool capability that might not be available through Raiser's Edge. For example:
1. Moving gifts from one record to another.
2. Editing or removing rows in the 'Alias' table or the 'Gift Reminder History' table on pledges (which you cannot even query on in RE); etc.
3. Unposting gift adjustments (and normal posted gifts without having to use the plug-in); assigning batch #'s to newly entered gifts after deleting a gift entered through a batch.
4. Creating normal RE queries and then 'assigning' them to other users (so they show up when the 'Only show my queries' box is checked).
5. Deleting any type of record based on anycriteria: constituents, educations, addresses, relationships, ratings, etc.
Tip: run the data manipulation query on the sample database first to make sure you don't botch something.
2 -
Patrick Manning:
Using SSMS to create queries and views on the Raiser's Edge database is incredibly advantageous if you feel comfortable doing it. You can create and save your own custom views and functions and then use them in reports (Crystal Reports) to make reporting much faster and easier. Also, cleaning up the database and manipulating the data is a pretty cool capability that might not be available through Raiser's Edge. For example:
1. Moving gifts from one record to another.
2. Editing or removing rows in the 'Alias' table or the 'Gift Reminder History' table on pledges (which you cannot even query on in RE); etc.
3. Unposting gift adjustments (and normal posted gifts without having to use the plug-in); assigning batch #'s to newly entered gifts after deleting a gift entered through a batch.
4. Creating normal RE queries and then 'assigning' them to other users (so they show up when the 'Only show my queries' box is checked).
5. Deleting any type of record based on anycriteria: constituents, educations, addresses, relationships, ratings, etc.
Tip: run the data manipulation query on the sample database first to make sure you don't botch something.I'm pretty sure they say that actual database manipulation voids your support contract though, don't they? I mean I'd love to do stuff like this, but I'm paranoid about having any more than read only access to the DB in case I accidentally have a spasm and drop all the tables or something.
4 -
Patrick Manning:
To answer a couple things you referenced: we have RODBA, but I'm pretty sure that even without it you can interact with the DB from SSMS (SQL Server Management Studio, if anyone's not familiar with the term). RODBA is just the plugin that allows you to write your queries from within RE and gives you that Ctrl-Q function to see the machine-generated SQL behind a query you build in the Query module.
If your DB administrator gives you access to the database itself, you can write your queries from pretty much anything that can interact with a database via ODBC -- SSMS, SSRS, Crystal, Tableau, Power Query, Access, whatever.
Your comment about getting the phone number also reminds me that RE has a ton of views and functions built into it that you can reference when you're querying the DB, saving you a lot of time and effort. There are functions to get preferred address blocks, build salutations, get the primary phone, etc. There are views that pull gift info by hard and soft credit and combinations thereof, that link constituents to primary addressees, that pull campaign and fund links, etc. So instead of writing all these subqueries into your queries, you can access the views that are already there.
Another example of its usefulness: there are no adjusted gift reports that really nicely present all the information from the original and adjusted gift, and show how things have changed. I wrote one in Crystal that pulls directly from RE using SQL, runs in just a few seconds and highlights the adjustment made. It separates the adjustments into ones that affect RE only and ones that actually affect the GL. It even (mostly) shows the GL lines that are credited and debited so Finance has a double-check (that part is still under construction).Using SSMS to create queries and views on the Raiser's Edge database is incredibly advantageous if you feel comfortable doing it. You can create and save your own custom views and functions and then use them in reports (Crystal Reports) to make reporting much faster and easier. Also, cleaning up the database and manipulating the data is a pretty cool capability that might not be available through Raiser's Edge. For example:
1. Moving gifts from one record to another.
2. Editing or removing rows in the 'Alias' table or the 'Gift Reminder History' table on pledges (which you cannot even query on in RE); etc.
3. Unposting gift adjustments (and normal posted gifts without having to use the plug-in); assigning batch #'s to newly entered gifts after deleting a gift entered through a batch.
4. Creating normal RE queries and then 'assigning' them to other users (so they show up when the 'Only show my queries' box is checked).
5. Deleting any type of record based on anycriteria: constituents, educations, addresses, relationships, ratings, etc.
Tip: run the data manipulation query on the sample database first to make sure you don't botch something.YIKES!! I would never advise anyone to directly manipulate the SQL data. RODBA is Read Only for a good reason. Any manipulation of the data outside the API will void your support contract and you could be stuck with a big bill having Blackbaud go over your data with a fine-toothed comb before they'd agree to support you again.
1 -
YIKES!! I would never advise anyone to directly manipulate the SQL data. RODBA is Read Only for a good reason. Any manipulation of the data outside the API will void your support contract and you could be stuck with a big bill having Blackbaud go over your data with a fine-toothed comb before they'd agree to support you again.
The data belongs to you, the client. The database structure itself cannot be manipulated or altered and that would violate your contract. I spoke with our account representative a few years ago to make certain. There are also FOREIGN KEY constraints throughout that will prevent any DELETE statements from processing if you try to remove a row of data that is referenced by data in another table (I wouldn't try to drop a table or anything, that's for sure). Also, this is not a part of the RODBA add-on. This is using SSMS to connect to your RE database through the back-end and using those tools to manipulate your data.
0 -
Patrick Manning:
YIKES!! I would never advise anyone to directly manipulate the SQL data. RODBA is Read Only for a good reason. Any manipulation of the data outside the API will void your support contract and you could be stuck with a big bill having Blackbaud go over your data with a fine-toothed comb before they'd agree to support you again.
The data belongs to you, the client. The database structure itself cannot be manipulated or altered and that would violate your contract. I spoke with our account representative a few years ago to make certain. There are also FOREIGN KEY constraints throughout that will prevent any DELETE statements from processing if you try to remove a row of data that is referenced by data in another table (I wouldn't try to drop a table or anything, that's for sure). Also, this is not a part of the RODBA add-on. This is using SSMS to connect to your RE database through the back-end and using those tools to manipulate your data.That reminder history thing is golden. What a frustrating setup that is. I am very interested in some of this sort of very minor data manipulation if it's allowed under warranty. Probably not moving gifts around though -- are you integrated with FE?
0 -
James Andrews:
Patrick Manning:
The data belongs to you, the client. The database structure itself cannot be manipulated or altered and that would violate your contract. I spoke with our account representative a few years ago to make certain. There are also FOREIGN KEY constraints throughout that will prevent any DELETE statements from processing if you try to remove a row of data that is referenced by data in another table (I wouldn't try to drop a table or anything, that's for sure). Also, this is not a part of the RODBA add-on. This is using SSMS to connect to your RE database through the back-end and using those tools to manipulate your data.That reminder history thing is golden. What a frustrating setup that is. I am very interested in some of this sort of very minor data manipulation if it's allowed under warranty. Probably not moving gifts around though -- are you integrated with FE?Yes, we were integrated with FE. I'm happy to send you any of the SQL scripts that I used but definitely check with your account manager before doing anything.
0 -
Patrick Manning:
YIKES!! I would never advise anyone to directly manipulate the SQL data. RODBA is Read Only for a good reason. Any manipulation of the data outside the API will void your support contract and you could be stuck with a big bill having Blackbaud go over your data with a fine-toothed comb before they'd agree to support you again.
The data belongs to you, the client. The database structure itself cannot be manipulated or altered and that would violate your contract. I spoke with our account representative a few years ago to make certain. There are also FOREIGN KEY constraints throughout that will prevent any DELETE statements from processing if you try to remove a row of data that is referenced by data in another table (I wouldn't try to drop a table or anything, that's for sure). Also, this is not a part of the RODBA add-on. This is using SSMS to connect to your RE database through the back-end and using those tools to manipulate your data.Oh, I don't dispute that the data belongs to the client. But that doesn't mean that Blackbaud must support you under their standard maintenance contract if you do something to mess up your data with direct SQL commands. "You break it, you pay for it." Everything I've ever read says Blackbaud will only support API for any data manipulation. If a data change with the API screws something up, Blackbaud must fix it. If a direct SQL command screws something up Blackbaud won't fix it for free is what I've been told every time I've asked. The Knowledgebase has dozens of samples for SQL reads and none for writing data. Foreign Key constraints don't handle things like ensuring the the Spouse data gets updated in both places (Constituent Record and Relationship Record) when a Spouse is added or deleted. Without both updates you can wind up with a Spouse Relationship that doesn't show up on the Spouse button on Bio1. The API is the only way to ensure that all those little quirks are covered.
1 -
John Heizer:
Patrick Manning:
YIKES!! I would never advise anyone to directly manipulate the SQL data. RODBA is Read Only for a good reason. Any manipulation of the data outside the API will void your support contract and you could be stuck with a big bill having Blackbaud go over your data with a fine-toothed comb before they'd agree to support you again.
The data belongs to you, the client. The database structure itself cannot be manipulated or altered and that would violate your contract. I spoke with our account representative a few years ago to make certain. There are also FOREIGN KEY constraints throughout that will prevent any DELETE statements from processing if you try to remove a row of data that is referenced by data in another table (I wouldn't try to drop a table or anything, that's for sure). Also, this is not a part of the RODBA add-on. This is using SSMS to connect to your RE database through the back-end and using those tools to manipulate your data.Oh, I don't dispute that the data belongs to the client. But that doesn't mean that Blackbaud must support you under their standard maintenance contract if you do something to mess up your data with direct SQL commands. "You break it, you pay for it." Everything I've ever read says Blackbaud will only support API for any data manipulation. If a data change with the API screws something up, Blackbaud must fix it. If a direct SQL command screws something up Blackbaud won't fix it for free is what I've been told every time I've asked. The Knowledgebase has dozens of samples for SQL reads and none for writing data. Foreign Key constraints don't handle things like ensuring the the Spouse data gets updated in both places (Constituent Record and Relationship Record) when a Spouse is added or deleted. Without both updates you can wind up with a Spouse Relationship that doesn't show up on the Spouse button on Bio1. The API is the only way to ensure that all those little quirks are covered.This is good to know... I've only been interested in getting data from the database via SQL but I can see how a user manipulating the data structure could potentially crush their database with the wrong command. I need to learn more about the SQL server management studio.
I think SQL opens more doors to the user, I'm hoping to get it functioning here soon.
0 -
As a parting remark Faith, I'm going to make a suggestion of what you might want to do with SQL, that I think I may pursue myself.Faith Murray:
Hello all you SQL-users out there! I'm looking for input on what the benefits are, to a database manager, for learning SQL (if any). I'm always looking to expand my career skills, and that seems like an obvious next step since many other types of database software types require SQL usage. However, right now our IT is the only one who uses SQL for RE, and if I expressed an interest I'm sure they would ask, "What for?"
So -- what can you do with SQL that you can't do in RE itself, and how does that benefit your data integrity and maintenance?
If you are interested in SQL but don't want to get into the complications of RE being propriety software and the complications of not knowing the names of tables etc (for instance I've read that a constiuent name like 'Vincent M' might be represented as '0000153gf64G9437' so you would need to write something like "SELECT 0000153gf64G9437 FROM 7777395g98e37 WHERE gifts > $500" in order to get a constituents gifts from a code gropu that are greater than $500). I'm just imagining it would not be user friendly), it gets complicated and also it becomes a fiscal risk if you do something wrong. You can utilize a datawarehouse concept where you create a copy of your data into a seperate database and operate on SQL there.
That would be the smartest thing to do, in my opinion.0 -
Thanks everybody! This has been very helpful.0
-
John Heizer:
Patrick Manning:
To answer a couple things you referenced: we have RODBA, but I'm pretty sure that even without it you can interact with the DB from SSMS (SQL Server Management Studio, if anyone's not familiar with the term). RODBA is just the plugin that allows you to write your queries from within RE and gives you that Ctrl-Q function to see the machine-generated SQL behind a query you build in the Query module.
If your DB administrator gives you access to the database itself, you can write your queries from pretty much anything that can interact with a database via ODBC -- SSMS, SSRS, Crystal, Tableau, Power Query, Access, whatever.
Your comment about getting the phone number also reminds me that RE has a ton of views and functions built into it that you can reference when you're querying the DB, saving you a lot of time and effort. There are functions to get preferred address blocks, build salutations, get the primary phone, etc. There are views that pull gift info by hard and soft credit and combinations thereof, that link constituents to primary addressees, that pull campaign and fund links, etc. So instead of writing all these subqueries into your queries, you can access the views that are already there.
Another example of its usefulness: there are no adjusted gift reports that really nicely present all the information from the original and adjusted gift, and show how things have changed. I wrote one in Crystal that pulls directly from RE using SQL, runs in just a few seconds and highlights the adjustment made. It separates the adjustments into ones that affect RE only and ones that actually affect the GL. It even (mostly) shows the GL lines that are credited and debited so Finance has a double-check (that part is still under construction).Using SSMS to create queries and views on the Raiser's Edge database is incredibly advantageous if you feel comfortable doing it. You can create and save your own custom views and functions and then use them in reports (Crystal Reports) to make reporting much faster and easier. Also, cleaning up the database and manipulating the data is a pretty cool capability that might not be available through Raiser's Edge. For example:
1. Moving gifts from one record to another.
2. Editing or removing rows in the 'Alias' table or the 'Gift Reminder History' table on pledges (which you cannot even query on in RE); etc.
3. Unposting gift adjustments (and normal posted gifts without having to use the plug-in); assigning batch #'s to newly entered gifts after deleting a gift entered through a batch.
4. Creating normal RE queries and then 'assigning' them to other users (so they show up when the 'Only show my queries' box is checked).
5. Deleting any type of record based on anycriteria: constituents, educations, addresses, relationships, ratings, etc.
Tip: run the data manipulation query on the sample database first to make sure you don't botch something.YIKES!! I would never advise anyone to directly manipulate the SQL data. RODBA is Read Only for a good reason. Any manipulation of the data outside the API will void your support contract and you could be stuck with a big bill having Blackbaud go over your data with a fine-toothed comb before they'd agree to support you again.I second that! We always create a secondary database - usually called "Admin" - and stick all our views, report queries, test junk, etc in there. It's clean, keeps your BB DB pristine and there's zero risk of you accidentally dropping a table (or the whole DB!).
If you go down this route, be sure to get your collations matched. It can cause all sorts of headaches later if they're not the same. And if you really want to be thorough, you can put the Admin DB on a completely separate SQL server instance and use Linked Servers to marry them up.
Oh, and one neat thing about RODBA: If you go View/SQL in a query and simply copy and paste the SQL into a new SQL Server Management Studio query and run it, there's a really good chance that it will run and give you a great, simple base for emaulating that complicated RE query that takes forever to run. Give it a try. (Don't forget to look at the additional tabs which contain sub-queries. Sometimes these need "unwinding" in your SSMS query code.)
1
Categories
- All Categories
- Shannon parent
- shannon 2
- shannon 1
- 21 Advocacy DC Users Group
- 14 BBCRM PAG Discussions
- 89 High Education Program Advisory Group (HE PAG)
- 28 Luminate CRM DC Users Group
- 8 DC Luminate CRM Users Group
- Luminate PAG
- 5.9K Blackbaud Altru®
- 58 Blackbaud Award Management™ and Blackbaud Stewardship Management™
- 409 bbcon®
- 2.1K Blackbaud CRM™ and Blackbaud Internet Solutions™
- donorCentrics®
- 1.1K Blackbaud eTapestry®
- 2.8K Blackbaud Financial Edge NXT®
- 1.1K Blackbaud Grantmaking™
- 527 Education Management Solutions for Higher Education
- 1 JustGiving® from Blackbaud®
- 4.6K Education Management Solutions for K-12 Schools
- Blackbaud Luminate Online & Blackbaud TeamRaiser
- 16.4K Blackbaud Raiser's Edge NXT®
- 4.1K SKY Developer
- 547 ResearchPoint™
- 151 Blackbaud Tuition Management™
- 1 YourCause® from Blackbaud®
- 61 everydayhero
- 3 Campaign Ideas
- 58 General Discussion
- 115 Blackbaud ID
- 87 K-12 Blackbaud ID
- 6 Admin Console
- 949 Organizational Best Practices
- 353 The Tap (Just for Fun)
- 235 Blackbaud Community Feedback Forum
- 55 Admissions Event Management EAP
- 18 MobilePay Terminal + BBID Canada EAP
- 36 EAP for New Email Campaigns Experience in Blackbaud Luminate Online®
- 109 EAP for 360 Student Profile in Blackbaud Student Information System
- 41 EAP for Assessment Builder in Blackbaud Learning Management System™
- 9 Technical Preview for SKY API for Blackbaud CRM™ and Blackbaud Altru®
- 55 Community Advisory Group
- 46 Blackbaud Community Ideas
- 26 Blackbaud Community Challenges
- 7 Security Testing Forum
- 1.1K ARCHIVED FORUMS | Inactive and/or Completed EAPs
- 3 Blackbaud Staff Discussions
- 7.7K ARCHIVED FORUM CATEGORY [ID 304]
- 1 Blackbaud Partners Discussions
- 1 Blackbaud Giving Search™
- 35 EAP Student Assignment Details and Assignment Center
- 39 EAP Core - Roles and Tasks
- 59 Blackbaud Community All-Stars Discussions
- 20 Blackbaud Raiser's Edge NXT® Online Giving EAP
- Diocesan Blackbaud Raiser’s Edge NXT® User’s Group
- 2 Blackbaud Consultant’s Community
- 43 End of Term Grade Entry EAP
- 92 EAP for Query in Blackbaud Raiser's Edge NXT®
- 38 Standard Reports for Blackbaud Raiser's Edge NXT® EAP
- 12 Payments Assistant for Blackbaud Financial Edge NXT® EAP
- 6 Ask an All Star (Austen Brown)
- 8 Ask an All-Star Alex Wong (Blackbaud Raiser's Edge NXT®)
- 1 Ask an All-Star Alex Wong (Blackbaud Financial Edge NXT®)
- 6 Ask an All-Star (Christine Robertson)
- 21 Ask an Expert (Anthony Gallo)
- Blackbaud Francophone Group
- 22 Ask an Expert (David Springer)
- 4 Raiser's Edge NXT PowerUp Challenge #1 (Query)
- 6 Ask an All-Star Sunshine Reinken Watson and Carlene Johnson
- 4 Raiser's Edge NXT PowerUp Challenge: Events
- 14 Ask an All-Star (Elizabeth Johnson)
- 7 Ask an Expert (Stephen Churchill)
- 2025 ARCHIVED FORUM POSTS
- 322 ARCHIVED | Financial Edge® Tips and Tricks
- 164 ARCHIVED | Raiser's Edge® Blog
- 300 ARCHIVED | Raiser's Edge® Blog
- 441 ARCHIVED | Blackbaud Altru® Tips and Tricks
- 66 ARCHIVED | Blackbaud NetCommunity™ Blog
- 211 ARCHIVED | Blackbaud Target Analytics® Tips and Tricks
- 47 Blackbaud CRM Higher Ed Product Advisory Group (HE PAG)
- Luminate CRM DC Users Group
- 225 ARCHIVED | Blackbaud eTapestry® Tips and Tricks
- 1 Blackbaud eTapestry® Know How Blog
- 19 Blackbaud CRM Product Advisory Group (BBCRM PAG)
- 1 Blackbaud K-12 Education Solutions™ Blog
- 280 ARCHIVED | Mixed Community Announcements
- 3 ARCHIVED | Blackbaud Corporations™ & Blackbaud Foundations™ Hosting Status
- 1 npEngage
- 24 ARCHIVED | K-12 Announcements
- 15 ARCHIVED | FIMS Host*Net Hosting Status
- 23 ARCHIVED | Blackbaud Outcomes & Online Applications (IGAM) Hosting Status
- 22 ARCHIVED | Blackbaud DonorCentral Hosting Status
- 14 ARCHIVED | Blackbaud Grantmaking™ UK Hosting Status
- 117 ARCHIVED | Blackbaud CRM™ and Blackbaud Internet Solutions™ Announcements
- 50 Blackbaud NetCommunity™ Blog
- 169 ARCHIVED | Blackbaud Grantmaking™ Tips and Tricks
- Advocacy DC Users Group
- 718 Community News
- Blackbaud Altru® Hosting Status
- 104 ARCHIVED | Member Spotlight
- 145 ARCHIVED | Hosting Blog
- 149 JustGiving® from Blackbaud® Blog
- 97 ARCHIVED | bbcon® Blogs
- 19 ARCHIVED | Blackbaud Luminate CRM™ Announcements
- 161 Luminate Advocacy News
- 187 Organizational Best Practices Blog
- 67 everydayhero Blog
- 52 Blackbaud SKY® Reporting Announcements
- 17 ARCHIVED | Blackbaud SKY® Reporting for K-12 Announcements
- 3 Luminate Online Product Advisory Group (LO PAG)
- 81 ARCHIVED | JustGiving® from Blackbaud® Tips and Tricks
- 1 ARCHIVED | K-12 Conference Blog
- Blackbaud Church Management™ Announcements
- ARCHIVED | Blackbaud Award Management™ and Blackbaud Stewardship Management™ Announcements
- 1 Blackbaud Peer-to-Peer Fundraising™, Powered by JustGiving® Blogs
- 39 Tips, Tricks, and Timesavers!
- 56 Blackbaud Church Management™ Resources
- 154 Blackbaud Church Management™ Announcements
- 1 ARCHIVED | Blackbaud Church Management™ Tips and Tricks
- 11 ARCHIVED | Blackbaud Higher Education Solutions™ Announcements
- 7 ARCHIVED | Blackbaud Guided Fundraising™ Blog
- 2 Blackbaud Fundraiser Performance Management™ Blog
- 9 Foundations Events and Content
- 14 ARCHIVED | Blog Posts
- 2 ARCHIVED | Blackbaud FIMS™ Announcement and Tips
- 59 Blackbaud Partner Announcements
- 10 ARCHIVED | Blackbaud Impact Edge™ EAP Blogs
- 1 Community Help Blogs
- Diocesan Blackbaud Raiser’s Edge NXT® Users' Group
- Blackbaud Consultant’s Community
- Blackbaud Francophone Group
- 1 BLOG ARCHIVE CATEGORY
- Blackbaud Community™ Discussions
- 8.3K Blackbaud Luminate Online® & Blackbaud TeamRaiser® Discussions
- 5.7K Jobs Board