6 steps to using EPS in MRDCL to gain an advantage
EPS stands for Excel Productivity Scripting. It’s…Read more
EPS stands for Excel Productivity Scripting. It’s a technique built into MRDCL which allows you to construct your own templates in Excel, so that a generic MRDCL script can read instructions stored in an Excel workbook/worksheet that will generate variables, tables or whatever else you want in MRDCL.
What this means is that MRDCL users have a tool at their disposal which allows them to produce anything that is laborious or complex to specify controlled by lists, settings or other keywords in an Excel spreadsheet. To use the template, the MRDCL user only sets a reference to the workbook and the worksheet and magically specifications that are laborious to script, repetitive or complex, such as variables, tables or other instructions can be automatically generated.
Let’s bring this to life with a simple example. It may not be the most beneficial or practical, but it gives an example of what is possible. Let’s imagine that researchers produce table specifications for the DP team so that the DP team know what tables are needed. Traditionally, DP staff would ‘translate’ these instructions into MRDCL script. With EPS, these instructions can be read in situ. The specifications might like something like this:
This example shows that you can easily train researchers to use this form to specify tables such that the DP team can just this spreadsheet without any alterations to produce the required analyses. You may note a few typical features of an EPS template in this example:
Of course, this example could be extended to plug in many more features or specification details.
This approach means that there is no duplication of effort. It also means that there is a clear, visible record of what is required, making it easier to look back on specifications if the project is repeated or part of a tracking study. Most importantly, it means that researchers are doing what they need to do and DP staff are doing what they are good at – this is typically defining variables that may be complex, tables that are non-standard and checking data issues and results. This template may not be appropriate for every company, but it shows how easy it is to design a system that will work for all sorts of data processing applications.
Having designed an EPS template in Excel, you will need to write the MRDCL script that reads and processes the template. To do this, there is a necessity to learn another set of tools within MRDCL. These tools are not particularly easy to learn, but there are only a few tools to learn, so once understood, they become easy to apply to other situations and templates. We have a good set of videos that provide learning to understand both the theory and the practice of implementing EPS templates. Without doubt, there will be benefits to learning these techniques.
EPS can provide this easy to understand interface for anything that is repetitive or complex. EPS templates are most commonly developed by one or two people to be shared amongst one or more users of MRDCL for use on a range of projects. They not only document specifications more clearly than a scripting language but make it easier to find and fix errors. EPS templates fall into two types – the more common type is used across a range of projects, but some can be project specific. Let’s look at each type.
Three examples are:
For this, I will give one example which reduced the time a client spent on a project by 15 times – yes, that’s reducing 30 days’ work down to two days. The project entailed have brands, sub-brands and pricing rates for each sub-brand which were liable to change from month to month. Each month, new sub-brands and pricing policies as well as offers had to be added to the MRDCL script to produce the required analysis. Occasionally, brands merged when there were takeovers. This required huge amounts of script that had to be edited each month. There was a large amount of recoding and merging of user data merging their purchase behaviour from an external database. EPS templates were built to manage the whole process. The task became a simple clerical task updating a few spreadsheets; the rest was automated from the EPS worksheets. It meant that rather than a highly-skilled MRDCL user managing the project, the project was managed by someone with good Excel skills, who was backed by the highly-skilled MRDCL user for about one hour per month on average.
To succeed with EPS, it is important to approach the task of implementation thoughtfully. Firstly, not all MRDCL users need to learn how to program EPS in MRDCL. Often, it is not the most talented MRDCL users that are best at building EPS templates, it is the person who can design tools well and think about where EPS can really benefit. Talking to colleagues and agreeing what tools would be of most benefit is an important starting point. Considering all the different variations that different users might want is better to program at the outset. Editing EPS templates can be difficult as the typical process is to develop an EPS templates, test it, document and then never look at the script that drives it again.
Keeping the EPS templates in an organised fashion and documenting what they do and how they work is important. Making the templates logical to use is worthy of careful consideration and sharing ideas with colleagues. Once in place, you can expect some huge benefits. As one EPS user told me that they spent one day developing an EPS template that has been used about 700 times saving about an hour each time – that’s almost 100 days.
If you need more help to get started or suggestions on how to learn EPS, please contact me, email@example.com. There are also a series of videos that take you through the learning process – covering why to use, when to use and how to use.