Wednesday, December 14, 2016

Use Case – Salesforce REST Connector + Power Bi connector

This page describes a connector scenario by integrating the Salesforce REST Connector with the Power Bi connector. For example you can use the Salesforce REST Connector to get the account details (Eg :- Id, Name, Account type of the records in your organization) from Salesforce and add that account details into table in the dataset of Power Bi using addRows operation in the Power Bi connector.


  1. Get the account details from the Salesforce using query operation.
  2. Add the account details into the Power Bi using the addRows operation.

Environment setup for running the above use cases

  • Download ESB 5.0.0 from here.
  • Upload the following connectors to the ESB:
  • Follow the instructions given in the developer guide of the above connectors.
  •  Import the certificate into the ESB client keystore to use the Salesforce REST connector. Refer this link to import the certificate.  
  • Creating the credentials to use the Power Bi Connector:
    1. Create a Office 365 E3 trial account.
    2. Navigate to the URL https://login.microsoftonline.com/login.srf and login with Microsoft account which is created in step 1 .
    3. Navigate to the URL https://powerbi.microsoft.com/en-us/ and signup for a Power Bi account using the previously created Microsoft account.
    4. Create a dataset and keep the dataset name for further use.
    5. Enable PowerBI pro by using the popup by selecting the "Create Group" option.
    6. Create a group in PowerBI.
    7. Navigate to the URL https://app.powerbi.com/apps.
             Step 1 - Login using Power BI account which used in step 2 .
             Step 2 - Provide App Name, Redirect URL, App Type and Home Page URL and for App Type use "Server-side Web app". Note Redirect URL for further use.
             Step 3 - select all the check boxes to enable access for the api.
             Step 4 - Click "Register App" and note client-id and client-secret that are returned for further use.
    8. Generate the authorization code by sending a GET request using url https://login.windows.net/common/oauth2/authorize?response_type=code&client_id=<client-id>&resource=https://analysis.windows.net/powerbi/api&redirect_uri=<Redirect URL>.
  • Creating the credentials to use the Salesforce REST Connector:

    Sample configuration


    Following is a sample proxy service that illustrates how to test the above scenario.

    Sample proxy


     <?xml version="1.0" encoding="UTF-8"?>  
     <proxy xmlns="http://ws.apache.org/ns/synapse"  
         name="importdataintoPowerbi"  
         startOnLoad="true"  
         statistics="disable"  
         trace="disable"  
         transports="http,https">  
       <target>  
        <inSequence>  
          <property expression="json-eval($.salesforceRESTHostName)"  
               name="salesforceREST.hostName"/>  
          <property expression="json-eval($.salesforceRESTRefreshToken)"  
               name="salesforceREST.refreshToken"/>  
          <property expression="json-eval($.salesforceRESTClientSecret)"  
               name="salesforceREST.clientSecret"/>  
          <property expression="json-eval($.salesforceRESTClientId)"  
               name="salesforceREST.clientId"/>  
          <property expression="json-eval($.salesforceRESTAccessToken)"  
               name="salesforceREST.accessToken"/>  
          <property expression="json-eval($.salesforceRESTQueryString)"  
               name="salesforceREST.queryString"/>  
          <property expression="json-eval($.salesforceRESTApiVersion)"  
               name="salesforceREST.apiVersion"/>  
          <property expression="json-eval($.salesforceRESTApiUrl)"  
               name="salesforceREST.apiUrl"/>  
          <property expression="json-eval($.salesforceRESTIntervalTime)"  
               name="salesforceREST.intervalTime"/>  
          <property expression="json-eval($.salesforceRESTRegistryPath)"  
               name="salesforceREST.registryPath"/>  
          <property expression="json-eval($.powerbiAccessToken)" name="powerbi.accessToken"/>  
          <property expression="json-eval($.powerbiRefreshToken)"  
               name="powerbi.refreshToken"/>  
          <property expression="json-eval($.powerbiClientId)" name="powerbi.clientId"/>  
          <property expression="json-eval($.powerbiClientSecret)"  
               name="powerbi.clientSecret"/>  
          <property expression="json-eval($.powerbiApiUrl)" name="powerbi.apiUrl"/>  
          <property expression="json-eval($.datasetId)" name="powerbi.datasetId"/>  
          <property expression="json-eval($.tableName)" name="powerbi.tableName"/>  
          <property name="fields" scope="operation" value="["/>  
          <property name="length" scope="operation" value=""/>  
          <salesforcerest.init>  
           <apiVersion>{$ctx:salesforceREST.apiVersion}</apiVersion>  
           <hostName>{$ctx:salesforceREST.hostName}</hostName>  
           <accessToken>{$ctx:salesforceREST.accessToken}</accessToken>  
           <refreshToken>{$ctx:salesforceREST.refreshToken}</refreshToken>  
           <clientSecret>{$ctx:salesforceREST.clientSecret}</clientSecret>  
           <clientId>{$ctx:salesforceREST.clientId}</clientId>  
           <apiUrl>{$ctx:salesforceREST.apiUrl}</apiUrl>  
           <intervalTime>{$ctx:salesforceREST.intervalTime}</intervalTime>  
           <registryPath>{$ctx:salesforceREST.registryPath}</registryPath>  
          </salesforcerest.init>  
          <salesforcerest.query>  
           <queryString>{$ctx:salesforceREST.queryString}</queryString>  
          </salesforcerest.query>  
          <property expression="//records" name="records"/>  
          <iterate continueParent="true"  
              description=""  
              expression="//records"  
              id="records_iterator"  
              sequential="true">  
           <target>  
             <sequence>  
              <property expression="json-eval($.records)" name="records"/>  
              <property expression="get-property('operation','fields')" name="fields"/>  
              <script language="js">  
                   var records = mc.getProperty("records");                
                   var obj = eval ("(" + records + ")");  
                   var ans;  
                   var fields = mc.getProperty("fields");  
                   fields = fields.concat("{");               
                   var Id = obj['Id'];  
                   var Name = obj['Name'];  
                   var AccountType = obj.attributes['type'];  
                   if(Id !="" &amp;&amp; Id !=null &amp;&amp; Name !="" &amp;&amp; Name !=null){  
                     fields = fields.concat('"Id"' + ':"' + Id + '","Name"' + ':"' + Name + '","AccountType"' + ':"' + AccountType + '"');  
                   }  
                   fields = fields.concat("},");  
                   mc.setProperty("fields",fields);  
                   mc.setProperty("length",fields.length());  
              </script>  
              <property expression="get-property('fields')" name="fields" scope="operation"/>  
              <property expression="get-property('length')" name="length" scope="operation"/>  
             </sequence>  
           </target>  
          </iterate>  
          <property expression="get-property('operation','fields')"  
               name="fields"  
               scope="operation"/>  
          <property expression="get-property('operation','length')"  
               name="length"  
               scope="operation"/>  
          <property expression="fn:concat(fn:substring(get-property('operation','fields'),0,get-property('operation','length')),']')"  
               name="values"/>  
          <powerbi.init>  
           <accessToken>{$ctx:powerbi.accessToken}</accessToken>  
           <refreshToken>{$ctx:powerbi.refreshToken}</refreshToken>  
           <clientId>{$ctx:powerbi.clientId}</clientId>  
           <clientSecret>{$ctx:powerbi.clientSecret}</clientSecret>  
           <apiUrl>{$ctx:powerbi.apiUrl}</apiUrl>  
          </powerbi.init>  
          <powerbi.addRows>  
           <datasetId>{$ctx:powerbi.datasetId}</datasetId>  
           <tableName>{$ctx:powerbi.tableName}</tableName>  
           <rows>{$ctx:values}</rows>  
          </powerbi.addRows>  
          <property expression="$axis2:HTTP_SC" name="addRowsStatusCode"/>  
          <filter regex="true"  
              source="get-property('addRowsStatusCode') != 200">  
           <then>  
             <log level="full"/>  
             <property name="message" value="Unable to Insert the row "/>  
             <loopback/>  
           </then>  
           <else>  
             <log level="full"/>  
             <property name="message" value="Success: Successfully Import the Data"/>  
             <loopback/>  
           </else>  
          </filter>  
          <respond/>  
          <send/>  
        </inSequence>  
        <outSequence>  
          <property name="messageType" scope="axis2" value="application/json"/>  
          <payloadFactory media-type="json">  
           <format>  
               {  
               "Response":{  
               "process":"ImportDataIntoPowerBi",  
               "activityResponse":"$1"  
               }  
               }  
             </format>  
           <args>  
             <arg evaluator="xml" expression="get-property('message')"/>  
           </args>  
          </payloadFactory>  
          <log/>  
          <send/>  
        </outSequence>  
       </target>  
       <description/>  
     </proxy>
    
    Sample request


    {  
      "powerbiAccessToken": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6Ikk2b0J3NFZ6QkhPcWxlR3JWMkFKZEE1RW1YYyIsImtpZCI6Ikk2b0J3NFZ6QkhPcWxlR3JWMkFKZEE1RW1YYyJ9.eyJhdWQiOiJodHRwczovL2FuYWx5c2lzLndpbmRvd3MubmV0L3Bvd2VyYmkvYXBpIiwiaXNzIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvNDg1NWRjNDEtNmU4MS00YTdkLWJhOGYtZGM5NGNiMGI3NTE3LyIsImlhdCI6MTQ3OTAzMTg1MywibmJmIjoxNDc5MDMxODUzLCJleHAiOjE0NzkwMzU3NTMsImFjciI6IjEiLCJhbXIiOlsicHdkIl0sImFwcGlkIjoiNTAyMDgxM2QtOTgzMC00NDk0LWE4MjYtYTU3MDVhMmIxZTRhIiwiYXBwaWRhY3IiOiIxIiwiZmFtaWx5X25hbWUiOiJHbmFuZXN3YXJhbiIsImdpdmVuX25hbWUiOiJCaXJ1bnRoYSIsImlwYWRkciI6IjE3NS4xNTcuMjQ0LjIiLCJuYW1lIjoiQmlydW50aGEgR25hbmVzd2FyYW4iLCJvaWQiOiIzZWM1MDc0ZC1jOTVhLTRmZWQtOWEzZS1hNTM0YzE1ZjE5YzQiLCJwbGF0ZiI6IjE0IiwicHVpZCI6IjEwMDNCRkZEOUJGM0ZGQ0EiLCJzY3AiOiJEYXNoYm9hcmQuUmVhZC5BbGwgRGF0YXNldC5SZWFkV3JpdGUuQWxsIEdyb3VwLlJlYWQgUmVwb3J0LlJlYWQuQWxsIiwic3ViIjoidG9ZaTAteFNDT3hYSTV4eTB2QkZuWmdzTjc5N2hXV3l6YWxBQnJISVF0USIsInRpZCI6IjQ4NTVkYzQxLTZlODEtNGE3ZC1iYThmLWRjOTRjYjBiNzUxNyIsInVuaXF1ZV9uYW1lIjoiYmlydW50aGFAZ25hbmVzLm9ubWljcm9zb2Z0LmNvbSIsInVwbiI6ImJpcnVudGhhQGduYW5lcy5vbm1pY3Jvc29mdC5jb20iLCJ2ZXIiOiIxLjAiLCJ3aWRzIjpbIjYyZTkwMzk0LTY5ZjUtNDIzNy05MTkwLTAxMjE3NzE0NWUxMCJdfQ.f5HoxDyvi3EcjmKzUCzKobq1jjGECm68bfbGAn72SXOOwqYG9IkvzOuhR_4UL0BSp-E7wex9MqkrGUK0b0rJ1awg8kF7Y5ICjnIiuEHK7jLJf5DEEMb91tBRo3mMgBrri_UR7V0MIBG5IRMtbBiAIZCn-dZw9izCc3pOb9J3rx7ifMdda5frKqF3gQXUGl_814PXQia5EGxcTAfVcBbvcgTZ7FQjzidgI0qzoirNtZshrFzAqj7N1_2WpOlVC-XugA4Oi5vOcm2A3LHZYhYrk1GoqJga8liyUALWDyrKsvyPHGO9dEoYFVCgBK-48envJWlzOO987rI6MACWz8QmIg",  
      "powerbiRefreshToken": "AQABAAAAAADRNYRQ3dhRSrm-4K-adpCJ0Bc0NMKTefb1M1UhZxzmA3v3afmAGpsz_3ISKe49U594Luh4vS53BoCWlAG0vlAv3y5gwhtln1smhbBM6-XZSjYvhgYrGMExiCnX92VPbqI61kYOnOrLtQ8MypdDMdoEotp0VIw3wMV-Pa9qU2J7DhPG0_r7V1b-ZqhQy0PZRRH7RTHelDLpCGsKHYNTqdgFH10GO8JOCHDwImXO5uMBgrxkI9hsAgYw-kc4HvSX8Ah8HXjfHGD3i90Rc79J0mA0eeL4knNkEKyBuXE7CVrEyFUKk6ijybCneHRUOC2_ejY2C04W3S4Us7TlC8rLOAo3Tz6JTF5gpTlmJ7QSF5k_7yiIT0ZmnloVfzCorkqxpcVX0rheQ3b83zX7I4VnEjsdUlIsvcV4QXPbQTcuNRsmEKZHErRph_8F-eG-gxUOBh4DWMss8FPylD8_tioscFIcMhC7aOXglOVnUNESFgJhcp8L0duu64Twn_9b1mCSlMXw8bM8ASm7QabHTnxXGmQHCGmehtd3Df9dIaAbXjwtwkxOSJjAlvGdjGoThO1IJ9gO5fMhaCRicfC8rkVQVsWaP_-sgeZtMFKyItT43C3CiWhPLQbKYwuOeJvWrHtw6z4A4oKPLJ-ALwMBDj8ffkiKjtdAHvpT5K3NQeVcMCkJnvkinLfSUo_--GSoxeWmJpktAhVIv8f8MB-TLUXc5DhufPUrZs1ofPTTZegzild9e-aob_K95zE_tzbvJa_cDF8gAA",  
      "powerbiClientId": "5020813d-9830-4494-a826-a5705a2b1e4a",  
      "powerbiClientSecret": "I+mhnYnSKqnsZmORLtPO5vbJCSnDRdP9J3xUyTPez24=",  
      "powerbiApiUrl": "https://api.powerbi.com",  
      "datasetId": "dd2defac-d062-4262-a9a9-00c1071723b8",  
      "tableName":"Records",  
      "salesforceRESTHostName": "https://login.salesforce.com",  
      "salesforceRESTRefreshToken": "5Aep861TSESvWeug_xCxj8yFaTjeV0kXa8X.gg5d1ipArZn7qABXj_BUgpsSVDvRfMNJtYwqnie1JejvB_GrLnJ",  
      "salesforceRESTClientSecret": "3543063387981089204",  
      "salesforceRESTClientId": "3MVG9ZL0ppGP5UrDt0n2ptTJ_fWBXDlYYXTic9.ApotX3azPQhMX2dw_gXGQ1JyPwTLWjrnCB.0K3vpUJV3Jv",  
      "salesforceRESTAccessToken": "00D28000000avoo!ARoAQGVqGhimwqewU5TNG1Uf4geWWRx9gV13sDffT1zXFHY_mAl158PI1wqfiAsH6uMOJfp5kzps0ERHXxTXpY8GWfNIltID",  
      "salesforceRESTQueryString": "select id, name from Account limit 4",  
      "salesforceRESTApiVersion": "v32.0",  
      "salesforceRESTApiUrl": "https://ap2.salesforce.com",  
      "salesforceRESTIntervalTime": "2400000",  
      "salesforceRESTRegistryPath": "connectors/salesforcerest"  
     }
    

No comments:

Post a Comment