Analytics Archives - Narwhal Data Solutions

Notes on dimensional modelling

In the other post on Coronavirus reporting we touched briefly the topic of dimensions. In particular we discussed about the temporal dimension. Since dimensional modelling is a basic concept in the data warehouse design it deserves a separate post.

The temporal dimension is commonly used in practically every business and science domain. Time / date is commonly considered as an example of a conformed dimension. In the end the 7th of November will always be the 7th of November and not the 25th of October, irrespectively of the context [1]. In reality, we typically deal with multiple different time/date dimensions – date of order is generally different that date of delivery and different than date of payment. An attempt to combine these 3 facts in one drill-across report using a single ‘date’ dimension will inevitably produce nonsense. These are 3 different, conformed dimensions. In practice it is incredibly important to clearly distinguish different kinds of dates or timestamps.

Know your process

In some cases this distinction is relatively easy, as the difference comes from the business process. In the example above it easily understandable for everyone, that the delivery cannot occur before the order and there may be a remarkable delay between these two events. It is obvious, the delivery date and the order date are two different things. This is an inherent feature of a business process we are describing using analytical tools.

In other cases – and they are often more difficult to handle – the difference comes from how we measure certain quantities or report on events. In the previously described Coronavirus example we were really interested to know the date of infection. But this was not an event we could monitor or measure directly. The event we could easily monitor was the submission of the report of the identified case. There is a significant delay between the two events. Moreover, this delay is not constant and can vary from case to case. It depends heavily on the details of the whole diagnostics and reporting procedure. These details and their influence on the results should be evident for an insider, but very likely will be unknown to the final user of the analysis prepared on the base of the collected data.

How to buy on-line without a single website visit

Let’s consider a process of making a purchase in an on-line shop. Typically, a user visits the website, add some products to the basket, initiates check-out, finalises it and pays. Of course, there may be many different scenarios, but for sake of simplicity let’s restrict our consideration to these five steps. In the ideal world we can register and timestamp the events corresponding to each step. Very likely each of the steps will be handled by a different (micro-)service or even by an external provider. The data about the website visits would be collected and stored differently than the data about payments. And, of course, one might report on the number of website visits per day and, independently, on the number of transactions per day.

It is not difficult to figure out that it takes some time from the website visit to the payment. Hence, the timestamp of the visit event will never be the same as the timestamp of the payment event. It does not take much to realise that a user can browse the site for many hours, then add some products to basket, then leave this basket for another couple of hours or even days, and only then continue with checkout.

Obviously, if we try to combine our facts about visits and facts about transactions in one report using “date” – supposedly a conformed dimension – we will obtain a mess. This mess will probably not be immediately visible on a very high level. But on a specific dimension splits we might see a theoretically impossible situation of transactions without a visit at some dates.

Clearly, there is no single “date” dimension. There are several: visit date, transaction date, e.t.c. We may report the numbers by visit date or by transaction date, but we cannot use them interchangeably and should not mix them together. As a logical consequence, we should clearly distinguish the different types of dates and timestamps by using clear and consistent naming in the data warehouse. But naming is a topic by itself and one day we may cover it separately.

What about other dimensions?

While time and date are important, they are not the only dimension typically used. The other common example of a dimension is “customer”. Every business has customers. But “a customer” may have a different meaning depending who we speak to. In a CRM system one would often store both actual customers and prospects customers. If you talk to a sales representative or an account manager he would use the word “customer” to name both. For finance department “a customer” very likely will be someone they can issue an invoice (or have issued one in the past). For legal “a customer” might be someone we have a valid contract with – so no prospects, and no former customers. Some other department might even define it as “someone, who has bought from us in the last 3 months”.

However, in this case we can define a superset of all “customers”. This superset will contain all the prospects, actual customers, former customers, e.t.c. If we manage to collect all the relevant attributes for such a customer dimension and we have a single primary key to identify an entry – we have a conformed dimension. Despite the fact we use “customer” in many different contexts, we have a single conformed dimension, which we can use to create coherent drill-across reports.

The problem with “customer” is not that it can mean different things, the problem is the data about customers are present in many different systems. In general, different departments, and consequently the systems they use, may need various subsets of data. The data of a particular system may be incomplete, outdated of simply false. Some of the data in system A may contradict the data in system B. Thus, apart from having a single data warehouse definition of what a customer is, we also need a single source of truth about its attributes. But this is a separate topic of Master Data Management.

[1] Unless one makes a conversion from Gregorian to Julian calendar.

Uncategorised

On Coronavirus Analytics and Reporting

The evolution of the COVID-19 coronavirus epidemic triggered an enormous interest in crunching the available data. There are plenty of data on this topic readily available, which makes this task to appear particularly easy.

Both official institutions and individuals have created a number of various visualisations, of various quality and various practical usefulness. I must admit that after having a look at a couple of them I was quite disappointed. Most of them, while often visually appealing, has significant shortcomings. We will not discuss the details here; those interested in the topic can find some critical thoughts here. The most important take is that the most of these visualisations do not really facilitate any decision making process. Even if they present trustworthy data they often do it the wrong way. Typically, they present answer on “what has already happened?” or “how does it looks like now?” – which is not yet bad as such. At the same time they make it difficult to find out “how the situation has been changing?”, not to mention the most important aspect: “what will / can happen in the future?“.

Of course, many attempts has been made to answer the latter question. The availability of the various data made many amateurs, both when it comes to epidemiology and handling data, to build and publish their “models”. This activity was particularly visible at the early stages of the epidemic. The “models” were very often not much more than an exponential regression based on few available data points. Very simple, there is nothing which could go wrong, right? Apparently, many people done seemingly the same thing, on ostensibly the same data and they… got totally different results. The only common thing was that the curve was rising quickly. But this was visible even without a “model”. We will mercifully not point to any examples. In fact, the most outrageous ones does not seem to be easily available in the Internet any more.

We will not try here to show another visualisation – there is enough of them. Neither we will attempt to build another predictive model of the course of the epidemic. While we are experts in data, we definitely prefer to leave disease spread modelling to professionals in epidemiology. Instead, we start with something very simple: the basic facts and metrics. Why? Because they are the cornerstone of every business analytics, not only the coronavirus ones.

Facts, dimensions and metrics

In case of coronavirus disease the atomic event is an infection. In the other words infections are our facts. Now, what are the most basic metrics on that facts? Of course, it is the number of infections and we will focus on that one. Each of the infections occurs at some point in time, at some geographical location and to a person of a certain characteristics (age, gender, profession, …). These are the potential dimensions. The important one here, and the most common in nearly every business analysis, is time. That is why we take that one as an example.

Immediately we can conceive the first metric: the total number of infections or more precisely the cumulative number of infections to date. This is the metric the most frequently shown in all tables and visualisations. Typically, the ‘date’ being ‘today’. But if have a closer look at this metric we would noticed that this is similar to the total number of a website visits, or the total number of customers or the total amount of sales (to date). These are all vanity metrics. They all will always grow, by definition. At the same time they carry very little useful information on how are we really doing.

Thus, we are not that much interested in the cumulative number of infections to date. We would rather look at the number of (new) infections per day. The business equivalents would be, for example, the number of website visits per day or the daily amount of sales. From this metric we will be able to easily see what is the trend (increasing, stable, decreasing) or if Monday is always the worst day of the week. This one is useful.

So, let’s have a look at how this looks like on a chart. As said previously the data are easily available, so are the charts. One of the most popular services showing curated data and some charts is Worldometer. Since we are located in Berlin let’s have a look at the data concerning Germany. The official source of German data is Robert Koch Institute. There are also a lot of other information and scientific studies on their website, so if you understand German it is well worth reading.

The Worldometer’s Germany Daily New Cases is updated daily and there is no history provided. At the time of writing it looked like that:

Data visualisation: Coronavirus daily new cases in Germany as of 2020-04-28, based on the original chart by Worldometer. X-axis adjusted to show data after 2020-03-01. — Coronavirus daily new cases in Germany as of 2020-04-28, by Worldometer. X-axis adjusted to show data after 2020-03-01.

The respective RKI charts are given in Daily Situation Reports (updated daily, archive available). The one from the time of writing looked like that:

Report of Coronavirus cases in Germany per day as of 2020-04-28 reported by Robert Koch Institute. — Coronavirus daily new cases in Germany as of 2020-04-28, by RKI.

Not all dates are equal

It does not take much time to realise they are substantially different. It is not just a matter of different scale on Y axis and different graphical representation. They looks like they were reporting on completely different data. But this is not the case – Worldometer takes the official data from RKI. Beyond any doubt, the source data are the same.

In both cases we use seemingly the same metric: the number of (new) infections aggregated and charted against seemingly the same dimension: date. If we have a closer look we realise that for Worldometer “date” means “date of reporting”. At the same time RKI uses “date of onset of symptoms” (if known) or “date of reporting” (if date of onset of symptoms is not known). In fact, we have here two distinctly different dimensions, disguised under the same name: “date”. This simple observation is of crucial importance and we will discuss it in a separate post.

To be exact the values on the RKI chart are plotted against two different dimensions, which are marked on the same X-axis. This is a very rare thing to do and normally we would discourage such attempts. However, in this case such a presentation serves a purpose.

Understand the process

To understand why it is important we first need to understand the measurement and reporting process. In the ideal world we would like to report on the date of infection. Obviously, in reality we do not know this date. In science, if we cannot measure or calculate a certain value directly we can try to estimate it. So, let’s think what would be the best estimate of it.

The sequence of events in the whole infection and diagnose process (largely simplified for the purpose of this post) may be as follows: one gets infected, after a few days develops symptoms, the samples are taken, then they are delivered to the lab, processed in the lab, the results are obtained, the result are reported through the official channels. As we understand, in general case the symptoms may be developed on any stage of the sequence, theoretically even after the case is officially reported, or not at all. Those interested in details may read more on RKI website.

What is important for our further consideration is that there are multiple steps and consequently multiple potential timestamps to be used. The delays between the steps are expressed in hours or days, are variable and are significant from the perspective of the dynamics of the process we are trying to depict. In the other words, the case reported today may have been infected 3 days ago or 10 days go. If we look again at the simplified sequence of events above it should be clear these variable delays come mostly from the procedure of diagnosing and reporting. When we realise that fact we can try to explain the sudden spikes and the weekly variations on the Worldometer’s chart.

As we can see the date of reporting is the very last date in the whole process. It is also very different than the date of infection we are looking for. The closest to it is the date of the onset of symptoms. Logically, RKI takes that one as the best estimate of the former. As the result we get the smoother chart, without the spikes or drops caused by irregularities in testing and reporting process. It much better depicts the process we are interested in. The problem is in many cases the date of onset of symptoms is not known, or cannot be given. This cases cannot be ignored or omitted, hence they are charted by the only certain and available date – the date of reporting. As mentioned above we are sceptical about the idea of mixing two distinct dimensions on the same report and we would normally discourage such practice in any business context. At the same time we clearly see the advantage of using the best estimate whenever possible.

Changing history

The way of charting taken by RKI has one more important feature: it can change retrospectively. One of the cases reported today might have the onset of symptoms 8 days ago, in the other one the symptoms might (in theory, at least) appear in 2 days from now. In both cases we would need to update the whole chart, not just the most recent date.

RKI Coronavirus daily new cases report change over week 2020-04-21 - 2020-04-28. Visible retrospective changes. — RKI Coronavirus daily new cases report change over week 2020-04-21 – 2020-04-28.

As the illustration here is how the RKI report has changed over 1 week, from 2020-04-21 to 2020-04-28. Retrospective changes in the recent days are remarkable and the adjustment of the numbers going back as far as few weeks can also be noticed.

We have seen it already

Such things are not unusual, it can happen in many applications that the events are reported with a significant delay and not necessarily in order they occur. Many modern data processing systems have built-in features which allow to handle such late arrivals easily. However, the consequence is that the report for the last month done today will be substantially different than the same report done in a week time from now. Many of business stakeholders find this uneasy to deal with such a volatility in their reporting. This often leads to picking a simple and stable “Worldometer-like” reports over a more adequate, but also more elaborate and volatile “RKI-like” ones. Even if the latter ones provide a better base for a data driven decision making.

As shown above, even in a relatively simple data analysis there are multiple decisions to be taken. Depending on how they are taken and how the whole data collection and analysis process conducted the reports of (seemingly) the same metrics, based on exactly same data may differ significantly. Clear definition of the relevant metrics and dimensions is the starting point for any business reporting. If done right – it can greatly facilitate further decision making process. If neglected or done carelessly – it will lead to discrepancies, misunderstandings and drawing false conclusions.