After recently being tasked to
provide a solution that will import crash reports entered through LexisNexis's
software into OSSI's mobile database I made contact with the LE manager with
LexisNexis to get technical information. I was soon advised that there were
numerous agencies that were also requesting such integration and began
receiving calls and emails as to my progress and approach. As a means of
centralizing my work and progress, and reducing the emails and calls to each
agency, I will be posting all my work and progress on my blog.
For anyone who is just now coming to
the party, and no invitation is required, this endeavor is for any agency
currently using, or planning to migrate to, LexisNexis for their crash
reporting and using Sungard OSSI for their records management system. There are a number of reasons why an agency
might choose to use LexisNexis as their primary crash reporting tool, first and
foremost being licensing costs, over OSSI’s mobile field reporting. The short version is:
1.
The software is free
2.
Crash reports will be forwarded to
the state(FL)
3.
Crash reports are visible and
printable in the newly required FL State format from Lexis Nexis
4.
Other crash information will be made
available to agencies using LexisNexis crash reporting tools
A reason why not to use LexisNexis for crash reporting in an
agency that utilizes OSSI RMS is that this information is not immediately, or
easily, integrated into the agencies RMS database, hence the reason for my
existence.
Enough of the why… I will now dive into the How. Each crash report from LexisNexis’s software is
stored as XML in a single file. Each
crash report entered through OSSI is stored in numerous tables within SQL
Server, edition is dependent upon agency.
The disparity of a one to one relationship, as well as a hierarchical
structure compared to relational model, is where the obstacles begin (DON’T
leave yet cuz of the Geek Speak. You
will miss the plot twist and how it ends).
After speaking with Lexis Nexis I
was immediately forwarded a schema of their crash report as well as several
sample report. I am currently working on
reviewing the schema and I am in the preliminary stages of data mapping. My
method of attack will be:
1.
Complete the data mapping
a.
I will provide a Visio diagram, and
.jpg images for those without Visio, once complete
2.
Create a SQL Server Integration Services
package to do the ETL, extract transform, and load, from XML to the OSSIMOB
database
a.
The package will monitor and save
i. Execution time
ii. Number of reports imported
iii. Any and all errors
iv. Process times
3.
Create a SQL Server agent job to
schedule the package to do bulk imports to OSSIMOB
4.
Create reports using SSRS to display
export/import information
So to cover the first question I
have been asked…
Question: “How much will this
cost our department?”
Answer: It cost nothing. I
am preparing this for my agency so I have been compensated so this is free,
buyer beware!
Question: “What if our network
is different than yours?”
Answer: I have received enough requests to realize that this is
something desperately needed by many agencies.
I will make this as portable and easy to implement as possible
regardless of network and data disparities.
Question: “What if we don’t
have a technical resource to assist us in deploying this?”
Answer: Anyone in public safety who has worked with me is aware
that I am free and available, for public safety. I will do whatever I can to assist. I obviously cannot insure 24/7 support or
promise to make myself immediately available at all times, but I will do
whatever I can to help.
Question: “As public safety is
a critical process we NEVER touch live production data, but utilize a reporting
solution for all ancillary processes.
Will this still work?”
Answer: You are preaching to the choir!!! Nothing touches production, BUT
PRODUCTION!! I fully understand and this
is the rule within our environment. The
final solution will not be
transactionally based, up to the minute, but rather batch processed and any
reporting solution, data warehouse, etc. will work.
I will post all progress and updates
at least weekly and provide all code here.
If there are any other questions than PLEASE feel free to post and let
me know and I will answer you ASAP.
If you are looking for resources that may help
you with understanding SQL Server Integration Services there is an awesome book
available, see below. I hear one of the
authors is INCREDIBLY intelligent, handsome, insightful, and one of the best
human beings you will ever meet. J
No comments:
Post a Comment