This is really all about taking public data from a bank and credit union websites and pushing it into a SQL database so we can query it from a single website. On scale, there are thousands of Banks and Credit Unions (15K), so we cannot manually create an ETL package for each; we don't have the resources for that. So, we need to create a way to extract data from Mozenda (www.mozenda.com is the tool we use to scrape data from websites), clean the data up (see details below), map it to an entity in SQL database (see details below), and then load the data into MS SQL 2008 database (hosted). Please let me know it this makes sense, and if not we can walk though with examples.

Here are some details...


1.) Administrator (non-technical person) will use Mozenda to extract data from bank and credit union websites. This data will be stored at Mozenda and categorized by a unique id for each bank/credit union ("Entity").

2.) The Mozenda data will be made available for extraction from Mozenda via an API. This data will be in for of XML.

3.) From the API, we need a way to import into MS SQL 2008 database.

4.) Once we have a way to import into SQL, we need to "clean up" the data so we can query it from website. For example, one bank may call mortgage "Mortg" while another calls it "*Mortgages for this" and yet another "Here are my mortgages", so forth and so on for thousands of banks/cu's. We need to take all of these "bank specific terms" and transform them into a single (general) term that can be queried from website. Make sense?

5.) Since there may be thousands of bank specific terms, and only one general term, we need to create a web page for Administrator to map bank specific terms to our general term. I hope you can begin to see how that can scale.

6.) Now that bank specific terms are mapped to our general term, we need a way to map the Mozenda data to its corresponding Entity in SQL database. When thinking of how this will be done, please consider that we will add new entities to Mozenda everyday and we would prefer not to "re-code" the mapping method for each one. Does that make sens? Please let me know and we can discuss in further detail.

7.) Now that we have the data cleaned and mapped from Mozenda to corresponding Entity in SQL database, we need to create a automatic (scheduled) method to pull from Mozenda and feed into SQL database. We may start with a manual (push to start) process, but with time we would like to have it clean data, automatically map, and push into SQL.

When you're ready, I will provide Mozenda account into for testing. Please let me know.

Many Thanks!

JB