How to fetch the data on the day where its got failed

[Sarayucog]

Hi all,

I have a cube with incrementally updating the data.

My requirement is

If cube fails continuously for some days on next day if the cube will get success it should fetch the data from the day where it got failed.

Foe Example: The cube got failed from 18th to 21st. Today is 22nd. If I run the cube on 22nd it should fetch the data from 18th to 21st.

I am using IQD as my data source. Database is Oracle and I am using batch file and windows schedule to run the cube automatically on a perticular time.

I want to know how to get the dates automatically in IQD.

For Example: where time > (sysdate-1) and time < sysdate ---> It will get the previous days data. If it fails for this I have implemented logic in batch file. For one day its working fine. But for more than one day how to do it.

where time > (sysdate-1) and time < sysdate this logic for single day cube failed. I want to know for more than 1 day cube failed logic.

Is there any way to implement the logic to take the dates automatically in Oracle.

This is my need. Its very ungent. Your help may be helpful to me.


Thanks in advance...

[redmist]

how about you put a filter in your fact query to bring in records from the days the load failed

[Sarayucog]

Hi Redmist,

Thanks for ur reply...

I know that to use the filter in IQD, but I am not getting an idea how to write the filter to take the dates dynamically.

How to identify on which day its got failed.

[Arsenal]

I haven't used Incremental update much, but wouldn't it be the case that your database will only have records for the newest dates for Incremental to work? If records from previus dates exist, then the incrementally updated cube will have duplicated counts after every update. If true, then how would you have the previous day records anyway on the database?

[Sarayucog]

HI Arsenal,

Everybody is saying the same reason which you are saying, but I am getting the data which I need as per my dates.

See my database contains years of data, but I am getting the data which I need as per my dates.

My db load will start after 12 am so it will load pervious days data only. ok.

When we implement the incremental update we need to change our iqd as per the need. Then only you would get the data as you need.

whatever the data is in database, if you modify your iqd as per the requirement then you can fetch the data without duplicates.

[Arsenal]

Hmm, that probably means that you're using a _add_days -1 or something like that in your IQD to restrict the date to yesterday..i.e. you're hard coding the day to be yesterday, correct?

[Sarayucog]

Yes, you are right.

Why I have mentioned that means everybody is saying the same which you told. So with this somebody would know this thing we can do in this way also.

Now could you tell me how we can identify the last successfull date of the cube OR last failure time of the cube. I want these dynamically by using SQL Query. I want make changes in IQD to get these cube last success OR last failure dates. With this I can start my work.

[redmist]

Easiest way to achieve this would be by entering a date in your database on the successful build of your cube. Even if you do not have the resources to insert a record in a table, i believe this principle would still work.

You have to monitor the error codes during your cube build. If the cube does not fail then enter the relevant date in your database. If the cube fails this step will not get processed and the table would still have the last successful date. Your IQD can then use this data as the filter.
If the cube build is successful the code expected is 0. Any other code represents failure

see below for a sample .bat file

"d:\Cognos73\cognos\cer4\bin\trnsfrmr.exe" -n2 -s -i -kAVDW=dw/manager1 -dLogFileDirectory="d:\Log Files" "d:\Production\<FOLDER NAME>\Models\<CUBE NAME>.mdl"
if %ERRORLEVEL% GEQ 1 goto error
:noerror
You can call another bat file if needed
goto end
:error
call failure.bat - This bat file can be used to enter a record in the database
:end

I do not have the knowledge of how to get the date inserted from a batch file but i assume it will not be too difficult.

[Sarayucog]

Hi Redmist,

I got some idea with your help. Thanks for giving quick reply.

Now I want to know how to enter the cube success date data (records) into DB table automatically.
Is it possible with batch files.

I don't know how to write the batch files and VB Script. If you have could you please give it to me.


Thankyou

[Sarayucog]

Hi Redmist,

My database will not save any cube success and cube failure dates. So now I have to get the cube success and failure dates and those records will insert into database and then I have to use those columns in my IQD as filter.

I know how to extract the cube success dates by using batch files, but I don't know how to insert those records into the database. If anyone knows pls let me know that batch ifle code.

Its urgent...