Forum Discussion
Data modeling - customer seniority
Hello!
I need an ideea.
I have a tabular model made up of tables of the dimensions and facts type. Customers find themselves in a slowly changing dimension.
I need to add the customer's seniority. How could I model this?
A customer can be active in different periods.
At this moment I have a table that contains the client, the account opening date and the account closing date:
| CUSTOMER_ID | OPEN_DATE | CLOSE_DATE |
| 123456 | 1/1/2022 | 1/6/2022 |
| 123456 | 1/12/2022 | NULL |
The client dimension looks like this:
| DIM_CUSTOMER_ID | CUSTOMER_ID | CUSTOMER_NAME | DATE_FROM | DATE_TO |
| 100 | 123456 | ANA | 1/1/2022 | NULL |
where date_from and date_to represent from when until when the record from DIM_CUSTOMERS is valid.
HOW CAN I MODEL THE DATA SO THAT I CAN BUILD A FACT TYPE TABLE WHERE I CAN FIND HOW MANY MONTHS OLD THE CLIENT IS ACTIVE ?
1 Reply
How do you define active? Open Date past Close Date, or do you have an actual transaction date of sorts?
