ServiceNow data model

[Casey12]

My company is having lots of challenges in doing any sort of operational or analytical reporting in-platform within ServiceNow.  One challenge is that a true semantic layer (i.e., not tables) is not exposed to a user for self-service.  Yes, user-friendly field names are exposed (as opposed to technical column names), but the user often has no idea what tables to search in or what each of the fields is supposed to hold.  And, even when a user does know what tables he / she needs to pull from, he / she is usually unable to bring them all into 1 report due to the inability to dot-walk the desired tables.  Another challenge is that we have lots of data transformations to go through for a typical report, which are either not possible in the NOW platform (e.g., pivoting) or require fields to be added to tables / scripts / business rules, which we are not allowed to create.  (We are not given the roles / access required to create these things.  We delegate these tasks to a vendor, and their turnaround times are very long.)

So, we would like to export all ServiceNow data to an external data storage via the ServiceNow ODBC driver and report from there (either raw or re-modeled).  How can we retain the data model in the export?  That is, I can identify all tables to export, but I cannot replicate the NOW data model itself without a lot of guesswork.  Is there a published data model that I can use?  Even if I export only some of the tables, I still need to know how to join the tables, so that I can either establish the same joins in my storage device or re-model the data into something more useful (e.g., dimension).

[Adam Stout]

When you use the APIs (including the ODBC driver) you get the same tables you see in the UI.    There is nothing special.  I'm not sure why would have any problems extracting data in a useable format (keeping it synced is what is challenging and can cause performance issues if not done wisely).  If you call incident, you get the full table with all fields including references and display values.

Can you give an example of where you are having an issue?

As for not being able to dot walk, it sounds like the implementor may not have used references when they should have, they just used a field that looked like a foreign key (but isn't) or used document id fields.  If it can be a reference, it should be, if it is a document id field, then it needs a view.  This should take 10 minutes to set up so if you can't get this done in a reasonable time, I'm concerned about how you move forward with anything.

[Casey12]

The issue isn't extracting data in a reliable format.  The issue is exposing the underlying schema (table joins), so that we can either maintain the same schema or re-model it into something more suited for reporting (e.g., dimensional).  I haven't found reliable documentation on the schema or how to expose it.  (I know I can generate schema maps, but it doesn't resolve my issue.)  With most sources, I am provided a schema map.  But, I can't find / generate one for ServiceNow.

Also, we understand that the actual development time for database views / references is pretty quick.  The problem is that our admin partner is backlogged in work.  They have a 3+ month backlog, and they don't allow for re-prioritization, even for quick items (like view creation).  In fact, they don't want any views to be created at all, as they claim that they aren't necessarily supported in environment upgrades, which I don't know if I believe.  They use the same justification for not following reporting best practices (e.g., create reporting fields in operational tables, create business rules / functions to populate these fields).

[Adam Stout · Accepted solution]

ServiceNow is based on a relational database, but that is never exposed.  It is always exposed as an Object-Oriented model.  The concept of a table extension which is key to ServiceNow makes the underlying database very difficult to read and subject to change (which is why there is no direct SQL access to it).

Your best bet is to replicate the Object-Oriented tables as a relational tables and ignore that they are extensions of base tables.  If you look in the dictionary you can see what certain field reference.  If you want to normalize the model, just build the foreign key relationships on that.

[psnow123]

@Adam Stout Can you provide a link to where the OO model is?  I see where you are coming from.