Cost analysis using Google Data Studio (Big Data)

Share on facebook
Share on twitter
Share on linkedin

Industry

Services
Platform
Domain

Industry

Services
Platform
Domain

Cost analysis using Google Data Studio (Big Data)

We at FAIR Consulting Group, primarily use Google Cloud Platform, for our internal infrastructure for various projects and opportunities, and it goes without saying that if proper analysis is not done on the monthly billing, you would end up asking yourself, where is all the money going? Now GCP does provide an excellent billing dashboard, but can you identify what is one particular thing is costing you, or identify some dangling load balancer in the midst of thousands? or create far more complex queries to satisfy your whims, it is safe to say, there is room for improvement, why then should we wait for GCP to catch up when we can venture on our own? So we played around with it a bit and realised there were a couple of places we were throwing money, we were not always able to keep an eye on everything, that was going on in our cloud, somehow if we could glimpse all the information at once.
Google Data Studio

We enabled the feature of export billing details to GCP Big query, which can be found here. After that, we waited so data could be accumulated to make queries a bit more interesting. I will try to break the queries down so you can replicate and evolve as you see fit for your own environments and infrastructures.

First 3 are the ones on the top right corner Month to Date, Quarter to Date and Year to Date spend;

Google Data Studio

All three of them are almost same, a simple SQL sum operation of the cost, the manipulation was done with the date, get the sum of each duration, EXTRACT functions seemed the easiest way to get them, but there were other ways, which you can explore on your own  

Then came a simple query which is also available in the GCP dashboard as well, but to have everything in one place is what we are hoping to get. 

This one got a bit out of hand. Had to call couple of functions to replicate something similar to the one found in GCP, we had to use to parse the date, we were not able to just extract month out of invoice.month for some reason, we had to first convert it into something acceptable for extraction when we tried direct EXTRACT(MONTH invoice.month), it was not working as expected, but this query has issues, PARSE_DATE(“%Y%m”,invoice.month))- %Y I am not sure what this is returning could be 1990 or 2019, but since we are extracting the month we might get away with this…
Google Data Studio

This was no effort at all but serves a very important purpose when you are tracing a sudden change in cost, it helps you narrow down on the dates you must look at, narrowing down on the usage.

Google Cloud Platform

This query is particularly helpful, showing how heavily dependent we are on VMs when we could avail different platform services to perform a similar task.

In the previous query, we realized we were heavily using compute engines, which led me to investigate what in compute engines were costing us so much, not interestingly it was the instance, not the storage. Though it helped me to identify a few load balancers running uselessly and static IP not assigned to anything, saved a few hundred cents, I hope my boss is not reading this…

One more query which was quite useful, was in which regions are my resources because that load balancer which I was talking about earlier was in the EU region, some intelligent lifeform decided to forget to delete it, and we never go there some were not able to identify in midst of so many projects.

Conclusion

FAIR mostly use one internal cloud for demo’s or internal upscaling, and we were able to save quite a lot by creating these custom reports, and it goes without saying, how much it could help those who actually are using Cloud at an enterprise level, maybe this is what FinOps is all about, I was quite impressed with the level of flexibility available by GCP to satisfy my curiosity, And I plan to keep these custom reports running for while internally so managers in FAIR, could keep track of the projects under their supervision and how much are they costing them.

Subscribe to our newsletter