Database Reference
In-Depth Information
In addition, these capacity limitations often force you to have the data prepared for you. That is,
someone else extracts large chunks of data from a large database, then aggregates and shapes the
data for use in Excel. Should you always depend on someone else for your data needs? What if you
have the tools to “access” vast quantities of data without relying on others to provide data? Could you
be more valuable to the organization? Could you focus on the accuracy of the analysis and the qual-
ity of the presentation instead of routing Excel data maintenance?
A relational database system (like Access or SQL Server) is a logical next step. Most database system
tables take very few performance hits with larger datasets and have no predetermined row limita-
tions. This allows you to handle larger datasets without requiring the data to be summarized or pre-
pared to fit into Excel. Also, if a process becomes more crucial to the organization and needs to be
tracked in a more “enterprise-acceptable” environment, it's easier to upgrade and scale up if that pro-
cess is already in a relational database system.
Transparency of analytical processes
One of Excel's most attractive features is its flexibility. Each individual cell can contain text, a number,
a formula, or practically anything else you define. Indeed, this is one of the fundamental reasons
Excel is such an effective tool for data analysis. You can use named ranges, formulas, and macros to
create an intricate system of interlocking calculations, linked cells, and formatted summaries that
work together to create a final analysis.
The problem with that is there is no transparency of analytical processes, meaning it is extremely dif-
ficult to determine what is actually going on in a spreadsheet. If you've ever had to work with a
spreadsheet created by someone else you know all too well the frustration that comes with decipher-
ing the various gyrations of calculations and links being used to perform an analysis. Small spread-
sheets that perform a modest analysis are painful to decipher but are usually still workable, while
large, elaborate, multi-worksheet workbooks are virtually impossible to decode, often leaving you to
start from scratch.
Compared to Excel, database systems might seem rigid, strict, and unwavering in their rules.
However, all this rigidity comes with a benefit.
Because only certain actions are allowable, you can more easily come to understand what is being
done within structured database objects, such as queries or stored procedures. If a dataset is being
edited, a number is being calculated, or any portion of the dataset is being affected as a part of an
analytical process, you can readily see that action by reviewing the query syntax or reviewing the
stored procedure code. Indeed, in a relational database system, you never encounter hidden formu-
las, hidden cells, or dead named ranges.
Separation of data and presentation
Data should be separate from presentation; you do not want the data to become too tied into any
one particular way of presenting it. For example, when you receive an invoice from a company, you
don't assume that the financial data on that invoice is the true source of your data. It is a presentation
of your data. It can be presented to you in other manners and styles on charts or on Web sites, but
such representations are never the actual source of the data.
 
Search WWH ::




Custom Search