Dimensional Data Modeling Patterns with Johnny Winter - Episode #73

Welcome to the Agile Data Podcast, where we talk about the merging of agile product and data patterns in a simply magical way. My first book, An Agile Data Guide to Information Product Canvas, is published on Amazon. If you hear your stakeholders say, "Hey, that's not what I wanted," and then you hear your team reply with, but that's what you asked for, then you probably have a problem capturing data requirements. The Information Product Canvas is a pattern template that is a quick and simple way to solve that problem. Head over to agiledataguides.com and click on the green book link to buy a copy now. Welcome to the Agile Data Podcast. I'm Shane Gibson.

And I'm Jonny Windsor.

Hey, Jonny, thanks for coming on. Today, we're going to talk about the patterns of dimensioning modeling. It's an interesting one for me because I started my career in data when we did lots of 3NF, and then we went on to dimensioning modeling. I'm that old. And then I moved on. I used a bunch of other patterns. And I was sitting back the other day thinking, holy shit, I've had a bunch of podcasts talk about modeling patterns, but actually nobody on to describe dimensional modeling, which pretty much is still the number one analytical modeling technique in the world. Before we rip into that, why don't you give the audience a bit of background about yourself?

Yeah, absolutely. So I guess for people listening, actually, even as you did that intro, all that was going on in my head was the intro music, because that's like a ripping tune. And if I ever do my own podcast, like, the standard's been set in terms of intro music. But yeah, me, I'm Johnny. I am based in Preston, Lancashire, in the UK. I have been working in data since, I always say 2007. Somewhere at my house, I've got a certificate where I went on a Crystal Reports course. So that's where I cut my teeth as a data professional. So basically a report writer analyst type role using good old Business Objects Crystal Reports. That's where I learned my SQL, then effectively graduated, which I think is quite a common path from that kind of sort of analysty type role into more the sort of full BI developer, as we called it back in the day. Moved on to building, developing data warehouses, the ETL and all of the dimensional modeling that came with that. Historically, that was good old Microsoft stack. So on-premise SQL Server, Integration Services, those kinds of things. I still did like the reporting services element of it as well. So I did the full sort of end-to-end. It was before we got trendy and had data engineers versus analysts, as it were. I just used to do absolutely everything. And then yeah, graduated more into cloudy stuff from a tech perspective. My last 4 or 5 years, I've been working in data consultancy for a couple of different consultancy firms, and I'm a consultant today looking to start my own independent consultancy imminently.

Wow. Welcome to the chaos that is doing a business, running a business, and trying to grow a business.

Yeah. Looking forward to it.

How to take a 40-hour job and make it 120 a week.

Yeah. Sort of simultaneously terrified and excited. What is it? Find a job you love and you never have to work another day in your life. And I'm pretty sure that it works the other way as well. Find a job you love and you'll never have another day off in your life. Yeah.

I think the key I say to people is always Always make sure you have annual leave. The problem with consulting is there's going to be lumps and bumps. There's going to be times where you're not working due to no choice of your own. And because of that, you tend not to book holidays and then you burn out. So yeah, just get somebody else to help you run the company, your partner or whatever, and say, this is annual leave. You've got to take it every year because otherwise you won't and you'll regret it. Crystal Reports though, the real question there to age people is, was that Crystal Reports before Business Objects or after Business Objects?

So in terms of my exposure to it, I started using it just after it got acquired. So I think it was Seagate before that. So when I started using it, the company I worked for at the time, there was this Seagate footprint everywhere. Like quite a lot of the system accounts that we'd use for accessing databases, because we didn't do things properly with, like you would do today with service principals and whatnot. So a lot of the accounts that we'd access things were like Seagate, everything was labeled Seagate, but yeah, they had just been acquired by Business Objects at that point in time. I think by the time I stopped using it was just as they got acquired by SAP as well. But yeah, it was just the Crystal Reports side of it. I never really touched like the universe side of it, the semantic layer, which is weird because I'm like a bit of a semantic layer geek now. I guess my sort of, public persona in the kind of data community, a lot of it has been built around Power BI, which is a semantic model, is like, I'm a big fan. And one of the reasons that I'm a big dimensional modeling fan as well, because it's one of the things that Power BI is absolutely optimized for. But yeah, never really got to grips with universes. I was just a pure Crystal Reports guy. And at the time, it was Crystal Reports always on OLTP databases as well. When I learned my trade, I didn't know what a data warehouse was. I didn't know what a dimensional model was.

I think it's back in the days where you used to do ODSs, which were really just near real-time replicas or overnight replicas of the source system. And then you were munging all those horrible tables together and there was no analytical layer as such. It was just a bunch of bloody horrible queries, which is where Business Objects and the Universes actually had massive value. And then from memory, it took them ages to get Crystal Reports to run against the semantic layer. It reminds me of SQL Server Reporting Services, SSRS, where again, that was direct query. Against the relational databases that never really had the semantic layer that you got when Power BI kind of took hold.

Yeah, funnily enough, Crystal Reports to SQL Server Reporting Services was exactly the sort of step that I took. As I left the role I was doing using Crystal Reports, we were actually in the process of migrating to SQL Server Reporting Services instead. So I had a bit of a foot in both camps to an extent. Definitely from an Analysis Services perspective, you could connect SSRS reports to Analysis Services semantic layer really easily. The Crystal Reports I had exposure to is exactly what you described, overnight ODS, just a database replication once a night, and then you'd write your reports. And I got forever frustrated about the fact I'd have to write the same convoluted business logic over and over again for lots and lots of different similar concepts, similar reports for stakeholders, or we need to do this thing, and I'd do it and be quite pleased with it. And then it was like somebody else wants something a little bit different, perhaps a variation on it, and I'd have to reuse some of that logic all over again. And it sent me down a bit of a rabbit hole in terms of trying to research that there must be a better way. And that is when I picked up my first copy of the Data Warehouse Toolkit and learned about what a data warehouse was. And ultimately, I actually left that role because the projects to implement our first data warehouse spent 2 years getting off the ground. And eventually I ran out of patience because they'd not even started it by the time I actually ended up resigning. So I was like, no, I'm fed up with this. I'm going to go work somewhere that's actually doing the things I want to do.

Forget the days where we used to spend 6 months to a year doing requirements and then 6 months to a year buying hardware and waiting to rack and stack it. Get the database installed before we could even start. The world has certainly moved in a good way.

Yeah, this is working in defense industry as well. So they were very risk-averse and the amount of red tape we had to go through for any kind of IT type projects was just horrific anyway. So I get the impression speaking to former colleagues that even with the way the world's gone these days, it's still like that. They're still mostly on-prem based and getting anything up and running is still very slow. So Glad I got out when I did.

Let's go into that idea of data warehousing, dimensional modeling, star schemas, all those good words. I just kind of want to go through and just discuss the patterns. 'Cause what we've seen is quite interesting with the adoption of DBT as a tool. Dimensional modeling seems to have come back to the fore for those people that are modeling or consciously modeling. Dimensional modeling seems to have come back as, again, the number one modeling technique. That is used in those kind of modern data stacks. So let's start off at the beginning. Dimensional modeling has this concept of facts and dims. Talk me through those.

Yeah, that is not far off the standard sort of textbook answer if you were to describe what is dimensional modeling. Organizing your data into two categories of table, facts or dimensions. The facts tend to be the measurements, the things you actually potentially want to aggregate or trend. The dimensions are effectively the context that you apply to those. So, I always talk about the slicing and dicing of the data. I think, like, when you first asked whether or not I'd like to be a podcast guest and talk about dimensional modeling, we talked about the fact that I was going to essentially do a bit of a blog series about dimensional modeling, and that perhaps we could then wrap that up as me being a podcast guest. And I think I wrote part 1, and part 2 is still in draft, and I've just not gotten around to it. And I was like, yeah, okay, Shane, let's just do the podcast, because the blogs are going slow. Still been putting quite a lot of thought into what the content of those blogs is going to be. And one of those is absolutely this sort of, oh, dimensional modeling. Yeah, it's just facts and dimensions. And then you dive into it and it really isn't. It's actually lots more nuanced than that. So I guess the first layer of the onion is very much the, yeah, facts, the things that you want to be able to measure, the event almost is the way that I describe it, especially in the sort of Lawrence Kor business eventy type. Context, and then yeah, the dimensions being, when I think of my 7 Ws from a Lawrence Corona Beam perspective, my how many is being my facts, and then my other Ws, my who, what, when, why, where, how, we know the Ws. Those might be the dimensions that are going to sit around it.

Yeah, I think about it as DIMs are things we want to look at, the things we can see, customer, supplier, employee, store, those kinds of things. And then the facts are things we want to count. Order value, payment value, those kind of things.

Yeah, absolutely.

And what we're doing is effectively we're breaking the data out into those types of tables. Primarily in the early days, it was around constraints. So our databases were constrained in such a way for performance and cost that we couldn't just load all the data effectively into a data lake and query it willy-nilly. We actually had to model it in certain ways. For performance reasons, as well as reuse reasons. And that's where it came from. And so, yeah, we got this idea of DIMM being a thing and a fact being a measure of those things. And then the next thing that often we need to talk about is grain. Yeah. So since we talk about a fact, we will typically want to have a conversation about grain. So do you want to explain how you think about grain of the facts?

In many ways, being brutal about it, I don't tend to overthink about my grain of my facts too much. And like historically, As you say, from almost like a technology constraints perspective, you potentially end up with aggregated grains and things like that. But I've always just gone in at trying to always model at the sort of atomic grain, at the lowest grain anyway. So grain for me is always about level of detail. And when you build a fat table, you've got to figure out what level of detail you're going to get to. So I'm always aiming to get to the very, very lowest grain of detail I can. You can always roll up grain easily in a query, you can always do an aggregation on top of a very granular fact table, but it's very difficult to do the other way around. So for me, that grain, it's trying to get to the lowest detail of information available for me. To an extent, in terms of a dimensional model as well, I'm looking at the sort of the cardinality of the fact table to its dimensions as well. So I want my dimensions for each unit of measurement that's in my fact table should only relate to one value in my dimensions in an ideal world.

We used to talk about transactional facts and aggregated facts. The aggregated facts back then was a constraint-based model. We couldn't hold all the transactions and then query them. And with an aggregated query fast enough, we needed to materialize it or physicalize the aggregations for a performance reason. Whereas now, not so much. We've got the firepower to be lazy and bring it all through, but we get less work up front, more value down the stream.

Yeah, so I think column store, from it going down a sort of what I call a techie gubbins perspective, the advent of column stores really helped with that. Column store databases and now storage formats like Parquet being columnar-based as well, does make aggregating data really easy and it helps reduce the storage footprint as well. So you're getting the best of both worlds with it.

But if you look at a fact, you're still gonna say, is the grain of that fact an order or an order line, aren't you?

Yeah, absolutely. I'd still always strive to just go as low as I can. So in that scenario, I'd always, we should build an order line because we can always roll up to order. But yeah, absolutely.

So treat your facts as the lowest grain possible for now. And then if you have to aggregate or change the grain for another fact table, then you're doing it for a specific reason. It's not an anti-pattern, but you're applying that aggregated pattern for a specific reason, performance, ease of use, something like that, rather than using it as the default.

Yeah, absolutely.

And then the next cool word is slowly changing dimensions, like 1 to, what is it? I can't remember. 7. Yeah, there's all these numbers.

7's as far as I've gotten to in terms of understanding them. In fact, I mean, I could say that there is no way that I could reel off what all 7 are, because they're not, just not used that much. It's zero-indexed as well. I think sometimes people forget there's a type 0 to start with too. So yeah, dimensions effectively, the context of your dimension with regard to your facts, and talking about the phrase I've been using most recently is as-is and as-was type reporting. And the one that I like to talk about is like a sickness record, for argument's sake. So if you were somebody in HR, and you wanted to understand which job titles potentially, or which job roles potentially caused the most illness, and we had a fact table that recorded a transaction every time somebody had a day off sick, and then a dimension for the employee. And one of the attributes of the employee was what their job title is, so that you can then slice it and dice it and say, ah, we can see that data consultants have had 100 sick days this year, whereas agile coaches have only had 5 sick days. So maybe we need to give a bit of wellness training to our data consultants. What you've got to take into account is the fact that someone's job title can change over time. So I'm a data consultant now, but I'm going to be a business owner in a couple of months' time. You were a data consultant and now you're an agile data coach-ish. So yeah, Type 0 is the basically it never ever changes. The best example I always have of that is the time. There will always be 24 hours in a day. There will always be 60 minutes in an hour. There will always be 60 seconds in a minute. So if you've got a time dimension, once you've defined it, you're never going to have to change it. Type 1 is that it gets overwritten. Me, for example, as a currently as a data consultant, if my job title changes to business owner, the record just gets overwritten. But what that means is that all of my historical records will now point to that value. So my historical sick record would now have all of my sickness loaded as a business owner. So it might cover up the fact that data consultant was the thing that was causing the stress. Type 2 basically allows you to track changes over time. So every time there's a change to the record, it creates a new record with it so that you can basically then say that, ah, all those absences that happened last year, the job title was data consultant. All the absences next year, not that I'm ever going to have any, the business owner absences, and then you get a potentially a more accurate reflection of that particular analysis. But you've always got to bear in mind that isn't necessarily what the users always want. Sometimes the users want to know what the current is. Was speaking with a client the other day and they were talking mergers and acquisitions. And so they were talking about the fact that if a particular entity changed its name, they would always want the historical records to be recorded against the current name of the entity, because from a historical reporting perspective, they'd want it to be overwritten. So you've got to understand it from that perspective. Type 3 is history recording, except for rather than with a Type 2, where you get an extra record when the value changes, you get an additional column, and it basically only ever tracks, say, the previous version of it. So you don't get the full history, but you get the previous. The good fun I've been getting into recently is Type 6s and 7s. So Type 6 is a hybrid 1 and 3, and Type 7 is a hybrid 1 and 2, which is when you get into the realms of the fact that you can do both and starting to use things like durable keys. I'm going properly down the rabbit hole at that point.

And I think it'd be fair to say that most people do 1s and 2s. Very rarely do you go onto the other numbers unless there's a really specific use case. And then it's all documented how to do it. You just need to know they exist. It's very rare that you are gonna bake that in. And again, I think in the early days, we used to have to make a call between ones and twos, because again, we had a constraint on how much data we could store. So we would cherry-pick which dimensions we moved to a type two because it involved more data, more complexity. Whereas now I'm guessing everybody is type twoing by default and then may surface a Type 1 view. When you query the data, you always just see as it now. And if you want to, you can query another view, which is as at a certain date, if you choose it. Or is that not true? Are people still defaulting to Type 1 by default and then Type 2 by exception?

My experience has been more Type 1 by default, Type 2 by exception. Yeah, there's so many nuanced arguments in terms of the way to implement it these days. Like if you even get into the realms of persisted keys and things like that, because I'm like an old-school surrogate key guy, and I believe that's still really important from a relational integrity perspective. You get quite a lot of people who prefer the sort of indemnity key. So basically, the scenario where the importance of surrogate key is this idea that it's almost like abstracting the business entity away from the business keys. The fact that you're basically just having something, a key that identifies a particular record that's unique to your data platform. When you get into the realms of things like type 2 dimensions, it's really important to have a surrogate key because you're going to get duplicate business keys or natural keys in there, because every time the record changes, the business key is not going to change, but you need a new unique key in your data warehouse. Some people are fans of the indention keys these days. It's this idea of hashing values to produce a key that's going to be pretty consistent. There are some drawbacks with that, The hashes aren't always perfect, you can end up with clashes. I was always coached on the, just the incrementing key type approach. And there's loads of people who argue against that these days. And what I've, my experience so far, is that people have gotten a bit lazy by using the indented key-value because they can create them on the fly, and they're like, "Oh, great, we're gonna have perfect relational integrity because it always, when we use this hash, it's gonna make all the records match, but they're still not checking that they've got that integrity between their data sources. So you can still end up with missing records and mismatches anyway. So I tend to use the old school surrogate key because it at least forces you to go back and look at your dimensions and make sure that your values exist and that you've got things like unknown members and things like that. I feel like I'm going down like another rabbit hole of, I've said lots and lots of phrases and I take for granted that I know what they mean.

But the good thing about dimensional modeling, and one of the reasons I think it is still popular, so popular as Kimball and Margot Ross, they wrote a hell of a lot. They write lots of books that were easy to read and understand. They wrote lots of blog posts, which became books. Some of those old tips and tricks, we should probably archive them so we don't lose them if those sites ever go down. They give us really good examples of if you have this problem, this is how you deal with it. I'm a big Data Vault fan and we haven't had that gift in Data Vault land. If you read any of the Data Vault books, they're pretty dire. They don't explain things well, in my view. This is my personal opinion, even though I use the modeling technique all the time. All the tips and tricks, there's very few of them that are usable, and lots of people have tried. So yeah, I think part of it is, if you want to understand what a surrogate key is, get the books or go read the blog posts, and it will explain them in infinite detail in a way you can understand. I think one of the key things you said was the surrogate key pattern, this pattern of saying, I'm going to look up unique business key, and then I'm actually going to store another unique key, maybe an incremental integer key that's the incremented up by 1 to say in the data warehouse, in the data platform, that is now the identifier for this customer, this employee, this supplier. We definitely use different technical patterns now. Concatenated business key, I think, is a viable pattern with the technology we have today. Hash keys, yes, we have collisions. That is a problem we need to worry about. How often do they really happen? Would we even know? We wouldn't know. They're just technical implementations. The ability to wrap trust patterns around it to say, if I've got a key in my Salesforce and I've got a key in my operational system, in my Salesforce, it's business key 1 is Johnny, my operational system business key 1 is Shane. I have to actually do some logic to say that I can't just slam those keys and put them in the same place. I have to make sure that they're unique, otherwise I'm going to do a whole lot of bad things. Whether we surrogate, whether we hash, whether we concatenate business keys, who cares? Pick one that works for you for the technology you're using and then make sure it's accurate, it's trustworthy, it's got all that rigor around it because those are the patterns that actually really count. So then let's go back to the ECD2. So I've got Shane the consultant and I've gone on for my lovely 2 weeks holiday because I'm working for a consulting company and I actually get leave. And then I become Shane the company owner and I go and take my 1 day weekend that I get forced to take. By somebody else for the year. We have a bunch of techniques on how we know that record has changed, end-dating versus windowing. So do you want to talk through those?

Yeah. So in terms of the way I've always done it, the terms of all the implementations I've seen, it's always about having that valid from, valid to date, or a start date and end date. That can be supplemented with an is active flag potentially. So you always know what the current one is. I've seen different people do them different ways. I've, like, personally for me, having a definitive start date and end date happen works really nicely. One of the patterns that I hate seeing is people who join on the is_active indicator, because if you happen to be loading a historic record, if it's a backdated payment that needs to go back to when you were working as a consultant, but we load it based on the active flag being business owner, for me, that's an inaccuracy. So for me, I would always sort of time-bound it between my active windows, and I always always hate leaving an end date as null as well, because then you're into the realms of casting that as some high date just to be able to figure out your date ranges. I guess it's one of the interesting things about the pattern, is even the patterns themselves have patterns within them. There's that pattern at a conceptual level, and then there's even other patterns at an implementation level that can differ as well. But yeah, Type 2, you've basically got an activeFrom date and an activeTo date, so what period of time a particular record was valid for.

And this is the key, is that there's the logical modeling or the conceptual modeling of dimensional. I've got a bunch of DIMMs, I've got a bunch of facts, and then there's the physical implementation. How am I doing my end dating strategy? So as you said, I was going to ask you, are you, with your start and end dates, and if it's a current active record, is your end date a null or do you pick a 9999 year?

That's always interesting.

And then we got this idea of windowing, which kind of came out of the Hadoop stage. So when we moved from relational databases to Hadoop, one of the problems was actually changing a record. Was incredibly expensive. So if we landed a record and then we wanted to end date it, actually it wasn't performant. So we moved to this upset model, this idea that we want to be insert only if we can. And so that forced us down the idea of using a pattern of windowing. So we'd only ever have the start date. And then whenever we run the query, we'd go back and say, run a window function and tell me between this period, what is the effectively active record? and that was an expensive query. So we just gotta make choices.

I've seen the window in, it wasn't even in a Hadoop implementation, it was just in a relational database where somebody hadn't done an end date. They'd just say, same rationale, but for the technology, probably the wrong choice. And even for that, it was an expensive query, which is why I prefer not to do it. But again, I've missed that era to an extent. I went from relational databases and then just leapfrogged straight to lakehouses and OpenTable formats. And so with your Deltas and your Icebergs, that kind of, updating historic records. I don't wanna go down the rabbit hole of explaining how it happens, but functionally you can, even though sort of the way under the hood it works is insert only.

Yeah, it is still an update of records and those technologies are still a technical anti-pattern for the technologies. It's just that they've worked out ways to make it work. And you shouldn't care. You may get a query go from 2 seconds to 2.1 seconds, unless you really care, which you don't. At that level. So you just got to choose a pattern that works for you. You can do start-only dates and windowing and then create views on top and the view can give you an end date on the fly. There's lots of choices to make it easy for the end user versus performance. You just got to pick one. The interesting for us is with our product, we end date because it's way cheaper with the partitioning strategy that we use with BigQuery. It's way cheaper for us to end date the records than it is to have a windowing function. So when we looked at it and cost was key for us, we said it's a trade-off decision we're going to make. We're going to go touch that record because it saves us money over everything we do if we use that pattern. So let's pick one. And then the other key is when you walk into a new site, you now got a whole lot of questions you need to ask yourself. Are they dimensionally modeling? Great. Okay. What's the typical grain of the facts? Is it transactional? Yeah. Are there any aggregate facts? No.

Okay.

What's their DIM strategy? It's primarily SCD Type 1. Okay. How do they make a decision when they need a Type 2? Okay. When they do a Type 2, what's their end dating strategy? Are they relying on active flags? Are they start-end dating? How do they fill out the end-dated field? Is it a null? Is it a specific date? Are they windowing? Where's the query for the window? Is it left to the user? Is it in Power BI? There's all these patterns within patterns that are known, but as soon as you walk into a place you've never seen before, you have to ask all those questions because you need to know, you need to follow those patterns. Heaven forbid you have a dimension where one dimension is end-dated and another is windowed. Yeah. You have to say, what the fuck? There may be a reason, but I'm going to ask really hard questions about why are we using a different end-dating strategy for DIMMs in the same data platform? There's got to be a reason for that, apart from, oh, a new developer came on, that's why they did it.

That would absolutely fry my OCD, just in terms of consistency. I can get quite opinionated on what my preferred patterns are, and I don't mind being challenged on those. And even the patterns I prefer have some trade-offs, and if those trade-offs aren't the right trade-offs for a client, then it's okay, great. If you're wanting to optimize for something different to the kind of default that I try to optimize for, that's fine. But yeah, consistency has got to be key. It's great though, that little spiel you just went on in terms of all those things you've got to think of. And in my head I was like, it's just facts and dimensions, dimensional modeling, it's just facts and dimensions.

Actually, I'll tell you what, you've just got me thinking again. So you know how we're working on that Data Layer Checklist? Yeah. I'm wondering if there's like a Dimensional Checklist when you walk in as a consultant to a site you haven't seen before. You just have a little checklist where you go through and you say, here's the patterns it could be. Let me just tick some boxes so that I'm thinking about it. The checklist is just helping me think to ask those questions. And I'm like, yep, I've got to reset because I've come off a gig for another customer that's got a slightly different pattern. And now I've got to reset my brain. And as a consultant, that flipping between organizations, that change of slight variation in pattern sometimes gives you a problem because you haven't reset your brain to the greenfield.

I've struggled with it recently with a client. There's a couple of things that they'd done. First of all, Are you familiar with this? If you were to look it up on the internet, dimensional modeling and fact tables, the common answer will say there are 3 types of fact table. You've got transactional, you've got accumulating snapshot, and then you've got snapshot. Transactional is almost as it is. It's just pretty much append-only. As a new action happens, you keep adding to it. Accumulating snapshot tends to be more sort of process-based. If we took booking this podcast as an example, I think you probably asked me in about March So you'd create a record that said, Shane invited guests for podcast, 1st of March. And then the ball was in my court in terms of committing to a date. And I sat on my hands for a long time. And I think last week was when I was like, let's do it. So that would've been mid-July. So we'd update our record to say, date invited, date confirmed, and then the date it happened, for argument's sake. So 21st of July, as it is now. So that's one record, but we've updated it 3 times. So our accumulating snapshot has been updated based on dates. A clean snapshot, you just take a full data dump every single day. So things like stock balances are normally quite good for that. Bank balances, maybe things like that. Finance teams are quite keen on month-end closing balances and things like that. So you take a snapshot of the data at a point in time. So you've got those 3 types, but actually there is a 4th type that just doesn't get mentioned very often. and I think it's because Kimball only thought about it as a bit of an afterthought. People refer to it as a Type 145 facts table. So have you heard of this Type 145?

No, I haven't actually, but I'm like, wow, what a great name. You ready for it? You ready? Yeah. How to give a thing a stupid name. Yeah.

So the reason that, to be fair, Kimball didn't call it a Type 145, but it's just data modelers have gone with that because all of the supplementary information on the Kimball Group website All of the blogs are numbered. So article number 145, if you were to look it up, basically refers to this fourth type of fact table, which is an accumulating time span snapshot. So we've basically taken two out of the three types of fact table and mashed them together somehow. The other thing that people call them, which really grinds my gears, because it's a complete oxymoron, is they'll call it an SCD Type 2 fact table. And it's okay, SCD is dimension tables and this is a fact table, so it can't be a Type 2. But the reason people get confused with it, because it works the same way, it's like a process-driven type fact table whereby the status of something changes and you get a new record every single time, but it's a change in status. They're really good for processes, things like a sales funnel or something like that. If you've got a particular opportunity that might be, you get a lead and it's a lead between the 1st of January and the 1st of February, and then the 1st of February it becomes a qualified lead. And then after 2 or 3 weeks of talking, it then becomes a proposal, and then maybe it becomes a sale. So you've basically got the same record that goes through 4 different statuses. And then rather than capture that as making it wide and putting extra columns in to represent each stage of the process, you basically create new records and you have a start date and an end date associated with the record, like a Type 2 slowly changing dimension.

That's what I call an administration event change. Invoice entered, invoice reviewed, invoice approved, invoice paid. 'Cause normally it would be a bunch of columns and now I've got a whole lot of SQL magic I've gotta do if I want to go and grab those columns and make them rows for whichever BI tool I'm using. So what you are saying is effectively we get a new row for that dimension key when there's a state change. We have a date for that state change. What about event slamming? So let's say I've got an event of something ordered. I've got an event of a payment. I've got an event of a delivery and I've got an event of a refund. In my understanding with standard dimensional modeling, I'd have 4 fact tables. I'd have an order table, a payment table, a delivery table, and a refund table. Is that true?

Oh, it's classic consultant answer at this point. And you definitely, it depends. My personal preference when I design, and this is not so much a Kimball thing, but more of a Lawrence Koren BIM type thing. I always tend to conceptually model them as separate events. and then having modeled them as separate events, if their conformed dimensionality, there's another great soundbite for you, conformed dimensionality.

It's on my list.

Yeah, if their conformed dimensionality is identical or so close to identical that it's not going to make a difference, and if their grain is also identical, then I may well model them as a single table, but it depends.

Okay, but if I had a fact table that thing is a thing. One fact table and in my whole warehouse, just one fact table that's every event all at the lowest grain. That's an anti-pattern. With dimensional modeling, we don't have fact tables that are thing as a thing. So let's say I've got a fact table with 3 keys, thing 1, thing 2, thing 3. And thing 1 goes back to a dimension. And that dimension is a bunch of keys and then types. So that dimension holds employees, suppliers, customers. And there's a typing on it. And then thing 2 is the event. So it goes back to another DIMM, and that DIMM holds all the records, and they're typed by order, payment, refunds, delivery, packing, all those kinds of things. And then we've got thing 3, it's some kind of location DIMM. And so I go back to a DIMM table, and I've got a bunch of keys, and it's store, every store in the world, and then every website and every URL. So I'm modeling at the highest level of extraction, where I've effectively got 3 dim tables and 1 fact table, and everything's a thing is a thing. That is an anti-pattern for dimension modeling. Dimensional modeling is designed to pick up some of the business language. I should look at a dim and actually understand that's something I can look at and understand that it holds a bucket of things that are different from a bucket of other things in my organization.

Yeah, I agree.

So do we still see dimensions of people? Which is customers, suppliers, and employees, or would you tend to model a customer dim, an employee dim, and a supplier dim?

It always goes back to me to an extent in terms of speaking with my business users and how they would interpret them. For that kind of example, like almost unquestionably, they're gonna be separate dimensions. That's gonna be an employee, supplier, and a customer. Yeah, almost undoubtedly, that's 3 separate concepts. Likely coming from three separate, possibly separate systems, but certainly if they're all in one system, separate tables. Where it gets fun is when you get into the realms of, so example I'm working with at the moment, and we've got this idea of a researcher dimension, and a researcher is actually a subset of employees, but it's okay, should we still have an employee table and a researcher table? We would typically tackle things like that with role-playing dimensions and having an employee dimension that can be filtered and maybe abstracted in a view as different types and things like that.

Yeah, and it's the same with suppliers and customers. Do I have an org dimension that is effectively role-playing customer supplier? There's choices there. As long as they fit the dimensional modeling pattern, you've got a dim, you've got a fact, you're deciding the grain of your fact, you're deciding what form of slightly changing your dim is, you've decided how you're going to manage your keys. It's okay, right? You now just come into some choices about which pattern works best for you. Let's go back to a couple of those things you talked about. Let's go through conform dimensions first and then role-playing dimensions next, because they're key terms that we'll see in dimensional modeling all the time.

Conform dimensions. So this idea that you can reuse the same dimension across multiple facts. So if we had a sickness data model, we had periods of sickness and we had our employees, so we knew which employees had been sick, you could use that employee dimension with your sickness facts and use that as part of a modeled business process, and then you can do your analysis on that. If you had a completely different business domain, let's go with sales, and you wanted to be able to analyze sales, and you wanted to be able to see which employees had sold the most of a particular product, you wouldn't remodel your employee for that sales domain. You would use that conform dimension. So this idea that this single entity can be used across many business events effectively. So the classic one's date, like a date dimension is, I think, not far off every dimensional model ever designed always has some kind of date dimension attached to it. And you're likely just going to reuse that date dimension, be it a finance domain if you were doing financial reporting, or if you're doing sales reporting, or if you're doing marketing or anything HR related, writing and maintaining a single date table means basically that date dimension's conformed across all of your potential facts.

And that's one of the things that's, I dunno if it's unique to dimensional modeling, but it's definitely one of the things that you do in dimensional modeling that you don't tend to do in some of the others. If I'm Data Vault modeling or I'm activity schema modeling, for me, dates are just attributes of a thing. I don't hold a hub and sat for dates in Data Vault. I don't hold dates as a secondary key in activity schema. So in dimensional modeling, you will typically see a date dim, which holds that is, and it's used across every fact table. So another kind of unique thing. I don't know if it's unique, because I haven't actually seen every modeler in the world, but definitely a pattern of a dimensional model is a date DOM. And role-playing. Talk me through role-playing dimensions.

Yeah, role-playing is the idea of a single dimension that can be reused for multiple different things in different contexts. Date is actually quite often a role-playing dimension because we took into account the example we talked about before where you might have an order date, a delivery date. Refund date, typically you wouldn't create 3 specific date tables relating to that. You'd have one date table that you can relate to your fact tables via a variety of foreign keys, and you'd reuse that single dimension in different contexts. Dates are a really typical one, but even things like— try to think of good ones I've had recently— I did a supply chain type thing where location was really important, but it was always from location they were always shipping from one location to a different location, but actually they could always be going in either direction. So rather than having a from location and a to location dimension, we just had a location dimension with everything in it, and then we just role-played to it depending on the context of whether it was the destination or the origin.

And so if I look at the pattern itself, it's effectively in our fact table, we have two columns with keys, but both of those keys come from the same dimension, and it's the typing of the dimension. So another one would be if we ever wanna see, customers and suppliers in the same fact. We might have an organization dim and it's typed by customer supplier, and then we'll see those keys in two different columns in the fact table.

Yeah, possibly.

Let's go out through the rest of the weird dimensional things. So junk dimension, talk me through that one. That's a cool name, right? That's better than fact 145 junk dimension.

The junk dimension. At the same time, it's a terrible name because it makes it sound really throw away and not particularly valuable. So going one back from junk dimension, have you ever come across the concept of a centipede facts table, or is that a new one on you?

That's a new one on me.

Cool. So a centipede facts table is where you end up with a fact table that's got lots of different context around it, and the context is very specific and granular. So you end up with a wide fact table that's got lots and lots of dimensional keys in it. and then the dimensions it's joining to end up being very narrow dimensions with only a few attributes on them. And you end up with, basically, to write any given query, having to do lots and lots of joins all over the shop. And they call it a centipede fact table, because if you think about an ERD, the fact table ends up being very long and thin, with lots of relationships coming off it, like little centipede legs. And they're difficult to use, because your dimensions are spread across so many different sort of, categories, they're difficult to navigate. You've got to write these really unwieldy SQL queries where you've got lots and lots of joins. And they only tend to be like joins that are one hop. We're not talking snowflaking. So they're not that bad from a performance perspective, but from writing them, they become unwieldy. And you can cure that by, there's a couple of things really. If you've got any sort of commonality between those lots and lots of small dimensions, and you can perhaps denormalize them into a single entity, That works nicely. But sometimes you just can't. And almost a way around that, the idea of this junk dimension is to take all those little, teeny-bitty, low cardinality type things, and actually just create sort of the product of them all into one fat dimension that combines all possible given combinations of them so that you've got this one entity that you can navigate through. It's almost like a miscellaneous dimension. Is almost one way that it feels could be a good way. In fact, miscellaneous dimension for me feels like a better description than a junk dimension. But it's almost this kind of just group all these things together because lots and lots of little homes for them doesn't make much sense. So we're just gonna stick 'em in one big group together and allow people to query 'em via that instead.

And so how does the key for that work? Because now we've got a bunch of things that kind of aren't the same. So we're still gonna surrogate it with an incremental key, the business key is going to have no real relationship.

The business key ends up being just a composite key of every column. That's how I've always done them. It's any given combination of a set of different things. I'm trying to think of a good example of it. So I would say in my life, I've only actually implemented junk dimensions, it might only be like twice. I don't find it a particularly common pattern, but I'd know how to do it if I needed to do it. And I think I'd recognize the need for it if I saw it as well. And sometimes I struggle to get my head around why. I would understand that. I'd see a centipede fact table and be like, there must be a better way to consolidate this and make it easier to navigate.

So it's in your toolkit. It's not an anti-pattern. It's a pattern you use, but you use it very rarely. It's only when you go, oh, actually, this is gonna cause me a problem if I do it the normal way. Let me use that alternate pattern and bring it in. And then degenerate dimension?

Yeah. So again, degenerate dimension is like another one that I was gonna be on my blog series in terms of, oh, it's just facts and dimensions, isn't it? So degenerate dimension, different people seem to have different interpretations of them. My interpretation, effectively, it's a dimensional value. It's context that would ordinarily exist on a dimension, but you just retain it on your facts table instead. So you basically get rid of the need to join out to a separate table to use it. The reason for it being a piece of contextual information that is only ever relevant in the context of a given fact, 'Cause at that point, if that given dimension is never ever gonna have any kind of conformity with other things, then you may as well just eliminate the need for it. Again, if I give it that description, theoretically, you could end up with very wide degenerate dimensions, which I definitely wouldn't recommend, 'cause then you end up with a wide fact table. So if it's only a small dimension where the context of it only applies on a given fact, then yeah, use Miss Degenerate. Again, this almost feels like a bit of a personal preference type thing, but it's a pattern I really try and avoid where I can. Because the typical thing that happens for me is that you go through the requirements gathering and you come across this particular concept and it feels like it's a good fit for a degenerate dimension. And then you go to your customer and your client and you discuss it conceptually with them. And does this thing only ever exist in this context? And they say, yep, absolutely. So you do it as a degenerate dimension, and then the next requirement comes along, and all of a sudden, oh yeah, we need that piece of information relating to this different fact at a different grain as well. And at that point, people start trying to join fact tables together, which is a discouraged practice as well. I always try and, even if it's quite a high cardinality, even if it's going to be almost one-to-one with a fact table, I will try to avoid degenerate dimensions if I can, just to promote that reusability in other processes at a later date.

So again, it's not an anti-pattern, but it's a pattern that's used really. It's an exception. You have to justify why using that pattern.

It's a pattern that I use rarely. It's a pattern that I see everybody else use all the time. And then I bang my head against the wall.

One of the problems with degenerate dimensions is you think about it as you are querying the data. You are a user coming in and you don't have a semantic layer. So you're hitting straight against the dimensional model as your semantic layer. And now you've got another rule. So the first rule is you join your DIMMS to your facts, you grab your fact table, you join it to the DIMMS you want. And effectively just creating a denormalized one big table is what you're gonna get back with those queries. And then you are saying, oh, but actually you probably need to check the fact table to make sure there's no degenerative dimensions, because if there's an attribute you need and it's actually in the fact table, not the dim, now you've gotta go and actually do something different to your query. So you are changing their query pattern from the standard pattern to a pattern plus, and now they've got to remember to check whether that attribute's sitting in the fact table or not versus attributes always sit in a DIMM. And so again, we don't have the ability to template our code as much. I suppose it's only a second pattern, right? Run this query and it's got degenerates, but why? Again, why are we justifying slamming the attribute on the fact table when attributes go on a DIMM? Maybe back in the days when we had constraints, but we don't really tend to have those now.

Yeah, I mean, the other sort of fallacy that I hear spouted is that in the world of of Spark engines and parallel processing and those kind of things, that it's more efficient to keep it on the fact table. And I get the impression that maybe that was true 4 or 5 years ago, but all of the major players these days are optimizing their engines for that BI workload, for that kind of star schema type shape, and it deals with them absolutely fine. I've had it a couple of times recently, it's, "Oh yeah, we need to reduce the number of joins," and it's like, why? The engine can deal with it now. It's not as big a problem as people think it is.

I think again, there's lots of those patterns where we have a preference and we try and justify it for the technology that we built that preference from. But things have changed. So test it, just run an experiment. See, simulate the volume of data you think you're going to have and the types of queries you're going to run and run them and see which performs better.

Okay.

So you talked about joining across facts. So again, there's this idea of, was it a bridge table that allows you to slam facts together or join them. I never quite remember that one. Talk me through.

I've never used bridge tables to span facts because ultimately that's just a conformed dimension. That's effectively— Kimball will talk about this idea of drill across, and the way to drill across is, again, it's really strange. I find myself in this position where I've lived and breathed this stuff for so long that I find it sometimes difficult to put into words exactly what's meant by some of it. Kimball puts it into a really great description in terms of this idea of drilling across fact tables. And this idea, have you come across things like the fan trap and the chasm trap and all those kind of things like that? And that fact tables tend to be at different grains. And if you join them together, the cardinality is not going to match. But if you basically root your queries in your dimensions and then aggregate across facts, it works absolutely fine. It's always going to depend on how the fact tables have been structured to an extent. But as a rule, if you basically structure your queries that way, that's how they're designed to work and how they're decided to drill across. And that's what your conformed dimensionality does. Bridge tables, an interesting one. Bridge tables are more, again, one of the patterns I talked about, and when we talked about defining grain, this idea that for every unit of measurement in a fact table, for every transaction, it should have a one-to-one relationship with its dimensions. It's not always the case. The classic example is bank accounts. So I have a joint bank account with my wife. If a bill goes out on a direct debit for our mortgage, for example, That's two customers associated with it. It's one bank account, but it's two customers. So you've almost got to have a bridging table that can resolve that so that your facts table only has one transaction and it can basically join through a bridge table and resolve out to those two customers. So that's where bridge tables mainly get used. So I went down a real rabbit hole. And again, this is for me, the fact that I take pride in this sort of amount of experience and knowledge I've amassed around dimensional modeling, but I'm definitely not encyclopedic. So it's still, for me, hang on, I've got a problem, I don't know how to solve this, but I have got a shelf of books, and in those books are patterns. So the one I was trying to solve recently was around hierarchies, and the best way to resolve recursive hierarchies, you can just flatten them out, which kind of works if they're not fixed depth, they can get a bit messy, but that does work. But there's also patterns you can use with bridge tables that will basically help you resolve recursive hierarchies as well. So that's the idea that we're talking about resolving many-to-many type relationships. And you basically end up that every level of the hierarchy becomes its own record, and then the bridge table resolves it back to the facts. Okay.

So bridge tables are a bridge between the facts and the DIMMs, not a bridge across facts, is what you just said.

That's how I've always used them. I feel like I'm gonna have to get my head in my books and see if I can find any examples of bridge tables between I can't even remember what they were.

And then I just had to think about bridge tables from Data Vault. And then so I'm applying a different pattern for that name in my head.

I can say it's so bizarre. My first foray into data platforms, after having been a reporting analyst, was Kimball. And every subsequent organization I've worked in, and I think you alluded to the fact that it's probably the number one applied pattern in the data industry. It's all I've ever known. I've got real blind spots for Data Vault. I can talk about hubs and satellites and vaguely sound like I know a little bit, but I would not have the foggiest how to start if I had to do a Data Vault implementation.

And it's a different language, and it's the same patterns to a degree. So with Data Vault, effectively, we just take the dimensional key and we make it a hub. So it holds the key only. And then we take the attributes out of a dim and we make those sets, but we can have more than one set table for a hub. So again, it's a very similar pattern, but it's different. And the language is different and the patterns are slightly different. And so you gotta reset your brain and then you go to activity schema. And that's again, it's very similar, but very different. And again, like you said, there's the depth and the breadth. You can do dimensional modeling with some of the core patterns and you'll do really well. And then eventually you'll hit an edge case where you need some of the more obscure patterns. You need to know they exist. But as you said, there's some good books. There's all the Kimball books. There's all the blog posts. And then of Lawrence Kors stuff, half his book has been, which is the stuff I use around the who does thoughts and effectively understanding requirements and concepts and conceptually modeling. And then the other half of his book is how to apply all that to dimensional modeling. And I remember in the course, there's quite a large part of the course is around ragged hierarchies and how you model those in DIMMs. And I don't care, I just use an OLAP cube back then, it takes care of it for me. So yeah, all right, let's get on to what I think will probably be the last one I can remember, which is Factless Facts, which kind of sounds dumb. It's kind of like, fictitious facts.

I'm trying to remember who I was talking to this about the other day, and we just basically decided that's just nonsense. No such thing as a factless fact table. The fact table still has facts in it. It's just that there are no values that you're going to aggregate that you like. So the facts basically represents the intersection of all the dimensions, and ultimately you end up pretty much counting the rows that becomes the measure at a given intersection. Quite often when I do basic star schema examples, I'll fall back on a sales example, I worked in online retail for a long time. And so a typical sales fact table might have sales amount, it might have cost, it might have margin, it might have order quantity. These are all things that you're going to be able to add up and average, things like that. A fatless fact table doesn't have any of those things. It basically just stores the intersection of the various dimensions and ultimately end up counting rows on it to get the facts.

With a fatless fact table, would it just hold the keys or would you have a column with 1, 1 in every row.

I would just have the keys. Again, so I've seen that pattern as well when people just put a 1 in. So from a Power BI perspective, that's considered an anti-pattern. That's just bloat that you don't need. It's just a count of the rows. You don't need the 1 because you just do a count, a count star, or a count 1.

So let's go into that for a second. So everybody tells me, not everybody, but most people tell me that Power BI is far more efficient efficient when you use a star schema versus one big table or anything else. But they never give me the context. And I'm like, is that when you're not using DirectQuery and you're actually bringing the data back into the Power BI layer? Or is that when you're using DAX or when you're creating a semantic model? Where is the thing that says Power BI works best with a star schema?

So I feel like I'm going to have to shout out a couple of Microsoft Oh, former MVPs now, I think. So there's a chap, Coen, Coen Verbeek, Dutch guy. And his catchphrase is that you must star schema all the things. And he's like a massive advocate of it. And he made stickers and t-shirts and all these things. Star schema all the things. And then there's a chap, Ben, I'm going to butcher his name, which he'll kill me for if he listens back to this. He is Belgian, Benny De Heere. He's on the Power BI CAT team and he did a conference session that was taking the mick out of Coen a bit. It. Basically, the session was called Star Schema, All the Things, But Why? And it was really fascinating because they actually dug into it and did a load of tests. So some people were like, oh, you need to use a star schema because you'll get better compression out of the— so Power BI is built on the VertiPaq engine, but it's basically the compression engine for it. Oh, you get better compression, so you'll have a lower memory footprint. And they did a load of tests versus one big table where they basically proved, yeah, that's not really the case. And there was like, oh, star schema will load quicker. 'cause you've got less data redundancy. And he did a load of testing, and it's like, that wasn't really the case. The main thing is that DAX as a language is structured to work with it well. It always strikes me as chicken and egg, like in terms of, is DAX built to work with star schema? Almost that kind of, what was the way around I was thinking about the other day? That effectively, did they make Power BI to be optimized for star schema as opposed to people saying that, ah, star schema is what's optimized for Power BI. The other soundbite I came up with the other day is that dimensional modeling's probably the second best pattern for everything. Speed-wise, one big table, a former colleague who did his thesis on it, star schema versus one big table, and was like, "Oh yeah, one big table's loads better." It's like, "Why is it better?" "Oh, it's quicker." "Okay, what about from maintenance and reusability?" And then when you get into the realms of that, actually, yeah, one big table's good and quick and easy to query, I've got to have a different one big table for lots and lots of different things. And then if I need to update a particular attribute, I'm going to have to update it in lots of different places. And then again, not being an expert on it, but my impression of Data Vault is that flexibility-wise, it's really good. It deals with change lots really easily. People say it's complex to implement. I've never done one. And that potentially querying it because there's a lot of joining of tables can be quite complex and difficult to navigate from a analyst perspective.

Yeah, so let's talk about that one, 'cause that's a really interesting one. It's true, you typically use Data Vault in a layered data architecture, what Joe Reese calls mixed model Darts. So we would typically never expose the Data Vault structures as the core reporting layer. We would dimension it, we'd one big table it, we would activity schema it, we'd do a whole bunch of things to make it easy. Because joining lots of tables together as an analyst is an anti- pattern, in my view. You're getting them to do work that the machine can do for you. And then everybody goes, oh yeah, but everybody can understand how to query a star schema. And I'm like, yeah, they can if you train them. And they can still get it wrong. If I give them one big table, if I give them a table with a grain and all the columns in it, as long as I don't give them 2,000 columns, it's much easier for them to query. Now, yes, what happens now is if my only pattern is one big table, if I write 5,000 DBT models that do all the transformations in code with no segmentation or layering, no shared reuse, no shared context, and I'm creating nothing but 10,000 one big tables, that's a bad pattern. But if my code is effectively my model, the context holds the model, and I'm just hydrating the one big tables at the end, and every time I do a change, those tables are automatically refreshed with those changes, not a human, then that's a pattern that works. If I'm data vaulting and then dimensioning and then one big tabling, It's a pattern. I can automate that and I can hold context and I can get the machine to do all the changes for me when I change that context. So that's my view. And I was always intrigued with Power BI's dimensionless, the norm, and that's fine because with the norm, there's lots of good articles. There's lots of things that have been written. There's lots of people that can help you if you get stuck. And if you don't follow the norm, it's a little bit trickier. But why is it the norm? And the same with dbt. For people that now consciously model rather than unconsciously model, dimensional seems to be the flavor they use the most. Now, why is that? Is it because the information is easily accessible? Is it because that's the things that people have been trained on the most? In our days, Dimensional became so popular because Ralph Kimball did a lot of training. You could always go on a Dimensional training course. It was easy to get hold of somebody that would teach you that. Not so much with the other courses, but he doesn't do the training anymore.

Yeah, he's been retired a long time.

Yeah, and then Margie Ross took over, but she's retired now, so actually who's doing the training? Because it's not the people who invented the patterns. But maybe that's it. Maybe the training is still more accessible, or the books are accessible. It's intriguing. How is that still the modeling pattern? And it has value. It's a really valuable pattern. It's not my favorite pattern. I'll be honest about that. But that's just my opinion. Like you said, you don't use a lot of junk dimensions, your degenerate dimensions. That's just your choice. That's how you model.

Yeah.

And that's fine. You're making conscious decisions around that. Which reminds me, there's one I missed, a late arriving fact. Sorry, that was the other one that we probably need to talk about.

So the way I always interpreted them was this idea that you're, and I guess this is a pattern that we've not really discussed, is this idea that you'd always load your dimensions first and then load your facts afterwards. And part of that is to guarantee you've got that sort of relational integrity. Again, this goes back to that indemnity key argument and the fact that I prefer to look up my keys after the event. So if you're going to look up your keys when you create your fact tables, Means you've always got to load your dimensions first. But what if in between you loading your dimensions and you loading your facts, a new dimension occurs? So we sell a brand new product. So we load our product dimension and that's got every product that exists. And then whilst that is happening, a new product goes live and gets sold straight away. So then basically we've loaded the facts, it's arrived late because it's arrived after the dimensions have been processed. And it doesn't have a matching record back in its dimension table to be able to join to. You deal with that with an unknown member. So basically a default key that gets assigned where the dimensional record doesn't exist. And then I was into the realms of rolling windows from my updates, because then I'd go back and revisit my facts table and update the key, which then for me, that is contrary to this idea of a transactional facts table that should be write-only, because that's not true. You'd still go back for a later arriving fact and update its dimensional key.

Is that effectively we get a fact turns up and there's no dim for the fact. We normally do a placeholder dim, don't we? So there's a dimensional key, 99999 or 0.

Minus 1, pretty much.

Minus 1, that's right. Used to be the big argument, wasn't there, about what do you use as the surrogate key for your late arriving fact? We used to argue about that all the time. So effectively, the fact turns up, the dimension isn't there for whatever reason, you bind it to this dummy dimension key, and then you go and update the fact later when that dim actually arrives, and that way you get consistency or reference integrity across the DIMMs and the facts.

It's strange to an extent because my love of dimensional modeling definitely predates even the invention of Power BI. But having turned into a bit of a Power BI nut, I think that's helped me go deeper and further in understanding all this stuff. And that goes back to Mather. People would argue that the idea of the indemnity key was that actually, because if you do that in your fact table, you don't have to go back and update late afterwards. It's a late arrival fact, it doesn't matter because the key's already been predetermined, so you don't have to worry about it. But yeah, but if you can imagine you were doing nightly batch loads, that's a whole day where you've got a relational integrity issue. And from a Power BI perspective, that can have quite a big impact on your DAX if you've not got proper relational integrity. So that's one of the reasons I always prefer to do the lookup, because then with the lookup, you fall back on your unknown member if you need to.

It's the key, isn't it? Is that these weird patterns are there for a reason because when people have been using this in anger for 20, 30 years, they have found these edge cases that they needed to have a pattern to deal with because they would turn up every now and again. And late arriving facts is one of those.

This is one of the sort of debates we've ended up getting into with engineering teams I've worked with in terms of, and it goes back to something again, Joe Reese talks about, is trade-offs and understanding what it is you're trying to optimize for. 'Cause your pipelines would be more efficient and run quicker if you don't have to do that dimensional key lookup. So your pipelines are run quicker. If they run quicker, they're gonna be cheaper. Your data's gonna be more available. Okay, but if my data's available with relational integrity issues, then it's not accurate data. And I'd rather do it slower and a bit more expensive, but have it accurate, than have the most cost-efficient pipeline because you don't think looking at my dimensions is the efficient thing to do.

But also, again, a lot of How many of the patterns were around technical constraints? Because I'm sure I remember really in the early days of Oracle, when we had foreign keys on the tables between the facts and the DIMMs, the updates were really slow because we had on-premise servers and we were constrained around memory and disk and all that kind of stuff. And I'm pretty sure we used to do an update, drop the referential integrity, drop the foreign keys, then load all the data, making sure that we kept referential integrity by the code in theory, and then we'd reapply the the foreign keys at the end of that process and hope like hell, bloody rebuild. Because that just helped us get the load times down from 12 hours to 2 hours. Now you would never do that now that I know. I mean, half the cloud analytical databases don't have foreign keys for that very reason. But I'd be surprised if anybody's doing that pattern now, where you're altering the tables, removing the referential integrity, doing a load, and then putting it back on.

Yeah. So what's interesting, again, another debate in terms of patterns, but even in my on-prem days with SQL Server in a data warehousing context, we never actually applied foreign key constraints. Never. We always just loaded it. We dealt with them with logic. So whenever we insert this record, we're going to check it's got a key that matches, and if it doesn't, we're going to put the unknown member in there. So battling against the constraint checks wasn't a problem. I guess when I talk referential integrity, that's probably me using my Power BI conditioned brain because I'm I'm not talking about the actual database constraint. I'm just talking about the effects that the database constraint would make, if that makes sense.

Yeah, referential integrity is actually a pattern that says everything has integrity. But then in my head, I just naturally go back to databases apply it. And therefore, whenever I use the term referential integrity, I'm falling back to that pattern of it's a technical implementation, not a logical one. And I think that's interesting, as somebody said to me the other day, is I often bring up ghosts of data paths. And it's intriguing because now I start to think about patterns and I go, where did that pattern come from? Was it a ghosted data path for a technical constraint? Was it a process constraint? Was it a people constraint? Was it an edge case that the patterns don't deal with and therefore it's still valid? Where did it come from? It's intriguing. And I wonder how many of the star schema stuff comes out of tools like Power BI. Like you said, chicken and egg.

I think it's just important to question it and have that curiosity. And go with it from there. What was it? What was the other thing somebody was talking to me about the other day? I had a really good, deep data conversation with a former colleague, and we had a right good natter, bang the world to rights. His problem is that he sees people apply patterns with no context, and because they've seen a pattern applied before, they assume that's the right pattern every single time. And the nuance is in absolutely understanding what patterns are available and which ones to use when, and sometimes when to when to break the rules. Sometimes when to knowingly implement an anti-pattern almost on purpose, because actually it serves a particular edge case and it makes sense in the right context and you can justify it.

Yeah, which makes it a pattern then, which is really weird because actually it's a solution to a problem that's not commonly applied, but with the context actually works.

All right.

And on that one, just to close it out, if people want to get ahold of you, what's the best way for them to to find you, read what you're doing, listen to what you do?

The worst thing you can possibly do is Google me, because if you Google me, the first hit's going to be an albino blues guitarist who's now dead and once performed at Woodstock. So Googling Jonny Winter doesn't work. I can't believe I've gotten this far in the podcast and not mentioned it, that I've got this kind of data persona called Greyskull Analytics. Yeah, it's a He-Man reference, Castle Greyskull. It came out of the idea of He-Man's slogan was, "I have the power," and I had the Power BI. So that's where Grayskull Analytics came from. And then I've turned it into a sort of massive Skeletor reference as well. So yeah, if people look me up on LinkedIn, you'll see quite a few Skeletor-themed memes being shared there with various sort of data contexts in them as well. grayskullanalytics.com is my website. The version that you can currently see is the original version because I've reverted it that, which was just really a blog. I've got a YouTube channel as well, so you can get me on YouTube. But that grayskullanalytics.com website's in the process of being revamped because I'm looking to launch my business in September. But yeah, LinkedIn or grayskullanalytics.com are the best places. Grayskull with an E as opposed to an A because I anglicized it partly because I'm English and partly because I didn't want to get sued by Mattel.

Maybe bring the Grayskull Analytics story right in the beginning next time. That's how I know of you. And I've got to do a shout out for probably what I reckon must be one of the best LLM engineering prompts in the world, that you can constantly generate Grayscale images that actually look like you've got a Grayscale character sitting in your office somewhere and you're just moving them around. The quality of those generations are pretty damn awesome.

Well, it does well. You're not the first person to point it out either. I tried to do one Skeletor playing cricket the other day and it couldn't get its head around not, but yeah, most of the time it manages to make a different, decent fist of it.

All right. That's been great. Thank you for going through all those dimensional patterns. I've ticked off another set of modeling patterns for the podcast and can't believe it's taken me so long to get around to this one. It probably should have been the first one, but anyway, thank you for that. And I hope everybody has a simply magical day. And that, Data Magicians, was another Agile Data Podcast episode. If you want to learn new patterns that you can apply to craft your own Agile Data way of working, head over to agiledata.io.