11. Reporting

Requirements

• You have done the Decision, Message section tutorial

What you will learn

• How to use job execution reports to raise information to your users

What is it used for?

Data Factory reports provides you structured information on what happened during your job and tasks execution. The reports are structured so that you can automate its processing to decide what must be the next step in your job, see Decision, Terminate, Message section.

In this tutorial

We will use the report on collected items to send emails and warn users to check out those items.

In this case the supplier has published several items, some maybe new and some others updated. If there are updated items, in order to warn users to check updated items, an email will be generated and sent.

Setup

1. Collect items

{
    "name": "exchange-receive-items",
    "taskReferenceName": "receive_items",
    "description": "Collect products",
    "optional": false,
    "type": "SUB_WORKFLOW",
    "inputParameters": {
        "new": "CREATE",
        "exist": "UPDATE",
        "matching": {
            "first": "EAN"
        }
    }
},

This first task collects items published by the supplier, and by default creates new items and update existing ones. You can find details about items publication and collect here.

At the end of the collection, a report is generated detailing what happened. Here we can see that two items have been updated with their unique identifier.

<Report task="exchange-receive-items" date="2023-02-27T14:49:30.291Z" actionId="b0111003-61e5-49ed-a5ea-cf7a0c18a54b">
    <Log type="info">
        <Metadata name="code">ITEM_UPDATED</Metadata>
        <Metadata name="xpath"/>
        <Metadata-Collection name="identifiers">
            <Metadata name="EAN">3700730500203</Metadata>
        </Metadata-Collection>
        <Metadata name="partitionKey">ACTIVE</Metadata>
        <Metadata name="tableKey">PRODUCTS</Metadata>
        <Message>The following item was imported successfully.</Message>
    </Log>
    <Log type="info">
        <Metadata name="code">ITEM_UPDATED</Metadata>
        <Metadata name="xpath"/>
        <Metadata-Collection name="identifiers">
            <Metadata name="EAN">7332543619405</Metadata>
        </Metadata-Collection>
        <Metadata name="partitionKey">ACTIVE</Metadata>
        <Metadata name="tableKey">PRODUCTS</Metadata>
        <Message>The following item was imported successfully.</Message>
    </Log>
    <!-- ... -->
    <Input name="correlationId">F3Ae2UoumWx9tyLiTUrpdHvcJpYExaNw70tYagmvud21P8MVeVAAXNC1KyEAITl:619b56efd6f35aa543d77d82:SvDLaFeq-1677509367112018056213208730032640138073:1</Input>
    <Input name="exist">UPDATE</Input>
    <Input name="new">CREATE</Input>
    <Input name="matching.first">EAN</Input>
</Report>

2. Read collect report

Now that the collection ended, we use the generated report to extract the list of all updated items, so that we can check if there are items to notify to the users.

The collection report is usable as input file for the file transformation task file-transformation-xslt by setting the variable ${<taskReferenceName>.output.report}. Here in our case : ${receive_items.output.report}.

{
    "name": "file-transformation-xslt",
    "taskReferenceName": "build_request",
    "description": "Prepare export request",
    "optional": false,
    "type": "SUB_WORKFLOW",
    "inputParameters": {
        "mode": "FILE",
        "file": "${receive_items.output.report}",
        "xslt": "file://assets/01-build-request.xslt",
        "fileName": "report.xml"
    }
},

On that report we will filter the information based on the log code ITEM_UPDATED to get only the updated items EAN. And we will generate a request file that we will use to export those items with the referencing request id.

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fn="fn" xmlns:pl="http://product-live.com" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="3.0" exclude-result-prefixes="xs fn pl">
    
    <xsl:output method="xml" indent="yes" encoding="UTF-8" cdata-section-elements="" />
    
    <xsl:template match="/">
        <Items-Request>
            <Filters>
                <Filter-Identifier key="EAN" operator="EQUALS">
                    <!-- Here we set the list of EAN which will be used to export those items -->
                    <xsl:for-each select="Report/Log[@type='info' and Metadata[@name='code']='ITEM_UPDATED']">
                        <Value><xsl:value-of select="Metadata-Collection[@name='identifiers']/Metadata[@name='EAN']"/></Value>
                    </xsl:for-each>
                </Filter-Identifier>
            </Filters>
            <Properties>
                <Field key="REQUEST_ID" />
            </Properties>
        </Items-Request>
    </xsl:template>
</xsl:stylesheet>

3. Export updated items

Now that we have generated the request to get the collected items that have been updated with specific information, we will use that request to export those items. For that we use the output of the previous task as the export-items input request file ${build_request.output.file}.

{
    "name": "table-export-items",
    "taskReferenceName": "export_items",
    "description": "Export collected items that have been updated",
    "optional": false,
    "type": "SUB_WORKFLOW",
    "inputParameters": {
        "tableKey": "PRODUCTS",
        "mode": "REQUEST",
        "request": "${build_request.output.file}",
        "fileName": "items.xml"
    }
},

4. Filter updated items

As there can be items updated by the supplier that are now archived and removed from sell by the retailer, it is only required to warn users about active products.

Here we will transform the exported items file to generate an output file with only active items. And in case there are no active items, raise a message saying so. To use the exported items file, we use the previous task output in a transformation : ${export_items.output.file}.

{
    "name": "file-transformation-xslt",
    "taskReferenceName": "filter_items",
    "description": "Remove archived requests",
    "optional": true,
    "type": "SUB_WORKFLOW",
    "inputParameters": {
        "mode": "FILE",
        "file": "${export_items.output.file}",
        "xslt": "file://assets/02-filter-active-items.xslt",
        "fileName": "active-items.xml"
    }
},

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fn="fn" xmlns:pl="http://product-live.com" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="3.0" exclude-result-prefixes="xs fn pl">
    
    <xsl:output method="xml" indent="yes" encoding="UTF-8" cdata-section-elements="" />
    
    <xsl:template match="/">
        <xsl:variable name="nbr" select="count(Table/Items/Item[@partition='ACTIVE'])" />

        <xsl:if test="$nbr=0">
            <xsl:message terminate="yes">No data to collect</xsl:message>
        </xsl:if>

        <Table key="{Table/@key}">
            <Items>
                <xsl:for-each select="Table/Items/Item[@partition='ACTIVE']">
                    <xsl:copy-of select="."/>
                </xsl:for-each>
            </Items>
        </Table>
    </xsl:template>
</xsl:stylesheet>

5. Decide if there is an email to send

Now that we have collected products, exported the updated ones and filtered only the active ones, we can check if there is information to report by email to the users.

To do that we use the previous task output to verify if there are indeed updated active products. The variable used is the ${filter_items.output.allFilesTransformed} which is YES if the file has been correctly transformed, and NO if there was an error.

(Note: To catch an error on purpose the transformation task must be set to optional:true, you can use xsl:message with the property terminate="yes" to create an error and stop the current transformation, like in our case).

If there were active updated products, the file would have been successfully processed. And so, the variable ${filter_items.output.allFilesTransformed} would have been set to YES.

Otherwise, if there were no active updated products, the transformation would have not been able to process the file as it would have been empty. So, the variable ${filter_items.output.allFilesTransformed} would have been set to NO.

Based on that variable we either terminates the job, or continue to generate and send the emails.

{
    "name": "decision",
    "taskReferenceName": "decision",
    "description": "Terminates if no active updated items",
    "optional": false,
    "type": "DECISION",
    "inputParameters": {
        "case_value_param": "${filter_items.output.allFilesTransformed}"
    },
    "caseValueParam": "case_value_param",
    "decisionCases": {
        "NO": [
            {
                "name": "terminate",
                "taskReferenceName": "terminate",
                "description": "End of process",
                "optional": false,
                "type": "TERMINATE",
                "inputParameters": {
                    "terminationStatus": "COMPLETED"
                }
            }
        ]
    }
},

6. Prepare product status update

From the collected active updated products, we will now set the recipients of the emails for each referencing request id from the data associated with the products. For that we will use the previous task output to fill in the parameters required to set up the emails.

{
    "name": "file-transformation-xslt",
    "taskReferenceName": "build_data",
    "description": "Prepare email data",
    "optional": false,
    "type": "SUB_WORKFLOW",
    "inputParameters": {
        "mode": "FILE",
        "file": "${filter_items.output.file}",
        "xslt": "file://assets/04-filter-items.xslt",
        "fileName": "filtered-items.xml"
    }
},

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fn="fn" xmlns:pl="http://product-live.com" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="3.0" exclude-result-prefixes="xs fn pl">
    
    <xsl:output method="xml" indent="yes" encoding="UTF-8" cdata-section-elements="" />
    
    <xsl:template match="/">
        <Data>
            <xsl:for-each-group select="Table/Items/Item" group-by="Field[@key='REQUEST_ID']">
                <xsl:variable name="currentKey" select="current-grouping-key()" />
                <xsl:variable name="firstItem" select="current-group()[1]" />
                <Request>
                    <Identifer><xsl:value-of select="$currentKey"/></Identifer>
                    <Mail name="supplier"><xsl:value-of select="$firstItem/Field[@key='SUPPLIER']"/></Mail>
                    <Mail name="retailer"><xsl:value-of select="$firstItem/Field[@key='RETAILER']"/></Mail>
                    <Mail name="supply-planner"><xsl:value-of select="$firstItem/Field[@key='SUPPLY_PLANNER']"/></Mail>
                    <Status><xsl:value-of select="$firstItem/Field[@key='STATUS']"/></Status>
                </Request>
            </xsl:for-each-group>
        </Data>
    </xsl:template>
</xsl:stylesheet>

7. Split the set of products to email

In order to send distinct emails depending on the referencing request id, we will split the list of information to send by referencing request id. For that we will use the previous task output and split it by request.

{
    "name": "file-transformation-split-xml",
    "taskReferenceName": "split_request",
    "description": "Split by request id",
    "optional": false,
    "type": "SUB_WORKFLOW",
    "inputParameters": {
        "mode": "FILE",
        "file": "${build_data.output.file}",
        "xpath": "Data/Request",
        "fileName": "requests.xml"
    }
},

8. Prepare email content

We have a list of items for each referencing request id with the targeted recipients. So now we can build the actual email. For that we will use each previous task output to fill in the parameters of the email.

{
    "name": "file-transformation-xslt",
    "taskReferenceName": "build_mail",
    "description": "Préparation des mails",
    "optional": false,
    "type": "SUB_WORKFLOW",
    "inputParameters": {
        "mode": "FILES",
        "files": "${split_request.output.files}",
        "xslt": "file://assets/05-build-mail.xslt",
        "fileName": "mails.html"
    }
},

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fn="fn" xmlns:pl="http://product-live.com" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="3.0" exclude-result-prefixes="xs fn pl">
    
    <xsl:output method="xml" indent="yes" encoding="UTF-8" cdata-section-elements="" />
    
    <xsl:template match="/">
        <xsl:variable name="requestId" select="Request/Identifier" />
        <xsl:variable name="supplierEmail" select="Request/Mail[@name='supplier']" />
        <xsl:variable name="retailerEmail" select="Request/Mail[@name='retailer']" />
        <xsl:variable name="supplyPlannerEmail" select="Request/Mail[@name='supply-planner']" />
        <xsl:variable name="status" select="Request/Status" />

        <html lang="en">
            <head>
                <meta charset="UTF-8" />
                <meta name="viewport" content="width=device-width, initial-scale=1.0" />
                <xsl:choose>
                    <xsl:when test="$status = 'VALID_RETAILER'">
                        <xsl:variable name="mailTo"><xsl:value-of select="$retailerEmail"/><xsl:text>;</xsl:text><xsl:value-of select="$supplyPlannerEmail"/></xsl:variable>
                        <meta name="pl-mail-to" content="{$mailTo}" />
                    </xsl:when>
                    <xsl:otherwise>
                        <meta name="pl-mail-to" content="{$retailerEmail}" />
                    </xsl:otherwise>
                </xsl:choose>
                <meta name="pl-mail-subject" content="Referenced products validation" />
                
                <style>
                    body {
                        font-family: 'Segoe UI', sans-serif;
                    }
                    table, th, td {
                        border: 1px solid black;
                    }
                    th, td {
                        padding: 3px;
                    }
                </style>
            </head>
            <body>
                <p>Hello,</p>
                <p>
                The product information request referenced N°<xsl:value-of select="$requestId"/>, for supplier <xsl:value-of select="$supplierEmail"/> has been processed by the portal.
                </p>
                <p>To valide the data, <a href="https://app.product-live.com/">click here</a>.</p>
                <p>Regards,</p>
            </body>
        </html>
    </xsl:template>
</xsl:stylesheet>

9. Send the emails

We have ultimately collected products from the supplier, figured out which ones were updated and built up a notification email with the referencing request id and the appropriate recipients. It is time to send the email we set up in the previous task.

{
    "name": "notification-send-email",
    "taskReferenceName": "send_email",
    "description": "Send email notification to retailer",
    "optional": false,
    "type": "SUB_WORKFLOW",
    "inputParameters": {
        "mode": "FILES",
        "files": "${build_mail.output.files}"
    }
}

What you have learned

We learned how to use task report to continue the process with additional actions: exporting data, transforming them to build up emails and send them. Using Decision, XSLT Transformation and Send Email tasks allow to handle these processes.

Next

Learn about Scalability and performance