On the SnowMirror website, we see that one of the more popular topics people search for is how they can connect PowerBI to their ServiceNow data. This was the topic of a recent SnowMirror webinar which you can view on demand here, but for this article we wanted to highlight some of the other creative ways we’ve seen to achieve this.
This idea came from a thread on Reddit in the r/servicenow subreddit, in which a user asks a relatively simple question: “Hello all! Is it possible to connect to an instance with powerbi? How can I do this? I’m looking at data from changes and survey results… For starts. 😉✌️”
The answers came fast and were less than encouraging.
One user wrote, “It’s not easy to do. If you can use the ServiceNow odbc stuff that’s the easiest, but I’ve only had success pulling table by table data using the url api.” In addition, another user wrote, “We solved this (for now) with a daily report, in csv format, to a sharepoint location, which is picked up daily by powerbi. Understood that ServiceNow ODBC was very expensive…” To which a third user corrected: “SN’s ODBC is free to use.”
But this solution in the thread was particularly interesting and helpful:
“I have done this a few different ways:
Comment/worknote counts by users- CSV loads
Tasks and other large tables- Use a Recursive API loop to pull entire tables and parameters to control fields, less fields more rows in pagination pull. below is the basic logic I used when I did this.
M requires some hoops to get the recursion going but the basic logic is along the lines of:
Create Variable for offset 10000 is default most you can pull at once.
Use aggregate api to get count of records on a table with whatever filter you plan on using.
Then use M to build a list(this allows you to iterate in M)
Then use the table API with variable offset in there as you build the API endpoint string so you pull more records( think of it like this your first run you want to pull 0- 10,000, then you want to pull 10001-20001 and so on).”
This solution is OK, but it does assume a level of technical skill that may or may not be there for many users. It also assumes that the user has the time to devote to writing, debugging and maintaining such a solution.
Last year, in a long and detailed post on LinkedIn Pulse, one user outlined what he called his “journey to find out ways to fetch data from ServiceNow to Microsoft PowerBI.” It was a project he undertook while stuck at home during lockdown.
The post outlines two approaches to the problem. One uses ServiceNow’s REST API, and the other exports data to storage and connects PowerBI to that using various methods such as CSV files. Kolesnikov writes that he failed on the REST API approach.
In this approach, various Amazon AWS tools such as Lambda, Athena and Glue are used to convert the CSV files into SQL queries and relational data tables.
As with the approach from Reddit, this approach is more of an experiment and is interesting, but we wouldn’t recommend this for a production environment.
Of course, one of the main benefits of using a tool like SnowMirror, however, is that we have already worked out all of these issues, allowing you to concentrate on other aspects of your replication in production environments. We have several case studies available showing how SnowMirror is being used in some of the world’s leading ServiceNow environments.
With SnowMirror, you simply point your SnowMirror installation to your ServiceNow instance, set the parameters for your replication, and that’s about it. Your ServiceNow data is backed up to the SQL server of your choice. And once it’s in an SQL database on your infrastructure (either on premises or in your cloud), you can easily connect it to PowerBI for your purposes.
Our thanks to the Reddit users mrhappy002, rusty2110, bsquinn1451, BasedPontiff and future_traveller for this great thread, and to Dmitry Kolesnikov for this excellent post and creative approach.
If you’d like to talk with us about how SnowMirror can help your business get the most out of ServiceNow, get in touch here.
