Energy Management by Microsoft Power Bi | Part 6: Lessons Learnt from lockdowns, Wrap-up

As discussed in the previous parts, Power Bi provides plenty of features to clean, monitor, and report your utility data. This article would be the last from this series and I would like to draw your attention to the baseload analysis and what we learn by looking at the consumption graphs.

On 17 Aug 2021, New Zealand prime minister announced the country enforcing lockdown followed by finding 1 new community case of Covid-19 with delta variation. The alert levels 4 and then 3 applied, forcing all the non-essential businesses and services to remain closed until further notices. Today that I am writing this, it has been almost 70 days that these restrictions have been put in place. Many businesses and commercial buildings had only a few hours after receiving the announcement to shut the energy users equipment hence leaving many of them running unintentionally during the whole lockdown period…causing not only extra cost for the business but also hundreds and thousands of kWh of energy and therefore emission. Below, are photos I took from Sylvia Park, the country’s largest shopping mall a night during alert level 4. The lights, sound system, and perhaps ventilations were running days and nights for no one! This is just an example of many across the country. And yet we are loudly talking these days about climate change and sustainability…

So, who will be going to pay for this? the shopping mall owners? the shopkeepers? the government? you and me? or the whole world? Yes! During my evening walks not only during the lockdown, but I also notice many buildings and malls and shops are shining unreasonably at night!

The amount of energy we use while no one uses a building or facility is called ‘baseload’ and managing this is a simple task so-called a low-hanging-fruit in energy management system implying a handy action plan to be taken without a huge effort. Let’s go back to the school (our example) and see how the fancy Power Bi graphs can help us understand this baseload and its reduction. The following displays the main site buildings usage in Aug. 2021.

What we are observing here? a sharp drop since 17 Aug. as expected due to the lockdown from 2400kWh to 900kWh a day. This means if no one uses the buildings this is the baseload would be still around 37% of normal daily usage. This has been almost close to the weekend load (look at those drops on days 7-8 and 14-15 which are Sat. & Sun.). Knowing that on a normal weekend, there are still people who use the buildings and some facilities, it appears that the usage in lockdown time is higher than expected as nobody has been allowed to get into the buildings according to the restrictions. The school has an energy management system put in place and they have been successful in implementing some good practices for example switching off unnecessary equipment and energy users after working hours. So the flat rate of usage could be optimistically due to the servers, emergency lightings, and cameras that would have been running all 247. The following is the sum of 24hour usage for Aug. 2021. This very flat rate (before and after the working hours) which has been also appeared in the previous months, proves this claim.

This is where having a good check metering system can be helpful to drill down to the users. The above data were collected from the retailer meter (revenue meter). The following illustrates the data collected from the check meters across the same site. Note in Power Bi we can add an interactive site map highlighting the selected buildings showing more details like where they are located and how big they are. I selected 4 buildings out of 13 which are being monitored by the check meters. They appeared to be large users.

Further analysis on this finding, can be really useful, for example, we know that the data server is located at one of these 4 buildings, so it makes sense that the user remains nearly the same as a holiday or weekend. I would not expect to see a higher usage in a building like ‘Technology and art”. Take a closer look! the lockdown daily usage is even bigger than a weekend! In the next step, I only choose and focus on two buildings that contributed to remarkable higher usages than an ordinary weekend.

Both selected buildings have been using more energy during the lockdown compared to a weekend day. They are not even hosting any data server so the question remains as to why their usage has gone up comparing the weekend before lockdown (days 14-15)? There might be some devices left on! Pay more attention! the red building has relatively a flat load while the yellow was fluctuating! knowing that none of these has been in use in all those days, more investigation is required. Next time when we go back to school, we can try listing the energy equipment in both buildings and have clear instructions to turn them off when another lockdown happens!!

In the other buildings, such as the boarding house where the students live, the lockdown drop is even sharper. Depicted below, from 18 Aug. which is the first day of lockdown, a 100kWh daily usage is a baseload. Apparently, for this facility, being on weekend does not necessarily mean a lower usage, instead, the occupancy rate does! Listing the energy users and grouping them to base users and variable users can be a good practice to further assess why some equipment is running all the time. Ask is it possible to switch them off or control them remotely when no one uses the facility?

The next figure shows the total baseload for a different month in the summer. A load of 200kW has been nearly in use from midnight to 6 AM. This 1200kWh per month is very similar to the same baseload in a winter month. It means the lighting or another use which are not affected by outside temperature, are the main contributing ones.

In many cases even including the school buildings, measuring the occupancy rate which is potentially an influential driver of the energy usage is not easy. We used to take the weekend load as the baseload of our energy baseline but we knew that people can still use the facilities during the weekend. Before the lockdown time, we never had a chance to monitor our fully vacant building/facility! Let’s say the bright side of Covid-pandemic is the opportunity of investigating our energy usage during the lockdown periods which has been long enough to learn from!

Summary:

There are many ways to interpret and analyze the energy/utility data if you manage to visualize them in meaningful graphics. What is important is doing these reviews systematically, discussing them with the team regularly, documenting the reasoning and findings, and consulting with energy advisers. If you have an energy management system, these practices will be central and inevitable parts of your routine tasks. The energy management system is still the most achievable effort towards reducing our energy cost and making tangible while strong steps towards abatement of emission and climate change.

In the end, I appreciate once again the Epsom Girls Grammar School’s manager, Bronwyn McGill, who granted me permission to use their data for these publications. I am also grateful for Simon Ross that I had the opportunity to work with him during my career at Economech Analytics. I hope you have found this series of articles comprehensible and informative.

Energy Management by Microsoft Power Bi | Part 5: Performance indicators

Managers would usually love to take a quick look at the overall performance indicators rather than spending time on worksheets, graphs, and other forms of showing historical usages. Performance indicators are essential when you want to simply and smoothly monitor how your business is going. In an energy management system, it is required to identify and adopt EnPi (energy performance indicators) to represent and reflect how energy is being used in your organization. Setting the appropriate and relevant EnPi does not depend on which software you use. However, as I am going to explain, using Power Bi is a huge relief when someone has to regularly calculate and report these metrics.

Use the ‘Gauge’ visual in the Power Bi desktop to set a calculated ‘measures’ that are to be updated automatically at the end of each period and indicate performance indicators. The metrics could be defined based upon the context of the organization as well as what the stakeholders (e.g. managers) would like to track (and share). The above indicators calculate the per capita energy/water usage, energy-related emission, and renewable energy generation of a university campus on a monthly basis. Creating and updating this with ‘Excel’ would involve much more effort and time.

In the figure above, another way of showing the indicators is using the ‘Tableua’ concept. This way, as you go forward, you see how different types of energy across different buildings are performing. The metric here is the ‘deviation % of Year to Date usage’ to be compared with the same period last time. For instance, if the total electricity usage of campus 1 from Jan-Aug 2021 exceeds over 10% of that of the same period last year (Jan-Aug 2020), the color would turn to red for 10%, yellow to below 10% increase, and green for less usage. This is a quick awareness for a manager to further investigate (or drill down on the other graphs) for the red areas and keep looking at the yellow ones in the future hence save a lot of time to analyze. Needless to say, green is not showing necessarily a saving because of your good practice and can be an effect of a long shutdown. A regression model with appropriate variables is a rather reliable reference to compare the actual usage with and conclude the savings. The concept of showing the colorful tableau can be simply applied to your other indicators.

The above chart is depicting the EII indicator for the energy performance of a site. It is worked out by dividing the ‘actual daily usage’ by ‘expected daily usage’ calculated from the regression equation (energy baseline model Ref. part 4 of this article). This is simple while a robust EnPi. It is supposed to be smoothly moving around 1 and if you have any action plan, you would expect the values to fall less than 1. The unusual changes or outliers can be further investigated and are easily detected with this graph.

Alert notification:

It is apparent that we cannot expect the managers to constantly monitor the graphs and indicators every day and hour. So, it makes more sense we notify them when something goes off the track! Here, we could manage to set up an alert to send to a school manager once the hourly (or total daily) water usage in the site reading from a smart meter exceeds a certain threshold. With this simple feature of Power Bi, the school could save a significant amount of water that would have been wasted from unnoticed leakages many of which occurred during night or weekend.

Energy Management by Microsoft Power Bi | Part 4: Energy baseline with regression modeling

In the context of energy management, having a baseline model as a reference for energy consumption has been highly recommended. An energy baseline is a mathematical (simple or complex) model representing the relationship between energy usage vs. energy drivers or the variables that influence this usage. Using the ‘Regression’ visual in Microsoft Power Bi provides you a lot of insights and ideas about how energy is being used and why! Using this technique with excel might end up with the same outcomes but the level of interaction and attraction of the graphs in Power Bi is outstanding. You may easily toggle the energy users, variables, timeline, and other inputs., include or exclude them in the model with a flick. Of course, this visual will be viewable when only one variable comes to play. A multi-variable regression, unfortunately, cannot be handled with this visual however the opportunity of trying the different variables and see which becomes the most single relevant one is something not to be missed.

The above example is borrowed from an urban water service project. On the x-axis, you may choose a variable like the amount of water delivered (the energy driver). And, the y-axis could be the pumping energy user. A very fine and neat correlation has come out. The line slope (0.43) is the amount of additional energy required to pump one more cubic meter of water. Isn’t it amazing? The dots represent each day of these 366 data (one year). The equation on the top left of the graph is the energy baseline given that the correlation ratio is significantly high in this case.

The outcome of regression modeling is not always promising. The following depicts how energy is used against the outside temperature (HDD) for a school building during the weekends. 106 dots have been picked in this sample. Even a poor correlation like this has something to learn from. It shows the energy varies indifferently to the heating required. This is obvious as, during a weekend, no heating equipment like a heat pump is used so the variation is likely due to other factors. The level of dispersion of the dots around the line (which is automatically added by this feature) may also give us some ideas of why on some weekends the energy behavior is different. The fitting line even in a poor regression is an indicator of the average daily usage (900kWh/weekend in this case). Interestingly if we switch to ‘weekday’ we observe a better correlation with HDD (heating degree days) which makes sense. Hence, we might end up with different baselines for different day types.

Needless to say that clustering the data is a useful technique. You may look at the regression outcome for your hourly/daily/monthly energy usage and group the variables (e.g. outside temperature, occupancy rate, et.) accordingly. Also grouping the data based on the daytype.

In the graph below, we managed to slice the timeline to include only the daily data for 2 years. The total energy usage versus the outside temperature. Clustering the data on this graph is helpful in such a way that we can look at the weekday and weekends separately. Listed below, the lessons I would learn from this graph:

• I expect this site uses relatively higher electricity on a weekday.
• Energy usage behaves differently from ‘weekday’ to a ‘weekend day’ with or without respect to the outside temperature. There is also a non-linear curve-shaped data form observed in the black chart so maybe a linear regression is not a useful method for showing this relationship.
• Those 4 or 5 black dots that are closer to the green line (the fitting line of the weekend) are worthwhile for further investigation so are the green ones in the proximity of the black dots! the former set delineates weekdays with good performance while the latter indicates more than expected usage.
• Outside temperature (HDD in this case) is definitely not an influential variable on the energy consumption of this specific site. So one needs to further seek the other variables to incorporate into the model.

Talking about regression and its application in energy management may take days and nights! and we are here just to quickly show you there is a powerful feature in Power Bi (named Craydec Regression Chart) you can plug and play to your report as an analytical tool for energy baseline modeling and performance tracking. In the presence of an acceptable correlation, it can be also used as a tool for the evaluation of action plans and calculation of savings. Please write to me if have any inquiry.

Energy Management by Microsoft Power Bi | Part 3: Energy load profile display & analysis

In this post, I am going to show you how a Power Bi’s visual can be helpful in understanding the energy load and what we find out by looking at the load graphs. Assuming that we still work with the same dataset I explained in the previous articles, I would suggest you pick the ‘line chart’ and assign ‘Time’ to the x-axis and ‘power’ (e.g. in kWh) to the y-axis. Display two graphs next to each other with one showing the ‘average power load’ and the other illustrating the ‘maximum power load’. Assign ‘month-year to the graph’s legends so you can compare and analyze the loads. Switching between these modes (maximum, minimum, sum, average) on a specific variable (table column) is quite easy in Power Bi.

Load profile interpretation: the line charts above literally depicts how energy (electricity in this example) has been used throughout a 24-hour period in a school’s buildings for 3 years same month. Since the school term in July appears to have the same number of holidays, comparing these graphs will be meaningful. While the ‘Average’ graph is showing how the average energy usage has dropped in 2021 comparing the other 2 years, the ‘Max’ graph indicates an insignificant difference between these periods. But why is that and why the separate interpretations of these graphs are important to complete the puzzle?

1. The ‘Average’ graph might imply the impact of energy conservation measures (we understand that the energy for heating is not supplied by electricity so the drop is not due to the seasonal effect). It appears that the amount of saving (difference between 2020 and the past two years) is improved meaningfully between 09:00 AM-03:00 PM while after and especially before this duration, the saving is not a reasonable amount. It could delineate that the efforts we made in 2021 mostly impacted the equipment being used during the school working hours and it has not improved the baseload (usage outside of working hours) as such. Non-stop energy consumers like the computer servers, outside lighting, security cameras, etc might be the reason why the baseload has not been reduced. In this case, as we were their energy adviser and helped the school to implement some action plans (e.g. changing the lighting system to LED) this fall in July 2021 could be perceived as the result of such improvement projects. Note, the ‘sum’ and ‘average’ graphs will exactly display the same pattern and could be interpreted likewise.

1. The ‘Max’ graph though has a different meaning. While the line curves are more touching in this graph during the peak time, the difference starts to occur around 12:00 PM-03:00 PM, does it mean we have not saved any energy! This graph features the equipment (s) that were using the maximum power during the time period. Therefore, this graph can be useful to tell us if we have taken any good action towards cutting down the maximum load (the large users) or peak shaving (the leveling out peaks in energy usage that is a common practice by industrial users). We still have around 330-340kWh of load running between 09:00 AM-10:00 AM. Comparing this to the ‘Average’ graph, we realize the lighting was not the large user because despite making it more efficient, the maximum load still exists around the same values as those of past years. It is also observed that in 2020, the school could have phased out some of the equipment that was running between 04:00 PM-07:00 PM resulting in a drop in the maximum load. However, looking back to the Average graph, it appears that this attempt has been neutralized by using more of the existing equipment after this change!

In summary, to assess the effectiveness of implemented energy efficiency projects and in the absence of seasonal effect and given the consistency of the number of working days, the ‘sum’ or ‘average’ graph can be used to indicate the saving. However, if the projects involve capital works and asset changes, it is advised some solid methods of energy saving measurement and verification (e.g. IPMVP) be used rather than just looking at the monthly changes. To realize if we have impacted the largest users, the maximum graph could be taken into our assessment. In most of the case, the Pareto principle of 80/20 underlies: less than 20% of the equipment uses more than 80% of the energy! and it makes sense to start investing in the efficiency of the big users. Analysis of the ‘Max’ graph is also a good initial point to design the alternative system. For example, if we are changing a heating system from a gas-boiler to an electric heat pump, the max. of a gas heating load profile may indicate the heating demand to be designed in the new system.

Energy Management by Microsoft Power Bi | Part 2: Energy review & comparison

If you are aiming to systematically and continuously manage your energy performance, a periodeic review of the energy usage and its historical trend is a key part. Despite data availability, many of the organizations ignore this simple and primary task mainly due to the complexity of summarizing and visualizing the data into excel charts and graphs. Using Microsoft Power Bi has an absolute advantage over excel when it comes to viewing your energy consumption over time with the ability to abstract, drilling up/down on the data. In the previous part, I showed you how the raw data might be received and this can be a source to pull in Power Bi. While there might be several approaches to visualize the data, I recommend you use ‘Stack columned chart’ which enables you to drill down and see what happened in the past. You may have the areas (the ICPs) as a button menu to choose from. Using this Power Bi graph gives you a quick insight into how much is the total usage and how over the past few years, your consumption has changed.

Using the same graph you can drill down (clicking on the double arrow facing down) to see how energy has been used across a 24-hour period. This quickly tells you whether or not an energy user is sensitive to the working hours. In this case, the main site load is correlated with the occupation rate of the buildings while the swimming pool load has been relatively constant.

If you simply drill down to the daily profile, depends on the context of the organization, you will see the week-day usage which differs from weekend usage. The chart below displays a drill-down on the month of June 2021 for school educational buildings with a drop appears almost every 5 times. This can be an initial indication of the weekend baseload. This can be compared to the other months to see if for example the weekend baseload is impacted by seasons. A weekend baseload is a significant variable indicating the low-hanging fruits to pick in energy saving. They are the good starting points to question why we use this much energy when no one was using the buildings? In this case, the weekend load is almost a third of a week-day load. Another interesting finding is the total daily usage barely exceeds 3,000kWh which can be a control limit to be notified when occurring in the future.

The last graph I want to introduce is the monthly comparison. To discard the seasonal effect on energy usage, it is suggested the monthly usage be compared with the same month past years. Although this comparison has to be interpreted very carefully due to changes in the other drivers, it may give you some initial ideas to track your performance. I used the ‘Clustered column chart’ visual in Power Bi desktop for the chart below. You should choose the ‘month’ on the x-axis and choose the ‘year’ as the legend with lighter colors representing the older year. The usage has been constantly increased over the last years which could be the result of site expansion, poor performance, occupancy rate, or less likely in this case, global warming!

In the next part, I will describe how to view and analyze the power profile charts

Energy Management by Microsoft Power Bi | Part 1: Collection & transformation of energy data

10 years ago, Microsoft company introduced a cloud-based suite of business intelligence that will help us analyze and view data through compelling visualizations and interactive reports and dashboards. In the context of energy management, continuous review, and analyzing energy usages are imperative tasks as prerequisites to identify the opportunities for saving. Initially inspired by Simon Ross (at Economech Analytics Ltd.), I have been using this tool for the last 2.5 years to develop some reporting platforms for our clients who were keen to understand more about their energy usage and performance. In this series of posts, I am going to walk you through this amazing solution and how to apply it to energy management. I borrow the examples from the Utility Management Platform developed for Epsom Girls Grammar School in Auckland thanks to Bronwyn McGill, the school manager, who kindly provided me permission to use their data. The school started using this platform in Aug. 2019. In May 2021 and following two years of persistent attempts, the school became the first NZ organization to receive ISO50001, the global standard certification for Energy Management Systems.

In this part, I will concisely explain how the energy data can be collected and transformed into a more meaningful form (information). My examples would apply to the New Zealand energy market however the concept of data collection and management does not vary if the energy retailer provides a different format of data.

• Power Metering: In New Zealand, the energy is being supplied by multiple retailers. To access the data you need to find out the meter’s unique number or ICP. An ICP is an Installation Control Point. Each ICP has a unique number that identifies it as an individual power connection. If the meter is smart, you can request and receive the data in a half-hour interval for your last 24 months’ worth of consumption. The retailer must deliver the most granular data it has used for each consumer. This is a format of data you might receive. The file extension could be .xlsx or .csv both of which are easy to handle by Power Bi. As seen, the usage is logged every half-hour in kWh. If the smart meter is Time of Use, you can receive the reactive power in kVArh as well which is useful for further analysis. Some retailers like Meridian may provide you a portal to access your data in your desired format and frequency whilst some others send you data only on request.

• Data transformation: Using Get Data, you can pull in the data you receive into Microsoft Power Bi. This menu is available both in Power Bi Desktop and Query editor. You need to group the data to a full hour to simplify the presentation though some energy analysts prefer dealing with 48 half-hour records a day, I suggest you aggregate these intervals and get the usage for every full hour. You’d better split the column date/time and remove the ones you won’t use (e.g. Account number in this case). Make sure you specify the right data format for each column. You may need to combine one ICP with many others, so keep the format simple and consistent.

• Gas/Water Meters: The concept explained above can be applied to other meter data. However, at present most of the revenue gas and water meters are read monthly and sometimes based on estimated readings which are not accurate for energy management. If a gas meter is smart (Time of Use), you may request the hourly usage from your retailer. At the time of writing this article, Watercare in Auckland is widely promoting and installing smart meters for large consumers so you may contact them if you wish to have some. With smart meters, you are able to receive the water hourly usage data on a daily basis.

In the next part, I will explain how to collect and maintain other useful data from each revenue meter.