Data Warehouse Assignment 2

Joseph DeCicco

Thanh Lam

Stephen Parshley

Introduction

 

The “real world” application chosen for this assignment is a data warehouse project named MERLIN, conducted by the State of Mississippi.  MERLIN stands for the Mississippi Executive Resource Library and Information Network.  The MERLIN project is very well documented because it is large, ongoing, and has been very successful.  Initial information from IBM web pages [3] provided information to help us answer the first three essential questions about this project:

 

  1. What were the pressing business problems that called for MERLIN?
  2. Were there stated operations before the project began?
  3. How were resources and timeliness estimated?

 

Further search on the web has revealed voluminous interesting information about MERLIN.  Because of the project’s wide publicity and recognition among other state governments, MERLIN gave use and excellent opportunity to study a data warehouse in action.  We learned much from the research of this project.  The state has documented the benefits of MERLIN, so we were able to find the answers to these three questions:

 

  1. What were the investments in hardware and software?
  2. What were the assessments of the project results?
  3. What were the lessons learned?

 

This paper is an attempt to answer the six questions above, as best we can using publicly available information.  See the References section for sources of information on the web.

Project MERLIN

1.    A brief description of the business problem

There were many problems prior to MERLIN with the information systems of the State of Mississippi.  The states information collection processes were unable to provide executives information necessary for effective decision-making.  State managers desired precise, current, accurate information.   The information was difficult to retrieve.  Report formats were inconsistent and did not lend themselves to easy consolidation at the state level.  Worse, information was often outdated and collected using inconsistent data entry rules.  State executives wanted information retrieval from multiple data sources.  State workers realized that they spent too much time reorganizing data into a single, comprehensive, and understandable format.  With these business problems in hand, the Governor of Mississippi, the Department of Finance and Administration executive director and the Mississippi Management and Reporting System (MMRS) partnered with IBM to build MERLIN.

2.    Stated operations of the data warehouse

MERLIN is a statewide executive information and decision support system implemented using client/server technology to access a data warehouse.  From a single mouse click, information is available for Appropriations, Cash, Capital Assets, Debt, Diversion, Economic/Statistical analysis, Expenditures, Federal Funding, and Revenue.  It also allows executives to examine, graphically, represented trends on their chosen subject.  Data are cleaned before consolidation.  Information is timely.  Reporting is flexible.  

3.    Estimate of resources and timeline

The project began in mid-1995, when a task force was formed to define common information and subject requirements.  After initial assessments and approval of funding, the project was broken up into four phases. 

Phase 1 began in July of 1996.

This phase was to accomplish several objectives.  First, it provided executive level definitions for common information needs and subject requirements.  It also provided the user interface design.  All of the hardware and software was set up in this phase. The challenging part to this phase was deducing where the data were located and how they could be extracted.  The goal was to get the workforce application up and running in four months [3].  In short, phase one was dedicated to getting a handle on the raw data collection. 

Phase 2 began in December of 1996.

This phase involved the finance application.  Data cleaning proved highly complex and took longer than expected, but the phase still completed on time.   The application was delivered on time.  The accomplishment of this phase was that “Users now can access detailed, transaction-level data to review agencies’ current funding status and investigate problems in key categories, such as revenue, appropriations and federal funds.” [3]

Phase 3 began in July 1997.

This phase involved adding “data on debt, capital projects and economics/statistics, along with initial development of internet access” [3].  The goal was to provide public access to data using the Internet tool called MERLIN.net.  Though the migration from intranet to Internet is simple in principle, the phase was very complex.

Phase 4 began in January of 1998.

There were many objectives for this phase.  First, data from diverse operational systems were integrated.  Managers were trained on how to access data.  Finally, tools were created to help non-technical users learn how to query the system and also how to create reports and graphs.  

 

Manpower resources included 20 MMRS staff to handle ad hoc requests.  In addition, there were 40 other users in 14 state agencies involved in this system. Mississippi invested about $5M into MERLIN.

 

4. A brief description of software/hardware environment

IBM signed up for the contract and won the bid for building MERLIN, starting in 1996.  The IBM Business Intelligent web page entitled “Almost Magic” [3] has a brief description of the data warehouse, hardware, and software being used:

 

In another article, “DB2 and Domino help Mississippi put public funds to best use” [4], DB2 is indicated as housing the MERLIN data warehouse.  This article also mentions that Visual Impact from PSR Software is used to power the MERLIN.net web site.

 

Additional information from an “IT on Human Resource Management” [1] paper revealed that:

 

The IBM SP2 system is a six-node parallel server that contains 256 gigabytes of data storage capacity [5].  “Currently, five years of financial data are being stored in the 50 gigabyte data warehouse.” [4]  All users inside and outside of the State can access the system via the State’s Intranet and Internet.  The current MERLIN system involves terabytes of data.

 

5. An assessment of the Data Warehouse’s success

From the National Association of State Information Systems (NASIRE) [5] web site, MERLIN was nominated for The Administrative Applications Award.  Although it didn’t win the award, it’s a success story with the following significant praises from the nominee write-up [5]:

 

In the long run, MERLIN has positive financial and managerial impacts:

 

6. Lessons learned from the project

First, the MERLIN team has factored in growth in the amount of data collected and system users.  The databases have been able to absorb new data with graceful scalability.  “MERLIN’s original purpose was analysis of revenue, budgets, expenses and cash flow.  The warehouse now includes HR data from various sources, such as individual personnel actions and detailed pay records.”  And the number of users grew “from 30 to over 100 users” within a short time [5].  That number kept growing to about 230 [1].  And with the plan of providing MERLIN access to public, its user base will certainly grow in the future.

 

Second, the team realized that building such data warehouse wasn’t a simple feat.  It was sure costly and it could never be “cost-justified per se” [3].  The hard work in data cleanup was due to the complexity of source systems [3]; as the databases came from different sources and systems [1].  The benefit of the system transcended a strict dollars cost analysis.  The good will generated by an efficient, accountable government is, in some ways, priceless.  Therefore, the system has had significant benefits other than attempting to limit the labor costs of running the state government. 

 

Last but not least, Mississippi learned that the partnership with an IT company in such a project is crucial.  The Mississippi-IBM relationship turned out to be very good.  Both organizations benefited.  “According to team members from both the State and IBM, MERLIN’s development has been characterized by a generosity of spirit not evident in many comparable situations….  In fact, MMRS managers consider the relationship with IBM to be one of the best with any vendor in Mississippi’s history.” [3]

Conclusion

The MERLIN project has been recognized as a leading model for other States to follow.  Most notably, NASIRE, the association of state CIOs, awarded MERLIN as an exemplary application of IT to state government.  MERLIN provides valuable lessons about how to build and maintain a large data warehouse project.  The continued existence of MERLIN.net affirms its utility.  “The State believes, MERLIN.net will provide citizens direct answer to commonly asked questions about the State…” [5].  And, “the most significant benefit will be realized in providing the Executive and Legislative branches with the ability to obtain information quickly and accurately for decision making.”

References

 

1. IT on Human Resource Management, Osmosis Info Pvt. Ltd.
This is one of the papers posted on the website called ProjectsHub.com. The objective is to study the impact of the advances in information Technology on the Human Resource Management (HRM) function. MERLIN was listed as an example.  The paper provided information about the software and hardware resources used in MERLIN project. The paper also explained what MERLIN has been used for.

2. MERLIN.net Official Website, Mississippi Management and Report System.
This website gives public access to MERLIN. It also provides MERLIN reference documentation such as: MERLIN Analyst Training Manual, MERLIN Analyst View Discussion Database, MERLIN Reference Document Database, and MERLIN Data Dictionary.

3. Almost Magic: The State of Mississippi Partners with IBM to Create its MERLIN Data Warehouse. 1998, IBM Corp.
This article introduced many aspects of the MERLIN data warehouse.  It also described the three phases of the project. However, it was written in 1998 and does not provide information about the current state of the project.

4. DB2 and Domino help Mississippi put public funds to best use. 1998, IBM Corp.
This article touted IBM’s data warehouse efforts including various software applications that made the project a success.

5. NASIRE Recognition Awards Program Nominees in the Administrative Applications Category. 1998.
NASIRE stands for National Association of State Information Systems.  It is an organization of state chief information officers in the United States. Their records showed that MERLIN was nominated in 1998 for Administrative Applications Award.

6. Powering the world's e-business solutions. 1999, IBM Corp.
An overview of how DB2 UDB and data warehouse or data mining software can help in e-business solutions. This paper has a quotation from Julie Allen, MERLIN Project Manger "Though the MERLIN data warehouse has grown very quickly, DB2 has grown with it. And, with its Internet connectivity, MERLIN's net benefit to the state is projected to be $2.9 million by 2002."

7. Mississippi Executive Resource Library and Information Network. 2001, Mississippi State.
Scores of information about MERLIN schedule of availability, how to become a MERLIN user, what MERLIN is, MERLIN Analyst User group meetings, MERLIN goals, MERLIN participating agencies, training schedule, and minimum system requirements to run MERLIN Analyst.