Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
How to calculate a person's age "as of" a reporting date
Hi everyone, I'm stuck on a problem in OBIEE that's (easily) been stumping me for 10 years, and I still don't have a decent solution. High level, I have a fact table that joins with a "date" dimension and a person dimension (along with other dims that don't play a part in this). The person dimension stores the person's birth date.
Assuming a person will turn 18 years old on Jan 15th, I need to produce a report that looks like the following:
Reporting Date Age in Years
1/13/2016 17
1/14/2016 17
1/15/2016 18
1/16/2016 18
...
Here's what I've tried so far:
1. OBIEE function TIMESTAMPDIFF(SQL_TSI_YEAR, birth_date, reporting_date) : this returns inaccurate ages. It shows the person turning 18 way before they hit their birthdate
2. OBIEE function TIMESTAMPDIFF using any of the other SQL_TSI intervals - haven't found satisfactory solution with this that works properly for leap years, etc.
3. EVALUATE function - I tried setting up an AGE column on the employee dimension, and then using an EVALUATE function to calculate the age using database functions I know work ("trunc(months_between(birth_date, reporting_date) / 12)". This might work ok for metrics, but I couldn't get this to work properly on dimensions. OBIEE doesn't set the query to "group by age" so I get invalid SQL if for example I try to show counts of employees by age.
I'm pretty out of ideas...I've been fighting with this issue for a decade at this point but haven't found a good solution. Does anyone have any other ideas?
Thanks,
Scott
Answers
-
Edit: never mind timestamdiuff rounds that way...
StackOverflow has an answer: sql server - How to calculate age (in years) based on Date of Birth and getDate() - Stack Overflow
0 -
Use of information drives physical design ... why not use the database (and functions you know work) to update the age attribute column in the dimension nightly in your post-load etl processing?
0 -
I assume he meant his reporting date (point in time) is something you can choose from a prompt so calculating it physically won't work.
0 -
"I try to show counts of employees by age" + "haven't found satisfactory solution with this that works properly for leap years, etc."
those statements alone beg a properly formed physical structure. The simple approach is a snapshot fact that stores either the detailed employee & age at a snapshot date or it stores the aggregate (pre-binned) counts or both structures are built.
The benefit of a snapshot is the ability to compare those binned counts over time
at 1/1/2015 30% are under 20 yrs old
at 1/1/2016 24% are under 20 yrs old
that comparison fetches a few rows and does the lite-logical calculation to find the % Change and Variance.
0 -
Until we know what his needs are in detail - could be the one could be the other could be something completely different yet again with - for example - the calculation being executed on request variables passing values into the RPD
0 -
Hi all, just to follow up on some details in my original post that seemed to be too vague:
1. On our database, I can perfectly calculate the age as trunc(months_between(birth_date, reporting_date) / 12). I simply want OBIEE to be able to call this same function
2. Users can select any valid date in the "reporting date" dimension to report against. This dimension is at the day level and has 10+ years of history.
3. When a user selects a reporting date, the report needs to show the age of the employees "as of" that reporting date
Given #2 and #3 above, I can't really store ages. We've got 60,000+ employees - trying to update a SCD type 2 dimension or something with this nightly would make our employee dimension blow up to hundreds of millions of rows. Thus my desire to have OBIEE call the calculation from the database using the EVALUATE function.
Having said this, I've defined AGE on my employee dimension as:
Evaluate('trunc(months_between(%1, %2) / 12)' as integer, "Scott Test"."Dim Date"."Reporting Date", "Scott Test"."Dim Employee Detail"."Birth Date" )
0 -
Hi all, just to follow up on some details in my original post that seemed to be too vague:
1. On our database, I can perfectly calculate the age as trunc(months_between(reporting_date, birth_date) / 12). I simply want OBIEE to be able to call this same function
2. Users can select any valid date in the "reporting date" dimension to report against. This dimension is at the day level and has 10+ years of history.
3. When a user selects a reporting date, the report needs to show the age of the employees "as of" that reporting date
Given #2 and #3 above, I can't really store ages. We've got 60,000+ employees - trying to update a SCD type 2 dimension or something with this nightly would make our employee dimension blow up to hundreds of millions of rows. Thus my desire to have OBIEE call the calculation from the database using the EVALUATE function. (p.s. also, I've greatly simplified our issue....besides "age in years" we also need decimal age, plus there are 6 or 8 other dates we track elapsed time against that all share this same issue).
Having said this, I defined an AGE attribute on my employee dimension as:
Evaluate('trunc(months_between(%1, %2) / 12)' as integer, "Scott Test"."Dim Date"."Reporting Date", "Scott Test"."Dim Employee Detail"."Birth Date" )
When I tested this the other day when I wrote the post, this was not working properly. Today it magically does seem to be ok. Unsure if this is due to caching (which I turned off) or something else...I'll continue to play with this and see if it works ok in all cases.
Thanks!
Scott
0 -
p.s. the SQL being produced using the evaluate is correct:
SELECT SUM(T940405.ANNUAL_PAY_RATE) AS c1,
T939141.DIM_DATE_KEY AS c2,
TRUNC(months_between(T939141.DIM_DATE_KEY, T938790.BIRTH_DATE) / 12) AS c3
FROM DIM_EMPLOYEE_DETAIL T938790,
DIM_DATE T939141,
FCT_JOB_JED T940405 /* FCT_JOB_Day_level */
WHERE ( T938790.EMPLOYEE_DETAIL_KEY = T940405.EMPLOYEE_DETAIL_KEY
AND T939141.DIM_DATE_KEY = DATE '2016-01-01'
AND T939141.DIM_DATE_KEY BETWEEN T940405.EFF_START_DATE AND T940405.EFF_END_DATE )
GROUP BY T939141.DIM_DATE_KEY, TRUNC(months_between(T939141.DIM_DATE_KEY, T938790.BIRTH_DATE) / 12)
When I tried this the other day, the SQL coming out was not including the "Trunc(months_between..." part in the group by clause, causing a SQL error. Today this is working, again unsure why. Adding in additional dimensions now to see if that breaks it somehow.
Thx,
Scott
0