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"
