Query APIs - SingleRow

Ken Swift

Hi everyone - I have been playing around with the preview release of Query SingleRow and it seems to work, but with several issues that don't make it a replacement for Export yet. My thoughts….

1. There is still a huge discrepancy between the fields available in Export vs Query. For example, our Aqueduct Analytics product still uses Export to get installment/payment/write-off information, since there is no other way to get at this data efficiently through the Sky APIs. We instead use Export+Queue provide a comprehensive list of all the IDs and amounts so that we can accurately account for pledge balances, etc.
2. We have used the Blackbaud Report Write Database export format for several exports since it has the undocumented feature of selecting "0" for the "number of X to export", which allows us to get the data in a tables (MDB) format rather a single row. SingleRow json, might support this, but it looks like you have to specify a number from 1-99 for one-to-many relationships. I understand the requirement in a CSV result, but for JSON I think it could be more dynamic.
3. The "Copy query JSON" button is very handy when creating MultiRow requests, but it very tedious to look up the nodes and construct the SingleRow definitions with all the child selects.
4. My private sandbox environment doesn't have a lot of data, so I can't comment on any performance metrics yet.

We have dramatically reduced our reliance on Export, but there are still several things holding us back from fully retiring it.

Happy to discuss and share my experiences with anyone that would like to chat!

Ken

ken@netoutlook.com

Erik Leaver

@David Springer & @Todd Yellin

Carol Grant

@Ken Swift - Hi Ken- I'm going to assume you are in the Early Adopter for the Single Row query. David Springer has provided a feedback sheet for the early adopters where they can keep track of issues like this.

I can relate to your experiences, they are still working on adding fields that aren't there and I have a wish list also like Relationship addresses, etc. The JSON is complicated, I had a hard time with it also knowing the nodes, etc.

David Springer

Hi Ken, thanks for testing and calling these out! Responses below:

1. We're adding in Export-specific fields and filters in order of usage. Fun fact, the write-off node is used by about 23% of environments! So not in place yet, but definitely planned.
2. MDB/ACCDB is planned as well, a bit further out. It's also fairly likely that we'll expand JSON to go outside of that 99-record limit, but our first goal is meeting the needs of Export. Then we'll look at expansions beyond that.
3. Yes, Copy query JSON is a life-saver when using the API. I expect when we've rolled out the UI to more users, the API will be easier because of that as well as visually comparing with query trees in the UI.
4. Please let us know when you start to get a sense of performance! I'd expect better performance than database view Export, but I'd love to hear folks' experience with real world data.

Ken Swift

@David Springer is great to know that a lot of work is still being incorporated into Query to support Export. I will keep my fingers and toes crossed!

Alex Wong

@Ken Swift for pledge/installment/payment (including write off), we use a combination of Gift v2 API, Query API, and regular Gift v1 list.

• Gift v1 - Gift List
  • get all gift details, but does not pull back any write off gift, which is highly important for completing pledge related report
• Query API
  • gift query filtering on write off gift type only, add this on top of the gift list to get the "full gift table"
• Gift v2 - get installment and get payment
  • this is unfortunately per pledge GET operation
  • initial load of all installment and payment took many hours, so a little concurrency helps us get over >50K pledge installment and payment (not payment gift detail, but rather the linkage data of which pledge and which installment which payment gift is applied to for how much)

all data for us goes into azure sql server database as data warehouse, to keep this up to date every 4 hours requires a little creativeness handling gift incremental change to the pledge or any payment record and re-do the gift v2 on pledge affected.

this avoided the problem with Export (Queue) where data is "columnized" and limited to 99.