Challenges
Massachusetts Eye & Ear Infirmary (MEEI) was looking for a way to avoid manually entering data from multiple financial applications into an obsolete DBase reporting package in order to generate and distribute monthly research reports.
Our Solution
DLA assisted in designing and implementing a data warehouse and reporting suite which allows all members of the research community to produce on-demand reports as soon as project and financial data are available.
- A SQL Server data warehouse imports data using SQL Server Integration Services from applications with five different database architectures (Sybase, Access, Btrieve, DBase, and a remote vendor-hosted database). A nightly ETL process Extracts data into the warehouse’s raw data area,Transforms raw data into clean normalized tables in a data staging area, and Loads transformed data into Data Mart tables and views which are optimized for reporting. See picture below.
- The database and reports integrate data from GL, AP, Payroll, Purchasing, and Project Budgeting applications. The data warehouse combines Payroll, GL, and Project data to calculate a projected Salary commitment, and combines AP and Purchasing data to calculate an Expense encumbrance.
- Ad-hoc reporting against the data mart is available.
- Data is made to look like it came from a single source. In particular, the data warehouse generates reconciling transactions where necessary to force source systems to match the General Ledger.
- Security is administered using a web-based application build by DLA. Users can directly access data only through data mart views. All data mart views incorporate security which limits users to only those specific cost centers and data types which they have a right to see. Therefore, the data can be securely accessed using any third party tool.
- A suite of sophisticated financial research reports are implemented using SQL Server Reporting Services. Reports were specifically designed to render well in various browsers (IE, Safari and Mozilla).
- SQL Server Reporting Services allow web-based access to 33 financial reports. User-controlled report parameters simplify data selection and sorting. Negative variances and project problems are highlighted. Users can drill down into a series of more detailed reports to analyze issues. Reports can be exported in a wide variety of formats.
Results for MEEI
- Administrators and Researchers have better control over expenses and revenues versus budget, because reports are more timely and accurate, and because they include projected salary expenses.
- Grant Accountants spend less time entering data, generating reports, and reconciling reports. Operationally, the application improves the process for calculating secondary costs, and improves cash flow by reducing the need to estimate expenses when drawing on letters of credit.