Suspecting issues with the date dimension in Request Metrics
Hi, I'm doing a report which the first part of that report is as simple as show total amount of open defects in a project over time.
So I tried using the Request Metrics business view. Now I simply created a chart putting the the fact measure "Total Requests" on the Y-axis and the "Date" dimension on the X-axis. I Also created a filter which filters on a specific project.
Now I just wanted to test this to see what the output would be. I was expecting to see the Total amount of defects per day (Since we ETL every night)
But I just got all the Requests in one bar with the date 1899-12-31. Now my suspicion is that something is wrong either in the FM model or these metrics are wrong.
I tried many more ways to make this work after this without any luck. Anyone knows what the problem could be?
So I tried using the Request Metrics business view. Now I simply created a chart putting the the fact measure "Total Requests" on the Y-axis and the "Date" dimension on the X-axis. I Also created a filter which filters on a specific project.
Now I just wanted to test this to see what the output would be. I was expecting to see the Total amount of defects per day (Since we ETL every night)
But I just got all the Requests in one bar with the date 1899-12-31. Now my suspicion is that something is wrong either in the FM model or these metrics are wrong.
I tried many more ways to make this work after this without any luck. Anyone knows what the problem could be?
Accepted answer
the problem is in ETL, when you run it daily , it suppose to get current date, do a lookup to get date_id and put into date_id column of the request metrics table, but looks it fails to get a correct date_id, then -1 is inserted into date_id, ended up at 1899-12-31.
We have a defect about this on Oracle: The fact builds puts the default date of 1899-01-01 as the ETL date (77670)
We have a defect about this on Oracle: The fact builds puts the default date of 1899-01-01 as the ETL date (77670)
Comments
Ok, is there a way for me to verify that this actually is the problem?
Also if this is true does this mean that I can't build any type of trend of the ETL'd data we have captured since start?
test the function mentioned in comment2 of the defect, and fix it by following it.
ok, maybe a stupid question but can I find these functions from datamanager and if yes which fact_build in the RTC OOTB ETL should I look at?
Ok, I found the functions. Now before I test this I would need to know:
For function GetDefaultEndDateID() and SELECT_ETL_DATE. If I change the code to match what you have put into comment2:
1. What implications could it have?
2. Will the fix on the date granularity only apply to dates collected after this change or will it apply to everything we have collected previosly as well meaning that I could now use the time dimension properly for older data?
it won't fix old data.
no implications, that's a fix for a defect.