Using Excel as a Front End for SQL Server and Power BI MIS

What follows is a deeper look into some common questions about this approach. A summary of those areas is listed in the table below:

Do I need a custom-built Excel spreadsheet to get information into SQL Server?

No, but at an enterprise level, most users won’t have the skills to access SQL Server directly and it would be very awkward to manage appropriate user permissions.

The traditional method for office workers to edit SQL databases was through Microsoft Access. Access files were designed to be split into a separate front-end file for editing, and a back-end containing the database. If you want a shipping team to be able to see order details read-only whilst being able to update shipment details then Access lets you pick and choose which fields users can edit. Access can also replicate some of Excel’s abilities to perform calculations on data.

The problems with Microsoft Access come when users are trying to find the records to edit. A front-end Access file can contain preset queries, making it easy for a user to view a list of open orders or search the database for a certain customer’s records. The problem is that general users tend not to be confident in building queries themselves and, if they did, it could change the front-end file for other users. You’re then comparing a custom-built Access database with a custom-built Excel spreadsheet and, in those circumstances, most users would prefer the software they use every day.

However, there are third-party add-ins available, such as SQLSpreads, that are designed to let people access and edit SQL data tables within Excel. These tools mean employees can work where they’re comfortable in Excel, but face three problems. The first is that it can be harder to get sign-off on a new tool from central IT than to get consultants to work within Microsoft Excel. The second is that uploads to the SQL database are manually controlled so users need training to regularly upload changes. Finally, off-the-shelf tools strike a balance between flexibility and universality that may mean they can’t do everything you require.

Are my SQL databases secure if employees can edit them from Excel?

Using SQL Server, each interaction with the database can be tied to a user’s Windows credentials using integrated authentication. That means Excel can make changes without having to store any password information and Excel access will be subject to the user’s SQL Server permissions.

If anything, having a bespoke Excel front end introduces another layer of control because you can restrict people’s editing rights in the Excel file as well. This can be a useful guard where database administrators have provided broader database permissions than strictly required for this process. Our Excel consultants can build solutions to prevent accidental errors in Excel reaching SQL Server. These invisible guardrails can save on user training.

Data security concerns tend to fall into two categories. The first is whether employees can walk away with valuable data. By introducing an Excel front end to the SQL database, you can limit employees to seeing relevant information only. If salespeople only need to add new accounts to a client database then you could give their Excel file could just contain a blank form. Or if the workshop only need to see a list of returns then you could create a heavily filtered table using a combination of Visual Basic and PowerQuery.

The more common issue is that employees might mess up a data table. If the goal of the SQL project is to create a Power BI dashboard showing year-on-year changes in the sales pipeline then you probably don’t want users messing around with last year’s data. However, you may want them to be able to see last year’s data if it provides insight on a customer’s behaviour. It is possible to continuously lock older rows of data in SQL server, but it’s a lot easier to make record editing data-dependent within Excel.

Can I edit data in an Excel spreadsheet before putting it into SQL Server?

At Excel4Business, we are frequently asked to take exports from third-party software such as accountancy packages, and manipulate the data to create management reports. If you’re a large corporation and want to build a dashboard in Power BI, you will want re-export the data to SQL Server. Both processes can be automated and, in both cases, we can let the user make manual edits in Excel in the middle of the process.

Reasons to edit data can fall into two categories. If we take the example of accounting data, the category of expenses can be obscured by their GL codes. If you want to make a departmental spending chart, it’s more useful to categorise things in plain English such as ‘materials’ and ‘premises’. This is a data transformation that can be fully automated within Excel. Our consultants could make a click button solution to ingest spending reports and upload them to SQL. The only reason for using Excel would be for an administrator to define expense categories by ledger code.

The second reason to edit data before exporting to SQL Server is that the users are creating fresh data in Excel, such as where people need to comment on the data. Let’s imagine an internal project to monitor departmental spending. One method would be to look at every expense and categorise it as contractually obliged, essential to operations, non-essential and unassessed. Each item would be given a review date and the exercise repeated quarterly.

This is a classic use of Excel which our consultants can streamline as much as possible. The export to SQL Server and the creation of PowerBI reports for management becomes a separate step that can be triggered when a user saves the file, or when a user wants to publish their changes.

Will my Excel spreadsheet slow down if I need changes to be sync-ed to SQL server?

Opening a SQL connection and uploading data necessarily takes time. This will be a function of the underlying server, the quality of the connection, the database size, and how well targeted the data upload is. When working with a large database, best practice is to keep uploads to a minimum, track changes within the Excel spreadsheet, and only edit records that have changed.

Our Excel experts will start with the question of why you need to upload Excel changes to SQL at all. One reason is to prevent downstream users seeing stale data, such as when they’re using Power BI reports. This is only really an issue if management need live reports because otherwise it should be sufficient to update the SQL database when the Excel file is saved.

Where there are multiple concurrent users, we would recommend setting a bespoke Excel front end for SQL Server to be read-only. If the underlying data exists in a SQL database online, it’s nice if everyone can access file simultaneously without treading on each other’s toes. The downside to a read-only file is that users don’t have to save the file and, if lots of people have the file open, it’s more important to regularly sync changes.

Our consultant’s preferred approach to the problem is to save each individual record as it is added, changed, or deleted. If records get locked during editing, that also avoids a possible conflict between users. We would develop a pop-up VBA form in which users could change multiple fields, with a ‘Save’ button they can click when complete. This is a tidy method as it means we only edit one record at a time, users expect a short delay when clicking ‘Save’ and the pop-up form can present the data in a more readable format than in an Excel table.

We have come across situations where users want to edit multiple records at the same time but it’s generally a process undertaken infrequently by a database administrator and then both the venue and method of the change can be assessed on a case-by-case basis.

Can I prevent my Excel spreadsheet sending invalid data to SQL Server?

Yes, there are two good methods for this. The first is that you restrict the initial inputs into Excel. You can restrict the length of text inputs or force date entry using data validation. You can show the user a custom message telling them to enter something else. The second is that you don’t send any invalid data to SQL Server and then prompt the user to make necessary corrections.

The first method is very neat but often insufficient. If users can copy and paste data into the Excel spreadsheet then that overrides any data validation present in the cell being edited. If the source data is not being manually inputted but is coming from another piece of software then it’s not realistic to restrict the inputs.

Note that SQL Server will not let you put a number in a date field, or a zip code in a true/false field so there is no risk of invalid data ever entering a SQL database. The reason you don’t want to send invalid data from Excel to SQL Server is because it will result in an error or, worse, users may not realise their data isn’t making it from their keyboard into SQL Server.

Our Excel gurus’ preferred solution is to ensure any data being sent matches the requirements of SQL server at point of upload. Any invalid data can be highlighted for review. Although Excel can read field requirements directly from the database (through the ActiveX Data Objects library), it makes far more sense for you to identify data types within the Excel spreadsheet itself. Somewhere in the spreadsheet you will have to maintain a list of Excel columns and their corresponding SQL field names, so it makes sense to store data types in the same place.

Why use Microsoft Excel to feed a SQL database and Power BI?

Excel spreadsheets are extremely quick to develop and can be used to track business performance. When there’s a new KPI, you may not have specialist software in place to measure it. Excel can act as a bridge between the data you have and the data you need. Excel may be a stopgap solution, or it may be the only solution that can be implemented quickly to achieve a short-term goal. Excel fills in the gaps unfilled by your large enterprise databases.

Excel4Business was asked by the procurement team of a global shipping company to help track the size of customer invoices. The idea was that suppliers should be sending fewer, larger value invoices and, indeed, the company should be using fewer suppliers. It was a short-term rationalisation project that could not justify custom work on the main accounting package.

Our consultants used Excel as a bridge to add fields to the raw invoice data, grouping invoices into size categories and suppliers into different functions. The grouped data was perfect for building a dashboard from which the project could be monitored. The dashboard was a perfect use case for Power BI because the company was generating 10,000 invoices a month and would then be able to look-through that data in several different dimensions.

Note that you can feed Power BI directly from an Excel file stored in Sharepoint. The reason to upload the Excel data to SQL Server is that a SQL data table is not likely to move whereas Excel data connections are more easily broken. If there are delays in getting the SQL tables in place, a pure Excel solution is better than nothing.