Focus of this project was to identify and extract data from diverse sources within DATATRAK, with a view to analyzing and making it available for billing and for improved decision making. The other important requirement was to ensure visibility and tractability of extracted data to ensure compliance with Section 404 of US federal law.
Data to be extracted was finalized jointly with the client. This data was available across 22 different sources and there were multiple data formats. Data interrelations in the source data were studied and understood. A database schema to store the extracted data was finalized. Thereafter, in consultation with the client team outputs to be generated from the extracted data were finalized.
Two specialized products, namely FDS Enterprise Application Integration Tool (FDS EAI) and FDS Business Intelligence Tool (FDS BIT), were used to execute this project.
FDS EAI was used for extraction and transfer of data. FDS EAI has full ETL capabilities and can be deployed to extract and transfer data from distributed and disparate data sources. It ensures that only valid data as per defined business rules is transferred. It also has features to map source and destination data dictionaries.
FDS BIT is the intelligent Report creation and rendering tool, which is capable of adapting to any third party application database with the help of its data dictionary. This tool provides great flexibility to the end user for analyzing and presenting the underlying data. FDS BIT made it possible to generate custom built reports that required cross table manipulation and multiple dimensions. Reports included consumptions, activity status and financial data represented in detail and summary formats. User could view outputs in a variety of formats like text, HTML or Excel.
Project was carried out on Microsoft platform entirely. Windows 2000 Server class machines were used for hosting Application Integration Tool and Business Intelligence Tool. ODBC drivers were used to establish connectivity to various data sources such as Oracle 9i, Sybase, MS Excel and SQL Server 2000.
FDS Application uses SQL Server 2000 as back end database. Web UI was built using ASP .Net. A separate Business logic layer was introduced. FDS EAI and FDS BIT used this layer for accessing data.
Appropriate use of store procedures and XMLs helped enhance the performance of the application.
MS Visio was used to record the data interrelationships.Entire project was carried out in 25 man months over a period of 3-4 months