Can the Microsoft PowerBI.com Connector Default Data Set be changed?

13 コメント

  • James Bubbers
    コメントアクション Permalink

    Hi Bill,

    It's a dead end. Looks like MS might have had a 3rd party build it out. They clearly have missed the mark. MS neither Zendesk want to look at it. My Zendesk account rep took a look and said it didn't look like something they would have built (too rudimentary).

    We are currently using the Zendesk API (and scripts we wrote) to export data into our EDW. This is another integration we need to actively manage and maintain (not ideal).

    As an alternative:

    We use Salesforce. We currently only display ticket data in Salesforce via the Zendesk API integration for Salesforce. So currently no ticket data is stored in SF.

    We are looking at syncing Zendesk ticket data into Salesforce using the mature Salesforce/Zendesk integration. Thus saving us the need to manage the the EDW source (Zendesk).

    We have found that Salesforce and PowerBI work really well together. Simply select a report from Salesforce through PowerBI and import (this pulls in the necessary data and the report into PowerBI). That's likely how we will roll past this obstacle.

    I hope this is helpful.

     

    1
  • Claire Miller
    コメントアクション Permalink

    Hi James,

    Unfortunately there doesn't appear to be any documentation on this in our Knowledge Base and I'm not super familiar with the Microsoft Power BI Zendesk integration. I would recommend checking out their community forum for further assistance.

    0
  • James Bubbers
    コメントアクション Permalink

    Thanks Claire we spoke with Microsoft BI consultants from Microsoft and they said to get in touch with Zendesk. I'll contact my account rep. 

    0
  • Bill Hand
    コメントアクション Permalink

    James, 

    Have you had any luck with the Zendesk custom fields in Power BI?

    Thanks.

    0
  • Matt
    コメントアクション Permalink

    Hi James,

    Any Chance you would be willing to share the scripts / etc you use to grab your data from the ZD API and bring this into your own EDW?

    We are looking to achieve something very similar.... and a leg up would be very helpful!

    You can email me directly - matt[AT]onesaas.com if preferred

    Cheers,

    Matt

    0
  • JamesBubbers
    コメントアクション Permalink

    Hi Matt,

    I've sent you a sample script that should get you on the way. 

    FYI - We went the Zen to SF to PBI route in the end.

    Cheers

    0
  • Christie
    コメントアクション Permalink

    Hello James,

    I had the same problem with Zendesk Custom Field in PowerBI being omitted.

    Do you mind if I ask you to share the sample script or any tips for better solution?

    If that is okay, you could email me at christie.limnois[AT]gradwell.com

    Regards,

    Christie

     

    0
  • JamesBubbers
    コメントアクション Permalink

    Hi Christie,

    I just sent you an email with the old script. 

    We now get around this issue by syncing/duplicating Zendesk ticket data into Salesforce using the mature Salesforce/Zendesk integration. Thus saving us the need to manage the the EDW source (Zendesk).

    We have found that Salesforce and PowerBI work really well together. Simply select a report from Salesforce through PowerBI and import (this pulls in the necessary data and the report into PowerBI). That how we rolled past this obstacle.

    Cheers 

    0
  • Lindsay Hales
    コメントアクション Permalink

    Hello James,

    I'm having the same issues as described above  with Zendesk Custom Field in PowerBI being omitted. :-( 

    Do you mind if I ask you to share the sample script or any tips for better solution?

    If that is okay, you could email me at lindsay.hales[AT]4finance.com

     

    Thanks

     

    Lindsay! 

    0
  • JamesBubbers
    コメントアクション Permalink

    Hi All,

    I've been asked for this a few times now. Below is what's probably the most complicated script. You can figure out others using this as a guide.

    Hope this helps.

    Again we are no longer using this method. See earlier posts for ZD to SF to PBI integration.

    Cheers

    James

     

    --------------SCRIPT------------

    #region Help: Introduction to the Script Component
    /* The Script Component allows you to perform virtually any operation that can be accomplished in
    * a .Net application within the context of an Integration Services data flow.
    *
    * Expand the other regions which have "Help" prefixes for examples of specific ways to use
    * Integration Services features within this script component. */
    #endregion

    #region Namespaces
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using System.Net;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.IO;
    using System.Web.Script.Serialization;
    using System.Collections.Generic;
    using System.Text;
    #endregion

    #region Class
    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
    /// <summary>Outputs records to the output buffer</summary>
    public override void CreateNewOutputRows()
    {

    /* this one uses the Incremental loads, only gets tickets from EPOC 1404172800 onwards for now, this URl can be paramaterised
    * using the script component, you'll get multiple outputs, make sure that the names of the outputs match in the data flow.
    * Also make sure that the data types if the output mapping matches the data types established in the JSON class declerations.
    *
    * This script has four outputs:
    * - Tickets
    * - Tags
    * - Custom Fields
    * - ZendeskRestDetails (just used for logging purposes)
    *
    * You may not need to split all your outputs like this, but i found it easier to map the tags and custom fields
    * back to each ticket this way, as its all a one to many relationship
    */

    string wUrl = "<your URL>";

    insertValues(wUrl, 0);
    }

    // actually does the insert, the URL is paramatised so if we have to run this again, we can pass in the next page
    private void insertValues(string wUrl, int totalCount)
    {
    RootObject outPutResponse = GetWebServiceResult(wUrl);

    // only proceed if there is more than 0 tickets
    if (outPutResponse.tickets.Count > 0)
    {
    try
    {
    // for each ticket
    foreach (Ticket ticket in outPutResponse.tickets)
    {
    TicketsBuffer.AddRow(); // adds a new row to the TicketsBuffer output
    TicketsBuffer.url = ticket.url;
    TicketsBuffer.id = ticket.id;

    // some null handling, it was having a cry if there was no value
    if (ticket.external_id != null)
    {
    TicketsBuffer.externalid = ticket.external_id.ToString();
    }

    if (ticket.via != null)
    {
    TicketsBuffer.channel = ticket.via.channel;
    }

    if (ticket.via.source != null)
    {
    TicketsBuffer.sourcefromemail = ticket.via.source.from.address;
    TicketsBuffer.sourcefromname = ticket.via.source.from.name;
    }

    TicketsBuffer.createdat = ticket.created_at;
    TicketsBuffer.updatedat = ticket.updated_at;
    TicketsBuffer.type = ticket.type;
    TicketsBuffer.subject = ticket.subject;
    TicketsBuffer.priority = ticket.priority;
    TicketsBuffer.status = ticket.status;
    TicketsBuffer.requesterid = ticket.requester_id;
    TicketsBuffer.submitterid = ticket.submitter_id;
    TicketsBuffer.assigneeid = ticket.assignee_id ?? 0;
    TicketsBuffer.organizationid = ticket.organization_id ?? 0;
    TicketsBuffer.groupid = ticket.group_id ?? 0;
    TicketsBuffer.forumtopicid = (Int32)(ticket.forum_topic_id ?? 0);
    TicketsBuffer.problemid = (Int32)(ticket.problem_id ?? 0);
    TicketsBuffer.hasincdients = ticket.has_incidents;

    if (ticket.due_at != null)
    {
    TicketsBuffer.dueat = ticket.due_at.ToString();
    }

    // each ticket has multiple tags, using a seperate output and including the TicketID
    for (int i = 0; i < ticket.tags.Count; i++)
    {
    TagsBuffer.AddRow(); // add a new row to the TagsBuffer output
    TagsBuffer.TicketID = ticket.id;
    TagsBuffer.Tag = ticket.tags[i].ToString();
    }

    // each ticket has multiple Custom Field, using a seperate output and including the TicketID
    foreach (CustomField customfield in ticket.custom_fields)
    {
    CustomFieldsBuffer.AddRow(); // add a new row to the CustomFieldsBuffer output
    CustomFieldsBuffer.TicketID = ticket.id;
    CustomFieldsBuffer.id = customfield.id;
    CustomFieldsBuffer.value = customfield.value;
    }

    // and satisfaction ratings
    if (ticket.satisfaction_rating != null)
    {
    TicketsBuffer.satisfactioncomment = ticket.satisfaction_rating.comment;
    TicketsBuffer.satisfactionid = ticket.satisfaction_rating.id ?? 0;
    TicketsBuffer.satisfactionscore = ticket.satisfaction_rating.score;
    }
    TicketsBuffer.brandid = ticket.brand_id;
    }
    }
    catch (Exception e)
    {
    FailComponent(e.ToString());
    }

    // track what we are up too
    totalCount += outPutResponse.count;

    // the output response is the total number of records available, the service only spits out 1000 at a time
    // if there are less than 1000, its all in one response and we don't need to go to the next page.
    if (outPutResponse.count == 1000)
    {
    // run again with the next page
    insertValues(outPutResponse.next_page, totalCount);
    }
    // otherwise insert in the details of this run into the logs
    else
    {
    ZendeskRestDetailsBuffer.AddRow(); // add a new row to the ZendeskRestDetailsBuffer output
    ZendeskRestDetailsBuffer.count = totalCount;
    ZendeskRestDetailsBuffer.nextpage = outPutResponse.next_page;
    }
    }
    }

    /// <summary>
    /// Method to return our WorkGroupMetric array
    /// </summary>
    /// <param name="wUrl">The web service URL to call</param>
    /// <returns>An array of WorkGroupMetric composed of the de-serialized JSON</returns>
    private RootObject GetWebServiceResult(string wUrl)
    {
    string userName = "<API username>";
    string password = "<API Password>";

    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
    httpWReq.Credentials = new NetworkCredential(userName, password);
    httpWReq.Accept = "application/json";
    httpWReq.Method = "GET";

    HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();

    RootObject jsonResponse = null;

    try
    {
    //Test the connection
    if (httpWResp.StatusCode == HttpStatusCode.OK)
    {

    Stream responseStream = httpWResp.GetResponseStream();
    string jsonString = null;

    //Set jsonString using a stream reader
    using (StreamReader reader = new StreamReader(responseStream))
    {
    jsonString = reader.ReadToEnd();
    reader.Close();
    }

    //Deserialize our JSON
    JavaScriptSerializer sr = new JavaScriptSerializer();
    sr.MaxJsonLength = Int32.MaxValue;
    jsonResponse = sr.Deserialize<RootObject>(jsonString);


    }
    //Output connection error message
    else
    {
    FailComponent(httpWResp.StatusCode.ToString());
    }
    }
    //Output JSON parsing error
    catch (Exception e)
    {
    FailComponent(e.ToString());
    }
    return jsonResponse;

    }

    /// <summary>
    /// Outputs error message
    /// </summary>
    /// <param name="errorMsg">Full error text</param>
    private void FailComponent(string errorMsg)
    {
    bool fail = false;
    IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;
    compMetadata.FireError(1, "Error Getting Data From Webservice!", errorMsg, "", 0, out fail);
    }
    }
    #endregion

    #region JSON Class
    /* this is the fun part, it will change depending on the table you are importing from
    * go to this address : http://json2csharp.com/
    * then get the JSON response from that page (just browse too it in another window), paste the JSON response in and hit Generate.
    * Copy the EXACT output into the below section. Don't change data types or variable names.
    * Take note of the data types for each variable, its important to match that mapping in SSIS (i.e. int? vs int)
    * You can see why things like Tags, Custom Fields and Satisfaction ratings had to be handled a little differently in the import itself.
    */

    public class From
    {
    public string address { get; set; }
    public string name { get; set; }
    public int? ticket_id { get; set; }
    public string subject { get; set; }
    }

    public class To
    {
    public string address { get; set; }
    public string name { get; set; }
    }

    public class Source
    {
    public From from { get; set; }
    public To to { get; set; }
    public string rel { get; set; }
    }

    public class Via
    {
    public string channel { get; set; }
    public Source source { get; set; }
    }

    public class CustomField
    {
    public int id { get; set; }
    public string value { get; set; }
    }

    public class SatisfactionRating
    {
    public string score { get; set; }
    public int? id { get; set; }
    public string comment { get; set; }
    }

    public class Field
    {
    public int id { get; set; }
    public string value { get; set; }
    }

    public class Ticket
    {
    public string url { get; set; }
    public int id { get; set; }
    public object external_id { get; set; }
    public Via via { get; set; }
    public string created_at { get; set; }
    public string updated_at { get; set; }
    public string type { get; set; }
    public string subject { get; set; }
    public string raw_subject { get; set; }
    public string description { get; set; }
    public string priority { get; set; }
    public string status { get; set; }
    public string recipient { get; set; }
    public int requester_id { get; set; }
    public int submitter_id { get; set; }
    public int? assignee_id { get; set; }
    public int? organization_id { get; set; }
    public int? group_id { get; set; }
    public List<object> collaborator_ids { get; set; }
    public object forum_topic_id { get; set; }
    public object problem_id { get; set; }
    public bool has_incidents { get; set; }
    public object due_at { get; set; }
    public List<object> tags { get; set; }
    public List<CustomField> custom_fields { get; set; }
    public SatisfactionRating satisfaction_rating { get; set; }
    public List<object> sharing_agreement_ids { get; set; }
    public List<Field> fields { get; set; }
    public int brand_id { get; set; }
    public int generated_timestamp { get; set; }
    public List<object> followup_ids { get; set; }
    }

    public class RootObject
    {
    public List<Ticket> tickets { get; set; }
    public int count { get; set; }
    public string next_page { get; set; }
    public string previous_page { get; set; }
    public int end_time { get; set; }
    }

     

    #endregion

    0
  • Jessie Schutz
    コメントアクション Permalink

    James, you're on fire in this thread! Thank you so much for sharing your knowledge!

    0
  • Bill Hand
    コメントアクション Permalink

    Thanks James.  What url were you using for this? https://<site>/api/v2/ticket_fields.json?

     

    0
  • JamesBubbers
    コメントアクション Permalink

    Hi Bill,

    Yes that is the correct URL.

    Cheers

    0

ログインしてコメントを残してください。

Powered by Zendesk