Ask any experienced Data Professional, “What’s the most important table in Analytics?” They will all tell you the same table, but with many different names. “Dates”, “Calendar” and “DimDates”… to name a few. The Calendar in Analytics is so important because most report packs have an element of comparison across dates. The converse of that is the table that is least often used although can in many ways be the most evocative name – The Time Dimension!
Most Data people have a calendar table structure that they prefer, within Power BI we recommend setting one up on your main data source or, if one does not exist, setting up a “Core” Power BI Dataflow, then implementing your favourite solution once and share it with everyone! Remember, excellence in data comes from sharing and collaborating, so if you’re not sharing what should be open across your organisation, then you should be asking yourself if you’re in the right line of work.
Time – or your Time Dimension needs the same care; however, it also has the power to be the table that breaks your business! Allow us to unpack that… and explain why your calendar is so important. Time is all about granularity, when you work in data, this will bite you in the backside at least once a year, if not once a project. Granularity relates to the level of detail that you want to report against. So, remember the level of detail, your Time Dimension needs to relate to your business level of time detail, but remember your data source (especially now) is more than likely a data-time field so time goes to the 1000th of the second, so do we care about sales per 1000th of a second or would Hourly Sales work? Hopefully, you can see that aggregating around an hourly granularity would benefit your business more than trying to see if there are more or less sales per 1000th of a second. So how can this break your business? What is my reporting or analytical benefit? I would likely be analysing sales to see if I have enough staff available to support sales activity. This means that whatever my shift management can be, would be the limit of the granularity. We have suggested hourly, but it may be half-hourly either way, you will need to aggregate the time values from within the datetime values held within your data. These bring out the next issue, perhaps the most important – why you split your date and time values.
Date and Time values get separated to allow comparative aggregation. After all the real questions we would want to know about our shifts would not just be about, when is the busiest time of the day, but also the week… the month… the year and those values only come when we can compare our aggregations. That is where the insights start to flow, “Peak sales are on Thursday between 10:00 and 14:00” or “We sell the most of Product Y within the first week of the month”. So comparative aggregations are the key to analysis, but that still doesn’t explain why Time is such a business breaker. Well, the time dimension can lead people to fixate on an irrelevant granularity. This can lead to “Analysis Paralysis”. The real-world scenario is that a time dimension leads to the vision of “Real Time Reporting”, the logic is clear “If we can track our performance real-time then we can act immediately”, the problem is that by doing that you end up with your leadership looking at blips rather than shaping strategy. In a previous life we created an email responder for the Senior Leadership Team of a company, it was very simple, email a particular email address and the system would respond with the current month to date performance figures. Analysis of the usage found that instead of being triggered daily, nearly 90% of the triggers were fired on the last day of the month. Senior staff were looking to see if targets would be made, rather than supporting teams across the month to meet targets. Analysis had stopped being a benefit.
Consider your calendar table as the most important table in your organisation, your time table should never be less than a 15 minute granularity but, consider the specific time questions that you will be asked to provide reports at. Let us hear your perspectives on calendars and time.
#geordielife #dataliteracy #calendaandclockfunctions #powerbi
