Most fundraising systems are designed for capturing transactional
data, but few are designed with the ability to run ad-hoc reports with
highly varied selection criteria.
Some systems have a limited data warehouse capability such as campaign rollup
tables, but for sophisticated organizations with a greater variety of
data extraction and analysis requests, the tables in the
transactional systems are usually inadequate.
Advancement is an opportunity driven business.
The number of ad-hoc
report requests is significant since a marketing question can be
asked in so many different ways.
The constant barrage of ad-hoc requests
can cripple your technical staff and take time away from other
We are increasingly working in environments
where resources are scarce. At the same time we're managing very complex technology. Managing security alone can take a full time staff member.
We all need to
reduce the amount of effort expended on reporting and data
Once Deploy Many
Using a ?create once, deploy many? philosophy, you can create tables in your data warehouse that increase your flexibility and
allow you to quickly answer questions from users.
These tables reduce completion time on requests from weeks in some cases, to a matter of hours.
These tables also help fulfill one of the most important
concepts in report writing:
extraction of the data from the formatting of the data.
A large portion of report writing involves making the report presentable.
Most report writing tools are
inefficient for complicated data extractions particularly when there are
complex joins, groupings and sorts.
By separating data extraction from report formatting and having
scripts run automatically to create specialized data warehouse tables, your data
extraction and report writing becomes much more efficient.
This also facilitates the creation of
sub report libraries around common
themes or blocks of data. i.e. On many reports the users want the home
and business address. By using a specialized address table, you can
create a sub report with a home and business address that runs efficiently and can be deployed
as a component in many reports.
Nothing is more time consuming than having to edit a formula in a
number of reports. You?ll find that different developers
have different variable names, formula names, and
have often calculated the same statistics and results in different ways.
Design your tables to eliminate complicated formulas and calculations in reports.
This lowers maintenance. If you change a
calculation, you only have to do it in one script and
all of your tables and reports will be changed.
This will work even better if you?re able to use a sophisticated ETL
(extract, transform, load) tool
to do all
of the calculations based on your business rules and include these in
your transformation objects.
How are your requests are grouped and sorted? By including
decoded descriptions in your tables, i.e. Alumni, Friends, and specialized sort fields,
i.e. 01_May, 02_Jun (fiscal month), sorting and grouping and the related
descriptions will be standard and
consistent on all your reports.
Report standards are
important since a common look and feel for your reports can greatly
improve your user community?s ability to interpret information
visually. Make sure that the tables you design have data formatted in the way it should
Don't forget your annual fund and other program areas that have
large unstructured data requests or require a data mining approach. Well
designed data warehouse tables can easily facilitate all these extractions.
Even though server performance continues to improve at
dramatic rates, use of these tables improves performance. Tables and indexes in your data warehouse will be more efficient to
report from than
your transaction tables.
Data for your on-line community and alumni and friends web presence is
facilitated by these tables.
Data deployed on the web often needs to be formatted differently. These tables save you having to do
formatting in your web programming languages.
These tables provide a facility to convert data. You may not have
the security access to create a stored procedure to convert dates to
fiscal dates, but you can create a table where you can do a lookup that
will perform these calculations for you.
Data warehouse tables help fix inadequacies in your
advancement system. For example, your system may not have a comprehensive
staff table to use for grouping on data entry and other
performance related reports.
It's more time consuming to manage such a table in a warehouse as opposed
to being part of your online system but the warehouse can
provide a good stop gap measure until your vendor is able to update their