Data Warehouse Tables

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 essential tasks.

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 extraction.

Create 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:

Separate the 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 be displayed.

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 system.