Ciò che viene descritto dall'OP è uno scenario di "canalizzazione di vendita" e desideri monitorare il movimento di (qualcosa che non hai spiegato chiaramente) attraverso questa canalizzazione. Ad esempio, "qualcosa" potrebbe essere "una vendita immobiliare" o "assumere un candidato". Il punto è che "qualcosa" è un processo e questo processo è ciò che stai descrivendo nella tua domanda.

Non parli di cosa sia l'"entità" nel tuo scenario e non è chiaro se l'entità sia:

• "prospetto / cliente" (l'acquirente / i oi candidati / i di lavoro) - le persone / aziende prese di mira dal team di vendita
• "prodotto / servizio" (il settore immobiliare o il ruolo / i di lavoro) - ciò che il "team di vendita" sta vendendo al "potenziale cliente"
• "team di vendita" (il / i realtor / i o l'intervistatore / i) - le persone della società che lavorerebbero per spostare il "potenziale cliente" attraverso la "canalizzazione"

Indipendentemente, ognuna di queste dimensioni è relativa al processo di canalizzazione delle vendite.

Stai pensando che sia sbagliato in termini di "stato". Quello che stai veramente monitorando è il movimento di un "prospect" attraverso fasi / passaggi a "imbuto di vendita". La canalizzazione di vendita è un processo di passaggi (i tuoi "stati") e un "potenziale cliente" passa attraverso uno o più di questi passaggi.

È possibile modellare ogni passaggio nella canalizzazione in quanto è FATTO personale e si può avere un FUNNEL_TRANSACTIONS_FCT potenzialmente per collegarli, ma ciò non soddisfa i requisiti di una serie dinamica di passaggi (stati, come li chiami).

Un potenziale cliente passa a un passaggio della canalizzazione diverso quando si verifica una "azione" (transazione), ad es. preventivo richiesto, preventivo fornito, preventivo accettato, ordine effettuato, contratto firmato, ordine cancellato, ecc. Quindi, ogni "stato" (come lo chiami) è un tipo di transazione (che sarebbe una dimensione di per sé) a quali nuovi "stati" possono essere aggiunti, e le modifiche possono essere apportate, ecc.).

Da qui è chiaro che le tue righe della tabella FACT rappresentano queste "azioni / transazioni", il cui risultato atterra in un certo punto della prospettiva in un determinato momento.

La soluzione più pulita sarebbe ovviamente quella di tenere traccia degli stati per ogni singolo attributo individualmente, come con il tuo primo approccio.

Non è così male come lo fai tu, dato che ricostruire uno stato passato è semplice come semplicemente ignorare tutte le nuove modifiche di stato introdotte più tardi, quindi solo un semplice WHERE date < ... GROUP BY attribute.order_id ORDER BY date DESC , e poi eseguire una query esterna su quel passato ricostruito snapshot.

Dove non sono d'accordo con te sta usando e la data di "fine". Utilizza invece un riferimento di follow-up, in modo che tu possa facilmente filtrare per transizioni di stato specifiche, che ti forniscano il log delle azioni tramite una percentuale JOIN , così come lo stato corrente controllando NULL .

Dovresti valutare se vuoi effettivamente un modello a stella, o solo uno schema monolitico di state , che esprima l'intero stato di un'entità in un dato momento. Questo è un compromesso lineare tra lo spazio di archiviazione e il tempo di query.

Probabilmente dovresti registrare sia lo stato, sia l'azione che ha causato la modifica dello stato in un singolo record. Nel caso in cui ci siano azioni che non possono essere espresse come una specifica possibilità di stato, come il filtraggio da parte di utenti specifici e allo stesso modo.

I assume that the time-efficiency with this solution could be non optimal for reading. Although I thought, that data warehouses aim for trading write-efficiency for read-efficiency.

Per prima cosa una domanda facile, il data warehouse dovrebbe essere progettato per essere letto in modo efficiente perché implica molte aggregazioni. D'altra parte, l'OLTP dovrebbe essere in grado di gestire molte transazioni, il che significa inserire operazioni.

Per la domanda principale, il tuo primo approccio sarebbe quello giusto. Penso che i fatti che vuoi memorizzare qui siano Sales, non l'entità stessa o qualsiasi azione sull'entità. Un'azione dovrebbe impostare lo stato sull'entità (qualunque sia l'entità).

La tabella delle vendite, può o non può essere divisa in due, cioè puoi avere una tabella con entità e ogni stato che è stato impostato su di esse, oppure puoi dividerla in Sales e SalesStatusHistory che tengono FK in Vendite, stato e il timestamp per lo stato.

Nota a margine, se si usano le tabelle Sales e SalesStatusHistory non si intende che i due siano nello schema a stella perché entrambi sono fatti e bisogna usarli entrambi insieme per ottenere il fatto, o in un altro modo, o uno non si adatta come una dimensione.

Dopo aver trasformato l'entità e l'azione per diventare Vendite con cronologia dello stato, l'interrogazione significherebbe solo (il più delle volte) trovare tutte le vendite con lo ultimo stato in un intervallo di tempo e filtrare per alcuni stati.

Esempio:

Il mio database delle transazioni crea un oggetto quando qualcuno ne ordina uno. L'articolo avrà lo stato in base al ciclo di vita delle vendite (creato, in sospeso, acquistato, pagato e annullato). La tabella delle vendite sarebbe simile a questa:

+--------+-----------+---------------------+
| item   | status    | timestamp           |
+--------+-----------+---------------------+
| Item 1 | Created   | 2016-12-19 03:32:04 |
| Item 1 | Purchased | 2016-12-19 03:40:04 |
| Item 1 | Cancelled | 2016-12-19 04:40:04 |
+--------+-----------+---------------------+

L'ultimo stato dell'articolo 1 è annullato; tutti gli acquisti fino alle 4 del mattino includeranno l'articolo 1.