Add Date to Incremental Load with multiple tables

joey_lutes — Mon, 05 Dec 2016 19:38:59 GMT

Referencing:

I'm at a loss and could really use some insight. I've been at this nonstop for 3 weeks and am failing miserably.

Here is a picture of the data model:

Each table is an individual feed of an individual, unique element and its attributes and associations.

The data is hierarchical, each dependent upon the upstream link(s).

The data is incremental, with Slowly Changing Dimensions (type 2), therefore every new load is concatenated/joined to the existing QVD and re-saved as a new QVD.

The DataLink table is formed by simply loading the individual timestamps as CDIts from their respective tables. No link to them (no ids loaded currently). If I attempt to link them, I get either 7 synthetic keys or a circular reference depending on how I do it. Every way I've tried (dozens of iterations) creates this problem. Even if I leave the Qlik-Generated synthetic keys alone, the data does not associate properly.

NOTE: WITHOUT the date injection, the data associates properly and displays accurately.

There are no dates in the data. I have inserted a uniquely-named timestamp (CDIts) into each table.

I've explored:

Concatenating all tables into 1. Produces 1 enormous table that bogs down performance/load substantially, plus from a development perspective, as each table/entity is its own element, keeping them separated would be my preference.
Link table: The data doesn't associate properly, even when I use individual keys for each table. It gets rid of the synthetic keys, but when displayed in the UI, the indirect data will not associate (ie, when a host is selected, the siteid will display, but not site.display-name).
Canonical date - all the dates will be common, not individual. Just need 1 common datestamp where multiple items can be shown on the same timeline. No dice.
Individual Master calendars - same as above.

I've read hundreds of articles on every related topic I can think of and none of them seem to do the trick.

THE GOAL:

Every row is timestamped so I am able to use a hash function to compare new data with old data to find the dimension changes and flag them accordingly. (that part works)

Display temporal view of multiple items on the same timeline/graph, showing evolution/changes over time.

Display table view of WHAT changed on any given date, given date/range selection.

At this point, I just want to load data effectively and have it reference properly.

topic Add Date to Incremental Load with multiple tables in App Development

Add Date to Incremental Load with multiple tables