Dimensional Data Modeling - A boolean is not a numerical measure but a descriptif attribute

About

A boolean in dimensional data modeling is not a numerical measure but a discrete attribute

A boolean in this article can be represented by:

  • a real boolean (true/false)
  • 0/1
  • Indicator (Y/N)

Really often the boolean are represented by the number 0/1 and are included in a fact table as measure

It's a bad design as it is not in essence a quantitative variable but a category of the representation of a status.

Because

nothing is binary

You may meet them to represent a two states of a condition such as True/False. It implies the use of one threshold: above it's true and below it's false (of vice-versa).

And as you know, as the business changes this condition can also changes and you may get the need of a multiple status such as :

  • Really True/Minder True/False
  • Red/Orange/Green
  • Ideal/Good/Acceptable/Warning/Critical

It's no more representative with 0 of 1 and you have to change the structure of your fact and to add them in a dimension.

you need to filter on it

When you need to filter on something, it's by essence an descriptif attribute

In order to analyse only one group (the True or False Group), you have to pass the value to a filter. On a detail level of your fact table, it's not a problem.

But when you begin to aggregate it to gain in performance you will loose this information and are completely unable to for instance analyse the False or Bad rows.


Powered by ComboStrap