<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Elanor King on Medium]]></title>
        <description><![CDATA[Stories by Elanor King on Medium]]></description>
        <link>https://medium.com/@elanor.king?source=rss-eb7b738e3a0d------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*rFx8EiJNbsTPJyABgRyYSA@2x.jpeg</url>
            <title>Stories by Elanor King on Medium</title>
            <link>https://medium.com/@elanor.king?source=rss-eb7b738e3a0d------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 05:33:21 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@elanor.king/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Generate useful error reports for Looker Studio BigQuery issues]]></title>
            <link>https://medium.com/@elanor.king/generate-useful-error-reports-for-looker-studio-bigquery-issues-fe3e139515a2?source=rss-eb7b738e3a0d------2</link>
            <guid isPermaLink="false">https://medium.com/p/fe3e139515a2</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[looker-studio]]></category>
            <dc:creator><![CDATA[Elanor King]]></dc:creator>
            <pubDate>Mon, 05 Feb 2024 23:21:11 GMT</pubDate>
            <atom:updated>2024-02-05T23:21:11.156Z</atom:updated>
            <content:encoded><![CDATA[<p>TL;DR: Analysts want to know when their dashes break. Find erroring queries, roll up daily stats, and add names to make a report that actually helps analysts prioritise what to look at.</p><p>This is article 3 in the Mushroom series &lt; <a href="https://medium.com/@elanor.king/how-to-use-a-google-sheet-as-a-table-in-bigquery-robustly-256c59ae5f12">Previous</a> | <a href="https://medium.com/@elanor.king/what-the-heck-is-looker-studio-using-in-bigquery-3c4a4e0c5459">Start</a> | Next (coming soon) &gt;</p><figure><img alt="Amethyst deceiver (Laccaria amethystina) which has nothing at all to do with databases, analytics dashboards or business intelligence. The image shows a purple mushroom with a flattish top and thin stem growing out of leaf litter in front of a mossy tree stump." src="https://cdn-images-1.medium.com/max/755/1*kTzzbG3Sj8nkssyZ3lnOSw.png" /><figcaption>Amethyst deceiver (Laccaria amethystina) which has nothing at all to do with databases, analytics dashboards or business intelligence.</figcaption></figure><h4>Preparation</h4><p>Here are the two previous articles in this series:</p><ul><li>The first is <a href="https://medium.com/@elanor.king/what-the-heck-is-looker-studio-using-in-bigquery-3c4a4e0c5459">about figuring out the useful links between BigQuery, and Looker Studio datasources and reports</a>. This article gives the SQL for parsing the BQ JOBS metadata to pull out the Looker Studio links.</li><li>The second shows <a href="https://medium.com/@elanor.king/how-to-use-a-google-sheet-as-a-table-in-bigquery-robustly-256c59ae5f12">how to <strong>robustly</strong> make BQ tables from a Google sheet</a>. In particular, it shows how to set up a registry of datasources and reports that your analysts can manage via a Google sheet.</li></ul><p><strong>In order to proceed, you will need:</strong></p><p>— A view called <em>my_dataset.ls_jobs </em>(see <a href="https://medium.com/@elanor.king/what-the-heck-is-looker-studio-using-in-bigquery-3c4a4e0c5459">this article</a>)</p><p>— Two tables, one called <em>my_dataset.ls_named_datasources </em>and one called <em>my_dataset.ls_named_reports. ls_named_datasources</em> has columns<em> datasource_uuid </em>and<em> datasource_name</em>, and similarly <em>ls_named_reports</em> has <em>report_uuid </em>and<em> report_name.</em></p><h4>Let’s flatten the Looker Studio jobs and expand some of the fields:</h4><pre>SELECT <br>  j.job_id, <br>  j.creation_time, <br>  j.datasource_owner, <br>  t.project_id as referenced_project_id, <br>  t.dataset_id as referenced_dataset_id, <br>  t.table_id as referenced_table_id, <br>  j.looker_studio_datasource_id, <br>  j.looker_studio_datasource_url,<br>  ifnull(<br>    dn.datasource_name, <br>    left(j.looker_studio_datasource_id,10)<br>  ) as looker_studio_datasource_name,<br>  j.looker_studio_report_id,<br>  j.looker_studio_report_url,<br>  ifnull(<br>    rn.report_name, <br>    left(j.looker_studio_report_id,10)<br>  ) as looker_studio_report_name,<br>  if(<br>    (<br>      j.is_uuid_looker_studio_datasource_id <br>      and j.is_uuid_looker_studio_report_id <br>      and j.error_result.message is null<br>    ),<br>    null,<br>    concat(<br>      ifnull(j.error_result.message,&quot;No job error&quot;),<br>      if(j.is_uuid_looker_studio_datasource_id,&quot;&quot;,&quot; | Bad Datasource UUID&quot;),<br>      if(j.is_uuid_looker_studio_report_id,&quot;&quot;,&quot; | Bad Report UUID&quot;)<br>    )<br>  ) as error_message,<br>FROM `my_project.my_dataset.ls_jobs` j  <br>left join unnest(j.referenced_tables) t<br>left join `my_project.my_dataset.ls_named_datasources` dn <br>  on dn.datasource_uuid = j.looker_studio_datasource_id<br>left join `my_project.my_dataset.ls_named_reports` rn <br>  on rn.report_uuid = j.looker_studio_report_id</pre><p>Pop this in a view and call it <em>my_dataset.ls_jobs_expanded.</em></p><p>We’ve split the referenced tables to multiple rows and we’ve also added human-friendly names for all registered datasources and reports. If the source wasn’t registered, we give the first 10 characters of the ID. This seems to be long enough to identify sources when reviewing.</p><h4>Now get out just the errors</h4><p>Create another view, this time call it <em>my_dataset.ls_errors</em></p><pre>with errors as (<br>  select <br>    job_id,<br>    creation_time,<br>    datasource_owner, <br>    looker_studio_datasource_name,<br>    looker_studio_datasource_url,<br>    looker_studio_report_name,<br>    looker_studio_report_url,<br>    error_message,<br>    referenced_project_id || &quot;.&quot; || referenced_dataset_id || &quot;.&quot; || referenced_table_id as full_table_id<br>    FROM `my_project.my_dataset.ls_jobs_expanded`<br>    where date(creation_time) = date_add(current_date(), interval -1 day)<br>    and error_message is not null<br>)<br>SELECT <br>  datasource_owner, <br>  looker_studio_datasource_name,<br>  looker_studio_datasource_url,<br>  looker_studio_report_name,<br>  looker_studio_report_url,<br>  error_message,<br>  string_agg(distinct full_table_id, &quot;, &quot; order by full_table_id) as queried_tables,<br>  count(distinct job_id) as n_occurrences,<br>  min(creation_time) as earliest_occurrence,<br>  max(creation_time) as latest_occurrence<br>from errors<br>group by 1,2,3,4,5,6</pre><ul><li>There’s no harm using a date filter again here (we had set one up in <em>ls_jobs </em>originally). If filters are removed from the underlying views, this query could suddenly balloon in costs, so it’s best to add more restrictions rather than fewer.</li><li><em>referenced_tables </em>in<em> ls_jobs </em>was a record (collection) of tables. I’ve effectively expanded this field out and then aggregated it back down again. This way I can control the formatting of the string and I can also guarantee the display order.</li></ul><h4>Ready to report!</h4><p>At this point, we can easily use <em>ls_errors</em> to make a report about Looker Studio BigQuery errors that happened yesterday. <br>We can give a human-friendly name and URL for both datasource and report. This is trivial to turn into a clickable link in a new, “meta” dashboard.<br>We can also give the datasource owner, likely table culprits, how many times the error occurred, and when this error was first and last seen.</p><p>Even better, we can use <em>ls_jobs_expanded </em>to monitor how Looker Studio is using individual tables over time.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fe3e139515a2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Make a Looker Studio source registry: Use a Google Sheet as a table in BigQuery ***Robustly***]]></title>
            <link>https://medium.com/@elanor.king/how-to-use-a-google-sheet-as-a-table-in-bigquery-robustly-256c59ae5f12?source=rss-eb7b738e3a0d------2</link>
            <guid isPermaLink="false">https://medium.com/p/256c59ae5f12</guid>
            <category><![CDATA[looker-studio]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[dataform]]></category>
            <dc:creator><![CDATA[Elanor King]]></dc:creator>
            <pubDate>Mon, 05 Feb 2024 21:37:16 GMT</pubDate>
            <atom:updated>2024-02-07T11:03:40.150Z</atom:updated>
            <content:encoded><![CDATA[<p>TL;DR: Sheets break. <em>Users</em>, amirite? Use a materialiser like Dataform to “freeze” a sheet as a table on schedule. This example creates a registry for Looker Studio datasources and reports.</p><p>This is article 2 in the Mushroom series &lt; <a href="https://medium.com/@elanor.king/what-the-heck-is-looker-studio-using-in-bigquery-3c4a4e0c5459">Start</a> | <a href="https://medium.com/@elanor.king/generate-useful-error-reports-for-looker-studio-bigquery-issues-fe3e139515a2">Next</a> &gt;</p><p>Here, we create a sheet, save and share it, and finally, read and freeze it.</p><figure><img alt="Penny bun (Boletus edulis) which has nothing at all to do with databases, analytics dashboards or business intelligence. The image shows a mushroom with a shiny, glistening, brown cap and a fat stem growing in soil in leaf litter. It’s not entirely evident from the picture whether the mushroom is shiny because it is wet, or slimy. Probably a bit of both." src="https://cdn-images-1.medium.com/max/755/1*1_6BnfEiCAjvK-ONIIu6yg.png" /><figcaption>Penny bun (Boletus edulis) which has nothing at all to do with databases, analytics dashboards or business intelligence.</figcaption></figure><h4>Why does “Robustly” have three asterisks in the title?</h4><p>Spreadsheets are fab, I love them, they are <strong>the</strong> power workhorse for day-to-day tasks. They can also make great interfaces to databases, so long as they are SIMPLE.</p><p>Users can update a table in BQ by adding, updating or deleting rows in a google sheet.</p><p>Unfortunately, they can also do things like add empty rows, insert columns, rename sheets and lots of other changes that break this fragile point of entry.</p><h4>Set up a simple Google sheet</h4><p>Create a sheet with two tabs. Each tab has two columns: <strong>uuid</strong> and <strong>name.</strong></p><figure><img alt="A screen shot of the example sheet. The name is Looker Studio sources. There are two tabs, Datasources and Reports. Datasources is open. The sheet has 2 columns: uuid and name. There are two rows with example data." src="https://cdn-images-1.medium.com/max/1024/1*Tla5yMUUSJj2ANXQqd7TEA.png" /><figcaption>A screen shot of the example Looker Studio sheet</figcaption></figure><p>Find some Looker Studio datasources and reports. Grab out the unique UUID from the URL. <br>For example, if the URL for your <em>Marketing dash</em> is <em>https://lookerstudio.google.com/u/0/reporting/</em><strong><em>a8ef9d5f-1234-abcd-6789-6aabefd5e4d0</em></strong><em>/page/p_aln20xzabc<br></em>Then the UUID is <strong><em>a8ef9d5f-1234-abcd-6789–6aabefd5e4d0</em></strong></p><p>If the URL for the <em>Daily sales</em> datasource is <br><em>https://lookerstudio.google.com/u/0/datasources/</em><strong><em>3857b78b-1234-abcd-6789-28d658d20f08<br></em></strong>Then the UUID is <strong><em>3857b78b-1234-abcd-6789–28d658d20f08</em></strong></p><p>Make sure you’re on the correct tab, pop the UUIDs into the first column, and the human readable name into the second.</p><p>Great, that’s that job done.</p><h4>Save and share</h4><p>The whole point of this article is to make a robust table. You’re going to use Dataform or dbt or similar to “freeze” aka materialise the data. So you need to share this sheet with the service account that will be executing that freeze.</p><p>Readers (both human and service) of the external table will need permission to read not ONLY the table in BQ but ALSO the google sheet. <br>If your sysadmin is anything like mine (i.e. admirably highly cautious), this means you will need to store the sheet in a place that service account will be able to read it. For us that’s a secure, externally-accessible shared drive.</p><p>Don’t forget to also share the file with everyone who will need to update it, i.e. the rest of your DE team, analysts and anyone else who manages Looker Studio reports.</p><h4>Import to BigQuery</h4><p>There are loads of articles that will walk you through adding a Google sheet, not least <a href="https://cloud.google.com/bigquery/docs/external-data-drive">google’s own help page</a> on adding an external table, so the instructions below are not meant to be comprehensive.</p><p><strong>Make your external table:</strong></p><ol><li>Create the table from Drive, paste the sheet’s URI &amp; set <em>File type</em> as Google sheet.</li><li>Enter the name of the tab from the sheet as the <em>Sheet range (i.e. Reports or Datasources)</em></li><li>Set the project and dataset to whatever makes sense for you, but call the table <em>ls_named_datasources_raw</em> or <em>ls_named_reports_raw </em>depending on which you’re setting up.</li><li>Under schema, toggle <em>Edit as text</em> to ON and enter the following</li></ol><pre>[<br>  {<br>    &quot;name&quot;: &quot;datasource_uuid&quot;,<br>    &quot;mode&quot;: &quot;REQUIRED&quot;,<br>    &quot;type&quot;: &quot;STRING&quot;,<br>    &quot;description&quot;: &quot;UUID of the Looker Studio datasource&quot;,<br>    &quot;fields&quot;: []<br>  },<br>  {<br>    &quot;name&quot;: &quot;datasource_name&quot;,<br>    &quot;mode&quot;: &quot;REQUIRED&quot;,<br>    &quot;type&quot;: &quot;STRING&quot;,<br>    &quot;description&quot;: &quot;The human name for the datasource&quot;,<br>    &quot;fields&quot;: []<br>  }<br>]</pre><p>Change occurrences of datasource to report as required. Note UUID is a datatype in its own right in BQ, I’ve used STRING here because I’ve found it’s easier to deal with errors later on.</p><p>5. Under <em>Advanced options</em>, set <em>Header rows to skip</em> as 1.</p><p>6. Click <em>Create table</em> aaaaaand done.</p><h4>That’s it right? I can use this table in my queries straight away!</h4><p>Eh... it exists, you <em>can </em>use it, but here’s why you shouldn’t:</p><p>If you use this directly, then every time anyone so much as tweaks the sheet, this table will break and everything downstream of it will go kablooey.</p><p>If you’re already a dbt/Dataform master, go ahead and make a <em>select *</em> copy of this table that materialises at the schedule you need. For us, that’s daily.</p><p>For Dataform, this boils down to</p><ol><li>Make a declaration that this is a new data source in <em>declarations.js</em> (or the file you use)</li></ol><pre>declare({<br>schema: &quot;my_dataset&quot;,<br>database: &quot;my_project&quot;,<br>name: &quot;ls_named_datasources_raw&quot;,<br>});</pre><p>2. Create a SQLX file for the materialisation. This one does a little bit extra to make sure the UUIDs are unique. It needs to be a table, because a view will require users to have permission to read the sheet, and will be brittle to changes just like the external table. Call the file <em>ls_named_datasources.sqlx. </em><br>Replace <em>datasource</em> with <em>report</em> as necessary.</p><pre>config {<br>  type: &quot;table&quot;,<br>  schema: &quot;my_dataset&quot;,<br>  database: dataform.projectConfig.vars.sourceProject,<br>  tags: [&quot;daily&quot;],<br>  description: &quot;A materialisation of the Datasources sheet in file &lt;file&gt;&quot;,<br>  columns: {<br>    datasource_uuid: &quot;UUID of the Looker Studio datasource&quot;,<br>    datasource_name: &quot;Human name of the datasource&quot;,<br>  },<br>  uniqueKey: [&quot;datasource_uuid&quot;],<br>}<br>select datasource_uuid, min(datasource_name) as datasource_name <br>from ${ref(&#39;my_dataset&#39;, &#39;ls_named_datasources_raw&#39;)}<br>group by 1</pre><p>3. If the google sheet is changed to the point where it breaks and causes Dataform execution to fail, your alert system (assuming you have one) should let you know that it needs to be fixed. In this event, the table should retain the data from the last successful run, which is what makes this solution robust.</p><p>If you’re following this article in order to make the Looker Studio error report, you can stop reading here and go on to<a href="https://medium.com/@elanor.king/generate-useful-error-reports-for-looker-studio-bigquery-issues-fe3e139515a2"> the next article in the series</a>.</p><p>If you want to see how to build even more robustness into the Google sheet itself, read on.</p><h4>Agh! Users keep changing the sheet!</h4><figure><img alt="A screen shot of a couple of cells from a spreadsheet. The cells contain a warning: “This sheet is used by BigQuery, take care when editing!”, which is highlighted in yellow." src="https://cdn-images-1.medium.com/max/852/1*VCGYvcbOP-IEC2Eo1gyjKQ.png" /><figcaption>No harm in adding a warning label.</figcaption></figure><p>We can do a bit more… we can make the sheet itself more resilient when things change. The sheet in the Looker Studio sources example above is really simple. It doesn’t even have formatting.</p><p>Most sheets you see that are made for human consumption will have a bit of colour at least. Prettification doesn’t usually affect the data, but cell equations and conditional formatting can. Further, the more leeway users have to modify the sheet, the more likely they are to make a breaking change.</p><p>To make things just a little more robust, I suggest having a cloned “ghost” sheet that users <strong>don’t</strong> have access to.</p><p>In the spreadsheet, make a new, blank tab. In <strong><em>every</em></strong> cell in the new, blank sheet, paste the following formula:</p><pre>=if(isblank(indirect(address(row(),COLUMN(),,,&quot;TheHumanSheet&quot;))),IFERROR(0/0),indirect(address(row(),COLUMN(),,,&quot;TheHumanSheet&quot;)))</pre><p>Where “TheHumanSheet” is the name of the matching tab that your users <em>are</em> allowed to edit.</p><p><strong>What does this formula do?</strong></p><ul><li>It’s fetching the value of the cell that has the same address as the current cell. If it’s blank, replace it with a true Null, and if it’s not blank, use the value.</li><li>IFERROR(0/0) is one of the few ways to make a true Null in Google sheets. Blanks and conditional data fills can mean you end up with semi-nulls that are BigQuery unfriendly.</li><li>If you have header rows to skip (often the case in pretty-for-humans sheets), you can change all instances of <em>row()</em> to e.g. <em>row()+2</em> to skip 2 rows.</li><li>The reason I use the indirect formula is because if a user deletes or inserts a row or column in the human sheet, all the direct formulae in this sheet would get adjusted too. Indirect decouples this dependency.</li></ul><p>Hide or protect this sheet/tab you’ve just made. <strong>Use this tab instead of “TheHumanSheet” to import the info into BigQuery. </strong>If you’re still following along from the Looker Studio example, note you will have to make a new table and delete the old one, as you can’t edit the sheet info on a BigQuery external table.</p><h4>When failure is good</h4><p>Please note, that this level of resilience can itself be a <strong>bad thing</strong>. Here, the sheet is much less likely to break if a user adds or deletes a column. Dataform will happily carry on crunching the data it is being given, especially if you have not written any tests or assertions to monitor the data.</p><p>For example, say we add a column to the left of <em>uuid</em> in the Looker Studio sources sheet. We fill this new column with <em>team_name</em>. Now, when we look at the external table in BigQuery, we’ll see team name where we’re expecting uuid, and uuid where we’re expecting name — i.e. it has failed silently, but not gracefully.</p><p>Sometimes, if we allow things to break catastrophically, it is easier to see the failure and fix it.</p><h4>Summary</h4><p>Hopefully you’ve picked up some tips to making a google sheet into a table; and setting procedures in place to make that table more robust. You can freeze the data so it isn’t broken by user changes to the sheet, and you can also make the sheet itself more resilient to breaking changes.</p><p>None of this is fool-proof unfortunately, so it is left as an exercise for the reader to decide the best course of action for your purposes!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=256c59ae5f12" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What the heck is Looker Studio using in BigQuery?]]></title>
            <link>https://medium.com/@elanor.king/what-the-heck-is-looker-studio-using-in-bigquery-3c4a4e0c5459?source=rss-eb7b738e3a0d------2</link>
            <guid isPermaLink="false">https://medium.com/p/3c4a4e0c5459</guid>
            <category><![CDATA[looker-studio]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Elanor King]]></dc:creator>
            <pubDate>Tue, 23 Jan 2024 15:43:48 GMT</pubDate>
            <atom:updated>2024-02-05T23:22:14.973Z</atom:updated>
            <content:encoded><![CDATA[<p>Native Integration of Looker Studio means it’s now easy to figure out the links between your BigQuery tables and the dashes that are using them.</p><p>This is article 1 in the Mushroom series &lt; <a href="https://medium.com/@elanor.king/how-to-use-a-google-sheet-as-a-table-in-bigquery-robustly-256c59ae5f12">Next</a> &gt;</p><figure><img alt="Shaggy Scalycap (Pholiota squarrosa) which has nothing at all to do with databases, analytics dashboards or business intelligence. These mushrooms are wet from the rain, golden brown and covered in scales. They look deep-fried!" src="https://cdn-images-1.medium.com/max/964/1*1t_SChCTK9MLX7vygbturA.png" /><figcaption>Shaggy Scalycap (Pholiota squarrosa) which has nothing at all to do with databases, analytics dashboards or business intelligence.</figcaption></figure><p><strong>TL;DR</strong>: Looker Studio expresses the datasource and report UUIDs as labels in the BQ job.</p><p>Here’s the SQL to grab this info out for jobs run in the past day:</p><pre>with looker_studio_job_info as (<br>  select <br>    job_id, <br>    creation_time, <br>    user_email as datasource_owner,<br>    labels,<br>    referenced_tables, <br>    query,<br>    left( (<br>      select l.value <br>      from unnest(labels) l <br>      where l.key = &quot;looker_studio_datasource_id&quot;<br>    ), 36) as looker_studio_datasource_id, <br>    left( (<br>      select l.value <br>      from unnest(labels) l <br>      where l.key = &quot;looker_studio_report_id&quot;<br>    ), 36) as looker_studio_report_id, <br>    error_result,<br>  from `my_project`.`region-eu`.INFORMATION_SCHEMA.JOBS <br>  where (select l.value from unnest(labels) l where l.key = &quot;requestor&quot;)=&quot;looker_studio&quot;<br>  and creation_time &gt;= timestamp_add(current_timestamp, interval -1 day)<br>)<br><br>select <br>  *, <br>  if(<br>    looker_studio_datasource_id is null,<br>    null,<br>    concat(&quot;https://lookerstudio.google.com/datasources/&quot;, looker_studio_datasource_id)<br>  ) as looker_studio_datasource_url,<br>  if(<br>    regexp_contains(looker_studio_datasource_id, &#39;[a-fA-F0-9]{8}(-[a-fA-F0-9]{4}){3}-[a-fA-F0-9]{12}&#39;),<br>    TRUE,<br>    FALSE<br>  ) as is_uuid_looker_studio_datasource_id,<br>  if(<br>    looker_studio_report_id is null,<br>    null,<br>    concat(&quot;https://lookerstudio.google.com/reporting/&quot;, looker_studio_report_id)<br>  ) as looker_studio_report_url,<br>  if(<br>    regexp_contains(looker_studio_report_id, &#39;[a-fA-F0-9]{8}(-[a-fA-F0-9]{4}){3}-[a-fA-F0-9]{12}&#39;),<br>    TRUE,<br>    FALSE<br>  ) as is_uuid_looker_studio_report_id,<br>from looker_studio_job_info </pre><p>Let’s make that into a view called ls_jobs so we can use it later <a href="https://medium.com/@elanor.king/generate-useful-error-reports-for-looker-studio-bigquery-issues-fe3e139515a2">to make an error report</a>.</p><pre>create or replace view my_dataset.ls_jobs as (<br>  &lt;... insert the SQL from above ...&gt;<br>)</pre><p>Some caveats:</p><p><strong>0. </strong>I’ve renamed <em>user_email</em> as <em>datasource_owner</em>. This is not quite true, but it’s close enough for our purposes.</p><p><strong>1. </strong>You’ll notice the LEFT ( xxx, 36) on <em>looker_studio_datasource_id</em> and <em>looker_studio_report_id</em> — this is because the ID sometimes comes with an extra “t0” on the end. I haven’t yet figured out why this happens (I suspect a bug).</p><p><strong>2. </strong>I have also noticed the values are not always proper UUIDs (8–4–4–4–12 format). When this happens, URLs created using the given ID will be broken. To this end, I have added <em>is_uuid_looker_studio_datasource_id </em>and <em>is_uuid_looker_studio_report_id </em>to indicate when the ID is not a UUID. Again I don’t know why this happens. It’s possible it could be very old dashes (pre-UUID). They seem to start with a “1”. Comment below if you know!</p><p><strong>3. </strong>The referenced tables are usually but not always expressed completely in the referenced_tables array when compared to the SQL in the query.</p><p><strong>4.</strong> When datasources and reports are deleted, the URLs will no longer work.</p><p><strong>5.</strong> <em>error_result</em> contains useful info if the job fails (e.g. <em>error_result</em>.<em>message </em>might be something like the always-delightful “Resources exceeded during query execution: Not enough resources for query planning — too many subqueries or query is too complex.”)</p><p>Using queries very like the above, I have made “metadashes” that monitor Looker Studio usage for our data analysts — allowing them to find and fix errors and incrementally improve the datasources. <br>BQ native integration for Looker Studio was rolled out for all users last month (Dec 2023), and I am delighted it’s now this easy to figure out which datasources and reports are linked to which BQ tables. The <em>bigquerybiengine</em> logging that we had funnelled into a sink seems to have disappeared though — let me know in the comments if you’ve an idea where it’s gone!</p><p><em>Update 2024–02–05: flatteringly, this article has been picked up by another Medium writer with bigger reach than newbie me — Ali Izadi shows how you can </em><a href="https://medium.com/@aliiz/cute-dashboards-big-bills-exploring-looker-studio-costs-with-bigquery-data-sources-7e5b9e3b0086"><em>improve the above to look for costly reports</em></a><em>. Very useful!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3c4a4e0c5459" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>