Typical MS Excel Application Lifecycle
MS Excel provides many powerful tools to design, manage, and analyze business
data. Excel is also an easy, inexpensive way to prototype new business
applications. All too often however, MS Excel spreadsheets that are useful to a
the power-user that built it start to get used by more and more people, the spreadsheet grows
in features and complexity, and gets copied, modified and emailed around. Before you know it,
the effort to support the MS Excel application starts to exceed the business benefits!
When you reach the point when your MS Excel application is out of control, it is
time to convert your Excel database application to a relational
SQL Server database application. This will eliminate all of the multi-user issues,
provide reliable and repeatable business functions, and improve the quality and
consistency of the underlying data.
Convert Excel to SQL Server
Converting Excel to a MS SQL Server database provides a far
more structured, more repeatable approach for business critical processes. Excel spreadsheets provide
infinite flexibility,
but at the cost of bad data, and the cost of managing a distributed application
that is intended for just one or a few users. SQL Server database supports
well-structured data, and repeatable, high quality data and workflow processes, with
unlimited number of concurrent End Users.
The good news is that PCA can import data from Excel spreadsheets
to MS SQL Server, and make your Excel Application more structured,
while preserving the original functional integrity of the original MS Excel spreadsheet.
|
|
MS Excel
|
SQL Server
|
|
Workflow |
None (Email) |
Process-flow defined |
|
Concurrent Updates |
No (Single User) |
Yes (Multiple, concurrent Users) |
|
Security |
One password fits all |
Role-based privileges |
|
Version Issues |
High (file data stamp) |
None |
|
Audit-ability |
Low (date and email comparisons) |
High (full audit trail) |
|
Data Integrity |
Low (somewhat constrained) |
Highly constrained |
|
Data Capacity |
Limited |
Unlimited |
|
Data Structures |
Flat: 2-dimensions |
Relational: 3-dimensions |
|
|
|
|
Using Excel With SQL Server
Once Excel has been converted to a SQL Server Database,
the data can still be viewed, exported, and analyzed in MS Excel! All of
the data input, equations, and data manipulation becomes part of a structured, well-
defined database application, which provides for improved data entry consistency,
improved versioning as new capabilities get added, and generally easier, more straightforward
user interfaces. Best of all, the data can be controlled and made
available through secure role-based security over the Internet.