ODBC Driver reliablity and performance issues - can data be bulk downloaded without ODBC?

[simonlawrence]

Hi everyone - Really could do with some advice, so here's hoping someone out there has encountered a similar issue to ourselves.

I run reports that use the INCIDENT table, and there are about 170000 records within it.   We run reports that analyse this data in many different ways. Originally folks here used to use the GUI and download/export to MSEXCEL - but this only downloads a maximum of 20000 records (and can take a while getting the data too). If different people were doing different reports, they'd all be doing the same thing - separately downloading and exporting to MSEXCEL.

Trouble is - 20000 records isn't enough for our analysis - especially for year on year comparisons, or for finding out answers to "when did 'x' start happening?" and the like. We did look at extending this, but we'd much rather have the data in a "proper" DB, where the contents of the INCIDENT table can be downloaded and stored automatically ona daily basis, requiring no user interaction at all.

Obviously, its efficient to have the data in a proper database anyway - its faster, and the results can be kept centrally and referred to by everyone who needs the data.     We're using the ODBC SERVICENOW driver to achieve this, and I have to say, our experience of it isn't very good at all (how do you guys find it, out of interest?)

Ok - that's the background - heres the issue:

I want all the fields (well, nearly all) the INCIDENT table.   Id like to do:

SELECT * INTO localtable from OPENQUERY (SNODBC,'SELECT * FROM INCIDENT')

..but I cant because some of the data types don't seem to like being imported into MSSQLSERVER owing to "precision" issues, and such. (it just means I have to do a query that specifies the fields by name so I can CAST them to datatypes that work).  

Ok so far, manually specifying the fields and CASTing the datatypes works well..     here's where it gets sticky:

I cant specify all the fields I want in the same query - it just times out.   So, my solution was to have 3 queries each pulling about 20 fields, join the results together on my MSSQLSERVER and so creating the day's INCIDENT table snapshot.

That does at least get me data, but not reliably.   Usually it fails - either one or all three queries fail - and the error message seems to be different each time.   it complains about the disk cache being too small (no it isn't, I've set it to 1GB in the ODBC admin tool), or it will just time out, or today's special "Unable to retrieve error message from OpenAccess SDK IP Layer".

The crazy thing is, I might as well specify all the fields I want (across multiple queries) because if I just ask for 5 fields, it seems to take the same amount of time as asking for 20 fields.   Each query can run for about 20-30 min before either actually delivering the data or just generating errors (in which case its try it again and again and again until eventually it works).

So, based on my experience so far - I'm finding it very hard to see how the ODBC driver is reliable enough to just do its job without constant intervention from technical resources that shouldn't have to be looking in on this -   is there any other way we can get our data?   Does SERVICENOW provide a daily data-dump-and-download-over-ftp service or similar?   I don't really mind HOW the data arrives here - but we need this daily - and the ODBC method just doesn't seem up to the job, and ive got a lot of fed up folks here who are depending on being able to use our data.

I've tried disabling the timeout on SERVICENOW ODBC, but this just means the queries don't work at all - they immediately fail.

Bandwidth to internet isn't a problem - we are on a 250Mbps connection .. guys I'm really running out of ideas,     if anyone has any suggestions,   I'm open to them.

Thanks!
-Simon

[pejayuk]

I had this same problem and my approach was to delta load the data every 15 minutes into a Qlikview QVD file.

I could of output the data into an SQL server table, but I wanted to use the data in Qlikview dashbaords, so had no reason to do this, but nothing should stop you being able to do it.

Most tables in ServiceNow have a UID. mostly sys_id. Not incident.number as sometimes they duplicate (once or twice a year we found), but sys_id seems to be the best to use across most tables.

Most tables have sys_updated_on as well and this is updated when ever the record is altered, so a good field to use for a delta load.

Simply lookup your minimum and maximum dates in your destination table to find your current data range in your SQL data warehouse, then delta load a limited load from the ServiceNow ODBC driver the minimum and maximum dates to exclude your already synced records.

We run ours for 10000 records every 15 mins. This allows for synchronising forward and backward in the same SQL statement from ServiceNow. Works well and stable. Takes a minute to run ever 15 mins. We pull about 20 fields from the incident table.

Moving forward, I have been working on 4 SSIS packages for data collection, delta load and resyncing of fields. I have found however that in SSIS the ODBC driver always times out around 35 seconds and have had to limit the records returned to a 2000 max. I have not yet found a way around this, but can live with it by running the package every 5 minutes.

Hope this helps someone. I had huge problems with this driver timeouts until I delta loaded the tables. Worked like a dream since. Had about 1 issue every 6 months to a year where I just dump the table and delta load again from scratch as its simple. Have been delta loading tables from service now using qlikview for about 4 years.

EDIT: Found that my timeout issue in SSIS was due to using the Data flow, Script component ScriptingLanguage C#. I found that C# has its own command timeout setting that also needed to be set.
OdbcCommand.CommandTimeout
By default it is set to 30. Changed this and all now working fine in SSIS with 10000 records.

[Greg75]

Just one other item I wanted to point out would be to ensure that you are using the correct ODBC driver version.

ODBC Troubleshooting Checklist states that we should be using the 32 bit version if you are trying to configure Microsoft SQL Server Integration Services.

See step seven instruction below:

7. Are you trying to configure Microsoft SQL Server Integration Services (SSIS) to work with ODBC?
Make sure that you are using the 32-bit version of the ODBC driver regardless of the OS bitness.

[pejayuk]

I ended up using Qlikview to sync data into SQL Server from all our service now instances (5 difference instances for over 10 countries around the world) into one big SQL data store holding world wide data to report for all our firms around the world, constantly updated every 15 minutes.

As our reporting teams had most skills in Qlikview, I was tasked to create the SQL solution into Qlikview for maintainability by the whole team, even though the SSIS Script component ScriptingLanguage C# solution was more elegant and quicker, qlikview was easy for everyone to understand and maintain.

The Qlikview server has several data collectors running on different instances at the same time and run like clockwork. Over time I added lots of auto recovery and bi directional synchronization from the current date and time in both directions with newly created or updated data being priority and historical data as secondary.

This is a complex, elegant and reliable system with built in auto recovery, so when something goes wrong, like a network or internet outage, the system just automatically recovers and catches up when the network or internet outage is resolved. 

I enjoyed this project and planned to blog about it on the Qlikview website in detail, but as they don't support blogging, I didn't. Maybe this might be a place to blog about this when I have time, I have some elegant solutions for some seemingly complex problems that I feel was a shame to not share.

If you need help with implementing some like the above, get in touch, I may be able to fit extra work in.

[pejayuk]

I ended up creating a data collector in Qlikview to collect and synchronize ServiceNow data using the ODBC driver in small amounts. The reason for using Qlikview over SSIS was maintainability by the team, as everyone knows Qlikview as its the main dashboarding tool we use.

I designed the qlikview data collector to be copied/duplicated and run multiple times on the same instance of service now and multiple instances of service now, as required. Simply copy the data collector and configure the new copy for the instance, table and fields required, then schedule on the Qlikview server. A very simple to understand and maintain system.

I built in auto recovery for low maintenance, so when there are network or internet outage, no interaction is required by anyone when the outage is resolved and the ODBC connection restored.

I added bi-directional synchronization. When new tables or fields are added, starting from the first run date/time with first priority on new/updated records over historical data, the latest data is available within minutes and historical data is available shortly after for quick start development and quick start full recovery if ever required.

I've setup several of these data collectors collecting updated data on a 15 minute cycle from multiple tables across multi instances of service now, into a local SQL server database. A Qlikview metric and KPI calculator then runs to create relevant stats per record relevant to incidents, analysts, service alerts etc. This allows any reporting tool from webpages, excel, qlikview, crystal reprots or any other system that can access SQL, to access the metrics and KPI information and display the same consistent results. If a KPI is changed, it is updated on the SQL server data and this replicates to all reporting dashboards the next time they reloaded.

Over 10 member firms across 5 instances of service now, having their data collected every 15 mins and stats calculated so the data they used to view tomorrow about what happened today, can be seen as it happens today, allowing actions to be taken proactively to stop a problem or outages as soon as calls start being made to the service desk, lowering calls over the day and decreasing wait times as a result.

Other systems data is also collected and integrated into the system to help with monitoring the impact of software deployments and new software releases. 

I was going to blog about all this in detail, but the Qlikview website doesn't allow blogs, so I didn't bother. There are a lot of elegant solutions to seemingly complex problems that I would like to share, maybe I could do this on a more generic basic version here, as a lot of the solutions are about approaching the problems from different angles and pushing solutions to other systems like servicenow or sql server, not qlikview. So this should be very portable as a result.

Do let me know if your looking for someone to help with a project like this, I really enjoyed the years I spent developing the solutions and its evolution into this flexible robust data collector and its qlikview reporting dashboards.