Here at Windward, I meet customers all the time who are at different points in what I think of as their “data storage evolution.” Some are just starting out with a simple spreadsheet, some are using a full-fledged database, and others are somewhere in between.
From these conversations, I’ve discovered some simple facts about data storage:
- Your data storage needs will change over time.
- If you don’t anticipate those changing needs, your data will have problems interacting with other software systems, including ours.
- It’s better to think about your long-term data storage needs now rather than later.
More specifically, although your first data storage choice may be Microsoft® Excel® (which works with our reporting software), there are pitfalls. Eventually, you should consider moving to a Microsoft SQL Server® solution. I wrote this post to show you why—and how to get there.
In your organization’s humble beginnings, you needed to keep records and store data. You may have started off with email as your first medium and then progressed into Excel. But you’ve noticed one or more of the following problems:
- Your data is stored in different places and is not formatted or structured well.
- Your data is not organized in a way that is easy for third-party software applications to interact with it.
- It has become harder to control the versions of this data when the data is stored in files that can be passed around and edited.
- Your storage medium isn’t performing as quickly and efficiently as you need it to.
So what is the best storage medium to use and what do you do if you outgrow your current solution?
We recommend this tiered approach for data growth and organization:
Excel –> Microsoft Access® –> Microsoft SQL Server Express –> Microsoft SQL Server Standard Edition.
Here’s why. Only Microsoft SQL Server can offer the performance needed for larger data sets. If you’re starting off in Excel, the progression that Microsoft natively offers to move from one storage medium to the next, as detailed above, is seamless.
Below I will detail each storage application, highlight the benefits and shortcomings of each, and describe how to move to the next storage application using Microsoft’s data import wizards.
NOTE: Windward supports retrieving data from Microsoft Excel, Microsoft Access and Microsoft SQL Server, so Windward customers can create reports at any stage in this data storage evolution.
Stage 1: I just got here. Let’s put our data in Excel.
Your business or project is starting, and so far you’ve been storing your data mostly in emails, which is great for finding and organizing information. But as your needs grow, you decide to move to Microsoft Excel to take advantage of certain benefits:
Key Excel Strengths
- Ability to make calculations
- Ability to identify trends through charts and graphs
- Ability to filter data based on specific criteria
Here at Windward, we have the ability to connect to an Excel spreadsheet and use that data to populate reports in our Windward Solution reporting products. We access the data via an SQL select query that treats the spreadsheet like an SQL table.
TIP: We recommend you use workbooks themselves to take advantage of separate worksheets within a workbook. If you have lists of clients, sales people, orders and regions, the best practice is to create a separate worksheet for each of these lists. You can then correlate information between worksheets to create complex reports like sales for each representative by month, filtered by year.
This works well at first, but as users pass the spreadsheet around through email and start keeping different versions on different computers, the confusion begins to creep in.
Key Excel Drawbacks
- Querying Microsoft Excel can be limited if the data in the spreadsheet is not structured. Many spreadsheets we see do not store data uniformly. By this I mean the first row of the spreadsheet is a header column denoting the information stored below it in each column (date, first name, address, etc.).
- Data entered in each column can also vary because data typing is not required. Dates, as an example, could be entered as 12-01-2014, 12/01/2014, Dec-01-2014, etc. You can see where this would cause confusion for a software program to access this information and manipulate it. (TIP: You can type an entire column by selecting the column and setting it via “format cells.” This is a best practice method that will assist other programs querying that data.)
- There will come a time when the Excel spreadsheet starts to show size and performance limitations. You will likely also encounter problems with it not being centrally managed.
When this happens, it’s time to think about moving to Microsoft Access. Because Windward already queries the Excel spreadsheet like a database, moving your Excel spreadsheet to a new storage medium like Access is an easy process.
Stage 2: We need a central data location that is portable. Let’s move our data to Microsoft Access.
The next logical step is to centralize this data so that everyone has access to the same information stored in the same place, typically a Microsoft Access database.
Key Microsoft Access Strengths
- Portability. The database file can be small enough to be portable.
- Central storage. Access can facilitate the function of remaining in a central location that can be easily backed up.
- Ability to create forms. The database provides the ability to create forms for users to enter data into the database and report on that data in a limited manner.
In short, Microsoft Access allows a user to build a front-end interactive interface to input and report on the data contained within, all while containing the ability to make the database mobile.
Microsoft includes a very nice import wizard for importing an Excel workbook into an Access database and assisting you in mapping your fields and creating your first database structure. (Here’s a great reference article on Excel and Access importing.) This is useful in creating relations between your worksheets in your workbook as well as typing your data in your columns to make sure the data storage is uniform.
While you will now experience the benefits of querying your data from a database structure (speed, performance and mobility), you may encounter problems when using an Access database as a data source.
Key Microsoft Access Drawbacks
- Lack of security. It is very easy to email or transport the database via a USB stick. This makes your database, which may contain sensitive information, susceptible to theft.
- Difficulty with large amounts of data. Storing moderate to large amounts of data is capped due to Microsoft Access database files having a limit of 2 GB.
- Absence of a “fully relational database.” You have the structure of a database but it is lacking many of the advantages that a full database provides, such as indexing of tables to increase performance, complex typing of data, and lack of views or stored procedures for Access 2007 and below.
With the data stored in confusing non-relational manner and storage size increasing due to centralization, you now need a better solution. Migrating your Microsoft Access database to Microsoft SQL Server is the last step in reaching both secure data storage and organized efficiency.
Stage 3: Our data is growing, queries are slow, and the system is unorganized and insecure. Let’s move our data to a Microsoft SQL Server.
The final progression is a Microsoft SQL Server using either the Express version or the Standard Full SQL Server version. This fills in the final checkbox of a secure, centralized management of data with relations between that data so data can be easily indexed, searched and accessed programmatically by other applications.
Key Microsoft SQL Server Strengths
- Table indexing increases performance of query response time.
- Complex typing of data such as dates, currencies, numbers, etc. ensures uniform selection.
- Views and stored procedures allow you to save complex queries and make them variable via entry of parameters.
- A secure and centrally located database adds a layer of security to your data storage implementation.
- A fully relational database enables you to cross reference and combine your tables to return a full data set in an efficient manner in order to expose trends in your data.
- Database size not limited to 2 GB, which will be important as your database grows.
You can use the import wizard available in the Microsoft SQL Server (both Express or Full Version) to assist with importing your Microsoft Access database.
We realize that cost is an important consideration before moving to a full SQL Server implementation. Microsoft offers an Express version of its SQL Server that can get you started at no cost right away. The best part is that you can still accomplish the same migration steps from Microsoft Access, and once your data is migrated you can migrate to Microsoft SQL Server Standard Edition at a later point using the same upgrade process.
Upgrading from MS SQL Server Express to a full MS SQL Server requires a license change and installing add-on components, so it’s not that difficult. The Express version is limited in that it can only use 1 CPU, it has 1 GB of memory, and the database size can be a maximum of 10 GB. You can find more information here.
In the end, Windward recommends using a Microsoft SQL Server database as your target database as it offers the most functionality, performance and reliability when interacting with not only our solutions but other software packages as well.
If you’re looking for more database tips, check out the recent blog I wrote on ideas for making your database more reporting friendly.
Author: Ryan Fligg
Ryan, Windward's Sales Engineer, has been with Windward since 2006 in many roles as a sales engineer, IT specialist and account executive. Ryan's background fuels his desire to guide Windward's product development. He now works on the future vision of Windward offerings through creating the product roadmap, responding to customer requests, and communicating what Windward is doing and where it’s headed.
Other posts by Ryan Fligg