Canopy Labs stored procedures

Steve,

If you're open to sharing we'd love to see your Canopy Labs SP's (Patron, ticket and performance).  I understand everyone's business rules are there same, but Canopy and our Marketing Department have no idea what specifically they are going to be receiving, because they have no idea what to tell us to send.

I found the Canopy "documentation" to be almost worthless.  It provided identification of the data elements they expect to have returned but nothing regarding the logic of how we would retrieve the data.  During their initial tech call it became clear that IT was going to have to figure this out.   Not reinventing the wheel would be a great thing. 

I'd be happy to share what we end up with as well.  Their "Customer Experience" managers have been completely unwilling to commit to obtaining any documentation or work examples from other organizations to share.  They seemed to be unfamiliar with the way the Network functions and our willingness to share work with one another. 

I would also be very interested in any logic that any of their Tessitura clients might have developed to measure success directly tied to their work. 

Thanks! 

Dan

Steve,

If you're open to sharing we'd love to see your Canopy Labs SP's (Patron, ticket and performance).  I understand everyone's business rules are there same, but Canopy and our Marketing Department have no idea what specifically they are going to be receiving, because they have no idea what to tell us to send.

I found the Canopy "documentation" to be almost worthless.  It provided identification of the data elements they expect to have returned but nothing regarding the logic of how we would retrieve the data.  During their initial tech call it became clear that IT was going to have to figure this out.   Not reinventing the wheel would be a great thing. 

I'd be happy to share what we end up with as well.  Their "Customer Experience" managers have been completely unwilling to commit to obtaining any documentation or work examples from other organizations to share.  They seemed to be unfamiliar with the way the Network functions and our willingness to share work with one another. 

I would also be very interested in any logic that any of their Tessitura clients might have developed to measure success directly tied to their work. 

Thanks! 

Dan

Hi Dan.

I'll not upload then directly here, but will zip them and email you and Jason. A little additional explanation might also be in order:

• LP_ROH_CL_Performances
  This used to list ALL of our Performances (28k +) but Canopy Labs ended requesting a smaller dataset. After internal discussion, we decided to return all Performances with the past 5 complete Seasons plus those between the start of this Season and the day on which the SP is invoked (now around 12.5 k)
  The URL information is only stored in our RDS database that the website uses when constucting links, hence the JOINs to RDS_LIVE
• LP_ROH_CL_Patrons
  This returns details for all Patrons who are out of our "test" account range, whose account is "Active" and whose last_update_dt in any of the T_CUSTOMER, LT_PRIVACY_ACT or TX_CUST_CONTACT_PERM_TYPE tables is within the supplied parameters. We are currently on Tessitura v 14.0.2 and are poised to move from LT_PRIVACY_ACT to using TX_CUST_CONTACT_PERM_TYPE (our cutoff date is 25/05/2018) but I had to build in a reliance on both tables until that date. I think my plan was to remove the LT_PRIVACY_ACT references once that date has passed.
• LP_ROH_CL_Tickets
  This returns all data for an order, including returns and refunds (VTO & VOO) as discussed with Canopy Labs, where either the T_ORDER.order_dt or .last_update_dt is within the supplied parameters. There are some ROH -specific filters in this SP (e.g. "Ghost" Seasons - don't ask!). Again, the Customer ID must be out of our "test" account range and be "Active". The decision to use those two date fields was taken because on a busy on-sale we operate a "drip-feed" queue where a Customer's purchased seats are effectively marked as sold in the database, but the orders are processed into the database at a rate of  20 per minute until demand has died down at which point we can gradually introduce more "worker threads" to process orders more rapidly until the queue is empty (this functionality was developed in partnership with POP) so that we can minimise deadlocking. This means that T_ORDER.create_dt can be up to 45 minutes adrift of .order_dt

If this makes no sense, then please contact me directly and I'll try and clear-up anything that isn't clear.

The SPs are being invoked via a REST layer we exposed to Canopy Lab's supplied IPs. This REST layer is looking at our STAGING environment (which is a restore of yesterday's backup) as it wouldn't impact our Production system, and up-to-the-minute information was not required. I enforced a 30-day ceiling to the number of days able to be processed by LP_ROH_CL_Tickets to limit the amount of data traffic as even 30 days returned over 2.5 million rows of JSON.

I started work on this project at the very end of October, and I think we got the green light that all was good to go around the middle of January. There was a lot of back-and-forth between myself and Canopy Labs which wasn't helped by the time-difference...

Let me know if there's any other info I can supply.

Steve