Hi everyone!
[b][color=red]This quest is for CCA>ODS>ETL gurus or DBA's only...!
[/color][/b]
I am attempting to optimize some of our Brio reports by placing a pre-check in the Brio document, to ensure that data is available to report from, [i]before [/i] running the main Queries of the Report. Since the main Queries take about 4-5 minutes to run and there's about 10 Users hitting the MART at the same time, pretty much every 15 minutes throughput the working day, I've considered that there must be an easier way...
So, I want to be able to run a Query/check that confirms/denies that a NOAGG CHUNK of data has been successfully transferred from the ODS to the MART and display this information to the User, so that they will know if it is worthwhile running their Report - or not.
I've got the basics in that I know I want to run a [i]PreProcess [/i] [b][i]Script [/i] [/b] before any Query is run on the Report. I also know which [b]VIEW_ID [/b] in the MART that the Brio Report Query is based on and I can constrain my Query to look at the last 15 minutes, to show if an entry exists. Something like this:
SELECT CHUNK_LOG.END_CHECK, CHUNK_LOG.REP_VIEW_ID
FROM [i]MART[/i].CHUNK_LOG
WHERE (CHUNK_LOG.REP_VIEW_ID = [i]nnnn[/i])
AND (CHUNK_LOG.END_CHECK > SYSDATE - 15 / 1440)
This will show me an entry for a successful data transfer within the last 15 minutes, using a rolling window of time and it works quite well. But.... I want to be able to check if the data has been transferred within the current 15 minute [i]segment[/i] - not as a rolling window of time. I think that means creating 4 scenarios which would cover each scenario, like these;
SCENARIO 1:
IF the time now (sysdate) in any hour of the day is between 0 minutes and 14:59.59 minutes past the hour, THEN I only want to check entries in the CHUNK_LOG for my VIEW_ID back as far as the first second of the first minute of this time period. [color=blue] e.g.: IF sysdate 12:02:32.00, THEN check if an END_DATE exists which is greater than 12:00:00.00 ONLY[/color]
SCENARIO 2:
IF the time now (sysdate) in any hour of the day is between 15:00:00 minutes and 29:59:59 minutes past the hour, THEN I only want to check entries in the CHUNK_LOG for my VIEW_ID back as far as the first second of the first minute of this time period. [color=blue] e.g.: IF sysdate 12:23:32.00, THEN check if an END_DATE exists which is greater than 12:15:00.00 ONLY[/color]
SCENARIO 3:
IF the time now (sysdate) in any hour of the day is between 30:00:00 minutes and 44:59:59 minutes past the hour, THEN I only want to check entries in the CHUNK_LOG for my VIEW_ID back as far as the first second of the first minute of this time period. [color=blue] e.g.: IF sysdate 12:33:35.00, THEN check if an END_DATE exists which is greater than 12:30:00.00 ONLY[/color]
SCENARIO 4:
IF the time now (sysdate) in any hour of the day is between 45:00:00 minutes and 59:59:59 minutes past the hour, THEN I only want to check entries in the CHUNK_LOG for my VIEW_ID back as far as the first second of the first minute of this time period. [color=blue] e.g.: IF sysdate 12:48:52.00, THEN check if an END_DATE exists which is greater than 12:45:00.00 ONLY[/color]
Does anyone have an idea what the best/optimized method of querying the database might be, to apply a window of time based on the current date/time? Or maybe there is another method to run as a pre-check to ensure data is available in the MART...
Any pointers would be great - thanks!
Tony
