Using a database to track medical trials

A few years ago, I was approached by a company that undertook medical trials in the area of cognitive conditions such as dementia to see if I could improve their forecasting process.

Over time they had developed a very sophisticated spreadsheet to predict the number of patients they would be treating in a particular month. I had to admit it was a very comprehensive spreadsheet due to the complex nature of the medical trials. In particular :-

  • They conducted trials on a regular basis (say every 3 months)
  • Each trial had several stages and at each stage, only a proportion would be accepted to go forward. There intervals of up to 60 days between trials and these durations varied at each stage on a trial.

So if the company wanted to calculate the number of patients expected to attend trials in a particular month (and the revenue) it meant they had to go back over each trial that could still have patients for and sum over all these values.

The challenge

My client asked me if there was a way of making the spreadsheet more user friendly. In particular:-

  1. The formulae in the spreadsheet were extremely complicated, meaning that only an individual who was extremely proficient in Excel could make changes
  2. There were multiple versions of the spreadsheet to manage the various different scenarios
  3. The spreadsheet was becoming very slow to operate.

The solution

I realised straightaway that they had to migrate from excel into a database solution as the only way of making their forecasting process more efficient. We built them a forecasting system using the platform Filemaker Pro. The benefits delivered by the solution were:-

  1. You no longer had to amend the spreadsheet (which entailed modifying / adding several new rows / columns) when adding a new trial. Instead the user just had to press a single button and the trial was created.
  2. There was no need for the user to have extremely advanced excel knowledge when amending the spreadsheet.
  3. The database provided greater flexibility in modelling the predicted demand as it could handle scenarios that excel could not handle easily :-

a) multiple locations of the same trial

b) allow a trial to be modified easily after it started

c) have an infinite number of trial stages on a partticular trial

/0 Comments/by

Our 5 top database design tips

Introduction

For many companies a database is a necessary investment – but many organisations struggle to implement them successfully. Fingertips Intelligence has several years of designing and deploying systems for our clients in different vertical markets. Here are some essential tips that we believe lead to successful database design. We’ve also included soime case studies where we have helped clients redesign their existng systems.

1.Think about your business

A database is a business tool and needs to fit your business. It may seem obvious but if the database design tie up with the way your business needs to, it then it is useless.

2.What business processes are important to the organisation

Give a lot of thought to the processes that make up your business and how they operate – In particular could they be improved through having a central database system. For example an invoicing process be reduced to a few simple clicks:-

  • Create an itemised invoice
  • Have the customer details appearing on the invoice
  • Generate the next invoice number
  • Email it to the customer
  • Send a copy to the accounts system

3. A business dashboard

Incorporate a dashboard into the database that shows all potential problems at a glance

  • Overdue invoices
  • Items that need to be re-ordered
  • Orders that are in production / about to be delivered

4. Information Sources

Consolidate distinct information sources – and create simple but effective business intelligence e.g :-

  • Before you might have had different spreadsheets showing customers and their orders – now a single comprehensive database can show you which customers are buying which products
  • Suppliers and stock levels – are separate parts of the organisation using their own suppliers to purchase the same product ? Using a single database can identify this and enable you to consolidate your purchasing

5. Management Reports

This is one of the most important aspect of a database – to be able to provide management with effective business intelligence at a click of a button. If a database has been well designed then these reports should be easy to create.

Case study one – A performing arts charity

Filemaker solution for Montage Arts

Helping this charity win a £10k award

In this case study, we describe how we helped the Montage Performiing Arts charity improve their management reporting by overhauling their database design. They already had a filemaker solution but it several years old and had become difficult to use.

Here’s how we improved the system:-

  • Built a better user interface so that it was considerably easier to use
  • Implemented a search facility so it was easy to find a locate family within the system
  • Redesigned the data hierarchy so that the data flowed logically – Families, Children, Terms and Courses attended by children in each term
  • Put click through buttons on the screen so that it was possible to drill down on the data
  • Allowed them to add new terms – so that they now had one single database. Previously they had to start a new database at the beginning of each term.
  • Gave them the facility to add further courses
  • Designed a complete set of management reports that produced all the required information at a click of a button

Following our work, the charity were able to successfully apply for a £ 10k grant. You can read more about the project here

Case Study 2 A Personal Car Management Service

Our client was Spoke360, who provide a personal car management service in Chelsea, London. They used a filemaker database to manage their business in partticular to track the status of their clients cars’. However, they felt that they were not getting the best use from the system and in particular it was difficult to extract data from it.

What was our client looking to achieve?

Our client was looking to make his business more efficient by having information more readily available :-

  • Easily see when key dates such as MOT due dates, service dates and road tax dates were coming up
  • Get advance notification of when members renewal dates were coming up
  • Become aware of when adhoc events were due
  • Organise the data much more effectively
  • Make better use of Filemaker’s features such as the web browser

How did we work with our client to achieve their desired objectives?

We had an initial discussion with the client in which we listened to their problems with the existing system, and their aspirations. It was clear that they were looking for some quick fixes rather than a complete rewrite of the system.

We gave them some initial thoughts on what could be acheived by simpy tweaking their existing Filemaker system:-

  • Having a dashboard that would show key information at a glance (M.O.T dates, service due dates
  • Having a search bar on each screen so that they could easily find client /vehicle details
  • Incorporating a web browser which linked to google maps, so the location of each client could be seen
  • A button bar so that they could navigate around the system easily
/0 Comments/by

Database design for Dummies

I was reflecting on the number of times that I have heard the phrase “Well I will provide you with the data and it can’t be that hard to build a database”. In my mind (apart from the indignant rage that this causes) this the same as expecting a plumber to turn up with your new boiler and radiator and having a new central heating system ready in a matter of minutes.

So I thought I would put together a list of the steps that I actually go through when doing database design so that we can build your system. At Fingertips Intelligence we build the majority of our platforms in Filemaker Pro.

1. Thinking time

At the end of the day, I am actually a business analyst / database developer. I know very little about my clients business. Given the diversity of my clients (I’m currently working for a National Firm of Appliance testers, a Chartered Accountants and a Sales Coach) I need to get to grips with their business problem. So the first stage of the database design is always thinking time. I’m oscillating between:-

  • Asking an excessive amounts of questions (which of course the client either loves or loathes). You’d be amazed how many times asking a basic question can provoke such rage with a client.
  • Having those Eureka moments when things fall into place
  • Having those non-Eureka moments went things fall apart
  • When I’m stumped and need some clear thinking time
  • When I’m absolutely stumped and am driving myself up the wall trying to find an elusive missing link

2. The client is virtually lying to me time

So I’ve done a lot of the premilinary database design work and I am about to get to building it when I think about the stuff that is going to break the system.

I will ask the client for example “Does the list of products ever change ?”. The answer from the client is “Absolutely not – it is a definitive product list”.

And half way through the project build it transpires that there is an extra set of optional products which does change. Hence in my database design I need to try and work out how this will happen,

So this tranche of my time is devoted to seeing what information the client thinks is not important to telling me, but in fact is fundamental.

3. Creating the Database Tables

So now get down to the fundamentals and start designing / building the underlying tables. Obviously there are the obvious tables such as customers and orders. However there can be some esoteric tables such as user names or even price history, where each time the price changes on a product it needs to be recorded. Some of my database solutions have over 25 tables.

4. Database Components

Having worked out the database tables, then I start to think about the components. So for example a customer will have the first name, last name, mobile number, email address etc. Most tables will have a minimum of 20 components. However in more sophisticated systems I have had over 300 components.

5. Relationships

This is where the fun can really start as I start thinking about how the various components can link up to each other. So ensuring that there is a relationship between the customer and all his orders. This will extend as each order will have order components that will feed from a master product list. Fortunately Filemaker makes it comparatively easy to connect up tables up using their visual interface.

6. The Data

So now I can go back to the data that the customer supplied me with and import it. You can bet that it will be in exactly the required format… In fact here are some of the things can go wrong:-

I will have asked for the data in a spreadsheet but it will be supplied as Word
Even if it is a spreadsheet, it will be formatted e.g instead of seperate columns for First and last name, they will be as one column and i will ned to split them out.
The dates will be in a non standard format e.g 12.07.2010 rather than 12/7/2010.

7. The User Interface

Naturally every customer wants an easy to use interface that is simple to follow. The things that they fail to realise are:-

  • It is not a single interface – it is several interfaces (or in Filemaker Pro layouts) that interact with each other. All of these need to be individually created.
  • Further it is never the first version of the layout that looks good – but rather each one needs several versions as I refine them continuously
  • And none of the layouts are stand alone, if I change one, it might force me to make change on several others
  • Having designed all the layouts, the client will want their own branding which will mean revising all the layouts

8. Making the database actually do something

Yes the database needs to do a few things. In Filemaker (and in other systems) this is doing by writing scripts. So there can be several scripts each doing a bit of the overall functionality

Add a new customer / order /product etc
Produce a report
Email a report

However as well as writing a script, it needs to be tested. So I test as I go along, and then when it is complete. Further you need to ensure that the script is idiot proof – what happens if the end user does not supply all the data that is required for the script. This means that there has to be comprehensive testing of the script.

9. Security

At its simplest level this means having seperate log on details for each user. However, it can often mean a lot more – in particular allowing certain parts of the system to be restricted. For example only certain users can view pricing data.

10. Testing

This is the most misunderstood aspect of the system design. Here are some examples of the testing that needs to be undertaken:-

The most important test is that I have understood the user requirement thoroughly. The majority of my clients have practically no systems experience other than facebook/linkedin usage. So I need to demonstrate that I have done this by regularly meeting with the client and show them progress. This gives both sides a chance to give feedback.
The client needs to undertake their own testing to check that they are happy with the system functionality.
However as part of my own role, I need to test from the ground up – look at every button / layout and script and ensure that they behave in the correct manner. This has to be done both at the microscopic and macroscopic level – the output from one part of the system needs to feed into another.

/0 Comments/by

How process automation can improve your sex life.

,

Yes in this blog I’m going to prove that process automation in your business can improve your sex life. This statement is actually based on a case study that happened several years ago.

The Challenge

My client was an international finance company based in London and Krakow (Poland). They had two directors – one based in each location. The London director looked after sales, whilst the Polish director foused on service delivery.

Once a month, the two directors held a meeting in which they reviewed the business performance. Prior to the meeting, the directors undertook a manual reconciliation process that compared the actual performance with the expected performance. However this was:-

  • an incredibly time consuming process taking 8 hours
  • invariably wrong due to the amount of data entry involved

In addition, as the process was so cumbersome it created considerable conflict between them. Indeed the business was stagnating because of the lack of having clear understanding of the numbers.

How we acheived process automation

Fingertips Intelligence implemented process automation by consolidating the existing spreadsheet systems into a single spreadsheet so that all the data flowed through the workbook. This was in contrast to the original process where the data had to be entered several times – which invariably resulted in errors.

Following the process automation, the updated spreadsheet:-

  • was instantaneous – immiediately saving them 8 hours a month
  • gave them an accurate picture of the business
  • contained graphs as well numbers so they could easily see what was happening on their business
  • allowed them to focus on growing the business rather than arguing with each other.

And Finally …

Having eliminated a very manual and cumbersome process, through process automation the directors were able to see the true state of the business and focus on growing it steadily rather than arguing with each other. Consequently they brought the business back from the brink, and turnover improved.

So why is an automated spreadsheet process benefical for your sex life ?

Well once they automated the most tedious part of their business, they weren’t so stressed. However as the turnover increased, the directors wives were a lot happier ….

/0 Comments/by

Dynamic Charts

,

Introduction to Dynamic Charts

Most excel users know how to create Excel charts by selecting a group of cells and clicking on one of the chart designs at the top

.

 

In this case, the chart is easy to produce because we only have one row of data covering just 10 months. However, what about if we had the data below :-

We have 17 rows of data and in fact the data goes up to 2019, so there are over 60 columns of data. The initial temptation might be to draw 17 graphs – which would probably result in either “Hell with Excel” or analysis paralysis.

However, Excel offers another solution – dynamic charts – where you can select the data item (e.g actual turnover) from a dropdown, and the chart updates with the values from the relevant field:-

So In the chart above, the user can select up to three options and the chart will pull through the values automatically. This is another big advantage of dynamic charts – it is very easy to do adhoc comparisons of data.

In addition, the solution permits the user to select which year they want to view. Hence if we have  several years of data, and only want to watch a snapshot, the user can select the relevant year from the dropdown. We have set it up so that the user can compare the selected year with the previous year.

How to create a dynamic chart

The key to creating a dynamic chart is the way that the data is set up. In particular it should be a single tranche as per the diagram above. One thing I would recommend is to extend the time period so that it includes say the next 3 years from the outset:-

 

This means that the person doing the data entry won’t have to come back to you each year to update the formulae.

There are three key stages in setting up a dynamic chart:-

  1. Creating the data entry area – where we specify the year and drop downs for the values.
  2. Setting up a mini data table – which uses the values from the drop downs to pull the data from the main tabl
  3. Set up the graphs using the values in the mini data table.These are explained in more detailed below.

Step 1 – Creating the data entry area

In this stage, we set up the interface so that the user can specify the years and create the drop downs. Note for simplicity, we will keep everything on the same sheet.So in cells B28,B29 and B30 enter the values Year, Option 1 and Option 2 as per the screenshot below:-

 

 

For simplicity, we just going to do a dynamic chart that allows the user to compare two values. However, it can easily be extended to have additional values.

The next step is to set up a couple of named ranges called “Graph_Options” and “Years” respectively:-

Named ranges can set up via the formulas option on the main menu. In this case,the named range consists of the values in the range B6:B22 – which are the possible options for the graphs.We also need to set up one for the years. In this case we need a blank area of the sheet – which the user won’t see (I often use a separate sheet) and type in the consecutive values for the years:-

 

Observe that we start the values for the year from 2015 rather than 2014,as in the dynamic chart we are comparing one year with a previous year. So when we select 2015, the previous year is 2014.

Now go back to the left hand side of the sheet, and use data validation to restrict the values that can be entered in cells D28 (for the year) and D29 & D30 for the graph options:-

 

Then use the drop down to select the values. For now we will select the values 2015, Actual Turnover and Forecasted Monthly Turnover:-

 

 

That completes setting up the data entry part.

 

STAGE 2. Setting up a mini data table – which uses the values from the drop downs  to pull the data from the main table.

The first part of the data table is to create the monthly timeframe. Using the date function, we enter the formulae above in cell D34. This sets up a link to the 1st of January of the selected previous year. So if cell D28 has the value of 2015, then the formulae above gives the value 1/1/2014.

We now need the next 23 consecutive months in our formulae. Excel’s EOMONTH formulae can be used to specify the end of a month whose value is stored in another cell. So for example, if we have the date 23/04/2015 in cell C12 then putting the following formulae in D12:-

D12 = EOMonth(C12,0)

Will return the value 30/04/2015 – the end of the current month. And if we want the first of the next month we just add one:-

D12 = EOMonth(D12,0)+1

Which returns 1st May 2015.  This is how we start to create the time line for the graph :-

And we now need to have a slightly different formulae in the adjacent twenty two cells to get the full time line for the dynamic chart:-

 

And now we drag this third formulae to the right to complete the time line – which should be 24 columns showing adjacent months.  Altering the value in D28 will automatically adjust the values in the timeline.

The next step is to incorporate the two axis into the data table. These are just cell references to the values in cells B29 and B30. So in cells b36 and b37 put “=b29” and “=b30” respectively:-

 

The next step is the most challenging – populating the mini data table using the values from the axis and the timeline. It uses a combination of the OFFSET and MATCH functions to do this. The OFFSET function allows to return cell values relative to an “anchor” cell. The MATCH function returns the position of one value within another range.

Enter the following formulae in cell D36 – which will be the intersection of the first month and first axis option in the datatable:-

=OFFSET($C$5,MATCH($B36,$B$6:$B$22,0),MATCH(D$34,$D$5:$DF$5,0),1,1)

And now drag this formulae across both axis of the time line – to complete the values in the datatable:-

 

 

If you use the dropdowns in cells D28, D29 and D30 then the data table should update automatically.

  1. Set up the dynamic charts using the values in the mini data table.

This is the final part of creating the dynamic charts – actually creating the chart. Highlight the first row of the datatable and insert a standard line chart:-

 

This is the first axis in our graph. We can click on the graph, and select the data source to specify the second axis:-

Closing the pop up, we now have a dynamic chart – that allows us to select two rows from the main data table and compare.

 

/0 Comments/by