Data modeling - customer seniority

[ANM_97]

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 ?

[lbendlin]

How do you define active?  Open Date past Close Date, or do you have an actual transaction date of sorts?