When you need to track which processes were executed, when and by whom, you can go to each process and check its stats. However, if the number of processes to track is large, this approach is inefficient. A better alternative is to create a log.
How to create a process log
For each process that you want to be logged...
1. Create Data step and add a System variable data step properties that hold the variables you want to log, e.g.:
2. Create a managed table with a field for each of the variables added in 3, and also an additional field to log the status of the process. This field will be updated at the beginning and end of the process.
- processID
- processName
- username
- time
- status
3. Create the Manipulate data action step with Run query in SQL table instruction at the beginning of the process. This step will update the log table. ProcessID, processName, username and time will be linked to the system variables. The status will be a hardcoded value, e.g. processStarted:
- Running this step will create an entry in the log table similar to this:
4. Repeat the previous step at the end process, using a different status, e.g. processCompleted:
Use the snippet below, but change the PROCESS_NAME, USERNAME, CURRENT_DATE_TIME to data step properties links.
- Running this step will add a second entry:
- Your process will look like:
How to log data load
It is also possible to track when data is loaded into a managed table.
For example, if you want to track when data is inserted into the SalesByRegion table:
Managed tables in SolveXia have five meta fields which can be extracted and added to a log:
_InsertionDateTime stores the date when the records were inserted.
_InsertingProcessID identifies the process inserted the records.
_InsertingUserID identifies the user inserted the records.
_SourcefileName identifies the source file name of data for the records.
_SourceOwningStepReference stores a reference to the data step the file is stored in.
1. Create a log table with the following columns:
- processID
- time
- fileName
- rowsAdded (optional)
Note that you can add extra fields to the log, for example, total sales.
2. Create the Manipulate data action step with Run query in SQL table instruction after the data is loaded into the managed table to update the log. The query should look like:
Use the snippet below:
3. Join the dataLoadLog table to the processStatusLog table to extract username and process name using the process log table. Alternatively, you can include process name and username as linked properties.
Tips and tricks
Create a Yellowfin report using the log table to visualize the log.
Comments
0 comments
Please sign in to leave a comment.