In our previous blog, we unveiled the magic of Flowtrail AI’s Text-to-SQL functionality, which empowers users to craft complex queries using natural language. This innovation eliminates the need for deep SQL expertise, making data analysis more accessible than ever. Today, we’re excited to delve into Flowtrail AI’s advanced capabilities—specifically, its dynamic datasets and customizable dataset parameters. These features offer unparalleled flexibility and control, allowing users to generate highly customizable datasets and elevate their data exploration experience.

Understanding Flowtrail AI Dataset Parameters

Dataset parameters in Flowtrail AI function like adjustable settings for your data. Available in various formats such as text, numbers, dates, and even custom SQL queries, these parameters can be customized with unique names, default values, and specifications for whether they are mandatory or optional. This flexibility enables the creation of tailored queries without the need for complex coding.

Types of Dataset Parameters

1. String: Tailor your queries with text-based inputs.
2. Number: Inject numerical values for precise control.
3. Date: Specify dates for time-bound analyses.
4. Select: Choose from predefined options for streamlined selection.
5. Multi-Select: Pick multiple values for comprehensive filtering.
6. SQL: Craft custom SQL queries within parameters for advanced control.

Each parameter type has its unique characteristics:

• Variable Name: A user-defined name for the parameter, ensuring clarity.
• Default Value: A pre-set value for the parameter if left unspecified.
• Required vs. Optional: Define whether the parameter is essential for the query.

Crafting Your Dataset

Let’s illustrate how to create a dynamic dataset using Flowtrail AI’s features. Suppose we want to retrieve salary process details by year. Here’s a SQL query generated by the Text-to-SQL feature for the year 2024:

SELECT id, name, startDate, endDate, paymentDate, totalAmount
FROM employee_payroll
WHERE YEAR(startDate) = 2024

To make the year dynamic, we can use a parameter. We will add a Select parameter named selectedYear.

Adding a Parameter to the Dataset

1. Name the Variable: In the form, name the variable selectedYear.
2. Choose Parameter Type: Select the Select option from the parameters list.
3. Enter Values: Enter your select values, separating them with commas. For a default value like the current year, select it from the Default value column.
4. Set Required Status: Since selectedYear is necessary to retrieve data based on the ‘where’ condition, check the ‘required’ checkbox.

Integrating the Parameter into the Dataset

Replace the year 2024 with our variable name selectedYear in the following format: '{{selectedYear}}'. The modified SQL query becomes:

SELECT id, name, startDate, endDate, paymentDate, totalAmount
FROM employee_payroll
WHERE YEAR(startDate) = '{{selectedYear}}'

This change makes the dataset more dynamic and flexible, allowing users to retrieve specific data based on their chosen year. This is just one example of how Flowtrail AI’s customizable dataset parameters can enhance your data exploration experience.

Parameters with Optional Values

In some instances, parameters will be optional. If a parameter value is present, the query will execute based on that parameter; if not, it will execute without it, offering greater flexibility in analytics.

Consider this query to get employee salary details:

SELECT e.id, e.firstName, e.lastName, es.totalSalary
FROM employee e
JOIN employee_salary es ON e.id = es.employeeId

To filter employees by their name, we will add a text-based parameter called employeeName. If an employee name is provided, the query will display that employee’s salary; if no name is given, it will display the salary details of all employees.

Implementing Optional Parameters

SELECT e.id, e.firstName, e.lastName, es.totalSalary
FROM employee e
JOIN employee_salary es ON e.id = es.employeeId
{`% if employeeName %}
    WHERE e.firstName LIKE '%{{employeeName}}%'
{% endif %`}

This SQL query uses an ‘if’ statement to check whether an employeeName is provided. If it is, the query adds a ‘WHERE’ clause to filter by the specified name. If not, it retrieves all salary details.

Conclusion

Flowtrail AI’s dataset parameters open a world of possibilities for crafting highly customized and dynamic data explorations. With a variety of parameter types, unique names, and control over required vs. optional fields, you can tailor your queries to perfectly suit your analytical needs.

Stay tuned for our next blog post, where we’ll delve deeper into creating reports using these dynamic datasets. Happy exploring with Flowtrail AI!