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).

[User212122]

Hi Casey,

Firstly reporting outside ServiceNow, is something which is not recommended since you cannot replicate the data model.

A work around will be to setup connections with the tables you need to export data from and then export it in a sequence.

The sequence of import should start with data which has zero dependency with other tables. for eg: Location is used in user table so first import the locations and then the user data so the location field is not empty.

Again to emphasize on the point of the data model, you need to import it in a sequence to replicate the data model in your new environment.

You need to plan the sequence before you import.

I hope this approach helps you!

Regards,

Sunil Arya

[Casey12]

I have yet to encounter a reporting scenario in which I can't replicate the data source's data model.  ServiceNow will be my first application in which I supposedly can't.

I found the following potentially useful link that contains sub-schemas of the ServiceNow data model: NashCo

If I were to take the approach of a sequenced export, how can I identify which tables have no upstream dependencies?

[Adam Stout]

We always recommend reporting on the data in the platform with the tools in the platform to get the most value.  This ensures you have real-time data that honors all security rules.  When you export the data, you lose the real-time visibility as well as add in the massive challenge the replicating ServiceNow's very configurable security rules (scripted ACLs can be challenging to replicate in SQL).

I have a couple of questions:

Why can't users dot walk to the tables they need?

Why aren't you looking at finding an admin partner that better supports your needs? 

Have you looked at leveraging Performance Analytics and the content packs that come with it?  

If you are trying to export the data there are a few 3rd party vendors that help with this.  You may want to look at using one of them to ensure that you handle all the of challenges with exporting ServiceNow data.  Manually building a process to export data from ServiceNow to keep a data warehouse in sync with the source system would be rather daunting without this.

[Casey12]

For some reason (I wasn't involved with our deployment of ServiceNow), we brought in a consulting company to implement some custom tables.  We are finding that we can't dot-walk from / to a lot of these custom tables.  I don't understand what they did or why we can't dot-walk these tables.  I was told that it can be resolved by submitting a database view creation request to our admin partner, but I'm not going to even bother with that, given their long delivery times.

Deciding to go with a different admin partner is beyond my purview.  To be sure, I have voiced my concerns to management.

Yes, we are looking at Performance Analytics (PA), since we do have a need for historical reporting.  If we don't go with PA, then we do need to build some data store and a snapshoting mechanism, and then put a reporting tool (we already have a few) on top of this historical data store.  We do not have needs for real-time reporting, and our security controls in ServiceNow are corrupted to the point that we pretty much give everybody read (not necessarily write) access to everything.  (For example, I have found that all of my reports can be edited by users I share them with.  An edit does not create a new report for the user.  I have been told that this shouldn't be, and that it's due to mis-management of our roles.  I can't pinpoint which roles are allowing this to happen.)  We also have a need for fairly involved transformations that I don't think are possible within ServiceNow (e.g., "work" tables, such as those created by pivoting) and / or would require involvement by our admin partner (e.g., database views, reporting fields, business rules), which, again, I don't want to bother considering as an option.  So, I think the best option for us is to go with a historical data store.

Having developed historical data stores for many other source systems in the past, I have yet to run into a case that would require hiring a vendor to perform the work.  To be sure, these sources sometimes had complex schemas, but I was always able to get what I needed.  What makes the ServiceNow database different from other data sources in this regard?