Co-author – Srihari Ananda Kumar  

Microsoft Power BI is a unified, scalable BI platform that gives users capabilities for collecting, analyzing, visualizing, and sharing data. Power BI's user interface is fairly simple, allowing users to visualize any data and seamlessly integrate the visuals into the applications they use every day.  

Building a flow in IBM App Connect with Microsoft Power BI

Use App Connect to build flows that integrate with Microsoft Power BI and other applications. The connector is displayed as Microsoft Power BI on the App Connect User Interface (UI). 

To allow App Connect to connect to your Microsoft Power BI account, you need to complete the connection fields that you see in the App Connect Designer Catalog page or flow editor.

For more information on connection fields, see How to use App Connect with Microsoft Power BI.

Microsoft Power BI objects

The following are the Microsoft Power BI objects that can be run in App Connect.

Scenario 1: Retrieve leads from Insightly and add the lead data to Microsoft Power BI

Consider this scenario where you use App Connect to create a scheduler-based flow that retrieves leads from Insightly. In this example, a JSON parser is used to convert the Insightly lead data from a JSON string into a JSON object. The output schema that is generated exposes the lead object's properties and is added to the Microsoft Power BI dataset.

Before you begin:

To use this template, you need to ensure that the lead columns are created in the Microsoft Power BI push dataset.

For example, in this flow, the output schema that is generated exposes the lead object's properties and is added to the Microsoft Power BI dataset. We need to create these lead object's properties in Microsoft Power BI, such as LEAD_ID, FIRST_NAME, LAST_NAME, TITLE, ORGANISATION_NAME, ADDRESS_STATE, EMAIL, INDUSTRY, LEAD_STATUS_ID, and so forth.

For more information on push datasets, see Real-time streaming in Power BI.

In this flow:

1. You run a scheduler-based flow that retrieves leads from Insightly.
2. A Set variable node is added to the flow to set the variable jsonstring to the Insightly lead.
   
   For example:
   
   
   
3. A JSON Parser node from the App Connect toolbox is used to parse the Insightly lead data from a JSON string into JSON objects.
   
   
   The output schema that is generated exposes the lead object's properties.
   
   
4.  A second Set variable node is added to the flow to set the variable FinalOutput to the Parsed JSON, which contains the lead properties.
   
   The lead object's properties, such as LEAD_ID, FIRST_NAME, LAST_NAME, TITLE, ORGANISATION_NAME, ADDRESS_STATE, EMAIL, INDUSTRY, LEAD_STATUS_ID, and so forth, are mapped as input to the variable FinalOutput.
   
   Note: Make sure that the lead object's properties columns are created in the Microsoft Power BI push dataset.
   
   For example:
   
   
   
5. The mapped lead properties are now added to the Microsoft Power BI dataset by using the Insert multiple rows operation.
   
   For example:
   
   
   
   Optionally, you can also use other CRM services, such as Salesforce. You can retrieve the lead records from Salesforce and parse the data into Microsoft Power BI.
   
   You can also perform a competitive analysis by using CRM applications such as Insightly and Salesforce in the same flow.
   
   

Resources

Try out our templates

You can view the following use cases in the Templates gallery in your App Connect Designer instance.

•         Retrieve leads from Insightly and add the lead data to Microsoft Power BI
  Template URL: https://<your-instance-id>/templates/Retrieve%20leads%20from%20Insightly%20and%20add%20the%20lead%20data%20to%20Microsoft%20Power%20BI
  
  
• Send a Gmail message when the Microsoft Power BI target goal value is either achieved or failed
  Template URL: https://<your-instance-id>/templates/Send%20a%20Gmail%20message%20when%20the%20Microsoft%20Power%20BI%20target%20goal%20value%20is%20either%20achieved%20or%20failed
  
  
• Export the report from Microsoft Power Bi and create the file in Microsoft OneDrive for Business
  Template URL: https://<your-instance-id>/templates/Export%20report%20from%20Power%20Bi%20and%20create%20the%20file%20in%20Microsoft%20OneDrive%20for%20Business

You must enable the Designer AI features in your containerized environment in order to access the App Connect templates. For more information, see The preloaded IBM App Connect templates.

You can also import these use cases directly into your App Connect Designer. These templates are added to a public GitHub repository at https://github.com/ot4i/app-connect-templates/tree/cp4i-templates/resources.

For more information about the supported connectors, see App Connect Connectors.