A leading university in California with a large Oracle footprint and over 600 users needed to re-architect its data warehouse solution for the Gift, Grants and HR data marts. As part of the initial go live, they wanted to implement their Gift data mart for Advancement services. The Gift data mart tracks the gifts/donations done by board members, trustees, alumni, faculty, staff, parents or any other internal/external sources. It covers Donor Services, Prospects management, strategic initiatives, Campaigns and Campaign communications.
The client wanted to have the following as part of the project
- To maintain historical data.
- To setup a migration process for Oracle Data Integrator (ODI) and Oracle Business Intelligence Enterprise Edition (OBIEE)
- To schedule ODI jobs using an external scheduler
Solution: Key implementation points for the Business Intelligence /Data Warehouse Project
The Gift data mart Star Model was designed using small manageable dimensions comprising of similar attributes. The Slowly Changing Dimension (SCD) Type 2 methodology was implemented to keep history for all the data and keep track of all the changes.CTR also directed the naming standards for Database, ODI and OBIEE objects to match the universal standards. Catalog structure was designed in such a way that it was easily scalable to accommodate future requirements and object level security.
CTR assisted the client designing a security table for row level security using Oracle Internet Directory (OID) repository tables. This was done so that data from various departments, projects, allocations and personal data would be secure to avoid any unauthorized access.In addition, CTR assisted the client in designing a security application to ensure that the data is securely based on allocation and users only have access to pertinent data. This made the user maintenance very easy.CTR configured Lightweight Directory Access Protocol (LDAP) for user authentication and created user and object security by creating multiple application roles for data access by over 200 GIFT Data mart users excluding Campus users (DBA, Developers, Analyst, Super User, Management User, Staff User, Campus User etc.)
- External Scheduling
CTR configured ODI jobs using an external scheduler, AppWorx. We configured an ODI Agent on the AppWorx server to execute jobs from command line.
- Design load validation
CTR in conjunction with the client designed automated validation processes to track daily loads. This process does the data reconciliation by calculating counts for source and target data and sum of each amount fields.CTR created graphical views highlighting mismatches which enables developers and analysts to keep track of the daily load process and take action in case of any errors.
- Multi User Development (MUD) environmentOur team setup MUD environment to enable multiple developers irrespective of project teams to work on the same RPD.
- Landing PageCTR assisted the client in designing default landing pages for each group. We developed an application to keep track of information about users, groups and assigned landing page information. This information will be used to redirect a user to the respective landing pages at the time of logging in.
- Custom Home Page
CTR configured the home page and linked it to a custom page which provides information about the university, a list of data marts with their details, important university links, links to Business Intelligence (BI) documents, their help center and other details.
- Migration Process
In conjunction with the client, CTR defined a migration process for BI objects using Volume Shadow Copy Service (VSS) for version control.
OBIEE 12C, ODI 12C, Oracle DB 12, Apex, AppWorx