ODBC - Cannot create Schema

[Piri]

Hi all!

This is my first time posting on here (just saw, apparently the second time!), apologise if it's the wrong forum.

I am trying to get ODBC to work so I can use PowerBI to report on SN data, but am stuck after finishing the initial setup.

When I try to run a simple command which retrieves 100 records of incidents, the system currently returns:

 [SN][ODBC ServiceNow driver][OpenAccess SDK SQL Engine]Cannot create schema.Cannot retrieve a DB schema. Please run <instance>?SCHEMA in your browser and try again. Also make sure that the table descriptor cache can hold all your tables and DB views. You can check the table descriptor stats runing <instance>/xmlstats.do in your browser.

I googled the issue and I found some articles online, I checked the max entries for syscache_tabledescriptor and this is set to the max it could be for a 32bit system (2147483647), I double checked anyway how many tables and view there are, and this is nowhere near this number. Just to make sure I created the glide.cache.size.syscache_tabledescriptor entry and set it to 12000 just to test, but noticed that the max entries didn't even change.

Any suggestions what else I could try?

Thanks!

P.S. My knowledge around SN is quite limited, so I'd appreciate it if you could explain it in a simple way. 🙂

[johnfeist]

Hi Piri,

A lot of these messages can be very cryptic.  As a first step, make sure that the user account that is accessing your instance has the correct roles assigned.  This includes the odbc role.  I have several background processes that run between my SQL Server databases and our ServiceNow instance.  The user account for that also needed to be an itil user for things to work properly.

You cannot dot walk in your queries so you will either need to have a good understanding of the ServiceNow data architecture or have your admins build you views that you can query.

I would also caution against too much use of PowerBI against a production instance.  We opted not to allow it at all because once we allow a user to query via PowerBI we cannot tell others no.  We were very concerned about negative impacts on system performance (best practice is not to report against production systems).  Instead people are scheduling report delivery from the instance or accessing the data that I extract nightly.

Hope that helps.

:{)

Helpful and Correct tags are appreciated and help others to find information faster

[Piri]

Hi John,

Thank you for your quick reply!

I created a ODBC user and granted it the control via ACL as per this article.

I just checked as well and the user has been added to these groups, which seems to be right:

I have been looking into reporting for some time now, and couldn't find a way to schedule the reports I need directly from ServiceNow, but what you are saying would make sense, the only issue for myself is that I need a lot of historic data (3 years), and exporting that into a dataset on a server might be a big task.

How do you currently export the data, do you also use ODBC to pull the data out and import it into a different SQL DB?

Thanks,

Piri

[pavelmuller]

Hi Piri, 

IMHO, connecting live to your ServiceNow instance using ODBC is not the best idea from the architecture point of view. Imagine that it was not ServiceNow but an Internet Banking system. Would you run reporting against a production IB?

I think the best practice is to build a copy of the data in some kind of a reporting database and to connect your PowerBI there without affecting your production environment.

You can use SnowMirror for example to build such a reporting database.

http://www.snow-mirror.com/

Kind Regards,
Pavel