Nonprofits and Data

last person joined: 4 days ago 

This group is for those interested in learning and sharing about all things data-related for nonprofits. The Nonprofits and Data group is for people using data to serve a mission, either directly or by improving nonprofits and the nonprofit sector. That includes everything from collecting data and managing databases to analytics, data visualization and data mining. Here are some examples of topics we discuss: using data to improve organizational effectiveness, measuring impact, using data for storytelling, tools for data management and analysis, figuring out the “right” data to collect, and learning skills to help us use data better.

Salesforce extracting data and creating complex queries

  • 1.  Salesforce extracting data and creating complex queries

    Posted Mar 25, 2019 12:17
    Hi all!

    We're moving our large case management system over to Salesforce from one that was custom made a couple of decades ago.  Our research team is new to Salesforce and is understandably concerned about their ability to export and manipulate data.  We've been told that Salesforce is limited if you're doing many joins across a lot of tables.

    I am sure that this is fairly easy to do.  I also keep mentioning Tableau, but they think Tableau is just for dashboards (maybe that's true?  I have limited experience).  And at the end of the day they'd prefer being able to use SPSS or R to manipulate data themselves.

    Does anyone have a similar experience or use-case and may be willing to talk to our team for a few minutes sometime about what you do?

    Shubha Bala
    Center for Court Innovation
    2020 Nonprofit Technology Conference Logo  w/ Baltimore Skyline

  • 2.  RE: Salesforce extracting data and creating complex queries

    Posted Mar 25, 2019 12:55
    Hi Shubha -

    You can indeed use R and R Studio to connect to the backend. Our eval folks do. I believe SPSS works as well - you just have to go via the OAuth route w/the proper perms and append your security token to your password on login.

    Dar Veverka
    Director of Information Technology
    Urban Teachers
    Baltimore, MD

    2020 Nonprofit Technology Conference Logo  w/ Baltimore Skyline

  • 3.  RE: Salesforce extracting data and creating complex queries

    Posted Mar 26, 2019 10:02
    Shubha, cross-object reporting is challenging to do in SF without any other tools. Apsona is a commonly-used app that provides tools for this kind of reporting. As Dar said, there's no problem connecting R or SPSS to Salesforce to manipulate the data, the issue you may still have is that the team that uses R for its reporting may not be the only team that needs to do cross-object reporting. Other teams that need that feature may not have the skills to use R/SPSS for the reports that they need. You'll want to be sure to develop an approach to reporting that ensures that every data consumer can be served.

    Isaac Shalev
    Stamford CT

    2020 Nonprofit Technology Conference Logo  w/ Baltimore Skyline

  • 4.  RE: Salesforce extracting data and creating complex queries

    Posted Mar 26, 2019 12:05
    Second on Issac's recommendation that you check out Apsona's multi-step reporting product.

    Our org has been using Salesforce for about a year-and-a-half, and the reporting paradigm does take some getting used to. Salesforce native reporting is slick, can be pinned to dashboards, and can be shared with any user in your org. The key limitation with standard reporting is that it's limited to joining up to three tables, and the tables available in your report are defined by the report type you select when creating the report. If you get halfway through writing a report and realize that you need data from another table, you have to start from scratch.

    Apsona's reporting product is spartan, has it's own limitations and quirks, but the key strength is that it lets you add table joins on the fly as you write the report. And I don't think they have any limitation on how many tables can be joined. My understanding of how their product works is that they execute a series of single table queries, then join the data together in the Apsona interface based on the field mappings you specify.

    Regarding the ability to manipulate data in batches, this is an aspect where Salesforce really shines. As long as you include an object's ID number in a report, data can be manipulated using whatever software you like and then pushed back into the system via Data Loader. As my org's Salesforce admin, I do far more data manipulation using Excel (plus Ablebits Suite) than via the native SF interface.

    The other area where SF shines is its ability to connect with so many other platforms. If you don't like Excel and prefer to use Google Sheets instead, there's a data connector available that allows you to pull, manipulate, and push data back all from inside a Google Sheet. There are connectors available if your eval folks want to bring data into SPSS or R Studio. There's a connector available for Tableau. And there are many, many more like these in the ecosystem around this platform.

    Jason Samuels
    IT Operations Manager
    American Craft Council
    Minneapolis, MN

    2020 Nonprofit Technology Conference Logo  w/ Baltimore Skyline

  • 5.  RE: Salesforce extracting data and creating complex queries

    Posted Mar 26, 2019 14:42
    Edited by Medha Nanal Mar 26, 2019 14:42
    Hi Shubha,

    I agree with what Isaac and Jason commented - regarding Salesforce's join ability and also using tools like Apsona etc. to augment reporting ability.

    I want to focus on another possible aspect here. Without knowing your current database schema/structures, it is quite  likely that when your team migrates their data to Salesforce, they will not be using the same schema/structures to store their cases as in current database. In fact, while this approach is possible, it's not desirable. Salesforce has quite a well defined framework to manage case information, and your current structures should be mapped to Salesforce for successful utilization of all Salesforce features.

    Once this is done correctly, you might find that the canned reports available on these cases are sufficient, and there's no need to do the same joins in Salesforce, because of a changed schema. If not, then tools like Apsona or R&R Studio, can be explored. (By the way, I am a fan of Apsona and I would highly recommend it!) The point here is that, if your schema is not mapped correctly on existing framework, then even advanced tools may be of little help.

    As a side-note, what the team thinks about using Tableau is correct -- it's primarily a visualization tool.

    Hope this helps.

    Medha Nanal
    Strategic Data/Database Consultant for Nonprofits (Fundraising, Operations, Programs)

    2020 Nonprofit Technology Conference Logo  w/ Baltimore Skyline