Start a new topic

Microsoft Power BI intigration

a very useful way to allow users to meet their own reporting needs.


Zen Desk is doing this very successfully.


Curtis


46 people like this idea

Hello, 


We have developed  a demo application which allows to query data from Freshservice in Power BI. It presents information about tickets and can be easy extended further. 


image





Best Regards,

Derek

 Derek, How are you getting the data? Via the Web API? I cannot seem to get the API to work. It keeps telling me I need to provide an Api Key Name.


1 person likes this

Hi Josh, 


The application connects through Web API using API key. What client do you use to connect to the service? 


Regards,

Derek

apps.devel@outlook.com

Derek,


Where can I get more details on this please?


Thanks


A

I'm no expert in the programming / coding aspect of this functionality, but I have at least managed to get PowerBI pulling some data from the API.


The issue I'm currently stuck with is that when the query returns more than one page, I'm sure I just haven't stumbled upon the right Google result to help me with this yet, if anyone here has sorted this and can share, that'd be fantastic !


pete

Hi Adam Dunn,


The Freshservice connector is able to synchronize data with local or in the cloud ms sql and my sql databases.

It is possible to provide OData interface in cloud as well, although that would require more efforts.  Power BI can consume data from all these sources. We're trying to establish  what is demand and who would like to take part in a pilot. 

Please email me on apps.devel@outlook.com for further details. 


Best Regards,

Derek

Hello All, 


The integration with Power BI preview is available  under https://webfreshreport.azurewebsites.net for testing.  

Please don't hesitate to contact with us in case of any questions. 


Best Regards,

Derek, 

Hello All, 

 

We have enabled export of 1000 tickets in the  https://webfreshreport.azurewebsites.net  service to Power BI.   

Please don't hesitate to contact with us in case of any questions. 


Best Regards,

Derek, 

FYI, there is an App in the FreshService marketplace provided by Radiare. I have tried this in the hopes that it would provide access to the raw data in a format that was easily accessible by a Power BI query. However that does not seem to be what they offer.


What they do provide is a link to a static report that while interesting is not able to be modified. I also think they may have some problems with their refresh mechanism as the data has not been updated since I was provided access.


I think I will resurrect my own attempts to pull the data via the API, I had made some progress with v1 of the FreshService API, and got stuck with V2 (Same as Pete's response from 2018-02-06). The challenge seems to be in getting PowerBI to recognise, request and load multiple pages of json response.

What's the official FreshService update on this? I thought there was supposed to be a solution in Q2?

Hi, 


We are currently working on scheduling data exports for Tickets, Problems, Changes, Release, Tasks, Assets, Groups, Users & Departments. You can consume these scheduled data exports from Freshservice to build your own custom dashboards within any BI tool. We are targeting to ship this feature in early Q3. 


Meanwhile, we have rolled out a 3rd party integration that legeraves the current Freshservice APIs to deliver canned dashboards in Power BI. You can access these dashboards here. If you wish to customise the dashboards further, please reach out to freshservicesupport@radiare.com


@Adam - We have contacted the Radiare team regarding your concerns with respect to the data refresh & to modify the dashboard to meet your needs. They will be reaching out to you by this week. 







Power BI Integration would be great. So please count my vote.

I tried the Radiare solution and found it did not meet our needs as we could only use the dashboard they create and not access our data.


I ended up spending some time and finally managed to get Power  BI to pull ticket data from the Freshservice API v2. A significant caveat is that I have not successfully managed to get the Power BI queries to handle the pagination gracefully. Rather, I pull up to 5000 records of tickets created after a hard-coded date.


I also needed to manually add a few tables to map ids for 'Status', 'Priorities', 'Source Type', 'Agents' and 'Department'.


These two pages were very helpful in figuring out how to do it - The technique on the first page worked great on the desktop application, however did not work with Scheduled Refresh on published reports.


image



Below is a very rough guide which may help people comfortable working in the Power BI Query Editor.


Two queries are needed one uses the other to load the tickets. You will also need to set up your security settings in Power BI. I used my credentials rather than an API key, but this can be changed. Do this via 'Data Source Settings' → 'Edit Permissions'

Query 1 "GetData"

  • Be Sure to swap <https://XXXXXXX.freshservice.com> with your URL (don't use a custom domain)
  • Update the date <&updated_since=2016-01-01T00:00:00Z> to match your requirements

(page as number) as table =>
let
    Source = Json.Document(Web.Contents("https://XXXXXXX.freshservice.com/api/v2/tickets?per_page=100&include=requester&updated_since=2016-01-01T00:00:00Z",
    [Query=[page=Number.ToText(page)]]
)),
    tickets = Source[tickets],
    #"Converted to Table" = Table.FromList(tickets, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"cc_emails", "fwd_emails", "reply_cc_emails", "fr_escalated", "spam", "email_config_id", "group_id", "priority", "requester_id", "responder_id", "source", "status", "subject", "to_emails", "department_id", "id", "type", "due_by", "fr_due_by", "is_escalated", "description", "description_text", "category", "sub_category", "item_category", "custom_fields", "created_at", "updated_at", "deleted", "requester"}, {"cc_emails", "fwd_emails", "reply_cc_emails", "fr_escalated", "spam", "email_config_id", "group_id", "priority", "requester_id", "responder_id", "source", "status", "subject", "to_emails", "department_id", "id", "type", "due_by", "fr_due_by", "is_escalated", "description", "description_text", "category", "sub_category", "item_category", "custom_fields", "created_at", "updated_at", "deleted", "requester"}),
    #"Expanded requester" = Table.ExpandRecordColumn(#"Expanded Column1", "requester", {"id", "name", "email", "mobile", "phone"}, {"id.1", "name", "email", "mobile", "phone"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded requester",{{"id.1", Int64.Type}, {"updated_at", type datetime}, {"created_at", type datetime}, {"fr_due_by", type datetime}, {"due_by", type datetime}, {"id", Int64.Type}, {"department_id", Int64.Type}, {"status", Int64.Type}, {"source", Int64.Type}, {"priority", Int64.Type}, {"requester_id", Int64.Type}, {"responder_id", Int64.Type}, {"group_id", Int64.Type}, {"email_config_id", Int64.Type}, {"fr_escalated", type logical}, {"spam", type logical}, {"is_escalated", type logical}, {"deleted", type logical}})
in
    #"Changed Type"

  

Query 2 "API v2 All Tickets"

This essentially runs "Query 1" 50 times (this can be changed on line 2, but be careful of your API limit)

 

let
    Source = {1..50},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Page"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each GetData([Page])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"cc_emails", "fwd_emails", "reply_cc_emails", "fr_escalated", "spam", "email_config_id", "group_id", "priority", "requester_id", "responder_id", "source", "status", "subject", "to_emails", "department_id", "id", "type", "due_by", "fr_due_by", "is_escalated", "description", "description_text", "category", "sub_category", "item_category", "custom_fields", "created_at", "updated_at", "deleted", "id.1", "name", "email", "mobile", "phone"}, {"cc_emails", "fwd_emails", "reply_cc_emails", "fr_escalated", "spam", "email_config_id", "group_id", "priority", "requester_id", "responder_id", "source", "status", "subject", "to_emails", "department_id", "id", "type", "due_by", "fr_due_by", "is_escalated", "description", "description_text", "category", "sub_category", "item_category", "custom_fields", "created_at", "updated_at", "deleted", "id.1", "requester.name", "requester.email", "requester.mobile", "requester.phone"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"id.1", Int64.Type}, {"updated_at", type datetime}, {"created_at", type datetime}, {"fr_due_by", type datetime}, {"due_by", type datetime}, {"id", Int64.Type}, {"department_id", Int64.Type}, {"status", Int64.Type}, {"source", Int64.Type}, {"priority", Int64.Type}, {"requester_id", Int64.Type}, {"responder_id", Int64.Type}, {"group_id", Int64.Type}, {"email_config_id", Int64.Type}, {"fr_escalated", type logical}, {"spam", type logical}, {"is_escalated", type logical}, {"deleted", type logical}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"requester.name", "Requestor"}, {"subject", "Subject"}, {"due_by", "Due"}, {"created_at", "Created"}, {"status", "status num"}, {"source", "source num"}, {"priority", "priority num"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"id.1"})
in
    #"Removed Columns"

 


1 person likes this

@Adam, Very nice. I was able to get PowerBI to work with Freshservice but didn't have the volume to really mess with over 5k. This is really useful. Thanks again.


I am not a PowerBI expert, but would it be possible to query the amount of pages/tickets and query only the amount of pages that you need instead of coding 50 runs. That is if I am reading this correctly.

On the topic of PowerBI integration. Freshservice could probably develop a pbix file to download which uses the API. Let us change the URL and the credentials. This would probably be the easiest way to accomplish this. I think a competent PowerBI person and a someone who understands the Freshservice API could crank through the process in a day. Seems like it could be an easy win for the FS team.

Login or Signup to post a comment
JS Bin