ServiceNow >> Power BI

[Jamsta1912]

Hi all,

I'm trying to get data from our ServiceNow instance into Power BI, and I'm after some general steer. I appreciate that this question has been asked a few times previously, but best practices / advice may have moved on.

I've created a scripted REST API for Power BI to consume, rather than using the OOTB Table APIs. One reason for this is that we wanted to present the user friendly field labels rather than the database field names (one limitation of the table APIs). The API takes the table name as a parameter, and is working fine (functionally speaking). The issue I'm encountering is with the size of the data. I'm able to refresh my dataset in Power BI via the API, if I take a small amount of data from each table (e.g. records updated this week). But if I try to grab records updated during the last 12 months the refresh fails. 

We're not using incremental refresh yet in Power BI, but even if we do, we'll still need some way of getting that initial dump of historic data across.

Any advice would be grealy appreciated - in particular, is a REST API even the best way to go? Are other options less prone to errors and interuptions when the datasets are large?

Thank you

Jamsta

[Giles Lewis · Accepted solution]

The recommended architecture is to replicate your ServiceNow data to an external SQL database. If you are using Power BI, then you will probably want to replicate to SQL Server.

There are several available products which can be used to solve this problem:

• SnowMirror is the most popular commercial solution in this space
• SNDML is an Open Source solution  which you can download from GitHub

These products are designed to work reliably with large tables (more than one million rows) and to handle the incremental refresh problem. If you try to build a solution yourself using the REST API, then you will likely run into a lot of technical and performance related challenges. 

Once you have the raw data in SQL Server, you will need to create database views to join tables, rename fields, etc.

Please mark this response as helpful and/or correct if I have answered your question.

[Jamsta1912]

Thank you Giles. I really appreciate the advice.

[Anna Odrynska]

Hi @Jamsta1912 ,

If you ever come to considering alternative options to integrate Service Now and Power BI, feel free the try our newest app - Power BI Connector, it is available from ServiceNow Store.

Anna