What is the Commonwealth Information Warehouse?

In this week’s Q+A, we discuss the goals and benefits of the Commonwealth Information Warehouse (CIW). Technical Manager Kevin McGrath discusses how the CIW improves EOTSS’ services and how it relates to our broader tech transformation.

1. What is the Commonwealth Information Warehouse and what different services does it provide?

The Commonwealth Information Warehouse (CIW) brings together a subset of the Commonwealth’s financial, budgetary, human resources, payroll and time reporting data. It serves as the central data repository for the diverse reporting needs of users and agencies across the Commonwealth. The CIW regularly updates data from MMARS, LCM (Comptroller Labor Cost Management), HR/CMS, UMass HR & Payroll, and other HRD, MMIS, and A&F supporting systems. CIW also preserves static data associated with retired systems — such as Classic MMARS, PMIS, CAPS, legacy MBTA & MA School Building Authority.

2. Who can access the CIW?

The CIW serves approximately 2,000 active users in 160 agencies & departments. Access is granted based on job responsibilities. The vast majority of users are desktop clients — Access, Excel, Tableau, WinSQL, or other ODBC compliant tools. There are approximately 20 system integration partners using Microsoft SSIS, Microsoft SQL Server utilities, Oracle ODI, Informatica, Java, etc. The CIW may serve as “upstream” data source for agency “downstream” analytical and statistical reporting platforms.

3. What are the benefits of CIW?

The CIW provides access to integrated, common data that supports timely, well informed business decisions to authorized users through a centralized and integrated repository. The CIW provides flexibility — allowing data integration with a varied set of source platforms, databases, applications, and data formats.

4. What is your team’s role in managing and modernizing the CIW?

The EOTSS CIW team develops data extraction, transformation, and load routines (ETL). We ensure accuracy of data, provide timely availability, safeguard security, provide end-user assistance, and monitor user performance. The CIW maintains infrastructure of current data warehouse appliances and provides oversight of next-gen design and implementation.

5. How does CIW improve the services EOTSS offers?

The CIW serves as a source for data transparency efforts undertaken by the Commonwealth. The CIW has been critical in analyzing Commonwealth wide IT spending and capturing resource/system usage. The CIW provides HR/CMS and UMass payroll, and vendor payment source data to Comptroller’s CTHRU financial records transparency platform.

6. Does CIW relate to our broader tech transformation? If so, how?

Yes. The CIW integrates with critical enterprise systems which are planning to migrate to the cloud. The CIW has performed benchmark testing integrating with AWS hosted source systems. The CIW is in initial stages of evaluating path-forward plan to transform core database, ETL (extract, transform, load), security provisioning, and reporting from on-premises to cloud.

7. What are your team’s objectives and goals for the next year?

The CIW will support any re-hosting and software upgrade efforts performed by MMARS, HR/CMS or UMass. The CIW is always adding functionality required by business owners to meet reporting demands. Phased migration to cloud is major objective for next year. Ensuring proper security compliance and appropriate user access are high priorities. Investigating other access methods for data retrieval — such as JSON \ REST web services or centralized BI capabilities will be explored.

8. Do you have anything else you would like to add?

Examples of CIW use:

• Budget analysis by 160+ state agencies. Payroll forecasting. Freedom of Information Requests.
• Current data maintained: 16 TB of uncompressed, structured data. Adding 400 GB per year.
• 80% of queries (count) are desktop users. 20% of queries (count) are system\download.
• Conversely: 80% of volume (bytes) are system\download. 20% of volume (bytes) are desktop users.
• No transactional CIW data ever archived — instead, legacy data preserved in CIW allowing source systems to perform data reduction\archiving.