Network Operations Guide

Wed Aug 22 21:36:37 UTC 2007

> I'm guessing there is a tool somewhere that will take a set 
> of data from a database and present it like a spreadsheet for 
> import/export of updates. Anyone have a pointer?

Keeping data clean and consistent is so important to network operations
that I think this is relevant to discuss for a bit.

First of all, assuming Windows is the client OS and the data is on a
server somewhere, you may find it easier to use Access to get at the
data, join tables, sort it and filter out unwanted rows. Then export it
to an Excel spreadsheet. But not all corporate setups include Access, so
you can do much the same directly from Excel. Look in Data->Import
External Data->New Database Query, then select your data source,
table/query/view, columns, etc. At the end you can select "View or edit
query in Microsoft Query" and you will get a query builder than can help
you sift out only the rows and columns that you need to import. These
queries can be saved so that in future you simply rerun the query
Data->Refresh Data and get fresh up-to-date data.

For updating a central database, you either need to develop applications
or use a general purpose tool like MS Access. Usually spreadsheets are
used to store fairly straightforward tables so building an update
application is not necessarily that complex. For instance an Excel
spreadsheet template can contain a VB subroutine that takes a row of
data and turns it into an SQL UPDATE or INSERT statement.

To an IT person this all may sound rather crude and hardly any better
than just keeping a bunch of spreadsheets, but they probably never have
to deal with the consequences of dirty and inconsistent data.
Spreadsheets tend to breed. They get copied around in email and pretty
soon, people make mistakes and throw away the updated version, not the
old one. Or other people, building a new spreadsheet think that person X
has the definitive spreadsheet with all the latest IP address
allocations, not realizing that this is a second hand copy of another
master spreadsheet, and person X only gets a copy whenever an upgrade
project completes, every few months. Meanwhile, operations is busy
rationalising PoP layout and all the subnetting changes but nobody
writes it down except in the one project managers planning spreadsheet. 

The key principle here is to keep all important data in tables on a
database server, and make sure that everybody understands that these
tables are the one and only true source for this data. And, of course,
make sure that server is backed up properly and you have a
disaster-recovey clone server ready for action when needed.

As long as the data is on a DB server, non-Windows systems should have
no problems with accessing it. Scripting languages, Open Office, web
servers and so on, can all share the same data.

Note that I am suggesting this be done, separate from the kind of
OFFICIAL corporate databases that run financial, ordering and billing
applications. Those databases are always locked down by the DBAs and no
tables are added to them without being properly designed and approved by
DBAs, data architects, app developers, etc. I am suggesting that you run
a separate DB server to attract all the data that usually gets
squirreled away in spreadsheets, to entice employees to share data and
cooperate, without a lot of bureaucracy in the way. DBAs can help a bit,
advise a bit, but they should not be able to forbid people to set up a
table or index or query/view. This suggestion is to treat the DB server
like a general service to all employees, like telephones or meeting
rooms. 

--Michael Dillon