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 10-
digit 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 Example
Organizational,
Reporting Relationships APS Organization Charts
Descriptive
UW Handbook Organization of Schools and
Unit Organization Organizational
Colleges
Service Units, Lines of Organizational structure in UW Technology or
Organizational
Service Financial and Facilities
Unit Name Change Descriptive
Authoritative, Org Code as Span of Control mechanism in
Span of Control
Organizational ASTRA
Descriptive,
Location Describing space
Organizational
Space Management Authoritative, SIMS
8
Organizational
Workflow,
Approval and Review EGC1, Paid Professional Work
Authoritative
Bulk Email, groups provisioning (SharePoint,
Communication Organizational
Outlook, Catalyst, departmental)
Budget Authority Authoritative Grant Award Budget
Organizational,
Group Provisioning Automated Groups in Groups Directory Service
Authoritative
Interdisciplinary Connections between Academic Units and
Organizational
Relationships Degree Programs for Grad School
Descriptive,
Granting both authorization and responsibility
Roles Organizational,
based on roles
Authoritative
Authoritative,
Authorization Delegator in ASTRA
Organizational
Workflow,
Delegation Delegation of work requests in Facilities
Organizational
Workflow, Communications to groups of people based on
Communications
Organizational relationship with university.
Descriptive,
Networking Domain Name System / Windows Domain
Organizational
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 Entity Relationship Type Implementation Org Code Change Data Generation
(system using (what is being organized) (how are things being (what is the reason for (how is it done or built) Management (1st = System of
Org Data) organized) organizing) (how is it changed or Record)
updated)
ASTRA Authorizations for people ASTRA uses Authorization, path of ASTRA queries an EDW- Differences (adds/deletes) to 2nd generation:
and processes. Restricted organization code for delegation, span-of- ODS-org table and stores a the org & budget lists create ASTRA consumes
by application an authority limiter control copy of the org list and of updates to ASTRA org and from ODS which
(privs)/role/action/SOC. or value. Budgets are the budget list, refreshed budget tables. receives nightly
SOC (or limit) types are: subordinate to daily (nightly) updates from
Budget, Org Code, orgs. ASTRA applies FIN/FAS source
PUC, Facility Number the hierarchical system.
(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
EIS Insurable items (mostly Financial accounts Financial, authoritative EIS database has a table The EIS fin_budget_num 1st Generation:
(Equipment equipment), budgets, (budget, PCA), List of called "fin_budget_num" table is refreshed every (direct feed/update
Inventory custodians (responsible items, financial risk?, that contains the Org Code night by a download process from FIN via AIX
System) parties) annual billing. value. EIS then extracts that gets the data directly to EISdb)
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.
eProcurement eProcurement purchases Financial accounts Financial, workflow, Ariba stores its own copy Ariba creates org and 3rd Generation:
(same as Ariba (account-codes; authoritative of findb tables including budget-approvals based on local Ariba copy of
info) expenditure, budget, organization, budget, the lastest information from SQL databridge
PCA), Review & account code, and FIN ASTRA, but does not re- copy of FIN Org
Approval (by bgt, acct global evaluate existing req index
code, org & $-amt, approval graphs to reflect
commodity, catalog), changes to org or budget-
10
Job-Assignment based approval roles in
(shops) 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 Requests for Facilities Financial accounts Financial, workflow, FS-WORKS gets its Org Changes in Org code will be 2nd Generation:
(facilities work Services work (budget, PCA, orgs), authoritative, eventual Code data from the FME reflected in the daily The current process
requests) (maintenance, Review & Approval billing process database. That database is interface. has the DBA team
construction, custodial, (by budget, org & updated nightly with delivering the three
engineering) FacNum/bldg), Work information from the tables (see column
Assignment to budget_no_index, four) via the
shops/svc providers organization_tl, and databridge process.
global_info tables from the Starting with the
FIN system. Currently new biennium, FS-
this 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
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 FS-
WORKS 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).
MyFD Budget number, time Financial summaries Informational, financial MyFD pulls financial data, Not managed in MyFD. 2nd, 3rd, 4th,
(financial and period, account (object- by object code, status, reconciliation of transactional and financial Org Code-Budget distant cousin twice
budget subobject) by budget, then by financial activity, transfer summaries, from the relationships flow from removed?
reporting) 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.
SAGE org code, org name, org hierarchy, all organizational, workflow, nightly pull of index from workflow is managed with a 3rd
(Grants person person appt and home descriptive, authoritative ODS:FIN/FAS; live matrix in the system that is
Management) 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
Groups Service person, home-dept- Auto-provision organizational perl script; query against Still in development; 6th Gen.
budget or appointing- reusable groups of AppDev ODS manual, sporadic
12
budget, employee-type people 1:mainframe,
(faculty, staff, student) 2:heppsstaging
3:HumanResources
4:ODS
5:AppDev ODS
6:Groups Directory
HEPPS Budget Number Person-Organization; Organizational Org Code does not exist in
(Payroll and Organization- HEPPS; However, Entity
Personnel Organization; references are based on
System) Payment-Organization budget numbers, each of
which belongs to an Org
Code; Org Code is
denormalized in the EDW
FAS/FIN Entities: Relationships: Organizational types: Implementation: Change management: 1st Gen
(Financial 1) Budget, 1. Fund tracks Budget 1) Authoritative Indexes related to entities Adds and changes are made
System) 2) Financial Account tracks Financial 2) Authoritative- are added to, and updated to the indexes using the FIN
3) Project (PCA), Account Organizational in, datasets (tables) in the online system. When
4) Fund (Financial), 3) Organization 3) Descriptive FIN database using the changes are made to the
5) Organization tracks Financial 4) Organizational FIN online system. They budget's organization code,
Accounts are crossed over into new the changes are saved and
4) Organization tracks fiscal and biennial periods applied weekly to financial
Budget tracks by batch software detail and summary data.
Financial Account programs.
5) Organization
tracks Project (PCA)
tracks Financial
Account
SDB Department, Financial Department-Financial UWSDB table Per Andrew Hummel-
(Student Organization Organization SR_Dept_Code includes a Schluger in Student Data
System) column Management, this field is
(Dept_FAS_Org_Code) to not actively updated and can
relate department to not be reliably used.
organization using Org
Code.
ARIBA eProcurement purchases; Financial accounts Financial, workflow, Ariba stores its own copy Ariba creates org and 3rd Generation:
eTravel expense reports; (account-codes; authoritative of findb tables including budget-approvals based on local Ariba copy of
Facilities work order expenditure, budget, organization, budget, the lastest information from SQL databridge
13
requests PCA), Review & account code, and FIN ASTRA, but does not re- copy of FIN Org
Approval (by bgt, acct global evaluate existing req index
code, org & $-amt, approval graphs to reflect
commodity, catalog), changes to org or budget-
Job-Assignment based approval roles in
(shops) 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, acct- financial, workflow
codes (obj-sobj), PCA-
codes
OASIS Physical Assets Organization Authoritative- Organization code index is Org code change is handled 2nd Generation
Organization tracks Assets Organizational, ftp'd to OASIS and is manually
Descriptive, derived from the FINDB
Organizational sql data warehouse
EDW - ODS Budget Activity Detail Financial Financial, The EDW - ODS Org Code in ODS is a 2nd Generation
(FAS Financial Organization-Budget informational (Operational Data Store) subset of the data stored in
transactions), Index has two topic areas, EDW "Staging" database.
Budget Index (Budgets), College Division- Finance and Human ODS only carries "current"
Financial Organization Budget Index Resources. These are org code data. That is, if an
(by Orgcode), Financial designed primarily as two org code does not exist in
Fund, Organization- separate subject FAS anymore (Get deleted
Program Category, Budget Activity submodels but they do or modifed), the data would
College Division, Detail both share relationships to not be loaded into ODS.
Person, Budget Index-Budget Organizations (Financial See note below for data
Person Organization (by Activity Detail and Person Organizations change rules.
14
OrgCode), Fund-Budget Activity respectively), and to
Person Unit Detail Budget Index and Budget
Program Category- Activity Detail (financial
BudgetActivityDetail transactions from FAS,
Person- which may or may not be
BudgetActivityDetail related to individual
Person-Budget Index Persons.) More than 30
Person-Person Org different FAS transaction
Code types (trancodes) are
Person-Person Unit tracked in the ODS,
Code including financial index
transactions, budget
transactions, actual
transactions, and project
cost accounting
transactions.
EDW - General (See HEPPS, FAS/FIN, Descriptive, There is no change made to Second Generation
SDB, and Organizational org code data in EDW or
eProcurement/PAS; the ODS.
EDW copies these source Org Code data is sourced
systems nearly literally from FAS. As an org code
on a daily basis, retaining data gets changed in FAS,
budget and organization- the changed data is
related data values if and identified by a process in
where they are captured EDW called "Diff", and the
in the mainframe source changed org code and its
data. History is retained attributes are loaded into the
and daily time-variant EDW "staging" databse, and
changes are captured in a the data gets time-stamped.
permanent staging area These data (before and after
archive, e.g. to reflect changes) are the "raw" data
associations of budgets to to build an org code data
organizations "as of" a change history.
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
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.
BGT 1) Budget Organization tracks Same as FIN-FAS above, Same as FIN-FAS above. 2nd Gen
2) Organization Budget 1) Authoritative BGT uses FIN budget and BGT uses FIN budget and
2) Authoritative- organization code index. organization code index.
Organizational
3) Descriptive
4) Organizational
Affirmative Person, Organization Person-Organization, Organizational • Appointees in the • EOO Org Code is Fourth Generation;
Action Plan Organization- Affirmative updated as
Organization Action plan are necessary by hand • 1st - FAS
reported by Org • Relationships • 2nd -
Code between Org Code EDW
• A shadow EOO and EOO Org Code • 3rd -
Org Code is must be updated by PnBDB
maintained hand • 4th - EOO
• A relationship • Simple basic query Datamart
between the EOO based audit to
Org Code and the determine if Org
Org Code is Codes are missing
maintained by
hand
• Units are
aggregated and
reported using
16
EOO Org Code
AHRDB Person, Person-Organization, Organizational, • People are • Shadow org code 3rd Generation
Organization Organization- Descriptive, Authoritative appointed into a must be updated by
Organization unit hand • 1st - FAS
• Units are • Connections • 2nd -
organized with between Units and EDW
relationships Org Code must be • 3rd -
amongst one updated by hand AHRDB
another
• Units are
connected to the
Org Code
• A shadow copy
of the org code is
maintained by
hand
OPB (a/k/a Organization, Budget Orgcode-Budget • The OPB Oracle
PNBDB) Index Index database collects
Office of and integrates
Planning and quarterly
Budgeting 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
17
categorizing code
entities, including
Org Code.
SIMS A Room A room is assigned to Management of space and By departmental and Organizational coding is a SIMS is the system
(Space an organization. the analysis of use and college staff upon change shared data resource with of record for space
Information Organization code is need for space. in space assignment or the OPB (PNBDB) assignment and
Management) used to indicated to use. database. use.
System whom the room is Emergency response
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.
PAS Requisition entry is User permissions are Permissions, workflow, PAS obtains the ORG- Org-code changes for 1st = FIN database
(Purchasing permitted if the user has obtained from TSAT authorization / approval CODE from the budget 02 budgets are made via the (budget-index) 2nd
System) permission to access the and compared to the index of the FIN database. FIN driver, and at least one = PAS
budget number. org-code on the The org-code associated of the org-code fields in datasets.(org-code
Purchases, (expenditures budget with PAS transactions is PAS datasets is updated stored as a field)
&encumbrances) are stored as a field in various weekly via a PAS program
dependent upon the PAS datasets. For which runs in a FAS
requisition. example, a modified jobstream.
version of the org-code
(with 000 in levels 5 & 6
is stored in the PAS REQ-
BGT-DETAIL.
DORA (Human person; org name hierarchical Descriptive; authoritative; at system inception, a manual updates only when 3rd
Subjects organizational download of orgs was prompted by transactional
Database) heavily edited to be used submissions of new unit.
to prepopulate
organizational unit table,
which also includes non-
18
UW entities from other
insitutions under our Co-
Op agreements of mutual
review acceptance.
eFEC Person-Org Phase I - No control Phase II - Authoritative Phase II - ASTRA Org Org Code Data and Home At least 4th
(Electronic Org Phase II - ASTRA Phase III - Workflow Code SoC crossed to 7 Department Budget number generation (pulls
Faculty Effort Span of Control - Org digit org from Home downloaded nightly from data from SAGE)
Certification) Code to Home Department Budget SAGE; Semi-annual org list
Department Number (as quieried from maintained manually
Phase III - Org Based SAGE - Appointee
Workflow Service);
Phase II - Orgs 304, 308,
310 do semi-annual
reporting. Org used as a
flag
Endowments Endowments- Organizational Authoritative Endowmentment database Budget Number to Org Second Generation
(Treasury) Organization contains two budget Code mapping is pulled
numbers, the Principal directly from 02 Index;
Budget (identifies the Budget numbers stored in
Endowment) and the Access database
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 Person Organizational Organizaitonal, ADVANCE tracks a Pulls data direclty from Second Generation
(Endowments, Person-Org (by PUC) Descriptive comprehensive list of all FIN/FAS, SDB. HEPPS (generally pulls
Alumni, Donors) Alumni degrees historically data pulled and then from mainframe)
Alumni-Degree granted by the UW (SDB reconsiled.
Budget-Org has a subset); ADVANCE
tracks relationships with
people in multiple roles
for the purposes of
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