Suspecting issues with the date dimension in Request Metrics

Amir Ghaemian (48●1●13●15) | asked Mar 08 '13, 8:10 a.m.

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?

Accepted answer

permanent link

Junli Wang (551●2) | answered Mar 08 '13, 8:28 a.m.
JAZZ DEVELOPER

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)

Amir Ghaemian selected this answer as the correct answer

Comments

Amir Ghaemian commented Mar 08 '13, 8:41 a.m.

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?

Junli Wang commented Mar 08 '13, 8:44 a.m.

JAZZ DEVELOPER

test the function mentioned in comment2 of the defect, and fix it by following it.

Amir Ghaemian commented Mar 08 '13, 9:02 a.m.

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?

Amir Ghaemian commented Mar 08 '13, 9:23 a.m.

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?

Junli Wang commented Mar 08 '13, 9:27 a.m.

JAZZ DEVELOPER

it won't fix old data.
no implications, that's a fix for a defect.

One other answer

Most liked answers ↑|Newest answers|Oldest answers

0	permanent link Walter Urrejola (23●1●7●11) \| answered Mar 08 '13, 9:22 a.m. When you set up to collect user data you gave the Data Collector license? Comments Amir Ghaemian commented Mar 08 '13, 9:27 a.m. Yeah there is a dedicated ETL user with the data collector license

It's all about the answers!

Related questions

Suspecting issues with the date dimension in Request Metrics

Accepted answer

One other answer

Your answer