Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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.
What you put within bracket of the exist () function must exist in both tables
Last_Updated_Date_Sales:
Load Max(InvoiceDate) as Date
FROM
(
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
(
Concatenate
LOAD CustomerID,
ProductID,
Qty,
UnitPrice,
InvoiceDate,
CustomerID&'#'&ProductID&'#'&InvoiceDate as linekey //Keyfield to check
FROM
(
Store Sales into "E:\Qvd\Create\Sales.qvd";
Drop table Sales;