I need to build out a dashboard that utilizes incremental loads and slowly changing dimensions. I understand the idea behind SCD, and I believe I need type 2.
We have a main app that pulls info from various SQL sources. At the end of that loadscript, I store five fields into a qvd. I need to now build a new app that loads this stored qvd plus any NEW data. See below. (UserRoles.qvd is the stored QVD).
OLD: load UserID, Critical, Role, SubRoleName, reloadtime() as CreateDate
FROM [C:\QVDev\SourceDocs\Shared QVD\Dimensional\UserRoles.qvd] (qvd);
Concatenate NEW: LOAD UserID, Role, Critical, SubRoleName, Reloadtime() as CreateDate
FROM [C:\QVDev\SourceDocs\QVW\Application\myOrders Resources\RoleChange.xlsx] (ooxml, embedded labels, table is Change);
Obviously this does not work the way I want. If there is an updated/changed record in the NEW table, I want the OLD table to have a new column called 'EndDate' with the date that the updated record came in. I want the NEW record to now contain a populated 'CreateDate' field. I want to keep both records.
