Dimensional Data Modeling Patterns with Johnny Winter - Episode #73

Foreign.

Welcome to the Agile Data Podcast where we talk about the merging of agile Twitter products 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 ask 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 Johnny Winter.

Hey Johnny, thanks for coming on. Today we're going to talk about the patterns of dimensional modeling. It's an interesting one for me because I started my career in data when we did lots of 3 and F and then we went on to dimensionally 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 in Lancashire in the UK and 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. Used to do absolutely everything and then yeah, graduated more into cloudy stuff from a tech perspective. My last four or five 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 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. Seagate before that. So when I started using it at 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 principles 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 customer report side of it. I never really touched like the university 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 date Warehouse was. I didn't know what a dimensional model was.

I think it's back in the days where you used to do ODs, 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 in the universes actually had massive value and then from memory. 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 directquery 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 Super Server Reporting Services instead. So I had a bit of a foot in both camps to an extent. Definitely from a analysis service perspective, you could connect SSRS reports to Analysis Services semantic layer really easily. The Crystal Reports that 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. Oh, we need to do this thing and I do it and be quite pleased with it. And it's 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 Data Warehouse was. And ultimately I actually left that role because the projects to implement our first data Warehouse spent two 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 because I was like, no, I'm fed up with this. I'm going to go work somewhere that's actually doing the things that I want to do.

Forget the days where we used to spend six months to a year doing requirements and then six months to a year buying hardware and wanting to rack and stack it and 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 still very slow. So I'm glad I got out when I did.

Let's go into that idea of data warehousing. Dimensional modeling, star schemas, all those good words just kind of want to go through and just discuss the patterns because what we're seeing 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 tends 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 potentially 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 one and Part two 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. But I've 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 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 core business event E type context. And then, yeah, the dimensions being when I think of my seven W's from a Lawrence core on a beam perspective, my how manys being my facts and then my other W's, my who, what, when, why, where, how we know the W's those might be in the dimensions that are going to sit around it.

Yeah, I think about it as dims of things. We want to look at the things we can see. Customer, supplier, employee, store, those kind 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 dim 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 this atomic grain and 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 facts table, but it's very difficult to do the other way around. So for me, that grain is 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 factor table to its dimensions as well. So I want my dimensions for each unit of measurement that's in my factor 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 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 columns start from going down a sort of what I call a techie goins perspective. The advent of column stores really help with that. Column store databases and now storage formats like parquet being column 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 going to 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 aggregator 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, 2. What is it? I can't remember.

Yeah, there's all these numbers sevens 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 seven are because they're not just not used that much, it's zero index 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're 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 fax 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 five 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 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 one 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, essentially 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 merges 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 gotta understand it from that perspective. Type three is history recording, except for rather than with a type two where you get an extra record when the value changes, you get an additional column and it basically only ever tracks so you 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 sixes and sevens. So type six is a hybrid one and three and type seven is a hybrid one and two, 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 ones or twos. Very rarely do you go on to 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 going to 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 2 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 at 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 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 indebted keys these days. There'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've 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 got a bit lazy by using the indemn key value because they can create them on the fly and they're like, oh great, we're going to have perfect relational integrity because it always when we use this hash it's going to 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 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 Margaret 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 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 just incremented up by one 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 and my salesforce business key one is Johnny. My operational system business key one 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 SED2. So I've got Shane the consultant and I've gone on for my lovely two 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 one 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 in 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 I 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 a 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 a implementation level that can differ as well. But yeah, type two, you've basically got an active from date and active to 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 or null or do you pick a 9999 year? It's always interesting. And then we got this idea of windowing which kind of came out of the Hadoop state. 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 had 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 got to 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 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 Missed that era to an extent. I went from relational databases and then just leapfrogged straight to lakehouses and open table formats. And so with your deltas and your icebergs, that kind of updating historic records, I don't want to 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 STD type one. Okay. How do they make a decision when they need a type 2? Okay, when they do a type 2, what's their in 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, 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 our 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's gotta 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, just facts and dimensions, dimensional modeling, 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 green field.

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 facts tables, the common answer say there are three types of facts.

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 have been mid July. So we'd update our record to say date invited, date confirmed, and then the date it happened, fragment's sake. So 21st of July as it is now, so that's one record, but we've updated it three 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 three types. But actually there is a fourth type that just doesn't get mentioned very often. And I think it's because Kimble 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. How to give this thing a stupid name.

Yeah, 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 a accumulating time span snapshot. So we've basically taken two out of the three types of facts table and mashed them together somehow. The other thing that people call them, which really grinds my gears because it's complete oxymoron, is they'll call it an SCD type two factor table table. And it's okay, SCD is dimension tables and this is a facts 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 factor 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 first February it becomes a qualified lead. And then after two or three 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 four 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, two slowly changing dimensions.

And that's what I call an administration event change. Invoice entered, invoiced, reviewed, invoiced, approved, invoice paid. Because normally it would be a bunch of columns and now I've got a whole lot of SQL magic I've got to do if I want to go and grab those columns and make them rows for whichever BI tool I'm using. So what you're 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 four 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 core and beam type thing, I always tend to conceptually model them as separate events and then having modelled them as separate events, if they're conformed dimensionality, there's another great sound bite for you. Conformed dimensionality.

It's on my list.

Yeah. If they're 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 three keys, thing one, thing two, thing three. And thing one 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 two is the event. So it goes back to another dim. And that dim holds all the records and they're typed by order, payment, refund, delivery, packing, all those kind of things. And then we've got thing three, it's some kind of location dimm. And so I go back to a dim 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 three DIMM tables and one fact table and everything's a thing is a thing that is an anti pattern for dimensional modeling. Dimensional modeling is designed to pick up some of the business language. I should look at a dim and actually understand that 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 reinterpret them for that kind of example. Like almost unquestionably they're going to be separate dimensions. That's going to be an employee, supplier and a customer. Yeah, almost undoubtedly that's three 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 is 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 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 dimensions conformed across all of your potential facts.

And that's one of the things that's. I don't know 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. 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 set 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 dip 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 model in the world, but definitely a pattern of a dimensional model is a date dim 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, a refund date. Typically you wouldn't create three specific date tables relating to that. You'd have one date table that you can relate to your facts tables via a variety of foreign keys and you'd reuse that single dimension in different contexts. Dates are really typical one, but even things like try to think of good ones I had recently I did a supply chain type thing where location was really important, but it was always from location. They were 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 want to 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 good, 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 facts 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 factor 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 factor 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 join. And they tend to be like joins that are one hop. We're not talking snowflake. And 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 the 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. 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 jump 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 going to stick them in one big group together and allow people to query them 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 going to surrogate it with an incremental key. But 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 facts 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 going to 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 going to 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, because at that point, if that given dimension is never, ever going to 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 because then you end up with a wide factor table. So if it's only a small dimension where the context of it only applies on a given fact, then yeah, use my 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 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 factor, a different grain as well. And at that point people start trying to join factor tables together, which is a discouraged practice as well. I always try, and even if it's quite a high card analysis, even if it's going to be almost one to one with a factor table, I will try to Avoid degenerate dimensions if I can, just to promote that reusability and other processes at a later date.

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

It's a pattern that I use, Rally. It's a pattern 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're querying the data, you're 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 dimsDFX, you grab your fact table, you join it to the dims you want and effectively just creating a denormalized one big table is what you're going to get back with those queries. And then you're 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 got to go and actually do something different to your query. So you're 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 dim. 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 dim? 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 oft 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 factor table. And I get the impression that maybe that was true four or five 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 fax. So again, there's this idea of was it a bridge table that allows you to slam some facts together or join them? I never quite remember that one. Talk me through.

I've never used bitch 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. Description in terms of this idea of drilling across factor 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 facts tables tend to be at different grains and if you join them together, the cardinality is not going to match. But if you basically route 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've decided to drill across. And that's what your conformed dimensionality does. Bridge table is an interesting one. Bridge tables and 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 a mortgage, for example, 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 fax 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 of a master round 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, 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 dims, not a bridge across facts is what you just said.

That's how I've always used them. I feel like I'm going to have to get my head in my books and see if I can find any examples of bridge tables between facts.

I can't even remember what they were. And then I just 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 vaults. 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 sats. But we can have more than one Sat 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 activities 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 what 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 courses 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 feckless facts.

I'm trying to remember who I was talking to this about the other day and we just basically decided that's just a 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 scheme 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 factless 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 factless fact table, would it just hold the keys or would you have a column with one and every row?

I would just have the keys again so I've seen that pattern as well when people just put a one 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 account of the rows. It's. You don't need the one because you just do account Account star or account one.

So let's go to that for a second. So everybody tells me, not everybody, but most people tell me that Power BI is far more 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, Kuhn 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 gonna butcher his name, which he'll kill me for if he listens Back to this. He is Belgian Benny Diego. He's on the Power BI CAT team. And he did a conference session that was taking the Mick out of Corona bit. 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, call it the Vertipack engine, but it's basically the compression engine for it. You get better compression so you'll have a lower memory footprint. And he did a load of tests versus one big table where you basically proved, yeah, that's not really the case. And there was like, ah, star scheme will load quicker because if you've got less data redundancy. And he did a load of testing 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 it the other day, that effectively did they make power bi to be optimized for star schema as opposed to people saying that our star schema is is what's optimized for power bi? The other sound bite I came up with the other day is that dimensional modeling is 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 is loads better. So why is it better? 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 is good and quick and easy to query, but. But 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 gonna 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 if 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 your tables can be quite complex and difficult to navigate from a analyst perspective.

Yeah, so let's talk about that one because that's a really interesting one. It's true. You typically use data vault in a layered data architecture, what Joe Reese calls mixed modeled arts. So we would typically never expose the data vault structures as the core reporting layer. We would dimensional it would one big table. It we would activity schema. We'd do a whole bunch of things to make it easy because joining lots of tables together as an analyst, as an anti piece in, 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 the 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 5000 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,001 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, 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 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. 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. And that's fine. You're making conscious decisions around that. Which reminds me, there's what I missed late arriving facts. 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 factor tables means you've always got to load your your dimensions first. But what if in between you loading your dimensions and you're 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 loaded the fact 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 ivan 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 dimension original key 99999.

Or 0 minus 1.

Pretty much minus 1. That's right. It 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 dims and the facts.

Like 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 me. For people would argue that the idea of the indemnity key was that actually because if you do that in your facts table, you don't have to go back and update it afterwards. If it's a late arriving factor, it doesn't matter because the key's already been predetermined so you don't have to worry about it. 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 to 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 patent to deal with because they would turn up every now and again. And the flight arriving fax 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 Rees talks about is trade offs and understanding what it is you're trying to optimize for because your pipelines would be more efficient and run quicker if you don't have to do that dimensional key lookup up. So your pipelines are run quicker. If they run quicker they're going to be cheaper, your data is going to be more available. Okay, but if my data is 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 the patterns were around technical constraints because I'm sure I remember really in the early days of Oracle when we have 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 referent actually by the code and theory and then we'd reapply the foreign keys at the end of that process and hope like Hill bloody rebuilt 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. We are altering the tables, removing the reference 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 premises 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 taught referential integrity. That's probably me using my Power BI conditioned brain because I'm not 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 reference 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 up ghosts of data past and it's intriguing because now I start to think about patterns and I go where did that pattern come from? Was it a ghost of data past 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. We had a right good natter bang the world to rights and 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 isn't absolutely understanding what patterns are available and which ones to use when and sometimes when to 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 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 hold of you, what's the best way for them 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 Johnny Winter doesn't work. I have 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 Grayskull Analytics. Yeah, it's a He man reference, Castle Gray School. 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 contacts 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 back that which was just really a blog. I've got a YouTube channel as well so you can get me on YouTube but that grayschool analytics.com websites in the process of being revamped because I'm looking to launch my business in September. But yeah, LinkedIn or Grayschool analytics.com are the best places. Grayschool 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 Grayscale analytics story right in the beginning next time. It's how I know of you and I got to do 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 Grayskull 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 that. But yeah, most of the time it manages to make a different, decent fist of it.

All right, it'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 round 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.