Hi Folks,
How can I model Slowly Changing Dimensions - Type 2 using the Ontology?
Context - a happy path approach to creating a time series plot based on Ontology objects
For a toy example, suppose I want a plot Tesco ‘Sales per square metre’ over time.
I have a Store table (one store per row) that tells me the store footprint, and a Transactions table (one transaction per row) that tells me sales over time:
(In dimensional modelling, the Store table is a dimension table. The Transactions table is a fact table.)
In the Ontology, I can:
- model each table as an Ontology type ‘Store’ and ‘Transaction’ and create objects for each row
- link the Object types using store_name.
…with a bit more work, I can imaging how I’d go from here to creating a time-series plot of: ‘Sales per square metre by month’ in Workshop.
My question is: how would we update the above approach if Tesco Cardiff doubled it’s footprint in 2010? In dimensional modelling, you’d convert the Store table into a Slowly Changing Dimension - Type 2 table:
…Then you’d join Store to Transactions using (store_name, from_date__inclusive) and (store_name, timestamp) to get the right size__sq_m value over time.
What’s the pattern for addressing SCD2 using Ontology?
Any help or links appreciated!