How to do the Incremental refresh in the fact Table

[#1]

Hi Experts,

I can do the Incremental refresh for the dimension table using the Primary Key. See below Script

Last_Updated_Date:

Load Max([Created Date]) as Date

FROM

(qvd);

Let Last_Modified_Date=Peek('Date', 0,'Last_Updated_Date');

Customer:

LOAD Name,

     CustomerID,

     [Created Date]

FROM

(ooxml, embedded labels, table is Customer) where [Created Date]>$(Last_Modified_Date);

Concatenate

LOAD Name,

     CustomerID,

     [Created Date]

FROM

(qvd) where not Exists (CustomerID);

But the Question is How to do the Incremental refresh for the fact table since fact table contains only foreign key.????

Thanks In advance .

Regards,

Kabilan K.

[adamdavi3s]

Hi Kabilan,

I am not sure what keys you are talking about exactly... why is this having an impact on reloading your fact table? Do you not simply have a date field you can test in the same way as you have done your customers?

Do you mean that the key on the fact table is not unique? If so then simply create yourself a key to use for the load.

[vinieme12]

Have you tried yet?

Can you post the FACT script which is in question?

You only need a key field that uniquely identifies each row of data that is all you need to implement an incremental load.

[#4]

Yeah Yes Fact Table will not have the unique value, Here I have attached my sample data.

Script for sales:

Last_Updated_Date_Sales:

Load Max(InvoiceDate) as Date

FROM

(qvd);

Let Last_Modified_Date_Sales=Peek('Date', 0,'Last_Updated_Date_Sales');

Sales:

LOAD CustomerID,

    ProductID,

    Qty,

    UnitPrice,

    InvoiceDate

FROM

(ooxml, embedded labels, table is Sales) where InvoiceDate>$(Last_Modified_Date_Sales);

Concatenate

LOAD CustomerID,

    ProductID,

    Qty,

    UnitPrice,

    InvoiceDate

FROM

(qvd) where not Exists(CustomerID&'#'&ProductID);

Store Sales into "E:\Qvd\Create\Sales.qvd";

Drop table Sales;

This will not work as we expected in dimension table Customer and Product.

Thanks In Advance

Regards,

Kabilan K.

[vinieme12]

What you put within bracket of the exist () function must exist in both tables

Last_Updated_Date_Sales:
  Load Max(InvoiceDate) as Date
  FROM
 
  (qvd);
 
Let Last_Modified_Date_Sales=Peek('Date', 0,'Last_Updated_Date_Sales');
 

Sales:
  LOAD CustomerID,
  ProductID,
  Qty,
  UnitPrice,
  InvoiceDate,
CustomerID&'#'&ProductID&'#'&InvoiceDate as linekey   //Keyfield to check

FROM
 
  (ooxml, embedded labels, table is Sales) where InvoiceDate>$(Last_Modified_Date_Sales);

Concatenate

LOAD CustomerID,
  ProductID,
  Qty,
UnitPrice,
  InvoiceDate,
CustomerID&'#'&ProductID&'#'&InvoiceDate as linekey     //Keyfield to check

FROM
 
  (qvd) where not Exists(CustomerID&'#'&ProductID&'#'&InvoiceDate);     //Keyfield to check must exist in both table





Store Sales into "E:\Qvd\Create\Sales.qvd";

Drop table Sales;