I am designing a data warehouse for a sales platform. There exists a group of users that offers and a group of users that demand entities.
The production data base holds two sources of information:
- The current status of each entity and to which users it is assigned (this can be versatile, "placed a price for an order", "bought", "offered"). This can thus involve multiple users depending on the status.
- An action log
Accordingly, there are two types of questions that the data warehouse can be asked. E.g.:
- How many offers have been without an order yesterday at 9 am? (Status)
- How many entities have been sold yesterday? (Action)
I aim for a simple star-schema where dimensions could be time, place, users, etc. But the question is: What would be the fact?
First approach
My first idea was to introduce a fact table for every status. Then every fact would need a starting and end validity and as soon as the action log says, that a status changed, I need to get back to the fact table where the current status of the entity is stored and add the end-validity time stamp and afterwards add the new fact in the table of the new status of the entity (with a missing end-validity time stamp).
With this approach, it is quite simple to answer question 1 (Status) but impossible to answer question 2 (Action). Also, It seems non-trivial to update the data warehouse on each action.
Second approach
An alternative idea was to simply store the action log in a star-schematized way. Every type of action becomes a fact. (Of course, the question 2 (Action) is most simple to answer but what about question 1 (Status)?
My Idea was to create date-parameterized views for each status: For Example, to get all offers without an order yesterday at 9 am, I would have to get all actions that say "offered something" from before yesterday at 9am and I would have to subtract all offers that
- an order has been placed on
- that have been canceled until yesterday 9 am
I assume that the time-efficiency with this solution could be nonoptimal for reading. Although I thought, that data warehouses aim for trading write-efficiency for read-efficiency.
So my question again: How to design the fact-table to be able to answer both above questions with the minimum of complexity?