Just like to take a minute or two to throw out a post on how good it feels to finish up a fairly large project. Although the project was nothing earth shattering, it only took about three months, it was a good deal longer than most of the tickets / applications / requests / etc that I generally work on.
Tonight we are installing the first phase of a new application which will take data from three policy systems, conform them and shape them into the shape that a claims tracking system uses and finally perform business logic on the data to put it into the correct transactional state within the claim system. Data Integration at its best. This was something a little out of the ordinary for me only in that I usually take similar data from the source systems, but it almost always gets stuffed into denormalized tables, a data warehouse or some sort of export file.
One of the more interesting points of the project was laying out the second version of the META database and framework (as discussed previously) as well as the work to provide the correct business logic and data flow in the different control packages.
We ended up with a design that would take a control project for each source system; these would run the packages to dump the data down in to an ODS and then do most of the conforming work to get them in to a single view of the policy / claim / adjuster / etc information. We placed some processes which I would not have normally thought of SSIS to use to check the availability of the source systems and continue to poll every x minutes until they were available and the correct days data or until we ran out of time for our processing window.
The main claims handling controller would be waiting for any combination of these source systems to finish before making the final conformation into a single database for all three source systems. It would then take this data and push it out to the shape of the claims system. It would then push this to a landing area on the claims server and finally utilize the business logic on that database through stored procedures (so that eventually the database group will be able to hand this logic off to the claims application group).
One of the points of interest which I put a good amount of effort was the conformance into a single database between the source systems such that the data is in a position where it can be utilized by other systems looking for new claims data that might not be in the same shape as that of the claims system. I’m not sure if this will be a fair amount of wasted effort or if it will be utilized somewhere down the road. Of course, if we ever get to a Service Oriented Architecture with good Master Data Management this will be completely unnecessary. But, I have a feeling that is a good ways down the road, if ever.
In any case, we’ll see how it goes tonight with the first run. Hopefully my colleagues will be able to handle the other two phases and bring the second and third source systems in to the fold without to much trouble. As for me, I’m back to my normal haunts of ETL into the warehouse. Hopefully I’ll have some more time to post in the coming months.