Use Case: How to build a KPI Excel template with SQL Server

In this article, we’re going to show you how to build a KPI Excel template using SQL Server and an Excel Add-In. Many organizations collect performance metrics related to aspects of their business and then use KPIs to measure and track performance against targets. However, the process of collecting the data is often a manual one and can be time consuming – the result is that KPI data quickly becomes outdated or has too many gaps.

The example we’ll work through shows an easy way to build a KPI Excel template in conjunction with the robust SQL Server database management system and the SQL Spreads Add-In for Excel.

Use Case Background for KPI Excel Template

The example that we’ll use relates to the collection of KPIs for ACME Health Group, a fictional healthcare company that manages 50 hospitals and healthcare centers across the US.

There are many best practice KPIs to measure and track in the healthcare industry, but for this use case, we’ll just look at two:

KPI	Type	Purpose	Definition	Target
% Hospital bed occupancy rate	Operational	Used to indicate the maximum volume of patients that can be admitted to the hospital, representing the hospital inpatient capacity, and also to indicate if beds are under-utilized.	Measures the percentage of beds that are occupied by inpatients in relation to the total number of beds within the facility.	85%
Staff-to-patient ratio	Operational	Used to  help healthcare providers assess their resource management strategies and ensure that they have enough staff available to provide quality care to their patients.	Measures the number of staff members available per patient in a healthcare facility.	25%

In order to calculate these KPIs, we need each of the 50 healthcare facilities to collect the following metrics on a regular basis:

• Beds in Use    
• Total Beds       
• Total Staff        
• Total Patients

For this example, we’re going to assume that the managers or their delegates will collect these metrics. In some cases, a single person may be responsible for collecting metrics from multiple hospitals or healthcare facilities, so we need to cater for this in our solution.

Finally, we’re going to specify that these metrics need to be collected every week – in reality, metrics such as Beds in Use, Total Staff and Total Patients would be collected daily (although the reporting itself may be on a weekly basis).

KPI Excel template design

The diagram below shows the database schema for our solution.

The following tables are in the database:

• FactMetrics : this table is where the metrics themselves are collected at each location for each period.
• DimLocation : this is a list of the hospitals and healthcare facilities that are managed by ACME.
• DimRegion : this is a list of regions that ACME uses for management and reporting purposes.
• DimKPI : this is a list of KPIs, include descriptions, targets and low/high comments that can be used in dashboards and other reports.
• DimDate : this is a list of the reporting periods, which in this case is weeks.
• metaUserAccess : this table is used to filter the data that each user sees when they are completing their weekly report.

In the next section, we’ll go through the steps required to build the solution – the data will be stored in SQL Server and Excel will be used as the front end. The SQL Spreads Add-In for Excel is used to synchronize data between SQL Server and Excel and to make it easier for users to filter, view and update the data.

Building the KPI Excel template

To build the KPI Excel template, we need to complete the following steps:

1. Create a database called KPIDemo
2. Create tables in SQL using SQL Spreads
3. Create computed columns for KPIs
4. Configure the KPI Excel template in SQL Spreads

To follow along, download the example Excel file containing the tables that need to be created in SQL Server.

You’ll also need to install the SQL Spreads Add-In for Excel. You can download the SQL Spreads trial and then follow the installation instructions.

Step 1: Create a database called KPIDemo

The first step is to create a database in SQL Server called KPIDemo. You can create the database in Azure SQL or on-premise SQL Server.

Step 2: Create tables in SQL using SQL Spreads

To create the tables in SQL Server, we’re going to use the New SQL Server table from Excel feature in SQL Spreads. This is a great way to quickly create tables in SQL Server. You can download the example Excel file if you have not already done so. This document contains the tables required – one on each tab.

Create the DimLocation table

Go to the DimLocation sheet in the Excel workbook and click in the table (note that your data needs to be formatted as an Excel table). Now go to the SQL Spreads menu and click on SQL Server Tools > New SQL Server table from Excel

In the Create SQL Server Table dialog box, enter the name of the table, DimLocation, and specify that it should be created in the KPIDemo database. SQL Spreads will infer the data type for each of the columns based on the formats in the Excel table, but you can change them if needed.

In the Settings section, we’ll leave the Insert Data and Add Auto-incrementing ID column checkboxes enabled (this is the default), although we’ll change the ID column name to ‘LocationKey’.

When you click OK, the table will be created in SQL Server and populated with the data from Excel.

We can verify that this worked by checking in SQL Server Management Studio:

We can now go ahead and create the other tables in much the same way.

Create the DimRegion table

The DimRegion table lists the regions in which a hospital is located. We can create the table in SQL Server in exactly the same way as the DimLocation table.

Create the DimDate table

The DimDate table contains the date periods (weeks) for our KPI solution. In the DimDate table in Excel, I’ve used several formulas to provision the data, and we can now use the New SQL Server table from Excel feature to create the table in SQL Server complete with all the data.

Note that when we create the DimDate table in SQL Server we don’t want to create an auto-incrementing ID column because the ID is generated in the Excel sheet using a formula (a concatenation of the Year and Week columns), so this option should be unticked:

Create the DimKPI table

The DimKPI table contains the definitions of the KPIs.

This table is created in the same way as the DimLocation table.

Create the FactMetrics table

The FactMetrics table is where the users will enter the metrics on a weekly basis for each location. In the Excel table, I have provisioned 52 rows (one for each week in 2024) for each of the locations:

This table is created in the same way as the DimLocation table.

Create the metaUserAccess table

The metaUserAccess table is used in SQL Spreads to filter the data than can be viewed and updated in Excel by the users.

The UserLogin is the user’s Windows Login in the format Domain\UserName.

When creating the metaUserAccess table, make sure you allow nulls to be entered for the Region and Location columns.

Step 3: Create computed columns for KPIs

We now have all the tables that we need in SQL Server, but there are 2 computed columns that we need to add to the FactMetrics table – these are for the actual KPI values themselves:

• % Hospital bed occupancy rate = Beds In Use / Total Beds
• Staff-to-patient ratio = Total Staff / Total Patients

Here is the SQL code to add the 2 computed columns:

KPI_BedOccupancy AS 
CASE WHEN [Total Beds] = 0 THEN 0 ELSE 
CAST([Beds in Use] AS DECIMAL(18,4)) / CAST([Total Beds] AS DECIMAL(18,4)) END PERSISTED,
KPI_StaffPatientRatio AS 
CASE WHEN [Total Patients] = 0 THEN 0 ELSE 
CAST([Total Staff] AS DECIMAL(18,4)) / CAST([Total Patients] AS DECIMAL(18,4)) END PERSISTED;

Next comes the fun part where we can build the KPI Excel template using SQL Spreads!

Step 4: Configure the KPI Excel template in SQL Spreads

Let’s first look at what the final solution looks like:

Create a connection to SQL Server from Excel

Open a new Excel workbook and click on the SQL Spreads menu and click on the Open Designer button. Initially when opening up Excel after the SQL Spreads installation, a dialog will pop up prompting you to connect to Microsoft SQL Server. Here you can enter the details of the SQL Server where you created the KPIDemo database.

Once you have connected to SQL Server you’ll see the available databases – expand the KPIDemo database and then click on the FactMetrics table. This will create a connection to the table and import the data into Excel.

In the screenshot below, you can see the data exactly as it is stored in SQL Server – we’re now going to configure the workbook to make it easy for users to view their data, make changes and write the data back to SQL Server.

Basic Column Configuration

The first thing that we’re going to do is change the LocationKey column so that it displays the related Location name instead of the Key value. Click on the Lookup icon for the LocationKey column – this will open the Lookup value for column dialog. Here we are going to specify the lookup table from which we want to get a value – in our case it is the DimLocation table.  In the Text to Display section, we need to select the Location column.

In the screenshot below, you can see the Location names from the DimLocation table:

We can also change the heading for this column so that is says Location instead of LocationKey. This is as simple as typing the new name in the Header Text section on the Columns tab of the SQL Spreads Designer pane.

Since the KPI_BedOccupancy and KPI_StaffPatientRation values are already calculated in SQL Server, these columns can be set to ready only:

Create a Tree Filter

A Tree Filter makes it easy for users to filter and view the SQL Server data that is displayed in their Excel workbook. The Tree Filters use SQL queries to display the list of items that the users can select from.

Click on the Setup Tree Filters button and then click on the New button:

In the Edit Tree Filter dialog, enter the name of the Filter – “Location”, and then enter the following code into the query pane:

SELECT
   loc.Location AS [Text],
   loc.LocationKey AS Code, 
   reg.Region  AS Group1 
FROM [KPIDemo].[dbo].[DimLocation] loc
LEFT JOIN [KPIDemo].[dbo].[dimRegion] reg
ON loc.Region = reg.RegionKey
ORDER BY reg.[Region], loc.[Location]

The SQL Spreads Knowledge Base has an article showing you step-by-step on how to Create a Tree filter using SQL queries.

Click OK. Now, the Setup Tree Filters dialog will display the Location filter. Finally, select the Enable Multi-Select option so that users can select more than one item at a time.

Now we have a Tree Filter displayed on the left hand side of the sheet:

Let’s add one more filter so that the users can also select one or more reporting periods. Click on the Setup Tree Filters button and then click on the New button. This will create a new filter called Date.  Here is the SQL query that you need to enter:

SELECT
   [DateKey] AS [Text],
   [DateKey] AS Code
FROM [KPIDemo].[dbo].[DimDate]

Before we can use the filters, there is one more step to complete – we need to configure the column filter in the SQL Spreads Designer. Click on the Filters tab in the SQL Spreads Designer and in the Filter Type dropdown, select Custom Filter.

In the pane at the bottom, enter the following code:

[FactMetrics].[DateKey] IN (@Date_Code)
AND
[FactMetrics].[LocationKey] IN (@Location_Code) 

Click Save. Now go to the Tree Filter and select the Central region and the first date period. You can see that the FactMetrics table only displayed rows for the hospitals in the Central region for the first date period.

Add row-wise access to the Tree Filter

It is often the case that people are responsible for managing sub-sets of data, so they should only see data that is relevant to them. We can modify the query for our Location tree filter to reference a simple access table.

In the SQL Spreads Designer, go to the Database tab and select the metaUserAccess table. In this example, the metaUserAccess table is in the same document as the tables. Usually, this will not be the case, and it will be in it’s own document for security purposes.

The metaUserAccess table includes the following columns:

• UserLogin – this is the user’s Windows Login in the format Domain\UserName
• TableToAccess – this should be either DimLocation or DimRegion. If you enter DimRegion, then the user will have access to all locations in that region.
• Location – if the TableToAccess value is DimLocation, then you should enter a Location here, otherwise, leave blank
• Region – if the TableToAccess value is DimRegion, then you should enter a Region here, otherwise, leave blank

To make this table easier to use, we’re going to add some lookups for the Location and Region columns. Go to the Columns tab and click on the Lookup icon. Then select the lookup values in the same way as we did before. Here is the dialog for the Location lookup:

We can also add a simple list lookup for the TableToAccess column to prevent any typos. Click on the lookup icon for the TableToAccess column and click on the Lookup in list tab. In the list pane, type DimLocation and DimRegion and click OK.

In the metaUserAccess table we can now add some data. In the screenshot below I have two rows:

• The first row indicates that I have access to see the Serenity Medical Center
• The second row indicates that I have access to all the locations in the Central region

Once you’ve added rows to the metaUserAccess table, make sure you click the Save to Database button. This will write back the changes to SQL Server.

Finally, we need to modify the query for the Location tree filter so that it takes into account the records in the metaUserAccess table. Click on the Setup Tree Filter button, select the Location filter and click on the Edit button. Copy the new query below and paste it in place of the existing query and click OK.

SELECT
   loc.Location AS [Text],
   loc.LocationKey AS Code, 
   reg.Region  AS Group1 
FROM [KPIDemo].[dbo].[DimLocation] loc
LEFT JOIN [KPIDemo].[dbo].[metaUserAccess] acc
ON loc.LocationKey = acc.Location
LEFT JOIN [KPIDemo].[dbo].[dimRegion] reg
ON loc.Region = reg.RegionKey
WHERE acc.TableToAccess = 'DimLocation'
AND acc.UserLogin = @WindowsUser
UNION
SELECT
   loc.Location AS [Text],
   loc.LocationKey AS Code, 
   reg.Region  AS Group1 
FROM [KPIDemo].[dbo].[dimRegion]  reg
LEFT JOIN [KPIDemo].[dbo].[metaUserAccess] acc
ON reg.RegionKey = acc.Region
LEFT JOIN [KPIDemo].[dbo].[DimLocation] loc
ON reg.RegionKey = loc.Region
WHERE acc.TableToAccess = 'DimRegion'
AND acc.UserLogin = @WindowsUser 
ORDER BY reg.[Region], loc.[Location] 

This query joins the Location and Region tables to the metaUserAccess table.

The screenshot below shows that the items in the Location part of the tree filter only contain the rows that I am allowed to see – as defined in the metaUserAccess table.

Enter data and write back to SQL Server

We can now enter data into the FactMetrics table for one of the periods. See below for some example data that I added:

When you have added the data that you need to, click the Save to Database button (this will write back the data to SQL Server). Then click on the Refresh from Database button to see the calculated KPIs.

Recommendations for a reporting tool

Managers and administrators can now use the KPI Excel template to enter performance metrics for their locations/regions on a weekly basis. We recommend using a reporting tool like Power BI, or similar, to get the most out of your analysis by creating a dashboards.

In an upcoming article, we’ll show you how to build a dashboard in Power BI to display current and historical KPI values. This dashboard will use the data stored in SQL Server.

Summary

This article has described how to create a KPI Excel template using SQL Server, Excel and the SQL Spreads Add-In for Excel. In the solution, SQL Server provides a robust relational database, whilst Excel is used as a front end to view data and perform updates to it. The SQL Spreads Add-In for Excel provides this functionality – the ability to easily connect to SQL Server and then view and edit data.

The collection of metric data for the calculation of KPIs is often the responsibility of business users across the organization who need a quick and easy way to enter the required metrics on a regular basis. With this solution, an Admin user can create and configure the collection workbook in Excel using SQL Spreads, leaving those responsible for collecting the data with an easy and user-friendly interface in an environment that most are very familiar with – Excel.

For more information on SQL Spreads and how it can help you with your SQL data management, contact us or visit our website to download a free trial.