Back to Success Stories

Empowering Data Warehouse Technology

Tuesday, August 27, 2019

Empowering Data Warehouse TechnologyTo execute its vital mission, the State Department must make hugely important decisions whose success hinges on the quantity and quality of the data at hand. Its data gathering efforts were often delayed by numerous institutional obstacles at State. In response to a mandate of the Secretary, CITI built a data warehouse/business intelligence solution—praised by DOS officials—that delivers enterprise data from 7 sources, stored in more than 100 tables and houses more than 675 million transactions, including performance data for 200+ posts.

To continually fulfill its mission around the globe, the U.S. State Department (DOS) has had to face one crisis situation after another over the past few decades—from the Iran hostage situation to the recent tragedy at the embassy in Benghazi, Libya. These and countless other incidents over the years point to State’s critical requirement to have decision-making data ready at a moment’s notice so it may execute its mission of creating a more secure, democratic, and prosperous world for the benefit of the American people and the international community. The central management analysis organization at State is tasked with providing DOS decision makers with the robust data analytics they need to make their routine but highly important decisions as well as to implement the Secretary’s vision of management reform and transformational diplomacy.

On a regular basis, this management analysis office faced the challenge of gathering critical data from multiple stove-piped systems which many DOS bureaus and offices maintained, independently of the others. Then, using a very manually intensive, time-consuming process of comparing, integrating, and performing calculations with this data, this organization strove to arrive at the accurate answers expected of it, in response to the regular data calls it received. In addition to briefing the State Department’s executive management in an effort to prepare them for making routine decisions and reacting appropriately to present crisis situations, the management analysis office also regularly compiles data from the many disparate DOS systems for the purpose of performing predictive analyses that will support future initiatives of the State Department. The office’s efforts to deliver the accurate and timely data analyses, reports, and presentations expected of it were often frustrated by delays and other obstacles in the data gathering process. It was little wonder then, that the Secretary of State pressed the organization to develop a centralized corporate data repository (one of her eight management reform initiatives), which she firmly believed would greatly benefit the Department.

Likewise, it was little wonder that this DOS office came to CITI to develop the data warehouse. Our company was a State Department IT provider known for “getting the job done” and getting it done with the cost-saving, innovative use of emerging technologies and industry practices. As it turned out, CITI was poised to meet the challenge, although it would be no easy task because the CITI team had to overcome roadblocks to efficient data analysis such as difficulties in gaining access to data, data incompatibilities, organizational politics, and data security issues.

However, now, after overcoming these challenges over the past five years, CITI and DOS have produced an enterprise level data warehouse and business intelligence (BI) solution that is the envy of other public and private sector organizations. The solution CITI developed for State, and for which it still assumes responsibility for day-to-day operations and administration, combines information from different transactional systems into a central point from which information can be quickly extracted and analyzed to facilitate business decisions. The data warehouse is designed to fulfill the important need that exists across the State Department enterprise for quick access to historical data for all types of decision-making scenarios—crisis management, short-fuse projects, and long-term planning. To realize this solution’s many benefits, CITI conducts monthly training courses for users, teaching them how to harness the power of the data warehouse for performing mission-critical analyses and creating the new ad hoc reports the system is capable of producing.

The State Department’s chief information officer (CIO) voiced her appreciation of the data warehouse and BI solution in a 2011 media interview. She pointed to several real-world examples of the information management power the new solution gives users through the greater visibility it provides into diverse yet related data. She said the solution enables better decision making about such things as (a) the overseas transfers of people in Foreign Service, (b) their movements from one post to another, and (c) the actual cost impact of a 2-year tour of duty versus a 1-year tour in particular locations. She was proud to say that thanks to CITI’s Data Warehouse, State decision makers could now quickly and easily locate data, make decisions, and weigh costs.

The CITI-developed data warehouse compiles enterprise level data from 7 different data sources; there are over 700 data elements available for reporting in EDW (reports that used to take weeks can now be generated in minutes); the system contains more than 100 tables and houses more than 675 million transactions (10 years’ worth of specific subject matter data); and the BI dashboard contains performance data for more than 200 posts spread over 6 different regions. The hard work this team has put into this solution over the past five years continues to pay off handsomely for State.

This CITI team’s project manager was recognized in 2008 as Project Manager of the Year by the Project Management Institute (PMI) Washington DC Chapter, for his leadership of this program. The company’s project team was awarded a Department of State Certificate of Appreciation for its efforts in developing and implementing a successful pilot despite an aggressive schedule and scope.