ServiceNow Power BI Integration: How to Connect & Get Started? | 2 Easy Methods

From a simple Ticketing Platform to a complete IT Automation and Infrastructure Platform with features like Security, Customer Support, HR, and ServiceNow Webhook Integration with popular Business Applications, ServiceNow has become a well-known application. ServiceNow makes a business stronger, more integrated, and ready for expansion, just like a holistic fully functional organism.

Power BI is a technology-driven Business Intelligence solution from Microsoft that analyses and visualizes raw data to offer actionable information. It combines Business Analytics, Data Visualization, and various best practices to help businesses make data-driven decisions. In February 2019, Gartner designated Microsoft the Leader in the “2019 Gartner Magic Quadrant for Analytics and Business Intelligence Platform” due to the capabilities of the Power BI platform.

This article will show you about ServiceNow Power BI Integration. It will also provide some pointers that can help you avoid any Power BI errors.

What is Microsoft Power BI?

Microsoft’s Power BI is a Business Intelligence tool. Users can leverage it to examine data from a variety of sources and create Reports and Dashboards. It can be used as a standalone desktop app or as a fully managed Cloud-based web service. While the Power BI Desktop is a free download, the Power BI Service is a subscription-based service that charges users based on their usage.

Power BI Mobile is now available from Microsoft for those who want to keep an eye on their data while on the go. Analytical features can also be included in custom web apps using Power BI. It works well with the majority of Microsoft’s enterprise products.

Power BI takes advantage of the ability to connect to the most common databases outside of the Microsoft ecosystem and create simple, Interactive Dashboards from them.

Key Features of Microsoft Power BI

Power BI has a large number of capabilities that set it apart from other BI applications. The following are some of these characteristics:

• It has a large number of visually appealing Visualization Templates to pick from. To display your dataset, you can generate Reports and Dashboards using as simple or as elaborate visuals as you desire.
• Power BI features a function called “Get Data” that allows you to choose from a variety of data sources, including On-Premise, Cloud-Based, Unstructured, and Structured data, among others. Every month, new data sources are added.
• You can filter your datasets in Power BI to focus on smaller datasets first. This allows you to focus on specific data in the dataset rather than the entire dataset at once.
• You may use a range of Graphical Elements to design your Dashboards. The Dashboards can be printed and shared.
• Dashboards, Data Models, Datasets, Embedded Queries, and many other features are available in Power BI’s “Content Packs.” Instead of searching for the pieces separately, you can use the elements directly from the “Content Packs.”

Explore more about: Xero Power BI Integration

What is ServiceNow?

ServiceNow is a SaaS (Software-as-a-Service) platform that automates business operations while providing a consistent and user-friendly experience. The platform provides a full development environment for building, testing, and implementing applications as it specializes in IT Service, Operation, and Business Management.

Multi-tenant architecture is used by several new-age enterprises, such as AWS, Azure, and Salesforce, to service numerous customers with a single instance. ServiceNow, on the other hand, uses a multi-instance design to generate a separate instance for each employee. Multi-instance architecture provides data isolation by isolating software applications, middleware, and databases for each customer.

Key Features of ServiceNow

• Automation Testing: Automated validation tests are used in testing, allowing users to quickly move their applications into production. It also reduces the risks associated with new apps while focusing on their functionality. The necessity for separate test data can be avoided with automation in testing.
• Predictive Intelligence: It is an AI/ML system that aids in the reduction of mistake rates and resolution time. This feature automatically clusters groups in order to comprehend the records that correspond to each one. To increase query resolution time, it also offers TF-IDF (term frequency-inverse document frequency) based classification.
• Analytics and Reporting: In an iterative process, analytics and reporting are adjusted to give a better experience. Instead of going through the complete report builder menu, ServiceNow can generate reports just by submitting a query. Furthermore, NLQ (natural language query) bridges the gap between technical and non-technical users, allowing useful data to be discovered.

How to Connect ServiceNow Power BI?

Method 1: Connect ServiceNow Power BI using REST APIs

Step 1: Create a ServiceNow User Account with REST Privileges

The first step in this method of ServiceNow Power BI is to create a ServiceNow user account and assign the REST role as well as the read role to the table form where Power BI will retrieve the records. Let’s imagine you wish to retrieve records from the change_request table. Here the username is Power.Now. You’ve now added the sn_change_read and snc_platform_rest_api access roles to the mix.

Note that the sn_change_read function is only available if the ITSM Roles — Change Management plugin (com.snc.itsm.roles.change_management) is installed.

Step 2: Fetch your REST Endpoint from ServiceNow REST API Explorer

REST Endpoint URI: A URI that can be used to retrieve data from Power BI. ServiceNow REST API EXPLORER is a clever and simple approach to acquire this. To get it right, follow the steps below.

• Select table change request in the REST API Explorer. Other settings can be set. For more information, see the screenshot attached.
• Set sysparm_display_value to true. This will return the reference/choice fields’ display value.
• Set sysparm_exclude_reference_link to true. If false, Power BI will give you the API link for the reference records, which you don’t require. When this is true, you will also have to deal with additional challenges in Power BI.
• It will appear as a link titled “Record” in PowerIn Power BI. And getting the actual value/transform data into PowerBi will be difficult or impossible. All the empty reference fields will result in an “Error” in Power BI.
• Sysparm_fields is a list of the fields in Power BI that you desire. Try to limit it to a bare minimum.
• There are only two possibilities for sysparm_limit. One or ten. You can enter any value and alter it later.

Find out what the endpoint is. You’ll find code samples in a variety of languages as you scroll down. Take a look at anyone and copy the endpoint. 

Step 3: Select Web Data Source in Power BI

PowerBI desktop should be installed on your system as a prerequisite. The next step in ServiceNow Power BI integration is to connect the Data Source. Open Power BI and select Get data from the menu. Choose the web and then connect.

Copy the ServiceNow URI from the previous stages and put it into the URL sections. Set accept to application/json in the HTTP request header. Click the OK button.

Step 4: Configure your ServiceNow Endpoint in Power BI

The system will question you for access when you click OK. Select basic and enter your ServiceNow credentials. Connect by clicking the connect button.

Once you’ve clicked connect, you’ll be sent to a new page. When your ServiceNow Power BI is connected, the screen below will appear. 

There are no records to be found here. You’ll need to do a few transformations before converting them to a tabular format. On the top left corner of the screen, select “Into table.” Take a look at the image above. When you click “Into table,” a table with name and value will appear. 

In the “Value” column, click the list link. This will display a single column with a list of all the records.

Click “To Table” once more. A pop-up window will appear. Fill in the fields as shown below.

“Select or enter delimiter“: none, and “How to Deal with Extra Columns“: Add to the last column. Click the OK button. When you click OK, all of the data will be split into two columns, as shown in the screenshot below.

Column 1 should be expanded, but column 2 should be ignored: An expand icon can be seen in the column 1 header. That should be clicked. A window will appear, prompting you to choose the columns. In the ServiceNow REST endpoint, you’ll get a list of all the fields you’ve configured. Uncheck the box that says “Use original column name as prefix.”

Limitations of Connecting ServiceNow Power BI using REST

• Check to see if you’re exporting big amounts of data. A maximum of 50K records can be fetched and the retrieval time is around 2 minutes.
• Large amounts of data can be exported. At export, you must increase the transaction quota rule in ServiceNow for “REST Table API Request Timeout,” which is set to 60 seconds by default.
• Don’t strive to get all of the fields; instead, keep it to a bare minimum.
• In Power BI, you won’t be able to set up incremental data updates. It means that you’ll have to refresh all of the data every time. When the data source is the web, Power BI does not enable incremental data updates.
• As noted in the previous steps, you must set sysparm_exclude_reference_link to true when configuring the REST endpoint, or you will get an error.

Method 2: Connect ServiceNow Power BI using CSV Files

Step 1: Login to ServiceNow

• In any web browser, type the base URL.
  • You are automatically logged in if your system employs external authentication. When you log in to your computer, you may, for example, log in to company services.
  • The Welcome page shows if your system does not use External authentication.
• Put your username and password in the boxes below.
• (Optional) If you click the Remember Me option, you’ll be logged in until you log out manually.
  • This option can be enabled or disabled by the administrator. Change the settings for the Remember me checkbox and cookies for additional details.
• Click Login or press the Enter key.

Step 2: Export Data from ServiceNow as CSV/Excel Files

Created a mailbox with an E5 license (requires SharePoint, MicrosoftFlow, and Power BI access).

You must arrange a report to be delivered to that mailbox in ServiceNow (or set of the reports if you have big chunks of data which you going to merge in Power BI, CSV is recommended, as xlsx has some limitations)

Step 3: Select Excel/CSV Data Source in Power BI

In SharePoint, create a Data Source location (the folder which the account will be accessing and where you will be storing data)

Set up Flow to save attachments to the Data Source from the previously created mailbox:

Step 4: Import ServiceNow CSV Data into Power BI

Set up the report in Power BI Desktop by pointing to the Data source. 

It should be published. Set up a Scheduled Refresh in Power BI Online.

Limitations of Connecting ServiceNow Power BI using CSV Files

• Since it’s a Service account / Mailbox, it should be set up so that the password never expires (every password change would affect Power BI data pull schedule).
• Outages in SharePoint on a global scale may have an impact on performance.
• The entire procedure, from transmitting data to viewing it in a report, could take up to 15-20 minutes.
• The Power BI refresh cycles have a limitation. 

Conclusion

You have learned about the core steps required to be done in ServiceNow Power BI Integration in this article. After that, you looked at how to do ServiceNow Power BI Integration using CSV files. Setting ServiceNow Power BI Integration manually can be challenging especially for a beginner & this is where Hevo saves the day.

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline can seamlessly transfer data from a vast sea of 100+ sources to a Data Warehouse, BI Tool like Power BI, or a Destination of your choice. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!  

If you are using Power BI as your Data Analytics & Business Intelligence platform and searching for a no-fuss alternative to Manual Data Integration, then Hevo can effortlessly automate this for you. Hevo, with its strong integration with 100+ sources and BI tools (Including 40+ Free Sources), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Share your experience of learning about ServiceNow Power BI Integration! Let us know in the comments section below!