Overview

The PowerBI Connector App integrates with Freshchat/CSS providing admins capability to export reporting data to a streaming dataset in PowerBI

The app includes three options:

  1. Ability to export data at a daily frequency
  2. Ability to export data at a weekly frequency
  3. Ability to export data at a monthly frequency

The app also provides flexibility to control which analytics export needs to be extracted.

Prerequisites

Create Streaming Datasets

One of the prerequisites before installing the PowerBI connector is to create a streaming dataset inside PowerBI that can be used to push the data to. A streaming dataset is a dataset that can accept data via API push which is what we will need to continually push data from report extract.


Here’s the process:

1) Click on Create from the left menu and choose Streaming Dataset as the option.

2) Choose API as an option inside streaming dataset.



3) Give a name to the Streaming Dataset and create all the fields that you want to bring into PowerBI. Make sure to turn on the Historical Data Analysis tab.


4) This has to be done for all the reports you want to export to PowerBI.

Create PowerBI Embed App

The second thing you will need before installing the app is to create a PowerBI embedded app to get the credentials to connect Freshchat/CSS and PowerBI.


  1. To set up your environment, go to the Power BI embedded analytics setup tool. Select Embed for your organization as the option.

  2.  In the next step authenticate and sign in with your PowerBI.
  3. In the next step give your app a name and fill in other details. For the redirect URL choose the custom URL option and enter this URL - https://www.workato.com/oauth/callback.
    If you are on EU datacenter then use this URL - https://app.eu.workato.com/oauth/callback
    For API access choose Select All

Then click on Register to complete the registration of the app.


  1. In next step you can create a new workspace if needed or click on skip if you want to continue with an existing workspace
  2. In the final step you can click on “Skip” to complete the creation of the app.
  1. You will be presented with an “Application ID” and “Application Secret” on the screen. Note these down as they will be needed in the installation process.

Create Data Export API

The final prerequisite is to create a data export schedule and get the API. To do this you can follow the following steps:

1) Go to “Analytics” and click on “Settings”

2) In the Settings page choose “Data Export” tab

3) Click on “Create Report”

4) Give your export a name and choose the module from which you want to export data. Also, select the schedule and time when you want to trigger the export. Take care to make sure the time and time zone are accurate as per your expectations.


5) In the next section choose the fields which you want to export and select to receive as “API”. Copy and keep the URL handy as this will be needed in later steps. 


6) Click on Schedule to create the export schedule.

Installation

Installing the PowerBI App in Freshchat
To install the PowerBI Connector App on Freshchat/CSS, go to the Freshchat/CSS marketplace by navigating to Admin -> Apps and search for PowerBI Connector App’.

Once you click Install, you will be prompted to enter your Freshchat/CSS credentials

Connect your Freshchat Account

The details listed below can be found at ‘Admin Settings -> API settings -> API Details for Chat’

  1. Enter your Freshchat domain. Enter the URL in the format  ‘'yourcompany.freshchat.com'’
  2. Enter the Freshchat API Key. 
  3. Enter your CRM domain. Enter the URL in the format  ‘yourcompany.myfreshworks.com’
  4. Enter your CRM API Key
  5. Click 'install'. Once the app installation is complete, navigate to the full-page app on the left navigation bar. 

Configuring data sync

To synchronize data between PowerBI and Freshchat/CSS, go to the 'Data sync' tab in the full-page app. This feature is powered by the 'Connector apps add-on'. For pricing information, refer to the section below.

  1. Connect the PowerBI account you want to synchronize data with.
  2. To connect to your PowerBI account you will need to enter the Application ID and Application Secret. Choose Auth type as “Oauth2 Authorization code” and enter the ID and secret in the Client Id and Client secret fields.
  3. Choose the API version as “Version 1.0” and Account Type based on the type of PowerBI account. In most cases it will be “Multitenant”
  4. Click on Connect to complete the setup.
  5. Once the connection is set up you will see recipes to push reporting data into PowerBI. The recipes will be available with 3 options, each giving a different cadence of syncing data. You can choose to sync data either daily, weekly or monthly. 
  6. Choose the frequency and the module you want to export from the recipe list.

Freshchat/CSS to PowerBI

  1. Once you have opened the recipe of the module and frequency you want to export, you will see a screen like below.

 

  1. In the recipe screen, go to step 1 and ensure the schedule frequency and time is correlated to your API export schedule. The trigger time in the recipe should be 5-10 mins after the API export schedule
  2. Then click on the second block inside the recipe and click on the connection tab

  1. Click on “Switch Connections” and then click on “new connection”
  2. In the New connection tab choose “Authentication Type” as Header auth. In the Header section enter your Freshchat API key and set it in the format shown below. Set the base URL of the connection as your desk URL which will be in the form of “https://domain.myfreshworks.com”

  1. Set “Use Custom TLS/SSLcertificate settings” option as “No”.
  1. Once this is done, go to the setup tab and in the Request URL option, enter the API url of your data export. (You can ignore the warning of sensitive data)
  2. Go to step 4 inside the recipe and upload a sample CSV of your export file so the schema of the export is captured.



  1. To complete the setup go to step 5 inside the recipe and choose the dataset in PowerBI where you want to load the data. Then map the fields from the export to the fields in your dataset.

   

  1. You can save the recipe and turn it on from the recipe screen and your data will start getting exported to PowerBI as per the schedule chosen by you.