Hi,
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.
Thoughts?
