Challenge

Introduction 

State of Illinois Central Management System (CMS). CMS is responsible for overseeing the benefits and services provided to state employees. By implementing a holistic data warehouse, CMS aims to streamline data management, improve data consistency, and offer a comprehensive view of all relevant information, ultimately benefiting state employees and enhancing the efficiency of CMS operations. 

The State of Illinois’ Central Management System (CMS) faced significant data management issues, including duplicated data, data type mismatches, and redundant tables. These problems led to inefficiencies and inconsistencies across their databases. 

Pain Points: 

• Duplicated Data: Increased storage costs and difficulty maintaining data accuracy. 

• Data Type Mismatch: Errors in data processing and integration. 

• Duplicate Tables: Confusion and inefficiency in data retrieval. 

• Lack of Data Integrity: Inaccurate reporting and decision-making. 

• Backup Analysis: To identify the future growth patterns of database. 

Measurable Impact: 

• Efficiency: Time wasted correcting errors, reducing productivity. 

• Scalable System: No upfront investments; the systems are scalable based on business needs. 
• Revenue: Missed opportunities and potential revenue loss. 

Solution

Objectives: 

• Deprecation of tables. 

• Designing new surrogate keys and primary keys. 

• Designing unique constraints and identifying composite business natural keys. 

• Designing relationships among 300 tables using Primary Key-Foreign Key relationships. 

• Creating a normalized holistic data warehouse for faster input operations and a denormalized data mart using a star schema for high-performance reporting systems. 

Our team at Krasan embarked on a mission to transform the client’s database landscape by applying fundamental database principles to achieve accurate results, high-performance systems, and scalable features in an agnostic manner. The complexity of the solution required us to manually review tables [Including System tables], deprecating them to 500+ [Excluding System Tables] tables and finishing the data warehouse with 300 tables. This meticulous effort ensured that the data warehouse was both comprehensive and efficient. 

We delivered the solution on time, despite discovering added complexities along the way. We employed a proprietary process to find and design surrogate keys, primary keys, unique constraints, and composite business natural keys. Our innovative approach to designing relationships among the tables and creating a normalized data warehouse and denormalized data mart set the stage for a robust and high-performance data management system. 

Features and Components: 

• Unique Keys: Each member or agent is assigned a unique key, along with composite business natural keys, to ensure data integrity and facilitate easy data retrieval. 
• Centralized Data Access: Users can access all relevant information from a single platform, eliminating the need to query multiple databases. 

Outcome

The implementation of the holistic data warehouse resulted in significant cost savings, increased revenue, and time efficiencies. The streamlined data management processes enhanced reporting capabilities for better business insights. The solution also saved time spent on data retrieval and management, allowing employees to focus on more strategic tasks. 

Key Benefits Delivered: 

Data Management: 

• Simplified data retrieval and management by consolidating multiple databases. 

• Enhanced data consistency and integrity through unique keys and composite business natural keys. 

Performance: 

• Reduced latency, leading to faster query responses. 

User Experience: 

• Users no longer need to query multiple databases, as all relevant information is accessible from a single platform. 

• Real-time insights and analytics support better decision-making. 

Enhanced Business Intelligence: 

• Build an analytics platform to answer queries visually and in real-time. 

• Create interactive dashboards that provide real-time insights into key performance indicators (KPIs), trends, and metrics. These dashboards enable business users to make data-driven decisions quickly and effectively. 

Operational Efficiency: 

• Unified platform reduced the complexity of data management. 
• Real-time case tracking from employee to case level improved operational oversight. 

Krasan Consulting Services is an I.T. consulting firm in Chicago with extensive experience in Oracle implementations and enterprise resource planning solutions. We have established ourselves as a leader in technology consulting, delivering innovative solutions to government agencies and organizations across industries nationwide.