3-Tier architecture in Excel

3-tier architecture in data analysis

When designing applications, a good practice is to build them up in three distinct tiers. By giving each tier its specific responsibility, an application gains a lot in maintainability, flexibility, and stability.

In this article we’ll take a quick look at what those tiers are, and which added benefit they bring.

What is a 3-tier architecture?

When talking about a 3-tier architecture, people commonly refer to the separation of data, logic and presentation. In the context of a common business application, this will be a database, the business rules and the user interface.

This setup allows for the user-interface to query and interact with the results of the business rules, which in turn read and/or write data to a database.

Data tier: This is the tier that contains the data required for the application to run and meet its requirements. Some applications only allow data to be read (e.g. data lakes and other data-sources used for analysis), some allow data to be written too (e.g. a database for a e-commerce application)

Logic tier: While the database contains data it almost always needs transformation in order by useful to the end-user. In other words, data needs to be transformed into information; lists of sales and product-prices needs to be aggregated into monthly revenue numbers.
Those transformation rules can rely on many parameters, which are all defined in the logic-tier.

Presentation tier: Once the information is created based on the data, it needs to be displayed in a format that the end-user understands. For a management report this could be a PDF file, for analysis this could be displayed in an interactive web page, while it might also be exposed through an API for other applications to consume this information.

Advantages of 3-tier approach

The advantages of such an approach are multiple:

Flexibility: in multi-user environments, not every user has the same approach or the same requirements on data. By separating the visualization of the data, each user can filter, pivot, or aggregate the data as they seem fit. This allows every stakeholder to have a different and relevant view on the same dataset, without having to copy over logic (which reduced maintainability) or data (which might result in data inconsistencies).

Reliability: Separating the logic from the presentation makes it easier to check the results of the business rules, without having to take any visualization issue into account. This will help automate the testing-procedure in more complex applications.

Robustness: Should the requirements of one of your tiers change; there should be no need to make (dramatic) changes to the other tiers; in an ideal scenario these should be interchangeable.

Single point of truth: Modelling the data-source in a separate tier will allow other application to connect to it. This will reduce the amount of data deduplication, which, in turn, will lead to less stale and erroneous data.

3-tier approach in Excel

You can leverage the advantages of a 3-tier architecture in Excel by using external data-sources (CSV, other Excel files, APIs, SharePoint lists, etc.) and Power Query. This will allow you to decouple the analysis (cleaning, transforming, aggregating, etc.) of the data from its source.

Should the source be updated, which of course happens more often than we would like, there will be no need to rewrite or update the analysis; the results will automatically take the updated data into account.

By executing the analysis in Power Query, you’ll remove all worksheets in your workbook that only contain intermediate results, which are not relevant to users and only clutter the workbook (hiding worksheets is just a workaround). Your workbooks will be more focussed and easier to interpret, as only relevant information is displayed to the user.

The visualization itself can happen in different worksheets or even in different workbooks, where in turn the data is imported by Power Query. This last option will even allow you to only share the results of your analysis through a read-only URL (from any file-sharing platform as Dropbox, Google Drive or OneDrive), increasing the security and reducing the risk of data-tampering.

Using Power Query has a few additional benefits that solve some of Excel biggest headaches:

Even if Excel has more advanced features as lambda functions and the ever-so-underused named ranges, there are still Excel files out there using this kind of formula: IFERROR(IF(C7>1000,110,0)+IF(C8>12,300,40)+VLOOKUP(A2,C50:C150,2,TRUE)/MAX(A4,A5,A6),””). Which is unreadable for anyone but the owner of the Excel. Power Query allows you to alter data, step-by-step, in a clear editable overview:

There are still Excel files using the VLOOKUP-function (instead of the more powerful, flexible XLOOKUP), these can be replaced by Power Query merges which allows you more flexibility and avoids the over-used “IFERROR(VLOOKUP(),””)”.

Should the input data change and contain more (or fewer) rows than previously, there will be no need to alter the number of rows in the worksheets containing intermediate records, as Power Query will automatically apply all transformations on the available data-rows.

Conclusion

Having a 3-tier architecture in your Excel-files, allows you to leverage productivity gains in a context where data changes fast and where many stakeholders’ views need to be tailored.

By not having to update big parts of the logic whenever the underlying data is changed or the visualization requirements are altered, you can focus on more important things than breaking your head over an unreadable old-school VLOOKUP-function.