AgileData #37 - Conceptually Modeling Concepts, Details and Events in AgileData

Foreign.

Welcome to the Agile Data Podcast where we talk about the merging of agile and data ways of working in a simply magical way.

Welcome to the Agile Data Podcast. I'm Shane Gibson.

And I'm Nigel Vining.

And today Nigel and I thought we would do a little bit of a deep dive into what we call concepts, which are one of the three types of objects that we create and store data as within agiledata IO. So the three objects that we store are concepts, details and events. And the 32nd thing that I use to describe them is concepts are a thing we care about, something we need to manage. So an example of a concept will be a customer, a product or an order. Detail is something that describes those concepts that provide some context. So it might be the customer's name, product's type of, or the order value. And the third object is an event. And an event is where a group of concepts have a relationship at a point in time and they are normally articulated in the way of a core business process. So we would say there is an event of a customer ordering a product and we store data in a certain way when we see those events. So Nigel, we under the covers, you know, everything we do in the event layer is stored as either a concept detail or an event once we've defined it. The config, you know, is that a lot of work is when we implemented that thing, was it months and months of programming to get those pattern for those objects?

No, Shane, actually the data vault modeling tends to be very nicely aligned to the actual code under the covers to make these things work. I guess I was actually just going to start by preface this with my background's Kimbal. So when I historically modeled data, I always thought in the terms of dimensions of Vex, but actually it was a very small learning curve and adjustment to me because effectively a concept is the key of a dimension, detail is effectively all the attributes of a dimension, and the event is effectively all those keys off a fact table. So for me to go from the facts and dims world to a concept detail, events was actually really easy. But what I love most about the data vault, Cs, Ds and Es is actually the wickedly easy to automate under the covers because the patent for a C is really straightforward. It's basically a table with a whole lot of keys in it. DS is not much more complex. It's a table with a whole lot of attributes in it. And events, again, is actually a table with multiple key columns in it. So under the covers we can Automate the snot out of creating and maintaining those and we can even extend them on the fly really easy. We don't end up with that situation known to developers all over that. Once you've built your dimension table and deployed it, a month later someone comes along and says, oh, we've missed an attribute out of it, we need to put in. What you end up with is dimension tables in mature warehouses which have your date columns out on the right, and then there's another 2, 3, 4, 5, 6 columns after those because they've been added on after the fact. The data vault paradigm lets us effectively extend out our detail tables at any point with no real hit.

Yeah, and I think it's important to explain that under the covers, we use the majority of the data vault modeling patterns. The reason we use different terminology. So anybody that's used to data vault will be familiar with hubsats and links or hubs, satellites and links. And so for us, a concept effectively is a hub. Detail is effectively a satellite, and a link is effectively an event. The reason we call them concepts, details and events though is our audience is analysts and business people. And so when we talk about hub, satellites and links, those are quite technical terms that really don't make sense to them. But when we say, you know, we have a concept, which is something you manage, it's. You have a concept of a customer, you have details that describe that customer, and that customer is part of a business process. There's an event that happens at some stage involving them. The business people understand those terms. It was funny, I was on one of the data vault meetup things that we do globally, every, every year. And it was meant to be in Rome this year, but unfortunately it was remote. And we were having a bit of a chat about, you know, the software market at the moment and how data vault's been around for a long time and lots of people were using it in their, in their data platforms as a modeling technique. But the tools themselves are pretty immature or have a low sense of adoption. And my standing joke was, well, why? Because if you're going to create a data vault, you only need six bit of code. Create concept, create detail, create event, where you populate the table. Create the table, Load concept, load detail, load event. Right. So there's only six bits of code to make your whole data platform work with vault.

Yeah, that's exactly right. And even if you come to a level of standardization under the covers, you can actually get that down to two effectively, which is create your table and update your table. Because the patterns, they actually share some commonalities and you can just smudge the lines a bit to even simplify it. It's funny you say that, actually. I'd completely overlooked the hub sets and lengths because I've been calling them concepts, details and events for so long, I actually forgotten that's where they actually came from.

Yeah, well, maybe we'll come out with Data Vault 3 and we'll rename them.

Probably not.

In the data vault world, there's lots of arguments on the detail. As they say, a group of architects is an argument of architects, a group of data vault modelers, an endless argument about particular use of a single field. So for us, we have adopted some things that work for us, for the way we work with our customers and the way the Google platform that we leverage under the covers make the best value of that. So let's talk about these concepts, Nigel. So, you know, in the dimensional world, you know, we had these things called dimensions, you know. Yes. A concept is almost like a key on a dimension, but sometimes it's not. So I'll give you an example. Dates. We don't have the concept of a date and we don't hold that as a concept. A date is not a thing we manage. A date is a piece of detail about something else. Right. It's the date that an order was placed, it was the date that the customer paid the invoice. So that's bound to the date of the invoice. So we don't hold dates as concepts like we did in dimensional modeling. So there are some slight tweaks in terms of the way we use the data vault modeling compared to the way we do the dimensional modeling. But you're right, it is fairly similar. And that also means, doesn't it, that once we've done this modeling in the event layer, we can quite easily present all the data in a consumable way as dimensional star schemas. Again, if we're using a tool that consumes star schemas by choice. Right?

Yeah, exactly right. Because it's not very much. Well, sorry, it's no effort whatsoever for us to put a C and a D back together and call it a. Same with the event table. We can easily join an event table back to its Cs and Ds and call it a fact. So by effectively utilizing a pattern, an easy to implement pattern, we can turn a CD and E back into a collection of dims and facts. And the tool that's sitting on top of it, the BI tool of preference, we'll read those quite happily and not realize that under the covers it's actually something else.

And I think the other thing is when I've worked on some of those dimensional only data warehouse projects that didn't go so well. We were commonly doing something called a factless fact. So what was that?

A factless fact. So a factless fact. We were typically trying to join something together. I'm just trying to think of an example of one. He put me on the spot. A factless fact. There's nothing to measure, but we needed to join two things together. So I don't know. You threw that one in there. You got an example?

Actually, I'm trying to think. I mean, I normally treat them as hierarchies, but that's probably not a good example. So they're an exceptional, like they're an exception to the fact modeling technique, to the dimensional modeling technique.

Right.

The factless facts. It was something that was used where there was no value for that fact happening. So it was an anti pattern effectively. And the point I was trying to get to is within the way we model, you can have an event that's not driven by an order value. You can have a set of concepts that have a relationship at a point in time. And that event concept, that event table we have where we say these concepts have this relationship as at this point in time is valid. Right? It doesn't have to be a numerical number driving the fact that that relationship happened. There just actually has to be a relationship in the data to say that it happened.

Absolutely. And that's quite a nice real world thing. Something happened. These three things intersected at this point in time. A person and, you know, something they did or. Yeah, can't think of an example for that either.

I've just Mr. Googled it. Student attendance at a class.

Oh, right, yeah. Okay. So I guess you could say, you could count that and say it's one attendance. But that's basically as far as the metric that would, you know, be attached to that fact. You could say a student attended a class, count it once. But otherwise you're right, it's just three keys. A student, the class. Well, I guess the attendance is actually the count they attended. Yeah.

So I think with factless facts it was where, you know, there was a count of one and the one didn't exist in the data, so we had to make it up. Whereas for our events, student attends class. That's a classic event.

Right.

We just have the student concept and the attendance concept and the class concept and where we see a relationship where those three things happened in the source system. Somehow we store that as an event record to say that at this point in time these three things had a relationship.

Right.

We saw them happen. So there's probably some complexities when we start talking about concepts because, you know, our source systems are never clean, they're never beautiful, they don't make our life easy. So, you know, in your experience, what are some of the things that you've seen that are a little bit more complex patterns or techniques or recipes we have to use when we're creating these concepts?

Okay, I guess the one that springs to mind, what if we have multiple source systems feeding into our data warehouse? Are we combining those keys together into a single concept? Are we storing them separately? What do we do with keys coming from different places?

So the answer is yes, we are all of those. So they're just choices. So what we can do is we can define source specific concepts. So we could have say, a Salesforce CRM that has a customer record in it, and maybe we've got Xero, a financial system that's got a customer record in it. And Salesforce is holding the events around communications and service calls, and Xero's holding the records around invoices and payments for those customers. So we can create two concepts. We could create a CRM or Salesforce concept and populate that with the Salesforce data. And we could create a Xero or financial customer concept and populate that data with Xero. We would recommend that they have different names, right? So either financial customer and CRM customer, or Salesforce customer and Xero customer. So we know when we're looking at the data, which concept we're looking at now, we may then want to actually have a single list of customers. So what we can do then is create another concept, right, called Customer or single view list of customer, or master customer or golden customer. And then we can take the two concepts and we can slam them together. Now if the keys between the key that identifies a unique customer in Salesforce and a unique customer in Xero, well managed and synchronized. So if you're customer 99 in Salesforce, you are customer 99 and 0, then just basically slamming those keys into that one concept is magic, right? It'll do it all for you. Nine times out of 10 that's not true. Nine times out of 10, the way we describe those customers or identify them, sorry, not describe them, is different. So then what we have to do is write a rule to say, how are we going to deal with that if the way we create those identifiers is unique for each system and they don't overlap, there are no collisions. So customers in Salesforce always start with letters A, B, C, D, E, F, G. And customers in Xero are 1, 2, 3, 4, 5. We can slam them to the same concept, but we won't get to see the de duping. So where, you know, McDonald's in Salesforce and McDonald's in Xero, they won't give us back the same. All the records from both systems, all the events. So what we do then is we have to write a rule, right? And that's a matching rule that says, how do I know that McDonald's and Salesforce is the same as McDonald's and Xerox? And then what do I do about that? And we end up creating one record, one concept record for McDonald's from one to six. We may find out that, funny enough, there's actually six McDonald's customers in Salesforce, but they're all the same customer because we just don't use the system properly. So we'll write a rule there to again, create a single ID for McDonald's in that single list for that concept. So just done through rules.

Great.

Okay. That's just one pet technique and recipe.

That we use for that.

The other one then is really important, is this idea of details. So we have details coming out of Salesforce for McDonald's, and we have details coming out of Xero for McDonald's. So we may have an address, the addresses may not align. So we will normally create a detail table with the Salesforce detail and a detail table with the zero detail. So we can always see what the data looks like based on what the source system looks like or the data factory. And then again, we have to make some choices. What do we pick as the single address that is the master? And again, they're just rules. Is it the last one that was updated? If we've got six McDonald's, do we say the address that's seen the most often is the winner? Do we say, actually, Salesforce is normally right, zero is normally wrong. So if you see one in Salesforce, use it. If you don't, grab the one from Xero. So again, they're just rules that we use to identify what's important.

So you're effectively using rules to remove the complexity and ambiguity in your source data. So by applying the right rules, you're effectively getting to your. What you call master records. And it's just a rule?

Yes, because everything we do is Just a rule. And if you're a consumer of the data, you don't want to have to typically choose because you don't understand the rule you need to apply normally to figure out what's right. That work should be done by the data magician in the event layer before it becomes consumable.

You just want to see the unique list of customers and their current address details.

Yeah, and I always thought, yeah, this isn't going to happen that often. But I read a research paper a little while ago and what they were saying is organizations between 0 and 50 employees has on average 7.8 source systems, 7.8 data factories, which blew me away, and over 50 employees. I can't remember the number, but it was a lot more. So if you think about 7.8 systems, how many of those are storing information without a customer? Unusual not to have to combine this data at some stage.

Absolutely. So this is somewhere that the concept of concepts and rules would really be beneficial for these organizations to clean up their data, present it back in a, I guess what you'd call a single view of customer.

Yeah. And because we take an agile approach, we can do it incrementally. So if reporting on customer from Salesforce is the most the highest value piece of information for the organization right now, just create the concept of a Salesforce customer, start reporting on it. Don't worry about Xero. But if actually combining the customer records across two systems or three systems is your most highest problem, you know your biggest pain in the bum right now. And the highest value or de juping the customers from one system is your problem. Again, you just write rules that create concepts that deal with that problem and we have the flexibility to change it later on. I mean, one of the other recipes or patterns we can use is you can from the beginning just create one single customer concept and don't do source specific ones. And when the keys come in, always write a rule. Make sure that the identifiers for the customers are unique and are combined and we don't hold them separately. That's okay as well. We tend not to do that ourselves because we want to incrementally show value to our users. So creating the concept of a Salesforce customer and allowing to count it and query it has value for them and we can do that quickly. And then doing Xero on its own has value for them and we can do that quickly and they can use that data while we then figure out the rules to create that single list. So that's one of the things we're really keen on from an agile way of working is you can chunk it down to small bits and you know when you get a rule that takes you some time, there's some value there already for the consumer.

Great.

Yeah. So that's the concept of a concept. I always struggle with that one a little bit because a concept has a terminology around concept of a concept. So yeah, I was always wondering if we were going to come up with a different term. But actually the idea that we, or the notion that we have a concept that we manage, we want to count and it has value, it's pretty useful. And the cool thing is I've worked with teams before where we have our analysts modeling our data for us without falling back to that age old pattern of the grumpy enterprise data modeler who sits in his little window office for nine months to come out with that perfect model that nobody can implement and nobody can use. So for us it's about model fast, model well, and then change it when you need to.

Absolutely. Oh, fantastic. So that's probably concept. So next time we'll do details. We'll dig into details, which is the second part of this equation.

Detail of details.

Excellent. All right, well, thank you and we'll catch up soon.

Thank you.

Let's make magic happen. And that Data Magicians was another agile data podcast. If you'd like to learn more on applying an agile way of working to your data and analytics, head over to agiledata IO.