Video Transcript
Hello everyone, welcome back to This Not That, a BI Vlog where we compare a best practice with a common mistake that we see in the business intelligence world. Today we’re going to be talking about Stage 3 in the BI process, analysis.
Welcome back everyone, my name is Hobbs. Today I’m going to walk you through a common situation that I run into when analyzing data regarding dates. Most of the data that you’re going to interact with, especially if its transactional data, is going to have some kind of date attached. A common thing that I see, which will work maybe 80% of the time, is just using the date for all of your filtering and your interactions based off of this single data column. But I’m going to encourage you to move away from interacting with that date column and instead make a date table. Use Date Tables Not Date Columns. Now why? Why does it matter?
When you create a date table, what you’re going to do is take a single day (you can break it down into further increments if you need to but a day is typically a good place to start) and make a column for each attribute or dimension of that day. For example, a single day could be broken down into week number 4 out of the 52 weeks year, or by month, or by quarter, or by fiscal quarter, or by fiscal month. That additional break down lets you do all sorts of time analysis that would be very tricky if you’re working off of a single date.
For example, I’m working on a project right now where each Friday a client sends us a week’s worth of sales data and assigned to a single day. I created a report to cannibalizing and remake this report for them but it didn’t work because it was daily data when really they needed weekly data. The easiest way to do this, if you’ve got a date table, all you do is you group things by week number. It was as simple as that. It was a three second change to go from daily data to weekly aggregated data. If you only have the date column and you’re doing all your calculations off of that, then you’re going to have to start writing custom calculations to figure out what the week number is for the time period and it gets even worse if you ever, heaven forbid, have to interact with fiscal dates. So I encourage you as you interact with your data and begin your analysis, take the time to set up a really thorough, well filled out date table instead of just interacting with the date column.
If you found some value in today’s episode, we would love to hear from you. You can leave a comment down below. You can also head to our website. If you’ve got a BI best practice that you would like to talk about, I would love to go through that. And you can also follow us on social media.
