Page 11 - Unanet AE: Creating Analytic Models Using Query Builder

Overview of Query Builder Components

The Query Builder applet offers a robust set of features for manipulating and extracting
insights from the dataset. It comprises several essential sections:

• Selected Fields: Displays a running list of selected fields from the dataset.
• Calculated Columns: Adds calculated fields using selected fields from the
dataset. Calculated columns use standard SQL formatting. These calculations
are added as new fields in the query using the specified title.
• Filters (requires SQL formatting): Add filters to the query using selected fields.
• Toolbar Options
o Build: Generates the query based on the dataset selections, filters, and
calculated columns. Selecting this will override previous build.
o Build & Execute: Generates the Query as described above, then runs the
SQL query as displayed in the Editor. Note: SQL Queries directly affect
the Unanet database. Use caution with this functionality.
o Execute: Executes the query in the Editor only. Note: SQL Queries
directly affect the Unanet database. Use caution with this functionality.
o Copy: Copies the text generated in the query editor.
o Clear: Clears the generated query.
o Export Query: Exports the text of the query to a .sql file.
o Save as Model: Saves the query as an Analytic Model, which can be
accessed in the Dashboard Queries manager and used on dashboards
and reports.
• Query Editor Window: Displays the query once built.
• Results: Displays the results of the query once executed from the dataset.

SQL Statements

Simplified SQL statements are built on the following commands: SELECT, FROM and
WHERE. Query Builder compiles these as follows:

• SELECT: Built from Selected Fields and Calculated Columns based on the
dataset
• FROM: Dynamically built from the selected Dataset
• WHERE: Built from defined Filters

Additional keywords such as GROUP BY and ORDER BY are dynamically built from the
dataset. Generated SQL can be further customized as necessary in the editor. Once
edited, selecting Rebuild will override any manual adjustments to the query.

Lesson 2: Building Queries In The Query Builder Applet ©2024 Unanet. All rights reserved. 11