HomeMy WebLinkAboutResolutions - 1996.10.10 - 24885• A
..September 26., 1996
MISCELLANEOUS RESOLUTION #96222
BY: STRATEGIC PLANNING COMMITTEE, DENN:S POWERS, CHAIRPERSON
IN RE: DEPARTMENT OF INFORMATION TECHNOLOGY - IMPLEMENTATION OF A DATA
WAREHOUSE PILOT PROJECT
TO THE OAKLAND COUNTY BOARD OF COMMISSIONERS
Chairperson, Ladies and Gentlemen:
WHEREAS Department of Information Technology has identified a need for
managing selected data into a standard directory system for ease of access by
County employees; and
WHEREAS the purpose of organizing selected data is to enable end users
in a personal computer environment a means of gathering their own decision
making information from a vast storehouse of data with minimal assistance from
technical staff; and
WHEREAS the best way to achieve these objectives is by taking advantage
of specialized data warehouse methodologies and software tools available; and
WHEREAS Information Technology has identified Prism Solutions, Inc. to
be the most cost beneficial and knowledgeable in data warehouse planning and
training; and
WHEREAS the estimated cost to employ front-end consultants to quick-
start the correct design methodology is a total of $75,000; and
WHEREAS there are available funds in Information Technology Fund Equity
to cover the cost of the consulting by Prism Solutions, Inc.
NOW THEREFORE BE IT RESOLVED that the Oakland County Board of
Commissioners approves the Data Warehouse Pilot Project planning effort.
Chairperson, on behalf of the Strategic Planning Committee, I move the
adoption of the foregoing resolution.
STRATEGIC PLANNING COMMITTEE
GP,e/YvYvtn /-11 eakmet.0
OAKLAND COUNTY
DATA WAREHOUSE
PROPOSAL
Prepared by the following contributing departments:
Management and Budget
Department of Information Technology
Community and Economic Development
September 10, 1996
Table of Contents
SECTION PAGE
Objectives 3
Scope 3
Problem 4
Alternative Solutions 4
Recommended Solution 5'
Estimated Costs 5
Benefits 5
Conclusion 6
Note: Appendices A-D are on file in the Strategic Planning
Committee's Master File.
Objectives
The primary objective of implementing a data warehouse is to organize selected data into
a standard directory system for ease of access by County employees. The purpose of doing this
is to empower end users with the means of g_leaning, their own decision making information from
a vast storehouse of data with minimal assistance from technical staff.
Other specific objectives are listed as follows:
• Set the stage for doing Geographic Information Systems analysis by linking parcel
data from the mainframe with electronic base map data
• Leverage the County's investment in desktop computers by providing
personalized access to all types of data
• Provide a staging area for enhanced access to data by both public and private
sectors
The best way to achieve these objectives is by taking advantage of the specialized data
warehouse methodologies and software tools available today. These methods and tools will
allow Oakland County to pool data by common subject area, integrate it together regardless of
the number of sources, and add the element of time to it for historical analysis purposes. About
28% of organizations in the U.S. either currently use a data warehouse or plan to implement one
in the next three years. See Appendix-A for a detailed White Paper on Data Warehousing.
SOX
To be successful with a county-wide data warehouse program, it will be important to
demonstrate early, incremental progress. For this reason we want to employ front-end consulting
to quick-start the correct design methodology and we want to focus on a four month pilot project.
Our Data Warehouse Request for Proposal (RFP) was limited to selecting a consultant for
the following three deliverables:
• A High Level Business Plan Report to establish direction and priorities for a data
warehouse environment.
• A Readiness Assessment Report to assess the County's technological and
organizational readiness for implementing a data warehouse environment.
• A Pilot Project Plan Report to indicate a detailed, four month implementation
plan for the parcel subject area (limited to Land, Sales Analysis, and Master
3
Appraisal data files). Selected Census and MESC data are also of special interest.
Included in the above consulting will be a technology transfer to make certain that
Oakland County staff are trained in the needs assessment. analysis and design methodology.
Business area management and key end users will also be extensively involved throughout (see
Appendix-B).
Problem
Oakland County has some 90 Gigabytes (billions) of operational data on its mainframe
alone. The potential for tapping this vast reservoir of data can only be realized if the data is in an
accessible format and is easily understood by the knowledge worker. Thus, data warehouse.
Mainframe data is stored at the Oakland County Department of Information Technology
in the Virtual Storage Access Method (VSAM) and the Integrated Data Management System
(IDMS) file formats. Each file or data base can consist of multiple record types and complex
relationships that are difficult for most end users to understand. Adding to this difficulty is the
fact that user friendly, MS Windows software tools cannot be used to access the mainframe data.
Opportunities for unlocking the information potential of County data are many times lost
because business knowledge workers are not empowered to search the data for answers without
the help of a technician.
Alternative Solutions
RFP responses were received from five consulting vendors:
• PRISM Solutions
• IBM
• Oracle
• Computer Consultants of America
• KPMG Peat Marwick
The RFP responses were scored by Information Technology personnel as well as the Data
Warehouse User Committee members (Marlys Vickers, Lisa Hobart, Phil Bertolini). Refer to
Appendix-C for the scoring detail.
4
Recommended Solution
The Data Warehouse RFP scoring committees unanimously selected PRISM Solutions,
Inc. as the top choice for moving, forward with the initial consulting deliverables. PRISM is the
recognized leader in this specialized consulting area. Their project methodology and software
tools are highly regarded in the industry.
IBM was also a high scorer in the RFP review because of their strength in the
networking, systems integration and system construction areas. They also have experience with
large government data warehouse projects. For these reasons IBM will be included in the
consulting effort on a no-charge basis to offer technical advise and to learn the PRISM
methodology. They may also become involved in the pilot project implementation effort to
follow.
Estimated Costs
The following costs are outlined for the PRISM consulting engagement:
• High Level Business Plan Report $25,520
• Readiness Assessment Report $20,240
• Pilot Project Plan Report $22,000
• Contingency $7,240
Total Estimated Cost: $75,000
Note: We will make every effort to trim the above costs via the use of Oakland County
team members.
Benefits
The real value of a data warehouse lies in its ability to plug revenue leaks and open new
revenue streams through enhanced management decision making and access to data. This is
made possible because data of all types (tabular, GIS, imaging, multi-media) is placed at the
finger-tips of the people that know the business - the knowledge workers or end users.
The Equalization Division and the Development and Planning Division have both
5
identified specific areas of benefit in the following categories:
• Enhanced efficiency within the division
• Enhanced availability of data to other County agencies
• Enhanced marketability of data
Justification for moving forward with the proposed consulting and the ensuing pilot
project is provided in detail in Appendix-D. If a common link can be implemented between
Community and Economic Development data and that of other County departments, then the
possible expansion of queries to include taxes paid, special assessment districts, demographic
data, census information and such would be limited only to the imagihation of the business
knowledge worker. Linking such data with the GIS would exponentially increase the County's
ability to recognize trends and manage resources. Departmental time would be spent more
efficiently. There would result an improvement in the uniformity and overall quality of property
appraisals. The accumulation of quality data, in regard to succeeding years, would continue to
add to the viability of the warehoused data. Linking personal property to real property and
assigning business codes which are more specific to the actual use of a property would increase
the ability to group properties for study and analysis.
Conclusion
The contributing departments recommend that Oakland County proceed with the
proposed data warehouse consulting as soon as necessary approvals and funding are in place.
It should be emphasized that the recommended consulting will provide planning and
training deliverables only. The actual implementation of the resulting pilot project plan will
happen in a second phase. For this reason, we will come back to the Board of Commissioners
with a presentation of the planning deliverables and an estimate for implementation costs at the
end of the consulting engagement.
djk/wpdw/proposal
6
DATA WAREHOUSE APPLICATIONS
COMMUNITY & ECONOMIC DEVELOPMENT DEPARTMENT
JULY 1996
EQUALIZATION DIVISION OBJECTIVES
QUERY: Key = Parcel ID
I. ENHANCE EFFICIENCY WITHLN THE DIVISION
To complete 4035a (required by State if CV is changed after initial Assessment Roll is set prior
to March Board of Review) must have data from sources #1 and #2; Prior Year SEV and TV,
current year Losses and Additions and Adjustments, VCM or CPI, and Current Year Assessed.
A query incorporating calculations and changes could produce the 4035a or at least give them all
the data they need to enter into a PC program format to produce the form.
Compilation of data to produce sale grids and supporting information to defend Tax Tribunal
appeals. Currently all research, compilation, and all calculations are done by hand which is time
consuming and inefficient. Appeals could be better defended by incorporating a broader data
base when preparing appraisals to defend our values.
Economic studies to set Economic Condition Factors for a unit are currently done by hand-
loading the information by neighborhood to a PC program and applying the projected E.C.F. to
Master Appraisal only to review the results and often change over and over until the overall
'move' is achieved. The data from the current year could be queried to a program where it could
be sorted, subclasses, individually manipulated on a PC based program and adjusted to project
any number of factors which would produce a better resulting balance, with less manpower, and
reach the end result with fewer rewrites.
Data relevant to specific valuations and appraisals could be queried resulting in a saving of
mainframe print time and paper. Appeals and Personal Property sections could utilize this
technology to analyze and store confidential information if a secure environment existed.
Data could be overlaid on the base GIS maps to produce maps for specific purposes; sales of
all ranches within a city against neighborhood areas and age could assist the appraiser in
developing neighborhood areas which are true representations of the parcels which they contain.
(See also II, III)
ID all properties on parcels with specific characteristics such as a certain acreage or square foot
size, which are vacant (no buildings), and analyze future path of development within a township.
(See also II, III)
Specific Personal Property applications may interrelate with previously mentioned applications
as well as interests from other divisions and departments. Development of an Establishment
Code Report for types of businesses would facilitate uniformity between like businesses. The
ability to sort property by Classification (Industrial, Commercial, Utility, etc.), alphabetically, or
by code, would assist in the preparation of Equalization Studies, entice development, and
pinpoint specific product audiences. (See also II)
Top Taxpayers in a unit and other key topics related to available data (from many sources) could
be obtained and queries manipulated to produce a number of reports and listings useful to
analysis by Equalization. Standards already prepares many informational listings and reports but
must enter and manipulate all data by hand with the assistance of PC programs. (See also II)
I. ENHANCE EFFICIENCY WITHIN THE DIVISION (cont.)
Data from #1 could form a query which would create the base studies for Equalization - by class
of property and with the total value and number of properties within the class immediately
available - the selected samples could be withdrawn and compared to sales. Subclasses could be
broken out of the master group. Adjustments could be experimented with to produce the most
equitable way to achieve a value adjustment.
Data could be overlaid on the base GIS maps to produce maps for specific purposes; sales of
all retail businesses within a city against sales expressed as building value per square foot
could assist in subdividing studies and recognizing areas that might be above or below the
general study indicators.
Permit separate study of TIFA, DDA, IFT and CFT properties without lengthy study to ID and
accumulate data.
Linking each personal property ID to the related real property ID and assigning business codes
which are more specific to the actual use of a property would increase our ability to group
properties for study. (See also II)
II. ENHANCE THE AVAILABILITY OF DATA TO OTHER COUNTY
AGENCIES.
Data could be overlaid on the base GIS maps to produce maps for specific purposes; sales of
all ranches within a city against neighborhood areas and age could assist the appraiser in
developing neighborhood areas which are true representations of the parcels which they contain.
(See also I, III)
ID all properties on parcels with specific characteristics such as a certain acreage or square foot
size, which are vacant (no buildings), and analyze future path of development within a township.
(See also I, III)
Specific Personal Property applications may interrelate with previously mentioned applications
as well as interests from other divisions and departments. Development of an Establishment
Code Report for types of businesses would facilitate uniformity between like businesses. The
ability to sort property by Classification (Industrial, Commercial, Utility, etc.), alphabetically, or
by code, would assist in the preparation of Equalization Studies, entice development, and
pinpoint specific product audiences. (See also I)
Top Taxpayers in a unit and other key topics related to available data (from many sources) could
be obtained and queries manipulated to produce a number of reports and listings useful to
analysis by Equalization. Standards already prepares many informational listings and reports but
must enter and manipulate all data by hand with the assistance of PC programs. (See also I)
• H. ENHANCE THE AVAILABILITY OF DATA TO OTHER COUNTY •
AGENCIES (cont.)
Top Taxpayers in a unit and other key topics related to available data (from many sources) could
be obtained and queries manipulated to produce a number of reports and listings useful to
analysis by other users - both within the realm of county government and also to outside
persons leading community and business activities and the general public. Users could form
their own query to suit data to specific needs such as relators using data from Master Appraisal
(source #4) and Land File (source #3) and Sales (source #6) to find 'hot' sale areas and the best-
selling home styles and amenities within that area. Reciprocity of information, such as our
structure and land data and multi-list sale data might be possible to the mutual benefit of all.
(See also III)
Linking each personal property ID to the related real property ID and assigning business codes
which are more specific to the actual use of a property would increase our ability to group
properties for study. (See also I)
III. ENHANCE MARKETABILITY OF DATA
Data could be overlaid on the base GIS maps to produce maps for specific purposes; sales of
all ranches within a city against neighborhood areas and age could assist the appraiser in
developing neighborhood areas which are true representations of the parcels which they contain.
(See also I, II)
ID all properties on parcels with specific characteristics such as a certain acreage or square foot
size, which are vacant (no buildings), and analyze future path of development within a township.
(See also I, II)
Top Taxpayers in a unit and other key topics related to available data (from many sources) could
be obtained and queries manipulated to produce a number of reports and listings useful to
analysis by other users - both within the realm of county government and also to outside
persons leading community and business activities and the general public. Users could form
their own query to suit data to specific needs such as relators using data from Master Appraisal
(source #4) and Land File (source #3) and Sales (source #6) to find 'hot' sale areas and the best-
selling home styles and amenities within that area. Reciprocity of information, such as our
structure and land data and multi-list sale data might be possible to the mutual benefit of all.
(See also II)
Totals and subtotals of values, such as all value within a specific school district, either taxable or
state equalized, could be very useful to bond companies and the school districts themselves.
Possibility of charging minor use fees, depending on how extensive the query, which would
generate good income due to the volume of use such a system would receive when the
availability of such data becomes general knowledge.
• III. ENHANCE MARKETABILITY OF DATA (cont.)
General business operations who could draw upon such information as would be available in the
warehouse should be able to better determine how to serve their client base, maintain an efficient
and productive workforce, and grow with the community. Equalization has multiple complex
files of information which interrelate and enhance each other but are currently difficult to access.
The warehouse will allow this information to be available in many different formats which will
benefit many different groups and individuals.
Access to this data by cities, townships, private appraisers, banks, title companies, real estate
companies, and the general public could be made available to PC's through phone and modem
links. Access fees could raise revenue in support of the data warehouse while reducing the
amount of counter personnel time and phone contacts necessary to disseminate information.
The county and local governments could have the benefit of a reduced work load.
DEVELOPMENT & PLANNING DIVISION OBJECTIVES
I. ENHANCE EFFICIENCY WITHLN THE DIVISION
Improve access to data in the Land files including the Master Appraisal file, census data and
summary MESC data.
Provide opportunities for creating new products in support of division goals and
objectives. Products would include new thematic maps, reports of tends in land use and
development activity, integrated GIS data and multi-media information as well as other products.
Enhance opportunities for cost savings and efficiencies.
Reduce time required to extract and utilize information
Improve ability to create information
Provide a vehicle to stage Internet and KIOSK data
Track land consumption by class by community or sub community over time.
Improve knowledge and understanding of relationships between demographic and socio-
economic data and land use data.
Quickly access land data needed for loan application review process.
Provide market profiles of proposed site location utilizing census and land data resources.
Create reports on development activity, profiles of housing stock, trends in business locations,
relationships between demographic profiles and economic activity, development potential by
land use category, redevelop potential, etc.
A way of linking descriptive data to the GIS.
H. ENHANCE THE AVAILABILITY . OF DATA TO OTHER COUNTY
AGENCIES.
Improve access to census and other data for other county departments and divisions.
Create data products useful to other county departments
Provide Board of Commissioners, Fiscal Services, Personnel, Sheriff, EMS, CMH and Health,
(those that come to mind) with access to information resources which support grant requests,
service delivery analysis, program development and evaluation and other applications.
III. ENHANCE MARKETABILITY OF DATA
Provide opportunities to develop and sell new information products.
Establish and retain revenues
Provide more service for the tax payers
Produce map and information products for sale. Products to be determined and developed after
completion of Pilot project.
FISCAL NOTE (M. R. #96222)
BY: FINANCE AND PERSONNEL COMMITTEE, SUE ANN DOUGLAS, CHAIRPERSON
IN RE: DEPARTMENT OF INFORMATION TECHNOLOGY - IMPLEMENTATION OF A DATA
WAREHOUSE PILOT PROJECT
TO THE OAKLAND COUNTY BOARD OF COMMISSIONERS
Chairperson, Ladies and Gentlemen:
Pursuant to Rule XII-F of this Board, the Finance and Personnel
Committee has reviewed the above referenced resolution and finds:
1. Information Technology proposes, and the Strategic Planning
Committee recommends, pilot project planning and training effort
in the organization of selected data into a personal computer
environment for ease of access to county employees through the
process of Data Warehousing methodologies.
2. Total cost for the proposal is $75,000. Funds are
available in the Information Technology fund equity to cover the
cost. The following Budget amendment is recommended:
1-63600-455-001 Estimated Profit & Loss
4-63600-182-01-00-3128 Professional Services
$(75,000)
75,000
Q
FINANCE AND PERSONNEL COMMITTEE
10/1/96
VE THE FOREGOING RESOLUTION
Date
s Patt.E,on, County ExecL.i° L. B
Resolution #96222 October 10, 1996
Moved by Powers supported by Quarles the resolution be adopted.
AYES: McCulloch, Moffitt, Obrecht, Palmer, Pernick, Powers, Quarles,
Schmid, Taub, Wolf, Amos, Crake, Devine, Dingeldey, Douglas, Garfield, Holbert,
Huntoon, Jacobs, Jensen, Johnson, Kaczmar, Kingzett, Law. (24)
NAYS: None. (0)
A sufficient majority having voted therefor, the resolution was adopted.
STATE OF MICHIGAN)
COUNTY OF OAKLAND)
I, Lynn D. Allen, Clerk of the County of Oakland, do hereby certify that the
foregoing resolution is a true and accurate copy of a resolution adopted by the
Oakland County Board of Commissioners on October 10, 1996 with the original
record thereof now remaining in my office.
In Testimony Whereof, I have hereunto set my hand and affixed the seal of the
b_S-*
County of Oakland at Pontiac, Michigan this 10th day of Octo 19 .
—59—
Lydn D. Allen, County Clerk