Whether the weather BI hot...

Russian version

Small article on building relevant Power BI reporting. Not a technical one (there's enough technical hints and courses online, maybe next time), but rather tactical - how do you want to build your BI report to make sure you use the key advantages of PowerBI technology, especially its scalability. Based on my personal experience only, so would be happy if you could share yours in comments.

As always, it starts with a clear goal. You need to set the purpose of our report. Which business process should it support, is it a one-time analysis of some situation, a supporting report for weekly calls with your business partners, or something your business partners could use daily without you to understand the situation? Most importantly, you need to understand what potential managerial decisions might be taken based on the report. The key is understanding the audience and their needs.

Let's assume we want to sell clothes in Kyiv, Ukraine (my home city I'm missing a lot). One of the things we need to do is analyze the weather. It's a one-time analysis (meaning, we obviously can come back to the report multiple times, but we don't need a daily update of our data), and our potential conclusion might be that at some point of time it's worth selling warm coats, umbrellas, windstoppers, sunglasses, etc.

Now we need to understand the potential discussion flow and potential questions that might be raised during the discussion by each of our partners. This will help us understand what data we need to have on the screen. In our case, we'll probably start our discussion with temperature, and we'll need to know what percentage of time during the year the weather is cold, warm, etc. Our next question would probably be how often it is rainy/snowy. And, I would assume the wind speed would be one of the factors we'll be discussing for any season.

Next, I would strongly recommend to make a small drawing on a sheet of paper (or OneNote). No need to be Rembrandt or Mondrian, main task here is to understand how you want to represent the data and, consequently, which data will you need. You can immediately understand whether you have all necessary data, and if you don't - trust me and my sad experience: changing the drawing is a lot easier than changing the "almost-ready" data model.

Couple of useful tips for the "artists".

• Count on 4 charts max. Normally you don't need more, the challenge is to design them in such a way that clicking one chart would bring up relevant information on other charts - that's the power of PowerBI. For instance, clicking the "rain" would show you the number and percentage of days by month on the charts above and the percentage of windy days on the right.
• Think about the numbers your partner would like to know and bring them as textboxes or a small 1-row table somewhere at the top of your report.
• Don't forget about the slicers - you will need to use them, for instance, to understand what percentage of hot and cold days at certain month was when it was rainy and windy at the same time.
• Don't underestimate tables - great advantage of BI is that you can go from top level of analysis to the very bottom, id est, specific record in your dataset.
• Last, but not least: leave a lot of free space on your drawing - trust me, actual report will be a lot busier. If there's no way you can live with only 4-5 charts - think how to split your report in two pages.

Time to model. This article is not a technical one, so nothing fancy here. Just one piece of tactical advice: start with Excel. Scalability is one of the greatest advantages of PowerBI. You haven't shown your report to any of your business partners yet, and you might need to do a lot of changes to the model. Excel is the most flexible tool I know, so you want to use it first. At this point you get to "taste" the data, so naturally your report will be a lot different from your drawing. You may notice, for instance, that your data include several points of weather observations per day, and as we're discussing clothes that's normally worn during the day, you will most probably add the corresponding filter. This is the time for various splendid ideas to appear - like the scatter chart in the bottom-left. And I wouldn't recommend spending a lot of time for "beautification" at this point - colors, chart types, etc. Model is the key at this stage.

Our next step would be moving the report to PowerBI. There's several ways to do it. I would recommend to put the Excel file on OneDrive group, and then import it in as datasource in PowerBI - you immediately have your report in PowerBI, and also keep the connection to your Excel file - meaning, whenever you change the model in Excel, it is refreshed in PowerBI automatically. Plus, if your Excel model was connected directly to external sources, you'll have the possibility to schedule automatic refresh in PowerBI. This is the point when you can "beautify" the report by adjusting data colors and chart types - and in PowerBI you have much more freedom here than in Excel.

Now, most important stage: you show your report to key business partners and align on the changes you should make. Remember you're not perfect, you can't predict all your business partners' needs, and be ready to change your report completely, no matter how perfect you think it is at this point.

Let's do it together! What would you suggest to change? What conclusions would you be able to make from this report? Please, click the link below (or report picture above), check it out, come back and leave your comments here if you will!