Book Image

Turning Spreadsheets into Corporate Data

By : Bill Inmon
Book Image

Turning Spreadsheets into Corporate Data

By: Bill Inmon

Overview of this book

Spreadsheets are a popular way to store and communicate business data, but, although they are easy to create and update, they are not reliable enough to be used for making important corporate decisions. With this book, you can gain insight into how to maintain spreadsheets, how to format them, and then convert them into a database of reliable and useful information. Turning Spreadsheets into Corporate Data starts with a quick history of spreadsheet usage. You’ll learn the basics of formatting spreadsheets, including how to handle special characters and column headings, and how to convert the spreadsheet first into an intermediate database and then into corporate data. You will also learn how to utilize the mnemonic dictionary that is created along with the intermediate database. The later chapters discuss the immutability of data and the importance of organizational and political considerations during the data transformation. By the end of this book, you’ll have the skills and knowledge needed to convert your spreadsheets into reliable corporate data.

Related Content you might be interested in

Current Title:

Small book image
Turning Spreadsheets into Corporate Data
Bill Inmon
Jun, 2017 | 140 pages
No titles found
Table of Contents (16 chapters)
Free Chapter
1
Introduction
Introduction
2
1: Brief History of Spreadsheets
1: Brief History of Spreadsheets
The IT Labyrinth
End User Acceptance of the Spreadsheet
Spreadsheet Hell
A Tradeoff
Responsibility—The Flip Side of Control
Management’s Problem
Differences Between Two Types of Data
In Summary
3
2: Spreadsheet Paradox
2: Spreadsheet Paradox
Public Data
The Spreadsheet as a Medium of Exchange
Recurring/Non-Recurring Spreadsheets
The Spectrum of Spreadsheets
The Cost of Transforming a Spreadsheet
Factors Other than Cost
Transcription of Data
Cell Formula
Spreadsheet Descriptors
Artificially Supplying Descriptors
In Summary
4
3: Spreadsheet Varieties
3: Spreadsheet Varieties
Simple Demarcation—xlstab
Other Special Characters—eold and Linefeed
The Internal View of a Spreadsheet
A Missing Column Heading
A Missing Value
A Multiline Row
The “Standard” Spreadsheet Format
Managing the User of the Spreadsheet
The ssdef Table
The Spreadsheet Processing Log
The Lineage of Spreadsheet Data
The Cell Formula
Relating to the Real World
Identifying the Header Line
In Summary
5
4: The PDF Spreadsheet
4: The PDF Spreadsheet
The Importance of Special Characters
PDF and OCR
A Final Option
In Summary
6
5: The Basics of Spreadsheet Formatting
5: The Basics of Spreadsheet Formatting
The System Name
Unreliability of Report Name
Multiple Sheets in a Spreadsheet
Other Special Characters
Identifying Column Headings
Similar Column Headings
Blocking Off Sections of a Spreadsheet
Non-Standard Spreadsheet Structures
A Spreadsheet that Cannot be Mapped
A Spreadsheet in a TXT Format
In Summary
7
6: Spreadsheet Disambiguation
6: Spreadsheet Disambiguation
Selecting Spreadsheets for Inclusion into Corporate Data
Recasting the Spreadsheet
Logging the Spreadsheet for Transformation
Entry into the Path Queue
Defining the Spreadsheet Headings
Pairing the ssdef Specification to the Spreadsheet
Finding and Creating Database Definitions and Values
The Intermediate Database
Some Anomalies
What if an Error is Discovered?
Manual Effort Required
Spreadsheet Width
Subdividing a Spreadsheet
No Value for a Column Name
No Column Headings
Creating the ssdef Specification Once
In Summary
8
7: The Intermediate Database
7: The Intermediate Database
Finding Errors
The Contents of the Intermediate Database
Functions Served by the Data Elements
Alternate Name
Adding Context to Data Values
Editing Data in the Intermediate Database
In Summary
9
8: The ssdef Database
8: The ssdef Database
Organizing Data Inside the ssdef Table
Processing Using ssdef Records
Searching the Full Path Queue
In Summary
10
9: The Corporate Database
9: The Corporate Database
From Intermediate Data to Corporate Data
Grouped Corporate Data
Tracing the Lineage
In Summary
11
10: The Mnemonic Dictionary
10: The Mnemonic Dictionary
The Contents of the Mnemonic Dictionary
Grouping Like Data Elements
Applying Naming Conventions
Value of the Mnemonic Dictionary
In Summary
12
11: Political Considerations Within the Organization
11: Political Considerations Within the Organization
Shifting Control
Immutability of Data
The Importance of Alternate Names
Limited Editing
Super Classifications of Data
The Lineage of Corporate Data
Relative Volumes of Data
In Summary
13
12: Data Modeling and the Spreadsheet Environment
12: Data Modeling and the Spreadsheet Environment
The Entity Relationship Diagram
The Data Item Set
The Physical Model
The Data Model
The Data Model and Spreadsheet Data
“Correctness” of Data
Aligning Data from Different Spreadsheets
An Algorithmic Resolution
An Indexed Resolution
Resolution and the Data Model
Spreadsheet Data in the Data Warehouse
Changing Spreadsheet Data
In Summary
14
13: Case Study
13: Case Study
15
Glossary
Glossary
16
Index
Index

Selecting Spreadsheets for Inclusion into Corporate Data

The first step in the process of spreadsheet disambiguation is selecting the spreadsheets for processing. Most spreadsheets will never be suitable for serving as a basis for corporate data. In order to evaluate whether a spreadsheet is fit to be changed into corporate data, the analyst must answer questions including:

  • • How reliable is the data on the spreadsheet?
  • • Is the data on the spreadsheet in a standard format? If not, can it be reformed into a standard format?
  • • Is the data on the spreadsheet something that is of use across the corporation? To multiple organizations?
  • • Is the data on the spreadsheet regularly updated or kept current?
  • • Is there a clear owner (or manager) of the data in the spreadsheet?
  • • Is there another very similar set of data being collected elsewhere?
  • • How often are new values added to the spreadsheet?
  • • How...
Previous Section
End of Section 2
Next Section