SQL Spreads: a front end for SQL Server

There are several options available if you are looking for a front end for SQL Server. Like most things, what is best for you will depend on a number of factors. In this article, we’re going to look at the things to consider when choosing a front end for SQL Server and how the SQL Spreads Add-In for Excel could be a good option for you.

What is a database front end?

A database front end refers to the user interface or application that enables users to view and manage (create, update, delete) the underlying data stored in a database like SQL Server. The front end holds the logic relating to how users retrieve data, view it and passes update requests to the backend database.

The front end application can vary in terms of features and functionality and this will depend on the main purpose for which it was created. For example, SQL Server Management Studio is a great front end for DBAs to manage SQL databases, but would be too complex for users that simply want to view data and make basic updates.

A typical use case might be a company that stores product and price information in a SQL database that wants to give wider access to people to view and edit data. This kind of information may reside in ERP or financial systems, and you wouldn’t necessarily want to give wide access to those applications. Having a simple front end for users to search for and view information relevant to their job (e.g. a sales person looking up prices), with edit capabilities for certain people is therefore a common requirement.

Things to consider when choosing a front end for SQL Server

When looking at options for a front end for SQL Server, there are several things to consider:

• Purpose – what is the purpose of the front end? Is it to view data and make updates, or is there a need to also manage components such as databases and tables?
• User roles – which type of user will be using the front end? What kind of actions do they need to perform? What level of technical skill do they have?
• In-house skills – do you have the skills to develop your own solution? Do you prefer buying off-the-shelf solutions?
• Mobile access – do users need to be able to use the front end app from a mobile device?

It’s also important to consider how your needs might change in the future – you don’t want to put a solution in place that you’ll grow out of quickly, or that becomes an unnecessary administrative burden.

Approaches for deploying a front end solution for SQL Server

Broadly speaking there are three main approaches for deploying a front end solution for your SQL Server database:

• Develop your own solution using ASP.net or similar
• Build a solution using a ‘low code’ development platform, such as Power Apps
• Use an off-the-shelf solution, such as SQL Spreads

If you have the skills and a clear understanding of the purpose of the front end app and the people that will use it, developing your own front end app is a good option. The downside to this approach is that it can take longer and cost more than you initially think if the development and deployment approach is not well managed.

The appeal of using low-code development platforms is obvious: a much wider group of people can create business applications at a faster rate than traditional development techniques. To create simple business apps such as ordering or ticketing systems is relatively straightforward with these platforms. There are also a large number to choose from – Airtable, Quickbase, Outsystems, Retool and MS Power Apps are just a few of the many examples.

In terms of an off-the-shelf solution, let’s not forget that everyone’s favorite business tool, Excel, can be used as a simple front end for SQL Server. It has a robust mechanism for getting data through it’s Get & Transform Data functions. You can use this to import data from SQL Server, and perform some transformations and data aggregation. However, with the addition of the SQL Spreads Add-In, you can really transform Excel into a powerful front end for SQL Server – we’ll take a look at it in the following sections.

We do, of course, need to mention that there is a front end included when you install SQL Server – SQL Server Management Studio (SSMS).  From an administrative point of view, SSMS provides a range of tools to configure, manage and monitor instances of SQL Server, databases and tables and is an essential tool for developing and managing the database side of applications. However, it is not necessarily the best ‘front end tool’ for SQL Server from the perspective of a general user that wants to view, and potentially edit data as it is not aimed at those types of users.

Using SQL Spreads as a front end for SQL Server

SQL Spreads Overview

SQL Spreads is an Excel Add-In that uses Excel as a front end for SQL Server, to let your end-users easily and securely update SQL Server data.

With the SQL Spreads Add-In installed, users can perform the following from within Excel:

• Connect to SQL Server and view data in tables and views
• Update, Insert and Delete data in SQL (based on their permissions)

Additionally, those with design permissions can control how users interact with the data by using features such as:

• Using tree filters and lookup columns to help users navigate their data
• Post-processing to validate changes before they are committed to the database
• Auditing to show which users made changes and when
• Data pivoting to show data in a summarized way

Finally, SQL Spreads also includes some tools to perform some basic SQL Server administration tasks from within the Excel interface:

• Create tables in SQL Server using data from Excel
• Modify table columns (change, add, delete) and rename tables in SQL Server
• Copy tables in SQL Server from one database to another.

Get started with SQL Spreads and download the 14-day trial.

SQL Spreads Use Cases

Let’s look at some examples of how SQL Spreads can be used as a front end for SQL Server.

In the examples we’ll talk about 2 different types of SQL Spreads users: Designers are those people that create a connection to SQL Server in the SQL Spreads Excel document, and add some filters and other settings to make it easy for general Editors to interact with the data. A Designer is therefore like a super user, whilst Editors are day-to-day users.

Using SQL Spreads to view and update SQL data

In this example, the SQL Spreads Designer has created a connection to a table in the AdventureWorks Data Warehouse and then added a tree filter to make it easier for Editors to find the data they need.

First, the SQL Spreads Add-In for Excel should be downloaded and installed. The Add-In creates a new menu item on the Ribbon for SQL Spreads. Most of the configuration of the SQL Spreads document is completed using the SQL Spreads Designer.

When you first open SQL Spreads a dialog will pop up prompting you to connect to your SQL Server. As the document Designer you can create a connection to the relevant SQL Server and select a table – in this case the DimReseller table. The SQL Spreads Designer pane is also where you set up column properties, filters, sort options and other settings.

Once the data from SQL Server has been loaded into Excel, it is ready for Editors to interact with straight away. In this case the Designer has also added a Tree Filter. This makes it easier for Editors to find and filter the data relevant to them. This particular filter has also been created so that the logged in user only sees Tree Filter items that are applicable to them (e.g. sales people only see resellers in their specific regions). This adds a useful level of security and also makes it easier for people to work with.

Editors can view the data and make changes to fields. For example, they could change the business type, fix a typo in the reseller name column, or update the annual revenue figures. Once all the required changes have been made, the Editor can click on the Save to Database button and the changes will be written back to SQL Server.

Depending on how the Designer has set up the connection to SQL Spreads, an audit trail can also be written so that details of who made which changes can be stored in SQL Server.

Using SQL Spreads to build a financial forecasting model based on data in SQL Server

Excel is often used to create financial models – rightly so, because of its flexibility and ease of use. However, for important applications, it’s often best to use Excel together with a relational database management system like SQL Server. SQL Spreads allows you to do this easily.

We have created a Financial Forecasting Model Demo in SQL Spreads using data from SQL Server as well as several features in Excel. Here is a brief summary of the main features of the demo:

• Connection to a forecasting table in SQL Server
• Tree filters to select forecast period, company and region
• Editing of Budget, Actual and Forecast values
• Calculation of variance
• Adding of comments (if relevant)
• Change tracking (date and person last modified)

Using SQL Spreads to manage SQL Tables

Users that have Designer permissions can also perform some administrative SQL tasks from within Excel using the SQL Spreads Add-In. This includes the following:

• Create tables in SQL Server using data from Excel
• Modify table columns (change, add, delete) rename tables in SQL Server
• Copy tables in SQL Server from one database to another.

Typically these tasks would need to be done in Microsoft SQL Server Management Studio.

Let’s look at an example where we want to create a table in SQL Server from some data in Excel. In the screenshot below I’ve formatted my data as an Excel table and then clicked the New SQL Server table from Excel:

The Create SQL Server Table dialog is populated with information inferred from the Excel table by SQL Spreads. You can review this information and change things as needed – e.g. change the table name and location, and alter the column properties. When you click OK, the table is created in SQL Server.

As you can see, creating a table in SQL Server from Excel becomes an easy process with SQL Spreads. The process to make changes to columns in existing tables or to copy tables is just as straightforward.

Summary

In this article we’ve looked at some things to consider when choosing a front end for SQL Server and specifically how the SQL Spreads Add-In for Excel could be a good option for you.

Depending on your needs and resources, you can adopt one of the following options

• Develop your own solution using ASP.net or similar
• Build a solution using a ‘low code’ development platform, such as Power Apps
• Use an off-the-shelf solution, such as SQL Spreads

In many cases, organizations take a hybrid approach which involves one or more of these options. For example, a critical organization-wide app may be developed using a traditional web front end with a database backend, whilst a department-specific data management project might use SQL Spreads to allow users to manage data.

Contact us if you need advice on creating a front end for SQL Server, and don’t forget to download a trial copy of SQL Spreads today.