A few Random Tips for ServiceNow Reporting

I have been developing reports directly from ServiceNow ever since the ODBC connection was made available (http://wiki.service-now.com/index.php?title=ODBC_Driver).

This new functionality allows ServiceNow to be analysed directly with greater freedom and accuracy than through the built-in reporting (mentioned in more detail elsewhere in this blog).

ServiceNow have done a fantastic job with the structure and naming conventions within the table schema and it is a delight compared to a lot of systems I have reported on.

These few tips may be of interest to anyone embarking on this route.

Field Naming conventions

The fields in the ServiceNow table structure are quite consistently named to the point where general rules can be applied:

If a field name begins with “DV_”, the DV stands for “Display Value”, which is the same thing as is shown in the ServiceNow application and what the audience of your reports will expect to see as labels for groups, charts and summary names.

There is nearly always a corresponding field without the DV_ prefix which holds the same data in a less accessible way.  For reporting, it is the DV_ fields which should be used to label everything but any calculations (grouping, summaries, filtering etc) the corresponding data fields should be used.

Top tip: Ordering the database fields alphabetically within your reporting software will group all the DVs together and makes it easier to find the field you want.

Please note that both date and system fields tend not to have a DV equivalent.

Comparing DB field names to ServiceNow

The actual field names used in the database are different to those used in the ServiceNow interface which is different again to the built-in reporting field names.

This can make identifying the required fields difficult.

However, if a built-in report is exported from ServiceNow in a CSV format, the column headings match the database field names, while the names in the report template it game from match ServiceNow.

When viewed side by side, it is a simple enough task to identify which is which and can save a lot of guess work and frustration.

Correct Linking

Tables have to be linked together in order to create a report, like Incident to Service to find out what Services have been impacted.  Having this freedom to link through tables and retrieve data in any order is the true power of ODBC reporting.

The quick and easy route is to link tables based on the DV name fields, it is obvious what is being linked and you can actually look in the fields manually and compare values.

There are two problems with this:

1.  Content

There is no guarantee the content of a DV field is unique, so any filtering or grouping on these fields can lead to errors.

2.  Speed

DV fields do not have an Index (a database thing which makes any sort or search on the field massively quicker) and can make reporting painfully slow if they are used for anything other than display purposes.

Both of the above problems can be resolved by using the sys_ID  field .  The sys_ID field is unique for a particular row of data, not just in a single table, but across the whole database.

And of course, the sys_ID is also Indexed, so any report based on this field will run as quickly as is possible (bad structure and excessive data still kills performance).

It’s De-Normalised for a reason!

Ok, time to get a bit (more) nerdy!  Databases are usually designed so each piece of data is unique and identifiable from an ID.  This is called Normalisation and is the foundation of both database design and professional report development.

What the ServiceNow table structure (and a lot of other databases) does is repeat the same information in other tables to save you the effort of linking.

A good example of this is the Incident table.  The Incident table contains fields to link to other tables to retrieve the affected business unit or the root cause.  But these also exist as DV fields that can be pulled straight from the Incident table making reporting quicker to develop and run.

And Many More…

Of course this is just a handful of hints is the tip of the iceberg of the knowledge needed to get the most from ServiceNow reporting.

For more cool information about ServiceNow Reporting, please look here!

Advertisements

About jasondove

I am a consulting BA, with a heavy leaning towards process mapping and Business Intelligence.
This entry was posted in ServiceNow and tagged , , . Bookmark the permalink.

5 Responses to A few Random Tips for ServiceNow Reporting

  1. Mark says:

    Hi Jason,

    Good articel and great to know about the DV_ fields, it makes writing the queries easier and reduces the number of joins required.

    However I’ve had some issues using some DV fields with the LIKE operator – no data is returned. For example, if I want to see all incidents that have the letter d in their category, I assumed this should work:

    SELECT * FROM OPENQUERY(SERVICENOW, ‘
    SELECT
    INCIDENT.number,
    INCIDENT.dv_category,
    INCIDENT.category
    FROM oauser.incident INCIDENT
    WHERE ((INCIDENT.dv_category LIKE ”%d%”))
    ORDER BY 1
    ‘)

    However it returns no results. If I change the where clause to WHERE ((INCIDENT.category LIKE ”%d%”)) I get a number of results. This happens for a number of fields (Priority, State, Impact for example). The same problem occurs using the ODBC driver directly (i.e. outside of SSMS).

    Have you experienced this issue at all?

    Thanks
    Mark

    • jasondove says:

      Hi Mark

      Yeah, the problem with the wildcards was one of the things that lead me to the DV fields in the first place, that and the clients not recognising the non-DV content when presented in reports.

      Jase

  2. DSR says:

    Whatever they gained performance, which is nominal if any, by having he database De-Normalised, they completely lost in reportability.

  3. DSR says:

    Whatever they gained in performance, which is nominal if any, by having the database De-Normalised, they completely lost in reportability.

  4. Pingback: The Secret New Weapon for ServiceNow Reporting | Jason Dove's Business Intelligence Rants

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s