Skip to main content

Where Do I Start With Virtuous BI Tables?

Amy Vitulli
  • Updated

If you're using Virtuous BI, it can sometimes be incredibly overwhelming to even know where to start - even if you have a lot of knowledge of BI tools. This guide provides a high level overview of the data in our Virtuous BI tables. For more in-depth information about the data contained in each table, check out the "Getting Started" Virtuous BI Best Practice report. 

Tables

The biggest hurdle to BI is understanding the data structure. If you're familiar with spreadsheet programs, imagine all of the data in your database being grouped and then placed into spreadsheets with similar data points. These are the tables in Virtuous BI. There are four different types of tables you'll see in Virtuous BI:

  • AG - Aggregate Tables - These tables "roll-up" the data in the table. These are ideal for performance analysis and trend monitoring.
  • DM - Dimension Tables - These tables provide details on an object (like contacts, projects, or organization groups).
  • FT - Fact Tables - These tables are based on events that have happened in the database (like gifts, imports, pledges) and give specifics about those events.
  • RP - Reporting Tables - These are tables intended to create reports and are not intended to provide details on the subject. These are useful for high level reporting.

Here are some examples of the types of tables you might use for different scenarios:

  • If you want a list of contacts and some high level information, like address and life to date giving, you can use the DM_Contacts table.
  • If you want to create a report showing the average amount of the first gift by year, you would want to use the AG_Contact_Giving_Statistics table.
  • If you need to see how much was donated this year versus last year by all donors, you might use the FT_Gifts table.
  • If you wanted to create a report on donor giving to campaigns, you might use the RP_Giving_By_Campaign table.

Common Tables 

Now that we've discussed the different types of tables in Virtuous BI, let's discuss tables that have a lot in common to help determine which one(s) you really need. Below are some examples of these tables and highlights of the types of data they include:

  • Giving
    • FT_Gifts - Much like a gift query, this table gives basic gift-level information but does not indicate the project or whether the gift is a pledge or pledge payment (which is important if you're tracking pledges as receivable).
    • FT_Gift_Designations - This table is similar to a gift split query. This looks at gift level information, but broken down by project designation. In this table, each row represents the piece of the gift designated to a project rather than the entire gift.
    • FT_Committed_Gifts - When you have pledges, it's important to know whether a gift is an outright gift, a pledge, or a pledge payment. This table gives overall gift information as well as what kind of payment it is (including grants, pledges, and other "commitments"). If you want to have this data with your designations, you will either need to add a column via lookup or join this table with the FT_Gift_Designations table.
    • FT_Project_Gifts - This table gives you similar information to the FT_Gift_Designations table with additional fields for the projects like Project Location and Needs.
  • Contacts
    • DM_Contacts - This table is similar to a contact query. You can find primary and secondary individuals as well as primary contact information. Additionally, there are some high-level giving statistics.
    • DM_Contact_Addresses - This table lists all addresses for your contacts. This is helpful if you have contacts that live at multiple addresses, and this can be joined with the DM_Contacts table if needed.
    • DM_Custom_Fields - This table (and similarly named ones) provide information about custom fields themselves - not the data filling the field.
    • DM_Contact_Custom_Field_Values - This table (and similarly named ones) provide the data values in the custom fields for contacts as well as data about when they were modified.

Report Basics

Once you're aware of the data structure you'll want to know how to use that data in Virtuous BI. Below are some articles to help you get started:

Related articles