PAFnow Data Model Specification¶
The PAFnow Data Model in a generic PAFnow Report consists of four tables: A transformed EventLog-table, a Cases-table, a FilterOr-table and a FilterAnd-table.
The first two tables in the data model consist of Default Columns, Power BI Calculated Columns, and Power BI Measures. Default Columns are calculated by means of an SQL Script, an SSIS Package, or the PAFnow Companion. Power BI Calculated Columns and Measures must be inserted into the reports by the report designer.
The last two tables are Power BI Calculated Tables that must be added by the report designer.
The column names of each table including descriptions and properties are explained below.
Event Log in PAFnow Format¶
This table contains event based information and is used to start various PAFnow Custom Visuals e.g. you can add ActivityName and ActivityNameTarget to start the Process Explorer.
Default Columns¶
| Folder | Name | Description | DataType |
|---|---|---|---|
| PAFnow Common Fields | ActivityName | The activity name of the event. | string |
| PAFnow Common Fields | ActivityNameTarget | The activity name of the next event. | string |
| PAFnow Common Fields | CaseId | The unique case identifier. | int64 |
| PAFnow Edge Attributes | Duration | The total lead time between two adjacent events in seconds. Includes activity execution time (if set). | int64 |
| PAFnow Edge Attributes | DurationIdle | The total idle time between two activities. Excludes activity execution time. If activity duration is not set in the event log, Duration and DurationIdle will be the same. | int64 |
| PAFnow Edge Attributes | Loop | Whether the current activity occurs for at least the second time in the case. Can be used in the PAFnow Process Explorer as an edge highlight. | int64 |
| PAFnow Edge Attributes | SelfLoop | Whether the next activity is the same as the current. | int64 |
| PAFnow Event Attributes | Automatic | Whether or not the event has been automatically executed. For instance, by an RPA bot. | int64 |
| PAFnow Event Attributes | Cost | A variable indicating cost per event. Has no implicit unit. Represents the Cost-column from EventLog. | double |
| PAFnow Event Attributes | DurationActivity | The total execution time of an activity. Is calculated from Timestamp to TimestampEnd. | int64 |
| PAFnow Event Attributes | EventNumber | The position of an event in the case. Start event will have number 1. | int64 |
| PAFnow Event Attributes | IsRework | Indicates that this activity already occurred earlier in the case. | int64 |
| PAFnow Event Attributes | Resource | The resource executing the event. Represents the Resource-column from the event log. | string |
| PAFnow Event Attributes | TimestampDate | The date on which the execution of the event has been started. See TimestampTime. | dateTime |
| PAFnow Event Attributes | TimestampDateEnd | The date on which the execution of the event has ended. If no end time is given in the event log this will be the same as TimestampDate, resulting in 0s execution time. | dateTime |
| PAFnow Event Attributes | TimestampTime | The time on which the execution of the event has been started. See TimestampDate. | dateTime |
| PAFnow Event Attributes | TimestampTimeEnd | The time on which the execution of the event has ended. If no end time is given in the event log this will be the same as TimestampTime, resulting in 0s execution time. | dateTime |
| PAFnow Next Event Attributes | TimestampTargetDate | The date on which the execution of the next activity has started. See TimestampTargetTime. | dateTime |
| PAFnow Next Event Attributes | TimestampTargetDateEnd | The date on which the execution of the next activity has ended. If no end time is given in the event log this will be the same as TimestampDateEnd, resulting in 0s execution time. | dateTime |
| PAFnow Next Event Attributes | TimestampTargetTime | The time on which the execution of the next activity has started. See TimestampTargetDate. | dateTime |
| PAFnow Next Event Attributes | TimestampTargetTimeEnd | The time on which the execution of the next activity has ended. If no end time is given in the event log this will be the same as TimestampTimeEnd, resulting in 0s execution time. | dateTime |
Power BI Calculated Columns¶
| Folder | Name | Description | DataType |
|---|---|---|---|
| PAFnow Edge Attributes | Edge | Helper column for FilterAnd- and FilterOr-tables. | string |
| PAFnow Edge Attributes | IsLoopsOrSelfLoops | This calculated column puts a flag whether a activity has either a loop or a selfloop (1) or not (0). | int64 |
Power BI Measures¶
| Folder | Name | Description | DataType |
|---|---|---|---|
| PAFnow Common Fields | Variants | To enable the slider in the Process Explorer, drag this measure into the Variants-bucket of the Process Explorer. | string |
| PAFnow Measures | DurationAtActivityEnd | This measure is used in the PAFnow Lead Time Calculator. | double |
| PAFnow Measures | DurationAtActivityStart | This measure is used in the PAFnow Lead Time Calculator. | double |
| PAFnow Measures | Probability | Transition probability for one activity directly following another. Can be used in the PAFnow Process Explorer as an edge highlight or edge tooltip. | double |
Cases¶
As the name implies, this table contains case-related information with a cardinality of '1:n', where the CaseId is contained as a foreign key in the table EventLog.
This table holds summary information about the cases found in the event log.
You can extend the PAFnow Data Model and connect additional case attributes via the CaseId.
Default Columns¶
| Folder | Name | Description | DataType |
|---|---|---|---|
| PAFnow Case Attributes | AutomationRate | The average automation rate for a case based on the Automatic-column in the event log. | double |
| PAFnow Case Attributes | CaseId | The unique case identifier. | int64 |
| PAFnow Case Attributes | Duration | The total lead time in seconds. | int64 |
| PAFnow Case Attributes | DurationDays | The total lead time in days. | double |
| PAFnow Case Attributes | EndActivity | The last activity of each case. | string |
| PAFnow Case Attributes | EndDate | The date (day, month, and year) on which the case has ended. Also see EndDateMonth and EndTime. | dateTime |
| PAFnow Case Attributes | EndTime | The time on which the case has ended. Also see EndDate. | dateTime |
| PAFnow Case Attributes | HappyPath | If the Happy Path is Happy you will see all the variants that start with the most frequent start activity (Happy Start) and end with the most frequent end activity (Happy End). All other variants are Not Happy. | string |
| PAFnow Case Attributes | NumActivities | Distinct number of activity names. | int64 |
| PAFnow Case Attributes | NumEvents | Total number of events (process steps) in the case (i.e., a case with loops will have higher number of events than activity names). | int64 |
| PAFnow Case Attributes | NumLoops | Number of loops in the case. Self-loops do not count up this number. | int64 |
| PAFnow Case Attributes | NumResources | Number of resources of a case based on the column Resource of the EventLog. | int64 |
| PAFnow Case Attributes | NumSelfLoops | Number of self-loops for the case. Loops do not count up this number. | int64 |
| PAFnow Case Attributes | PathId | Relationship column for FilterAnd- and FilterOr-tables. | int64 |
| PAFnow Case Attributes | StartActivity | The first activity of each case. | string |
| PAFnow Case Attributes | StartDate | The date (day, month, and year) on which the case has started. Also see StartDateMonth and StartTime. | dateTime |
| PAFnow Case Attributes | StartTime | The time on which the case has started. | dateTime |
| PAFnow Case Attributes | TotalCost | The total cost of your case based on the column Cost from the EventLog. | double |
| PAFnow Case Attributes | VariantId | The unique order of activities in the case. Most frequent variant will be number 1. | int64 |
| PAFnow Case Attributes | VariantIsUnique | Whether the case variant is unique among all cases. Can be 1 (variant is unique) or 0 (variant is not unique). | int64 |
Power BI Calculated Columns¶
| Folder | Name | Description | DataType |
|---|---|---|---|
| PAFnow Case Attributes | EndDateMonth | The date (month and year) on which the case has ended. Also see EndDate and EndTime.This column is needed for the Report Server-version. | dateTime |
| PAFnow Case Attributes | GroupAutomationRate | The calculated column creates bins for the automation rate of cases. It can be used for visualizations, e.g. as an axis for a bar chart. | string |
| PAFnow Case Attributes | GroupDuration | The calculated column creates bins for the total lead time of cases. It can be used for visualizations, e.g. as an axis for a bar chart. | string |
| PAFnow Case Attributes | GroupProcessSteps | The calculated column creates bins for the total number of process steps of cases. It can be used for visualizations, e.g. as an axis for a bar chart. | string |
| PAFnow Case Attributes | HasLoopsOrSelfLoops | This calculated column puts a flag whether a case has either a loop or a selfloop (1) or not (0). | int64 |
| PAFnow Case Attributes | LongDuration | Cases with a duration in bin (g) of GroupDuration. | string |
| PAFnow Case Attributes | StartDateMonth | The date (month and year) on which the case has started. Also see StartDate and StartTime.This column is needed for Report Server-version. | dateTime |
| PAFnow Case Attributes | StartDateYear | The year on which the case has started. Also see StartDate and StartTime. This column is needed for Report Server-version. | int64 |
| PAFnow Case Attributes | VariantIsUniqueText | Whether the case variant is unique among all cases. Can be Yes (variant is unique) or No (variant is not unique). | string |
Power BI Measures¶
| Folder | Name | Description | DataType |
|---|---|---|---|
| PAFnow Measures | #Cases | The constant number of all cases in the event log. Does not change the number on any filter. | int64 |
| PAFnow Measures | #Events | The constant number of all events in the event log. Does not change the number on any filter. | int64 |
| PAFnow Measures | #Variants | The constant number of all variants in the event log. Does not change the number on any filter. | int64 |
| PAFnow Measures | AverageAutomationRate | The average automation rate for the whole process, based on the Automatic-column in the EventLog. | double |
| PAFnow Measures | AverageAutomationRatePrevMonth | The average automation rate in the previous month, based on the measure AverageAutomationRate. | double |
| PAFnow Measures | AverageNumResourcesPerStep | The average number of resources per step for the whole process, based on the Resource-column in the EventLog. | double |
| PAFnow Measures | AverageNumResourcesPerStepPrevMonth | The average number of resources per process steps in the previous month, based on the measure AverageNumResourcesPerStep. | double |
| PAFnow Measures | PercentCasesWith(Self)Loops | Percentage of your cases which have either loops or selfloops. | double |
| PAFnow Measures | PercentCasesWith(Self)LoopsPrevMonth | Percentage of your cases which have either loops or selfloops in the previous month based on the measure PercentCasesWith(Self)Loops. | double |
| PAFnow Measures | PercentCasesWithLongDuration | Percentage of your cases with a duration equal or longer as calculated in GroupDuration in category (g). | double |
| PAFnow Measures | PercentCasesWithLongDurationPrevMonth | Percentage of your cases with a duration equal or longer as calculated in GroupDuration from the previous month based on the measure PercentCasesWithLongDuration. | double |
| PAFnow Measures | PercentCasesWithManyProcessSteps | Percentage of your cases with process steps equal or higher as calculated in GroupProcessSteps in category (g). | double |
| PAFnow Measures | PercentCasesWithManyProcessStepsPrevMonth | Percentage of your cases with process steps equal or higher as calculated in GroupProcessSteps in category (g) in the previous month based on the measure PercentCasesWithManyProcessSteps. | double |
| PAFnow Measures | PercentSelectedCases | Percentage of your selected/displayed cases. It is used for the gauge visualizations, e.g., in the Discovery. | double |
| PAFnow Measures | PercentSelectedVariants | Percentage of your selected/displayed variants. It is used for the gauge visualizations, e.g., in the Discovery. | double |
| PAFnow Measures | PercentUniqueVariants | Percentage of your unique variants, i.e., variants with only one case. | double |
| PAFnow Measures | PercentUniqueVariantsPrevMonth | Percentage of your unique variants in the previous month based on the measure PercentUniqueVariants. | double |
| PAFnow Measures | Top1VariantCoverage | Percentage of your cases covered by the most frequent variant. If not filtered, variant with ID 1 is the most frequent. | double |
Power BI Calculated Tables¶
These Power BI Calculated Tables, explicitly FilterAnd and FilterOr, are necessary to use filter capabilities of the Process Explorer* and do not require further explanation.