Apache Spark (PySpark) transformation

The Apache Spark (PySpark) transformation is used in the enterprise version of process.science for Power BI to prepare the data. The package is designed so that the data can be transformed directly in the Power BI report. It creates four tables that can be used for analysis in Power BI.

Preparing the input table

Before the scripts can be used, you must ensure that your input table fulfils the following requirements. The column names for case_id, activity and timestamp are fixed and must be present in your input table.

Field Name	Description	Data Type	Mandatory
case_id	Through this field the process instances can be differentiated.	nvarchar	✓
activity	This field describes the activity that has taken place.	nvarchar	✓
activity_grouped	This field describes the group of the activity that has taken place.	nvarchar	✗
timestamp	This field describes when an event has taken place.	datetime2	✓
timestamp_end	This field can be used to calculate the duration of activities.	datetime2	✗
sort_key	This field can be used to adjust the sorting of the event log.	numeric	✗
value	This allows you to set the costs for the current event.	numeric	✗
automated	This allows you to set wether the activity was automated or not.	numeric	✗
resource	This allows you to set user information for each activity.‚	Any	✗
Additional Activity Values	Further information per activity, e.g. quantity of items. All fields, not matching other names will be mapped as additional activity values and will be stored in the newly created event log table.	Any	✗
Additional Cases Values	Further information per case, e.g. a company division. All fields starting with case_ will be mapped as additional cases values and will be stored in the newly created cases table.	Any	✗
Additional Activity Values Grouped	Further information per activity group, e.g. quantity of items. All fields ending with _grouped will be mapped as additional activity group values and will be stored in the newly created event log table.	Any	✗

Downloading the scripts

The scripts can always be downloaded in the latest version from our Self Service Portal. The scripts are contained in a ZIP file and must be unpacked.

Installation and configuration

Customisations for databases used

The scripts are designed to work with most databases. However, it is necessary to adapt them for the appropriate databases. The jar files for the database drivers must be specified in the upper part of the script. These can usually be downloaded from the database provider's website.

    spark = SparkSession.builder
            .config('spark.jars', '/Users/jars/spark-mssql-connector_2.12-1.2.0.jar, /Users/jars/mssql-jdbc-11.2.1.jre8.jar')
            .getOrCreate()

The connection data for the database must also be adjusted.

	## TODO Set the MS SQL parameters
    server_name = "jdbc:sqlserver://xy.database.windows.net:1433"
    database_name = ""
    url = server_name + ";" + "databaseName=" + database_name + ";"
    table_name = "dbo.eventlog_en"
    username = ""
    password = ""

Event log

Steps 1 - 3 are required to create the event log table. A temporary table containing the data from the input table is created in these steps. This is then converted into the final table. If the grouped activities are used, the corresponding columns must be present in the input table.

All additional columns that do not start with case_ or end with _grouped are saved in the eventlog table. All additional columns with the suffix _grouped are saved in the eventlog_grouped table.

Filter tables

Steps 4 - 6 are required to create the filter tables. In these, a temporary table is created that contains the data from the input table. This is then converted into the final table.

Activity group mapping

Step 7 is required to create the activity group mapping table. This is required when working with activity groups.

Cases table

Step 8 is required to create the case table. All additional columns beginning with case_ are stored in the cases_data table.

Writing the data to the database

In the last step, the data is written to the database. Here, if necessary, the function write_to_sql_server must be adapted for writing to the database; in the example below, the target is a Microsoft SQL database.

    ## Write tables to MS SQL,
    def write_to_sql_server(df: any, output_name: str):,
        df.write.mode("overwrite")
                .format("jdbc")
                .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
                .option("url", url)
                .option("dbtable", output_name)
                .option("user", username)
                .option("password", password)
                .save()