Code Snippet
Code Snippet
What is the best way to calculate a customer's age?
I have a customer table which acts as both a dimension and a fact table and a corresponding Activity Fact that shows what kind of activity type a customer associated with.
How do I calculate the following?
1. Customer Current Age based on DOB?
This one has to be calculated on the fly based on today's date.
I am thinking about adding a link from DOB to the time dimension
so DOB is a dimension of the dimension dimCustomer.
Or it can be a dimension of the Fact dimCustomer.
2. Customer Age at the time of Enrollment ?
For this one, I created a new calculated column in the DimCustomer table to show AgeAsOfEnrollmentDate by using
Case ISNULL(DateOfBirth,'1900-01-01')
WHEN '1900-01-01' THEN NULL
ELSE DATEDIFF(Month,DateOfBirth,Enrolldate)/12
End AS AgeAsOfEnrollmentDate
3. Customer Membership Age?
How do I calculate How long has the customer been a member based on the EnrollDate?
So for this, I need something calculated on the fly like
DATEDIFF(Month,Enrolldate, Current Day)/12
Code Snippet
DimCustomer
CustomerID int
AccountNo int
DateOfBirth datetime (example--> 19700531)
EnrollDate datetime (example --> 20060731)
Fact_Activity
ActivityID int
ActivityNo int
ActivityDate datetime (example --> 20070201)
CustomerID int
ActivityTypeID
DimActivityType
ActivityTypeID
ActivityType
ActivityTypeDescription
DimTime (is a role-playing dimension for EnrollDate and ActivityDate)
I am thinking about adding also for DateOfBirth.
This has a hierarchy CALENDAR
Calendar Year
Calendar Quarter
Month Of Year
FullDateKey
Hello Binh,
One way you could calculate the customer's age on the fly based on DOB is to create a new named calculation in the data source view of your cube, inside the customer table (similarly to what you did for point 2). In the expression field, you would need to use a T-SQL expression that returns the age of the customer with the desired accuracy. For instance, something like "datediff(year, [DateOfBirth], getdate())" or “datediff(month, [DateOfBirth], getdate())/12.
Hope this helps,
Artur
|||Hi Artur,
Yes, I know I can do this for both the Customer Current Age and the Enrollment Age,
but the problem is that these values need to be dynamic and if I put it in the data source view like you suggested,
I would have to reprocess the cube everyday for the age to reflect right?
If I somehow can calculate it in the calculation script, I don't have to reprocess the cube to see the new number right?
