The Division of Comparative Medicine in the WashU Office of Medical Research use a tracking system called LAMPS for their processes and daily operations, but found that this system was lacking valuable reporting capabilities. As they were in need of financial and operational reporting, they approached the Data and Analytics team to see if there was some other way to get the critical reporting that they needed.
The Data team went to work on a solution, and after investigating options, devised a plan consisting of 3 parts.
- The DCM provides exports from the LAMPS system once per month
- The Data and Analytics team loads the exports into a special database called the LARS database
- A Cognos report is generated from the data in the database with the data that the DCM needs
In order to make this happen, the Data and Analytics team set up and configured the custom database which serves the purpose of housing the data exported from the LAMPS system. They also authored a custom report in Cognos according to exact specifications so that it could provide the DCM team with the information it needed. The value of this report was a direct result of close collaboration between the two teams.
Each month, six export files are synthesized into a single database table which is then used as the data source for the custom report. Stakeholders are automatically notified when the Cognos report is refreshed with the new data and ready for use. The report helps the DCM determine which PIs need equipment, space, etc., as well as providing spend tracking.
Division of Comparative Medicine users can also create their own ad-hoc reports based upon two custom data source packages in Cognos which the Data and Analytics team built for their reporting needs.
The latest effort by the Data and Analytics team related to this project is a move of the LAMPS database from an on-premises server into an ODS (Operational Data Store) in a cloud-based solution called Azure. While this will not be a “visible” change to the DCM team (their data and reporting will remain the same), it will keep the data as secure as possible, using the most up-to-date tools available in Microsoft’s Azure cloud platform. Those same tools offer slightly more in terms of monitoring, logging, and auditing than the older SQL Server platform would have. Additionally, these logs are visible from an internet browser instead of a special program (SQL Server Management Studio).
The Data and Analytics team has added financial and budget data for Capital Projects, Non-Capital Projects, and Gifts to the data warehouse in order to strengthen analytical and reporting capabilities for the university.
The Data and Analytics team developed a direct way to access and report on data for the School of Continuing and Professional Studies, and saved them thousands per year in labor costs as a result.
The Data and Analytics team implemented a reporting solution that helped create a more succinct and scalable process, dramatically reducing the time spent producing reports and eliminating manual efforts with automation.
The Data and Analytics team helped leaders across our university gain invaluable insights into WashU’s Economic Impact in the region, providing technical expertise in data collection, organization, and synthesis.
“As an economic engine in our community and our region, there’s a lot of excitement around this. And the work that (Data and Analytics) has done is a fundamental and important element to us being able to do that well,” said JoAnna Schooler, Assistant Vice Chancellor for Community & Local Government Relations.
Data and Analytics has added the HCM Jobs and Positions datamart to the WashU Data Warehouse. This datamart includes Jobs, Positions, Requisitions, and Academic Appointment data, and is designed to help data consumers get a more comprehensive view of our employees and contingent workers.
The Data & Analytics team recently completed another effort with the university’s Undergraduate Admission’s (UA) Integrations and Strategic Analysis teams to enhance the Slate data mart in the Data Warehouse (DWH) to support continuous improvements to the university’s undergraduate recruitment, admissions, and institutional reporting source system: Undergraduate Slate.
These updated fields were previously unavailable in Slate. The most recent changes were:
- An updated field to provide information if an applicant is a new or transfer student.
- An updated field to help identify future students using unique IDs.
- An updated geographic-based field to help admissions officers understand where students live before applying.
- An updated numeric-based field to help rate student applications.
- A field to indicate that certain admissions materials are not needed for applications.
We are thrilled to announce the launch of the Student Data Program at Washington University in St. Louis!
This initiative, led by the WashU IT Data & Analytics team in collaboration with key university partners, is designed to bring student-related data from a variety of source systems into the WashU Data Warehouse, offering a comprehensive view of the student journey at WashU – from admissions to graduation, financial aid, campus life, and more.
The WashU IT Data & Analytics team recently completed another effort with the university’s Undergraduate Admission’s (UA) Integrations and Strategic Analysis teams to enhance the Slate data mart in the Data Warehouse (DWH) to support continuous improvements to the university’s undergraduate recruitment, admissions, and institutional reporting source system: Undergraduate Slate.
The recent changes were:
- Eight new source system fields related to the Fall 2024 application cycle have been added to the DWH, which will enable UA staff and the university to report on and analyze this data alongside pre-existing data in the DWH.
- UA recently began purging old Organization Contact (OC) records from the source system, so the DWH modified our processes to mirror the source system’s OC records.
- Additional modifications were made to the DWH’s processes to provide more consistent default date values that provide more efficient reporting capabilities for the UA staff.
The university HRMS, AIS, and WebFocus systems were decommissioned on November 15, 2022.
Additional Information:
- Most of the day-to-day operational reporting that was previously done in HRMS or AIS can now be accomplished with Workday.
- Legacy HRMS and AIS data and reports (data prior to July 2021) can be accessed via Cognos.
- WebFocus report preservation was recommended prior to that system’s retirement.
If you have any questions, please contact the Data Management support team at data@wustl.edu.
The Data management team has developed an ‘Other Support – NIH Format’ report in the new NIH format using data in the RMS datamart. Click the button below to see details about this report.
Current (Workday) Awards data is now available in the Data Warehouse and the new Awards package has been released in Cognos. You can find the Cognos package under Team Content > Packages > Financial Reporting Packages > Workday Financial Packages. Learn more about the new Awards package here.
Following is a list of enhancements that are now available:
Web Authoring Improvements:
- You can now create, edit, and rename published data sources on Server with no need to republish data sources from Desktop. Modified data sources automatically propagate throughout all content types that use it, and you can see your changes automatically indexed in the catalog.
- You can now copy & paste in dashboards. This feature will allow you to easily copy and duplicate images, text, boxes, and web page containers within the same and across different dashboards.
- New workbook formatting enhancements allow you to choose from a variety of number and data formatting options for Parameters.
- Easily format numbers to display negative values and localize currency with the proper symbol and value
- Use any common date and time format for date fields, or quickly customize them according to your company’s standards
Inherited descriptions now appear in the fields list on lineage pages:
If a field in the source has a description already associated with it, Tableau will pull that description in (for example, if a DW field has a description the same description will be pulled into Tableau). Information about where the description is inherited from is also included.
Easily create Prep flows based on external assets:
Now you can use databases and tables as starting points for new flows in Prep web authoring.
The Office of Data Governance has classified Compensation data in the data warehouse as sensitive, and so access to the Compensation data for reporting/analytics has been restricted at the data consumers’ own school level. Access is granted by request only.
Access to Compensation data is requested using the School Compensation access request form. Approval from the appropriate department supervisor and data administrator for this domain is required. The domain approver for compensation data is CHRO – Legail Chandler. Once completed, the signed form should be submitted to the Data Management service team, which will then grant the appropriate access.
The Office of Data Governance (ODG) is excited to announce a new collaboration and learning opportunity for our Stewardship community, called, Collibra & Coffee.
Collibra & Coffee is intended to create an engaging forum for dialogue focused on learning more about Collibra, Data Governance, and WashU’s Stewardship practices.
Collibra & Coffee is a monthly office hours format created to support YOU, our Data Stewardship Community. This series will have an informal dynamic agenda to allow ease of questions, dialogue, knowledge sharing, exchanges of business use cases, best practices, performing demos, sharing tips and tricks, and much more!
We look forward to you joining us! Thank you for your continued support and all YOU do for the greater good for Data at WashU.
Questions? Contact the Data Governance Team at: datagovernance@washu.edu
The Data Management add-on allows for automated scheduling of Prep flows on the Server. Once scheduled, the flows will populate without the need for a manual refresh from the author’s desktop.
If you experience any technical difficulties after the upgrade, please contact data@wustl.edu.
With the help and support of the RMS user community, Data Management was able to accomplish the following:
- Successful kickoff and execution of User Acceptance Testing (June 16th – July 15th)
- Refactored RMS Data Mart cutover (July 16th – July 19th)
What changed?
What does this mean for the Data Warehouse?
The newly refactored RMS data marts transitioned from an on-campus server data warehouse to a cloud-based data warehouse hosted in Microsoft’s Azure environment and administered by the Data Warehouse team.
What does this mean for Cognos?
- The packages in Cognos were updated to reflect the changes made as part of the transition to a cloud-based data warehouse, including being updated to the new dynamic query mode (DQM). DQM packages have improved interactive query performance for all users in Cognos.
- The packages and IT-managed reports in Cognos were updated to account for the new data fields introduced to RMS as part of the new integration with Workday (e.g., Cost Center Hierarchy Rollup).
How do these changes impact users?
- Users will be required to sign into Cognos with their WUSTLKey Email (including the ‘@wustl.edu’), rather than their WUSTLKey username. This change was previously communicated to all Cognos users.
- IT managed reports have been updated with the new packages and data fields. The list of IT managed research reports can be found on the newly updated Data Management website.
- Report Authors that have built reports off of the pre-Workday RMS packages will need to copy their reports and replace the package with the newly refactored versions of these RMS Pre Award and COI packages. Instructions on these changes and recommendations on field replacements can be found on the newly updated Data Management website.
RMS transition to operate and support:
If any issues arise, the RMS user community can use the current ServiceNow process by emailing the distribution list at data@wustl.edu. The mailbox will create a ServiceNow incident and assign it directly to the Data Warehouse support assignment group. Please provide as much detail as possible on your issues when you submit (e.g., report name/location, exact error details, screenshots, etc.).
Data and Analytics Blog
Where the Data and Analytics (DnA) team shares essays and information about various data-related topics.
Data Governance Blog
Where the Office of Data Governance (ODG) shares WashU’s Data Governance Journey.