With the introduction of the Microsoft Power Automate Connector, you can perform flat file integrations from external source systems with ease.

 

About this feature

Microsoft Power Automate is a cloud-based web service that enables you to create automated data integrations between a source system and Vena. Connectors function as a proxy around an API that allows the source to communicate with the destination (Vena). More specifically, Power Automate allows you to connect with external source systems that are not currently available through Vena's traditional integration methods. The Microsoft Power Automate connector may be used to import data on an ongoing basis. For example, when a new file is added to Dropbox, an automated workflow can instantly bring a copy of the file into the Vena cube. In the Microsoft store, you'll find the Vena ETL connector, which is designed using a trigger and an action pairing. When a specific trigger occurs, the pre-defined actions are to follow. In the context of Microsoft Power Automate, a "trigger" is an event that occurs in your connector's target application. Examples of triggers that may be used include Dropbox, Google Drive, etc. Whenever a trigger is prompted, a pre-defined action is performed in another application. In this instance, the action is to prompt an ETL job in Vena.

To learn how to export data from Vena with Microsoft Power Automate Connector, see this article. 

 

Before you begin

To follow the instructions in this article, you will need at least Admin access. If Data Permissions are set up in your environment, you will also need the appropriate permissions for the data that you are working with.

 

Table of contents

• How to
  • Set up application token
  • Restrict Permissions
  • Set up Microsoft Power Automate connector
  • Monitor your connector
• Frequently Asked Questions

 

How to

Before setting up your Microsoft Power Automate Connector, you must first create a corresponding application token inside Vena. We recommend always creating the token first before enabling Microsoft Power Automate.

 

Set up application token

Log into Vena.io and navigate to Admin.

Select the Application Tokens tab and navigate to the bottom left-hand side of the screen and select +Add Application Token:

In the pop-up window, enter a name for your new application token and selecrt Save:

Once you have created the token it will be added to the Application Token list. Navigate to your newly created token (it may be on the last page) and select the name to highlight the token. Navigate to the top of the screen and select Show Token:

This will prompt a pop-up window to open that will include your token credentials. You can reference these credentials at any time by selecting the Show Token button:

Restrict Permissions 

If you have multiple data models, it is recommended that you restrict the permissions of the account to only access one data model. You can do this by restricting both application permissions and data permissions.

 

To restrict application permissions:

Select the +Add Permission button in the Application Permissions list on the right-hand side of the screen. A pop-up window will open and you will be prompted to set the permissions.

A.) In the Select Operation drop-down, select Update.

B.) In the Select Type drop-down, select Model.

C.) Select the model to which you wish to apply the permission.

D.) In the Select Dimension drop-down, select All(*).


Select Add to add the permission. Once you return to the home screen, highlight the Update All (*) permission and select Delete:

To restrict data permissions:

Select the +Add Permission button in the Data Permissions list on the right-hand side of the screen. A pop-up window will open and you will be prompted to set the permissions.

A.) Select the model to which you wish to apply the permission. 

B.) In the Select Operation drop-down, select the type of data permission you wish to add (e.g., Write/Read). 

Select Add to add the permission.

 

Once you are finished modifying your permissions you are ready to set up the Microsoft Power Automate Connector.

Set up Microsoft Power Automate connector

Log into https://flow.microsoft.com

Select My Flows on the left-hand side of the screen then select +New at the top of the screen;  select Create from blank:

 

Select the Create from blank button on the screen:

Next, you will search for a trigger that works with the Vena ETL Connector. Triggers that align with the Connector include:

• Box
• One Drive
• DropBox
• Sharepoint
• Google Drive
• STFP

Once you have selected your trigger, the second step is to search for the desired action. Select the Action tab and search for Vena. 



After selecting the Vena Connector, you will be prompted to enter the API username and API key that you sourced from Vena:



Once you have identified the file and action, the next step is to configure the ETL Connector. Select the Vena Model and ETL Template (based on those in your Vena environment) that you would like to connect then select Save:

You can test your connector by selecting Test to trigger the action:

 

Monitor your connector

Once you have finished creating your connector, you can check the status of it at any time by selecting “My Flows” and selecting your flow where you will be able to see the Run History of the flow to view any errors.

 

You can also view the resulting Data Load in the ETL history in Vena. Simply navigate to the Modeler tab > Data Modeler > ETL. Select View Details to download and view the contents of the file that was uploaded:

If you need to delete or pause your flow, navigate to My Flows, select your flow and select Turn Flow Off (pause) your flow or Delete (delete):

 

 

 

Frequently Asked Questions

What is Microsoft Power Automate?

Microsoft Power Automate is a cloud-based web service that allows you to create automated data integrations between a source system and Vena. Connectors function as a proxy around an API that allows the source to communicate with the destination (Vena).

 

Where can I monitor the progress of Integrations?

You can track connections both in Microsoft Power Automate via the My Flows tab; you can also monitor the progress of connectors in Vena.io by access the ETL Jobs tab in the Modeler.

 

What triggers are compatible with Vena?

The following triggers can be connected with Vena using the Microsoft Power Automate connector:

• OneDrive
• SharePoint
• Dropbox
• Box
• GoogleDrive
• SFTP

 

 

 

Questions? Comments? Reach out to us directly at support@venasolutions.com.
(Please include the link to the article for reference.)