PowerBI real-time integration with ServiceNow data?

Anonymous — Thu, 19 Nov 2020 17:44:31 GMT

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"

Re: PowerBI real-time integration with ServiceNow data?

antonstorozhuk — Wed, 14 Sep 2022 03:17:35 GMT

Hello @Anonymous

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.

topic Re: PowerBI real-time integration with ServiceNow data? in Developer

PowerBI real-time integration with ServiceNow data?

Re: PowerBI real-time integration with ServiceNow data?