Reload lineage over time for Data files / Sharepoint connections? ( Reload Monitor App )

[110]

Quick question hopefully  -  I'm using the reload monitor to pull just the apps / connections that are using sharepoint excel files, or uploaded excel files .

Within the reload monitor - I've added the following to the script to filter just Excel files.

TempReloadAuditData:
NoConcatenate
LOAD
_ReloadExecutionConcurrencyKey as 'ExcelConcurrencyKey',
    ReloadID as 'ExcelReloadID',
    _AuditReloadKey    // keep key for join to AuditReloadLineage
RESIDENT Reloads;


LEFT JOIN (TempReloadAuditData)
LOAD
    _AuditReloadKey,
    FileType as 'ExcelReloadFileType',
    FileName as 'ExcelReloadFileName',
    DataConnectionID
RESIDENT AuditReloadLineage
where
FileType ='xlsx' or FileType ='xlsm' 

However - this only appears to pull in the latest loaded Excel lineage files, and not the historical ones. 

Should I just start populating this into a QVD or is there a way to see this data historically also?

[Chanty4u]

Hi

In your modified Reload Monitor script, store the relevant lineage data (FileType, FileName, DataConnectionID, ReloadID, AppID, ReloadStartTime) into a QVD on every reload.

Then in the next reload, append new rows (rather than overwriting).

This builds your own historical lineage repository.

[diegozecchini]

Hi!

I agree with @Chanty4u the recommended solution is to store lineage rows into your own QVD keyed by the execution, then reuse it.

somenthing like
// Historical lineage store
LET vLineageQvd = 'lib://YourDataLake/ReloadLineageHistory.qvd';

// Load existing history (if any)
LineageHistory:
LOAD
ReloadID,
_AuditReloadKey,
FileType,
FileName,
DataConnectionID,
Timestamp // whatever execution timestamp field you have (from Reloads)
FROM [$(vLineageQvd)] (qvd)
WHERE 1=1; // handles first-run if file exists

// New lineage from this reload's ingestion
// (Assumes you also have Reloads with ReloadID, Timestamp)
LEFT JOIN (AuditReloadLineage)
LOAD
_AuditReloadKey,
ReloadID,
Timestamp
RESIDENT Reloads;

// Append only new execution+file combos
CONCATENATE (LineageHistory)
LOAD DISTINCT
ReloadID,
_AuditReloadKey,
FileType,
FileName,
DataConnectionID,
Timestamp
RESIDENT AuditReloadLineage
WHERE NOT EXISTS(ReloadID) // coarse grain; refine key if one ReloadID can emit multiple rows
OR NOT EXISTS(_AuditReloadKey); // safer: use a composite key

// Save back
STORE LineageHistory INTO [$(vLineageQvd)] (qvd);

// Use LineageHistory in your UI instead of the volatile AuditReloadLineage
If a single reload execution can produce multiple lineage rows, build a composite key like Hash128(ReloadID, FileType, FileName, DataConnectionID) and use that in the NOT EXISTS() filter.