- Release notes
- Before you begin
- Getting started
- Integrations
- Managing access
- Working with process apps
- Creating apps
- Loading data
- Uploading data
- Retrieving the SQL Server database parameters
- Setting up a SQL Server account for data upload using an extractor
- Loading data using Theobald Xtract Universal
- Customizing process apps
- Data transformations
- TemplateOne app template
- Purchase to Pay app template
- Order to Cash app template
- Basic troubleshooting guide
Tips for writing SQL
- In unions, the names, and order of fields must exactly match. It may be necessary to create empty fields on parts of the union
to get all the fields aligned, with the
selectstatementnull as "Field_X". - When writing dbt for SQL Server, all Jinja statements are compiled into SQL code, regardless if they are inside SQL comments or not. For example,
in the following
-- {{ ref('Table1') }}, the Jinja will be compiled into SQL code. - Do not round numbers, this can lead to inconsistencies. The platform will do this automatically whenever a display value requires this.
In SQL, not all transformations can be computed in the same table. The reason for this may be aggregates or properties that cannot be expressed in a single select statement. You can create supporting tables for this purpose. Creating a supporting table in the database allows you to use the model in multiple transformations. If it is not needed to reuse the model, the supporting transformation can also be added as a preprocessing query in the existing table.
To make a distinction between supporting transformations and the other transformations, you can group them in a separate sub directory.
To make your query execution faster:
-
Avoid
select distinctwhere it is also possible to build an aggregate and just take one record using awhereclause. - Use
union allinstead ofunion. Usingunion all, records from tables are concatenated, whileunionremoves duplicates. - If you are working on a large dataset, you can
limit the data you are working with during development by using
limitin yourwhereclauses. - All models (except for the models in the
1_inputdirectory) are materialized as a table by dbt. This is the default setting for all UiPath Process Mining Connectors. For more information, see the dbt documentation on Materializations. - When generating an id field, use the
generate_idmacro available in pm-utils. Examples can be found in the devkit-connector.
The following style guide was used to develop the Process Mining app templates. It is recommended to follow these guidelines for consistency and readability.
- Write SQL commands and functions in lower case.
-
Use the same level of indentation for
select,from,where,join, etc., to understand the structure of the model more easily.- Use indentation for the usage of functions if this improves readability. For example, use indentation for each statement in
a
casefunction.
- Use indentation for the usage of functions if this improves readability. For example, use indentation for each statement in
a
-
Use consistent naming conventions for tables and fields to prevent SQL errors indicating that tables or fields do not exist in the database. Adhere to the following guidelines:
- Tables and fields start with a capital.
- Use an underscore
_between separate words in tables and fields. - All fields have quotes.
- Tables do not have quotes. This improves readability in combination with fields having quotes.
- All fields are prefixed with the table they originate from, to make the model easier to understand and extend.
- Commas used for the separation of fields are placed at the end of the line.
- Use inline comments when certain constructions need to be explained. When this is done, make sure the comment adds value.
- For readability and maintainability, use explicit select statements in the transformation part of the query and do not use
select *. Especially for unions, using theselect *can yield errors.
