Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

Suspecting issues with the date dimension in Request Metrics

Questions
Tags
Users
Badges
Amir Ghaemian
(48●1●13●15) 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?

0 votes

2 answers
7,982 views
0 votes

Accepted answer

Permanent link
Junli Wang
JAZZ DEVELOPER (551●2) Mar 08 '13, 8:28 a.m.
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

1 vote

Comments
Amir Ghaemian
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
JAZZ DEVELOPER Mar 08 '13, 8:44 a.m.
test the function mentioned in comment2 of the defect, and fix it by following it.

Amir Ghaemian
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
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
JAZZ DEVELOPER Mar 08 '13, 9:27 a.m.
it won't fix old data. no implications, that's a fix for a defect.

One other answer

Permanent link
Walter Urrejola
(23●1●8●11) Mar 08 '13, 9:22 a.m.
When you set up to collect user data you gave the Data Collector license?

0 votes

Comments
Amir Ghaemian
Mar 08 '13, 9:27 a.m.
Yeah there is a dedicated ETL user with the data collector license

Your answer

Register or log in to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.

Ask a question
Guidelines
FAQ
Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details
× 12,144

Question asked: Mar 08 '13, 8:10 a.m.

Question was seen: 7,982 times

Last updated: Mar 08 '13, 9:27 a.m.

Confirmation Cancel Confirm