Faster way to remove orphan fields from data model
Hello Everyone,
I am working with a large Qlik sense data model where we have huge number of orphan fields which are not being used in frontend charts as well as not used anywhere for linking of tables or calculations in the load script.
I want to remove those fields from the data model to improve performance. Since the number of such fields is very high, is there any faster way or any shortcut method to remove those fields ? I can't keep on removing the fields manually one by one as it is taking a lot of time.
Such a list of fields might be used within a loop at the end of the scripts to drop these fields.
More automation is probably quite difficult because it would require to read all scripts within all layers and then adding appropriate drop-statements respectively much more preferable to avoid already their loading. A manipulating of the scripts from the outside is probably possible but surely not trivial. Be further aware that fields which have no usage within the UI might be used for any temporary calculations/matching within the ETL.
Therefore I doubt that there is a sensible way to apply an automation else more pragmatic would be to do it manually and this pain will be helpful to avoid such struggle in the next time by loading everything explicitly.
1. Schedule the Document Analyzer app to run after your main app reload. 2. Use its generated QVDs to identify unused fields. 3. Load the unused fields list into your app script. 4. Loop through the list and DROP FIELD each unused field dynamically. 5. Optionally, log dropped fields for governance tracking.
Below is snippet for ref.
UnusedFields:
LOAD
FieldName,
AppName,
'Unused' as FieldStatus
FROM [lib://Governance/DocumentAnalyzer_Fields.qvd] (qvd)
WHERE [FieldState] = 'UNUSED' // This depends on your DA structure
AND [AppName] = 'MyAppName';
FOR i = 0 TO NoOfRows('UnusedFields') - 1
LET vField = Peek('FieldName', $(i), 'UnusedFields');
TRACE Dropping unused field: $(vField);
DROP FIELD [$(vField)] FROM *;
NEXT i
Hi @marcus_sommer , thanks for your reply. As I said, these fields are not being used anywhere in the data model. neither in any calculations nor in any key generation for table linking. these are completely orphan useless fields which are unnecessarily increasing the load time of the model.
Anyways, I guess I will have to do the removal manually since I cannot use a loop for dropping them as it would require them to load in the memory first. Thanks for the help anyways.
@HirisH_V7 , I cannot use this approach as it would require the fields to load into memory first before dropping them. my purpose of removing these fields was to reduce loading time.
Best possible Solution would be doing a binary load for final app and doing the dropping at end of it. This way the current app reload time would be less and only required fields would be their in it.
Else, other work around would be to create a transformation layer with limited fields (which are inline with metadata extracted previously) loaded into QVD's. Then those QVD's sourced into main app.
As per you ask, some or other way we should get meta data of which fields are being used and not. Then only dropping or commenting can be done.
You may try to reverse the above QSDA approach by evaluating the existing fields against the unused ones to get the necessary fields. These field-list could be then used to load from the data-base which means that all wanted fields are explicitly listed within the load/select-statement (which is a recommended best-practice approach).
