Lots of companies have done great without really paying too much attention to their data and its underlying processes. They have grown over time (and pretty well) without doing so. In this blog post, we take the example of a successful traditional retailer, it has grown a lot the last decades due to their rich product range and low prices. The company and its brand has stood the test of time. However, the internal processes and way of working have stagnated, and are becoming outdated.

Let us focus on some specific sales reports in the different shops from that retailer. The job of the analyst is to create a spreadsheet in Excel with a few graphs. Those spreadsheets are distributed around to other departments and get extra information added on, transformed and passed around for different purposes such as management reporting or replenishment of the stores. Every week, that spreadsheet is made again by the analyst, with the new weekly data. A way of working that probably sounds familiar for lots of organisations, especially in more traditional industries. However, it is not only repetitive for the analyst, but also dangerous for the organisation. In this article, we will further explain the downside of traditional reporting, and the way forward with Power BI.

Problems with spreadsheets

There are some major potential pain points with the traditional process. Below we highlight the most important pains that working with spreadsheets will inflict. The problems arise from the perspective of the analyst, but the consequences will directly and negatively affect the company.

1. Spreadsheets have limitations that make them unsuitable for some of the tasks we use them for, which causes them to become very large and slow. For example, an Excel file with hundreds of thousands of order lines and many sheets, columns, and tables with hundreds of formulas can become a spreadsheet where every action takes several minutes to do.

2. When we look at the process when working with spreadsheets. When distributing this Excel, it will end up with people who will struggle to understand what this Excel does and how it calculates the result. This is often because the process is not clear and easy to follow. Unfortunately that’s usually due to how spreadsheets work. A formula works by using cell IDs to refer to the different objects, so tracing the actual logic behind the formula becomes a struggle quickly. Especially, this makes complex formulas very hard to understand.

3. When analysts have to manually move formulas, enter values or paste the result of some formula somewhere else, they increase the risk of human error. As the old cybersecurity saying goes, humans are the most vulnerable part of the process. The computer will always compute a value correctly if the formula was right to begin with, but humans can and will make mistakes when copying that value at least once.

4. We also need to talk about data governance, and how doing everything in spreadsheets will cause problems. When copying data from one spreadsheet to another, and another,.. Passing it along with each copy and applying more transformations than copies, we lose track of the data source, the changes it has undergone, and the reasons for them.

5. Finally, from a human point of view, it is fair to say that using spreadsheets for everything is a lot of work, which takes a lot of time and is plain boring. By reducing this manual work, people will have more time to do deeper analysis, which will create more value for the business and be more interesting for the analyst. Although these spreadsheets can also be automated to some extent, with Macros, I would contend that those have limitations and more importantly mastering VBA is harder than learning the basics of Power BI.

Power BI to the rescue

A full scale migration from spreadsheet based work to a BI environment might be the best way to get automated and reliable reporting on the long term. But be aware that this requires some big changes and expenses. You would need a data storage system (eg. Microsoft Azure), an easy way to put data into storage (eg Power Apps), a BI tool like Power BI and a lot of time. It also means trusting that those new systems will work better than just using spreadsheets, which you may not be sure they do if you have never seen them in action. Hesitation is normal, its often good to only trust systems you have seen work yourself.

That’s why we want to show you the immediate benefits of getting started with Power BI. We are going to do that by showing the power of Power BI when replacing an Excel report. So, let's achieve some immediate results and demonstrate what business potential Power BI can offer.

Identify the spreadsheet to replace

The first thing that needs to be done is find the spreadsheet that could most benefit from automation. This spreadsheet should be one that is updated often, requires a lot of effort to update and uses an Excel or csv file as input. A good example is a monthly sales report based on a csv extracted from your CRM system.

A spreadsheet like this can quickly face the potential problems discussed earlier:

• It has thousands of rows of sales, it's complicated and hard to understand.
• Analysts are copying and pasting results from this file
• The spreadsheet is used into many other reports for marketing and sales

Now to Power BI!

In the next part I’ll give you the tools to translate a file I’m using a simple example for demonstrative purposes but this can usually be extended without too much extra hassle. The main points I will put in writing but for those that like to see the software actually perform its magic and get a more practical view you can watch the videos as well.

Loading the data

The first step to get our report build in Power BI is to load the data into Power BI. The data we are currently considering is one that could remind you of the output of a sales tool albeit a bit simplified. We aren’t going to do anything inside the spreadsheet itself. Partly because Power BI can do what we need and mostly because any manual work we do in the spreadsheet will need to be repeated when we update the data (with next months sales). We connect to this spreadsheet using Power BI, we shouldn’t need to change any of the data loading settings as Power BI can usually detect what the settings in the spreadsheet are.

Now we clean and transform the data, of course this process depends heavily on your data. In short, you should focus on any impossible values, making sure that Power BI knows your numbers are numbers and your dates are dates, and getting rid of any missing values that should not be missing by either replacing them with the values they should be or getting rid of the lines. After that our data is ready to be used!

Building the model

Well maybe the data is not entirely ready to be used, depending on how robust we want to make this report we are building. However, with a little extra work we can easily turn our data into a basic dimensional model. Dimensional models are a type of data model that uses fact and dimension tables.
Fact tables contain all values at the level of an event and keys to link to dimension tables. In our case it will simply contain the amount sold and the keys.
The Dimension tables contain all of the additional information like product information, dates etc and keys to link to the fact table.
In this example we will make a Calendar table and a Product table. Your first Power BI Model might also include things like a Customer table, a Location table (for the store it was sold from) or a wholesaler table, etc.

Using the keys we link these tables together using 1.n joins meaning that every one line in a dimension table can have an infinite number of lines in the fact table and every line in the fact table has exactly one line in the dimension table. A product can be sold multiple times but a single sale is only about one product. That is it for the model, on to the actual report.

Making the report

Now that we have a dimensional model we can build the actual report. For any necessary calculations we can use measures, a measure is equivalent to the formulas usually used in spreadsheets. But instead of being contained in specific cells, it is dynamically calculated when we use the measure in the report.
Once we have the measures that replace the formulas in our excel we can add it the measures to visualisations, from simple tables to scatter diagrams, bar charts and so much more. Every time we use this model we can reuse these measures so we never have to rebuild them when working on other reports based on the same output.
If any of the measures changes later on, it only needs to be adjusted in once to adjust it in all reports based on this model.

The Benefits

Of course, one has to learn the basics of a new tool to really see the benefit. Building a data model and Power BI reports are worth that time investment. Especially when we look at the benefits:

• Power BI is much faster at doing many calculations and will finish them sooner.
• Much clearer formulas! If you’ve used good measure and column names, a Power BI formula is much easier to understand than an Excel one and there are no manual steps to forget either that might only be known by the person who usually prepares the spreadsheet.
• Speaking of manual steps, there are (almost) no manual steps required to refresh the file whenever you need it, just change the source input file and you are done! So less time wasted every time the file needs an update.
• No more multiple spreadsheets, you can build everything directly on that one model.
• And most importantly, more time to do enjoyable, fulfilling work that advances the company instead of monotonous repetitive work that maintains the company where it is!

What will this cost?

Using Power BI to create these kinds of simple reports is very cost-effective for small businesses. Power BI Pro costs about 10 Euro per user. You only need one person who knows how to use the software well, as changing the reports once they are made is very simple and intuitive. You can also experiment with the software for free using your own data, so there's no harm in giving it a try. Maybe you'll discover some visualizations that you couldn't do in your spreadsheets and improve your reporting!

So get yourself some basic licences, eliminate parts of your repetitive work and create some quick business value! With the low cost of entry and relative ease of building basic reports, there is really nothing to lose.

Need an extra hand getting started with Power BI reporting, need help with getting more insights out of your data? Reach out, we are always happy to talk data!