We are using OBIA 18.104.22.168, OBIEE 10.1.3.4.1, Informatica 8.6.1. basically we have two data sources one is siebel 8.1.1 and other is sales data mart. we are extracting data from both the the sources and loading data to OBAW. I customized Informatica and DAC. basically I developed new mappings for a fact table and source for that mapping is sales DM and that mapping also have lookups to some dimension tables(source is siebel). only two fact tables developed based on sales DM.
we want to do daily incremental for from siebel and weekly full load from Sales DM. for that I created one execution plan for siebel mappings and it is working fine.
when I try to create new subject area with tasks related to sales DM(tasks have lookups to dimenions of siebel). it is automatically adding dependent dimensions. since we have daily incremental scheduled I dont want to run tasks related to dimension tables as part of weekly full load.
I want to build a subject area with only two tasks (sales DM) which is actually have dependency on other tasks. how do I do it?
Edited by: Jay on Jun 13, 2011 1:35 PM
The execution plan you built might have tasks included that you dont want. But, you can always disable them so that they wont run every day.
But when you want to run the weekly update, enable these tasks. See if this works.
Add a task parameter for that particular task. When you have the daily one running, pass a value that will make the source qualifier return 0 rows which means it does run but does not bring any updates. When you run the weekly update, send a parameter value that will force to run the updates and bring all the results you want.
Award points if its helpful.
Let you give you clear idea about the issue
Dimensions : D1,D2.....D10
Facts: F7 and F8 ( these two fact tables have lookups to some dimension tables in subject area 1)
Execution plan1(subject area1) run daily incremental load.
Execution plan2(subject area2) run weekly full load.
problem is with the subject area2, DAC automatically adding some tasks related to subject area 1. I just want to build execution plan with taks related to two fact tables in subject area 2.
If you include the dimenisons from SA1 as foreign keys to the Fact tables in SA2...the DAC will require these to run as it will consider these dimensions as pre-requisites for the load. Here are some options:
Option 1: Since all you need for SA2 is the lookups (and do not want the tasks for SA1 run) you can just schedule the weekly full of SA2 to run as long as the Dimensions from SA1 have already been loaded. Just make sure NOT to specify the dimensions as Foreign keys in the SA2 fact tables (F7, F8). This way, the DAC will run the weekly full load for the second EP but not use the tasks for the dimensions. Basically all you have to do is make sure EP1 dimensions are loaded before EP2 runs weekly.
Option 2: You can include both SAs in one EP and set it run to an initial FULL load and then daily incrementals. However, you can write a small script to update the REFRESH DATES of the 2 facts in SA2 (F7, F8) to be NULL every week. This will force a FULL of those two facts and the other SA1 Facts and Dims will continue to be incremental. This may mean you have to run daily incrementals of SA2 Facts as well..not sure if that is an issue or not.
Hope this helps.
For option2 as mentioned by obiaps, what script (database pl/sql, sql script or something else) can be used to change the values in DAC?
Edited by: user566193 on Jun 15, 2011 2:11 PM
Edited by: user566193 on Jun 15, 2011 2:11 PM
There is no script documented as far as I know...you just need to query the DAC metadata tables that hold the refresh date information and write a small procedure. It may require digging into the DAC metadata tables. I am unsure offhand which tables but the concept is to just update the table directly. The other option is to invoke a polling script (cron job) on the server once the EP kicks off that uses the DAC command line to check the status of the load..if it detects a failure you can restart the EP via command line. You can then add a post session task or task action in the last Task (update row in run table) that then "turns off" the polling when the task runs to completion. Using a combination of DAC command line, ability to call stored porcedures or shell scripts, and also understanding the DAC metadata tables..you can do a number of things like this.
W_ETL_REFRESH_DT holds the last etl refresh dates for each of the source/target tables in the DAC metadata. This is where we set the refresh date to null.
Edited by: user566193 on Jun 16, 2011 3:29 PM