1 / 30

Using Data Mart reports to create snapshots

Using Data Mart reports to create snapshots. Jaap van Reijendam Data Warehouse Architect Brandeis University 05/01/2013. Brandeis University. Founded in 1948 Named after late Justice Louis Brandeis Youngest Private Research University 3500 Undergraduates 1800 Graduates.

devon
Download Presentation

Using Data Mart reports to create snapshots

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using Data Mart reports to create snapshots Jaap van Reijendam Data Warehouse Architect Brandeis University 05/01/2013

  2. Brandeis University • Founded in 1948 • Named after late Justice Louis Brandeis • Youngest Private Research University • 3500 Undergraduates • 1800 Graduates

  3. Jaap van Reijendam • Degree in Computer Science with specialty in data-structures • 17 years experience with PeopleSoft (CS, HR, FIN) • 14 years experience in Higher Education • 9 years at Brandeis • Since 2010 in charge of Data Warehouse

  4. Agenda • Architecture • Head count report version 1 • Snapshot needed • Census-Date needed • Deploying in MicroStrategy • Questions?

  5. Data Warehouse Architecture

  6. Existing Data Marts • Student Records • Term Enrollment • Class Enrollment • Class Information • Current Program / Plan • Faculty Information • Consolidated Statistics • Financial Aid / Student Financials • General Ledger / Budgeting • GL Journal • GL Ledger • Commitment Control Balances • Commitment Control Transactions

  7. Head count report version 1

  8. ETL Process Using PeopleSoft delivered process for creating “Consolidated Statistics” Added ETL process to extend with Student Financials and Financial Aid data Runs in the PeopleSoft source system

  9. Snapshot needed Students Enrollment wants to see numbers over time Budget office wants to see “current” data

  10. Extended key-structure table

  11. Census Date needed Registrar needs to provide official numbers Numbers are based on a census-date, taken each semester Census date stored in PeopleSoft However, not always exactly right

  12. Deployment in MicroStrategy Goal to have ETL only create “current” snapshot MicroStrategy to create regular (non-census) daily snapshot Allow Registrar to run official census snapshot

  13. Create “Today” Attribute

  14. Setup Column Alias

  15. Census Report = “N” Attribute

  16. Set up Metrics

  17. Census = “N” Report

  18. Report SQL: Create Table

  19. Report SQL: Delete & Insert

  20. Census = “Y” Report

  21. Add VLDB Properties

  22. Additional SQL

  23. Metrics now in current and snapshot

  24. Questions & Comments? • Jaap van Reijendam • jvanreij@brandeis.edu • 781-736-4254

More Related