Backend Overview

 

Microsoft SQL Server 2005

Bus Apps is backended by Microsoft SQL Server 2005.  The EDW tables we download are stored in a database ("DW").  Since the database is read only, there is no transaction logging for performance improvement.  The table structure of the tables downlaoded mirrors the EDW tables.  This is one less layer of transformation and makes the SQL easily portable to other tools,such as Toad.

Permissions

Read access to the DW data is limited to business staff in our college.  The goal is to match the permissions on Banner - therefore staff request authorization via normal Banner Unit Security Contact channels. The HR Transactions data (in a different database) is under tighter constraints -- limiting users to view and add transactions only within authorized org codes.

The MS Access Project frontend has no usernames or passwords in it.  Authentication and authorization are done via the Active Directory and SQL Server.  The connection uses OLE DB (no odbc dsn!).  Tables, views, stored procedures and the like are all maintained on the SQL Server (whereas a traditional .mdb file has linked tables and queries).  So data remains in SQL Server but is managed and displayed in an Access Project.

Multiple Databases

Data from our College "ED" database is tapped by the HR Transactions component.  This module needs transaction logging/backup and ties to the central People table.