Microsoft Dataverse

Microsoft Dataverse is secure, cloud-based table storage for business applications, such as those belonging to the Microsoft Power Platform (Power BI, Power Apps, Power Automate, Power Virtual Agents, and Power Pages), Microsoft 365, Microsoft Azure, Dynamics 365, and other standalone applications.

This topic describes the steps that are required to send table rows that match a table schema to Microsoft Dataverse from Amperity:

  1. Get details

  2. Configure your Microsoft application

  3. Add webhook destination

  4. Add data template

How this destination works

Use the Microsoft Dataverse destination to send rows of data from Amperity, and then add them to tables in Microsoft Dataverse. Use a Lambda function to validate each row of data that is sent from Amperity against the table schema in Microsoft Dataverse. Only rows that validate against the table schema will be added to the table.

Send rows of data from Amperity to Microsoft Dataverse.

A Microsoft Dataverse destination works like this:

  1. Use a query to build a table with rows of data that match the schema of a table in Microsoft Dataverse. The column names, data types, and column order must match the schema that is defined in Microsoft Dataverse.

  2. Configure a webhook destination to send data to a Lambda function that is running in AWS Lambda.

  3. Deploy a Lambda function to AWS Lambda. The Lambda function may be deployed as a serverless application (recommended) or it may be a custom Lambda function that you have built using the open source toolkit provided by Amperity, and then uploaded to AWS Lambda.

  4. Configure the Lambda function to run against the data that is sent from Amperity. The Lambda function will ensure that each row of data sent from Amperity matches the schema of the table in Microsoft Dataverse.

  5. The Lambda function sends validated results to Microsoft Dataverse.

Get details

Amperity can be configured to send data to Microsoft Dataverse.

  1. Microsoft Azure must be configured for your application, after which that application must be added to the Microsoft Power Platform.

  2. Microsoft Dataverse must be enabled in your Amazon AWS account and be configured to send email, SMS, push, or in-app messages to your customers.

  3. A Lambda function must be uploaded to AWS Lambda that is running in your Amazon AWS account, and then configured to be available to Amperity.

  4. A webhook destination that is configured in your Amperity tenant that sends query results to a Lambda function running in AWS Lambda.

Configure your Microsoft application

To send data to Microsoft Dataverse, you need to configure an application in Microsoft Azure to create the application and directory IDs and a client secret, after which you must add your application to the Microsoft Power Platform.

To configure your application in Microsoft Azure

  1. Log in to your Microsoft Azure account.

  2. Open Azure Active Directory, and then App Registrations.

  3. Click New Registration, and then enter the name for your application. Leave all other settings as the default, and then click Register.

  4. The application overview page contains the following IDs:

    Application (client) ID

    Directory (tenant) ID

    Important

    You will need these IDs when configuring environment variables in AWS Lambda.

  5. From the left-side navigation, click Authentication, then Add a platform, and then Web.

  6. On the next page, in the Redirect URI field, enter: https://app.amperity.com.

  7. Under Implicit grant and hybrid flows, enable the Access tokens (used for implicit flows) option, and then click Configure.

  8. From the left-side navigation, click Certificates & Secrets, select the Client secrets tab, and then click New client secret.

    Important

    You will need the client secret when configuring environment variables in AWS Lambda.

  9. From the left-side navigation, click API Permissions, select Add a permission, select Dynamics CRM, select user_impersonation, and then click Add permissions.

To add your application to the Microsoft Power Platform

  1. Open the Microsoft Power Platform console , click Environments, and then click on your environment.

  2. Click Settings, tehn Users and permissions, and then Application users.

  3. Click New app user, and then select the application you just created, your business unit, and then under Security roles click the Edit button.

  4. Select the following settings:

    Service Reader

    Service Writer

    and then click Save.

  5. Click Create.

Configure Microsoft Dataverse

To configure Microsoft Dataverse, you must complete one of the following series of steps:

  1. Use the pre-built serverless application

  2. Manual configuration

Serverless application

The AWS Serverless Application Repository is a managed repository for pre-built serverless applications. Find an application, configure it, and then deploy it to your Amazon AWS account.

Amperity manages a pre-built serverless application for Microsoft Dataverse. Use this application to send rows of data from Amperity, and then add them to tables in Microsoft Dataverse.

To deploy a serverless application

  1. Log in to your Amazon AWS account.

  2. Open the AWS Serverless Application Repository .

  3. Search for the amperity-dataverse-runner , and then open it. On the “amperity-dataverse-runner” page, click Deploy to deploy the application to your Amazon AWS account.

  4. Open the AWS Lambda console, and then open the “amperity-dataverse-runner” Lambda function, and then click Configure.

  5. On the Configuration page, click Environment variables, and then click Edit.

  6. Add the following values for each key-pair:

    Key

    Description

    PA_ENV_NAME

    The unique name for your Power Apps environment. For example: “acme”.

    PA_ENV_REGION

    The region in which your data center is located. For example: “crm”.

    AZ_TENANT_ID

    The tenant ID for your instance of Microsoft Azure. For example: “123ab123-12ab-12ab-12ab”.

    AZ_CLIENT_ID

    The client ID for your instance of Microsoft Azure. For example: “789bc789-78bc-78bc-78bc”.

    AZ_CLIENT_SECRET

    The client secret for your instance of Microsoft Azure. For example: “xyz~xyz_xyz”.

    SINGULAR_TABLE_NAME

    The singular table name in Microsoft Dataverse. This is the table from which the Lambda function pulls the schema for validation. For example: “abc123_customer”.

    PLURAL_TABLE_NAME

    The plural table name in Microsoft Dataverse. This is the table to which the Lambda function will write data. For example: “abc123_customers”.

  7. Click Save.

Manual configuration

A manual configuration requires more steps than deploying a serverless application, but enables the use of a custom Lambda function and the ability to define inline policies.

To manually configure Microsoft Dataverse, do each of the following steps:

  1. Add basic function to your AWS Lambda console

  2. Get Lambda function template

  3. Upload function to AWS Lambda

  4. Configure function in AWS Lambda

  5. Add the API gateway

  6. Set AWS Lambda environment variables

Add function to AWS Lambda

Add a basic function to your AWS Lambda console using only the default function without any customization.

To add a function to AWS Lambda

  1. Log in to your Amazon AWS account.

  2. Open the AWS Lambda console, and then click Create function.

  3. Select Author from scratch.

  4. Under Basic information, specify the function name, runtime, and architecture. For example:

    Function name “Amperity function for Microsoft Dataverse”

    Runtime “Python 3.9”

    Architecture “x86_64”

  5. Click Save.

Get Lambda function template

Amperity provides a pre-built collection of Lambda functions that is available from an open-source repository on GitHub.

Note

Lambda functions are built using Python. Amazon AWS provides runtimes for Python that include an SDK, along with credentials from an AWS Identity and Access Management (IAM) role that you manage.

To get the Lambda function template

  1. Clone the open source amperity-lambda-runner repository or download it as a ZIP file.

  2. In the repo, navigate to “/src/lambdas/lambda_handlers/”, and then open “dataverse.py”.

  3. You may use this file directly (requiring only configuration updates) or you may customize it to support your desired workflow.

Upload function to AWS Lambda

You must upload the application code (and any dependencies) to AWS Lambda as a ZIP file archive.

To upload a Lambda function to AWS Lambda

  1. Add the application code and dependencies to a ZIP file archive.

    Tip

    To build the ZIP file for Microsoft Dataverse, open util/lambda-build.sh and update line 9 to:

    pip install msal -t build/
    

    and then run the following command from your amperity-lambda-runner directory:

    sh util/lambda-build.sh filename=dataverse.py
    

    This will create a ZIP file that contains the following files: dataverse.py, amperity_runner.py, and helpers.py.

  2. Open the AWS Lambda console, and then open the Code tab.

  3. Click Upload from, and then click ZIP file.

  4. Find the ZIP file archive that contains the application code (and any dependencies), and then click Upload.

  5. Click Save.

Configure function in AWS Lambda

AWS Lambda must be updated for the name of the Microsoft Dataverse application and to specify a timeout value.

To configure the Lambda function in AWS Lambda

  1. Open the AWS Lambda console, and then open the Code tab.

  2. Under Runtime settings, select Edit.

  3. Under Handler, add “app.dataverse”, and then click Save.

  4. In the AWS Lambda console, open the Configuration tab.

  5. Under General configuration, select Edit.

  6. Set the Timeout value to “15 min 0 sec”, and then click Save.

Add the API gateway

Amazon API Gateway provides tools for creating and documenting web APIs that route HTTP requests to AWS Lambda functions. An API gateway is required to use webhook destinations.

To add an API gateway

  1. Open the AWS Lambda console.

  2. Open the Lambda function for Microsoft Dataverse.

  3. Click Add trigger.

  4. From the drop-down menu, select “API Gateway”, and then select the following:

    Setting

    Value

    Intent

    Create a new API

    API type

    REST API

    Security

    API Key

  5. Click Add.

    You will need these values when configuring the webhook destination in Amperity.

Set AWS Lambda environment variables

Use AWS Lambda environment variables provide to the Lambda function details about Microsoft Dataverse, how the tables can be accessed, the table to use for schema validation, and the table into which rows will be added.

To set environment variables

  1. Open the AWS Lambda console.

  2. Open the Lambda function for Microsoft Dataverse.

  3. Click Configuration.

  4. On the Configuration page, click Environment variables, and then click Edit.

  5. Click Add environment variable, and then set the following key-value pairs:

    Key

    Description

    PA_ENV_NAME

    The unique name for your Power Apps environment. For example: “acme”.

    PA_ENV_REGION

    The region in which your data center is located. For example: “crm”.

    AZ_TENANT_ID

    The tenant ID for your instance of Microsoft Azure. For example: “123ab123-12ab-12ab-12ab”.

    AZ_CLIENT_ID

    The client ID for your instance of Microsoft Azure. For example: “789bc789-78bc-78bc-78bc”.

    AZ_CLIENT_SECRET

    The client secret for your instance of Microsoft Azure. For example: “xyz~xyz_xyz”.

    SINGULAR_TABLE_NAME

    The singular table name in Microsoft Dataverse. This is the table from which the Lambda function pulls the schema for validation. For example: “abc123_customer”.

    PLURAL_TABLE_NAME

    The plural table name in Microsoft Dataverse. This is the table to which the Lambda function will write data. For example: “abc123_customers”.

  6. Click Save.

Add webhook destination

Use a webhook destination to send query results from Amperity to a Lambda function running in AWS Lambda in your instance of Amazon AWS.

To add a destination

  1. From the Destinations tab, click Add Destination. This opens the Add Destination dialog box.

  2. Enter the name of the destination and a description. For example, “Microsoft Dataverse” and “Send table rows that match a table schema in Microsoft Dataverse”.

  3. From the Plugin drop-down, select “Webhook”.

  4. The “webhook” credential type is selected automatically.

  5. From the Credential drop-down, select a credential that has already been configured for this destination or click Create a new credential, which opens the Create New Credential dialog box. For new credentials, enter a name for the credential, the API key, and the webhook URL. (The webhook URL is the endpoint for the API gateway.) Click Save.

    Tip

    The API key and webhook URL settings are available from your AWS Lambda console. Log in to your AWS Lambda console, and then click Configuration. The API key is in the Details section and the webhook URL is in the Triggers section.

    For example:

    API key AB1c2D3eFGH4j5KL6

    Webhook URL https://acme.execute-api.us-east-123.amazonaws.com/Prod/run/

  6. Select Allow customers to use this destination.

  7. Select Allow orchestrations from users with limited PII access. (A user with limited PII access has been assigned the Restrict PII Access policy option.)

  8. Click Save.

Add data template

A data template defines how columns in Amperity data structures are sent to downstream workflows. A data template is part of the configuration for sending query and segment results from Amperity to an external location.

You can configure Amperity to send query results to Microsoft Dataverse. These results are sent using an orchestration and will include all columns that were specified in the query.

To add a data template for orchestrations

  1. From the Destinations tab, open the menu for a destination that is configured for Microsoft Dataverse, and then select Add data template.

    This opens the Add Data Template dialog box.

  2. Enter the name of the data template and a description. For example: “Microsoft Dataverse” and “Send table rows that match a table schema to Microsoft Dataverse.”

  3. Under Webhook settings, if Webhook Settings is empty, set it to empty curly braces: {}.

  4. Enable the Allow customers to use this data template option, and then enable the Make available to campaigns option. This allows users to send campaign results from Amperity to Microsoft Dataverse.

  5. Verify all template settings and make any required updates.

  6. Click Save.