Should Your Organization Integrate Microsoft Excel and Microsoft SQL Server To Enhance Performance
From small Data To BIG Data To HUGE Data – MS Excel/SQL Server Solutions
This post picks up on “The Integration & Automation Series” of posts, from where we left off on our last post, Should you integrate Microsoft Excel and Access in an attempt to work with BIG Data. In that post we discussed how to integrate Microsoft Excel (Front-end) with Microsoft Access (Back-end). This post like that post applies to Businesses (Both Large and Small), Government Offices such as the DMV, Educational Institutions such as Harvard and individuals that work with Microsoft Excel as one of their primary data manipulation and reporting tools, wither as a stand alone app or via integration and automation. In this post our topic is “Should Your Organization Integrate Microsoft Excel and SQL Server?”. If your Excel workbook is performing slowly or worse, unresponsive, and if you work with BIG Data, then this solution is for you.
This post applies to all of those individuals that call themselves Microsoft Excel experts, Excel gurus, Excel MVP’s, data scientists, visualization experts, Excel consultants, Excel programmers, or Excel developers. So no matter what you call yourself, if you work with so much data that Microsoft Excel cannot hang, and if you are in a position that you and your organization needs to decide whether to use Microsoft Access or Microsoft SQL Server as your back-end, this post will help you decide. With that said, make sure to read the final post in this series, that post will cover the newest and baddest Microsoft database solution, Microsoft Power BI.
We will go over the various data set sizes (Small, Big, Huge) of throughout this series of posts.
For this post we cover two of the more common scenarios that many businesses encounter when they attempt to use Microsoft Excel to work with BIG data: Scenario #1) Your company has been using Microsoft Excel as a stand alone, self-contained solution, doing both data manipulation and reporting (Access, SQL Server, Azure and Power BI are not used). Scenario #2) Your organization has been using a Microsoft Excel front-end with a Microsoft Access back-end as a fully integrated and automated data manipulation and reporting solution.
Now that you are working with BIG Data, neither of these solutions is performing well as both have become inefficient and huge time burners, or worse, they have simply stopped responding at all. So with this post we offer a new solution, one that overcomes the challenges of both scenarios, one that incorporates a more advanced, fully integrated and highly automated solution that allows you to work with BIG Data. This solution has a Microsoft Excel front-end, and a Microsoft SQL Server back-end. The advantage of this solution is that it uses the Microsoft SQL Server Database Engine, and that it has the ability to store much more data than does the Access back-end. If this solution is done properly, it will be both an affordable and simple to use Microsoft data concentric solution.
But what is all of this talk about “BIG Data“? What does that even mean?
According to Lynda.Com: The Database Engine is the core service for storing, processing, and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise.
What is BIG data when it comes to working with Microsoft Excel – Would you like to learn more about BIG data, if so, Lynda.Com covers the topic very well.
Big data is big news. But what is big data, and how do we use it? Simply put, big data is data that, by virtue of its velocity, volume, or variety (the three Vs), cannot be easily stored or analyzed with traditional methods. Spreadsheets and relational databases just don’t cut it with big data.
Big Data has been here for decades and it is largely responsible for the exponential increase in the number of data scientists and visualization experts that make their living harnessing the power of the data concentric applications on the market. Note, in the real world data scientists are not restricted to the handful of Microsoft database applications as there are literally dozens of choices. But for this post we will limit the options to those made by Microsoft. Working with BIG Data is no longer the exception, rather it is the norm, and as such, selecting which Microsoft applications to work with is critical. Why, because each application has size limitations. As HUGE data replaces BIG data the mastery of the Microsoft database applications for reporting and analysis purposes cannot be ignored. Microsoft Excel is not now nor has Excel historically been able to manipulate BIG data-sets of hundreds of thousands of rows populated with advanced functions and formulas, regardless of the applications ability to make 1,000,000 rows available to the user. As each day passes Microsoft Excel becomes less of a data manipulation tool and more of a user interface front-end tool for reporting and analysis.
This series of posts has been written to help you and those at your organization decide what Microsoft data driven applications are well suited for your data manipulation and reporting needs. Choosing the wrong solution will cost your organization large amounts of time and money, and if those amounts are large enough, possibly your position. So it is important to take the time to ask the right questions, to fully understand what your needs are now, and what you expect them to be 10 to 15 years from now.
Microsoft Excel and Microsoft Access Size Limitations: As the older, more traditional data applications (Microsoft Excel and Microsoft Access) struggle to keep up with the sheer volume of ever increasing mountains of data being amassed these days by most organizations, the current solutions built on them are rapidly falling behind. With one million rows having been available since the release of Microsoft Excel 2007, the actual use of those same cells, filled with complicated Excel formulas, tens of millions of them, having it functioning to acceptable standards is becoming literally impossible. Becoming equally impossible is the ability of Microsoft Access to house BIG Data, as its limitation has been set at 2.09 gigabytes for years now and that changing does not appear to be happening anytime soon, if ever. At some point, the combination of new and historical data becomes just too much for many solutions to function within tolerable standards. Yet Microsoft has not nor will it increase the number of gigabytes Access can house, as it has long been Microsoft’s policy to get people to upgrade to SQL Server, at an added cost.
The exponential rise of data over the past few decades has caused both large and small businesses, government offices, and educational institutions to look for alternative BIG data solutions, and possibly at different Microsoft database applications to build those solutions on. Solutions that are not only affordable, but solutions that are also forward looking. Microsoft based solutions that will still be viable ten or fifteen more years from now.
You also want to make sure to look at data solutions that allow the option to incorporate one or more of the Microsoft Office applications, such as Microsoft Excel, Access, Word, Outlook, and PowerPoint. For it is the ability to easily integrate and to automate the various Microsoft Office applications with the database applications that makes them so enticing. Companies will continue to use email for the unforeseeable future. Companies will continue to use programs such as Excel and PowerPoint for reporting and analysis. Being able to easily integrate these various MS Office programs with each other and the Microsoft database applications is something that Microsoft is betting on. Microsoft is putting lots of money behind this. This is how Microsoft maintains its dominance; not one or two business productivity tools, but a dozen or more, tools businesses use day in and day out.
As expert Excel consultants we work in all of the Microsoft applications. We are experts in integrating and automating the suite of Microsoft Office applications with the Microsoft database applications, Access, SQL Server, Azure and Power BI.
In this series of intermediate BIG Data solutions we will continue to use the Microsoft Office applications your organization currently has in place. Each has its use and there is no need to make changes to them other than to point them to the new SQL Server back-end. We also recommend minimizing the revisions needed to the Excel files. Revisions take time and they cost money. For this solution all we want to do is to 1) either create a back-end, or 2) switch from an Access back-end to a SQL Server back-end. Once again this is in effort to take a poorly operating Excel application and to make it a speedy, responsive, long term solution, one that can easily deal with BIG data. These revisions will focus on making SQL Server the back-end, with Excel being the front-end. The revisions will be needed, but there is no need to create new workbooks, unless that is desired.
We receive several requests to help companies with their unresponsive Excel files several times each week. Some of them, such as the client that wrote the Google Plus 5-Star Review below are small projects that we can quickly resolve. Others are large projects that require creating and Excel front-end with a SQL Server back-end.
Putman Media had a 5-Star Google Plus Review – “Wow” experience. We were struggling with a large, critical sales commission worksheet for 15 sales reps. This monthly computation had become a dreaded task because the worksheet was substantially unresponsive. We sent the file to Excel and Access, LLC on Friday. They looked it over and quoted a price to fix. We agreed and in two days, they solved the problem and we already have the file and it works?. Seems that Jacob is a certified Microsoft Excel MVP and we are very happy he could handle our problem. Putman is a very happy customer!
This post is focused on upgrading/expanding a system, not on creating a new system. Why, to keep costs down.. The system already does what you need it to do, you just need to speed it up. So with that said, once the revisions have been completed, and once the Microsoft SQL Server back-end application has been created, the SQL Server application will be used to manage, manipulate and to store the BIG Data sets. For those of you who are new to SQL Server and that have no idea what SQL Server is and what it can do, SQL Server has been successful in filling this role since its inception decades ago. That is why so many IT departments use SQL Server as their primary database application. Having SQL Server house mountains of their detailed and historical data. Data that every department in the company uses, each in their own unique way. But SQL Server like Microsoft Access does have its own set of limitations. For our scenario, two of those limitations will need to be overcome.
SQL Server limitations: For years it has been said, that SQL Server is like Access on steroids. From a data crunching and data housing point of view, that is correct. But with that said, it is critical to point out that SQL Server does not have the same friendly user interface that the Microsoft Access forms have, with their buttons, drop-downs, etc, nor does SQL Server have the same reporting capabilities of Access. One way to get around the need of a friendly user interface is to use Microsoft Excel UserForms which are similar to Access forms. A second option to resolve the user interface challenge is to create an Access application without tables or queries, and instead use Access forms for the user interface, with Access manipulating both Excel and SQL Server. In this scenario, you can use Excel for reporting, as many organizations already do. A second reporting option is to use the Access application to provide the reports, based on the tables in the SQL Server back-end.
The Microsoft SQL Server interface looks nothing like Microsoft Access, nor is it as “User Friendly” to use, but it is substantially more powerful and therefore substantially more useful. Compare the two images below and you will see the difference.
Below is a good example of what Microsoft Access looks like, as you can see, the forms create a nice user interface.
Below is an example of a typical SQL Server interface, clearly not as user friendly as Microsoft Access.
There are numerous versions of Microsoft SQL Server available, no matter your need you will have no problem finding one that fits your size requirements and your budget.
Microsoft Excel limitations:
The diagram below will show you when Microsoft Excel hypothetically became able to house and manipulate “BIG Data”. Unfortunately, just because Microsoft expanded the number of rows and columns available in Microsoft Excel 2007, Excel was and is still unable to perform within acceptable performance standards. And that is the reason so many firms over the years have adopted an Access back-end. But as this post points out, Access too has its limitations an abilities. 2.09 gigs in size is the biggest prevention.
Starting in Excel 2007, the “Big Grid” increases the maximum number of rows per worksheet from 65,536 to over 1 million, and the number of columns from 256 (IV) to 16,384 (XFD).
To summarize to this point
To summarize to this point, when it comes to manipulating and calculating BIG Data, Microsoft Excel on its own, as a standalone application is typically not powerful enough to do the job properly. Any fantasies that you have about populating one million Excel cells with advanced formulas and functions can be forgotten, as Excel will not be able to function according to acceptable standards. To manipulate BIG Data you need to leverage the power of the Microsoft database applications such as Microsoft Access, SQL Server, Azure or Power BI. In order to do so for the solution be recommended here, you will use one of those applications as the back-end, and your existing Microsoft Excel as the front-end.
Recommended Microsoft Data Applications Based on Size:
- Small Data – Microsoft Excel
- Medium Data – Microsoft Excel, Microsoft Access
- Large Data – Integrate Excel with Microsoft Access, Microsoft Azure, Microsoft SQL Server
- BIG Data – Integrate Excel with Microsoft SQL Server, Microsoft Azure
- HUGE Data – Microsoft Power BI
End of Part One – look for part to learn more about this solution
This post has a lot of aspects to it, so we need to break it up. We will then do a post on the third solution, the one on Power BI.
It is one thing to read how to do something and to understand it than it is to actually do it. Many people find themselves wanting to build the solution that they are excited to get in place. But often once they start the work they find that it is a bit more complicated and challenging to do than they had originally thought. But no worries, that is why we are here. We not only teach you how to do thins, we can also be hired to do the work for you..
Call us now and get started today 877-392-3539