Core Docs - Church Management - Intro to BI

Rock Version: v20.0
Last Modified: 2026-04-24 11:52 AM

Business Intelligence is a buzz word for tools that allow you to quickly analyze data and present actionable information to leaders. In large organizations, these tools usually are separate from the normal day-to-day systems, but in Rock we’ve simplified the process and built the tools right in.

Why You Need Rock's BI Tools

With all of the reporting tools in Rock, why do I even need the BI capabilities? That’s a great question! It really comes down to speed and simplicity. Think about your kitchen. It’s nice and neat with everything having its place (even if yours isn’t, pretend it is). For your normal meals, this is great and organized, nothing is ever lost. That’s Rock’s normal mode (we’d say “transactional mode”).

Now let’s consider a Thanksgiving feast for thirty people. To speed up the cooking you’re probably going to pull everything you need out of the cupboards and line it up in the order you’ll need it. As you start wanting to run large reports and find insights into your data, Rock needs to do the same thing to stay efficient. It needs to arrange the data in a different fashion that’s optimized for speed.

Most systems don’t do both the daily cooking and the large meal. They require you to manually design the process for large analytics tasks. Rock’s BI tools simplify this process and automate much of it. You still need to provide it with a little guidance on what you’d like to see, but it’s much easier than traditional systems.

As a part of this process, Rock will create a set of new tables (aka, models) that contain a simplified version of your data that is very fast to process. These tables will need to be updated on a routine basis. We’ll discuss how you set that up a little later. It’s important for you to know, however, that the BI Analytics models are not updated in real-time. They represent a snapshot of the data from the last time the analytics jobs ran (typically nightly).

How The BI Tools Are Used

Once the BI tools are configured and running, they’re ready for you to use them for reporting. There are a couple of areas where you can apply these tools.

Types of Analytics Tables

If you’re used to a traditional transaction data model you may find the model for BI a bit confusing. Things like normalization fly out the window in exchange for raw speed. If you have no idea what we’re talking about, no problem; you’re actually at an advantage as you’re unencumbered by the rigid thinking of traditional systems.

There are two different types of analytics tables: Facts and Dimensions. What’s the difference?

Facts & Dimensions

Facts are the things you’re going to report on, such as Attendance or Financial Transactions. Dimensions (often referred to simply as Dim) are characteristics of the Facts. For instance, Dimensions of Attendance would be the Location, Date or Person of an attendance record. Dimensions of a Financial Transaction would be the Batch, Transaction Type or Account.

Another way to think of this is Facts are what you’re measuring while Dimensions are what you’ll filter by.

The tables that you will be importing into your BI tool are all SQL Server Views and start with the prefix "Analytics". For example, the fact model for financial transaction would be (AnalyticsFactFinancialTransaction). The dimension model for the account would be (AnalyticsDimFinancialAccount), the transaction type would be (AnalyticsDimFinacialTransactionType), and the date would be (AnalyticsDimFinancialTransactionDate), to name a few.

When displaying these models as a diagram the fact model usually goes in the center with the dimension models around it. This arrangement creates what is known as a Star Topology. Diagrams for Rock’s financial and attendance models can be found below.

SQL Server Tables & Analytics Source Tables
If you’re super observant you may have noticed some analytics SQL Server Tables, or Analytics Source Tables. Don’t worry about these. They contain some of the source data for the views above. They are not a complete representation of data and should not be used directly.

Renaming Metrics Tables
Renaming a Metric will not update the metric's table/view name in the context of BI. For instance, AnalyticsFactMyTableName will not be updated to AnalyticsFactMyNewTableName if the name of the metric is changed in Rock.  

Analytics Source Date

Working with the calendar in reporting can be difficult. Different months have different numbers of days, there are leap years to account for and individual weeks can cross months or years. Getting year-over-year or quarter-over-quarter calculations is especially challenging given these factors. That's where the Analytics Source Date table can help.

Analytics Source Date Table

The Analytics Source Date table ships with Rock and contains a row for every date from about 100 years ago through to about 100 years from now. So whatever date you're looking for, there should be a row in the table for it. Using the different properties of the Analytics Source Date table provides ways to approach dates and date-related data that would be much more complicated, and less performant, if you had to do all these calculations yourself. It's a powerful tool that you'll find yourself using more as you get more familiar with it.

Many properties for each date are listed in the table. One that you'll want to be aware of is the DateKey property, which can be used to link the Analytics Source Date table to a variety of other tables throughout Rock. For instance, the MetricValue table has a property called MetricValueDateKey which provides the date associated with a metric value in a format that can be used to join to the Analytics Source Date table's DateKey property.

Besides MetricValue, other tables that have a Date Key property you can use to link to Analytics Source Date include:

Aside from DateKey, you'll find many other useful properties in the AnalyticsSourceDate table. For instance, you can get different formats of the date. You can also see the day of the week that the date is (e.g., '0' for Sunday, '1' for Monday). For each date you can also get the associated Sunday Date, which month the date is in for giving, as well as a variety of Calendar and Fiscal Year data showing things like which quarter or week the date falls in. There are also indicators telling you if the date is a holiday, or if the date falls within the week of a holiday.

The last property of the Analytics Source Date table is called Count. Every date in the table has the same value of '1' for this property. This lets you easily do counts against the dates. For instance, if you have two different dates then you could get all the records between those two dates and simply sum up the Count property to give you the number of days between the two dates.

For additional information on the Analytics Source Date table and its properties, check out this video.

Using Week Of Year

Providing year over year comparisons of metrics can be very helpful. Unfortunately, it can also be difficult and taxing on the server to implement. To help ease the creation of these metrics and reduce the processing load on the server you can use the WeekOfYear column on the AnalyticsSourceDate model. This field represents the week number of the day within the year for a Monday to Sunday week (just like the name suggests).

Since dates are incredibly difficult to standardize, there are a few points you should understand about how this field is calculated.

There is also a property called WeekCounter that is the week number for all time (at least since 1/1/1870 the first date in the table). This field is helpful to key off of if you want to compare this week to the last x weeks using SQL windowing functions. 

Historical vs. Current Tables

You may notice that several entities have a Current and Historical table (e.g., Person and Family). These entities track changes to certain key properties and attributes over time. Let’s dive in a little deeper to understand these tables and how they can be used.

Historical Tables

As noted, these tables track an entity over time. To do that, a single entity (like a person) may have several records in the historical table, one for each change that was made. A couple of key fields, listed below, help to keep track of these records.

Current Tables

The records in these tables represent the most recent record in the Historical tables. The implementation of this is a simple view that looks for records where the CurrentRowIndicator = 1. Elegant, right?