Add Database Activity

You can add the database activity to the existing workflow or to a new work flow. In the workflow, using the database activity, you can fetch the production information of the industrial plants and show them in the logger. This information can be mailed to users in real-time and can be scheduled using scheduling activity.

To add Database Activity

1. Select the Workflows option to create a new workflow.

2. To create a new workflow, click on Workflow.

3. Add the title for the workflow.

4. Click Save & Continue.

5. Select Design the Workflow now option.

6. Click Finish to design the workflow.

7. Go to the Engine activities to find Database activity.

8. Add the database activity to the workflow design.

9. Go to Properties of the database activity.

10. Change the Activity Name if needed.

11. Add the Description if needed.

12. Select the database connection from the drop-down list and click Save.

13. Click on the Query Builder icon to create the query to fetch data from Intelligence datastore.

Query Builder

14. To enter the query, click on the Query Builder property to open the property window.

    Set the following properties:

    • Command Type: Select the Query command.

    • Query: You can enter the query either in the text box or from Expression builder.

    • Output XML variable

    Example: The query can be "Select * from FactTable" and if the query is to be passed with parameters, then the query will be "Select * from FactTable where DefectiveProducts >@defectiveProducts". Here @ defectiveProducts is the parameter.

15. Click Load Parameters. It loads the parameters within the query. In the Value field you can pass the value for the parameter or assign the values from a workflow variable. Enter the Output XML variable to store the select result set in the XML format. Click Save.

    The query used in this example fetches the product-related information from the fact table and product dimension table. It fetches the total number of products, defective products and expenses based on the ProductionDate range and setting of defective products > 1000. The variable @defectiveProducts value is kept constant to 1000. The production date range values come for the work flow variables.

    select p.Product, p.ProductType, f.TotalProducts, f.DefectiveProducts, f.TotalExpenses from FactTable f inner join Product p on f.productId = p.productId where f.DefectiveProducts > @defectiveProducts and ProductionDate between @currentDate1 and @currentDate2

16. Save the information. Click on 'View Schema' to view the schema of the output XML variable "outXmlInfo".

    The XML Variable "outXmlInfo" will be added in the Start Activity > XML Variables.

    Parameter values cannot update data types like ‘image’, ‘blob’ and ‘unique identifier’ from XML variables or variables.

17. Output XML Variable for return value: The Output XML variable will have the schema of the table as per the query entered. If the query is "Select * from Products", the XML variable will have all the columns as attributes in the schema. These can be looped through and used in the subsequent activities. The Output XML Variable name should be unique.

    To create an XML Iterator of the output XML Variable, the node selected should always be the "Table" node. XML Iterator can be created in the XMLVariables section of Start activity.

Activity outputs: The Database Activity returns the following values.

• Successful: Displayed when the query is executed.

• Unsuccessful: Displayed when the query execution is unsuccessful.