How to Monitor GCP Costs for Users Utilizing the 'OWOX: Reports' Extension [TEMPLATE] – OWOX Help Center

In this article, we’ll guide you on how to monitor your Google Cloud Platform (GCP) spending using the "OWOX: Reports..." extension. This is especially valuable for organizations where employees handle and process large volumes of data in BigQuery.

At OWOX BI, we're committed to being transparent and simplifying how you understand your use of our products. If you need to see who's running Data Marts and how much data they're processing, we've got a tool for you. It's a Google Sheets template called "OWOX BI Extension Usage Analysis based on GBQ logs." It gives you all the details on how the extension is used. We'll tell you how to get and set it up in this guide.

Important Note #1This template uses data from real-time metadata about all BigQuery jobs (INFORMATION_SCHEMA.JOBS) to give you extra information on your GCP spending. It's slightly different from the info on the OWOX BI Credits consumption page.

Important Note #2This template displays the BigQuery Job count instead of the Report Run count. The calculation logic is as follows:
– simple reports: one Report Run corresponds to one Job;
– reports that include filtering or sorting features: one Report Run in the OWOX extension will execute two Jobs in BigQuery logs due to the specifics of the feature’s implementation.

A quick look at the template

First, let's take a quick look at what this template can do for you. It answers questions like:

How many Jobs each day and how much data is processed?

The first two charts in the template provide an overview of Extension usage. You can see statistics on the number of launches, the volume of processed data, and the type of launch (manual or scheduled) by day.

Who are the top users processing lots of data?

The next two charts display statistics by users. You can identify under which account the most GB of data are processed. It also allows you to see in which GCP projects the most data is processed.

What's the monthly overview of usage by day and user?

Another sheet contains a pivot table grouping launch data by days and months. Here, you can see the Grand Total for the month for manual and automatic launches.

Expanding a row for a day, you will see a list of users who launched a Data Mart that day, either manually or on a schedule.

Who's running reports without actually processing data?

Another useful data segmentation is a table listing users who launch Report Runs but do not process any data as a result. Essentially, these are forgotten reports that do not benefit you but are counted in the launch statistics. Ideally, this list should be empty.

The data source for the template

This template pulls data from your BigQuery logs. Every job from the 'OWOX: Reports...' extension is labeled, and you can fetch this data into Google Sheets.

Some limits to know

You can only get data from the last 180 days, starting from February 25, 2024.
The user executing the Data Mart needs to have the BigQuery Resource Viewer role. Read this article about how to assign a role in GCP.
The template is ideal if your data is stored in one location. For example, if your data is stored both in US and EU locations, you need to execute two different Data Marts to retrieve data from each location. Then, you need to merge these data in your Google Sheets document.

Step-by-step guide: setting up the template

Step 1. Open this template and make a copy of it.

Step 2. In your copy, go to 'Extensions' → 'OWOX: Reports, Charts & Pivots...' → Add a new report.

Step 3. Select your Project.

Step 4. Open the list of Data Marts and select “+ Add new data mart…”

Step 5. Copy the SQL code, paste it into the Data Mart editor, and replace the text “YOUR_GCP_PROJECT_ID” with the actual ID of your GCP project.

/* Settings block
Analyse queries triggered by OWOX BI Extension within GCP project or your own queries only
{scope default='JOBS_BY_PROJECT' values='JOBS_BY_PROJECT,JOBS_BY_USER,' type='select'} -- 

BigQuery jobs region https://cloud.google.com/bigquery/docs/locations#supported_locations
{region default='us' type='input'}

Your Time Zone
{timeZone default='Europe/Dublin' type='input'}

Start date for analysis
Data in logs exists only from Feb, 25 2024
{startDate default='2024-02-25' type='input'}

End date for analysis
{endDate default='2024-07-31' type='input'}
*/

WITH combinedLogFromProjects AS (
  -- add additionals projects here
  SELECT *  
  FROM (SELECT * FROM `YOUR_GCP_PROJECT_ID.region-{region}.INFORMATION_SCHEMA.{scope}`) -- first project
    --UNION ALL (SELECT * FROM `YOUR_GCP_PROJECT_ID.region-{region}.INFORMATION_SCHEMA.{scope}`) -- additional project, uncomment if needed
),

queryLog AS (
  SELECT
    project_id AS gcpProjectId,
    user_email AS userEmail, 
    job_id,    
    total_bytes_processed AS bytesProcessed,
    DATE(creation_time, '{timeZone}') AS date,
    IF(error_result IS NULL, 'success', 'failed') AS status,
    (SELECT l.value FROM UNNEST(labels) l WHERE l.key = 'owox_bi_project_id') AS biProjectId,
    (SELECT l.value FROM UNNEST(labels) l WHERE l.key = 'owox_bi_data_mart_id') AS dataMartId,
    (SELECT l.value FROM UNNEST(labels) l WHERE l.key = 'owox_bi_run_type') AS runType
  FROM combinedLogFromProjects
  WHERE DATE(creation_time, '{timeZone}') BETWEEN '{startDate}' AND '{endDate}'
    AND job_type = 'QUERY'
    AND state = 'DONE'
    AND (SELECT l.value FROM UNNEST(labels) l WHERE l.key = 'owox_bi_requester') = 'owox_bi_extension'
)

SELECT
  DATE_TRUNC(date, MONTH) AS month,
  date,
  gcpProjectId,
  biProjectId,
  userEmail,
  runType,
  status,
  COUNT(DISTINCT job_id) AS jobs,
  IFNULL(SUM(bytesProcessed), 0) AS bytesProcessed
FROM queryLog
GROUP BY month, date, gcpProjectId, biProjectId, userEmail, runType, status
ORDER BY date DESC, bytesProcessed DESC

If the Data Mart requires specifying multiple GCP projects, uncomment line 23 and enter the ID of your second GCP project.

Step 6. It's essential to change the Data Mart title to – logs data.

IMPORTANTIf the Data Mart is named differently, you will then need to adjust the formulas in the document, something we would prefer to avoid.

Step 7. Click the “Save&Run” button.

Step 8. After the Data Mart is successfully executed, a new “logs data” sheet with your real data will appear in the document.

Step 9. Delete the “Demo” sheet, as it is no longer needed because it contains demo data. After deletion, open the “[data] for charts” sheet, where you should see your real data.

✅ Step 10. You’re all set!

Now you can view the “Graphs”, “Pivot by date”, and “Jobs w/o data” sheets.

Modifying the Data Mart for Source Data

By editing the Data Mart in the Extension panel, you will see a list of dynamic parameters that can be changed without opening the Data Mart editor.

Setting up data auto-refresh in the template

To have the data in the template automatically refreshed, you can set up Scheduled refreshes by following these instructions.

Finding documents

Even though the template doesn't link directly to your Google Sheets documents, you can use Google Drive search to find them. Just use the right search phrase. For instance:

type:spreadsheet "OWOX BI Queries" "Result OK(2024" owner:john.snow@owox.com

Replace john.snow@owox.com with the required email. This search query will return a list of Google Sheets for this user, where successful Data Marts launches from the Extension in 2024 occurred. For more information on searching Google Sheets, read this article.

You might also find this useful:

Elevate your financial oversight with the BigQuery Budget Forecast Toolkit, a template designed to help you analyze, manage, and forecast your BigQuery expenses effectively.
Additionally, a complete list of Google Sheets documents is available on the Credits consumption page in OWOX BI.