Designing and Developing Data Warehousing Solution

The development of the data warehouse is extremely critical for effective management of all aspects of the claim automation, including cost allocation and revenue claiming.

However, developing such portal required merging the data from various WDACS and County- Wide systems (eCAPS, Cost Allocation, Time Collection, Inventory, and Item Control) under a single application architecture and relational database. Coordination with County resources to initiate development and integration activities forms a significant portion of this engagement.

WATI reviewed and documented the administrative systems to create data flow diagrams for Time-sheets, Cost Allocation, Inventory, Item control, and worked with County Management and Users to assist in the transition to eCAPS modules for HR (item control), inventory, cost allocation, and time management. The activities included analyzing internal systems at WDACS and mapping data elements and procedures to the new systems. WATI upgraded automated interfaces to County-Wide financial and HR systems such as eCAPS, CWPAY and CWTAPPS. The activities included analyzing the data requirements at WDACS, identifying the requisite source elements, and coding transformations as needed (VBScript, T-SQL, PL/SQL). WATI worked with County Department staff to enable the transition of an expense claiming solution specific to the requirements of WDACS using data from eCAPS G/L and the HRA (item control) module. WATI designed system specifications for contract analysis, revenue, expenditure and staffing analysis.

WATI defined Logical Data Models (LDM) to depict the logical entity types and the relationships between the entities across all of the key subject areas. Based on the LDM, WATI developed a physical data model (PDM) to define the internal schema of a database, including tables, columns and proposed relationships between the tables. WATI extended the OLAP database to support required data (aggregated and transcobactional) along with derived relationship tables using County standards. WATI extended and bridged existing systems to allow for distributed maintenance and data relationships as well as added operational functionality.

WATI developed and automated extraction, transform and load processes for data residing in the source systems. The team built all database objects necessary to support the Cognos Analytics and reporting environment including tables, views, materialized views, procedures, DB links, triggers, sequences.

The team planned and designed all pre-staging, staging and aggregation tables. The team built star schemas for various reporting requirements and data marts for various subject areas within WDACS utilizing Oracle SQL Developer, TOAD and Cognos framework manager.