3 Ways to Export ServiceNow Data to Power BI

There are two common challenges IT companies may face while following their growth paths. The first is how to improve operational, employee, and customer workflows to enhance performance, develop faster and save costs. And the second one is how to get useful insights for the best reporting experience, making more data-driven decisions, and planning effectively for the future.

ServiceNow, a cloud-based SaaS platform that automates business operations, can easily solve both issues. As a comprehensive management solution, it allows companies to take control over and automate digital workflows, plan and track routine tasks, connect people, functions, and systems in the most appropriate way, increase productivity, optimize costs, predict and prevent issues, eliminate threats and solve other security issues, etc. But it also contains loads of useful information that may help business owners and managers to get the whole picture of what is going on throughout the organization, and analysts to measure KPIs, make other reports and develop appropriate strategies for the future. This data may even assist customer support teams in performing their daily activities.

But how to make this data work? So, here comes Microsoft’s analytics platform, Power BI. With customizable dashboards and flexible data processing and reporting functionality, it can help visualize valuable information and turn it into useful insights.

The next logical question will be how to connect ServiceNow to Power BI. Below we will look through and discuss three possible solutions.

1. Connect ServiceNow to Power BI Using REST API

The most widespread method is to connect ServiceNow to Power BI with ServiceNow API. API is some kind of a mediator between the users or clients or different software and web services. The integration is possible with the help of a REST (“REpresentational State Transfer”), which is a set of concepts for accessing the application’s data as interrelated objects and collections.

With the REST API, ServiceNow and Power BI connections can be made in two steps. First, you need to build the REST call, and then, connect to Power BI. Let’s see this procedure in more detail.

Building the REST call

Hopefully, you need not invent any creative methods to accomplish this task. ServiceNow itself contains a tool called Rest API Explorer designed especially for this. The only thing you should do is to create a ServiceNow user account with the “rest_api_explorer” role assigned (if you still do not have one).

1. When ready, simply select the ServiceNow table with the suitable data and click the Sent button. After loading, you will see the call. During the next steps, you will need to provide it to Power BI to be able to export information.
2. Narrow the choice by setting the values to the commands provided, applying filters, and using other options. In this way, you can, for example, include the incident table records having only the fields you need. Otherwise, you can exclude particular fields by using appropriate commands.

Connecting to Power BI

Once the call is ready, open the Power BI Desktop. You will now need to create a new Data Source and configure your ServiceNow Endpoint.

1. In the menu, select Get data, choose the web, and connect.
2. You will be asked to provide some credentials. Use the ServiceNow URL from the previous step when required in the URL sections.
3. In the HTTP request header, choose to accept for application/json and confirm by pressing OK.
4. The access web contact screen will appear. Go to the basic tab and enter your ServiceNow credentials. Finally, hit the Connect button.
5. A new page will be displayed. Normally, it contains no records.
6. To proceed, you will need to convert the data into tables.
   • Select the command Into table on the top left corner of the screen, then click the list link in the Value column. A single column with a list of all the records will be displayed.
   • Click To Table and complete the fields appropriately in the pop-up window that appears.
   • Expand Column 1 by clicking the corresponding icon in the column header.
   • And, finally, uncheck the Use original column name as prefix box.

When those are done, you will be able to create the report.

You will have the option to share the newly created report on Power BI Service by following the route File -> Publish -> Publish to Power BI. You can also opt to share your report on “My Workspace” If you want to provide access to it on the web and mobile or to show it to colleagues and other interested persons (including via Microsoft Teams).

Pros of this method:

• No need to look for additional plugins or solutions.
• You can have real flexibility in choosing the data you need if you are familiar with REST API functionality.
• It’s free.

Cons of the method:

• Additional skills and knowledge are required. You may have errors when misusing commands.
• More time is needed to accomplish the Power BI ServiceNow integration.
• The data amount is limited to 50K records if you use the default retrieval time of 2 minutes. To get more data, you will need to modify the transaction quota rule in ServiceNow.
• It’s not possible to configure incremental refresh in Power BI. You’ll have to do it manually every time.
• The complicated process of building filters.
• Data is not typed. Everything is represented as strings and needs to be converted to target types: integer, boolean, decimal, etc.

Connect Power BI ServiceNow using CSV Files

The second method to have a ServiceNow and Power BI integration will be less tricky but it has its limitations. The approach is to use CSV Files for data transfer. Many users opt for CSV (comma-separated values) files to export and import text files of their data between different platforms. CSVs are a universal file type that many systems support that’s why they are comfortable to manage.

Before the start, ensure you are logged in to ServiceNow, and then follow the steps described below.

1. Export Data from ServiceNow in the CSV/Excel Files format. For this, you need to create a mailbox with an E5 license and arrange a report to be delivered to that mailbox in ServiceNow.
2. Navigate to SharePoint and create a folder aka Data Source location.
3. Set up Flow to save attachments to the Data Source from the previously created mailbox.
4. Go to Power BI Desktop. With the mouse cursor, point to the needed data source to set up the report. With the filters provided, it is possible to access particular files.
5. When the report is published, navigate to Power BI Online and set up a Scheduled Refresh if necessary.

Pros of this method:

– It takes less time to perform than the previous one.

– No additional products and costs are required.

Cons of this method:

• You still need special knowledge to deal with.
• Performance depends deeply on SharePoint’s functionality and wellbeing.

• It can take up to 15-20 minutes to accomplish the task.
• There are limitations in the Power BI refresh cycles.
• Data is not typed. Everything is represented as strings and needs to be converted to target types: integer, boolean, decimal, etc.

3. ServiceNow Power BI Integration With Power BI Connector App

The third method will be to use a ready-to-use third-party solution, some kind of Power BI Connector for ServiceNow. It is a standalone app that helps to avoid involving experts in linking Power BI with ServiceNow and saves a lot of time for integration and reporting.

The plugin has advanced functionality for data filtering and selection, does not collect, store or share any information and supports Power Query for complex data models. It has also a trial version that allows you to use the app for free for a limited time to understand if you need it.

It is very easy to install. Go to the ServiceNow Store and find a product named Power BI Connector. You need only to choose whether you want to try the plugin or to buy it at once, find it using the filter criteria and search bar, and click Install.

Power BI Connector also has a very intuitive interface and use mode. Here are the main steps to import data from ServiceNow to the Power BI dashboard:

Step 1: Create a Data Source

Before performing this step ensure you have a PBI ADMIN or PBI EDITOR role. These roles must be assigned to Power BI Connector users by ServiceNow System Admin, who has permission to work with the connector by default.

1. Depending on the roles assigned, you can choose one of the following two locations to start from. Navigate either to the Power BI Connector page (available by default) Instance-Name.service-now.com/pbi_connector: or go to the Service Portal main page Instance-Name.service-now.com/sp: (only available if the administrator has previously configured this in the interface). You will see the Data Sources table. Click the New button.
2. Fill in corresponding fields like Data Source name and Description, set Rows limit, and configure fields of one or more tables that you want to export to Power BI. Configure appropriately Use display value if you need your data in string format. Click Save. Remember, when adding the table you can narrow your export data by selecting only records needed for the reports. It can be done by using corresponding filtering options.

Step 2: Import data to Power BI

1. Find the data source created in the previous step in the list of data sources.
2. Click the data source with the right mouse button, and the menu will open. Choose Copy OData feed URL; otherwise, you can use the Copy action available for each particular data source.
3. Go to the Power BI Desktop, press Get Data, Select OData feed, and enter the URL  copied earlier. To save, click OK.  The new window will open.
4. Switch to the Basic authentication tab. Here you will be asked to enter your ServiceNow credentials. When done, press the Connect button.
5. Check appropriate tables (that are to be loaded) in the Navigator preview and confirm your choice by pressing the Load button.

The import will start automatically.

Pros of this method:

• Easy to use. Does not require coding and other technical skills.
• Takes only several minutes to install and use.
• Extensive filtering options. The app allows exporting multiple Servicenow tables within a single data source.
• Supports Power Query which makes it possible to merge ServiceNow with other data sources.
• Have no limitations in exported data amount. To reduce the server load during the export process, the application provides the server-side pagination of data over the OData protocol (limited to 1.000 rows per page).
• The app is regularly updated and maintained.
• You can fully control access to the data with the provided roles within the application.
• Exported data is typed. You don’t need to convert data types as with previous methods.
  

Cons of this method:

• You need to install a third-party application.
• It is not free.

Conclusion

As you can now see there are several ways to perform Power BI ServiceNow integration. To choose the one that suits your business best you should consider the time and costs you can afford. If your staff includes people with coding skills, and your budget is limited, you can choose more creative ways to connect those two tools like REST API and CSV Files. If you need to load massive datasets and have no team members with strong technical knowledge, maybe it would be more appropriate to invest some money and install a third-party Power BI ServiceNow connector.