This was very helpful! I was able to create a “Top 10 Dashboard” and “Top 10 Question” set of questions going over a recent period of time.
I’d like to be able to find any dashboard associated with a specific question. I have built the query, but in order to use a field filter, I believe I need to go in as an administrator and change the “Type” of the dashboard name field. My questions is…if I’m connecting directly to the Metabase Database, from Metabase, and changing field types (from name to" Category")…does that cause any problems with the Metabase site? Changing the type of a field that is used everywhere in the system seems dangerous. Should I leave this alone?
@MetaKC When you add Metabase’s own application database to Metabase as a datasource, then the Data Model only has something to do with the datasource - and not the application database itself, that Metabase writes information to.
So you can change anything in the Data Model.
But as always, make sure to have backups, and if you’re using the default H2 database, then you should migrate away: https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
Thanks for this !
Adding the 2 models I use at the minute:
-- timeseries of cumulated new users
with data as (
select
timestamp :: date as date_at,
count(user_id) as count
from activity
where topic= 'user-joined'
group by 1
)
select
date_at,
sum(count) over (order by date_at asc rows between unbounded preceding and current row) as running_users
from data
-- weeky active users
select
date_trunc('week', created_at) as week_at,
count(distinct user_id) as weekly_active_users
from
core_session
group by 1
order by 1 asc
I’m adding a few queries that are really useful to me and maybe can be helpful to someone else.
I use them after changing the data model that feeds 100s/1000s of queries and I’m absolutely not even considering changing anything by hand!
A) replace anything with a regex within the actual code of my queries
Step 1: identify all the queries contain the term I need to update
SELECT
dataset_query,
*
FROM report_card
where dataset_query like '%native%' -- to isolate those in native SQL
and dataset_query ~* 'field\/edit\/\?id=.[\|]{2}your_old_value'
Step 2: update the queries
UPDATE
report_card
SET
dataset_query = REGEXP_REPLACE
(
dataset_query,
'field\/edit\/\?id=.[\|]{2}your_old_value',
'field/edit/?id='||'||your_new_value',
'g'
)
WHERE
dataset_query like '%native%'
AND dataset_query ~* 'field\/edit\/\?id=.[\|]{2}field_id'
After Step 2 I usually run Step 1 to confirm I have successfully updated everything and then check that all my dashboards are running smoothly
B) Update filters that where once based on my old column_name
Step 1: I find all the filters that rely on my old column_name
SELECT
*
FROM public.metabase_field
WHERE display_name = 'old_column_name'
OR name = 'old_column_name'
ORDER BY created_at DESC
Step 2: update from old to new column_name ( I usually do it one at the time to double check everything)
UPDATE metabase_field
SET display_name = 'new_column_name'
WHERE display_name = 'old_column_name'
/* Most viewed dasboards in the last 3 months */
select rd.name as DashboardName, count(cu.id) as viewCount, rd.description as DashboardDescription
from core_user cu
INNER JOIN view_log vl on vl.user_id = cu.id
INNER JOIN report_dashboard rd on rd.id = vl.model_id
where vl.model='dashboard'
-- last 3 months
AND str_to_date(concat(date_format(vl.timestamp, '%Y-%m'), '-01'), '%Y-%m-%d')
BETWEEN str_to_date(concat(date_format(date_add(now(6), INTERVAL -3 month), '%Y-%m'), '-01'), '%Y-%m-%d')
AND str_to_date(concat(date_format(now(6), '%Y-%m'), '-01'), '%Y-%m-%d')
GROUP BY rd.name, rd.description
ORDER BY viewCount DESC
/* Most viewed questions in the last 3 months */
SELECT
rc.name AS CardName
, COUNT(vl.user_id) AS viewCount
, rc.description AS CardDescription
FROM view_log vl
INNER JOIN report_card rc ON rc.id = vl.model_id
WHERE vl.model='card'
-- last 3 months
AND str_to_date(concat(date_format(vl.timestamp, '%Y-%m'), '-01'), '%Y-%m-%d')
BETWEEN str_to_date(concat(date_format(date_add(now(6), INTERVAL -3 month), '%Y-%m'), '-01'), '%Y-%m-%d')
AND str_to_date(concat(date_format(now(6), '%Y-%m'), '-01'), '%Y-%m-%d')
GROUP BY rc.name, rc.description
ORDER BY viewCount DESC
Questions never viewed in the last 3 months
/* Questions never viewed in the last 3 months */
SELECT
rc.id
, CONCAT('http://your_base_url', rc.id) AS CardUrl
, rc.name AS CardName
, rc.description AS CardDescription
FROM report_card rc
WHERE rc.id NOT IN (
SELECT vl.model_id
FROM view_log vl
WHERE vl.model='card'
-- last 3 months
AND str_to_date(concat(date_format(vl.timestamp, '%Y-%m'), '-01'), '%Y-%m-%d')
BETWEEN str_to_date(concat(date_format(date_add(now(6), INTERVAL -3 month), '%Y-%m'), '-01'), '%Y-%m-%d')
AND str_to_date(concat(date_format(now(6), '%Y-%m'), '-01'), '%Y-%m-%d')
)
What format is the dataset query stored in the app DB, and what is the best way to convert/clean after exporting / copying dataset query to obtain pure SQL? @marco
I just run them from Metabase. Your database should be either Postgres or MySQL. If you're still on H2, you need to migrate now before you lose all your work.
My application database has been Postgres already, but I still could not run those queries. May it be due to that my Metabase is not paid-plan (I mean Pro/Enterprise)?
Hey everyone, I am trying to figure out if it is possible to query for all questions and dashboards that weren't visited in the past few months. Is it possible to do this in the application database?
@amzar Look at the column descriptions, they are available for most tables and columns.
Specifically query_execution.running_time: The time, in milliseconds, this query took to complete.
Thank you for sharing your queries! It was really helpful:)
Additionally I need to show dashboards which appear at the main page of our company metabase page. This dashboards are pinned in our collection Our analytics. Maybe you know where to find pinned dashboards in metabase logs?
List of un archived dashboards with last accessed date on the public link
-- using view log table see when cards were last accessed and map the card ids to dashboard ids.
-- (none of my cards are on more than one dash)
with joined as (
select
view_log.model_id as Model_ID,
date(max(view_log.timestamp)) as Last_Viewed,
report_dashboardcard.dashboard_id as Dashboard_ID
from
view_log
left join report_dashboardcard on view_log.model_id = report_dashboardcard.card_id
where
view_log.model = 'card'
and view_log.user_id is null
group by
Model_ID,
Dashboard_ID
),
-- get rid of dashboards that are archived
dashboards as (
select
report_dashboard.id as Dashboard_ID,
report_dashboard.name as Dashboard_Name
from
report_dashboard
where
report_dashboard.archived = false
)
-- join two above tables to link dashboard name to dashboard id
select
dashboards.dashboard_name as Dashboard_Name,
max(joined.last_viewed) as last_viewed
from
joined
inner join dashboards on joined.dashboard_id = dashboards.dashboard_id
group by
Dashboard_Name
order by
last_viewed desc
