Welcome to the third installment of the Learn to Build Better series. This multi-part series showcases the tools and techniques to rapidly build, test, and prototype energy-focused applications, analytics and use cases on the Awesense Energy Transition Platform, using the AI Data Engine to process data, and TGI or APIs to access and visualize the data structured according to the Awesense open Energy Data Model (EDM).
Everything You Need to Know About Spreadsheets
This is the part where we start to build on the complexity from last week – we will discuss spreadsheets. We can’t do that without a little introduction on what they are, why we use them, and what some popular tools are. We promise we’ll get to the good stuff right after this!
What are spreadsheets?
At its basics, spreadsheets store data in a tabular format. They hold data from anywhere you like through manual or automated entries. They also allow you to properly categorize and analyze your data, all while keeping track of it in a single place.
Why do we love spreadsheets?
We love spreadsheets because they are used for a variety of projects! That versatility and utility they offer are exactly why we are introducing them in this edition of the series. Spreadsheets enable us to address multiple use cases. Some notable examples include complex mathematical calculations, data modelling, reporting, data sharing, data visualization & dashboarding, and even data analytics.
Due to their ubiquity, no industry today doesn’t use spreadsheets as a part of their organization’s operations. Whether it’s finance, healthcare, or even energy, everyone uses spreadsheets!
CSVs Are Spreadsheets Too!
How could we ever forget about the popularity of the Comma Separated Value (CSV) file format? We can’t! Though most commonly used for data exchanges, a CSV file format is easily readable through our eyes but can be opened natively in spreadsheet software like those mentioned above. This adds to the popularity of spreadsheets and the versatility of what they bring to the table. Needless to say, spreadsheets are a team player!
Common Spreadsheet Applications
Spreadsheets are so common these days that several organizations offer great spreadsheet applications. Common players in the spreadsheets market are tools like Microsoft Excel, LibreOffice Calc, Apple Numbers, and Google Drive Spreadsheets.
Our Favorite Spreadsheet- Microsoft Excel
The leading spreadsheet software is Microsoft Excel (Excel from here on). Yes, you read that correctly. The same Excel application you used a decade or two ago in school is still the most prominent spreadsheet software to this date- talk about consistency!
Why Microsoft Excel Rules the Spreadsheet World
Excel has reigned as the spreadsheet king because of its significant footprint in the high-level data analytics world. Excel comes pre-installed on many computers. This has added to its popularity and ubiquity. More importantly, Excel has worked hard to democratize working with data. Requiring almost no knowledge to begin using the application, children worldwide learn Excel from as young as six years old!
From a data analytics standpoint, Excel uses data visualizations & dashboarding, reporting, and much more, without users knowing a single line of code. For those looking for the quickest data analysis out there, Excel is often the go-to. It’s an easy-to-use and potent tool, all housed in that seemingly simple green “e” on your desktop.
Why is Excel our favourite spreadsheet?
Excel democratized data the way Awesense aims to democratize energy, can you see why we’re so fond of their spreadsheets? At Awesense, we’re passionate about bringing analytics and analytical capabilities to anyone and everyone, regardless of their data analytics skills and backgrounds. We believe Excel is helping people do the same.
So how do we use Excel in the Awesense Digital Energy Platform? Let’s look at some examples and see (this is that fun part)!
Analyzing a CSV File in the Awesense Platform
The Awesense Energy Transition Platform’s frontend application, called TGI, can produce CSV data, so let’s take a look at an example of a typical use case many utilities are dealing with. The following example will describe the process of filtering an Outage Data table in TGI’s user interface (UI), exporting that data to a CSV file, and then working with that data in Excel.
Each table throughout the TGI front-end visualization tool is filtered, and data is exported to a CSV file. The screenshot above shows TGI’s Outage Page with all recorded outages. All of this is sample data from the Awesense Sandbox Environment (please contact us to gain access to the Awesense Sandbox Environment); we live and breathe cybersecurity, so we can’t show you the real thing! In this example, the outage table was filtered to display past outage occurrences before being exported as a CSV file.
From there, we can open the CSV file in Excel because it is a native form of data – remember, CSVs are spreadsheets too! With that, we can now summarize this outage data, prepare a data report, or create a visualization for a PowerPoint presentation.
Was that example a bit too manual for you? If you need more convenience and automation and are a more advanced Excel user, Excel has a toolset that can connect to the Energy Transition Platform SQL API (OEDP). You will need a simple ODBC driver to proceed from here. After that, Awesense can provide each user with unique login credentials. These credentials can then be imputed into Excel’s UI for establishing the connection between the two.
From there, a whole set of Energy Data Model tables is available for the user to access! This allows them to pick any data table they want to work with, all with a simple double click!
IoT Sensors in the Awesense Digital Energy Platform
How about an example for an IoT sensor? We’ll use our Awesense Raptor 3 in this case. Specifically, let’s look at sensor health data and analyze it further in Excel. To do this, we would pull the raptor_status data table into Excel. This operation is done easily since Excel has a built-in utility called Power Query Editor. This capability allows the user to filter, truncate and wrangle the pulled data, all without any coding skills required!
Once the user is satisfied with the data they’ve pulled, it can be inputted into Excel’s worksheets. This allows users to then work with the data directly in that worksheet from that point onwards. Following that example, we can create a chart with a histogram displaying the internal temperature of the IoT sensor devices and the device battery levels. It doesn’t stop there either, users can build any type of visualization and then use them as an example dashboard or a summary report.
The two examples we walked you through here are just the tip of the iceberg. These were basic operations performed with a combination of Excel and the Awesense Energy Transition Platform. There is significantly more that you can do when you master these basics, and yes, we will cover some of these in future articles! Notable possibilities which we haven’t touched upon yet are the use of Visual Basic macro for automated CSV processing, as well as the use of advanced SQL querying in Excel’s Power Query Editor. These are a bit more advanced, but we’re sure that you can master them if you keep up with this series!
Next Up Is…
You! How are you enjoying this series so far? Please leave us some feedback by answering that question here.
We have a ton of great content lined up, with business intelligence (BI) tools as the focus of our next article. These are widely used by organizations and have become essential to any organization’s ongoing operations.
We love connecting and would love for you to share our content! Follow along with this series and let us know what ideas YOU would like to see us write about. Let us know whether it’s more content about the topics we’ve already written on or even a specific use case or tool you would like to know more about.
If you or your team are interested in building a custom application or use case using the Awesense Platform, or you have an analytical tool we should demonstrate with our platform, feel free to reach out to us at tools@awesense.com.
Part 4 – The Energy Transition Platform: Learn to Build Better. Ep. 4 Business Intelligence Tools (BI Tools)
