Oracle Communities

Oracle Analytics and AI

Child Item

Options for displaying a "refresh" date/time

Hi All -

What kind of options are there for building a "banner" on the home page (or, possibly somewhere else) that shows the date/time of the most recent incremental load ? I think that even the completion time would suffice. I'm using the term "banner", but this doesn't need to be anything fancy. Just a simple string of text that says something along the lines of: 'Refreshed: {date:time}'.

The idea is that users want to know about the data ... how old/new it is.

OBIA 11.1.1.10.1 / OBIEE 11.1.1.9.3 / ODI 11.1.1.9.0 / Database 12.1.0.2

Source EBS 12.2.5

Regards,

Charles

Find more posts tagged with

Accepted answers

All comments

Martin van Donselaar

Keep track of this date during your ETL Process, depending on the ETL tool you are using this might already be done for you .

Get the latest refresh date in a (repository) variable and create a answer report with one item displaying the variable: valueof(<RepVariable>) .

There are other methods but thats how I usually do it.

Charles M

Thanks Martin. We are using Oracle Data Integrator (ODI) as our ETL tool. I believe we can get the date/time from one of the ODI repository or warehouse tables.

I think users would like to see this information located somewhere on the home page. That way, when they log in it is displayed prominently to them.

I come from the applications DBA side of things, so the RPD/repository/variables are fairly new to me. I'm hoping to get some steps that will guide me as I try to work on this.

Regards,

Charles

Martin van Donselaar

Well, I would strongly advise you to read https://docs.oracle.com/cd/E23943_01/bi.1111/e10540/toc.htm because you shouldn't mess with the Obiee Repository unless you know what you're doing obviously.

Chapter 19 deals with variables.

Sherry George

Hi Charles,

Since you are using OBIA, there might already be a variable available in the RPD that captures the last ETL run date.

Charles M

Thanks Sherry. I'll look for the product documentation on this.

Regards,

Charles

Charles M

Hi Sherry,

I can see that there is a dynamic repository variable named 'LAST_REFRESH_DATE'.

Here are a few details:

It sounds like this would contain the correct information. Thoughts?

Regards,

Charles

Sherry George

Yes, that should be it. You can also check what query is written for that initialization block 'Warehouse Refresh Date'.

Charles M

Hi All -

Haven't had very much time to work on this recently, but I did come across an interesting find earlier today while strolling through the RPD file (OBIA 11.1.10.1).

I can see that there are some objects named 'Dim - ETL Load Dates' and 'Fact - ETL Load Dates' ... both under an object called 'Data Load Tracking'

It looks like these trace back to the W_ETL_LOAD_DATES table in the data warehouse. That table has the dates at which all of the steps/packages in the load plan get completed.

This could be a good way to create a dashboard that contains the "refresh time". The 'max date' in this table corresponds to when the last step was finished. Further, it could even display individual times of the different functional areas/steps.

Anyone familar with this?

Regards,

Charles