nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Vertical Axis in Line Graph does not display times or dates correctly

Hangten4881

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.

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

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?

Hangten4881

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.

Hangten4881

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.

Gianni Ceresa

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)

Thomas Dodds

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.

Hangten4881

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.

Hangten4881

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.