- Release notes
- Before you begin
- Getting started
- Integrations
- Working with process apps
- Working with dashboards and charts
- Working with process graphs
- Working with Discover process models and Import BPMN models
- Showing or hiding the menu
- Context information
- Export
- Filters
- Sending automation ideas to UiPath® Automation Hub
- Tags
- Due dates
- Compare
- Conformance checking
- Root cause analysis
- Simulating automation potential
- Starting a Task Mining project from Process Mining
- Triggering an automation from a process app
- Viewing Process data
- Creating apps
- Loading data
- Customizing process apps
- Publishing process apps
- App templates
- Additional resources
- Out-of-the-box Tags and Due dates
- Editing data transformations in a local environment
- Setting up a local test environment
- Custom throughput time metrics
- SQL differences between Snowflake and SQL Server
- Configuration settings for loading input data
- Designing an event log
- Extending the SAP Ariba extraction tool
- Performance characteristics
Custom throughput time metrics
With customizing data transformations and dashboard editing you can create and use custom throughput time metrics. Throughput times are the timings between two activities A and B. Below is a description of the steps you need to perform to create a custom throughput time metric when editing transformations and how to enable the througput time metric in the process app dashboards.
You must first caclulate the throughput time and then make it available as a Case field.
Per case, you can compute throughput times between Activity A and Activity B. Since activities can occur more than once per case, you need to take into account whether you take the first or last occurrence of an activity.
-
Create an additional model based on the Event log to calculate the desired throughput times. For example, Cases_with_throughput_times.
-
In this model, create pre-processing tables in which you define which event ends you want to use for the computations. Per table, you need the Case ID, and the Event end of an activity. Below is an example of how to select the last occurrence of activity A for a case.
Event_end_activity_A as ( select Event_log."Case_ID", max(Event_log."Event_end") as "Event_end_activity_A" from Event_log where Event_log."Activity" = 'Activity A' group by Event_log."Case_ID")
Event_end_activity_A as ( select Event_log."Case_ID", max(Event_log."Event_end") as "Event_end_activity_A" from Event_log where Event_log."Activity" = 'Activity A' group by Event_log."Case_ID")Note:In this example, if you want to select the first occurrence of the activity, replace max with min.
-
Define the throughput time table by joining the pre-processing tables to the Event log and calculating the actual throughput time.
Tip:You can use the datediff function provided in the pm-utils package to compute the time difference between any two event ends.
The throughput time should be computed in milliseconds for the instances where Activity A precedes Activity B. Milliseconds are the unit of time used to define durations in the app template. As the throughput times are already grouped per case in the pre-processing tables, you can pick any record. In the above example he aggregation min is used. The throughput time table, selecting the throughput time and a Case ID, can be defined as displayed below.
Cases_with_throughput_times as ( select Event_log."Case_ID", case when min(Event_end_activity_A."Event_end_activity_A") <= min(Event_end_activity_B."Event_end_activity_B") then {{ pm_utils.datediff('millisecond', 'min(Event_end_activity_A."Event_end_activity_A")', 'min(Event_end_activity_B."Event_end_activity_B")') }} end as "Throughput_time_activity_A_to_activity_B" from Event_log left join Event_end_activity_A on Event_log."Case_ID" = Event_end_activity_A."Case_ID" left join Event_end_activity_B on Event_log."Case_ID" = Event_end_activity_B."Case_ID" group by Event_log."Case_ID)"
Cases_with_throughput_times as ( select Event_log."Case_ID", case when min(Event_end_activity_A."Event_end_activity_A") <= min(Event_end_activity_B."Event_end_activity_B") then {{ pm_utils.datediff('millisecond', 'min(Event_end_activity_A."Event_end_activity_A")', 'min(Event_end_activity_B."Event_end_activity_B")') }} end as "Throughput_time_activity_A_to_activity_B" from Event_log left join Event_end_activity_A on Event_log."Case_ID" = Event_end_activity_A."Case_ID" left join Event_end_activity_B on Event_log."Case_ID" = Event_end_activity_B."Case_ID" group by Event_log."Case_ID)"
Calculating throughput time in days excluding weekends
date_from_timestamp
function provided in the pm-utils package to compute the number of days between two activities. Additionally, the diff_weekdays
function enables you to filter out weekend days.
See the code example below for how to calculate the number of weekdays between Activity A and Activity B.
with Event_log as (
select * from {{ ref('Event_log') }}
),
Activity_A as (
select
Event_log."Case_ID",
min({{ pm_utils.date_from_timestamp('Event_log."Event_end"') }}) as "Date_activity_A"
from Event_log
where Event_log."Activity" = 'Receive invoice'
group by Event_log."Case_ID"
),
Activity_B as (
select
Event_log."Case_ID",
min({{ pm_utils.date_from_timestamp('Event_log."Event_end"') }}) as "Date_activity_B"
from Event_log
where Event_log."Activity" = 'Pay invoice'
group by Event_log."Case_ID"
),
Total_days_minus_weekends as (
select
Activity_A."Case_ID",
Activity_A."Date_activity_A",
Activity_B."Date_activity_B",
{{ pm_utils.diff_weekdays('Activity_A."Date_activity_A"', 'Activity_B."Date_activity_B"') }}
-- Only compute for cases where both dates are known.
from Activity_A
inner join Activity_B
on Activity_A."Case_ID" = Activity_B."Case_ID"
)
select * from Total_days_minus_weekends
with Event_log as (
select * from {{ ref('Event_log') }}
),
Activity_A as (
select
Event_log."Case_ID",
min({{ pm_utils.date_from_timestamp('Event_log."Event_end"') }}) as "Date_activity_A"
from Event_log
where Event_log."Activity" = 'Receive invoice'
group by Event_log."Case_ID"
),
Activity_B as (
select
Event_log."Case_ID",
min({{ pm_utils.date_from_timestamp('Event_log."Event_end"') }}) as "Date_activity_B"
from Event_log
where Event_log."Activity" = 'Pay invoice'
group by Event_log."Case_ID"
),
Total_days_minus_weekends as (
select
Activity_A."Case_ID",
Activity_A."Date_activity_A",
Activity_B."Date_activity_B",
{{ pm_utils.diff_weekdays('Activity_A."Date_activity_A"', 'Activity_B."Date_activity_B"') }}
-- Only compute for cases where both dates are known.
from Activity_A
inner join Activity_B
on Activity_A."Case_ID" = Activity_B."Case_ID"
)
select * from Total_days_minus_weekends
Calculating throughput time in days excluding holidays
Follow the steps below to calculate the throughput time in days between Activity A and Activity B excluding weekend days and holidays.
Holidays.csv
file to define the days that should be counted as holidays. The file should at least contain a record for each holiday. Use
the following format:
Holiday |
Date |
Weekday |
New year's day |
2024-01-01 |
Yes |
Easter |
2024-03-31 |
No |
.. |
.. |
.. |
Holidays.csv
file are used to count the number of days that need to be excluded exclude from a date range.
Holidays.csv
file as a seed file in the dbt project. For detailed information, see the official dbt documentation on jinja seeds.
date_from_timestamp
and the diff_weekdays
functions provided in the pm-utils package as described above in Calculating throughput time in days excluding weekends.
.csv
file that fall within the given date range for each case. See the example code below.
Holidays_count as (
select
Total_days_minus_weekends."Case_ID",
count(Holidays."Date") as "Number_of_holidays"
from Total_days_minus_weekends
left join Holidays
on Holidays."Date" between Total_days_minus_weekends."Date_activity_A" and Total_days_minus_weekends."Date_activity_B"
where Holidays."Weekday" = 'Yes'
group by Total_days_minus_weekends."Case_ID"
)
Holidays_count as (
select
Total_days_minus_weekends."Case_ID",
count(Holidays."Date") as "Number_of_holidays"
from Total_days_minus_weekends
left join Holidays
on Holidays."Date" between Total_days_minus_weekends."Date_activity_A" and Total_days_minus_weekends."Date_activity_B"
where Holidays."Weekday" = 'Yes'
group by Total_days_minus_weekends."Case_ID"
)
Weekday = 'Yes'
is used to not subtract holidays when the holiday is on a Saturday or a Sunday. This is already taken care of in the diff_weekday
function.
5. Subtract the computed number of holidays from the total number of days computed for each case. See the example code below.
Total_days_minus_weekends_and_holidays as (
select
Total_days_minus_weekends."Case_ID",
Total_days_minus_weekends."Number_of_days" - Holidays_count."Number_of_holidays" as "Number_of_days_between_dates"
from Total_days_minus_weekends
inner join Holidays_count
on Total_days_minus_weekends."Case_ID" = Holidays_count."Case_ID"
)
Total_days_minus_weekends_and_holidays as (
select
Total_days_minus_weekends."Case_ID",
Total_days_minus_weekends."Number_of_days" - Holidays_count."Number_of_holidays" as "Number_of_days_between_dates"
from Total_days_minus_weekends
inner join Holidays_count
on Total_days_minus_weekends."Case_ID" = Holidays_count."Case_ID"
)
Once the throughput time table is created this table needs to be joined to the Cases table to add the additional throughput time data as case information. To have the new throughput time field available in the dashboards, it is necessary to cast the new throughput time field to one of the custom case duration fields.
Replace one of the custom case duration lines in the Cases table that look like this:
{{ pm_utils.optional(ref('Cases_base'), '"custom_case_duration_1"', 'integer') }} as "custom_case_duration_1",
{{ pm_utils.optional(ref('Cases_base'), '"custom_case_duration_1"', 'integer') }} as "custom_case_duration_1",
with the newly created throughput time:
Cases_with_throughput_times."Throughput_time_activity_A_to_activity_B" as "custom_case_duration_1",
Cases_with_throughput_times."Throughput_time_activity_A_to_activity_B" as "custom_case_duration_1",
The updates to the transformations for the custom throughput time metric are now done and can be imported into the app template.
When you have created a custom throughput time in your transformations it is available in your app template as a case property under its alias. You can customize your process app to create a throughput time metric based on the custom throughput time you created in the transformations.
By default, a new custom duration field is added as a field of type numeric. Make sure to edit the field and change the Type of the new field to duration. See also Data manager.
- Go to Data manager and create a new metric.
- Select the custom duration field to be used for the throughput time, and select Average or any other desired aggregation. You can also rename the custom case duration field to your desired name in the Data Manager.
- Edit the application and put the new metric on the charts where you want to make it available for business users.
- Publish the dashboards to make the throughput time metric available on the dashboards.
In Purchase-to-Pay and Order-to-Cash app templates a throughput time calculation is already available in the Purchase_order_items_with_throughput_times and Sales_order_items_with_throughput_times, respectively. Custom throughput times can be added there and then be made available as a custom duration on the Purchase_order_items or Sales_order_items.