Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
eltonaguiar2020
Frequent Visitor

PowerBI real-time integration with ServiceNow data?

Hi,

I am looking for a way to do a PowerBI real-time integration with ServiceNow data. 

I believe from ServiceNow side, the approaches could be REST API, export sets.

 

The PowerBI API doesn't seem to let me delete specific records, I don't want it to need to download 200K records everytime I refresh, only the updated records. Is that possible?

 

Ideally I would see something like, 

1) download historical dataset (there are like 200K records for example)

2) append only records updated since the last data

 

I explored Rest API with the code below, but for 200K records, it takes a really long time like 2+ hours and I believe it times out eventually. I'm aware some ServiceNow properties can be changed, but the performance I am skeptical about. 

 

 

let
//sysparm_limit=1000&sysparm_count=true
        EntitiesPerPage = 1000,
BaseUrl="https://<mysite>training.service-now.com/api/now/table/incident?",
BaseUrlCounting="https://<mysite>training.service-now.com/api/now/v1/stats/incident?" ,
 GetJson = (Url) =>
        let 
        
        //Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
            //RawData = Web.Contents(Url, Options),
            RawData = Web.Contents(Url),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>

let
Url= BaseUrlCounting & "sysparm_count=true",
Json  = GetJson(Url),
RawData = Json.Document(Web.Contents(Url)),

    #"Converted to Table" = Record.ToTable(RawData),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"stats"}, {"Value.stats"}),
    #"Expanded Value.stats" = Table.ExpandRecordColumn(#"Expanded Value", "Value.stats", {"count"}, {"Value.stats.count"}),
    Count = Number.FromText(#"Expanded Value.stats"{0}[Value.stats.count])
    
    //#"Expanded Value.stats"{0}[Value.stats.count]
    
    
    


        //let Url   = BaseUrlCounting & "sysparm_count=true&sysparm_limit="& Text.From(EntitiesPerPage),
        //let Url   = BaseUrlCounting & "sysparm_count=true&sysparm_limit=10000",
            //,
             //md = Value.Metadata(Web.Contents(BaseUrlCounting & "sysparm_count=true&sysparm_limit=10000"),
         //   Count=Json[#"Value.stats.count"]
            
            //Json[#"response.result.stats"]
            //210100
            
        in  Count,

 /*https://<mysite>training.service-now.com/api/now/v1/stats/incident?sysparm_limit=10&sysparm_count=true*/
    GetPage = (Index) =>
        let Skip  = "sysparm_offset=" & Text.From(Index * EntitiesPerPage),
            Top   = "sysparm_limit=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Skip & "&" & Top,
            Json  = GetJson(Url),
            Value = Json[#"result"]
        in  Value,
 
    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 }, 
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    //for performance lets try just number and a few other fields
    //#"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"u_name", "u_vendor_ticket_number", "parent", "made_sla", "caused_by", "watch_list", "u_major_incident", "upon_reject", "sys_updated_on", "child_incidents", "approval_history", "u_ci_not_found", "skills", "number", "resolved_by", "sys_updated_by", "user_input", "sys_created_on", "sys_domain", "state", "sys_created_by", "knowledge", "order", "calendar_stc", "closed_at", "cmdb_ci", "active", "u_actual_work_effort", "business_service", "sys_domain_path", "rfc", "time_worked", "expected_start", "opened_at", "business_duration", "group_list", "work_end", "caller_id", "reopened_time", "resolved_at", "u_location_specifics", "subcategory", "u_service_restoral_time", "short_description", "u_business_services", "close_code", "correlation_display", "work_start", "additional_assignee_list", "business_stc", "description", "calendar_duration", "u_first_level_triage_notes", "close_notes", "notify", "sys_class_name", "u_affected_users", "closed_by", "follow_up", "parent_incident", "sys_id", "contact_type", "reopened_by", "incident_state", "problem_id", "reassignment_count", "u_resolution_category", "u_intermediate_comments_holder", "u_type_of_issue", "activity_due", "severity", "comments", "approval", "sla_due", "due_date", "sys_mod_count", "u_intermediate_work_holder", "reopen_count", "sys_tags", "u_assignee_reassignment", "u_location_of_ci", "u_vendor_contact_information", "u_comments", "escalation", "upon_approval", "u_resolution_subcategory", "correlation_id", "location", "u_location", "category"}, {"Column1.u_name", "Column1.u_vendor_ticket_number", "Column1.parent", "Column1.made_sla", "Column1.caused_by", "Column1.watch_list", "Column1.u_major_incident", "Column1.upon_reject", "Column1.sys_updated_on", "Column1.child_incidents", "Column1.approval_history", "Column1.u_ci_not_found", "Column1.skills", "Column1.number", "Column1.resolved_by", "Column1.sys_updated_by", "Column1.user_input", "Column1.sys_created_on", "Column1.sys_domain", "Column1.state", "Column1.sys_created_by", "Column1.knowledge", "Column1.order", "Column1.calendar_stc", "Column1.closed_at", "Column1.cmdb_ci", "Column1.active", "Column1.u_actual_work_effort", "Column1.business_service", "Column1.sys_domain_path", "Column1.rfc", "Column1.time_worked", "Column1.expected_start", "Column1.opened_at", "Column1.business_duration", "Column1.group_list", "Column1.work_end", "Column1.caller_id", "Column1.reopened_time", "Column1.resolved_at", "Column1.u_location_specifics", "Column1.subcategory", "Column1.u_service_restoral_time", "Column1.short_description", "Column1.u_business_services", "Column1.close_code", "Column1.correlation_display", "Column1.work_start", "Column1.additional_assignee_list", "Column1.business_stc", "Column1.description", "Column1.calendar_duration", "Column1.u_first_level_triage_notes", "Column1.close_notes", "Column1.notify", "Column1.sys_class_name", "Column1.u_affected_users", "Column1.closed_by", "Column1.follow_up", "Column1.parent_incident", "Column1.sys_id", "Column1.contact_type", "Column1.reopened_by", "Column1.incident_state", "Column1.problem_id", "Column1.reassignment_count", "Column1.u_resolution_category", "Column1.u_intermediate_comments_holder", "Column1.u_type_of_issue", "Column1.activity_due", "Column1.severity", "Column1.comments", "Column1.approval", "Column1.sla_due", "Column1.due_date", "Column1.sys_mod_count", "Column1.u_intermediate_work_holder", "Column1.reopen_count", "Column1.sys_tags", "Column1.u_assignee_reassignment", "Column1.u_location_of_ci", "Column1.u_vendor_contact_information", "Column1.u_comments", "Column1.escalation", "Column1.upon_approval", "Column1.u_resolution_subcategory", "Column1.correlation_id", "Column1.location", "Column1.u_location", "Column1.category"}),

/*significantly less than the max  columns..*/
#"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"number", "sys_updated_by", "sys_created_on", "u_ci_not_found", "cmdb_ci", "active", "closed_at", "sys_created_by", "opened_at", "caller_id", "subcategory", "short_description", "description", "u_first_level_triage_notes", "contact_type", "reopened_by", "problem_id"}, {"Column1.number", "Column1.sys_updated_by", "Column1.sys_created_on", "Column1.u_ci_not_found", "Column1.cmdb_ci", "Column1.active", "Column1.closed_at", "Column1.sys_created_by", "Column1.opened_at", "Column1.caller_id", "Column1.subcategory", "Column1.short_description", "Column1.description", "Column1.u_first_level_triage_notes", "Column1.contact_type", "Column1.reopened_by", "Column1.problem_id"})
//    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1",{"Column1.number", "Column1.sys_updated_by", "Column1.sys_created_on", "Column1.u_ci_not_found", "Column1.cmdb_ci", "Column1.active", "Column1.closed_at", "Column1.sys_created_by", "Column1.opened_at", "Column1.caller_id", "Column1.subcategory", "Column1.short_description", "Column1.description", "Column1.u_first_level_triage_notes", "Column1.contact_type", "Column1.reopened_by", "Column1.problem_id"})

in
    #"Expanded Column1"

 

 

 

1 REPLY 1

Hello @eltonaguiar2020 

We've built an app for Servicenow and Power BI integration: Power BI Connector for Servicenow. It's easy to use, it's fast and automated. Feel free to DM me in you want to Trial it or need any assistance.

We're working on the Incremental refresh feature right away, that should solve your problem.

Cheers!

Anton

===

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.