we have initialization blocks that populate repository variables such as CURRENT_YEAR, CURRENT_MONTH, etc. There variables are used by various reports / dashboards to dynamically update to show the current data. We want these repository variables to update after our various DWs and marts have completed loading (there are a different set of repository variables for each DW / mart)
I'm running in to an issue with the initialization blocks though - they can be scheduled to re-run every X minutes / hours / days. They also seem to be able to be scheduled "every morning at 7:30 a.m." by setting the appropriate time. But what I need is this - an initialization block that I can refresh ON DEMAND when our batch processing is over - i.e. when our "spend mart" is finished loading, I want to run the init block that pulls down the current date variables for the spend mart. And when our HR DW finishes loading, I want to trigger the init block that loads it's variables. Our DW load batches do NOT complete at the same time every day - they're dependent on application batch and can vary by several hours.
I've been unable to find any elegant solutions to this - I'm currently working a ticket with Oracle, but it's not looking promising. Has anyone found a way to do this?
I've tried setting our init blocks to refresh every couple of minutes, so that once a DW load completes, OBIEE should show updated current date info within a few minutes. But this causes several problems:
1. There is always a "gap" between when the data refreshes and when the repository variable is updated. It might not be a long gap, but it's there
2. Unfortunately, our databases go down nightly for cold backups. When the init blocks are set to run every couple of minutes, they will naturally try to execute the queries while the database is being backed up. This causes the query to fail, and in turn the init block becomes "blacklisted" and no longer runs (at all). Meaning from that point forward the variables don't get updated unless I restart the server (which is tough because we have multiple different marts which all complete loading at various times - which means I'd have bounce the system while users are in it querying other data)
If we had a simple way to trigger the init block using nqcmd or something, we could simply add a step to our Autosys sequences and call the init block refresh directly after the load is complete. Then there wouldn't be a "gap", and the query wouldn't need to be firing continuously, so it wouldn't become blacklisted when the database is being backed up.
Appreciate any ideas!