Skip to content

Azure Transformation

What is Azure Transformation

The PAFnow Azure Transformation is an Azure Marketplace offer, intended to simplify your SQL data transformation. Like the SSIS Package, Azure Transformation is used to automatically generate a PAFnow Data Model from your event log.

You will be able to use the Azure Transformation once you have an active PAFnow Enterprise licence and having stored your event log on a Azure hosted service like SQL Server on Azure Virtual Machines, Azure SQL Managed Instance or an Azure SQL Database.

Attention

If you are using an older version of Microsoft SQL Server (e.g. 2017 or lower), please refer to the SSIS section of this documentation.

How it works

With Azure Transformation, a Docker container is deployed on Azure that acts as a manager for your transformation. The container then validates your credentials and connects to your database, where it coordinates the transformation.

Just enter your settings and let Azure Transformation do the rest.

Note

After the transformation is complete, you may delete the created resource.

How to use the Azure Transformation

Using our Azure Transformation works as follows:

  1. Review our Azure Marketplace offer here

  2. Click on Get It Now.

  3. A pop-up window will appear asking for your consent. By clicking Continue, you agree to the terms and will be redirected to the Azure Portal.

  4. Click on Create.

  5. Fill out the first step of the wizard by selecting a resource group for deployment under Resource Group, a preferred region under Region, and a unique container name under Container Name.

    Resource

    Note

    Due to restrictions of Azure, you will deploy the Azure Transformation to an empty resource group.

  6. Proceed to the next tab and enter your PAFnow credentials there.

    Credentials

    Attention

    Your password is not directly validated in the user interface. Double check that you have not made any mistakes, to prevent the additional work of having to do it again.

  7. Switch to the next tab and enter the details of your SQL instance connection.

    SQL

    Getting the FQDN

    If you have an Azure SQL Server but do not know its connection details, check the resource in the Azure Portal. On the overview page, you will find the Fully Qualified Domain Name under Server Name

    It should look something like this: pafnow-azure-sqlserver.database.windows.net.

  8. Proceed to the next step and enter your transformation settings.

    SQL

    • EventLogTable (mandatory): Specify the name of the database table which contains the event log.
    • SchemaIn (mandatory): Specifies the table schema where the event log is located.
    • CaseAttributesTable (optional): If you have case attributes in a seperate table, specify their name here.
    • SchemaOut (optional): Specifies the table schema where the output tables should reside.
    • TablePrefix (optional): Specifies a table prefix, e.g., DepartmentX_EventLog which will be used for all created tables.

  9. Continue to the next step, double-check all the settings, and then click Create to start the transformation.

  10. Check the newly created Docker container in the Azure Portal. Navigate to the resource group where you deployed the package and switch to the new container group.
    On the left side tabs, select Containers and then click Logs.

    Info

    Check the last message in the log. If you see the message [Main] Finished transformation. Cleaning up, it means that the transformation was successful.

  11. When you see [Main] Finished transformation. Cleaning up in the log of when the docker containers state has switched to Terminated you may delete the container group from the Azure Portal.

To connect your transformed data please read SQL Data Import

Debugging

If you have problems with the transformation, please check the container log as described in step 8 of How to use Azure transformation. Most problems are caused by typos in credentials or connection details.

A typical error message might look like this: 

[ConnectionManager] Error connecting to the server.
Source: Core Microsoft SqlClient Data Provider
Number: 18456
State: 1
Class: 14
Server: paf-testing-sql.database.windows.net
Message: Login failed for user 'pafnow_user'.
Procedure:
LineNumber: 1
or like this: [Authentication] Authentication failed. Check your credentials

The following table will give a brief overview of the most common errors:

Message Cause
Login failed for user <databse user> There was a problem with the database users credentials. Double check if you entered them correctly.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. [...] Could not connect to the SQL Server instance. Check that you entered the FQDN correctly. (Are you using a non-standard port for your SQL Server?)
Cannot open server <server> requested by the login. Client with IP address <ip> is not allowed to access the server. The SQL Servers firewall does not permit access from this IP address. Check below on how to solve this problem.
[Authentication] Authentication failed. Check your credentials Check your PAFnow credentials.
[Authentication] Authentication failed. Insufficent access rights! It seems that your PAFnow account is not associated with a valid PAFnow Enterprise licence. If this is wrong, please contact support.
Problem with the property: EventLogTable does not exists The table name you specified does not exist in the given database. Check for typos.
Opening access to your SQL Server

If you don't personally have access to manage your companies Azure resources, please contact an administrator to do this for you:

  • Navigate to the resource hosting your SQL Database on the Azure Portal (If applicable: Don't select the database itself, but the server hosting it)
  • Go to the Firewalls and virtual networks tab
  • Either turn on Allow Azure services and resources to access this server or manually add the IP address specified in the error message to the rules down below
  • Save the changes and restart your Container Instance to retry connection

If you are reaching out to support, please provide a copy of the whole execution log in your inquiry.

Performance

The duration of the transformation proccess heavily depends on your server performance settings. If you are running a large dataset make sure to scale your Azure Resource to an adequate performance level. If you are using an Azure SQL Database we recommend to use Premium performance tier because of its greater IO performance. Choose an equivalent performance tier if you are running another type of SQL Server, paying special attention to providing sufficent IO throughput.