09-20-2011 12:14 AM
Has anyone out there had any experiences extracting data from Guidewire Policy center for the use in a Data Warehouse.
I know very little about guidewire but understand it is not as easy as pulling data from the underlying table structure as guidewire is rules and event based
Any hints appreciated
09-21-2011 04:51 PM
Sorry, not any experience. But the following quote from their web site gives a hint:
"Integration—provides a variety of integration mechanisms, including a web services API, event-based messaging, and the ability to add integration adapters at any point in the application; data can be exchanged in any format, including ACORD XML or IAA"
So if this is the access path that is available for your project, there is a concern about high data volumes and XML/service brokers.
I guess you have to do the usual steps for investigating any data source; meet up with the supplier, invstigate interfaces, and match with your user requirements.
10-21-2011 02:19 AM
While we haven't had the experience extracting the policy center, we however have extracted the tables from Claim Center of the Guidewire. Guidewire seems to like to use id/numeric value in their transaction tables, meaning you will often to look up the real value from another table to get the actual value of the id. Because of this, it could easily create up to thousand of tables in a system. Also, the underlying tables tend to be built based on its function rather than role, and this also make them to share the tables for different products easily, provided the function is the same.
The data dictionary should be helpful to understand the relationship of the tables and columns. Have you had any luck extracting the tables so far?
10-24-2011 04:12 PM
Still in the inception stage of the project, so no data extracts yet....
Sounds like we are not going to the conventional ETL from the underlying tables and will an event messaging interface to get the deltas.... We'll see how it goes and will post our experiences....
12-08-2011 08:32 PM
Event messaging seems to be a preferred and standard way of getting the data these days, at least that's what I know how few different organisation are doing it. This is also the case for the the Policy Center too..
11-03-2012 07:57 AM
Could I ask about your experience with feeding data warehouse with data from Guidewire system? Did you chose their event-based messaging system as a way of data extraction? If yes, is it working without problems? Have you worked out alternative ways of re-extracting data from Policy Center Database in case of failure in sending some messages or such cases did not occur?
I'm facing similar problem of chosing correct approach (basically push vs. pull) and I will be grateful for any hints concerning Guidewire system.
11-11-2012 06:16 PM
I am in the situation as described by you a year ago. My company introduces the Guidewire system and one part of the project is loading the data warehouse. Guidewire strongly recommends their messaging system instead of ETL approach to feed the staging area. As this is not typical way of doing this we weigh up the pros and cons. This is why I asked about your experience with Guidewire system (the questions are in my first post).
I look forward to hearing from you
11-03-2012 10:42 AM
You mention feeding a data warehouse, which to my view implies large data volumes.
My experience with using messaging interfaces to try to pull large data volumes is that they are not designed for this, and are VERY slow. I suggest ensuring that you can meet the required volumes with your approach.
Would another alternative be to meet your analysis needs from whatever analysis resources Guidewire supplies? Let them do the heavy lifting?
11-03-2012 01:08 PM
Thanks for replay.
Messaging interfaces in this approach are for sending messages to staging database in real-time (per transaction), so large volumes of data are spread throughout the day. The data warehouse is loaded from the staging area on daily basis with ETL tools (DI).
Has someone met similar solution for feeding data warehouse?
11-04-2012 08:13 AM
If I understand this right then you will source your data from some tables in a staging area. How the data gets in there is from your point of view an upstream process and you don't have to worry about.
I've dealt already with "trickle feed". The main question here is: How do you make sure that the data you source from is in sync? Eg. if there are several tables with relationships how do you make sure that you don't pick up a record from one table with a related record in another table which hasn't been loaded yet?
It's a question you need to ask the guys loading your staging area. How are they controlling data integrity? There might be a load timestamp column and you might decide to only extract records loaded a certain time ago to make sure your extracts link up. You might then also maintain a control table where you protocoll what you've already extracted and what not. You might also need to validate your extracts before loading and only load records which link up.
Easiest would be if you would be given extracts as then the people loading the staging area are also responsible to provide you with correct data. After this it's standard ETL/ELT for delta load. If you have to do the extracts (the landing files): Make sure to also understand how you can identify deletes.