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

Logging the Spreadsheet for Transformation

The next step in the transformation process is logging the spreadsheet in. It is at this point that the physical ownership of the spreadsheet passes from the original owner to the organization that is charged with data transformation. Upon transferring the spreadsheet, the spreadsheet is “logged in”.

As mentioned earlier, the information gathered during the logging process includes:

  • • Identifying the name of the spreadsheet
  • • Identifying the date and time the submission was made
  • • Identifying who submitted the spreadsheet
  • • Identifying any other relevant information – the size of the spreadsheet, the language the spreadsheet is in, any restrictions, etc.

The login process is very important in the transformation of spreadsheet data to a corporate database, as it represents one of the vital links in the establishment of data lineage.

Previous Section
End of Section 4
Next Section