topic Re: Reload lineage over time for Data files / Sharepoint connections? ( Reload Monitor App ) in Connectivity & Data Prep

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

110 — Wed, 13 Aug 2025 15:46:22 GMT

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?

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

Chanty4u — Thu, 21 Aug 2025 04:05:15 GMT

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.

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

diegozecchini — Thu, 21 Aug 2025 07:07:32 GMT

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.