Developing a Self-Service Business Intelligence Solution
Services
- Integrate the data from various WDACS and County-Wide systems under a single application architecture and relational database.
- Implement County Standard Business Intelligence tool (Cognos) to allow flexible access to the data on-demand
Features
The Challenge
The development of Self Service Portal 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. Furthermore, such system requires a role based access through a web interface to WDACS staff at various locations, based upon County established security requirements. Additionally, County Standard Business Intelligence tools (Cognos) are required to be used to development such portal. Coordination with County resources to initiate development and integration activities forms a significant portion of this engagement. WDACS needed a vendor who can not only develop business requirements for reporting architecture, but also assist in development and implementation.
The Solution
WATI team implemented this project in multiple phases and handled all aspects of this deployment including business requirements, report framework standardization, data integration, portal and report development, user security ad project management.
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. The activities included creating the specifications for the database, user interfaces and reports.
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 transactional) along with derived relationship tables using County standards and licensed development tools. 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.
WATI setup the reporting infrastructure for online reporting, distributing batch reports and for analytical facilities and decision support to management. The team structured the Cognos ReportNet for the department’s specific needs and assisted in development and support for the Cognos report library with primary focus on the WDACS Cost Allocation Cognos reports. These reports were developed with Cognos reports studio and Cognos framework manager.and involved multi-drill through cross-tab and list reports .The team worked with financial management division to obtain specifications and perform data validation testing. In addition Cognos charts were developed to augment the table-based reports.
The team built a self-service library of inter-system reports using County Bl standards and licensed development tools. WATI team planed, analyzed and built a self-service information portal using Cognos for WDACS governing bodies and contract agencies that allow for management of financial and operational performance based on County standards.
WATI trained County Managers and Analysts in the use of Cognos ReportNet modules developed for WDACS. The team performed following activities:
- Provided written training materials to describe ReportNet use, functionality, and features.
- Conducted end-user meetings to identify any functionality and design issues.
- Conducted regular application change control/planning meetings with designated user representatives.
- Developed and maintained operational and system level documentation.
- Trained County WDACS staff on system management and maintenance practices.
Results
The entire engagement resulted in effective Management of all aspects of the claim automation, including of automated cost allocation to revenue claiming. The self service portal provided effective insights into the data with complex data-drill and cross-tab reporting.