For users who want to analyze incidents and track progress, ServiceNow does provide out of the box reporting and analytics capabilities. In our use scenario, our customer wanted the ability to leverage an analytics platform like PowerBI in order to slice and dice and visualize the data in a variety of ways.

More specifically, there was a need to understand cross functional ITSM impacts on other areas of the organization to develop a comprehensive view and explore potential metrics and KPIs through a single integrated data view. Hence, extracting data from ServiceNow into Azure and/or PowerBI integration into ServiceNow was established and a defined requirement.

In addition, our customer wanted to create a POC to apply ML/AI on ITSM operations to understand root cause analysis and directly impact cost. There is a finite and measurable cost for each ticket created.

The sooner major incidents can be identified and resolved, the more they would save on costs. ML/AI leveraged models that could identify root cause, minimize duplication of tickets, reduce time to resolution and further reduce the operational complexity of managing a large number of open tickets all resulting in a simplified, better managed process with a measurable cost savings. For more info Servicenow Training

This post will demonstrate the integration options between Azure and ServiceNow as well as leveraging Azure to apply AI/ML on some of the scenarios described earlier. We will share some of the learnings we had as we went through this journey. Various integration points between Azure and ServiceNow:

1. Azure Data Factory to ServiceNow Integration
2. Connect directly to ServiceNow with PowerBI using the SIMBA driver
3. Leverage PowerBI Premium ML in order to execute models for use cases described above leveraging ServiceNow data

At a high level, the solution will look as follows:

Azure Data Factory ServiceNow Connector Integration

With Azure Data Factory, there are two integration options into ServiceNow:

1. ServiceNow Connector out of the box or
2. REST API Connector
   
   

ServiceNow Connector

In you Azure Data Factory, create a new connection and search for ServiceNow as shown below

Configure the ServiceNow connectivity:

Key takeaways from the ServiceNow connectivity option:

• The connector is easy to configure and provides access to the out of the box tables and fields in ServiceNow.
• If you are looking for the Problems or Incidents table in ServiceNow, this connector is a great way to get started.
• However, in our scenario we had many user defined tables and fields in ServiceNow and this connection option does not yet support user defined types. Therefore, we went ahead and tested the next option, REST connection. Servicenow Certification
  

REST API Connector

In Azure Data Factory, create a new connection and search for REST as shown below

Configure the REST API to your ServiceNow instance.

Key takeaways from the REST API connector option:

• Uses the REST API access capabilities provided by ServiceNow
• We used basic authentication to provide REST API access
• The end point and relative URL constructed by using ServiceNow REST explorer. Please see the screenshot below from ServiceNow portal:

Login to ServiceNow and Search for “REST”

• You can use it to GET/CREATE/RETRIEVE/etc… records from sources.

• In our case, we want Incidents and Problems data so we can build some analytics and apply AI/ML to this data.

As a side note, we found the Tables/Schemas documentation for ServiceNow that was relevant for the areas we were interested in like Problems leading to one or many Incidents. The ServiceNow models and schemas provided were a great way to understand what was available to us and what table elements we would need.

Here is a sample of the Incidents table and its associated relationships:

Once you’ve established the linked service, you can even filter using the REST APIs as shown below.

Key takeaways from configuring the REST API connector:

• REST API call allows data filtering; we can use the ServiceNow REST explorer to construct the relative URL with extra parameters including data filters.
• The relative URL can be dynamically constructed by using Azure Data Factory expressions, functions and system variables. In our case, we are only interested in the last 365 days of Incidents (adddays(utcnow(‘yyyy-MM-dd’),-364)…

• If you click on “Mappings”, you can see with the REST API user defined fields like “u_rca_status” and “u_major_incident”
• In our scenario, we want to extract data from ServiceNow and put it into Azure SQL PaaS instance so we can execute queries without impacting ServiceNow. Along the way, we discovered ADF natively translates the JSON schema which is mapped to the target table

PowerBI Desktop to ServiceNow via SIMBA driver

We discovered that PowerBI provided a ServiceNow app that provided an out of the box dashboard into ServiceNow however after we discovered it, it was subsequently removed as an option and hence we had to look for alternative connectivity options like the SIMBA driver. To get more skills enroll for Sericenow Online Training

The SIMBA is a 3rd party vendor that driver provides JDBC/ODBC connectivity to ServiceNow. We installed the SIMBA driver and was able to connect relatively easily to ServiceNow using an JDBC/ODBC driver. The SIMBA driver was also able to read the user defined tables and columns as the Azure Data Factory REST API described earlier

The Analytics, Intelligence, and Reporting products help provide many different kinds of data to many different kinds of users. Listen to the Story of the Four Stakeholders, which describes the kinds of data different people in your organization might want to see.

Get the insights you need

The Analytics, Intelligence, and Reporting products can help you to lower costs and increase productivity through process improvement, self-service, and automation. Service owners can deliver and refine AI capabilities quickly, gaining greater insight into real-time patterns and trends for service delivery teams.

This information enables you to make better, faster decisions—without the need for data science expertise. For more info Servicenow Training

Performance Analytics:

Performance Analytics

enables businesses to set, track, and analyze progress against goals. The products helps you improve performance and accelerate continual service improvement by:

• Tracking critical process metrics and trends.
• Measuring process health and behavior against organizational targets.
• Identifying process patterns and potential bottlenecks before they occur.
• Continually visualizing the health of processes through both historical and real-time statistics in role-based dashboards, so you and your business can make informed decisions.

Spotlight:

Spotlight illuminates records that otherwise you might overlook due to evaluating only one aspect of given records. You can define weighted criteria to identify and rank records that require attention, such as when triaging incidents or performing lead scoring. You can rank records based on multiple dimensions, instead of by a single field value such as priority. While most organizations address high-priority items in a timely manner, lower priority items sometimes are not addressed for an extended period of time. Spotlight helps you focus on items based on business need. learn more from Servicenow Developer Training

Virtual Agent:

Implementing a virtual agent to handle common requests and tasks enables your users to get immediate help, day or night. Providing your virtual agent on channels familiar to your users, such as third-party messaging apps, to offer a convenient way for them to contact you. A virtual agent can also offer personalized customer experiences by applying and remembering user information during the conversation.

Natural Language Understanding:

Natural Language Understanding (NLU) provides an NLU model builder and an NLU inference service that enable the system to learn and respond to human-expressed intent. By entering natural language examples into the system, you help it evaluate word meanings and contexts so it can infer user or system actions.

Predictive Intelligence

Predictive Intelligence uses machine learning to shorten triage and categorization time, contributing to higher customer satisfaction. Pinpoint issues and deliver actionable insights to get service owners and agents to faster resolutions.

Get started

• Attend the "Getting Started with Performance Analytics" webinar to learn how you can improve performance by visualizing critical metrics and trends. Discover how you can use Performance Analytics to get real-time insight into influential factors in each stage of your service to help you meet and exceed your business goals.
• For information about free classes, office hours, and other Performance Analytics resources. For more depth knowledge, enroll for Servicenow Online Training
• Pre-packaged Analytics and Reporting Solutions are available to integrate Analytics, Intelligence, and Reporting tools with your ITSM, CSM, or HR ServiceNow products.

This post talks about my experience of building a small scale ETL with Pandas. It also offers some hands-on tips that may help you build ETLs with Pandas.

Background: Recently, I was tasked with importing multiple data dumps into our database. The data dumps came from different source, e.g., clients, web. We were lucky that all of our dumps were small, with the largest were under 20 GB. Also, the data sources were updated quarterly, or montly at most, so the ETL doesn’t have to be real time, as long as it could re-run.

SQL vs. Pandas

For simple transformations, like one-to-one column mappings, caculating extra columns, SQL is good enough.

However, for more complex tasks, e.g., row deduplication, splitting a row into multiple tables, creating new aggregate columns with on custom group-by logic, implementing these in SQL can lead to long queries, which could be hard to read or maintain.

Python and Pandas are great for many use cases, but Pandas becomes an issue when the datasets get large because it’s grossly inefficient with RAM.

In our case, since the data dumps are not real-time, and small enough to run locally, simplicity is something we want to optimize for.

Our reasoning goes like this: Since part of our tech stack is built with Python, and we are familiar with the language, using Pandas to write ETLs is just a natural choice besides SQL.

Writing ETL in a high level language like Python means we can use the operative programming styles to manipulate data. For more info ETL Testing Online

Useful Pandas functions

Most of my ETL code revolve around using the following functions:

• drop_duplicates
• dropna
• replace / fillna
• df[df['column'] != value]: filtering
• apply: transform, or adding new column
• merge: SQL like inner, left, or right join
• groupby
• read_csv / to_csv

Functions like drop_duplicates and drop_na are nice abstractions and save tens of SQL statements.

And replace / fillna is a typical step that to manipulate the data array.

One thing that I need to wrap my head around is filtering. Writing

df[df['column'] != value]

was a bit awkward at first. This has to do with Python and the way it overrides operators like []. I haven’t peeked into Pandas implementation, but I imagine the class structure and the logic needed to implement the __getitem__ method.

Pandas + Jupyter Notebook

Data processing is often exploratory at first. This is especially true for unfamiliar data dumps. We need to see the shape / columns / count / frequencies of the data, and write our next line of code based on our previous output. So the process is iterative.

One tool that Python / Pandas comes in handy is ETL Testing Course. It’s like a Python shell, where we write code, execute, and check the output right away. However, it offers a enhanced, modern web UI that makes data exploration more smooth.

Also, for processing data, if we start from a etl.py file instead of a notebook, we will need to run the entire etl.py many times because of a bug or typo in the code, which could be slow.

In Jupyter notebook, processing results are kept in memory, so if any section needs fixes, we simply change a line in that seciton, and re-run it again. There is no need to re-run the whole notebook (Note: to be able to do so, we need good conventions, like no reused variable names, see my discussion below about conventions).

My workflow was usually to start with notebook, create a a new section, write a bunch of pandas code, print intermediate results, and keep the output as reference, and move on to write next section. Eventually, when I finish all logic in a notebook, I export the notebook as .py file, and delete the notebook.

While writing code in jupyter notebook, I established a few conventions to avoid the mistakes I often made.

• Avoid global variables; no reused variable names across sections.
• Avoid writing logic in root level; Wrap them in functions so that they can reused.
• After seeing the output, write down the findings in code comments before starting the section. Doing so helps clear thinking and not miss some details

Stablizing IDs

When doing data processing, it’s common to generate UUIDs for new rows. For debugging and testing purposes, it’s just easier that IDs are deterministic between runs. In other words, running ETL the 2nd time shouldn’t change all the new UUIDs.

To support this, we save all generated ids for a temporary file, e.g., generated/ids.csv. This file is often the mapping between the old primary key to the newly generated UUIDs. We sort the file based on old primary key column and commit it into git.

To get in-depth knowledge, enroll for a live free demo on ETL Testing Training

This way, whenever we re-run the ETL again and see changes to this file, the diffs will us what get changed and help us debug.

Prerequisites

To use this information, you should be familiar with Salesforce Analytics, Mule, Anypoint Connectors, Anypoint Studio, Mule concepts, elements in a Mule flow, and Global Elements.

You need login credentials to test your connection to your target resource.

POM File Information

<dependency>

<groupId>

com.mulesoft.connectors

</groupId>

<artifactId>

mule-sfdc-analytics-connector

</artifactId>

<version>

RELEASE

</version>

<classifier>

mule-plugin

</classifier>

</dependency>

Mule converts RELEASE to the latest version.

To specify a version, view Salesforce Einstein Analytics Connector in Anypoint Exchange and click Dependency Snippets. For more info Mulesoft Training

Connect in Design Center

1. In Design Center, click a trigger such as this connector as they trigger, HTTP Listener,or Scheduler trigger.
2. To create an optional global element for the connector, you can choose from the following options. More information is provided in the sections that follow, and links to Salesforce documents are listed in the See Also section of this document.
• Required Parameters for Basic Username Password Authentication
• Required Parameters for the OAuth 2.0 Configuration
• Required Parameters for the OAuth 2.0 JWT Bearer Configuration
• Required Parameters for the OAuth 2.0 SAML Bearer Configuration
3. Select the plus sign to add a component.
4. Select the connector as a component.
5. Configure these fields for Upload external data into new dataSet and start processing operation:
• Type - Type of the records to be inserted. You need to upload a JSON file representing the schema of the dataset to be created.
• Records - Data sense expression; the records to be inserted
• Operation - Which operation to use when you’re loading data into the DataSet
• Description
• Label
• Data Set Name

Required Parameters for Basic Username Password Authentication

• Username: Enter the Salesforce Analytics username.
• Password: Enter the corresponding password.

Required Parameters for the OAuth 2.0 Configuration

• Consumer Key - The consumer key for the Salesforce connected app.
• Consumer Secret - The consumer secret for the connector to access Salesforce.

Required Parameters for the OAuth 2.0 JWT Bearer Configuration

• Consumer Key - The consumer key for the Salesforce connected app.
• Keystore File - See Generating a Keystore File.
• Store Password - The password for the keystore.
• Principal - The Salesforce username that you want to use.

Required Parameters for the OAuth 2.0 SAML Bearer Configuration

• Consumer Key - The consumer key for the Salesforce connected app.
• Keystore File - The path to the key store used to sign data during authentication* Only Java key store format is allowed.
• Store Password - Key store password

Generating a Keystore File

The Keystore is the path to the keystore used to sign data during authentication. Only Java keystore format is allowed.

To generate a keystore file:

1. Go to your Mule workspace, and open the command prompt (for Windows) or Terminal (for Mac).
2. Type keytool -genkeypair -alias salesforce-cert -keyalg RSA -keystore salesforce-cert.jks and press enter.
3. Enter the following details:
• Password for the keystore.
• Your first name and last name.
• Your organization unit.
• Name of your city, state, and the two letter code of your county.The system generates a java keystore file containing a private/public key pair in your workspace.
4. Provide the file path for the Keystore in your connector configuration.Type keytool -exportcert -alias salesforce-cert -file salesforce-cert.crt -keystore salesforce-cert.jks and press enter.The system now exports the public key from the keystore into the workspace. This is the public key that you need to enter in your Salesforce instance.
5. Make sure that you have both the keystore (salesforce-cert.jks) and the public key (salesforce-cert.crt) files in your workspace.

Add the Connector to a Studio Project

Anypoint Studio provides two ways to add the connector to your Studio project: from the Exchange button in the Studio taskbar or from the Mule Palette view.

Add the Connector Using Exchange

1. In Studio, create a Mule project.
2. Click the Exchange icon (X) in the upper-left of the Studio task bar.
3. In Exchange, click Login and supply your Anypoint Platform username and password.
4. In Exchange, search for "analytics".
5. Select the connector and click Add to project.
6. Follow the prompts to install the connector.

Add the Connector in Studio

1. In Studio, create a Mule project.
2. In the Mule Palette view, click (X) Search in Exchange.
3. In Add Modules to Project, type "analytics" in the search field.
4. Click this connector’s name in Available modules.
5. Click Add.
6. Click Finish.

Configure in Studio

1. Drag the connector to the Studio canvas.
2. To create a global element for the connector, set these fields:
• Basic Authentication:
• Username: Enter the Salesforce username.
• Password: Enter the corresponding password.
• Security Token: Enter the corresponding security token.
• OAuth 2.0:
• Consumer Key - The consumer key for the Salesforce connected app.
• Consumer Secret - The consumer secret for the connector to access Salesforce.
• OAuth 2.0 JWT:
• Consumer Key - The consumer key for the Salesforce connected app.
• Keystore File - See Generating a Keystore File.
• Store Password - The password for the keystore.
• Principal - The password for the keystore.
• OAuth 2.0 SAML:
• Consumer Key - The consumer key for the Salesforce connected app.
• Keystore File - See Generating a Keystore File.
• Store Password - The password for the keystore.
• Principal - The password for the keystore.
3. Configure these fields for upload external data into new dataSet and start processing operation:

To get in-depth knowledge, enroll for a live free demo on Mulesoft Online Training

• Type - Type of the records to insert. Select a JSON file representing the schema of the dataset to be created.
• Records - DataSense expression - the records to be inserted.
• Operation - Which operation to use when you’re loading data into a data set.
• Description
• Label
• Data Set Name

It can run on both virtual and physical machines. It is a multi-user, multi-process and multi-threaded system.

Tableau Server Architecture

The various layers used in the Tableau server are given in the following architecture diagram

Let's study the different components of Tableau Architecture

Data Server

The primary component of Tableau Architecture is the Data sources it can connect to it.

Tableau can connect to multiple data sources. These data sources can be on-premise or remotely located. It can connect to a database, excel file, and a web application all at the same time. Tableau can connect data from heterogeneous environments. It can blend the data from multiple data sources. It can also make the relationship between various types of data sources. Learn more from Tableau Training

Data Connectors

The Data Connectors provide an interface to connect external data sources to Tableau Data Server.

Tableau has in-built ODBC/SQL connector. This ODBC Connector can connect to any databases without using their native connector. Tableau has an option to select both live and extract data. Based on the usage, one can be easily switched between extracted and live data.

• Live Connection or Real time data: Tableau can connect to real time data by linking to the external database directly. It uses the infrastructure of existing database system by sending dynamic MDX (Multidimensional Expressions) and SQL statements. This feature can link to the live data with Tableau rather than importing the data. It makes good the investment done by an organization on a fast and optimized database system. In many enterprises, the size of the database is huge and is updated periodically. In those cases, Tableau works as a front-end visualization tool by connecting to the live data.

Components of Tableau Server

The different components present in a Tableau server are:

• Application Server
• VizQL Server
• Data Server

A) Application Server:

The application server is used to provide the authentications and authorizations. It handles the administration and permission for web and mobile interfaces. It assures security by recording each session id on Tableau Server. The administrator can configure the default timeout of the session in the server.

B) VizQL Server:

VizQL server is used to convert the queries from the data source into visualizations. Once the client request is forwarded to VizQL process, it sends the query directly to data source and retrieves information in the form of images. This image or visualization is presented to the user. Tableau server creates a cache of visualization to reduce the load time. The cache can be shared across many users who have the permission to view the visualization.

C) Data Server:

Data server is used to manage and store the data from external data sources. It is a central data management system. It provides metadata management, data security, data storage, data connection and driver requirements. It stores the relevant details of data set such as metadata, calculated fields, sets, groups, and parameters. The data source could extract data as well make live connections to external data sources.

Gateway

The gateway channelizes the requests from users to Tableau components. When the client makes a request, it is forwarded to external load balancer for processing. The gateway works as a distributor of processes to various components. In case of absence of external load balancer, gateway also works as a load balancer.

For single server configuration, one primary server or gateway manages all the processes. For multiple server configurations, one physical system works as primary server while others are used as worker servers. Only one machine can be used as a primary server in Tableau Server environment.

To get in-depth knowledge, enroll for a live free demo on Tableau Online Course

Clients

The dashboards and visualizations in Tableau server can be viewed and edited using different clients. The Clients are Tableau Desktop, web browser and mobile applications.

• When you work with Hadoop and Tableau, you can connect to your Hadoop cluster in real-time and then extract the data from Tableau’s fast in-memory data engine.
• This prevents you to suffer from high latency of Hadoop.
• It gives you fast, direct connect to your databases.
• The swift data functions are a result of a powerful query process such that users extract data without waiting for the MapReduce queries to compile and execute.
• The in-memory data storage in Hadoop speeds up the slow databases available and further accelerates the overall visualization and reporting process in Tableau.
• The direct database connection in Tableau serves as a significant benefit to everyday users as they can leverage the potential of Hadoop in a familiar and easy-to-use Tableau interface.

The company partners with Tableau to provide excellent data solutions to business users at 100X the performance, having enterprise-grade security, without the overhead of moving, transforming and sampling data for analysis. This indeed relieves business groups and data experts by introducing a way to query billions of rows at top speed and an all-in-one solution for all that will meet your business on Hadoop needs.

Connecting Tableau directly to Hadoop is one of the biggest achievements to drive your business today and handle the big data explosion at the same time. The primary advantage of this integration is now you can take all your data at one location and still access it in a more secure, efficiently performing and controlled manner with the logics and syntactic the businesses people can understand and implement.

Benefits of using Tableau on Hadoop

With the announcement of AtScale joining hands with Tableau to work it on the Hadoop platform, all those Data Analysts, Business Intelligence Professionals, Big Data and Hadoop aspirants can now learn to work with Tableau and Hadoop together. Soon, you can expect a bulk of open job positions for the individuals having dexterity in both the data technologies keeping up with one another. Get more from Tableau Server training

But what are the real-time benefits of using the data visualization tool on the Hadoop distributed file system (HDFS)? In context to this brilliant invention, a Tableau partner said, “When looking to propose solutions to our clients, we look for innovative, proven solutions that deliver tangible value. The AtScale approach is well aligned with Tableau’s strategy, both from a business and a technology standpoint. We are excited to take this offer to market!’

The Tableau ‘BI on Hadoop’ invention will provide security, efficiency and speed to the big data processes to be handled with Hadoop, supporting all Hadoop distributions. Customers and clients can query Hadoop in seconds without going through the local setup and installation.

To get in-depth knowledge, enroll for a live free demo on Tableau Online Training

Ultimately, all paths of learning and using Tableau and Hadoop together will lead us to achieve a seamless, fast and competent data analytics in the enterprises, letting us gain tractions in the business.

This post will be divided into two sections. The first section is about Initial Setup, and the second section is about the Freeflow of updates between Service Now and any other tool Service Now is integrating with.

Initial Setup:

Initial setup is a phase where there is no data in Service Now, and you need to get in entire dump of data from the other tool.

Free flow of Updates:

Here you are more concerned about how to get the data that is Updated/inserted in the other tool, into Service Now in real time. For example, you need to quickly get the Incident created/updated in NetCool into Service Now.

Different ways we can do it:

JDBC/FTP(s)/HTTP(s)

JDBC offers a very robust way to connect directly to a Database like SQL/ Oracle using drivers and pull the information into Service Now. You can even give in your SQL query in the Service Now which will be executed and the data be fetched.

Pros:

It’s a kick ass way of getting the updates between two Databases at a time. The fastest when it comes to setting up an Initial dump of Data. You will be directly talking to the Database, with no middle man. I would go for it without thinking twice. It’s fast and secure.

Cons:

It really is very lethargic when it comes to free flow of updates. Say something got changed in the other tool, JDBC cannot tell you until the schedule that picks the data runs in Service Now. So it’s NOT recommended for free flow of updates.

I wouldn't talk much about the good ol’ FTP(s) and HTTPS(s). Both are excellent ways of getting the information into Service Now. They operate the same way as JDBC but may be slow when compared to JDBC because we are taking the entire shebang at the Application Layer ( over HTTP and FTP ). Other than that, I don’t see any other reason on why I don’t use FTP, HTTP. It like JDBC suffers from the same disadvantage when it comes to free flow of updates. Get more from Servicenow Admin training

Web services:

Web services come in twos. SOAP and REST.

Both SOAP and REST are very good when you are using them for Freeflow of updates. There are many designs when it comes to SOAP and REST. Let’s look at them one by one.

SOAP:

You are presented with three design options here :

a) Using Direct Web services.

b) Using Scripted Web services.

c) Using an Inbound table. Learn more from Servicenow Developer Training

a) Using Direct Web services:

The advantage the Direct Web services offer you is the speed. When you use a Direct Web service, you directly insert into a table, and you get the response. There are many disadvantages though, some of them are

1.     You cannot customize the output in case of an error.

2.   You are directly exposing your Production tables.

3.   You cannot log the request you received from the external tool.

4.   You cannot talk to more than one table at a time.

I wouldn't take this approach, unless I have no option. For more info Servicenow Training

b) Using Scripted Web services

The advantages of Scripted Web services:

1.     You can talk to more than one table at a time.

2. You can log the request you received.

3. You can customize the output, and this will require some customization on your part.

4. You can hide the Production table, by creating a Staging table in the middle, and once the data is in Staging table, write the logic on the Staging table. Don’t get confused that Staging table here is the Import set table. No, it is not. We will discuss the Import set tables in the next bullet.

The main disadvantage of Scripted Web service is the speed. Apart from that, All is well here. Oh, you can get only one SOAP endpoint per one Scripted Webservice, which sucks. Where as using Direct Webservices/Import set table, you get whole lot of SOAP endpoints to Update/insert/delete/get etc.,

c) Using an Import Set table:

If you ask me this is the best way when you go the SOAP way. Instead of exposing a Production table, you expose an Intermediate Import set table, that takes the input and gives the output. You have all the flexibilty to customize the output, and it’s fast as well because you are using the OOB setup to write Transform Maps to move the code into Production tables. To get more info Servicenow Online Training

REST:

My personal favorite, as REST is one the most flexible specifications that is there out there when it works with JSON.

Given that companies are releasing new applications faster than ever — some releasing updates on demand and multiple times a day -—too many organizations are using manual ETL test processes and the wrong tools to manage critical parts of releases for highly visible, often customer-facing, applications.

That translates into risk to customer loyalty, the brand, confidential data ——nd worse. For more details ETL Testing Training

This article explores how applying DevOps-style test automation to DW/BI and other data integration projects can guarantee a high level of data quality -- instilling the trust that is essential for the success of BI projects and the digital transformation initiatives that are ultimately driving them.

Taking a DevOps Approach to DW/BI Testing

DevOps, with its focus on tool automation across the entire development life cycle, addresses an enormous challenge for big data and DW/BI developers. Many of today's big data and DW/BI projects are already leveraging (or actively planning to adopt) agile and DevOps processes — but not for testing.

DW/BI projects in general are not currently using automated testing tools to the extent that is needed for project successes. Perhaps this is because they believe the required testing functions are not commercially available or are too complex and expensive to develop in-house. Learn from ETL Testing Course

When thinking about what you need to test to ensure data integrity, consider that BI is more than just data warehouses (DW) and extract, transform, and load (ETL).

Services between the ETL processes, as well as the middleware and dashboard visualizations, also come under the purview of BI.

Messages and negotiating pacts between these layers are complex and require considerable coordination and testing.

DevOps helps facilitate this with constant deployments and testing. Implementing a DevOps testing approach to DW/BI means automating the testing of different source and target data sets to keep data current.

This can be tremendously beneficial when handling many (possibly hundreds of) diverse data sources and volumes. Your team will be able to detect errors before they threaten BI applications in production.

Moreover, you will have more time to fix issues before applications reach production. For more ETL Testing Certification

Why Test Automation?

Continuous quality is a systematic approach to process improvement in order to achieve the quality goals of development and the business it supports.

You can use Hibernate to map object-oriented domain models to a traditional relational database. The tutorial below shows how to use the CData JDBC Driver for ServiceNow to generate an ORM of your ServiceNow repository with Hibernate.

Though Eclipse is the IDE of choice for this article, the CData JDBC Driver for ServiceNow works in any product that supports the Java Runtime Environment. In the Knowledge Base you will find tutorials to connect to ServiceNow data from IntelliJ IDEA and NetBeans.

Install Hibernate

Follow the steps below to install the Hibernate plug-in in Eclipse.

1.   In Eclipse, navigate to Help -> Install New Software.

2.   Enter "http://download.jboss.org/jbosstools/neon/stable/updates/" in the Work With box.

3.   Enter "Hibernate" into the filter box.

4.   Select Hibernate Tools. Learn more skills from Servienow Training

Start A New Project

Follow the steps below to add the driver JARs in a new project.

1.   Create a new project. Select Java Project as your project type and click Next. Enter a project name and click Finish.

2.   Right-click the project and click Properties. Click Java Build Path and then open the Libraries tab.

3.   Click Add External JARs to add the cdata.jdbc.servicenow.jar library, located in the lib subfolder of the installation directory.

Add a Hibernate Configuration File

Follow the steps below to configure connection properties to ServiceNow data.

1.   Right-click on the new project and select New -> Hibernate -> Hibernate Configuration File (cfg.xml).

2.   Select src as the parent folder and click Next.

3.   Input the following values:

o    Hibernate version:: 5.2

o    Database dialect: Derby

o    Driver class: cdata.jdbc.servicenow.ServiceNowDriver

o    Connection URL: A JDBC URL, starting with jdbc:servicenow: and followed by a semicolon-separated list of connection properties. More from Servicenow Certification

ServiceNow uses the OAuth 2.0 authentication standard. To authenticate using OAuth, you will need to register an OAuth app with ServiceNow to obtain the OAuthClientId and OAuthClientSecret connection properties. In addition to the OAuth values, you will need to specify the Instance, Username, and Password connection properties.

See the "Getting Started" chapter in the help documentation for a guide on connecting to ServiceNow.

Built-in Connection String Designer

For assistance in constructing the JDBC URL, use the connection string designer built into the ServiceNow JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

java -jar cdata.jdbc.servicenow.jar

Fill in the connection properties and copy the connection string to the clipboard.

Connect Hibernate to ServiceNow Data

Follow the steps below to select the configuration you created in the previous step.

1.   Switch to the Hibernate Configurations perspective: Window -> Open Perspective -> Hibernate.

2.   Right-click on the Hibernate Configurations panel and click Add Configuration.

3.   Set the Hibernate version to 5.2.

4.   Click the Browse button and select the project.

5.   For the Configuration file field, click Setup -> Use Existing and select the location of the hibernate.cfg.xml file (inside src folder in this demo).

6.   In the Classpath tab, if there is nothing under User Entries, click Add External JARS and add the driver jar once more. Click OK once the configuration is done.

7.   Expand the Database node of the newly created Hibernate configurations file.

Take your career into new heights with Servicenow Developer Training 

Reverse Engineer ServiceNow Data

Follow the steps below to generate the reveng.xml configuration file. You will specify the tables you want to access as objects.

1.   Switch back to the Package Explorer.

2.   Right-click your project, select New -> Hibernate -> Hibernate Reverse Engineering File (reveng.xml). Click Next.

3.   Select src as the parent folder and click Next.

4.   In the Console configuration drop-down menu, select the Hibernate configuration file you created above and click Refresh.

5.   Expand the node and choose the tables you want to reverse engineer. Click Finish when you are done.

Configure Hibernate to Run

Follow the steps below to generate plain old Java objects (POJO) for the ServiceNow tables.

1.   From the menu bar, click Run -> Hibernate Code Generation -> Hibernate Code Generation Configurations.

2.   In the Console configuration drop-down menu, select the Hibernate configuration file you created in the previous section. Click Browse by Output directory and select src.

3.   Enable the Reverse Engineer from JDBC Connection checkbox. Click the Setup button, click Use Existing, and select the location of the hibernate.reveng.xml file (inside src folder in this demo).

4.   In the Exporters tab, check Domain code (.java) and Hibernate XML Mappings (hbm.xml).

5.   Click Run.

One or more POJOs are created based on the reverse-engineering setting in the previous step.

Insert Mapping Tags

For each mapping you have generated, you will need to create a mapping tag in hibernate.cfg.xml to point Hibernate to your mapping resource. Open hibernate.cfg.xml and insert the mapping tags as so:

<hibernate-configuration>

<session-factory name="">

<property name="hibernate.connection.driver_class">

cdata.servicenow.ServiceNowDriver

</property>

<property name="hibernate.connection.url">

jdbc:servicenow:OAuthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;Username=MyUsername;Password=MyPassword;Instance=MyInstance;<!--?xml version="1.0" encoding="UTF-8"?-->InitiateOAuth=GETANDREFRESH

</property>

<property name="hibernate.dialect">

org.hibernate.dialect.SQLServerDialect

</property>

<mapping resource="incident.hbm.xml"></mapping>

</session-factory>

</hibernate-configuration>

Execute SQL

Using the entity you created from the last step, you can now search ServiceNow data:

import java.util.*;

import org.hibernate.Session;

import org.hibernate.cfg.Configuration;

Session session =  new

Configuration().configure().buildSessionFactory().openSession();

String SELECT = "FROM incident i WHERE category = :category";

Query q = session.createQuery(SELECT, incident.class);

q.setParameter("category","request");

List<incident> resultList = (List<incident>) q.list();

for(incident s: resultList){

System.out.println(s.getsys_id());

System.out.println(s.getpriority());

To get in-depth knowledge, enroll for a live free demo on Servicenow Online Training

}

}

}

Automate integration tasks using ServiceNow-built components for Flow Designer, or develop custom integrations. Requires a separate subscription.

IntegrationHub features

ServiceNow eBonding spoke

The ServiceNow eBonding spoke demonstrates some common integration design patterns through a common use case of synchronizing incidents across ServiceNow instances.

Base system eBonding actions

If you have multiple production instances in your environment, you might have a need to synchronize data across these instances. For example, one instance might manage internal applications (your source system) and another manages external customer facing applications (your target system).

A common use case is an incident which initially opens on the source system, but it requires a correlated incident to be created and tracked on the target instance. The ServiceNow eBonding integration contains the following OOB actions to assist in creating the synchronization: Get skills from Servicenow Developer Training

Create Remote Incident action

This action uses the source system incident details to create a new incident on the target instance. It passes the source incident number as the Correlation ID on the target system. It takes the target system incident number and updates the Correlation ID of the source system.

Lookup Remote Incident action

This action takes the remote incident number as input and retrieves more details about that incident, such as: short description, description, priority, etc.

Update Remote Incident action

This action uses the source incident details to update the remote incident with details from source instance. Look up of remote incident is performed using Correlation ID in source instance’s incident. Learn more from Servicenow Certification

Credential and Connection information for eBonding

When building actions, you must decouple the connection and credential information from the action so there is a seamless transition from distinct production environments. This also makes it easier to share content and create content through the ServiceNow store.

As part of eBonding example, you can associate an OOB connection alias (snebondingah.ServiceNow). Create an HTTP connection record and associate it with this alias. For credentials, ServiceNow web services supports a multitude of authentication mechanisms. For more details Servicenow Online Training

Create a BasicAuth credential to start with, however, that login ID must have permissions to create, read, and update an incident on your remote system. An example HTTP connection:

Additional eBonding Script

The Payloadbuilder script is included with this example. It builds a payload by reading a set of fields from an incident table used in the REST steps for these actions.

Slack spoke

The Slack spoke provides actions which post messages and ServiceNow incident, problem, and change record details to Slack channels. For more Servicenow Training

Slack actions

The Slack spoke is an available integration through IntegrationHub. You can specify the following Slack actions within a flow.