Rock U - Reporting - BI Models

Transcribed Video Content

Okay. So let's take a deeper look into the business intelligence models within Rock. And just full disclosure, we're gonna go into a little bit of nerdville here. So we're gonna go a little bit deeper. Some of this information you don't really need to know to be successful using the BI tools within Rock. But I think it's really important that if you go the long term, if you take a long term view of this, that you understand these concepts. We're gonna take some of these concepts, we're gonna make them very easy I think to understand. But I would say if you start to feel a little bit lost, that's okay. That's probably to be expected in fact. And don't let that discourage you from moving forward with using, business intelligence within Rock. Okay. So let's take a look at these models and how they work. When you look at BI models in general, there's two types of models. There's dimensions and facts. Now, the fact is gonna be in the middle. And the fact is a thing you're actually reporting on. Okay? So we're gonna show you some real concrete examples. But say attendance might be your fact and then the dimensions are different other attributes about that attendance that you might filter on. Okay. So we're gonna look at that a little bit later and we'll see some of those examples. So just realize that there's two types of models, facts which is usually kind of what you're reporting on and dimensions which are different attributes about those facts that you're gonna filter on. Together, they form what is called a star topology. Okay? So that's why we have the star in here. So your fact is in the middle and these attributes are kind of on the edges. Now, there's no limitation on how many dimensions you can have. Here, we just have five and five points at a star. That's just handy. It kinda worked out that way. But you can have as many as you want. Okay. So let's look at some concrete examples. Financial transactions. That's probably one of the first things you might wanna start reporting on is giving within Rock or the financial transactions with Rock. So financial transactions would be the fact of of those reports. And then the dimensions of those reports would be things the account, the transaction date, the person, the family, or possibly even the batch. Okay? So these are the things you're gonna filter on. It's possible you're gonna filter on a general fund or a building a fund. Date actually might seem very simple to you but it's actually not. Dates are very rich in BI. It's not just a simple September 12, it's which month of the year, what month of the fiscal year, what quarter. So dates are actually very rich and we'll be looking at that a little bit later. And then of course, the family in person. If we were to do one with attendance, let's take another example. Attendance. Attendance would be in the middle. That's the fact. That's the thing you're actually reporting on and what you'll be filtering then on is what location was the attendance at, what date was the attendance at, and then the family and the person tied to that attendance. Okay? So that's another good example. But basically, here, the takeaway is two types of models, facts and dimensions and they work together in what typically is known as a star schema. Let's actually take a look at where we'd find these models within Rock. Now, again, most of you will never have to go into SQL Server Manager to look at this. But if you did have to, I'd wanted wanted to let where they were. So these models are actually views within the database and you can find them because they're prefixed with the word analytics. And if it's a dimension, it's we've prefixed that further with dim. And if it's a fact, it's prefixed with the word fact. Now just because it's labeled dimension doesn't necessarily mean it always has to be. It's just a more common use of it. So you're what we'll see a little bit later in some other videos is that sometimes we actually gonna use person as the fact. Okay? But in most cases, person's gonna be a dimension. So that's why we labeled it dimension. So don't get too too solid on on whether it's a dimension or a fact. Most of the time, these will be dimensions but occasionally, you could actually use them as a fact if you would . Okay. So let's look at one of the fact tables, the attendance fact table. If you look at these attributes, you'd kinda see what you would expect. You would see the date. You'd see what group did they check into, what schedule. And these IDs are really just links out to the dimension tables. Okay? So you'd see all that. You'd actually see the group name. So if you wanted the group name, it is over here too as well as the area name. So in in that case, it's somewhat what we would call denormalized. A lot of times in a transactional system, you only have the ID. We've actually we're very nice and we actually put in the name too. So a lot of these are the properties that you would expect to see. Now, two of them that I wanna kinda point out, we're gonna come back to this a little bit deeper later, is that you have a person key and a current person key. So just note this. Okay? The person key is gonna be related to the person at the time of check-in and the current person key is that same person but it's gonna be linking to the data as it exists today. And that's gonna become much more clear in a few minutes here. Okay. So this is the attendance fact table. Now one of the things that we mentioned on that was that there was a link there to the date dimension based off of this date key. Now this allows us to actually slice and dice the data in all kinds of rich ways. And when you start talking about business intelligence, dates matter and you're gonna wanna see it in lots of different ways. So this allows you to very quickly and easily know what day of the week it was, what's the name of that week name. So in this case, of week name. So it'd be Monday and whereas day of the week would be more of a numeric representation of that. And if you want the abbreviation of that, , that's there too. But you also get the the number in the calendar month, the number in the calendar year. There's also the concept of fiscal months and fiscal years. Out of the box Rock ships with the fiscal year kinda set at January to December but you can change that and it'll recalc what the fiscal month and year is which is very very powerful. But there's just a lot of different attributes about that specific day. And normally in a and and if you're writing SQL, would do all of this but you would do all this by writing code which is fine but it's a little bit slower than having the date data already pre processed and ready to be filtered. We also have some very kind of unique things to for churches whether that that day is Easter and whether that day is the week of Easter, whether that day is Christmas and whether that day is the week of Christmas. So we've tried to think through all the different ways you'd wanna use this data and we've made this analytics dim date models very very powerful. Okay. So now if we look at the person dimension. This is basically everything you would expect to see on a person. So there's record statuses, connection statuses, birth dates, photos, titles, names, marital statuses, and some things that maybe you would think of as more of person attributes because in fact they are. So this dimension table is a combination of the person model within Rock and also attributes. So again, we've denormalized this data. And so here, you'd actually see some attributes. And the attributes that you do pick are up to you. Okay. So if you were to actually look at a person attribute, you would actually see two new fields here, analytics enabled. So if I check this, it'll actually show up now in analytics and analytics history enabled. Okay. And what that means is, yeah, I want it in analytics but I also wanna track its history in analytics. So every time that value changes, I'm gonna know about it and I'm gonna get a new row in analytics to be able to track. And we're gonna dive into that how that works next. Okay. So let's look at the analytics dim person historical table. So when it comes to people, there's actually gonna be two tables. There's a historical and current. Let's start with historical. If you're used to more of a traditional database approach, you're gonna expect to find one row per person in the database. But with these historical business analytics tables, you're actually gonna it's not actually gonna be the case. You're gonna find that there's n number of records per person. So in this case, we have three. So what happens is when when we first created this table, Ted was entered in. He had the first name of Ted, the last name of Decker, and he didn't have a Twitter account. And then he had a whole bunch of other columns that were just for simplicity not showing. Okay. Well, along comes a change. So Ted actually gets a Twitter account and that's added. So we get a new record in our in our database. And then something else happens. He gets a new Twitter account. He changes his Twitter account name. And so we update and add another row. Now, what's unique about this this table here is that we have a current row indicator. So this is basically saying this here is the most current version of Ted that we have. So when we go out to link to attendance, we can link off of a few different things. We're gonna link off of if this was the current row at the time of check-in, we would link in to row two. But then as that data changes, we're also gonna relink to the most current row. Now, what's really great about that is I can find out at the time of check-in what was his Twitter account but I can also easily find out what his current Twitter account is. It's up to me in the business intelligence world to determine which one I want. Now the analytics dim person current table, these are all views in the database remember. This actually isn't gonna read it's not gonna make redundant use of this data. It's actually just querying this this view up here and just showing, hey, just give me all the ones where current row indicator equals one and that's where I'm gonna get this. Okay. So we have these two different models. Historical could have more than one record per person. Dim person current will only have one and it's always gonna be the most current one. Okay? So let's look at this in terms of a financial transaction. the attendance, it's also gonna have two different keys. Authorized person key is gonna be the ID that it was at the time of the transaction. So if I ever wanted to know, hey, when Ted made this transaction, what was his Twitter account? I could come over here and say, oh, it was Ted Decker at that time. I'm also gonna have this authorized current person key. Okay. And that's gonna point down here to three which is actually in actuality the same thing as this row. And I can say now as of today, his Twitter account is Rock Pastor. Okay? So now if you're kinda thinking, how is that gonna be kept up to date because as as this changes, this financial transaction table is gonna have to be updated. Don't worry. We take care of that for you. And that's all magic under the covers. So you just know that the authorized person key was Ted at the time of the transaction and authorized current person key is Ted, the most recent version of Ted that we have and that's gonna tie out to three. So that's how these person historical and person current tables work. Now, we talked about the person historical person current but there's actually another one that works exactly the same way and that's the family historical and family current. Exactly the same concepts as person. Even the family attributes will work the same way as a person attribute. So these are two powerful ways of filtering and linking that data in. Okay. So we've looked at a lot of the tables but we we kind of glossed over the other fact table that we have here which is the financial transaction fact table. But again, when you look at these fields, you're see what you would expect, a lot of the same information. Now, if you wanna go deeper into the concepts that we've just talked about, this is kind of the go to book, the data warehouse toolkit. You can get this off of Amazon. A lot of the stuff that we were reading about to analyze and research when building these tools came actually from this book. So if you wanna dig deeper especially how that current row stuff works and the methodology behind that, it's all kind of came right out of this book and it's pretty much kind of the definitive book on data warehousing. You can also learn more about data warehousing and business intelligence at a high level from Pluralsight and here is a video that you can go to and that we've we actually have this LinkedIn. So if you go to rockrms.compluralsight, you can find a link to this video. But I'll give you a little bit further information about the concepts that we've been discussing.