Fact incremental load

[Anonymous]

Hi Experts,

Can anyone of you explain me about fact table incremental load. I have an idea about for dimension.

But confusion in fact incremental load.

Thanks in advance

Regards,

Kumar

[rahulpawarb]

Hello Kumar,

Please refer attached pdf file. Please let me know if you have any questions.

Regards!

Rahul

[Anonymous]

Hi Rahul,

Thanks for your attachment. But in this document it contains only Inserts, updates and delete scripts.

But in my fact table it will have only inserts on daily basis around 10k records.

Can you help me on this only inserts technique.

Regards,

Kumar

[rahulpawarb]

Hello Kumar,

Thank you for the prompt response.

Please refer below sample script for incremental load (INSERT ONLY):

//Set lower and upper bound values using variables
LET vExecTime = UTC();
LET vLastExecTime = 0;


//Get the upper bound value and set it into variable
LoadTime:
LOAD MaxString(LastModifiedDate) AS LastModifiedDate
FROM Table1.qvd(qvd);


LET vLastExecTime = Peek('LastModifiedDate', 0, 'LoadTime');


//Load the history data from QVD file
Table1:
LOAD Id,
...
FROM Table1.qvd(qvd);


//Load the changed data from base table and append it to result of QVD file load 
Concatenate(Table1)


Incremental:
LOAD Id,
...
FROM <TableName>
WHERE LastModifiedDate >= $(vLastExecTime)
AND   LastModifiedDate < $(vExecTime);

If you still have further queries then please share application file.

Hope this will be helpful.

Regards!

Rahul

[Anonymous]

Hi Rahul,

Once again Thanks,

I have used script as mentioned above.

As of now, I don't have any qvd in related to my fact. Since it is searching for fact and is failing.

Please see that screen shot.

Regards,

Kumar

[Anonymous]

I am trying to do with following script.

But problem here is

LET BeginningThisExecTime = Now();

LET LastExecTime  = BeginningThisExecTime ;

IncrementalExtraction:

SQL SELECT PrimaryKey, Field1, Field2 FROM Table_X

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(BeginningThisExecTime)#;
HistoryData:

LOAD *

RESIDENT IncrementalExtraction;

Concatenate

LOAD *

FROM $(SourceFilePath)History.QVD

WHERE NOT EXISTS (PrimaryKey,PrimaryKey);

STORE HistoryData INTO $(SourceFilePath)History.qvd;

In above script variable LET BeginningThisExecTime = Now(); shouldn't use.

why because in fact dates will never match with NOW().

If use NOW() variable related will never match in below highlighted.

Here in below script variable($(LastExecTime)) always should come from my fact table date only

IncrementalExtraction:

SQL SELECT PrimaryKey, Field1, Field2 FROM Table_X

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(BeginningThisExecTime)#;

Pls help me how to use that corresponding dates form my fact table

Regards,

Kumar

[rahulpawarb]

Hello Kumar,

Could you please share some sample data using which you are trying to implement incremental load?

Regards!

Rahul