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 to 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 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 run (typically nightly).

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.

• Tables in red are fact tables.
• Tables in blue are dimension tables.
• Tables with the calendar icon are date dimension. They contain several columns to act as filters (aka, slicers).

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 help to keep track of these records.

• EffectiveDate – This is the date that the record started. If it’s the first and only record for a person this date will be the date the analytics tables were first loaded. If the record is a subsequent change it will be the date of the change.
• ExpireDate – This is the date when the record was no longer the current (latest) change. If this record is the current record the date will be 1/1/9999 (which helps you with SQL compares).
• CurrentRowIndicator – This column notes the latest row for the entity.

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 filters out the records where the CurrentRowIndicator = 1. Elegant, right?

Person Attributes

You can determine which person attributes you would like to have added to the BI models. You can also determine which are marked to track history. Tracking history allows you to see the changes to attributes over time.

Family Attributes

Like Person attributes, you can also mark specific family attributes to track their changes.

Calendars

Part of what makes the BI models so fast is how they handle dates. Each day has a row in a large date table. Along with the actual date is stored the year, month, quarter, etc. This allows the data to be filtered, or sliced, very quickly. Because some of the date information centers around fiscal years, which can be different for each organization, Rock allows you to customize the calendar dimension settings. You can configure this under Admin Tools > System Settings > Calendar Dimension Settings.

BI Job

Rock’s Process BI Analytics job keeps the data that the BI tools uses up to date. The job is located in the Jobs Administration screen (Admin Tools > System Settings > Jobs Administration). While the job ships with Rock, you need to schedule it and run it at least once for the data to be available. (If you open your BI tools and don’t see any data, this is why.)

Let’s take a look at the BI job settings and what each does.

The BI job runs a number of processes, including:

• Process Person BI Analytics – Keeps person tables up to date and processes historical data.
• Process Family BI Analytics – Keeps family tables up to date and processes historical data.
• Process Campus BI Analytics – Keeps campus information in sync.
• Process Financial Transaction BI Analytics – Processes financial transactions and adds any new transactions.
• Process Attendance BI Analytics – Updates attendance data.
• Refresh Power BI Account Tokens – Keeps account tokens from expiring.

Now let's look at how you can customize the BI job by enabling and disabling these settings.

Enabling and Disabling BI Job Settings

You may wonder why you’d want to disable the BI job settings since they keep your data up to date. There are some valid reasons for disabling certain settings, though, such as if you decide not to use Power BI.

Keep in mind that the BI tables are only updated when the job runs; so the tables aren’t 100% up-to-date every minute. You may want to update some data more often than others, running the analytics, say, four times a day as opposed to only once a day. For example, if you want to process person analytics more often than the others, you could disable the Process Person BI Analytics setting and create a separate Process BI Analytics job that runs the person analytics more often, separately from other analytics.

So, you can create multiple version of the job to run different schedules. This gives you a lot of flexibility when it comes to processing data in a way that’s most useful to your organization.

On-Premises Data Gateway

As you use PowerBI in the cloud you’ll want to ensure that your data is always up to date. While you can do that manually using the PowerBI Desktop application, you can automate the process using the PowerBI On-Premises gateway. This is a free tool that will keep your data in Rock in sync with the PowerBI cloud. Below are steps for getting this tool configured. If you prefer a video tutorial, this YouTube video from Microsoft covers the steps in detail: https://www.youtube.com/watch?v=GwuRQhm241c.

1. Download the ‘On-premises data gateway installer’ to your Rock server from your PowerBI account (located under the Download link).
2. Run the installer and select the ‘On-premise data gateway’.
3. After the install, you’ll be asked to enter an email address that is assigned to your account. This will allow you to link the gateway to your account.
4. Once your gateway is linked you’re ready to define a source to your Rock SQL Server. To do this follow these steps:
   1. Select the Settings option on the PowerBI web portal (the gear icon), then select ‘Manage Gateways’. You should see the gateway you just named and registered listed.
   2. Place your cursor over the gateway. This should display an ellipsis. Click the ellipsis and select ‘Add a datasource’.
   3. Give your new datasource a name and enter the configuration info needed for a SQL Server Data Source Type. (Tip: you might consider using the same database user name and password that is defined in your website’s connection string).
5. Finally, create a new PowerBI project using the desktop application. Connect to the same SQL Server and select the Rock BI models. (Hint, they’re the tables and views that start with the word “Analytics”).

Considerations for Embedded Reports

Rock includes the awesome ability to embed Power BI reports right onto a page, which provides a great user experience without end users having to learn or use a special reporting client. However, this power comes at a cost. We have compiled the various options for utilizing Power BI reports in Rock and what Microsoft charges for these features below.