How to handle the incremental update of big volumes of a cube in a robust way

[Sarayucog]

Hi all,

How to handle the incremental update of big volumes of a cube in a robust way.

My cube will run daily.

I have an IQD. With this iqd  we are generating a cube. I have implemented incremental update for this cube.

The IQD is like this

select col1, col2 ... col10 from table_name1 join table_name2
left outer join table_name3 .... left outer join table_name9
Where col1 > (sysdate-1) and col1 < sysdate

According to the above WHERE condition its going to add the only previous day (ONLY ONE DAY) data to the existing data in the cube.

Till now its ok.

My problem is if cube fails and not run for some days we will loose some data for those days the cube got failed. According to the above logic it will fetch the data for previous day only. With this I will loose the previous days data. So I want to retrieve the data into the cube on which day the cube has got failed.

For Example:

I ran the cube according and it got failed on 05-AUG-2010. Like this cube fails for next 4 days continuously (last day is 10-AUG-2010). On 11-AUG-2010 I ran the cube and it got succeeded. According to the above logic it will fetch the data for 10_AUG-2010 only. So here I am loosing the data for 04-AUG-2010 to 09-AUG-2010.

So I want to implement this like on which day it got failed from that day it should fetch the data into the cube.


Your help will be more appreciated.


Thanks in advance
Sarayu

[twlarsen]

Is this the same question you asked several days ago?

The suggestion about storing the date in a database table has worked for me.

For example, once the cube has been built, have your database append a record into the table for tha date.  Your query can then filter data between  the current date and the max date in the table.

[Sarayucog]

Thanku for the quick reply.

How to store the cube success date in the database table.


How did you extract the cube success date. Which logic you had followed. Could you please share with me. Did you used any macros or anything else, if it is macro please share that code with me. Its very urgent...

If you share that with me you will be a life saver.


Sarayu.

[twlarsen]

Use a filter in your datasource that pulls records where Date between Max[tableCubeSuccessDate] and ([Current date]-1).

[nmcdermaid]

"How to store the cube success date in the database table"

The cube success date... is the day that your cube built successfully. I suggest you make this a manual process for now (i.e. identify the cube built and write a record manually)

Once you're happy it works, you can do things like check the transformer log file for a lack of error messages and if so, mark the day down as successful.