How to identify within the script that app is already ran successfully?

[EliGohar]

Hi All,

I'm having an app that performs the ETL process (no client-side).

There is an initial process of creating QVD files that do not change later.
I want to recognize that the app ran successfully so that in the following runs I will skip creating the files I mentioned above.

How is it possible?

Many thanks,

Eli.

[JuanGerardo]

Hi @EliGohar, a solution is create a "flag" QVD with some meaningful information, for example last execution time, at the end of your script when everything went successfully. So you can use this flag to check if last time the process ended successfully.

To invalidate the flag, you can save an empty QVD, so next time you will get a NULL value when reading that value, for example with Peek() function.

JG

[EliGohar]

Hi @JuanGerardo 

Many thanks for your answer, This is something I thought about as well, I need to clarify something:

Sometimes we publish an updated application (Upgrade scenario) instead of the existing one, this means that the QVDs need to be created again.

Due to this new information, Can you think of another solution?

Thanks again,

Eli.

[JuanGerardo]

Hi, it is not needed to be re-created, just save it as an empty QVD, for example:

EmptyTable:
Load
	1 		AS LastSuccessRun
AutoGenerate(0);

Store EmptyTable into lib://Temp/EmptyQVD.qvd (qvd);

JG 

[EliGohar]

@JuanGerardo I meant that the QVD files that my script creates need to be created again in case I published an updated app. 

The use case:

1. Fresh new app - first publish

2. Create the "Static" QVD files only the first time the app is running

3. Publishing an updated app (Overwrite the previous one)

4. In the first run of the updated app, Create the "Static" QVD files again (because they might include additional values) 

Hope it's clear now,

Eli.

[JuanGerardo]

My suggestion:

1.- Use QvdCreateTime() to check if QVD exists, then read it and check for the value, for example with Peek().

2.- If creating static new static QVDs, clear the QVD saving it empty. This indicates the process is not complete.

3.- Create your static QVDs.

4.- If everything success, create the same QVD with one record, with the timestamp value (for example).

JG

[EliGohar]

@JuanGerardo Still, the big challenge remains, How do I recognize that the published app has been replaced with another one and ran for the first time?

[marcus_sommer]

I'm not sure if your intention is expedient to update a workflow-process within a multi-tier data-architecture because it requires more information as only the last state of the qvd's.

Personally I use a different approach in which all layers (unless the reports which just load binary) are controlled from the outside (external Excel table) by setting the load-type of 'full', 'partial', 'incremental' and 'incremental+'. In the standard everything loads 'incremental' or 'incremental+' which means the current period and/or the last n days/periods and if something is changed in the data-structure and/or the applied logics the setting goes - manually - to 'full' to load all available data or to 'partial' to load the periods from - to.

- Marcus

[EliGohar]

I'm using the Incremental/full load method for other transaction tables. These specific QVDs are lookups tables that unfortunately do not contain timestamp field.

[JuanGerardo]

Timestamp is just a value to fill the QVD flag, and can be your current time. So for the initial check you could use following example:

If IsNull(QvdCreateTime('lib://Temp/FlagQVD.qvd')) Then
	Let vCreateStaticQVD = True();	// QVD doesn't exist
Else
    DataTable:
    Load
        LastSuccessRun
    From lib://Temp/FlagQVD.qvd (qvd);

    Let vRun = Timestamp(Peek('LastSuccessRun'));
    Drop Table DataTable;
    
    If IsNull(vRun) Then
		Let vCreateStaticQVD = True();	// QVD exists but it's empty
    Else
		Let vCreateStaticQVD = False();	// QVD exists and isn't empty, no need to create static qvds
    End If
End If

JG