ServiceNow is a cloud-based platform that provides a wide range of IT service management (ITSM) and business process automation (BPA) solutions. It is designed to streamline and automate various organisational processes, such as incident management, problem management, change management, and asset management.
ServiceNow is a central hub for managing IT services and workflows within an organisation. It helps businesses manage their IT infrastructure, deliver services to end-users, and maintain operational efficiency. ServiceNow offers a user-friendly interface and robust features that allow organisations to handle complex tasks, track service requests, and maintain service level agreements (SLAs).
As a Power BI developer, you might need to extract data from ServiceNow to gain insights and create visualisations for reporting and analysis. Integrating ServiceNow data into Power BI allows you to leverage the platform's advanced data modelling and visualisation capabilities to generate meaningful reports and dashboards. This enables you to monitor key performance indicators (KPIs), identify trends, and make data-driven decisions based on the information stored in ServiceNow.
This post will dive into two ways to connect Power BI to ServiceNow data: API and AlphaServe connector.
ServiceNow API
ServiceNow provides a RESTful API allowing developers to access and retrieve data programmatically. With the API, you can request HTTP to the ServiceNow API endpoints, retrieve data in JSON format, and then import it into Power BI using the "Web" connector or custom Power Query code. This method provides more flexibility as you can specify the data you need, apply filters, and automate the data retrieval.
ServiceNow authentication in Power BI
it's important to note that all API requests require basic authentication, which involves providing a username and password to authenticate your request. This authentication process ensures that only authorised users can access the ServiceNow data.
However, it's crucial to emphasise that the level of authorisation and access to ServiceNow data may vary from one organisation to another. The specific permissions and privileges granted to users in ServiceNow, including API access, are typically managed by the organisation's ServiceNow administrators.
API Basic request
ServiceNow requests utilise the Web.Contents() function to access the ServiceNow API endpoint. In this basic case, the endpoint at "https://{ServiceNowInstance}/api/now/" retrieves data from a specified table (indicated by {tableName}). The code specifies the headers for the request, including the "Accept" and "content-type" headers.
Request with query parameters
The code below accesses the ServiceNow API endpoint at "https://{ServiceNowInstance}.service-now.com/api/now/" and retrieves data from a specified table (indicated by {tableName}). The code includes several query parameters to control the behaviour of the request. The "sysparm_offset" and "sysparm_limit" parameters are used for pagination, allowing you to offset and limit the number of results returned. The "sysparm_display_value" parameter determines whether to return the display or raw values for the fields. The "sysparm_query" parameter contains a query string that filters the results based on a specific condition. The "sysparm_fields" parameter specifies the fields to include in the response.
Aggregation endpoint
The code below performs a ServiceNow request using the aggregate API (stats endpoint). It accesses the ServiceNow API endpoint at "https://{ServiceNowInstance}.service-now.com/api/now/" and retrieves statistics for a specified table (indicated by {tableName}). The code includes the "sysparm_count" query parameter set to "true," indicating that it requests the count of items in the table. The result can be useful for obtaining the number of elements in a table, which can be helpful for tasks such as paging through the elements.
ServiceNow REST API Explorer
The ServiceNow REST API Explorer is a web-based tool provided by ServiceNow that allows developers and administrators to explore and test the ServiceNow REST APIs interactively. It provides a user-friendly interface to discover the available APIs, view the API documentation, and experiment with different API calls.
It streamlines exploring, understanding, and testing the REST APIs, facilitating the development and integration of ServiceNow functionalities into custom applications, scripts, or Power BI reports.
ServiceNow API further considerations
Paging is often required when working with large datasets in ServiceNow or any other API. The code we provided includes parameters like "sysparm_offset" and "sysparm_limit" which facilitate paging.
It's important to note that for global organisations with vast data in ServiceNow, the code we provided may not perform properly as it can not implement incremental refresh; this may require different tools to ensure efficient and optimised data updates in Power BI like Power BI Connector by acSoft, US subsidiary of Alpha Serve.
Power BI Connector
As a ServiceNow Build Partner, acSoft Inc offers a Power BI Connector specifically designed for ServiceNow. This connector simplifies the integration process by directly connecting ServiceNow and Power BI. It lets you connect to your ServiceNow instance, select the desired tables, and import the data into Power BI. The connector handles authentication, data transformation, and periodic data refreshes, making it a convenient option for Power BI developers.
The Power BI Connector for ServiceNow is an enterprise application that offers seamless integration between ServiceNow and Power BI. It provides a simple and efficient way to extract ServiceNow data and bring it into Power BI for analysis and visualisation.
The advantage of using this connector is that you don't need to rely on ServiceNow consultants or architects to implement it within your ServiceNow platform. It is a user-friendly and pre-configured application that can be readily used without extensive setup or additional professional assistance. The connector also comes with reliable support and continuous improvements based on user feedback.
By leveraging the Power BI ServiceNow integration app, users can explore ServiceNow data visually through intuitive dashboards. They can create impactful reports within Microsoft Power BI, empowering data-driven decision-making within their organisation. This integration enhances the analysis and reporting capabilities, enabling users to derive valuable insights from ServiceNow data in an efficient and user-friendly manner.
AlphaServe Power BI connector key features
ServiceNow ITIL report templates - AlphaServe offers Power BI dashboard templates with pre-configured data sources
Enhanced User Experience - Benefit from an intuitive user interface within the ServiceNow portal, allowing seamless connectivity between ServiceNow and other data sources with a no-code experience. This simplifies the process of integrating data into Power BI, providing a user-friendly environment for users of all levels of technical expertise.
Advanced Reporting Capabilities - Empower ServiceNow reporting by leveraging powerful features such as filtering options and Power Query Support. These capabilities enable comprehensive reporting, allowing users to extract, transform, and load data from ServiceNow into Power BI for in-depth analysis and visualizations.
Streamlined Dashboard Creation - Select from a range of built-in ServiceNow dashboard templates that come pre-configured with data sources. These templates make it easy to create visually appealing dashboards and seamlessly export them to Power BI, saving time and effort in designing and configuring dashboards from scratch.
Automated Data Refresh - Ensure that reports and dashboards in Power BI always display up-to-date ServiceNow data by scheduling automated data refreshes. This feature allows you to define the frequency at which the data is refreshed, keeping your insights accurate and timely.
Incremental Refresh Support - With the Power BI Connector for ServiceNow, take advantage of incremental refresh functionality. This feature enables efficient updates by refreshing only the new or modified data since the last refresh, reducing the load on both the ServiceNow instance and Power BI. It ensures faster and more efficient data updates in Power BI reports.
Robust Data Security - Maintain data security by leveraging convenient and predefined user role management within the connector. Grant appropriate permissions for data access, ensuring that users only have access to the relevant ServiceNow data based on their roles and responsibilities. This helps maintain data confidentiality and integrity while providing granular control over access to sensitive information.
Conclusion.
In conclusion, the Power BI integration with ServiceNow plays a crucial role in empowering organizations to extract meaningful insights from their ServiceNow data. While the ServiceNow API offers direct access to data, it requires manual coding and lacks certain built-in features for data transformation and visualization. On the other hand, the AlphaServe connector provides a more seamless and user-friendly experience, enabling a no-code integration between ServiceNow and Power BI.
Pros and cons.
API with Web.Contents
👍 Pros:
✅ Provides direct access to ServiceNow data.
✅ Offers flexibility to customise API calls.
✅ "Free" if built by an internal senior Power BI developer with ServiceNow knowledge.
👎 Cons:
❌ Requires manual coding and configuration.
❌ Requires deep senior-level Power BI knowledge that can be very costly.
❌ Lack of built-in features for data transformation and visualisation.
❌ Limited support for data refreshing and incremental updates.
❌ Doesn't work great for enterprise-grade solutions.
AlphaServe Connector
👍Pros:
✅ Simplifies ServiceNow integration with Power BI.
✅ Provides a no-code experience for connecting ServiceNow and Power BI.
✅ Offers pre-built templates and dashboards for quick and easy deployment.
✅ Supports advanced features like data refreshing, incremental updates, and scheduled refreshes.
✅ Provides reliable support and continuous improvements based on user feedback.
✅ An intuitive user interface for easy setup and configuration.
✅ Non-deep Power BI expertise required.
✅ Cheaper than hiring an experienced senior Power BI developer with ServiceNow knowledge.
👎 Cons:
❌ Query filters not available.
❌ Cost considerations.