Categories
Vertical Axis in Line Graph does not display times or dates correctly
Hello, I have to build a report that displays job completions times based off an ETL. When I go to display using a line graph the vertical axis displays as a numeric numbers instead of TIME/TIMESTAMP (or even VARCHAR). I am using OBIEE version 11.1.1.7. Any help on this matter would be greatly appreciated.
Below is a snap shot of what my graph looks like now.
Answers
-
Hi,
As your vertical axis is supposed to be an aggregated value, what kind of aggregation do you have there? What is the original data type of your Y column?
0 -
Hi Gianni,
The vertical axis is not an aggregate value it is just a time value that reflect when load time completed on an ETL process so there is no Aggregation rule there. I guess my question is since the vertical axis is supposed to be an aggregate value is there a way to display time on the vertical axis with out applying aggregation (Maybe XML adjustments?). The graph is supposed to show ETL load times across a date range to determine Load times trends. As for the original data type for the Y axis the value it is set to VARCHAR at the Physical RPD level. I've since tried setting the data type to TIME, TIMESTAMP, and DATETIME to derive the appropriate results with no avail. Any suggestions are welcome at this point. Thanks.
0 -
Is there anyone who might be able to point me in the right direction? Any Admins or Subject Matter Experts on the forum? Even if to tell me that my task is impossible in OBIEE. Thank you.
0 -
Simple answer? Not there out of the box ...
Just because a time isn't an aggregate measure by default and the Y axis except to display an aggregate of numbers.
You want to try to hack the XML for that? Well, you have the XSD available, look inside if there is a tag allowing you to set a special format on that axis ... (it can be switched to be % etc., so maybe he can accept something).
Just a detail: if you look at the chart you posted you will see it display a 1, this one doesn't mean anything special, it looks like the chart try to convert the times into something setting 1 as the value of the first of your time (but as you have a flat line it means that your ETL is perfectly in time every day or the chart can't support that kind of data type).
Options?
- Display a duration, in number of minutes or seconds of your ETL load process, the resulting chart will allow you to see if it goes up or down and the table you add next to it display end time
- JS chart (D3 or any other)
0 -
if you are looking to display the intersection of date and run completion time, then you need a fact table keyed/structured that way. Use of information drives data model design.
So you have options ... quick and dirty would be:
tblETLComplete
DATEKEY | Completetime*
20160807 | 445
20160808 | 530
convert the hours and mins to an integer value (I suggest a 24 hr clock).
Now you can plot the complete times across days and get the line the way you want.
0 -
I think your second idea might have worked accept we don't have a time stamp of when the ETL Job started only when it ended. I thank you for your time though.
0 -
Thomas,
Your suggestion worked. I set the time to 24 hour and then set it to TO_NUMBER at the RPD level and the set the column to INT as Numeric was throwing a warning. Thank you for your creative solution.
0
