Created
March 26, 2025 20:44
-
-
Save clowa/6fe78ee1da2ff4751c145f6b9f59352a to your computer and use it in GitHub Desktop.
Databricks Workspace Usage Dashboard
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "datasets": [ | |
| { | |
| "name": "static_text", | |
| "displayName": "static_text", | |
| "query": "select\n 'Usage Analysis: Tag Matching' as title_tags,\n 'Usage Analysis: Top Spending' as title_top\n" | |
| }, | |
| { | |
| "name": "select_yes_no_tag_show_mismatch", | |
| "displayName": "select_yes_no_tag_show_mismatch", | |
| "query": "select explode(array(\n 'Yes',\n 'No'\n)) as toggle\n" | |
| }, | |
| { | |
| "name": "select_yes_no_top_show_null", | |
| "displayName": "select_yes_no_top_show_null", | |
| "query": "select explode(array(\n 'Yes',\n 'No'\n)) as toggle\n" | |
| }, | |
| { | |
| "name": "select_time_key_overview", | |
| "displayName": "select_time_key_overview", | |
| "query": "select explode(array(\n 'Day',\n 'Week',\n 'Month'\n)) as time_key\n" | |
| }, | |
| { | |
| "name": "select_time_key_tag", | |
| "displayName": "select_time_key_tag", | |
| "query": "select explode(array(\n 'Day',\n 'Week',\n 'Month'\n)) as time_key\n" | |
| }, | |
| { | |
| "name": "select_time_key_top", | |
| "displayName": "select_time_key_top", | |
| "query": "select explode(array(\n 'Day',\n 'Week',\n 'Month'\n)) as time_key\n" | |
| }, | |
| { | |
| "name": "select_group_key", | |
| "displayName": "select_group_key", | |
| "query": "select explode(array(\n 'Billing Origin Product',\n 'SKU'\n)) as group_key\n" | |
| }, | |
| { | |
| "name": "select_workspace", | |
| "displayName": "select_workspace", | |
| "query": "with\nusage_with_ws_filtered_by_date as (\n select\n workspace_id as workspace,\n *\n from system.billing.usage\n where usage_date between :param_start_date and :param_end_date\n),\n-- query\nworkspace_selection as (\n select\n distinct(workspace)\n from usage_with_ws_filtered_by_date\n)\nselect * from workspace_selection\n", | |
| "parameters": [ | |
| { | |
| "displayName": "param_start_date", | |
| "keyword": "param_start_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now-12M/M" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_end_date", | |
| "keyword": "param_end_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now/d" | |
| } | |
| ] | |
| } | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "name": "select_tag_key", | |
| "displayName": "select_tag_key", | |
| "query": "with\nusage_with_ws_filtered_by_date as (\n select\n workspace_id as workspace,\n *\n from system.billing.usage\n where usage_date between :param_start_date and :param_end_date\n),\n-- apply workspace filter\nusage_filtered as (\n select\n *\n from usage_with_ws_filtered_by_date\n where workspace_id = 'MY_WORKSPACE_ID_HERE'\n),\n-- query\ntag_key_selection as (\n select\n distinct(explode(map_keys(custom_tags))) as tag_key\n from usage_filtered\n)\nselect * from tag_key_selection\n", | |
| "parameters": [ | |
| { | |
| "displayName": "param_start_date", | |
| "keyword": "param_start_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now-12M/M" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_end_date", | |
| "keyword": "param_end_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now/d" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_workspace", | |
| "keyword": "param_workspace", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<ALL WORKSPACES>" | |
| } | |
| ] | |
| } | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "name": "select_rank_key", | |
| "displayName": "select_rank_key", | |
| "query": "with\nusage_with_ws_filtered_by_date as (\n select\n workspace_id as workspace,\n *\n from system.billing.usage\n where usage_date between :param_start_date and :param_end_date\n),\n-- apply workspace filter\nusage_filtered as (\n select\n *\n from usage_with_ws_filtered_by_date\n where workspace_id = 'MY_WORKSPACE_ID_HERE'\n),\n-- enumerate all usage_metadata keys\nusage_metadata_keys as (\n select\n distinct(explode(\n map_keys(from_json(\n to_json(usage_metadata),\n 'map<string,string>'\n ))\n )) as rank_key\n from usage_filtered\n)\nselect explode(array('run_as')) as rank_key\nunion all\nselect rank_key from usage_metadata_keys\n", | |
| "parameters": [ | |
| { | |
| "displayName": "param_start_date", | |
| "keyword": "param_start_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now-12M/M" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_end_date", | |
| "keyword": "param_end_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now/d" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_workspace", | |
| "keyword": "param_workspace", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<ALL WORKSPACES>" | |
| } | |
| ] | |
| } | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "name": "usage_total", | |
| "displayName": "usage_total", | |
| "query": "with\nusage_with_ws_filtered_by_date as (\n select\n workspace_id as workspace,\n *\n from system.billing.usage\n where usage_date between :param_start_date and :param_end_date\n),\n-- apply workspace filter\nusage_filtered as (\n select\n *\n from usage_with_ws_filtered_by_date\n where workspace_id = 'MY_WORKSPACE_ID_HERE'\n),\n-- calc list priced usage in USD\nprices as (\n select coalesce(price_end_time, date_add(current_date, 1)) as coalesced_price_end_time, *\n from system.billing.list_prices\n where currency_code = 'USD'\n),\nlist_priced_usd as (\n select\n coalesce(u.usage_quantity * p.pricing.effective_list.default, 0) as usage_usd,\n date_trunc('QUARTER', usage_date) as usage_quarter,\n date_trunc('MONTH', usage_date) as usage_month,\n date_trunc('WEEK', usage_date) as usage_week,\n u.*\n from usage_filtered as u\n left join prices as p\n on u.sku_name=p.sku_name\n and u.usage_unit=p.usage_unit\n and (u.usage_end_time between p.price_start_time and p.coalesced_price_end_time)\n),\n-- calc total usage in USD\nusage_total as (\n select\n sum(usage_usd) as usage_usd\n from list_priced_usd\n)\n-- query\nselect\n concat(\n 'Total usage (USD): $ ',\n case\n when usage_usd >= 1e9 then concat(format_number(usage_usd / 1e9, 2), 'B')\n when usage_usd >= 1e6 then concat(format_number(usage_usd / 1e6, 2), 'M')\n when usage_usd >= 1e3 then concat(format_number(usage_usd / 1e3, 2), 'K')\n else format_number(usage_usd, 2)\n end\n ) as total_usage_usd\nfrom usage_total\n", | |
| "parameters": [ | |
| { | |
| "displayName": "param_start_date", | |
| "keyword": "param_start_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now-12M/M" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_end_date", | |
| "keyword": "param_end_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now/d" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_workspace", | |
| "keyword": "param_workspace", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<ALL WORKSPACES>" | |
| } | |
| ] | |
| } | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "name": "usage_overview", | |
| "displayName": "usage_overview", | |
| "query": "with\nusage_with_ws_filtered_by_date as (\n select\n workspace_id as workspace,\n *\n from system.billing.usage\n where usage_date between :param_start_date and :param_end_date\n),\n-- apply workspace filter\nusage_filtered as (\n select\n *\n from usage_with_ws_filtered_by_date\n where workspace_id = 'MY_WORKSPACE_ID_HERE'\n),\n-- calc list priced usage in USD\nprices as (\n select coalesce(price_end_time, date_add(current_date, 1)) as coalesced_price_end_time, *\n from system.billing.list_prices\n where currency_code = 'USD'\n),\nlist_priced_usd as (\n select\n coalesce(u.usage_quantity * p.pricing.effective_list.default, 0) as usage_usd,\n date_trunc('QUARTER', usage_date) as usage_quarter,\n date_trunc('MONTH', usage_date) as usage_month,\n date_trunc('WEEK', usage_date) as usage_week,\n u.*\n from usage_filtered as u\n left join prices as p\n on u.sku_name=p.sku_name\n and u.usage_unit=p.usage_unit\n and (u.usage_end_time between p.price_start_time and p.coalesced_price_end_time)\n),\n-- eval time_key param\nlist_priced_usd_with_time_key as (\n select\n identifier\n (\n case\n when :param_time_key = 'Quarter' then 'usage_quarter'\n when :param_time_key = 'Month' then 'usage_month'\n when :param_time_key = 'Week' then 'usage_week'\n else 'usage_date'\n end\n ) as time_key,\n *\n from list_priced_usd\n),\n-- eval group_key param\nws_count as (\n select\n count(distinct(workspace)) as workspace_count\n from list_priced_usd_with_time_key\n where workspace is not null\n),\ntop_workspace_usage as (\n select\n workspace as top_workspace,\n sum(usage_usd) as _top_ws_usage_usd\n from list_priced_usd_with_time_key\n where workspace is not null\n group by top_workspace\n order by _top_ws_usage_usd desc\n limit 10\n),\nlist_priced_usd_with_time_and_group_keys as (\n select\n if(workspace_count <= 50 or workspace is null or top_workspace is not null, workspace, '<OTHERS>') as workspace_norm,\n identifier\n (\n case\n when :param_group_key = 'Workspace' then 'workspace_norm'\n when :param_group_key = 'SKU' then 'sku_name'\n else 'billing_origin_product'\n end\n ) as group_key,\n *\n from ws_count, list_priced_usd_with_time_key u\n left join top_workspace_usage\n on u.workspace = top_workspace_usage.top_workspace\n)\n-- query\nselect\n time_key, group_key, usage_usd\nfrom list_priced_usd_with_time_and_group_keys\n", | |
| "parameters": [ | |
| { | |
| "displayName": "param_start_date", | |
| "keyword": "param_start_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now-12M/M" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_end_date", | |
| "keyword": "param_end_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now/d" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_workspace", | |
| "keyword": "param_workspace", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<ALL WORKSPACES>" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_time_key", | |
| "keyword": "param_time_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Week" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_group_key", | |
| "keyword": "param_group_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Billing Origin Product" | |
| } | |
| ] | |
| } | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "name": "usage_overview_overtime", | |
| "displayName": "usage_overview_overtime", | |
| "query": "with\nusage_with_ws_filtered_by_date as (\n select\n workspace_id as workspace,\n *\n from system.billing.usage\n where usage_date between :param_start_date and :param_end_date\n),\n-- apply workspace filter\nusage_filtered as (\n select\n *\n from usage_with_ws_filtered_by_date\n where workspace_id = 'MY_WORKSPACE_ID_HERE'\n),\n-- calc list priced usage in USD\nprices as (\n select coalesce(price_end_time, date_add(current_date, 1)) as coalesced_price_end_time, *\n from system.billing.list_prices\n where currency_code = 'USD'\n),\nlist_priced_usd as (\n select\n coalesce(u.usage_quantity * p.pricing.effective_list.default, 0) as usage_usd,\n date_trunc('QUARTER', usage_date) as usage_quarter,\n date_trunc('MONTH', usage_date) as usage_month,\n date_trunc('WEEK', usage_date) as usage_week,\n u.*\n from usage_filtered as u\n left join prices as p\n on u.sku_name=p.sku_name\n and u.usage_unit=p.usage_unit\n and (u.usage_end_time between p.price_start_time and p.coalesced_price_end_time)\n),\n-- eval time_key param\nlist_priced_usd_with_time_key as (\n select\n identifier\n (\n case\n when :param_time_key = 'Quarter' then 'usage_quarter'\n when :param_time_key = 'Month' then 'usage_month'\n when :param_time_key = 'Week' then 'usage_week'\n else 'usage_date'\n end\n ) as time_key,\n *\n from list_priced_usd\n),\n-- eval group_key param\nws_count as (\n select\n count(distinct(workspace)) as workspace_count\n from list_priced_usd_with_time_key\n where workspace is not null\n),\ntop_workspace_usage as (\n select\n workspace as top_workspace,\n sum(usage_usd) as _top_ws_usage_usd\n from list_priced_usd_with_time_key\n where workspace is not null\n group by top_workspace\n order by _top_ws_usage_usd desc\n limit 10\n),\nlist_priced_usd_with_time_and_group_keys as (\n select\n if(workspace_count <= 50 or workspace is null or top_workspace is not null, workspace, '<OTHERS>') as workspace_norm,\n identifier\n (\n case\n when :param_group_key = 'Workspace' then 'workspace_norm'\n when :param_group_key = 'SKU' then 'sku_name'\n else 'billing_origin_product'\n end\n ) as group_key,\n *\n from ws_count, list_priced_usd_with_time_key u\n left join top_workspace_usage\n on u.workspace = top_workspace_usage.top_workspace\n),\n-- calc usage by period\ngrouped_usage_by_period as (\n select\n time_key as period_key,\n replace(replace(group_key, '<', '<'), '>', '>') as group_key,\n sum(usage_usd) as usage_usd\n from list_priced_usd_with_time_and_group_keys\n group by all\n),\n-- calc periodic change\ngrouped_usage_change as (\n select\n period_key,\n group_key,\n usage_usd,\n lag(usage_usd, 1) over (partition by group_key order by period_key) as prev_usage_usd,\n round(try_divide(usage_usd - prev_usage_usd, prev_usage_usd) * 100, 2) as usage_change_percentage\n from grouped_usage_by_period\n),\ntotal_usage_change as (\n select\n period_key,\n '<b>TOTAL</b>' as group_key,\n sum(usage_usd) as usage_usd,\n lag(sum(usage_usd), 1) over (order by period_key) as prev_usage_usd,\n round(try_divide(sum(usage_usd) - prev_usage_usd, prev_usage_usd) * 100, 2) as usage_change_percentage\n from grouped_usage_by_period\n group by period_key\n),\n-- periods\nperiod_info as (\n select\n case\n when :param_time_key = 'Day' then current_date()\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date())\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date())\n end as current_period,\n case\n when :param_time_key = 'Day' then current_date() - interval 1 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 7 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 1 month)\n end as last_period,\n case\n when :param_time_key = 'Day' then current_date() - interval 2 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 14 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 2 month)\n end as 2_periods_ago,\n case\n when :param_time_key = 'Day' then current_date() - interval 3 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 21 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 3 month)\n end as 3_periods_ago,\n case\n when :param_time_key = 'Day' then current_date() - interval 4 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 28 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 4 month)\n end as 4_periods_ago,\n case\n when :param_time_key = 'Day' then current_date() - interval 5 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 35 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 5 month)\n end as 5_periods_ago\n),\n-- pivot change\nusage_change_pivot as (\n select\n case\n when period_key = current_period then 'Current period'\n when period_key = last_period then 'Last period'\n when period_key = 2_periods_ago then '2 periods ago'\n when period_key = 3_periods_ago then '3 periods ago'\n when period_key = 4_periods_ago then '4 periods ago'\n when period_key = 5_periods_ago then '5 periods ago'\n end as x_period_back,\n group_key,\n usage_usd,\n usage_change_percentage\n from (\n select * from grouped_usage_change, period_info\n union all\n select * from total_usage_change, period_info\n )\n),\n-- pivot all time\nall_time_usage_pivot as (\n select\n 'Start to End date' as x_period_back,\n group_key,\n sum(usage_usd) as usage_usd,\n null as usage_change_percentage\n from grouped_usage_by_period\n group by group_key\n),\n-- pivot total all time\nall_time_total_usage_pivot as (\n select\n 'Start to End date' as x_period_back,\n '<b>TOTAL</b>' as group_key,\n sum(usage_usd) as usage_usd,\n null as usage_change_percentage\n from grouped_usage_by_period\n),\nunion_usage_pivot as (\n select\n x_period_back,\n group_key,\n case\n when x_period_back = 'Start to End date' then string(usage_usd)\n else concat('<span style=\"zoom:1\">', usage_usd_str, '</span><span style=\"zoom:1;color:', change_color, '\"> ', usage_change_str, '</span>')\n end as usage_info\n from (\n select\n case\n when usage_usd >= 1e9 then concat(format_number(usage_usd / 1e9, 0), 'B')\n when usage_usd >= 1e6 then concat(format_number(usage_usd / 1e6, 0), 'M')\n when usage_usd >= 1e3 then concat(format_number(usage_usd / 1e3, 0), 'K')\n else format_number(usage_usd, 0)\n end as usage_usd_str,\n case\n when usage_change_percentage > 10 then '#00A972'\n when usage_change_percentage < -10 then '#FF3621'\n else '#919191'\n end as _change_color,\n concat('(', if(usage_change_percentage > 0, '+', ''), format_number(usage_change_percentage, 0), '%)') as _usage_change_str,\n coalesce(_change_color, '#919191') as change_color,\n coalesce(_usage_change_str, '') as usage_change_str,\n *\n from (\n select x_period_back, group_key, usage_usd, usage_change_percentage from usage_change_pivot\n union all\n select * from all_time_usage_pivot\n union all\n select * from all_time_total_usage_pivot\n )\n )\n)\n-- query\nselect\n group_key,\n concat('$', format_number(float(`Start to End date`), 0)) as `Start to End date`,\n float(`Start to End date`) as _all_time_usage_usd,\n 2 as _order,\n coalesce(`5 periods ago`, '<span style=\"zoom:1;color:#919191\">0</span>') as `5 periods ago`,\n coalesce(`4 periods ago`, '<span style=\"zoom:1;color:#919191\">0</span>') as `4 periods ago`,\n coalesce(`3 periods ago`, '<span style=\"zoom:1;color:#919191\">0</span>') as `3 periods ago`,\n coalesce(`2 periods ago`, '<span style=\"zoom:1;color:#919191\">0</span>') as `2 periods ago`,\n coalesce(`Last period`, '<span style=\"zoom:1;color:#919191\">0</span>') as `Last period`,\n coalesce(`Current period`, '<span style=\"zoom:1;color:#919191\">0</span>') as `Current period`\nfrom union_usage_pivot\npivot (\n first(usage_info)\n for x_period_back in (\n 'Start to End date',\n '5 periods ago',\n '4 periods ago',\n '3 periods ago',\n '2 periods ago',\n 'Last period',\n 'Current period'\n )\n)\nunion all\n(\n select\n '' as group_key,\n concat('<b><i>', date_format(:param_start_date, 'MMM dd yyyy'), ' - ', date_format(:param_end_date, 'MMM dd yyyy'), '</i></b>') as `Start to End date`,\n null as _all_time_usage_usd,\n 1 as _order,\n concat('<b><i>', date_format(5_periods_ago, 'MMM dd'), ' - ', date_format(4_periods_ago, 'MMM dd'), '</i></b>') as `5 periods ago`,\n concat('<b><i>', date_format(4_periods_ago, 'MMM dd'), ' - ', date_format(3_periods_ago, 'MMM dd'), '</i></b>') as `4 periods ago`,\n concat('<b><i>', date_format(3_periods_ago, 'MMM dd'), ' - ', date_format(2_periods_ago, 'MMM dd'), '</i></b>') as `3 periods ago`,\n concat('<b><i>', date_format(2_periods_ago, 'MMM dd'), ' - ', date_format(last_period, 'MMM dd'), '</i></b>') as `2 periods ago`,\n concat('<b><i>', date_format(last_period, 'MMM dd'), ' - ', date_format(current_period, 'MMM dd'), '</i></b>') as `Last period`,\n concat('<b><i>', date_format(current_period, 'MMM dd'), ' - Now', '</i></b>') as `Current period`\n from period_info\n)\norder by _order asc, _all_time_usage_usd desc\n", | |
| "parameters": [ | |
| { | |
| "displayName": "param_start_date", | |
| "keyword": "param_start_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now-12M/M" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_end_date", | |
| "keyword": "param_end_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now/d" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_workspace", | |
| "keyword": "param_workspace", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<ALL WORKSPACES>" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_time_key", | |
| "keyword": "param_time_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Week" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_group_key", | |
| "keyword": "param_group_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Billing Origin Product" | |
| } | |
| ] | |
| } | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "name": "usage_analyze_tags_match", | |
| "displayName": "usage_analyze_tags_match", | |
| "query": "with\nusage_with_ws_filtered_by_date as (\n select\n workspace_id as workspace,\n *\n from system.billing.usage\n where usage_date between :param_start_date and :param_end_date\n),\n-- apply workspace filter\nusage_filtered as (\n select\n *\n from usage_with_ws_filtered_by_date\n where workspace_id = 'MY_WORKSPACE_ID_HERE'\n),\n-- calc list priced usage in USD\nprices as (\n select coalesce(price_end_time, date_add(current_date, 1)) as coalesced_price_end_time, *\n from system.billing.list_prices\n where currency_code = 'USD'\n),\nlist_priced_usd as (\n select\n coalesce(u.usage_quantity * p.pricing.effective_list.default, 0) as usage_usd,\n date_trunc('QUARTER', usage_date) as usage_quarter,\n date_trunc('MONTH', usage_date) as usage_month,\n date_trunc('WEEK', usage_date) as usage_week,\n u.*\n from usage_filtered as u\n left join prices as p\n on u.sku_name=p.sku_name\n and u.usage_unit=p.usage_unit\n and (u.usage_end_time between p.price_start_time and p.coalesced_price_end_time)\n),\n-- eval time_key param\nlist_priced_usd_with_time_key as (\n select\n identifier\n (\n case\n when :param_time_key = 'Quarter' then 'usage_quarter'\n when :param_time_key = 'Month' then 'usage_month'\n when :param_time_key = 'Week' then 'usage_week'\n else 'usage_date'\n end\n ) as time_key,\n *\n from list_priced_usd\n),\n-- eval tags param\ntag_entry_parsing as (\n select\n explode(split(\n if(trim(:param_single_tag_key) = '<USE TAG FILTER>', :param_tag_entries, :param_single_tag_key),\n ';'\n )) as tag_entry,\n contains(tag_entry, '=') as is_filter,\n if(is_filter, split(tag_entry, '=')[0], tag_entry) as tag_key\n),\n-- parse tag entries\ntag_entry_parsed as (\n select\n array_sort(collect_list(tag_key)) as all_keys,\n array_sort(collect_list(if(is_filter, tag_key, null))) as filter_keys,\n array_sort(collect_list(if(is_filter, tag_entry, null))) as filter_expected\n from tag_entry_parsing\n),\n-- match tag entries\nlist_priced_usd_with_matching_tag_kvp as (\n select * from (\n select\n transform(filter_keys, k -> concat(k, '=', custom_tags[k])) as filter_actual,\n transform(all_keys, k -> concat(k, '=', custom_tags[k])) as kvp,\n if(\n size(filter_keys) = 0 or filter_actual = filter_expected,\n array_join(kvp, ';'),\n ''\n ) as _custom_tag_key_value_pairs,\n if(_custom_tag_key_value_pairs = \"\", \"<MISMATCH>\", _custom_tag_key_value_pairs) as custom_tag_key_value_pairs,\n *\n from list_priced_usd_with_time_key, tag_entry_parsed\n ) \n)\n\n-- query\nselect\n sum(if(custom_tag_key_value_pairs <> '<MISMATCH>', usage_usd, 0)) as match_usage_usd,\n sum(usage_usd) as total_usage_usd,\n concat(\n round(match_usage_usd / total_usage_usd * 100, 2),\n '% of usage matched with tags: \"',\n if(trim(:param_single_tag_key) = '<USE TAG FILTER>', :param_tag_entries, :param_single_tag_key),\n '\"'\n ) as tag_match_percentage\nfrom list_priced_usd_with_matching_tag_kvp\n", | |
| "parameters": [ | |
| { | |
| "displayName": "param_start_date", | |
| "keyword": "param_start_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now-12M/M" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_end_date", | |
| "keyword": "param_end_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now/d" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_workspace", | |
| "keyword": "param_workspace", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<ALL WORKSPACES>" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_time_key", | |
| "keyword": "param_time_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Week" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_single_tag_key", | |
| "keyword": "param_single_tag_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<USE TAG FILTER>" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_tag_entries", | |
| "keyword": "param_tag_entries", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Budget;Env" | |
| } | |
| ] | |
| } | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "name": "usage_analyze_tags", | |
| "displayName": "usage_analyze_tags", | |
| "query": "with\nusage_with_ws_filtered_by_date as (\n select\n workspace_id as workspace,\n *\n from system.billing.usage\n where usage_date between :param_start_date and :param_end_date\n),\n-- apply workspace filter\nusage_filtered as (\n select\n *\n from usage_with_ws_filtered_by_date\n where workspace_id = 'MY_WORKSPACE_ID_HERE'\n),\n-- calc list priced usage in USD\nprices as (\n select coalesce(price_end_time, date_add(current_date, 1)) as coalesced_price_end_time, *\n from system.billing.list_prices\n where currency_code = 'USD'\n),\nlist_priced_usd as (\n select\n coalesce(u.usage_quantity * p.pricing.effective_list.default, 0) as usage_usd,\n date_trunc('QUARTER', usage_date) as usage_quarter,\n date_trunc('MONTH', usage_date) as usage_month,\n date_trunc('WEEK', usage_date) as usage_week,\n u.*\n from usage_filtered as u\n left join prices as p\n on u.sku_name=p.sku_name\n and u.usage_unit=p.usage_unit\n and (u.usage_end_time between p.price_start_time and p.coalesced_price_end_time)\n),\n-- eval time_key param\nlist_priced_usd_with_time_key as (\n select\n identifier\n (\n case\n when :param_time_key = 'Quarter' then 'usage_quarter'\n when :param_time_key = 'Month' then 'usage_month'\n when :param_time_key = 'Week' then 'usage_week'\n else 'usage_date'\n end\n ) as time_key,\n *\n from list_priced_usd\n),\n-- eval tags param\ntag_entry_parsing as (\n select\n explode(split(\n if(trim(:param_single_tag_key) = '<USE TAG FILTER>', :param_tag_entries, :param_single_tag_key),\n ';'\n )) as tag_entry,\n contains(tag_entry, '=') as is_filter,\n if(is_filter, split(tag_entry, '=')[0], tag_entry) as tag_key\n),\n-- parse tag entries\ntag_entry_parsed as (\n select\n array_sort(collect_list(tag_key)) as all_keys,\n array_sort(collect_list(if(is_filter, tag_key, null))) as filter_keys,\n array_sort(collect_list(if(is_filter, tag_entry, null))) as filter_expected\n from tag_entry_parsing\n),\n-- match tag entries\nlist_priced_usd_with_matching_tag_kvp as (\n select * from (\n select\n transform(filter_keys, k -> concat(k, '=', custom_tags[k])) as filter_actual,\n transform(all_keys, k -> concat(k, '=', custom_tags[k])) as kvp,\n if(\n size(filter_keys) = 0 or filter_actual = filter_expected,\n array_join(kvp, ';'),\n ''\n ) as _custom_tag_key_value_pairs,\n if(_custom_tag_key_value_pairs = \"\", \"<MISMATCH>\", _custom_tag_key_value_pairs) as custom_tag_key_value_pairs,\n *\n from list_priced_usd_with_time_key, tag_entry_parsed\n ) where :param_show_tag_mismatch = 'Yes' or custom_tag_key_value_pairs != '<MISMATCH>'\n)\n\n-- query\nselect\n time_key, custom_tag_key_value_pairs, usage_usd\nfrom list_priced_usd_with_matching_tag_kvp\n", | |
| "parameters": [ | |
| { | |
| "displayName": "param_start_date", | |
| "keyword": "param_start_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now-12M/M" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_end_date", | |
| "keyword": "param_end_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now/d" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_workspace", | |
| "keyword": "param_workspace", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<ALL WORKSPACES>" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_time_key", | |
| "keyword": "param_time_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Week" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_single_tag_key", | |
| "keyword": "param_single_tag_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<USE TAG FILTER>" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_tag_entries", | |
| "keyword": "param_tag_entries", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Budget;Env" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_show_tag_mismatch", | |
| "keyword": "param_show_tag_mismatch", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Yes" | |
| } | |
| ] | |
| } | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "name": "usage_analyze_tags_overtime", | |
| "displayName": "usage_analyze_tags_overtime", | |
| "query": "with\nusage_with_ws_filtered_by_date as (\n select\n workspace_id as workspace,\n *\n from system.billing.usage\n where usage_date between :param_start_date and :param_end_date\n),\n-- apply workspace filter\nusage_filtered as (\n select\n *\n from usage_with_ws_filtered_by_date\n where workspace_id = 'MY_WORKSPACE_ID_HERE'\n),\n-- calc list priced usage in USD\nprices as (\n select coalesce(price_end_time, date_add(current_date, 1)) as coalesced_price_end_time, *\n from system.billing.list_prices\n where currency_code = 'USD'\n),\nlist_priced_usd as (\n select\n coalesce(u.usage_quantity * p.pricing.effective_list.default, 0) as usage_usd,\n date_trunc('QUARTER', usage_date) as usage_quarter,\n date_trunc('MONTH', usage_date) as usage_month,\n date_trunc('WEEK', usage_date) as usage_week,\n u.*\n from usage_filtered as u\n left join prices as p\n on u.sku_name=p.sku_name\n and u.usage_unit=p.usage_unit\n and (u.usage_end_time between p.price_start_time and p.coalesced_price_end_time)\n),\n-- eval time_key param\nlist_priced_usd_with_time_key as (\n select\n identifier\n (\n case\n when :param_time_key = 'Quarter' then 'usage_quarter'\n when :param_time_key = 'Month' then 'usage_month'\n when :param_time_key = 'Week' then 'usage_week'\n else 'usage_date'\n end\n ) as time_key,\n *\n from list_priced_usd\n),\n-- eval tags param\ntag_entry_parsing as (\n select\n explode(split(\n if(trim(:param_single_tag_key) = '<USE TAG FILTER>', :param_tag_entries, :param_single_tag_key),\n ';'\n )) as tag_entry,\n contains(tag_entry, '=') as is_filter,\n if(is_filter, split(tag_entry, '=')[0], tag_entry) as tag_key\n),\n-- parse tag entries\ntag_entry_parsed as (\n select\n array_sort(collect_list(tag_key)) as all_keys,\n array_sort(collect_list(if(is_filter, tag_key, null))) as filter_keys,\n array_sort(collect_list(if(is_filter, tag_entry, null))) as filter_expected\n from tag_entry_parsing\n),\n-- match tag entries\nlist_priced_usd_with_matching_tag_kvp as (\n select * from (\n select\n transform(filter_keys, k -> concat(k, '=', custom_tags[k])) as filter_actual,\n transform(all_keys, k -> concat(k, '=', custom_tags[k])) as kvp,\n if(\n size(filter_keys) = 0 or filter_actual = filter_expected,\n array_join(kvp, ';'),\n ''\n ) as _custom_tag_key_value_pairs,\n if(_custom_tag_key_value_pairs = \"\", \"<MISMATCH>\", _custom_tag_key_value_pairs) as custom_tag_key_value_pairs,\n *\n from list_priced_usd_with_time_key, tag_entry_parsed\n ) where :param_show_tag_mismatch = 'Yes' or custom_tag_key_value_pairs != '<MISMATCH>'\n)\n,\n-- calc usage by period\ngrouped_usage_by_period as (\n select\n time_key as period_key,\n replace(replace(custom_tag_key_value_pairs, '<', '<'), '>', '>') as group_key,\n sum(usage_usd) as usage_usd\n from list_priced_usd_with_matching_tag_kvp\n group by all\n),\n-- calc periodic change\ngrouped_usage_change as (\n select\n period_key,\n group_key,\n usage_usd,\n lag(usage_usd, 1) over (partition by group_key order by period_key) as prev_usage_usd,\n round(try_divide(usage_usd - prev_usage_usd, prev_usage_usd) * 100, 2) as usage_change_percentage\n from grouped_usage_by_period\n),\ntotal_usage_change as (\n select\n period_key,\n '<b>TOTAL</b>' as group_key,\n sum(usage_usd) as usage_usd,\n lag(sum(usage_usd), 1) over (order by period_key) as prev_usage_usd,\n round(try_divide(sum(usage_usd) - prev_usage_usd, prev_usage_usd) * 100, 2) as usage_change_percentage\n from grouped_usage_by_period\n group by period_key\n),\n-- periods\nperiod_info as (\n select\n case\n when :param_time_key = 'Day' then current_date()\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date())\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date())\n end as current_period,\n case\n when :param_time_key = 'Day' then current_date() - interval 1 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 7 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 1 month)\n end as last_period,\n case\n when :param_time_key = 'Day' then current_date() - interval 2 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 14 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 2 month)\n end as 2_periods_ago,\n case\n when :param_time_key = 'Day' then current_date() - interval 3 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 21 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 3 month)\n end as 3_periods_ago,\n case\n when :param_time_key = 'Day' then current_date() - interval 4 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 28 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 4 month)\n end as 4_periods_ago,\n case\n when :param_time_key = 'Day' then current_date() - interval 5 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 35 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 5 month)\n end as 5_periods_ago\n),\n-- pivot change\nusage_change_pivot as (\n select\n case\n when period_key = current_period then 'Current period'\n when period_key = last_period then 'Last period'\n when period_key = 2_periods_ago then '2 periods ago'\n when period_key = 3_periods_ago then '3 periods ago'\n when period_key = 4_periods_ago then '4 periods ago'\n when period_key = 5_periods_ago then '5 periods ago'\n end as x_period_back,\n group_key,\n usage_usd,\n usage_change_percentage\n from (\n select * from grouped_usage_change, period_info\n union all\n select * from total_usage_change, period_info\n )\n),\n-- pivot all time\nall_time_usage_pivot as (\n select\n 'Start to End date' as x_period_back,\n group_key,\n sum(usage_usd) as usage_usd,\n null as usage_change_percentage\n from grouped_usage_by_period\n group by group_key\n),\n-- pivot total all time\nall_time_total_usage_pivot as (\n select\n 'Start to End date' as x_period_back,\n '<b>TOTAL</b>' as group_key,\n sum(usage_usd) as usage_usd,\n null as usage_change_percentage\n from grouped_usage_by_period\n),\nunion_usage_pivot as (\n select\n x_period_back,\n group_key,\n case\n when x_period_back = 'Start to End date' then string(usage_usd)\n else concat('<span style=\"zoom:1\">', usage_usd_str, '</span><span style=\"zoom:1;color:', change_color, '\"> ', usage_change_str, '</span>')\n end as usage_info\n from (\n select\n case\n when usage_usd >= 1e9 then concat(format_number(usage_usd / 1e9, 0), 'B')\n when usage_usd >= 1e6 then concat(format_number(usage_usd / 1e6, 0), 'M')\n when usage_usd >= 1e3 then concat(format_number(usage_usd / 1e3, 0), 'K')\n else format_number(usage_usd, 0)\n end as usage_usd_str,\n case\n when usage_change_percentage > 10 then '#00A972'\n when usage_change_percentage < -10 then '#FF3621'\n else '#919191'\n end as _change_color,\n concat('(', if(usage_change_percentage > 0, '+', ''), format_number(usage_change_percentage, 0), '%)') as _usage_change_str,\n coalesce(_change_color, '#919191') as change_color,\n coalesce(_usage_change_str, '') as usage_change_str,\n *\n from (\n select x_period_back, group_key, usage_usd, usage_change_percentage from usage_change_pivot\n union all\n select * from all_time_usage_pivot\n union all\n select * from all_time_total_usage_pivot\n )\n )\n)\n-- query\nselect\n group_key,\n concat('$', format_number(float(`Start to End date`), 0)) as `Start to End date`,\n float(`Start to End date`) as _all_time_usage_usd,\n 2 as _order,\n coalesce(`5 periods ago`, '<span style=\"zoom:1;color:#919191\">0</span>') as `5 periods ago`,\n coalesce(`4 periods ago`, '<span style=\"zoom:1;color:#919191\">0</span>') as `4 periods ago`,\n coalesce(`3 periods ago`, '<span style=\"zoom:1;color:#919191\">0</span>') as `3 periods ago`,\n coalesce(`2 periods ago`, '<span style=\"zoom:1;color:#919191\">0</span>') as `2 periods ago`,\n coalesce(`Last period`, '<span style=\"zoom:1;color:#919191\">0</span>') as `Last period`,\n coalesce(`Current period`, '<span style=\"zoom:1;color:#919191\">0</span>') as `Current period`\nfrom union_usage_pivot\npivot (\n first(usage_info)\n for x_period_back in (\n 'Start to End date',\n '5 periods ago',\n '4 periods ago',\n '3 periods ago',\n '2 periods ago',\n 'Last period',\n 'Current period'\n )\n)\nunion all\n(\n select\n '' as group_key,\n concat('<b><i>', date_format(:param_start_date, 'MMM dd yyyy'), ' - ', date_format(:param_end_date, 'MMM dd yyyy'), '</i></b>') as `Start to End date`,\n null as _all_time_usage_usd,\n 1 as _order,\n concat('<b><i>', date_format(5_periods_ago, 'MMM dd'), ' - ', date_format(4_periods_ago, 'MMM dd'), '</i></b>') as `5 periods ago`,\n concat('<b><i>', date_format(4_periods_ago, 'MMM dd'), ' - ', date_format(3_periods_ago, 'MMM dd'), '</i></b>') as `4 periods ago`,\n concat('<b><i>', date_format(3_periods_ago, 'MMM dd'), ' - ', date_format(2_periods_ago, 'MMM dd'), '</i></b>') as `3 periods ago`,\n concat('<b><i>', date_format(2_periods_ago, 'MMM dd'), ' - ', date_format(last_period, 'MMM dd'), '</i></b>') as `2 periods ago`,\n concat('<b><i>', date_format(last_period, 'MMM dd'), ' - ', date_format(current_period, 'MMM dd'), '</i></b>') as `Last period`,\n concat('<b><i>', date_format(current_period, 'MMM dd'), ' - Now', '</i></b>') as `Current period`\n from period_info\n)\norder by _order asc, _all_time_usage_usd desc\n", | |
| "parameters": [ | |
| { | |
| "displayName": "param_start_date", | |
| "keyword": "param_start_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now-12M/M" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_end_date", | |
| "keyword": "param_end_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now/d" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_workspace", | |
| "keyword": "param_workspace", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<ALL WORKSPACES>" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_time_key", | |
| "keyword": "param_time_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Week" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_single_tag_key", | |
| "keyword": "param_single_tag_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<USE TAG FILTER>" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_tag_entries", | |
| "keyword": "param_tag_entries", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Budget;Env" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_show_tag_mismatch", | |
| "keyword": "param_show_tag_mismatch", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Yes" | |
| } | |
| ] | |
| } | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "name": "usage_analyze_top_spending", | |
| "displayName": "usage_analyze_top_spending", | |
| "query": "with\nusage_with_ws_filtered_by_date as (\n select\n workspace_id as workspace,\n *\n from system.billing.usage\n where usage_date between :param_start_date and :param_end_date\n),\n-- apply workspace filter\nusage_filtered as (\n select\n *\n from usage_with_ws_filtered_by_date\n where workspace_id = 'MY_WORKSPACE_ID_HERE'\n),\n-- calc list priced usage in USD\nprices as (\n select coalesce(price_end_time, date_add(current_date, 1)) as coalesced_price_end_time, *\n from system.billing.list_prices\n where currency_code = 'USD'\n),\nlist_priced_usd as (\n select\n coalesce(u.usage_quantity * p.pricing.effective_list.default, 0) as usage_usd,\n date_trunc('QUARTER', usage_date) as usage_quarter,\n date_trunc('MONTH', usage_date) as usage_month,\n date_trunc('WEEK', usage_date) as usage_week,\n u.*\n from usage_filtered as u\n left join prices as p\n on u.sku_name=p.sku_name\n and u.usage_unit=p.usage_unit\n and (u.usage_end_time between p.price_start_time and p.coalesced_price_end_time)\n),\n-- eval time_key param\nlist_priced_usd_with_time_key as (\n select\n identifier\n (\n case\n when :param_time_key = 'Quarter' then 'usage_quarter'\n when :param_time_key = 'Month' then 'usage_month'\n when :param_time_key = 'Week' then 'usage_week'\n else 'usage_date'\n end\n ) as time_key,\n *\n from list_priced_usd\n),\n-- eval rank_key param\nlist_priced_usd_with_rank_key as (\n select\n struct(workspace) as rank_metadata,\n identifier\n (\n case\n when :param_rank_key = 'workspace' then 'rank_metadata'\n when :param_rank_key = 'run_as' then 'identity_metadata'\n else 'usage_metadata'\n end\n )[:param_rank_key] as rank_key,\n *\n from list_priced_usd_with_time_key\n),\n-- calc top ranked usage\ntop_ranked_usage as (\n select\n rank_key,\n true as is_top_ranked,\n sum(usage_usd) as usage_usd\n from list_priced_usd_with_rank_key\n where rank_key is not null\n group by rank_key\n order by usage_usd desc\n limit :param_top_n\n),\nlist_priced_usd_with_rank_key_normalized as (\n select\n if(is_top_ranked or u.rank_key is null, u.rank_key, '<OTHERS>') as rank_key,\n u.time_key,\n u.usage_usd\n from list_priced_usd_with_rank_key as u\n left join top_ranked_usage\n on u.rank_key = top_ranked_usage.rank_key\n where :param_show_null_rank_key = 'Yes' or u.rank_key is not null\n)\n\n-- query\nselect\n rank_key,\n time_key,\n sum(usage_usd) as usage_usd\nfrom list_priced_usd_with_rank_key_normalized\ngroup by all\n", | |
| "parameters": [ | |
| { | |
| "displayName": "param_start_date", | |
| "keyword": "param_start_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now-12M/M" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_end_date", | |
| "keyword": "param_end_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now/d" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_workspace", | |
| "keyword": "param_workspace", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<ALL WORKSPACES>" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_time_key", | |
| "keyword": "param_time_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Week" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_rank_key", | |
| "keyword": "param_rank_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "job_id" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_show_null_rank_key", | |
| "keyword": "param_show_null_rank_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Yes" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_top_n", | |
| "keyword": "param_top_n", | |
| "dataType": "INTEGER", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "INTEGER", | |
| "values": [ | |
| { | |
| "value": "10" | |
| } | |
| ] | |
| } | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "name": "usage_analyze_top_spending_overtime", | |
| "displayName": "usage_analyze_top_spending_overtime", | |
| "query": "with\nusage_with_ws_filtered_by_date as (\n select\n workspace_id as workspace,\n *\n from system.billing.usage\n where usage_date between :param_start_date and :param_end_date\n),\n-- apply workspace filter\nusage_filtered as (\n select\n *\n from usage_with_ws_filtered_by_date\n where workspace_id = 'MY_WORKSPACE_ID_HERE'\n),\n-- calc list priced usage in USD\nprices as (\n select coalesce(price_end_time, date_add(current_date, 1)) as coalesced_price_end_time, *\n from system.billing.list_prices\n where currency_code = 'USD'\n),\nlist_priced_usd as (\n select\n coalesce(u.usage_quantity * p.pricing.effective_list.default, 0) as usage_usd,\n date_trunc('QUARTER', usage_date) as usage_quarter,\n date_trunc('MONTH', usage_date) as usage_month,\n date_trunc('WEEK', usage_date) as usage_week,\n u.*\n from usage_filtered as u\n left join prices as p\n on u.sku_name=p.sku_name\n and u.usage_unit=p.usage_unit\n and (u.usage_end_time between p.price_start_time and p.coalesced_price_end_time)\n),\n-- eval time_key param\nlist_priced_usd_with_time_key as (\n select\n identifier\n (\n case\n when :param_time_key = 'Quarter' then 'usage_quarter'\n when :param_time_key = 'Month' then 'usage_month'\n when :param_time_key = 'Week' then 'usage_week'\n else 'usage_date'\n end\n ) as time_key,\n *\n from list_priced_usd\n),\n-- eval rank_key param\nlist_priced_usd_with_rank_key as (\n select\n struct(workspace) as rank_metadata,\n identifier\n (\n case\n when :param_rank_key = 'workspace' then 'rank_metadata'\n when :param_rank_key = 'run_as' then 'identity_metadata'\n else 'usage_metadata'\n end\n )[:param_rank_key] as rank_key,\n *\n from list_priced_usd_with_time_key\n),\n-- calc top ranked usage\ntop_ranked_usage as (\n select\n rank_key,\n true as is_top_ranked,\n sum(usage_usd) as usage_usd\n from list_priced_usd_with_rank_key\n where rank_key is not null\n group by rank_key\n order by usage_usd desc\n limit :param_top_n\n),\nlist_priced_usd_with_rank_key_normalized as (\n select\n if(is_top_ranked or u.rank_key is null, u.rank_key, '<OTHERS>') as rank_key,\n u.time_key,\n u.usage_usd\n from list_priced_usd_with_rank_key as u\n left join top_ranked_usage\n on u.rank_key = top_ranked_usage.rank_key\n where :param_show_null_rank_key = 'Yes' or u.rank_key is not null\n)\n,\n-- calc usage by period\ngrouped_usage_by_period as (\n select\n time_key as period_key,\n replace(replace(rank_key, '<', '<'), '>', '>') as group_key,\n sum(usage_usd) as usage_usd\n from list_priced_usd_with_rank_key_normalized\n group by all\n),\n-- calc periodic change\ngrouped_usage_change as (\n select\n period_key,\n group_key,\n usage_usd,\n lag(usage_usd, 1) over (partition by group_key order by period_key) as prev_usage_usd,\n round(try_divide(usage_usd - prev_usage_usd, prev_usage_usd) * 100, 2) as usage_change_percentage\n from grouped_usage_by_period\n),\ntotal_usage_change as (\n select\n period_key,\n '<b>TOTAL</b>' as group_key,\n sum(usage_usd) as usage_usd,\n lag(sum(usage_usd), 1) over (order by period_key) as prev_usage_usd,\n round(try_divide(sum(usage_usd) - prev_usage_usd, prev_usage_usd) * 100, 2) as usage_change_percentage\n from grouped_usage_by_period\n group by period_key\n),\n-- periods\nperiod_info as (\n select\n case\n when :param_time_key = 'Day' then current_date()\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date())\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date())\n end as current_period,\n case\n when :param_time_key = 'Day' then current_date() - interval 1 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 7 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 1 month)\n end as last_period,\n case\n when :param_time_key = 'Day' then current_date() - interval 2 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 14 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 2 month)\n end as 2_periods_ago,\n case\n when :param_time_key = 'Day' then current_date() - interval 3 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 21 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 3 month)\n end as 3_periods_ago,\n case\n when :param_time_key = 'Day' then current_date() - interval 4 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 28 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 4 month)\n end as 4_periods_ago,\n case\n when :param_time_key = 'Day' then current_date() - interval 5 day\n when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 35 day)\n when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 5 month)\n end as 5_periods_ago\n),\n-- pivot change\nusage_change_pivot as (\n select\n case\n when period_key = current_period then 'Current period'\n when period_key = last_period then 'Last period'\n when period_key = 2_periods_ago then '2 periods ago'\n when period_key = 3_periods_ago then '3 periods ago'\n when period_key = 4_periods_ago then '4 periods ago'\n when period_key = 5_periods_ago then '5 periods ago'\n end as x_period_back,\n group_key,\n usage_usd,\n usage_change_percentage\n from (\n select * from grouped_usage_change, period_info\n union all\n select * from total_usage_change, period_info\n )\n),\n-- pivot all time\nall_time_usage_pivot as (\n select\n 'Start to End date' as x_period_back,\n group_key,\n sum(usage_usd) as usage_usd,\n null as usage_change_percentage\n from grouped_usage_by_period\n group by group_key\n),\n-- pivot total all time\nall_time_total_usage_pivot as (\n select\n 'Start to End date' as x_period_back,\n '<b>TOTAL</b>' as group_key,\n sum(usage_usd) as usage_usd,\n null as usage_change_percentage\n from grouped_usage_by_period\n),\nunion_usage_pivot as (\n select\n x_period_back,\n group_key,\n case\n when x_period_back = 'Start to End date' then string(usage_usd)\n else concat('<span style=\"zoom:1\">', usage_usd_str, '</span><span style=\"zoom:1;color:', change_color, '\"> ', usage_change_str, '</span>')\n end as usage_info\n from (\n select\n case\n when usage_usd >= 1e9 then concat(format_number(usage_usd / 1e9, 0), 'B')\n when usage_usd >= 1e6 then concat(format_number(usage_usd / 1e6, 0), 'M')\n when usage_usd >= 1e3 then concat(format_number(usage_usd / 1e3, 0), 'K')\n else format_number(usage_usd, 0)\n end as usage_usd_str,\n case\n when usage_change_percentage > 10 then '#00A972'\n when usage_change_percentage < -10 then '#FF3621'\n else '#919191'\n end as _change_color,\n concat('(', if(usage_change_percentage > 0, '+', ''), format_number(usage_change_percentage, 0), '%)') as _usage_change_str,\n coalesce(_change_color, '#919191') as change_color,\n coalesce(_usage_change_str, '') as usage_change_str,\n *\n from (\n select x_period_back, group_key, usage_usd, usage_change_percentage from usage_change_pivot\n union all\n select * from all_time_usage_pivot\n union all\n select * from all_time_total_usage_pivot\n )\n )\n)\n-- query\nselect\n group_key,\n concat('$', format_number(float(`Start to End date`), 0)) as `Start to End date`,\n float(`Start to End date`) as _all_time_usage_usd,\n 2 as _order,\n coalesce(`5 periods ago`, '<span style=\"zoom:1;color:#919191\">0</span>') as `5 periods ago`,\n coalesce(`4 periods ago`, '<span style=\"zoom:1;color:#919191\">0</span>') as `4 periods ago`,\n coalesce(`3 periods ago`, '<span style=\"zoom:1;color:#919191\">0</span>') as `3 periods ago`,\n coalesce(`2 periods ago`, '<span style=\"zoom:1;color:#919191\">0</span>') as `2 periods ago`,\n coalesce(`Last period`, '<span style=\"zoom:1;color:#919191\">0</span>') as `Last period`,\n coalesce(`Current period`, '<span style=\"zoom:1;color:#919191\">0</span>') as `Current period`\nfrom union_usage_pivot\npivot (\n first(usage_info)\n for x_period_back in (\n 'Start to End date',\n '5 periods ago',\n '4 periods ago',\n '3 periods ago',\n '2 periods ago',\n 'Last period',\n 'Current period'\n )\n)\nunion all\n(\n select\n '' as group_key,\n concat('<b><i>', date_format(:param_start_date, 'MMM dd yyyy'), ' - ', date_format(:param_end_date, 'MMM dd yyyy'), '</i></b>') as `Start to End date`,\n null as _all_time_usage_usd,\n 1 as _order,\n concat('<b><i>', date_format(5_periods_ago, 'MMM dd'), ' - ', date_format(4_periods_ago, 'MMM dd'), '</i></b>') as `5 periods ago`,\n concat('<b><i>', date_format(4_periods_ago, 'MMM dd'), ' - ', date_format(3_periods_ago, 'MMM dd'), '</i></b>') as `4 periods ago`,\n concat('<b><i>', date_format(3_periods_ago, 'MMM dd'), ' - ', date_format(2_periods_ago, 'MMM dd'), '</i></b>') as `3 periods ago`,\n concat('<b><i>', date_format(2_periods_ago, 'MMM dd'), ' - ', date_format(last_period, 'MMM dd'), '</i></b>') as `2 periods ago`,\n concat('<b><i>', date_format(last_period, 'MMM dd'), ' - ', date_format(current_period, 'MMM dd'), '</i></b>') as `Last period`,\n concat('<b><i>', date_format(current_period, 'MMM dd'), ' - Now', '</i></b>') as `Current period`\n from period_info\n)\norder by _order asc, _all_time_usage_usd desc\n", | |
| "parameters": [ | |
| { | |
| "displayName": "param_start_date", | |
| "keyword": "param_start_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now-12M/M" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_end_date", | |
| "keyword": "param_end_date", | |
| "dataType": "DATE", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "DATE", | |
| "values": [ | |
| { | |
| "value": "now/d" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_workspace", | |
| "keyword": "param_workspace", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "<ALL WORKSPACES>" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_time_key", | |
| "keyword": "param_time_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Week" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_rank_key", | |
| "keyword": "param_rank_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "job_id" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_show_null_rank_key", | |
| "keyword": "param_show_null_rank_key", | |
| "dataType": "STRING", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "STRING", | |
| "values": [ | |
| { | |
| "value": "Yes" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "displayName": "param_top_n", | |
| "keyword": "param_top_n", | |
| "dataType": "INTEGER", | |
| "defaultSelection": { | |
| "values": { | |
| "dataType": "INTEGER", | |
| "values": [ | |
| { | |
| "value": "10" | |
| } | |
| ] | |
| } | |
| } | |
| } | |
| ] | |
| } | |
| ], | |
| "pages": [ | |
| { | |
| "name": "page1", | |
| "displayName": "New Page", | |
| "layout": [ | |
| { | |
| "widget": { | |
| "name": "globalStartDate", | |
| "queries": [ | |
| { | |
| "name": "globalStartDateselect_workspace", | |
| "query": { | |
| "datasetName": "select_workspace", | |
| "parameters": [ | |
| { | |
| "name": "param_start_date", | |
| "keyword": "param_start_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalStartDateselect_tag_key", | |
| "query": { | |
| "datasetName": "select_tag_key", | |
| "parameters": [ | |
| { | |
| "name": "param_start_date", | |
| "keyword": "param_start_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalStartDateselect_rank_key", | |
| "query": { | |
| "datasetName": "select_rank_key", | |
| "parameters": [ | |
| { | |
| "name": "param_start_date", | |
| "keyword": "param_start_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalStartDateusage_total", | |
| "query": { | |
| "datasetName": "usage_total", | |
| "parameters": [ | |
| { | |
| "name": "param_start_date", | |
| "keyword": "param_start_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalStartDateusage_overview", | |
| "query": { | |
| "datasetName": "usage_overview", | |
| "parameters": [ | |
| { | |
| "name": "param_start_date", | |
| "keyword": "param_start_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalStartDateusage_overview_overtime", | |
| "query": { | |
| "datasetName": "usage_overview_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_start_date", | |
| "keyword": "param_start_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalStartDateusage_analyze_tags_match", | |
| "query": { | |
| "datasetName": "usage_analyze_tags_match", | |
| "parameters": [ | |
| { | |
| "name": "param_start_date", | |
| "keyword": "param_start_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalStartDateusage_analyze_tags", | |
| "query": { | |
| "datasetName": "usage_analyze_tags", | |
| "parameters": [ | |
| { | |
| "name": "param_start_date", | |
| "keyword": "param_start_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalStartDateusage_analyze_tags_overtime", | |
| "query": { | |
| "datasetName": "usage_analyze_tags_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_start_date", | |
| "keyword": "param_start_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalStartDateusage_analyze_top_spending", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending", | |
| "parameters": [ | |
| { | |
| "name": "param_start_date", | |
| "keyword": "param_start_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalStartDateusage_analyze_top_spending_overtime", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_start_date", | |
| "keyword": "param_start_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "fields": [ | |
| { | |
| "parameterName": "param_start_date", | |
| "queryName": "globalStartDateselect_workspace" | |
| }, | |
| { | |
| "parameterName": "param_start_date", | |
| "queryName": "globalStartDateselect_tag_key" | |
| }, | |
| { | |
| "parameterName": "param_start_date", | |
| "queryName": "globalStartDateselect_rank_key" | |
| }, | |
| { | |
| "parameterName": "param_start_date", | |
| "queryName": "globalStartDateusage_total" | |
| }, | |
| { | |
| "parameterName": "param_start_date", | |
| "queryName": "globalStartDateusage_overview" | |
| }, | |
| { | |
| "parameterName": "param_start_date", | |
| "queryName": "globalStartDateusage_overview_overtime" | |
| }, | |
| { | |
| "parameterName": "param_start_date", | |
| "queryName": "globalStartDateusage_analyze_tags_match" | |
| }, | |
| { | |
| "parameterName": "param_start_date", | |
| "queryName": "globalStartDateusage_analyze_tags" | |
| }, | |
| { | |
| "parameterName": "param_start_date", | |
| "queryName": "globalStartDateusage_analyze_tags_overtime" | |
| }, | |
| { | |
| "parameterName": "param_start_date", | |
| "queryName": "globalStartDateusage_analyze_top_spending" | |
| }, | |
| { | |
| "parameterName": "param_start_date", | |
| "queryName": "globalStartDateusage_analyze_top_spending_overtime" | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "Start date" | |
| }, | |
| "version": 2, | |
| "widgetType": "filter-date-picker" | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 0, | |
| "width": 3, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "globalEndDate", | |
| "queries": [ | |
| { | |
| "name": "globalEndDateselect_workspace", | |
| "query": { | |
| "datasetName": "select_workspace", | |
| "parameters": [ | |
| { | |
| "name": "param_end_date", | |
| "keyword": "param_end_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalEndDateselect_tag_key", | |
| "query": { | |
| "datasetName": "select_tag_key", | |
| "parameters": [ | |
| { | |
| "name": "param_end_date", | |
| "keyword": "param_end_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalEndDateselect_rank_key", | |
| "query": { | |
| "datasetName": "select_rank_key", | |
| "parameters": [ | |
| { | |
| "name": "param_end_date", | |
| "keyword": "param_end_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalEndDateusage_total", | |
| "query": { | |
| "datasetName": "usage_total", | |
| "parameters": [ | |
| { | |
| "name": "param_end_date", | |
| "keyword": "param_end_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalEndDateusage_overview", | |
| "query": { | |
| "datasetName": "usage_overview", | |
| "parameters": [ | |
| { | |
| "name": "param_end_date", | |
| "keyword": "param_end_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalEndDateusage_overview_overtime", | |
| "query": { | |
| "datasetName": "usage_overview_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_end_date", | |
| "keyword": "param_end_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalEndDateusage_analyze_tags_match", | |
| "query": { | |
| "datasetName": "usage_analyze_tags_match", | |
| "parameters": [ | |
| { | |
| "name": "param_end_date", | |
| "keyword": "param_end_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalEndDateusage_analyze_tags", | |
| "query": { | |
| "datasetName": "usage_analyze_tags", | |
| "parameters": [ | |
| { | |
| "name": "param_end_date", | |
| "keyword": "param_end_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalEndDateusage_analyze_tags_overtime", | |
| "query": { | |
| "datasetName": "usage_analyze_tags_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_end_date", | |
| "keyword": "param_end_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalEndDateusage_analyze_top_spending", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending", | |
| "parameters": [ | |
| { | |
| "name": "param_end_date", | |
| "keyword": "param_end_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "globalEndDateusage_analyze_top_spending_overtime", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_end_date", | |
| "keyword": "param_end_date" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "fields": [ | |
| { | |
| "parameterName": "param_end_date", | |
| "queryName": "globalEndDateselect_workspace" | |
| }, | |
| { | |
| "parameterName": "param_end_date", | |
| "queryName": "globalEndDateselect_tag_key" | |
| }, | |
| { | |
| "parameterName": "param_end_date", | |
| "queryName": "globalEndDateselect_rank_key" | |
| }, | |
| { | |
| "parameterName": "param_end_date", | |
| "queryName": "globalEndDateusage_total" | |
| }, | |
| { | |
| "parameterName": "param_end_date", | |
| "queryName": "globalEndDateusage_overview" | |
| }, | |
| { | |
| "parameterName": "param_end_date", | |
| "queryName": "globalEndDateusage_overview_overtime" | |
| }, | |
| { | |
| "parameterName": "param_end_date", | |
| "queryName": "globalEndDateusage_analyze_tags_match" | |
| }, | |
| { | |
| "parameterName": "param_end_date", | |
| "queryName": "globalEndDateusage_analyze_tags" | |
| }, | |
| { | |
| "parameterName": "param_end_date", | |
| "queryName": "globalEndDateusage_analyze_tags_overtime" | |
| }, | |
| { | |
| "parameterName": "param_end_date", | |
| "queryName": "globalEndDateusage_analyze_top_spending" | |
| }, | |
| { | |
| "parameterName": "param_end_date", | |
| "queryName": "globalEndDateusage_analyze_top_spending_overtime" | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "End date" | |
| }, | |
| "version": 2, | |
| "widgetType": "filter-date-picker" | |
| } | |
| }, | |
| "position": { | |
| "x": 3, | |
| "y": 0, | |
| "width": 3, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "totalUsage", | |
| "queries": [ | |
| { | |
| "name": "main_query", | |
| "query": { | |
| "datasetName": "usage_total", | |
| "fields": [ | |
| { | |
| "name": "total_usage_usd", | |
| "expression": "`total_usage_usd`" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "target": { | |
| "displayName": "total_usage_usd", | |
| "fieldName": "total_usage_usd" | |
| } | |
| }, | |
| "version": 2, | |
| "widgetType": "counter" | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 101, | |
| "width": 6, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s1ParamTimeKey", | |
| "queries": [ | |
| { | |
| "name": "s1ParamTimeKeyselect_time_key_overview", | |
| "query": { | |
| "datasetName": "select_time_key_overview", | |
| "fields": [ | |
| { | |
| "name": "time_key", | |
| "expression": "`time_key`" | |
| }, | |
| { | |
| "name": "time_key_associativity", | |
| "expression": "COUNT_IF(`associative_filter_predicate_group`)" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s1ParamTimeKeyusage_overview", | |
| "query": { | |
| "datasetName": "usage_overview", | |
| "parameters": [ | |
| { | |
| "name": "param_time_key", | |
| "keyword": "param_time_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s1ParamTimeKeyusage_overview_overtime", | |
| "query": { | |
| "datasetName": "usage_overview_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_time_key", | |
| "keyword": "param_time_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "fields": [ | |
| { | |
| "displayName": "time_key", | |
| "fieldName": "time_key", | |
| "queryName": "s1ParamTimeKeyselect_time_key_overview" | |
| }, | |
| { | |
| "parameterName": "param_time_key", | |
| "queryName": "s1ParamTimeKeyusage_overview" | |
| }, | |
| { | |
| "parameterName": "param_time_key", | |
| "queryName": "s1ParamTimeKeyusage_overview_overtime" | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "View date by" | |
| }, | |
| "version": 2, | |
| "widgetType": "filter-single-select" | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 106, | |
| "width": 3, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s1ParamGroupKey", | |
| "queries": [ | |
| { | |
| "name": "s1ParamGroupKeyselect_group_key", | |
| "query": { | |
| "datasetName": "select_group_key", | |
| "fields": [ | |
| { | |
| "name": "group_key", | |
| "expression": "`group_key`" | |
| }, | |
| { | |
| "name": "group_key_associativity", | |
| "expression": "COUNT_IF(`associative_filter_predicate_group`)" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s1ParamGroupKeyusage_overview", | |
| "query": { | |
| "datasetName": "usage_overview", | |
| "parameters": [ | |
| { | |
| "name": "param_group_key", | |
| "keyword": "param_group_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s1ParamGroupKeyusage_overview_overtime", | |
| "query": { | |
| "datasetName": "usage_overview_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_group_key", | |
| "keyword": "param_group_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "fields": [ | |
| { | |
| "displayName": "group_key", | |
| "fieldName": "group_key", | |
| "queryName": "s1ParamGroupKeyselect_group_key" | |
| }, | |
| { | |
| "parameterName": "param_group_key", | |
| "queryName": "s1ParamGroupKeyusage_overview" | |
| }, | |
| { | |
| "parameterName": "param_group_key", | |
| "queryName": "s1ParamGroupKeyusage_overview_overtime" | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "Group by" | |
| }, | |
| "version": 2, | |
| "widgetType": "filter-single-select" | |
| } | |
| }, | |
| "position": { | |
| "x": 3, | |
| "y": 106, | |
| "width": 3, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "group_key_usage_time_series", | |
| "queries": [ | |
| { | |
| "name": "main_query", | |
| "query": { | |
| "datasetName": "usage_overview", | |
| "fields": [ | |
| { | |
| "name": "group_key", | |
| "expression": "`group_key`" | |
| }, | |
| { | |
| "name": "time_key", | |
| "expression": "`time_key`" | |
| }, | |
| { | |
| "name": "sum(usage_usd)", | |
| "expression": "SUM(`usage_usd`)" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "color": { | |
| "displayName": "Key", | |
| "fieldName": "group_key", | |
| "legend": { | |
| "position": "right", | |
| "title": "Key" | |
| }, | |
| "scale": { | |
| "mappings": [ | |
| { | |
| "color": "#ffe4ba", | |
| "value": "<OTHERS>" | |
| }, | |
| { | |
| "color": "#dedede", | |
| "value": null | |
| } | |
| ], | |
| "type": "categorical" | |
| } | |
| }, | |
| "x": { | |
| "axis": { | |
| "hideTitle": true | |
| }, | |
| "displayName": "time_key", | |
| "fieldName": "time_key", | |
| "scale": { | |
| "type": "temporal" | |
| } | |
| }, | |
| "y": { | |
| "axis": { | |
| "title": "usage (USD)" | |
| }, | |
| "displayName": "usage (USD)", | |
| "fieldName": "sum(usage_usd)", | |
| "scale": { | |
| "type": "quantitative" | |
| } | |
| } | |
| }, | |
| "version": 3, | |
| "widgetType": "bar" | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 107, | |
| "width": 6, | |
| "height": 8 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "group_key_usage_overtime", | |
| "queries": [ | |
| { | |
| "name": "main_query", | |
| "query": { | |
| "datasetName": "usage_overview_overtime", | |
| "fields": [ | |
| { | |
| "name": "group_key", | |
| "expression": "`group_key`" | |
| }, | |
| { | |
| "name": "Start to End date", | |
| "expression": "`Start to End date`" | |
| }, | |
| { | |
| "name": "5 periods ago", | |
| "expression": "`5 periods ago`" | |
| }, | |
| { | |
| "name": "4 periods ago", | |
| "expression": "`4 periods ago`" | |
| }, | |
| { | |
| "name": "3 periods ago", | |
| "expression": "`3 periods ago`" | |
| }, | |
| { | |
| "name": "2 periods ago", | |
| "expression": "`2 periods ago`" | |
| }, | |
| { | |
| "name": "Last period", | |
| "expression": "`Last period`" | |
| }, | |
| { | |
| "name": "Current period", | |
| "expression": "`Current period`" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "allowHTMLByDefault": false, | |
| "condensed": false, | |
| "encodings": { | |
| "columns": [ | |
| { | |
| "alignContent": "left", | |
| "allowHTML": true, | |
| "allowSearch": true, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "Key", | |
| "fieldName": "group_key", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10000, | |
| "preserveWhitespace": false, | |
| "title": "Key", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "right", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "Start to End date", | |
| "fieldName": "Start to End date", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10001, | |
| "preserveWhitespace": false, | |
| "title": "Start to End date", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "5 periods ago", | |
| "fieldName": "5 periods ago", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10002, | |
| "preserveWhitespace": false, | |
| "title": "5 periods ago", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "4 periods ago", | |
| "fieldName": "4 periods ago", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10003, | |
| "preserveWhitespace": false, | |
| "title": "4 periods ago", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "3 periods ago", | |
| "fieldName": "3 periods ago", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10004, | |
| "preserveWhitespace": false, | |
| "title": "3 periods ago", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "2 periods ago", | |
| "fieldName": "2 periods ago", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10005, | |
| "preserveWhitespace": false, | |
| "title": "2 periods ago", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "Last period", | |
| "fieldName": "Last period", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10006, | |
| "preserveWhitespace": false, | |
| "title": "Last period", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "Current period", | |
| "fieldName": "Current period", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10007, | |
| "preserveWhitespace": false, | |
| "title": "Current period", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "Usage (USD) over past periods" | |
| }, | |
| "invisibleColumns": [], | |
| "itemsPerPage": 10, | |
| "paginationSize": "default", | |
| "version": 1, | |
| "widgetType": "table", | |
| "withRowNumber": false | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 115, | |
| "width": 6, | |
| "height": 10 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s2Title", | |
| "queries": [ | |
| { | |
| "name": "main_query", | |
| "query": { | |
| "datasetName": "static_text", | |
| "fields": [ | |
| { | |
| "name": "title_tags", | |
| "expression": "`title_tags`" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "target": { | |
| "displayName": "title_tags", | |
| "fieldName": "title_tags" | |
| } | |
| }, | |
| "version": 2, | |
| "widgetType": "counter" | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 200, | |
| "width": 6, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "tagMatchPercentage", | |
| "queries": [ | |
| { | |
| "name": "main_query", | |
| "query": { | |
| "datasetName": "usage_analyze_tags_match", | |
| "fields": [ | |
| { | |
| "name": "tag_match_percentage", | |
| "expression": "`tag_match_percentage`" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "target": { | |
| "displayName": "tag_match_percentage", | |
| "fieldName": "tag_match_percentage" | |
| } | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "Tag matching %" | |
| }, | |
| "version": 2, | |
| "widgetType": "counter" | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 201, | |
| "width": 2, | |
| "height": 2 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s2ParamTagKey", | |
| "queries": [ | |
| { | |
| "name": "s2ParamTagKeyselect_tag_key", | |
| "query": { | |
| "datasetName": "select_tag_key", | |
| "fields": [ | |
| { | |
| "name": "tag_key", | |
| "expression": "`tag_key`" | |
| }, | |
| { | |
| "name": "tag_key_associativity", | |
| "expression": "COUNT_IF(`associative_filter_predicate_group`)" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s2ParamTagKeyusage_analyze_tags_match", | |
| "query": { | |
| "datasetName": "usage_analyze_tags_match", | |
| "parameters": [ | |
| { | |
| "name": "param_single_tag_key", | |
| "keyword": "param_single_tag_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s2ParamTagKeyusage_analyze_tags", | |
| "query": { | |
| "datasetName": "usage_analyze_tags", | |
| "parameters": [ | |
| { | |
| "name": "param_single_tag_key", | |
| "keyword": "param_single_tag_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s2ParamTagKeyusage_analyze_tags_overtime", | |
| "query": { | |
| "datasetName": "usage_analyze_tags_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_single_tag_key", | |
| "keyword": "param_single_tag_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "fields": [ | |
| { | |
| "displayName": "tag_key", | |
| "fieldName": "tag_key", | |
| "queryName": "s2ParamTagKeyselect_tag_key" | |
| }, | |
| { | |
| "parameterName": "param_single_tag_key", | |
| "queryName": "s2ParamTagKeyusage_analyze_tags_match" | |
| }, | |
| { | |
| "parameterName": "param_single_tag_key", | |
| "queryName": "s2ParamTagKeyusage_analyze_tags" | |
| }, | |
| { | |
| "parameterName": "param_single_tag_key", | |
| "queryName": "s2ParamTagKeyusage_analyze_tags_overtime" | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "Tag key" | |
| }, | |
| "version": 2, | |
| "widgetType": "filter-single-select" | |
| } | |
| }, | |
| "position": { | |
| "x": 2, | |
| "y": 201, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s2ParamTagKeyDesc", | |
| "textbox_spec": "Select one tag key, or select `<USE TAG FILTER>` for advanced tag filtering.\n" | |
| }, | |
| "position": { | |
| "x": 4, | |
| "y": 201, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s2ParamTagEntries", | |
| "queries": [ | |
| { | |
| "name": "s2ParamTagEntriesusage_analyze_tags_match", | |
| "query": { | |
| "datasetName": "usage_analyze_tags_match", | |
| "parameters": [ | |
| { | |
| "name": "param_tag_entries", | |
| "keyword": "param_tag_entries" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s2ParamTagEntriesusage_analyze_tags", | |
| "query": { | |
| "datasetName": "usage_analyze_tags", | |
| "parameters": [ | |
| { | |
| "name": "param_tag_entries", | |
| "keyword": "param_tag_entries" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s2ParamTagEntriesusage_analyze_tags_overtime", | |
| "query": { | |
| "datasetName": "usage_analyze_tags_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_tag_entries", | |
| "keyword": "param_tag_entries" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "fields": [ | |
| { | |
| "parameterName": "param_tag_entries", | |
| "queryName": "s2ParamTagEntriesusage_analyze_tags_match" | |
| }, | |
| { | |
| "parameterName": "param_tag_entries", | |
| "queryName": "s2ParamTagEntriesusage_analyze_tags" | |
| }, | |
| { | |
| "parameterName": "param_tag_entries", | |
| "queryName": "s2ParamTagEntriesusage_analyze_tags_overtime" | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "Tag filter" | |
| }, | |
| "version": 2, | |
| "widgetType": "filter-single-select" | |
| } | |
| }, | |
| "position": { | |
| "x": 2, | |
| "y": 202, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s2ParamTagEntriesDesc", | |
| "textbox_spec": "Input is formated as `<entry1>;<entry2>;<entry3>`.\nEach entry is specified as either `<key>` which match all values of the key, or `<key>=<value>` which matches only the specified value of the key.\n" | |
| }, | |
| "position": { | |
| "x": 4, | |
| "y": 202, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s2ParamTimeKey", | |
| "queries": [ | |
| { | |
| "name": "s2ParamTimeKeyselect_time_key_tag", | |
| "query": { | |
| "datasetName": "select_time_key_tag", | |
| "fields": [ | |
| { | |
| "name": "time_key", | |
| "expression": "`time_key`" | |
| }, | |
| { | |
| "name": "time_key_associativity", | |
| "expression": "COUNT_IF(`associative_filter_predicate_group`)" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s2ParamTimeKeyusage_analyze_tags", | |
| "query": { | |
| "datasetName": "usage_analyze_tags", | |
| "parameters": [ | |
| { | |
| "name": "param_time_key", | |
| "keyword": "param_time_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s2ParamTimeKeyusage_analyze_tags_overtime", | |
| "query": { | |
| "datasetName": "usage_analyze_tags_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_time_key", | |
| "keyword": "param_time_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "fields": [ | |
| { | |
| "displayName": "time_key", | |
| "fieldName": "time_key", | |
| "queryName": "s2ParamTimeKeyselect_time_key_tag" | |
| }, | |
| { | |
| "parameterName": "param_time_key", | |
| "queryName": "s2ParamTimeKeyusage_analyze_tags" | |
| }, | |
| { | |
| "parameterName": "param_time_key", | |
| "queryName": "s2ParamTimeKeyusage_analyze_tags_overtime" | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "View date by" | |
| }, | |
| "version": 2, | |
| "widgetType": "filter-single-select" | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 203, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s2ParamShowMismatched", | |
| "queries": [ | |
| { | |
| "name": "s2ParamShowMismatchedselect_yes_no_tag_show_mismatch", | |
| "query": { | |
| "datasetName": "select_yes_no_tag_show_mismatch", | |
| "fields": [ | |
| { | |
| "name": "toggle", | |
| "expression": "`toggle`" | |
| }, | |
| { | |
| "name": "toggle_associativity", | |
| "expression": "COUNT_IF(`associative_filter_predicate_group`)" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s2ParamShowMismatchedusage_analyze_tags", | |
| "query": { | |
| "datasetName": "usage_analyze_tags", | |
| "parameters": [ | |
| { | |
| "name": "param_show_tag_mismatch", | |
| "keyword": "param_show_tag_mismatch" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s2ParamShowMismatchedusage_analyze_tags_overtime", | |
| "query": { | |
| "datasetName": "usage_analyze_tags_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_show_tag_mismatch", | |
| "keyword": "param_show_tag_mismatch" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "fields": [ | |
| { | |
| "displayName": "toggle", | |
| "fieldName": "toggle", | |
| "queryName": "s2ParamShowMismatchedselect_yes_no_tag_show_mismatch" | |
| }, | |
| { | |
| "parameterName": "param_show_tag_mismatch", | |
| "queryName": "s2ParamShowMismatchedusage_analyze_tags" | |
| }, | |
| { | |
| "parameterName": "param_show_tag_mismatch", | |
| "queryName": "s2ParamShowMismatchedusage_analyze_tags_overtime" | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "Show mismatched" | |
| }, | |
| "version": 2, | |
| "widgetType": "filter-single-select" | |
| } | |
| }, | |
| "position": { | |
| "x": 2, | |
| "y": 203, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s2ParamShowMismatchedDesc", | |
| "textbox_spec": "Show usage without the matching tags as `<MISMATCH>`\n" | |
| }, | |
| "position": { | |
| "x": 4, | |
| "y": 203, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "custom_tag_key_value_pairs_usage_time_series", | |
| "queries": [ | |
| { | |
| "name": "main_query", | |
| "query": { | |
| "datasetName": "usage_analyze_tags", | |
| "fields": [ | |
| { | |
| "name": "custom_tag_key_value_pairs", | |
| "expression": "`custom_tag_key_value_pairs`" | |
| }, | |
| { | |
| "name": "time_key", | |
| "expression": "`time_key`" | |
| }, | |
| { | |
| "name": "sum(usage_usd)", | |
| "expression": "SUM(`usage_usd`)" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "color": { | |
| "displayName": "Tag Key-Value Pairs", | |
| "fieldName": "custom_tag_key_value_pairs", | |
| "legend": { | |
| "position": "right", | |
| "title": "Tag Key-Value Pairs" | |
| }, | |
| "scale": { | |
| "mappings": [ | |
| { | |
| "color": "#dedede", | |
| "value": "<MISMATCH>" | |
| } | |
| ], | |
| "type": "categorical" | |
| } | |
| }, | |
| "x": { | |
| "axis": { | |
| "hideTitle": true | |
| }, | |
| "displayName": "time_key", | |
| "fieldName": "time_key", | |
| "scale": { | |
| "type": "temporal" | |
| } | |
| }, | |
| "y": { | |
| "axis": { | |
| "title": "usage (USD)" | |
| }, | |
| "displayName": "usage (USD)", | |
| "fieldName": "sum(usage_usd)", | |
| "scale": { | |
| "type": "quantitative" | |
| } | |
| } | |
| }, | |
| "version": 3, | |
| "widgetType": "bar" | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 204, | |
| "width": 6, | |
| "height": 8 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "custom_tag_key_value_pairs_usage_overtime", | |
| "queries": [ | |
| { | |
| "name": "main_query", | |
| "query": { | |
| "datasetName": "usage_analyze_tags_overtime", | |
| "fields": [ | |
| { | |
| "name": "group_key", | |
| "expression": "`group_key`" | |
| }, | |
| { | |
| "name": "Start to End date", | |
| "expression": "`Start to End date`" | |
| }, | |
| { | |
| "name": "5 periods ago", | |
| "expression": "`5 periods ago`" | |
| }, | |
| { | |
| "name": "4 periods ago", | |
| "expression": "`4 periods ago`" | |
| }, | |
| { | |
| "name": "3 periods ago", | |
| "expression": "`3 periods ago`" | |
| }, | |
| { | |
| "name": "2 periods ago", | |
| "expression": "`2 periods ago`" | |
| }, | |
| { | |
| "name": "Last period", | |
| "expression": "`Last period`" | |
| }, | |
| { | |
| "name": "Current period", | |
| "expression": "`Current period`" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "allowHTMLByDefault": false, | |
| "condensed": false, | |
| "encodings": { | |
| "columns": [ | |
| { | |
| "alignContent": "left", | |
| "allowHTML": true, | |
| "allowSearch": true, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "Tag Key-Value Pairs", | |
| "fieldName": "group_key", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10000, | |
| "preserveWhitespace": false, | |
| "title": "Tag Key-Value Pairs", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "right", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "Start to End date", | |
| "fieldName": "Start to End date", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10001, | |
| "preserveWhitespace": false, | |
| "title": "Start to End date", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "5 periods ago", | |
| "fieldName": "5 periods ago", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10002, | |
| "preserveWhitespace": false, | |
| "title": "5 periods ago", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "4 periods ago", | |
| "fieldName": "4 periods ago", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10003, | |
| "preserveWhitespace": false, | |
| "title": "4 periods ago", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "3 periods ago", | |
| "fieldName": "3 periods ago", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10004, | |
| "preserveWhitespace": false, | |
| "title": "3 periods ago", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "2 periods ago", | |
| "fieldName": "2 periods ago", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10005, | |
| "preserveWhitespace": false, | |
| "title": "2 periods ago", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "Last period", | |
| "fieldName": "Last period", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10006, | |
| "preserveWhitespace": false, | |
| "title": "Last period", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "Current period", | |
| "fieldName": "Current period", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10007, | |
| "preserveWhitespace": false, | |
| "title": "Current period", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "Usage (USD) over past periods" | |
| }, | |
| "invisibleColumns": [], | |
| "itemsPerPage": 10, | |
| "paginationSize": "default", | |
| "version": 1, | |
| "widgetType": "table", | |
| "withRowNumber": false | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 212, | |
| "width": 6, | |
| "height": 10 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s3Title", | |
| "queries": [ | |
| { | |
| "name": "main_query", | |
| "query": { | |
| "datasetName": "static_text", | |
| "fields": [ | |
| { | |
| "name": "title_top", | |
| "expression": "`title_top`" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "target": { | |
| "displayName": "title_top", | |
| "fieldName": "title_top" | |
| } | |
| }, | |
| "version": 2, | |
| "widgetType": "counter" | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 300, | |
| "width": 6, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s3ParamTopN", | |
| "queries": [ | |
| { | |
| "name": "s3ParamTopNusage_analyze_top_spending", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending", | |
| "parameters": [ | |
| { | |
| "name": "param_top_n", | |
| "keyword": "param_top_n" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s3ParamTopNusage_analyze_top_spending_overtime", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_top_n", | |
| "keyword": "param_top_n" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "fields": [ | |
| { | |
| "parameterName": "param_top_n", | |
| "queryName": "s3ParamTopNusage_analyze_top_spending" | |
| }, | |
| { | |
| "parameterName": "param_top_n", | |
| "queryName": "s3ParamTopNusage_analyze_top_spending_overtime" | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "Top N" | |
| }, | |
| "version": 2, | |
| "widgetType": "filter-single-select" | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 301, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s3ParamRankKey", | |
| "queries": [ | |
| { | |
| "name": "s3ParamRankKeyselect_rank_key", | |
| "query": { | |
| "datasetName": "select_rank_key", | |
| "fields": [ | |
| { | |
| "name": "rank_key", | |
| "expression": "`rank_key`" | |
| }, | |
| { | |
| "name": "rank_key_associativity", | |
| "expression": "COUNT_IF(`associative_filter_predicate_group`)" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s3ParamRankKeyusage_analyze_top_spending", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending", | |
| "parameters": [ | |
| { | |
| "name": "param_rank_key", | |
| "keyword": "param_rank_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s3ParamRankKeyusage_analyze_top_spending_overtime", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_rank_key", | |
| "keyword": "param_rank_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "fields": [ | |
| { | |
| "displayName": "rank_key", | |
| "fieldName": "rank_key", | |
| "queryName": "s3ParamRankKeyselect_rank_key" | |
| }, | |
| { | |
| "parameterName": "param_rank_key", | |
| "queryName": "s3ParamRankKeyusage_analyze_top_spending" | |
| }, | |
| { | |
| "parameterName": "param_rank_key", | |
| "queryName": "s3ParamRankKeyusage_analyze_top_spending_overtime" | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "Rank by" | |
| }, | |
| "version": 2, | |
| "widgetType": "filter-single-select" | |
| } | |
| }, | |
| "position": { | |
| "x": 2, | |
| "y": 301, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s3ParamRankKeyDesc", | |
| "textbox_spec": "Select `run_as`, or any field from [usage_metadata](https://docs.databricks.com/en/admin/system-tables/billing.html#usage-metadata) to rank the usage.\n" | |
| }, | |
| "position": { | |
| "x": 4, | |
| "y": 301, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s3ParamTimeKey", | |
| "queries": [ | |
| { | |
| "name": "s3ParamTimeKeyselect_time_key_top", | |
| "query": { | |
| "datasetName": "select_time_key_top", | |
| "fields": [ | |
| { | |
| "name": "time_key", | |
| "expression": "`time_key`" | |
| }, | |
| { | |
| "name": "time_key_associativity", | |
| "expression": "COUNT_IF(`associative_filter_predicate_group`)" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s3ParamTimeKeyusage_analyze_top_spending", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending", | |
| "parameters": [ | |
| { | |
| "name": "param_time_key", | |
| "keyword": "param_time_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s3ParamTimeKeyusage_analyze_top_spending_overtime", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_time_key", | |
| "keyword": "param_time_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "fields": [ | |
| { | |
| "displayName": "time_key", | |
| "fieldName": "time_key", | |
| "queryName": "s3ParamTimeKeyselect_time_key_top" | |
| }, | |
| { | |
| "parameterName": "param_time_key", | |
| "queryName": "s3ParamTimeKeyusage_analyze_top_spending" | |
| }, | |
| { | |
| "parameterName": "param_time_key", | |
| "queryName": "s3ParamTimeKeyusage_analyze_top_spending_overtime" | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "View date by" | |
| }, | |
| "version": 2, | |
| "widgetType": "filter-single-select" | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 302, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s3ParamShowNull", | |
| "queries": [ | |
| { | |
| "name": "s3ParamShowNullselect_yes_no_top_show_null", | |
| "query": { | |
| "datasetName": "select_yes_no_top_show_null", | |
| "fields": [ | |
| { | |
| "name": "toggle", | |
| "expression": "`toggle`" | |
| }, | |
| { | |
| "name": "toggle_associativity", | |
| "expression": "COUNT_IF(`associative_filter_predicate_group`)" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s3ParamShowNullusage_analyze_top_spending", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending", | |
| "parameters": [ | |
| { | |
| "name": "param_show_null_rank_key", | |
| "keyword": "param_show_null_rank_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| }, | |
| { | |
| "name": "s3ParamShowNullusage_analyze_top_spending_overtime", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending_overtime", | |
| "parameters": [ | |
| { | |
| "name": "param_show_null_rank_key", | |
| "keyword": "param_show_null_rank_key" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "fields": [ | |
| { | |
| "displayName": "toggle", | |
| "fieldName": "toggle", | |
| "queryName": "s3ParamShowNullselect_yes_no_top_show_null" | |
| }, | |
| { | |
| "parameterName": "param_show_null_rank_key", | |
| "queryName": "s3ParamShowNullusage_analyze_top_spending" | |
| }, | |
| { | |
| "parameterName": "param_show_null_rank_key", | |
| "queryName": "s3ParamShowNullusage_analyze_top_spending_overtime" | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "Show null" | |
| }, | |
| "version": 2, | |
| "widgetType": "filter-single-select" | |
| } | |
| }, | |
| "position": { | |
| "x": 2, | |
| "y": 302, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "s3ParamShowNullDesc", | |
| "textbox_spec": "Show usages with the \"Rank by\" field being `null`.\n" | |
| }, | |
| "position": { | |
| "x": 4, | |
| "y": 302, | |
| "width": 2, | |
| "height": 1 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "rank_key_usage_time_series", | |
| "queries": [ | |
| { | |
| "name": "main_query", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending", | |
| "fields": [ | |
| { | |
| "name": "rank_key", | |
| "expression": "`rank_key`" | |
| }, | |
| { | |
| "name": "time_key", | |
| "expression": "`time_key`" | |
| }, | |
| { | |
| "name": "sum(usage_usd)", | |
| "expression": "SUM(`usage_usd`)" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "encodings": { | |
| "color": { | |
| "displayName": "Top N and Others", | |
| "fieldName": "rank_key", | |
| "legend": { | |
| "position": "right", | |
| "title": "Top N and Others" | |
| }, | |
| "scale": { | |
| "mappings": [ | |
| { | |
| "color": "#ffe4ba", | |
| "value": "<OTHERS>" | |
| }, | |
| { | |
| "color": "#dedede", | |
| "value": null | |
| } | |
| ], | |
| "type": "categorical" | |
| } | |
| }, | |
| "x": { | |
| "axis": { | |
| "hideTitle": true | |
| }, | |
| "displayName": "time_key", | |
| "fieldName": "time_key", | |
| "scale": { | |
| "type": "temporal" | |
| } | |
| }, | |
| "y": { | |
| "axis": { | |
| "title": "usage (USD)" | |
| }, | |
| "displayName": "usage (USD)", | |
| "fieldName": "sum(usage_usd)", | |
| "scale": { | |
| "type": "quantitative" | |
| } | |
| } | |
| }, | |
| "version": 3, | |
| "widgetType": "bar" | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 303, | |
| "width": 6, | |
| "height": 8 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "rank_key_usage_overtime", | |
| "queries": [ | |
| { | |
| "name": "main_query", | |
| "query": { | |
| "datasetName": "usage_analyze_top_spending_overtime", | |
| "fields": [ | |
| { | |
| "name": "group_key", | |
| "expression": "`group_key`" | |
| }, | |
| { | |
| "name": "Start to End date", | |
| "expression": "`Start to End date`" | |
| }, | |
| { | |
| "name": "5 periods ago", | |
| "expression": "`5 periods ago`" | |
| }, | |
| { | |
| "name": "4 periods ago", | |
| "expression": "`4 periods ago`" | |
| }, | |
| { | |
| "name": "3 periods ago", | |
| "expression": "`3 periods ago`" | |
| }, | |
| { | |
| "name": "2 periods ago", | |
| "expression": "`2 periods ago`" | |
| }, | |
| { | |
| "name": "Last period", | |
| "expression": "`Last period`" | |
| }, | |
| { | |
| "name": "Current period", | |
| "expression": "`Current period`" | |
| } | |
| ], | |
| "disaggregated": false | |
| } | |
| } | |
| ], | |
| "spec": { | |
| "allowHTMLByDefault": false, | |
| "condensed": false, | |
| "encodings": { | |
| "columns": [ | |
| { | |
| "alignContent": "left", | |
| "allowHTML": true, | |
| "allowSearch": true, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "Top N and Others", | |
| "fieldName": "group_key", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10000, | |
| "preserveWhitespace": false, | |
| "title": "Top N and Others", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "right", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "Start to End date", | |
| "fieldName": "Start to End date", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10001, | |
| "preserveWhitespace": false, | |
| "title": "Start to End date", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "5 periods ago", | |
| "fieldName": "5 periods ago", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10002, | |
| "preserveWhitespace": false, | |
| "title": "5 periods ago", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "4 periods ago", | |
| "fieldName": "4 periods ago", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10003, | |
| "preserveWhitespace": false, | |
| "title": "4 periods ago", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "3 periods ago", | |
| "fieldName": "3 periods ago", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10004, | |
| "preserveWhitespace": false, | |
| "title": "3 periods ago", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "2 periods ago", | |
| "fieldName": "2 periods ago", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10005, | |
| "preserveWhitespace": false, | |
| "title": "2 periods ago", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "Last period", | |
| "fieldName": "Last period", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10006, | |
| "preserveWhitespace": false, | |
| "title": "Last period", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| }, | |
| { | |
| "alignContent": "center", | |
| "allowHTML": true, | |
| "allowSearch": false, | |
| "booleanValues": [ | |
| "false", | |
| "true" | |
| ], | |
| "dateTimeFormat": "YYYY-MM-DD", | |
| "displayAs": "string", | |
| "displayName": "Current period", | |
| "fieldName": "Current period", | |
| "highlightLinks": false, | |
| "imageHeight": "", | |
| "imageTitleTemplate": "{{ @ }}", | |
| "imageUrlTemplate": "{{ @ }}", | |
| "imageWidth": "", | |
| "linkOpenInNewTab": true, | |
| "linkTextTemplate": "{{ @ }}", | |
| "linkTitleTemplate": "{{ @ }}", | |
| "linkUrlTemplate": "{{ @ }}", | |
| "order": 10007, | |
| "preserveWhitespace": false, | |
| "title": "Current period", | |
| "type": "string", | |
| "useMonospaceFont": false, | |
| "visible": true | |
| } | |
| ] | |
| }, | |
| "frame": { | |
| "showDescription": false, | |
| "showTitle": true, | |
| "title": "Usage (USD) over past periods" | |
| }, | |
| "invisibleColumns": [], | |
| "itemsPerPage": 10, | |
| "paginationSize": "default", | |
| "version": 1, | |
| "widgetType": "table", | |
| "withRowNumber": false | |
| } | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 311, | |
| "width": 6, | |
| "height": 10 | |
| } | |
| }, | |
| { | |
| "widget": { | |
| "name": "mdFooter", | |
| "textbox_spec": "### Information\n#### Workspace Usage Dashboard _(version: 1.1, created: 2025-03-17)_\n\n- All the usages in this dashboard are calculated based on the effective list price\n in USD, and the effective list price means the default list price or promotional\n sku price.\n- Please also check the [data retention policy](https://docs.databricks.com/en/admin/system-tables/index.html#which-system-tables-are-available) of system.billing.usage table.\n- If there is *data loading error*, please ensure:\n 1. The workspace has [Unity Catalog enabled](https://docs.databricks.com/en/data-governance/unity-catalog/enable-workspaces.html).\n 2. You have the **SELECT** permissions of [system.billing.usage](https://docs.databricks.com/en/admin/system-tables/billing.html) and [system.billing.list_prices](https://docs.databricks.com/en/admin/system-tables/pricing.html).\n" | |
| }, | |
| "position": { | |
| "x": 0, | |
| "y": 900, | |
| "width": 6, | |
| "height": 3 | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| } |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Requirements
MY_WORKSPACE_ID_HEREwith the ID of your databricks workspace you want to import the dashboard to.