Addendum

Sub-Committee Report on UW Organizational Data Use and Improvements Addendum Table of Contents Sub-Committee Report ................................................................................................................... 1  on..................................................................................................................................................... 1  UW Organizational Data Use and Improvements Addendum........................................................ 1  Table of Contents ............................................................................................................................ 1  Overview of the Financial Organization ......................................................................................... 2  Overview ..................................................................................................................................... 2  Architecture................................................................................................................................. 3  Implementation ........................................................................................................................... 4  Maintenance ................................................................................................................................ 4  Suggested Business Rules for the Financial Organization ............................................................. 5  Suggestions for Revision of the Financial Organization ................................................................ 5  Model for Describing Organization ................................................................................................ 6  Dimensions ................................................................................................................................. 6  High Level Types of Organizational Information (in order of complexity) ............................... 7  Entities Being Organized ................................................................................................................ 7  Organizational Activities ................................................................................................................ 8  Financial Organization Application Matrix .................................................................................... 9  Technical Specifications for Entity-Relationship Master Data Application................................. 21  Overview ................................................................................................................................... 21  Architecture............................................................................................................................... 21  Benefits ..................................................................................................................................... 22  Diagram..................................................................................................................................... 23  Team Membership ........................................................................................................................ 24  1   Overview of the Financial Organization Overview The following is the definition of Organization Code that was published with the Faculty Top-5 Reports: Organization Code (also known as Org Code) represents the University organizational structure in a view that is primarily financially oriented. When referenced on the Budget Index (the 02 Index), Organization Code relates a Budget Number to an organizational unit. • • • • • • 2-00-00-00-00-0 - President 2-10-00-00-00-0 - Dean /VP 2-10-01-00-00-0 - Major Level 2-10-01-01-00-0 - Department 2-10-01-01-01-0 - Division 2-10-01-01-01-1 - Subdivision At any point in time, every budget number in the financial systems belongs to a single 10digit Org Code, which allows budgets to be aggregated. One Organization Code may have many budgets associated with it; however, one budget may be related to only one Organization Code at a time. Over time, one budget may be related to different Organization Codes, but only one at a time. This is what is referred to as a slowly changing dimension. The Financial Organization, in the Office of Information Management systems, originates from the Financial Accounting System Index database (FINDB01). It is contained in two sets of tables. The most commonly used tables are the budget number index (BUD-NO-INDEX) and the organization code index (ORGANIZATION-TL). For those units at the University, which use project cost accounting to track expenditures for research projects, there are organization codes as database access keys in the project data, task code and option code tables, and in the overhead cost allocation (ocap) tables: the rate, account, project, task, option and earnings code. The FINDB01 is a mainframe database. However, its data is copied, nightly, to the FINDB SQL data warehouse and made available to other University applications. It has been copied to other servers including MEAD and VITCOS. There are major issues with the Organization Code: • • • It is a multi-characteristic attribute. Even though embedded in the code, the hierarchy is not applied consistently throughout campus. It does not always represent organizational areas; it can also represent financial holding areas, such as tuition. 2 Architecture • • Architectural Overview: o The Organization Code was designed as a way to group budgets for financial rollup purposes. Generally speaking, accounting is a zero sum game, where financial transactions should be represented only once in the same context. It is therefore imperative that the Organization Code structure be a strict hierarchy to make the rollup work. o The full organization code has 10 digits, consisting of 6 levels. Levels one (the first digit) and level 6 (the tenth digit) are limited to 9 items (a zero is equivalent to a null value); the other four levels have two digits, and are therefore limited to 99 items. Architectural Issues: o Single structure with an intelligent key; The use of intelligent keys (where the code itself has business meaning) is a violation of generally accepted data architecture principles; Surrogate keys (keys that have no business meaning and are used for database structure) are the industry standard for a slowly changing dimensions such as Org code. Using intelligent keys in database architecture is inflexible and increases the likelihood of re-architecture when the business makes changes to the key. In this example, reorganization could potentially cause issues in the underlying data structures and force some re-architecture to ensure accuracy in reporting. The implementation of a single code means that the codes themselves must change in order to facilitate any reorganization of the structure; in other words, a code represents both a grouping of budgets and its relationship to other groupings at the same time; this essentially eliminates the ability to refer to the codes in other systems, because every time the relationships between codes change, so do the codes themselves. o This hierarchical structure is insufficient to represent the complexities of the business of the University. A single organizational entity may need to be represented as belonging to more than one other entity (e.g. the Department of Global Health belongs to both the School of Medicine and the School of Public Health and Community Medicine). The Organization Code is unable to accurately represent the reporting relationships between appointees at the University, because there is not necessarily a direct relationship between the way budgets are organized and the way reporting relationships among personnel are organized. o The Organization Code lacks a robust time dimension or support of granular time variance. In the context of analysis and reporting, it is very difficult, if not impossible to accurately represent changes over time to the code structure; this may be partially mitigated using ETL and warehousing. 3 Implementation • • • • The Organization Code is simultaneously used in more than one context; multiple organizational structures are combined into a single structure. It does not always represent organizational areas; it can also represent financial holding areas, such as tuition. Organization Code: o Budgets (as ledger cash accounts) indicate actual funds available to units, and facilitates the ability to transfer funds between units; o Budgets (as ledger accrual accounts) indicate revenues and expenses available to units; o Budgets (as a budget planning mechanism) indicate funds available to units over a period of time; o Budgets (as a control mechanism) are used to track and control spending for units over a period of time; Personnel Organization: o Budgets operate as a surrogate for an organizational unit for personnel purposes (e.g. appointing department budget number) o Budgets operate as an indicator for primary appointments for faculty o Budgets operate as an indicator of organizational responsibility (home department) o Budgets operate as an indicator of location (home department as related to mailbox number and payroll unit code) Operational Organization o Budgets operate as a surrogate for an organizational unit for operational purposes: The placement of budgets in the Organization Code structure therefore operates as a surrogate for a Organization Chart for the University; Budgets are used for asset management through inventory control Maintenance • The Organization Code has long lacked active stewardship in the management of the rules and usage of the organizational structure. o The rules with regard to the usage and maintenance of the Organization Code structure are not broadly available. o Even though embedded in the code, the hierarchy is not applied consistently throughout campus. o The structure lacks consistent naming conventions, leading to confusion. o There is a lack of guidance with regard to the use of data in the Organization Code structure; consequently, the data in the structure is often misunderstood or misused, leading to confusion. o The Budget Office enters and alters the codes, because the of the relationship between Financial Organization codes and Budget Numbers, in the Financial Accounting System (FAS) and flows through to the other systems such as HEPPS and BGT, and the EDW. 4 • The Organization Code structure lacks official mechanisms for navigating and using the structure. Suggested Business Rules for the Financial Organization The following are suggestions for business rules governing the Financial Organization. Any items here should be reviewed for alignment with business needs before being implemented, as part of a larger process of developing, vetting and implementing business rules governing the Financial Organization. • • • • • • • Any change to the Financial Organization must be explicitly approved by Planning and Budgeting. For each org code value, only one budget per org code should be flagged for use as a home department budget, as indicated by the Home Department Org Indicator flag on the budget. This budget should be used for appointments in the given org. Changes to the first seven digits (first four levels) of the Organization Code shall conform to rules specified by Planning and Budgeting. Changes to the final three digits (last two levels) shall be made at the discretion of the respective Deans, Chancellors, Vice Presidents and Vice Provosts. The Financial Organization shall be used to designate reporting relationships and budgetary responsibility. Other changes to the Financial Organization shall be disallowed to the extent that they impact the designation of reporting relationships or budgetary responsibility. A change in the name of an organization that does not include a reorganization of reporting relationships or budgets should be reflected as an update to the name of the specific Financial Organization entry in the financial systems. A change in organizational structure, either in terms of reporting relationships or budgets should be implemented as a combination of renaming or removing existing entries, and adding new entries, as necessary, in the Financial Organization, and moving corresponding budgets as necessary, in order to accurately reflect the organizational change. Suggestions for Revision of the Financial Organization Below are suggestions for a revision or reorganization of the Financial Organization. • • Conduct a general analysis to determine the extent to which the organization code should be separated by physical location, personnel reporting structures, budgetary control, fiduciary responsibility, or a combination thereof. Attention should be given to the fact that a number of university systems commonly report entries in the Financial Organization at the 3-digit, 7-digit and 10-digit levels. 5 • • • • • • • • • The delineation of Seattle General University and Health Sciences into two separate groups is based on a historical funding model employed by the State of Washington, which no longer applies. Separate the University Administration from Seattle Campus and into two separate groups. Combine all Seattle Campus academic schools and colleges (including those that are currently in Health Sciences and are located on the Seattle Campus) into a single Seattle Campus organizational structure. Investigate the creation of an organizational structure to support cross-functional units and budgeting. Create a separate organizational structure for Medical Centers, including UWMC and Harborview Medical Center. Clarify the organizational structure of Health Sciences Administration in terms of location, reporting relationships, fiduciary responsibility, and budgetary control. Conduct analysis to account for regulatory requirements that may be impacted by changes to the organizational structure. Organize Schools and Colleges on the Seattle Campus, along with academic Programs at Bothell and Tacoma campuses to align with a single, consistently applied model. Delineate organizational units at the Vice President and Vice Provost units into separate organizational structures with similar form and function. Model for Describing Organization In order to consolidate information regarding Organization in a consistent manner, a model was developed. The model was used to organize the information in the "Organizational Activities" section below, which attempts to summarize different efforts to organization information in university business processes, as well as the "Financial Organization Application Matrix" section below, which attempts to comprehensively list the enterprise systems that use the Financial Organization. In order to uniformly describe organization, the model below describes three dimensions, and then describes the concept of Organization information using four different types of organizational information using the defined dimensions. Dimensions • Entity o Entities (or Things) are unique items that either have a physical or logical manifestation (or both). They are atomic, meaning that they have a meaning in the absence of an action and they are not divisible: Ex.1: Person (physical) A Person uniquely exists regardless of whether they are doing anything A Person cannot be divided and have meaning. You can't have half a person 6 • • Ex.2: College (logical) A College exists as a single concept that is different from the other things of which it may be composed. A College cannot be divided. A college may contain 10 departments, but half a college is not equivalent to 5 departments. o Things may have characteristics, that vary between types of things o Things may have characteristics that are common cross types: Name or Unique Identifier Period of Existence Relationship o Relationships describe a connection, organization, or structure between or among two or more entities within a certain context. o Relationships cannot exist without the entities they describe. o Any two or more Entities can be related to one another under different contexts. Process o Processes involve actions by Entities or action on Entities. o A process may be simple, or a combination of more simple processes. o Processes may use or follow relationships as they act on entities. High Level Types of Organizational Information (in order of complexity) • • • • Descriptive (Entities) Organizational: Structure and Relationships (Relationships) Authoritative: Authorization, Authority and Delegation (Entities and Relationships) Workflow (Complex interaction of Entities based on Relationships and Process) Entities Being Organized This list is an attempt at enumerating the different types of things being organized or that need to be organized in University systems, for the purpose of understanding the scope of organizational structures. The list is not comprehensive. • • • • • • • • • • • • Budget Employee Appointment Employee Pay Distribution Student Major Curriculum Degree Major Purchase Order Appointee (Person) Student (Person) Alumni (Person) Donor (Person) Patient (Person) 7 • • • • • • • • • • • • • • • • • • • • • • • • • User (Computer Systems) Financial Account Project (PCA) Physical Location Event (Transaction) Space Building Room Fund (Financial) Financial Account (Asset, Liability, Revenue, Expense, Balance) Organization Date (Calendar) Grant Gift Vendor Contract Task / Work Request Endowment Degree Conferred Physical Asset Expense Group Domain Name Network Workstation Organizational Activities The table below lists a number of ways in which different Entities above are organized, using different organizational structures. This can be used an informational foundation for determining requirements for systems-based organizational structures. Activity Type Organizational, Reporting Relationships Descriptive Unit Organization Organizational Example APS Organization Charts UW Handbook Organization of Schools and Colleges Organizational structure in UW Technology or Financial and Facilities Org Code as Span of Control mechanism in ASTRA Describing space SIMS 8 Service Units, Lines of Organizational Service Unit Name Change Descriptive Authoritative, Span of Control Organizational Descriptive, Location Organizational Space Management Authoritative, Approval and Review Communication Budget Authority Group Provisioning Interdisciplinary Relationships Roles Authorization Delegation Communications Organizational Workflow, Authoritative Organizational Authoritative Organizational, Authoritative Organizational Descriptive, Organizational, Authoritative Authoritative, Organizational Workflow, Organizational Workflow, Organizational Descriptive, Organizational EGC1, Paid Professional Work Bulk Email, groups provisioning (SharePoint, Outlook, Catalyst, departmental) Grant Award Budget Automated Groups in Groups Directory Service Connections between Academic Units and Degree Programs for Grad School Granting both authorization and responsibility based on roles Delegator in ASTRA Delegation of work requests in Facilities Communications to groups of people based on relationship with university. Domain Name System / Windows Domain Networking Financial Organization Application Matrix The "Financial Organization Application Matrix" below describes the applications of the Financial Organization in enterprise systems, and is an important component of future change management implementations. The Matrix includes the following columns regarding the implementations and uses of the Financial Organization: • • • • • • • System: system using Org Data Entity: what is being organized Relationship: how things are organized Type: reason for organizing Implementation: how this is done or built Change Management: how it is changed or updated Data Generation: how far the data is to the source system 9 System (system using Org Data) Entity Relationship Type (what is being organized) (how are things being (what is the reason for organized) organizing) Implementation (how is it done or built) Org Code Change Management (how is it changed or updated) Data Generation (1st = System of Record) ASTRA EIS (Equipment Inventory System) Authorizations for people ASTRA uses and processes. Restricted organization code for by application an authority limiter (privs)/role/action/SOC. or value. Budgets are SOC (or limit) types are: subordinate to Budget, Org Code, orgs. ASTRA applies PUC, Facility Number the hierarchical (FacNum), Curriculum relationship of Code, Major, etc. budgets:orgs and suborgs w/in orgs. People's authority can be defined/limited by org. ALSO, Org (or PUC-group = 3120000) also is used to identify all the PUCs within UWMedCtrs Insurable items (mostly Financial accounts equipment), budgets, (budget, PCA), List of custodians (responsible items, financial risk?, parties) annual billing. Authorization, path of delegation, span-ofcontrol ASTRA queries an EDW- Differences (adds/deletes) to 2nd generation: ODS-org table and stores a the org & budget lists create ASTRA consumes copy of the org list and of updates to ASTRA org and from ODS which the budget list, refreshed budget tables. receives nightly daily (nightly) updates from FIN/FAS source system. Financial, authoritative eProcurement (same as Ariba info) eProcurement purchases Financial accounts Financial, workflow, (account-codes; authoritative expenditure, budget, PCA), Review & Approval (by bgt, acct code, org & $-amt, commodity, catalog), EIS database has a table The EIS fin_budget_num 1st Generation: (direct feed/update called "fin_budget_num" table is refreshed every that contains the Org Code night by a download process from FIN via AIX to EISdb) value. EIS then extracts that gets the data directly all the budgets that belong from the mainframe FIN to the Org Code for use in database and loads them to the EIS application. the fin_budget_num table in the EIS database. Ariba stores its own copy Ariba creates org and 3rd Generation: of findb tables including budget-approvals based on local Ariba copy of organization, budget, the lastest information from SQL databridge copy of FIN Org account code, and FIN ASTRA, but does not reindex global evaluate existing req approval graphs to reflect changes to org or budget- 10 Job-Assignment (shops) FS-WORKS Requests for Facilities (facilities work Services work requests) (maintenance, construction, custodial, engineering) Financial accounts Financial, workflow, (budget, PCA, orgs), authoritative, eventual Review & Approval billing process (by budget, org & FacNum/bldg), Work Assignment to shops/svc providers based approval roles in ASTRA, unless an edit to the Ariba requisition causes the approval graph to be redrawn. Ariba generally picks up the org associated with a budget at a given point in time, and does not react to a budget moving to a different org, or changes to org codes themselves. When a req is copied as the basis of a new requisition, the point-in-time budget/org relationships that existed on the old requisition are copied to the new req FS-WORKS gets its Org Changes in Org code will be 2nd Generation: Code data from the FME reflected in the daily The current process database. That database is interface. has the DBA team updated nightly with delivering the three information from the tables (see column budget_no_index, four) via the organization_tl, and databridge process. global_info tables from the Starting with the FIN system. Currently new biennium, FSthis data is delivered to the WORKS will pull FME database server by a this data nightly nightly DBA process. On from siriussql's July 1, 2009, when we findb table. implement the vendor's replacement for FME, we will draw this data nightly from the findb on siriussql. We currently store the first seven digits of the Org Code and we use it to group budgets and to associate users with 11 MyFD (financial and budget reporting) Budget number, time period, account (objectsubobject) SAGE (Grants Management) org code, org name, person Groups Service person, home-deptbudget or appointing- budgets (our user data load includes home department Org Code for each user). With the new application, we will use the full 10 digits of Org Code. Ariba approvals for FSWORKS are based on Facnum, Budget Number, and / or Org Code. If an FS-WORKS request includes a Budget Number, that number and the Org Code to which it belongs is sent to Ariba to generate an approval graph (many/most Budgets and Org Codes have no approvals established for FS-WORKS). Financial summaries Informational, financial MyFD pulls financial data, Not managed in MyFD. 2nd, 3rd, 4th, by object code, status, reconciliation of transactional and financial Org Code-Budget distant cousin twice by budget, then by financial activity, transfer summaries, from the relationships flow from removed? organization code. (budget to budget) of operational data store FIN-FAS to ODS (and there financial transactions (ODS). Additional pulled from MyFD) financial transaction data pulled from FDI table. ASTRA authorizations provide authorizations by budget and organization code. org hierarchy, all organizational, workflow, nightly pull of index from workflow is managed with a 3rd person appt and home descriptive, authoritative ODS:FIN/FAS; live matrix in the system that is dept, lookup of person info in manually updated; person sequnece of system appt and assoc dept/unit are availability to not managed or updated per ODS/PersonReg/HEPPS; each transactional activity Auto-provision organizational perl script; query against Still in development; 6th Gen. reusable groups of AppDev ODS manual, sporadic 12 budget, employee-type (faculty, staff, student) people 1:mainframe, 2:heppsstaging 3:HumanResources 4:ODS 5:AppDev ODS 6:Groups Directory Org Code does not exist in HEPPS; However, Entity references are based on budget numbers, each of which belongs to an Org Code; Org Code is denormalized in the EDW Implementation: Indexes related to entities are added to, and updated in, datasets (tables) in the FIN database using the FIN online system. They are crossed over into new fiscal and biennial periods by batch software programs. Change management: 1st Gen Adds and changes are made to the indexes using the FIN online system. When changes are made to the budget's organization code, the changes are saved and applied weekly to financial detail and summary data. HEPPS (Payroll and Personnel System) Budget Number Person-Organization; Organizational OrganizationOrganization; Payment-Organization FAS/FIN (Financial System) Entities: 1) Budget, 2) Financial Account 3) Project (PCA), 4) Fund (Financial), 5) Organization Relationships: 1. Fund tracks Budget tracks Financial Account 3) Organization tracks Financial Accounts 4) Organization tracks Budget tracks Financial Account 5) Organization tracks Project (PCA) tracks Financial Account Department-Financial Organization Organizational types: 1) Authoritative 2) AuthoritativeOrganizational 3) Descriptive 4) Organizational SDB (Student System) Department, Financial Organization ARIBA eProcurement purchases; Financial accounts eTravel expense reports; (account-codes; Facilities work order expenditure, budget, Financial, workflow, authoritative UWSDB table SR_Dept_Code includes a column (Dept_FAS_Org_Code) to relate department to organization using Org Code. Ariba stores its own copy of findb tables including organization, budget, Per Andrew HummelSchluger in Student Data Management, this field is not actively updated and can not be reliably used. Ariba creates org and 3rd Generation: budget-approvals based on local Ariba copy of the lastest information from SQL databridge 13 requests PCA), Review & Approval (by bgt, acct code, org & $-amt, commodity, catalog), Job-Assignment (shops) account code, and FIN global copy of FIN Org ASTRA, but does not reindex evaluate existing req approval graphs to reflect changes to org or budgetbased approval roles in ASTRA, unless an edit to the Ariba requisition causes the approval graph to be redrawn. Ariba generally picks up the org associated with a budget at a given point in time, and does not react to a budget moving to a different org, or changes to org codes themselves. When a req is copied as the basis of a new requisition, the point-in-time budget/org relationships that existed on the old requisition are copied to the new req JPMorgan/Chase budget, org-code, acctcodes (obj-sobj), PCAcodes OASIS Physical Assets Organization financial, workflow EDW - ODS AuthoritativeOrganizational, Descriptive, Organizational Budget Activity Detail Financial Financial, (FAS Financial Organization-Budget informational transactions), Index Budget Index (Budgets), College DivisionFinancial Organization Budget Index (by Orgcode), Financial Fund, OrganizationProgram Category, Budget Activity College Division, Detail Person, Budget Index-Budget Person Organization (by Activity Detail Organization tracks Assets Organization code index is Org code change is handled 2nd Generation ftp'd to OASIS and is manually derived from the FINDB sql data warehouse The EDW - ODS Org Code in ODS is a 2nd Generation (Operational Data Store) subset of the data stored in has two topic areas, EDW "Staging" database. Finance and Human ODS only carries "current" Resources. These are org code data. That is, if an designed primarily as two org code does not exist in separate subject FAS anymore (Get deleted submodels but they do or modifed), the data would both share relationships to not be loaded into ODS. Organizations (Financial See note below for data and Person Organizations change rules. 14 OrgCode), Person Unit Fund-Budget Activity Detail Program CategoryBudgetActivityDetail PersonBudgetActivityDetail Person-Budget Index Person-Person Org Code Person-Person Unit Code respectively), and to Budget Index and Budget Activity Detail (financial transactions from FAS, which may or may not be related to individual Persons.) More than 30 different FAS transaction types (trancodes) are tracked in the ODS, including financial index transactions, budget transactions, actual transactions, and project cost accounting transactions. Descriptive, Organizational There is no change made to Second Generation org code data in EDW or ODS. Org Code data is sourced from FAS. As an org code data gets changed in FAS, the changed data is identified by a process in EDW called "Diff", and the changed org code and its attributes are loaded into the EDW "staging" databse, and the data gets time-stamped. These data (before and after changes) are the "raw" data to build an org code data change history. EDW - General (See HEPPS, FAS/FIN, SDB, and eProcurement/PAS; the EDW copies these source systems nearly literally on a daily basis, retaining budget and organizationrelated data values if and where they are captured in the mainframe source data. History is retained and daily time-variant changes are captured in a permanent staging area archive, e.g. to reflect associations of budgets to organizations "as of" a prior date. The staging area is not designed to support direct end-user queries. Also see "EDW - ODW"; FAS transactions and HEPPS data are also 15 BGT copied into an EDW "Operational Data Store" (ODS) daily where both involve relationships to organizations and budgets (see relationships detailed under "EDW ODS"). The ODS is designed to be directly accessible for departmental user/application queries, and to serve as a data source for financial summaries and the financial destop. 1) Budget Organization tracks 2) Organization Budget Affirmative Action Plan Person, Organization 1) Authoritative 2) AuthoritativeOrganizational 3) Descriptive 4) Organizational Person-Organization, Organizational OrganizationOrganization Same as FIN-FAS above, Same as FIN-FAS above. BGT uses FIN budget and BGT uses FIN budget and organization code index. organization code index. 2nd Gen • • • • Appointees in the Affirmative Action plan are reported by Org Code A shadow EOO Org Code is maintained A relationship between the EOO Org Code and the Org Code is maintained by hand Units are aggregated and reported using • • • EOO Org Code is Fourth Generation; updated as necessary by hand • 1st - FAS Relationships • 2nd between Org Code EDW and EOO Org Code • 3rd must be updated by PnBDB hand • 4th - EOO Simple basic query Datamart based audit to determine if Org Codes are missing 16 EOO Org Code AHRDB Person, Organization Person-Organization, Organizational, OrganizationDescriptive, Authoritative Organization • • People are appointed into a unit Units are organized with relationships amongst one another Units are connected to the Org Code A shadow copy of the org code is maintained by hand The OPB Oracle database collects and integrates quarterly snapshots of data from many sources and topic areas across campus, including financial data, academic data, and human resources data. The financial tables area of the OPB database includes the Budget Index as a central table, related to many • • Shadow org code 3rd Generation must be updated by hand • 1st - FAS Connections • 2nd between Units and EDW Org Code must be • 3rd updated by hand AHRDB • • OPB (a/k/a PNBDB) Office of Planning and Budgeting Organization, Budget Index Orgcode-Budget Index • • 17 categorizing code entities, including Org Code. SIMS (Space Information Management) System A Room A room is assigned to an organization. Organization code is used to indicated to whom the room is assigned. A room is also used by an organization. The using organization may or may not be the same as the organization assigned the room. Organization code is used to indicate the room user. Management of space and By departmental and the analysis of use and college staff upon change need for space. in space assignment or use. Emergency response Organizational coding is a shared data resource with the OPB (PNBDB) database. SIMS is the system of record for space assignment and use. PAS (Purchasing System) Requisition entry is permitted if the user has permission to access the budget number. Purchases, (expenditures &encumbrances) are dependent upon the requisition. User permissions are Permissions, workflow, obtained from TSAT authorization / approval and compared to the org-code on the budget DORA (Human person; org name Subjects Database) hierarchical PAS obtains the ORGCODE from the budget 02 index of the FIN database. The org-code associated with PAS transactions is stored as a field in various PAS datasets. For example, a modified version of the org-code (with 000 in levels 5 & 6 is stored in the PAS REQBGT-DETAIL. Descriptive; authoritative; at system inception, a organizational download of orgs was heavily edited to be used to prepopulate organizational unit table, which also includes non- Org-code changes for budgets are made via the FIN driver, and at least one of the org-code fields in PAS datasets is updated weekly via a PAS program which runs in a FAS jobstream. 1st = FIN database (budget-index) 2nd = PAS datasets.(org-code stored as a field) manual updates only when 3rd prompted by transactional submissions of new unit. 18 eFEC (Electronic Faculty Effort Certification) Person-Org Org Phase I - No control Phase II - Authoritative Phase II - ASTRA Phase III - Workflow Span of Control - Org Code to Home Department Phase III - Org Based Workflow UW entities from other insitutions under our CoOp agreements of mutual review acceptance. Phase II - ASTRA Org Code SoC crossed to 7 digit org from Home Department Budget Number (as quieried from SAGE - Appointee Service); Phase II - Orgs 304, 308, 310 do semi-annual reporting. Org used as a flag Endowmentment database contains two budget numbers, the Principal Budget (identifies the Endowment) and the Operation Budget (out of which funds are dispersed). These are always different, and may belong to different Orgs. Each budget belongs to an Org Code, from which endowments may be reported. ADVANCE tracks a comprehensive list of all degrees historically granted by the UW (SDB has a subset); ADVANCE tracks relationships with people in multiple roles for the purposes of Org Code Data and Home At least 4th Department Budget number generation (pulls downloaded nightly from data from SAGE) SAGE; Semi-annual org list maintained manually Endowments (Treasury) EndowmentsOrganization Organizational Authoritative Budget Number to Org Code mapping is pulled directly from 02 Index; Budget numbers stored in Access database Second Generation ADVANCE Person (Endowments, Person-Org (by PUC) Alumni, Donors) Alumni Alumni-Degree Budget-Org Organizational Organizaitonal, Descriptive Pulls data direclty from FIN/FAS, SDB. HEPPS data pulled and then reconsiled. Second Generation (generally pulls from mainframe) 19 communication (email, newsletters, etc); ADVANCE also attempts to track multiple relationships a person may have with the university over time. 20 Technical Specifications for Master Data Application Overview The purpose of the Master Data Application is to provide a mechanism that allows data about organizational structures and relationships between organizational structures to be managed and used, both by central administration units for enterprise needs, as well as by departments for their own localized needs. Architecture • • • The proposed solution includes three different data tables instead of one: o The Organization Unit table stores information about each individual unit, regardless of its relationship to other units. Each entry in this table includes a surrogate primary key, which is the unique identifier for an entry in the table. This identifier exists unchanged for the life of the unit. o The Organization Tree table stores information about the relationships between Organization Units. This differs from the current Organization Code, where both the Units and the relationships are represented with a single code. o The Organization table stores information about the context of the relationship; for example, HR Reporting vs. Budgetary Roll-up. In the current code, there is no way to differentiate between uses of the organization code for different reasons. Note that the proposed architecture can be generalized to represent relationships between any entity (any thing) of interest to the university: o Organization Unit would be renamed Entity; entity could represent a unit, a person, a budget, a physical asset, or any number of things; generally there would be a one-to-one relationship between an Entity and a universal identifier, such as a UUID (universally unique identifier), as used in an identity management system. o Organization Tree would be renamed to Entity-Relationship, and could represent any number of relationships between any two entities, such as A is part of B or A reports to B or A belongs to B or A is related to B o Organization would be renamed to Context. The idea here is that Entities can exist, and have different relationships between other Entities, depending on the context. Examples include HR Reporting Relationships, Budgetary Responsibility, Financial Reporting, or even a custom routing procedure for a specific process. Each of these tables can be independently time-variant to record changes to the organization and relationships over time. The time dimension of these tables can be very granular (using a time dimension of 1 second); which can then be easily related to less granular time dimensions such as day, pay cycle, month, year, fiscal year, academic quarter and biennium as needed. 21 • • • The system should facilitate the concept of ownership, and its use should be distributed in nature. o Each of the entries in the three tables should refer to an entity that is the "owner" of that Entity, Entity-Relationship, or Context. o A core set of Entities, Entity-Relationships, and Contexts should be managed centrally as official Organizational Structures. In addition, to facilitate the organizational needs of individual units, units should be able to manage their own lists of Entities, Entity-Relationships, and Contexts, and connect these entries to centrally managed entries. o This concept of distributed ownership of data in a centrally managed system would allow every organizational unit to leverage central organizational structures, without the need to develop and maintain their own systems. Structures that currently exist in legacy systems can be designed to "point to" records in the improved organization structure or vice versa. The proposed architecture is currently implemented in the production environment for the Academic Human Resources database; it allows future flexibility while retaining the ability to work with the existing Organizational Code structure. Benefits • • • • • • Allows each Entity/Unit/Person to be represented by a surrogate key that does not need to change during reorganization. Allows Entity/Unit/Persons to be reused in different contexts. Allows flexibility in creating levels of the organization as necessary. Facilitates tracking changes to organizations and relationships between organizations over time. Facilitates connecting existing organizational structures to workflows. Allows every organizational unit to leverage central organizational structures, without the need to develop and maintain their own systems. 22 Diagram 23 Team Membership • • • • • • • • • • • • • Heidi Berrysmith Sr. Computer Specialist, Identity & Access Mgmt, UW Technology Baokai Chen Sr. App. Sys. Engr., Decision Support Services, Office of Information Management Nancy Corning Director, Finance and Admin, UW Bothell John Drew Director, Computing and Information, Graduate School Rachel Gatlin Sr. Data Analyst, Human Resources Information Systems Phil Hoffman Director, Institutional Studies, Planning and Budgeting Jim Kresl Director, Office of Research Information Services Tim McAllister Technology Manager, Financial Systems, Office of Information Management Nancy McDonald Director, Admin and Finance, School of Medicine Nancy McQuillen Information Architect, Decision Support Services, Office of Information Management Jeani Wells Business Analyst/Data Architect, Office of Information Management Erick Winger Data Analyst/Project Manager, Financial Management Brandon Whitehead, committee chair Director, Academic Personnel Information Services 24