1

We have a fact table that links to a customer dimension using the customer id field. It's working fine and the product owner is happy with it too. As part of the customer attributes we have the [SIC Code 2007][1]

[1]: https://onsdigital.github.io/dp-classification-tools/standard-industrial-classification/ONS_SIC_hierarchy_view.html and the ISO country code.

Now we have in the backlog a new user story to classify the customers based on a combination of those 2 fields. It's an straightforward table maintained by the user that for example says if SIC is 01.12 and Country FR then classify the customer as "Farming EU".

On a 3FN world we would join the customer dimension with the classification table and done. On a dimensional model we have studied 3 options:

  1. A new table and this means moving to a snowflake schema.
  2. Add the classification field to the customer dimension table. This means that if the user changes the classification ( Farming EU to Farming France for example ) we have to reprocess the dimension table.
  3. Create the classification as a dimension and move the SIC and country code to the fact table ( or a surrogate key combining them ). This gives the user great flexibility to modify those combinations.

What do you think it is a best approach considering that the product is now on a releasable state but not in production yet?

Oscar Garcia
  • 49
  • 1
  • 5
  • 2
    Personally I would choose option 3, but consider what other additional categorisations you can put in here besides SIC – Nick.Mc Dec 16 '17 at 14:21
  • Thanks, we are still on the feedback loop phase so we may be changing this feature in the future. – Oscar Garcia Dec 16 '17 at 19:42

0 Answers0