Report Writing - Who does it?

Kjersten,

Over the past 10+ years  BAM has created over 150 custom reports.  IT has created all of the reports.  However a “Business Owner” (from the general staff) is responsible for testing it, loving it, and the use of the report.  This can be a challenge as staff turnover occurs.  Documentation is incomplete.  Enhancements and new thinking is often spotty.

Over past 3-5 years we have come to realize that custom reports slow our ability to upgrade, and increase the expense of upgrading in time (both to Test and Fix) local custom reports.

As we moved into Tessitura Next Generation Technology with the Tessitura Network (V11 and on) we made a conscience decision to try to cut the number of custom reports in our system.  We are now down to ~100.  (Still a lot of custom reports.)  And we continue to work on reducing this overhead where it makes sense.

Second, over the last year or two we have begun to understand that these custom reports do not provide a consistent “One Truth”.  For example in some cases:

·         Individual reports, filter some data to not show it at all in the report.  This can be confusing if you have chosen parameter that say you should get all of the data.

·         Or some reports group data in different ways and report them using similar terminology as other reports.  (A classic is Fiscal Year Groupings of Contributions.)

·         Or Considering different elements in the system in different ways from report to report.  (Considering for example some holds as sold merchandise.  Where most reports consider Holds as unsold available merchandise.)

This makes this issue of having “one Truth” coming out of the system very difficult, and leads to folks “not trusting” the data.  The data by in large is fine.  However, because it is represented in so many different ways from one report to the next.  People have a hard time reconciling the difference one report to the next.

As the Tessitura Network reporting has become more mature, we now believe in keep new transactional custom reporting to a bare minimum.  Because the “standard” reports in general tell “One Truth”.   (Or at least just a few truths.)

Therefore, As we get requests for new types of reporting, We first look to adapt our current business practices to the existing (ever maturing) Tessitura System.  This provided a benefit in lower initial expense in creating reports.  Lower ongoing maintenance. And typically provides a more consistent “One Truth” from the data.

That said we have requests for unique (one time or infrequent) data mash-ups.  In general we want to empower BAM users to do these mash-ups themselves.  In these cases we first look to the existing Business Intelligence offerings from the Tessitura Network.  T-Stats, RMA, Dashboards.  Unfortunately,   many (maybe most) of the requests cannot be fully answered from this data.  Either because the data entities presented are not the ones to answer the question.  (For example you can’t get things like Arrival Time on Tickets in T-Stats.  You can’t tell the membership level at the time of purchase.  Constituency Codes at time of purchase.  Address at time of donation…, The name of the Credited Customer on a donation, not the name of the donor…, Sales volumes in comparison to budget, rather than capacity. Payment Schedules are not treated as Transactions they are off in their own special place in the system and rarely show up in reports.)

So, Then when we get a report for custom data.  We go through a process to answer the question in a consistent way with as little customization to our system as necessary. Because customization is very expensive over the long run. (Unless we believe that a certain type of analysis is going to become ubiquities. And then we usually start with the Ad-hock methods below before producing a custom reporting.)

·         First we will look to extracting transactional level data from existing transactional reports or the T-Stats data repository to do a mash-up.  This will often fail to yield results that are useful.  We have a variety of methods to do this.  From saving local files to live OLEDB connections to the T-Stats Cube Repository.

·         Then we will look to making minor additions to List Criteria (if special types of selection of customers is a challenge) Or output sets if special types of data elements is a challenge.  Again bringing this data into MS Excel not for long term storage.  But for analysis and presentation.  Those approaches have often failed as well.

·         Then only with some realistic business justification we will work with IT Programing staff to construct a custom views of data that are either made available as transactional custom reports for further mash-up or via direct OLEDB connections.  (In this way we were able to have graphic representations of daily Sales Trend lines, several years before the RMA delivered such functionality, and relate this to budgeted numbers. As well as a number of other innovations.)

·         Most recently we are starting to be able to include live 3^rd party data using new tools like PowerQuery and PowerPivot from Microsoft.  (During conference this year, I’ll spend some time talking about these new methods.   This is new for us and a rapidly developing approach.  That can allow things like 3^rd party mash-ups with things like census data or other data available from 3^rd parties.)

I’d be glad to discuss this with you further if you are interested.

--Tom

…

718.724.8135

tbrown@BAM.org

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Chris Jensen
Sent: Tuesday, June 17, 2014 10:55 AM
To: Thomas Brown
Subject: Re: [Tessitura Technical Forum] Report Writing - Who does it?

Kjersten Schladetzky:

I'm looking for some ideas on how you handle custom report writing in your organization as well as what you think are the pros and cons of your chosen model.  So, for example, does 1 person in IT handle all custom reporting needs or do you have a power user in each department that can write reports, but use IT to vet them and move them into the Production system?  Or something else entirely?  

Hi, Kjersten--

One report writer here (me), in IT. No-one outside IT has access to SQL or report writing tools. In some cases it might be nice to have one more, though being a single conduit for report requests definitely cuts down on needless duplication.

Kjersten,

Over the past 10+ years  BAM has created over 150 custom reports.  IT has created all of the reports.  However a “Business Owner” (from the general staff) is responsible for testing it, loving it, and the use of the report.  This can be a challenge as staff turnover occurs.  Documentation is incomplete.  Enhancements and new thinking is often spotty.

Over past 3-5 years we have come to realize that custom reports slow our ability to upgrade, and increase the expense of upgrading in time (both to Test and Fix) local custom reports.

As we moved into Tessitura Next Generation Technology with the Tessitura Network (V11 and on) we made a conscience decision to try to cut the number of custom reports in our system.  We are now down to ~100.  (Still a lot of custom reports.)  And we continue to work on reducing this overhead where it makes sense.

Second, over the last year or two we have begun to understand that these custom reports do not provide a consistent “One Truth”.  For example in some cases:

·         Individual reports, filter some data to not show it at all in the report.  This can be confusing if you have chosen parameter that say you should get all of the data.

·         Or some reports group data in different ways and report them using similar terminology as other reports.  (A classic is Fiscal Year Groupings of Contributions.)

·         Or Considering different elements in the system in different ways from report to report.  (Considering for example some holds as sold merchandise.  Where most reports consider Holds as unsold available merchandise.)

This makes this issue of having “one Truth” coming out of the system very difficult, and leads to folks “not trusting” the data.  The data by in large is fine.  However, because it is represented in so many different ways from one report to the next.  People have a hard time reconciling the difference one report to the next.

As the Tessitura Network reporting has become more mature, we now believe in keep new transactional custom reporting to a bare minimum.  Because the “standard” reports in general tell “One Truth”.   (Or at least just a few truths.)

Therefore, As we get requests for new types of reporting, We first look to adapt our current business practices to the existing (ever maturing) Tessitura System.  This provided a benefit in lower initial expense in creating reports.  Lower ongoing maintenance. And typically provides a more consistent “One Truth” from the data.

That said we have requests for unique (one time or infrequent) data mash-ups.  In general we want to empower BAM users to do these mash-ups themselves.  In these cases we first look to the existing Business Intelligence offerings from the Tessitura Network.  T-Stats, RMA, Dashboards.  Unfortunately,   many (maybe most) of the requests cannot be fully answered from this data.  Either because the data entities presented are not the ones to answer the question.  (For example you can’t get things like Arrival Time on Tickets in T-Stats.  You can’t tell the membership level at the time of purchase.  Constituency Codes at time of purchase.  Address at time of donation…, The name of the Credited Customer on a donation, not the name of the donor…, Sales volumes in comparison to budget, rather than capacity. Payment Schedules are not treated as Transactions they are off in their own special place in the system and rarely show up in reports.)

So, Then when we get a report for custom data.  We go through a process to answer the question in a consistent way with as little customization to our system as necessary. Because customization is very expensive over the long run. (Unless we believe that a certain type of analysis is going to become ubiquities. And then we usually start with the Ad-hock methods below before producing a custom reporting.)

·         First we will look to extracting transactional level data from existing transactional reports or the T-Stats data repository to do a mash-up.  This will often fail to yield results that are useful.  We have a variety of methods to do this.  From saving local files to live OLEDB connections to the T-Stats Cube Repository.

·         Then we will look to making minor additions to List Criteria (if special types of selection of customers is a challenge) Or output sets if special types of data elements is a challenge.  Again bringing this data into MS Excel not for long term storage.  But for analysis and presentation.  Those approaches have often failed as well.

·         Then only with some realistic business justification we will work with IT Programing staff to construct a custom views of data that are either made available as transactional custom reports for further mash-up or via direct OLEDB connections.  (In this way we were able to have graphic representations of daily Sales Trend lines, several years before the RMA delivered such functionality, and relate this to budgeted numbers. As well as a number of other innovations.)

·         Most recently we are starting to be able to include live 3^rd party data using new tools like PowerQuery and PowerPivot from Microsoft.  (During conference this year, I’ll spend some time talking about these new methods.   This is new for us and a rapidly developing approach.  That can allow things like 3^rd party mash-ups with things like census data or other data available from 3^rd parties.)

I’d be glad to discuss this with you further if you are interested.

--Tom

…

718.724.8135

tbrown@BAM.org

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Chris Jensen
Sent: Tuesday, June 17, 2014 10:55 AM
To: Thomas Brown
Subject: Re: [Tessitura Technical Forum] Report Writing - Who does it?

Kjersten Schladetzky:

I'm looking for some ideas on how you handle custom report writing in your organization as well as what you think are the pros and cons of your chosen model.  So, for example, does 1 person in IT handle all custom reporting needs or do you have a power user in each department that can write reports, but use IT to vet them and move them into the Production system?  Or something else entirely?  

Hi, Kjersten--

One report writer here (me), in IT. No-one outside IT has access to SQL or report writing tools. In some cases it might be nice to have one more, though being a single conduit for report requests definitely cuts down on needless duplication.