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