Hi community,
I have a process which is stored in this way right now:
| Client | StartDate Step 1 | StopDate Step 1 | Indicator Step 1 reached | StartDate Step 2 | StopDate Step 2 | Indicator Step 2 reached | StartDate Step 3 | StopDate Step 3 | Indicator Step 3 reached | StartDate Step 4 | StopDate Step 4 | Indicator Step 4 reached |
| 100 | 2019-01-06 | 2019-01-08 | 1 | 2019-01-12 | 2019-01-15 | 1 | NULL | NULL | NULL | 2019-02-01 | 2019-04-02 | 1 |
| 120 | 2019-01-01 | 2019-01-06 | 1 | 2019-01-09 | 2019-01-12 | 1 | 2019-02-01 | 2019-04-02 | 1 | NULL | NULL | NULL |
As you can see some steps can be empty. My basic question is: how to model this? In the above way or going this way:
| Client | Stepname | Startdate | Stopdate | Indicator reached |
| Client | Step 1 | 2019-01-01 | .... | 1 |
| ...... | ...... | ...... | ...... | ...... |
And so on....
Or even this way came to my mind:
| Client | Date | Step |
| 100 | 2019-01-01 | 1 |
| 100 | 2019-01-02 | 1 |
| 100 | .... | .... |
To make it a little more complex; there is a second fact-table; this table has information about campaigns that have can be active and can have a complete different period:
| ClientID | Start | Stop | Product category |
| 100 | 2018-12-16 | 2019-02-12 | 89 |
| 100 | 2019-02-13 | 2019-03-12 | 88 |
| 120 | 2019-02-01 | 2019-02-22 | 73 |
And those facts must be presented in a combination also...
I will be able to prepare the data in every shape I'll need (can do it in SQL by example).... Can some people give me an advice about how to handle this. I've been working as BI-developer for many years but this is a hard case....
