Oracle Business Intelligence Applications

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Options for displaying a "refresh" date/time

Received Response
96
Views
8
Comments

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Answers

  • Rank 6 - Analytics Lead

    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.

  • Rank 6 - Analytics Lead

    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

  • Rank 6 - Analytics Lead

    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.

  • Rank 7 - Analytics Coach

    Hi Charles,

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

  • Rank 6 - Analytics Lead

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

    Regards,

    Charles

  • Rank 6 - Analytics Lead

    Hi Sherry,

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

    Here are a few details:

    pastedImage_0.png

    pastedImage_1.png

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

    Regards,

    Charles

  • Rank 7 - Analytics Coach

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

  • Rank 6 - Analytics Lead

    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'

    pastedImage_0.png

    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

Welcome!

It looks like you're new here. Sign in or register to get started.