Skip to content
Infoveave Learning

Defining Automation Scenarios

The automation workflow scenario outlines an automation workflow in a retail company’s sales department, highlighting the importance of accurate sales data for performance evaluation. The scenario includes defining prerequisites, such as creating a SQL query to extract necessary columns from the master data and establishing a new Datasource using the retrieved columns.

Scenario

In a retail company, the sales department relies heavily on accurate and up-to-date sales data to make informed decisions. The company’s current system stores all sales-related information in a master Datasource named ‘Sales,’ which includes data such as transaction IDs, product IDs, product categories, geographical locations, sales dates, quantities sold, revenues generated, and so on. To streamline the process of analyzing and reporting on sales data, the company decides to create a new Datasource named ‘Product Details,’ which will specifically focus on product-related information, including product IDs, product categories, product types, product names, and prices. The company decided to automate the process of extracting relevant columns from the ‘Sales’ Datasource and uploading them to the ‘Product Details’ Datasource. This automation workflow will enable the sales team to access consolidated product-related information for analysis and reporting purposes.

Prerequisites

  • SQL Query Preparation: Before creating the workflow, you must ensure that SQL queries are prepared to fetch the required columns from the ‘Sales’ Datasource efficiently.
  • Datasource Configuration: Prior to executing the workflow, you must configure the ‘Product Details’ Datasource with appropriate measures and dimensions to accommodate the uploaded data.

SQL Query Preparation

For the workflow, writing the SQL query is a critical component as it determines which columns are extracted from the ‘Sales’ Datasource. Follow the below steps to craft required SQL query in Infoveave.

  1. To create a SQL query, navigate to Queries under Studio module.
  2. To create a new query, to be converted to a report, click on New Query.
    • You will be redirected to the SQL query editor.
  3. Select the required Datasource from the pre-configured list. Let us select the ‘Sales’ Datasource.
  4. Click on Continue and you will be redirected to the SQL query editor.

alt text

  1. In the SQL query editor, write your SQL query to collect the required columns.

    • Let us write the below query into the editor to identify the country wise profit.
      • Select [sales].[Order ID],[sales].[Order Date],[sales].[Product ID], [sales].[Category], [sales].[Product Name], [sales].[Product Type], [sales].[Quantity],[sales].[Total Cost], [sales].[Profit] from [Sales.sales]
      • The SQL query returns the columns Order ID, Order Date, Product ID, Category, Product Name, Product Type, Quantity, Total Cost, and Profit from the master Datasource ‘Sales’.

    alt text

  3. Click on the Save button to save the query with a name. Let us name the query as “Product Details”.

  4. The query is now saved in Query under Studio module.

Datasource Configuration

You are now required to configure a Datasource with the columns retrieved using the above query execution. This process involves converting the SQL query into a query report, downloading the report from Reports, and using the downloaded file to create a new Datasource named “Product Details.” Follow the below steps to configure a Datasource with the columns retrieved.

  1. Convert SQL Query into Query Report:

    • To create a SQL query report, navigate to Queries under Studio module.
    • Select the query that is to be converted to SQL report format.
    • Click on the Show as Report icon associated with the query.
      • You will get a notification on the successful conversion of the query into a data report.

  2. Download the Query Report from Reports:

    • To view the report generated, navigate to Reports under Studio module.
    • To download the query report, click on the Download icon associated with the query.
      • Download the file in Excel format.
        • You cannot choose a date range or apply filters to the downloaded report as associated query does not have both.

  3. Create a Datasource with the Downloaded File:

    • Go to the Datasources section in Infoveave and click on the Add Datasource button.
    • Choose the option to create a new Datasource from an Excel file.
    • Upload the downloaded report file and provide the name “Product Details” for the new Datasource.
    • Map the columns from the report to the corresponding fields in the Datasource as measures and Dimensions.
    • Choose the aggregation as required for the analysis.
    • Save the Datasource configuration.

Conclusion

The automation workflow discussed in this scenario illustrates the critical role of accurate sales data in the retail company’s sales department. By defining prerequisites, such as creating a SQL query and establishing a new Datasource, the automation workflow can contribute significantly to the sales team’s efficiency and effectiveness in decision-making.