Dimension table or Fact?

I am remodelling my data mart for my property platform. I can consider the former efforts fitting a specific use case but the world has moved on, with my knowledge increased I need a system capable of facilitating multiple data sources. So starting upon reviewing the data model for which I use the excellent and free Oracle Data Modeler for modelling data (yes, they only use one 'l').

I have some pretty powerful measures to add into the platform in due course which will be game changers for buyers and sellers trying to make sense of the market.

When starting to remodel the data, the question of Dimension or Fact springs to mind. Sounds simple doesn't it, are we dealing with a Fact - i.e. something that can be measured and aggregated or something that can categorise or slice a measure. Then what happens when we put data into a dimension table only to realise it is measurable?

My view is we need to sometimes consider having data in both dimension and fact tables and educate the user as to the differences between when they are using a specific instance.

The solution is far from being a data warehouse only as I often establish within the enterprise for clients but this gives freedom to define a model that can act for the appropriate use rather than having to build one path only for one outcome with no budge.

Add comment