{ "datasets": [ { "name": "669e9598", "displayName": "select_time_key", "queryLines": [ "select explode(array(\r\n", " 'Day',\r\n", " 'Week',\r\n", " 'Month'\r\n", ")) as time_key\r\n" ] }, { "name": "4fcc0aa1", "displayName": "databricks_usage_total", "queryLines": [ "with\r\n", "usage_with_ws_filtered_by_date as (\r\n", " select\r\n", " workspace_id as workspace,\r\n", " *\r\n", " from system.billing.usage\r\n", " where usage_date between :param_start_date and :param_end_date\r\n", " and usage_metadata.job_id in (select job_id from meta.cluster_configuration) --filter only for DataForge jobs compute\r\n", "),\r\n", "-- calc list priced usage in USD\r\n", "prices as (\r\n", " select coalesce(price_end_time, date_add(current_date, 1)) as coalesced_price_end_time, *\r\n", " from system.billing.list_prices\r\n", " where currency_code = 'USD'\r\n", "),\r\n", "list_priced_usd as (\r\n", " select\r\n", " coalesce(u.usage_quantity * p.pricing.effective_list.default, 0) as usage_usd,\r\n", " u.*\r\n", " from usage_with_ws_filtered_by_date as u\r\n", " left join prices as p\r\n", " on u.sku_name=p.sku_name\r\n", " and u.usage_unit=p.usage_unit\r\n", " and (u.usage_end_time between p.price_start_time and p.coalesced_price_end_time)\r\n", "),\r\n", "-- calc total usage in USD\r\n", "usage_total as (\r\n", " select\r\n", " sum(usage_usd) as usage_usd\r\n", " from list_priced_usd\r\n", ")\r\n", "-- query\r\n", "select\r\n", " concat(\r\n", " 'Databricks usage (USD): $ ',\r\n", " case\r\n", " when usage_usd >= 1e9 then concat(format_number(usage_usd / 1e9, 2), 'B')\r\n", " when usage_usd >= 1e6 then concat(format_number(usage_usd / 1e6, 2), 'M')\r\n", " when usage_usd >= 1e3 then concat(format_number(usage_usd / 1e3, 2), 'K')\r\n", " else format_number(usage_usd, 2)\r\n", " end\r\n", " ) as total_usage_usd\r\n", "from usage_total\r\n" ], "parameters": [ { "displayName": "param_start_date", "keyword": "param_start_date", "dataType": "DATE", "defaultSelection": { "values": { "dataType": "DATE", "values": [ { "value": "2024-10-03T00:00:00.000" } ] } } }, { "displayName": "param_end_date", "keyword": "param_end_date", "dataType": "DATE", "defaultSelection": { "values": { "dataType": "DATE", "values": [ { "value": "2024-10-11T00:00:00.000" } ] } } } ] }, { "name": "aed3d1aa", "displayName": "dataforge_usage_total", "queryLines": [ "with processes as (\r\n", " select\r\n", " date_format(end_datetime, 'MMM yy') as usage_month,\r\n", " count(1) as success_process_count\r\n", " from meta.process\r\n", " where date(end_datetime) between :param_start_date and :param_end_date\r\n", " and status_code = 'P'\r\n", " group by usage_month\r\n", " order by usage_month\r\n", "),\r\n", "processes_with_cost as (\r\n", " select\r\n", " usage_month,\r\n", " success_process_count,\r\n", " (least(success_process_count, 1000) * 2.50 --first tier price\r\n", " + IF(success_process_count > 1000, least(success_process_count, 5000) - 1000, 0) * 0.60 --second tier price\r\n", " + IF(success_process_count > 5000, least(success_process_count, 50000) - 5000, 0) * 0.06 --third tier price\r\n", " + IF(success_process_count > 50000, least(success_process_count, 1000000) - 50000, 0) * 0.02 --fourth tier price\r\n", " + IF(success_process_count > 1000000, success_process_count - 1000000, 0) * 0.01 --fifth tier price\r\n", " ) as dataforge_usage_usd\r\n", " from processes\r\n", "),\r\n", "usage_total as (\r\n", " select\r\n", " sum(success_process_count) as success_process_count,\r\n", " sum(dataforge_usage_usd) as dataforge_usage_usd\r\n", " from processes_with_cost\r\n", ")\r\n", "select\r\n", " concat(\r\n", " 'DataForge processes: ',\r\n", " case\r\n", " when success_process_count >= 1e9 then concat(format_number(success_process_count / 1e9, 2), 'B')\r\n", " when success_process_count >= 1e6 then concat(format_number(success_process_count / 1e6, 2), 'M')\r\n", " when success_process_count >= 1e3 then concat(format_number(success_process_count / 1e3, 1), 'K')\r\n", " else cast(success_process_count as string)\r\n", " end\r\n", " ) as success_process_count,\r\n", " concat(\r\n", " 'DataForge usage (USD): $ ',\r\n", " case\r\n", " when dataforge_usage_usd >= 1e9 then concat(format_number(dataforge_usage_usd / 1e9, 2), 'B')\r\n", " when dataforge_usage_usd >= 1e6 then concat(format_number(dataforge_usage_usd / 1e6, 2), 'M')\r\n", " when dataforge_usage_usd >= 1e3 then concat(format_number(dataforge_usage_usd / 1e3, 2), 'K')\r\n", " else format_number(dataforge_usage_usd, 2)\r\n", " end\r\n", " ) as dataforge_usage_usd\r\n", "from\r\n", " usage_total" ], "parameters": [ { "displayName": "param_start_date", "keyword": "param_start_date", "dataType": "DATE", "defaultSelection": { "values": { "dataType": "DATE", "values": [ { "value": "2024-10-03T00:00:00.000" } ] } } }, { "displayName": "param_end_date", "keyword": "param_end_date", "dataType": "DATE", "defaultSelection": { "values": { "dataType": "DATE", "values": [ { "value": "2024-10-11T00:00:00.000" } ] } } } ] }, { "name": "8bcca2d2", "displayName": "monthly_usage_combined", "queryLines": [ "with\r\n", "--CALCULATING DATABRICKS COST\r\n", "databricks_usage_with_ws_filtered_by_date as (\r\n", " select\r\n", " *\r\n", " from system.billing.usage\r\n", " where usage_date between :param_start_date and :param_end_date\r\n", " and usage_metadata.job_id in (select job_id from meta.cluster_configuration) --filter only for DataForge jobs compute\r\n", "),\r\n", "-- calc list priced usage in USD\r\n", "databricks_prices as (\r\n", " select coalesce(price_end_time, date_add(current_date, 1)) as coalesced_price_end_time, *\r\n", " from system.billing.list_prices\r\n", " where currency_code = 'USD'\r\n", "),\r\n", "databricks_list_priced_usd as (\r\n", " select\r\n", " coalesce(u.usage_quantity * p.pricing.effective_list.default, 0) as usage_usd,\r\n", " date_trunc('MONTH',usage_date) as usage_month,\r\n", " u.usage_metadata.job_id,\r\n", " u.*\r\n", " from databricks_usage_with_ws_filtered_by_date as u\r\n", " left join databricks_prices as p\r\n", " on u.sku_name=p.sku_name\r\n", " and u.usage_unit=p.usage_unit\r\n", " and (u.usage_end_time between p.price_start_time and p.coalesced_price_end_time)\r\n", "),\r\n", "total_databricks_job_cost as (\r\n", "-- query\r\n", "select\r\n", " usage_month, \r\n", " 'Databricks' as provider, \r\n", " sum(usage_usd) as usage_usd\r\n", "from databricks_list_priced_usd\r\n", "group by usage_month\r\n", "),\r\n", "--CALCULATING DATAFORGE COST\r\n", "dataforge_processes as (\r\n", " select\r\n", " date_trunc('MONTH',date(end_datetime)) as usage_month,\r\n", " count(1) as success_process_count\r\n", " from meta.process\r\n", " where date(end_datetime) between :param_start_date and :param_end_date\r\n", " and status_code = 'P'\r\n", " group by usage_month\r\n", " order by usage_month\r\n", "),\r\n", "dataforge_processes_with_cost as (\r\n", " select\r\n", " usage_month,\r\n", " success_process_count,\r\n", " (least(success_process_count, 1000) * 2.50 --first tier price\r\n", " + IF(success_process_count > 1000, least(success_process_count, 5000) - 1000, 0) * 0.60 --second tier price\r\n", " + IF(success_process_count > 5000, least(success_process_count, 50000) - 5000, 0) * 0.06 --third tier price\r\n", " + IF(success_process_count > 50000, least(success_process_count, 1000000) - 50000, 0) * 0.02 --fourth tier price\r\n", " + IF(success_process_count > 1000000, success_process_count - 1000000, 0) * 0.01 --fifth tier price\r\n", " ) as dataforge_usage_usd\r\n", " from dataforge_processes\r\n", "),\r\n", "total_dataforge_cost as (\r\n", "select\r\n", " usage_month, \r\n", " 'DataForge' as provider,\r\n", " success_process_count, \r\n", " dataforge_usage_usd as usage_usd\r\n", "from dataforge_processes_with_cost\r\n", ")\r\n", "select usage_month, provider, null as success_process_count, usage_usd from total_databricks_job_cost\r\n", "union all\r\n", "select usage_month, provider, success_process_count, usage_usd from total_dataforge_cost\r\n", "order by usage_month asc" ], "parameters": [ { "displayName": "param_start_date", "keyword": "param_start_date", "dataType": "DATE", "defaultSelection": { "values": { "dataType": "DATE", "values": [ { "value": "now/M" } ] } } }, { "displayName": "param_end_date", "keyword": "param_end_date", "dataType": "DATE", "defaultSelection": { "values": { "dataType": "DATE", "values": [ { "value": "2024-10-01T00:00:00.000" } ] } } } ] }, { "name": "990cacac", "displayName": "select_dataforge_group_key", "queryLines": [ "select explode(array(\r\n", " 'operation_type',\r\n", " 'cluster_configuration_id',\r\n", " 'cluster_configuration_name',\r\n", " 'source_id',\r\n", " 'source_name'\r\n", ")) as group_key\r\n" ] }, { "name": "152e6f1d", "displayName": "select_metric_key", "queryLines": [ "select explode(array(\r\n", " 'process_duration_hours',\r\n", " -- 'process_count',\r\n", " 'success_process_count',\r\n", " 'dataforge_cost_usd',\r\n", " 'databricks_cost_usd',\r\n", " 'total_cost_usd'\r\n", ")) as metric_key" ] }, { "name": "d50f5737", "displayName": "monthly_processes_overtime_by_group_key", "queryLines": [ "with \r\n", "--Stage 1 - Calculating the cost per process for DataForge\r\n", "successful_processes as (\r\n", " select\r\n", " date_trunc('MONTH', end_datetime) as usage_month,\r\n", " sum(case when status_code = 'P' then 1 else 0 end) as success_process_count\r\n", " from meta.process\r\n", " where end_datetime between :param_start_date and :param_end_date\r\n", " group by usage_month\r\n", " order by usage_month\r\n", "),\r\n", "--Stage 2 - Calculating the cost per process for DataForge\r\n", "processes_with_cost as (\r\n", " select\r\n", " usage_month,\r\n", " success_process_count,\r\n", " (least(success_process_count, 1000) * 2.50 --first tier price\r\n", " + IF(success_process_count > 1000, least(success_process_count, 5000) - 1000, 0) * 0.60 --second tier price\r\n", " + IF(success_process_count > 5000, least(success_process_count, 50000) - 5000, 0) * 0.06 --third tier price\r\n", " + IF(success_process_count > 50000, least(success_process_count, 1000000) - 50000, 0) * 0.02 --fourth tier price\r\n", " + IF(success_process_count > 1000000, success_process_count - 1000000, 0) * 0.01 --fifth tier price\r\n", " ) as dataforge_usage_usd\r\n", " from successful_processes\r\n", "),\r\n", "--Final - Calculating the cost per process for DataForge\r\n", "single_process_cost as (\r\n", " select\r\n", " usage_month,\r\n", " dataforge_usage_usd / success_process_count as cost_per_process\r\n", " from processes_with_cost\r\n", "),\r\n", "-- Stage 1 - Calculating the cost per job per month for Databricks\r\n", "databricks_usage_with_ws_filtered_by_date as (\r\n", " select\r\n", " workspace_id as workspace,\r\n", " *\r\n", " from system.billing.usage\r\n", " where usage_date between :param_start_date and :param_end_date\r\n", " and usage_metadata.job_id in (select job_id from meta.cluster_configuration) --filter only for DataForge jobs compute\r\n", "),\r\n", "-- Stage 2 - calc list priced usage in USD\r\n", "databricks_prices as (\r\n", " select coalesce(price_end_time, date_add(current_date, 1)) as coalesced_price_end_time, *\r\n", " from system.billing.list_prices\r\n", " where currency_code = 'USD'\r\n", "),\r\n", "-- Stage 3 - Calculating the cost per job per month for Databricks\r\n", "list_priced_usd as (\r\n", " select\r\n", " coalesce(u.usage_quantity * p.pricing.effective_list.default, 0) as usage_usd,\r\n", " u.usage_metadata.job_id,\r\n", " date_trunc('MONTH', usage_date) as usage_month,\r\n", " u.*\r\n", " from databricks_usage_with_ws_filtered_by_date as u\r\n", " left join databricks_prices as p\r\n", " on u.sku_name=p.sku_name\r\n", " and u.usage_unit=p.usage_unit\r\n", " and (u.usage_end_time between p.price_start_time and p.coalesced_price_end_time)\r\n", "),\r\n", "--Final - Calculating the cost per job per month for Databricks\r\n", "databricks_job_cost_by_month as (\r\n", " select \r\n", " usage_month,\r\n", " job_id,\r\n", " sum(usage_usd) as usage_usd\r\n", " from list_priced_usd\r\n", " group by usage_month, job_id\r\n", "),\r\n", "--Getting DataForge processes and applying per process cost\r\n", "processes as (\r\n", " select \r\n", " cc.name as cluster_configuration_name,\r\n", " cc.job_id,\r\n", " 1 as process_count,\r\n", " CASE WHEN p.status_code = 'P' THEN 1 ELSE 0 END as success_process_count,\r\n", " CASE WHEN p.status_code = 'P' THEN 1 * pc.cost_per_process END as dataforge_cost_usd,\r\n", " date_trunc('DAY', end_datetime) as usage_date,\r\n", " date_trunc('MONTH', end_datetime) as usage_month,\r\n", " (unix_timestamp(p.end_datetime) - unix_timestamp(p.start_datetime))/3600 as process_duration_hours,\r\n", " p.*\r\n", " from meta.process p\r\n", " left join single_process_cost pc on date_trunc('MONTH', p.end_datetime) = pc.usage_month\r\n", " left join meta.cluster_configuration cc on p.cluster_configuration_id = cc.cluster_configuration_id\r\n", " where date(p.end_datetime) between :param_start_date and :param_end_date\r\n", "),\r\n", "--Getting all job ids and total process duration of jobs by month for allocation\r\n", "job_id_processing as (\r\n", " select\r\n", " usage_month,\r\n", " job_id,\r\n", " sum(process_duration_hours) as job_duration_hours\r\n", " from processes\r\n", " group by usage_month, job_id\r\n", "),\r\n", "--Getting processes and calculating % of total job process duration for the month\r\n", "processes_with_job_duration_percentage as (\r\n", " select\r\n", " p.*,\r\n", " job_duration_hours,\r\n", " try_divide(process_duration_hours, job_duration_hours) as job_duration_percent_of_total \r\n", " from processes p\r\n", " left join job_id_processing j on p.usage_month = j.usage_month and p.job_id = j.job_id\r\n", "),\r\n", "--Calculating Databricks cost per process and total cost for DataForge + Databricks\r\n", "processes_with_job_costs as (\r\n", " select\r\n", " p.*,\r\n", " djc.usage_usd as databricks_job_cost_usd,\r\n", " p.job_duration_percent_of_total * djc.usage_usd as databricks_cost_usd,\r\n", " p.dataforge_cost_usd + databricks_cost_usd as total_cost_usd\r\n", " from processes_with_job_duration_percentage p\r\n", " left join databricks_job_cost_by_month djc on p.usage_month = djc.usage_month and p.job_id = djc.job_id\r\n", "),\r\n", "--Group process level data by group metric and get only metric key value\r\n", "processes_by_group_and_metric_key as (\r\n", "select \r\n", " identifier(:param_group_key) as group_key,\r\n", " case \r\n", " when :param_time_key = 'Month' THEN date_trunc('MONTH', usage_date)\r\n", " when :param_time_key = 'Week' THEN date_trunc('WEEK', usage_date)\r\n", " when :param_time_key = 'Day' THEN usage_date\r\n", " end as time_key,\r\n", " sum(success_process_count) as success_process_count, --Only for verifying cost calculations\r\n", " sum(identifier(:param_metric_key)) as metric\r\n", "from processes_with_job_costs\r\n", "group by group_key, time_key\r\n", "order by group_key, time_key \r\n", "),\r\n", "-- Set datetimes for time periods that will be calculated based on time key\r\n", "periods_raw as (\r\n", " select\r\n", " case\r\n", " when :param_time_key = 'Day' then current_date()\r\n", " when :param_time_key = 'Week' then date_trunc('WEEK', current_date())\r\n", " when :param_time_key = 'Month' then date_trunc('MONTH', current_date())\r\n", " end as current_period,\r\n", " case\r\n", " when :param_time_key = 'Day' then current_date() - interval 1 day\r\n", " when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 7 day)\r\n", " when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 1 month)\r\n", " end as last_period,\r\n", " case\r\n", " when :param_time_key = 'Day' then current_date() - interval 2 day\r\n", " when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 14 day)\r\n", " when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 2 month)\r\n", " end as two_periods_ago,\r\n", " case\r\n", " when :param_time_key = 'Day' then current_date() - interval 3 day\r\n", " when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 21 day)\r\n", " when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 3 month)\r\n", " end as three_periods_ago,\r\n", " case\r\n", " when :param_time_key = 'Day' then current_date() - interval 4 day\r\n", " when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 28 day)\r\n", " when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 4 month)\r\n", " end as four_periods_ago,\r\n", " case\r\n", " when :param_time_key = 'Day' then current_date() - interval 5 day\r\n", " when :param_time_key = 'Week' then date_trunc('WEEK', current_date() - interval 35 day)\r\n", " when :param_time_key = 'Month' then date_trunc('MONTH', current_date() - interval 5 month)\r\n", " end as five_periods_ago\r\n", "),\r\n", "periods_info as (\r\n", "select period, date\r\n", "from periods_raw\r\n", "unpivot (date for period in (current_period, last_period, two_periods_ago, three_periods_ago, four_periods_ago, five_periods_ago))\r\n", "),\r\n", "-- Attach group key with time periods to be calculated\r\n", "periods_info_with_groups as (\r\n", " select\r\n", " distinct group_key,\r\n", " period,\r\n", " date\r\n", " from\r\n", " processes_by_group_and_metric_key, periods_info\r\n", " order by group_key, period\r\n", "),\r\n", "usage_by_period as (\r\n", "select \r\n", " pig.group_key, \r\n", " pig.period, \r\n", " pig.date, \r\n", " pgm.success_process_count, \r\n", " coalesce(pgm.metric,0) as metric\r\n", "from periods_info_with_groups pig\r\n", "left join processes_by_group_and_metric_key pgm on pig.group_key = pgm.group_key and pig.date = pgm.time_key \r\n", "order by group_key, date asc\r\n", "),\r\n", "usage_change as (\r\n", "select\r\n", " group_key,\r\n", " period,\r\n", " date,\r\n", " success_process_count,\r\n", " metric,\r\n", " lag(metric, 1) over (partition by group_key order by date) as prev_metric,\r\n", " int(round(try_divide((metric - prev_metric), prev_metric) * 100,0)) as change_percentage\r\n", "from usage_by_period\r\n", "order by group_key, date\r\n", "),\r\n", "all_time_usage as (\r\n", " select\r\n", " group_key,\r\n", " 'start_to_end' as period,\r\n", " null as date,\r\n", " sum(success_process_count) as success_process_count,\r\n", " sum(metric) as metric,\r\n", " null as prev_metric,\r\n", " null as change_percentage\r\n", " from processes_by_group_and_metric_key\r\n", " group by group_key\r\n", "),\r\n", "-- Join period usage with start to end usage\r\n", "joined_usage as (\r\n", " select * from usage_change\r\n", " union all\r\n", " select * from all_time_usage\r\n", "),\r\n", "-- Format metric to be displayed in html\r\n", "joined_usage_formatted as (\r\n", " select\r\n", " group_key,\r\n", " usage_sort,\r\n", " period,\r\n", " case\r\n", " when period = 'start_to_end' then metric_str\r\n", " else concat('', metric_str, ' ', change_percentage_str, '') \r\n", " end as usage_info\r\n", " from\r\n", " (\r\n", " select\r\n", " group_key,\r\n", " period,\r\n", " case when period = 'start_to_end' then metric else 0 end as usage_sort,\r\n", " case\r\n", " when :param_metric_key like '%count%' then format_number(metric, 0)\r\n", " when :param_metric_key like '%cost%' and metric <> 0 then concat('$ ', format_number(metric, 2))\r\n", " when :param_metric_key like '%cost%' and metric = 0 then '$ 0'\r\n", " when :param_metric_key like '%duration%' and metric <> 0 then format_number(metric, 2)\r\n", " else format_number(metric, 0)\r\n", " end as metric_str,\r\n", " case \r\n", " when change_percentage is null or change_percentage = 0 then '(0%)'\r\n", " else concat('(',if(change_percentage>0, '+',''), change_percentage, '%',')')\r\n", " end as change_percentage_str,\r\n", " case\r\n", " when change_percentage > 10 then '#00A972'\r\n", " when change_percentage < -10 then '#FF3621'\r\n", " else '#919191'\r\n", " end as change_color\r\n", " from joined_usage\r\n", " )\r\n", ")\r\n", "-- final query - unioning top row of dates with data\r\n", "select\r\n", " group_key as `Group Key`,\r\n", " start_to_end_usage_sort as usage_sort,\r\n", " 2 as _order,\r\n", " start_to_end_usage_info as `Start to End`,\r\n", " coalesce(five_periods_ago_usage_info, '0') as `5 periods ago`,\r\n", " coalesce(four_periods_ago_usage_info, '0') as `4 periods ago`,\r\n", " coalesce(three_periods_ago_usage_info, '0') as `3 periods ago`,\r\n", " coalesce(two_periods_ago_usage_info, '0') as `2 periods ago`,\r\n", " coalesce(last_period_usage_info, '0') as `Last period`,\r\n", " coalesce(current_period_usage_info, '0') as `Current period`\r\n", "from joined_usage_formatted\r\n", "pivot (\r\n", " first(usage_info) as usage_info, sum(usage_sort) as usage_sort\r\n", " for period in (\r\n", " 'start_to_end',\r\n", " 'five_periods_ago',\r\n", " 'four_periods_ago',\r\n", " 'three_periods_ago',\r\n", " 'two_periods_ago',\r\n", " 'last_period',\r\n", " 'current_period'\r\n", " )\r\n", ")\r\n", "union all\r\n", "(\r\n", " select\r\n", " '' as `Group Key`,\r\n", " null as usage_sort,\r\n", " 1 as _order,\r\n", " concat('', date_format(:param_start_date, 'MMM dd yyyy'), ' - ', date_format(:param_end_date, 'MMM dd yyyy'), '') as `Start to End date`,\r\n", " concat('', date_format(five_periods_ago, 'MMM dd'), ' - ', date_format(four_periods_ago, 'MMM dd'), '') as `5 periods ago`,\r\n", " concat('', date_format(four_periods_ago, 'MMM dd'), ' - ', date_format(three_periods_ago, 'MMM dd'), '') as `4 periods ago`,\r\n", " concat('', date_format(three_periods_ago, 'MMM dd'), ' - ', date_format(two_periods_ago, 'MMM dd'), '') as `3 periods ago`,\r\n", " concat('', date_format(two_periods_ago, 'MMM dd'), ' - ', date_format(last_period, 'MMM dd'), '') as `2 periods ago`,\r\n", " concat('', date_format(last_period, 'MMM dd'), ' - ', date_format(current_period, 'MMM dd'), '') as `Last period`,\r\n", " concat('', date_format(current_period, 'MMM dd'), ' - Now', '') as `Current period`\r\n", " from periods_raw\r\n", ")\r\n", "order by _order asc, usage_sort desc" ], "parameters": [ { "displayName": "param_start_date", "keyword": "param_start_date", "dataType": "DATE", "defaultSelection": { "values": { "dataType": "DATE", "values": [ { "value": "2024-01-01T00:00:00.000" } ] } } }, { "displayName": "param_end_date", "keyword": "param_end_date", "dataType": "DATE", "defaultSelection": { "values": { "dataType": "DATE", "values": [ { "value": "now/d" } ] } } }, { "displayName": "param_group_key", "keyword": "param_group_key", "dataType": "STRING", "defaultSelection": { "values": { "dataType": "STRING", "values": [ { "value": "cluster_configuration_name" } ] } } }, { "displayName": "param_metric_key", "keyword": "param_metric_key", "dataType": "STRING", "defaultSelection": { "values": { "dataType": "STRING", "values": [ { "value": "process_duration_hours" } ] } } }, { "displayName": "param_time_key", "keyword": "param_time_key", "dataType": "STRING", "defaultSelection": { "values": { "dataType": "STRING", "values": [ { "value": "Month" } ] } } } ] } ], "pages": [ { "name": "230e9df7", "displayName": "DataForge Usage", "layout": [ { "widget": { "name": "147cbe12", "queries": [ { "name": "main_query", "query": { "datasetName": "d50f5737", "fields": [ { "name": "Group Key", "expression": "`Group Key`" }, { "name": "Start to End", "expression": "`Start to End`" }, { "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": true } } ], "spec": { "version": 1, "widgetType": "table", "encodings": { "columns": [ { "fieldName": "Group Key", "booleanValues": [ "false", "true" ], "imageUrlTemplate": "{{ @ }}", "imageTitleTemplate": "{{ @ }}", "imageWidth": "", "imageHeight": "", "linkUrlTemplate": "{{ @ }}", "linkTextTemplate": "{{ @ }}", "linkTitleTemplate": "{{ @ }}", "linkOpenInNewTab": true, "type": "string", "displayAs": "string", "visible": true, "order": 100000, "title": "Group Key", "allowSearch": false, "alignContent": "left", "allowHTML": false, "highlightLinks": false, "useMonospaceFont": false, "preserveWhitespace": false, "displayName": "Group Key" }, { "fieldName": "Start to End", "booleanValues": [ "false", "true" ], "imageUrlTemplate": "{{ @ }}", "imageTitleTemplate": "{{ @ }}", "imageWidth": "", "imageHeight": "", "linkUrlTemplate": "{{ @ }}", "linkTextTemplate": "{{ @ }}", "linkTitleTemplate": "{{ @ }}", "linkOpenInNewTab": true, "type": "string", "displayAs": "string", "visible": true, "order": 100003, "title": "Start to End", "allowSearch": false, "alignContent": "left", "allowHTML": true, "highlightLinks": false, "useMonospaceFont": false, "preserveWhitespace": false, "displayName": "Start to End" }, { "fieldName": "5 periods ago", "booleanValues": [ "false", "true" ], "imageUrlTemplate": "{{ @ }}", "imageTitleTemplate": "{{ @ }}", "imageWidth": "", "imageHeight": "", "linkUrlTemplate": "{{ @ }}", "linkTextTemplate": "{{ @ }}", "linkTitleTemplate": "{{ @ }}", "linkOpenInNewTab": true, "type": "string", "displayAs": "string", "visible": true, "order": 100004, "title": "5 periods ago", "allowSearch": false, "alignContent": "left", "allowHTML": true, "highlightLinks": false, "useMonospaceFont": false, "preserveWhitespace": false, "displayName": "5 periods ago" }, { "fieldName": "4 periods ago", "booleanValues": [ "false", "true" ], "imageUrlTemplate": "{{ @ }}", "imageTitleTemplate": "{{ @ }}", "imageWidth": "", "imageHeight": "", "linkUrlTemplate": "{{ @ }}", "linkTextTemplate": "{{ @ }}", "linkTitleTemplate": "{{ @ }}", "linkOpenInNewTab": true, "type": "string", "displayAs": "string", "visible": true, "order": 100005, "title": "4 periods ago", "allowSearch": false, "alignContent": "left", "allowHTML": true, "highlightLinks": false, "useMonospaceFont": false, "preserveWhitespace": false, "displayName": "4 periods ago" }, { "fieldName": "3 periods ago", "booleanValues": [ "false", "true" ], "imageUrlTemplate": "{{ @ }}", "imageTitleTemplate": "{{ @ }}", "imageWidth": "", "imageHeight": "", "linkUrlTemplate": "{{ @ }}", "linkTextTemplate": "{{ @ }}", "linkTitleTemplate": "{{ @ }}", "linkOpenInNewTab": true, "type": "string", "displayAs": "string", "visible": true, "order": 100006, "title": "3 periods ago", "allowSearch": false, "alignContent": "left", "allowHTML": true, "highlightLinks": false, "useMonospaceFont": false, "preserveWhitespace": false, "displayName": "3 periods ago" }, { "fieldName": "2 periods ago", "booleanValues": [ "false", "true" ], "imageUrlTemplate": "{{ @ }}", "imageTitleTemplate": "{{ @ }}", "imageWidth": "", "imageHeight": "", "linkUrlTemplate": "{{ @ }}", "linkTextTemplate": "{{ @ }}", "linkTitleTemplate": "{{ @ }}", "linkOpenInNewTab": true, "type": "string", "displayAs": "string", "visible": true, "order": 100007, "title": "2 periods ago", "allowSearch": false, "alignContent": "left", "allowHTML": true, "highlightLinks": false, "useMonospaceFont": false, "preserveWhitespace": false, "displayName": "2 periods ago" }, { "fieldName": "Last period", "booleanValues": [ "false", "true" ], "imageUrlTemplate": "{{ @ }}", "imageTitleTemplate": "{{ @ }}", "imageWidth": "", "imageHeight": "", "linkUrlTemplate": "{{ @ }}", "linkTextTemplate": "{{ @ }}", "linkTitleTemplate": "{{ @ }}", "linkOpenInNewTab": true, "type": "string", "displayAs": "string", "visible": true, "order": 100008, "title": "Last period", "allowSearch": false, "alignContent": "left", "allowHTML": true, "highlightLinks": false, "useMonospaceFont": false, "preserveWhitespace": false, "displayName": "Last period" }, { "fieldName": "Current period", "booleanValues": [ "false", "true" ], "imageUrlTemplate": "{{ @ }}", "imageTitleTemplate": "{{ @ }}", "imageWidth": "", "imageHeight": "", "linkUrlTemplate": "{{ @ }}", "linkTextTemplate": "{{ @ }}", "linkTitleTemplate": "{{ @ }}", "linkOpenInNewTab": true, "type": "string", "displayAs": "string", "visible": true, "order": 100009, "title": "Current period", "allowSearch": false, "alignContent": "left", "allowHTML": true, "highlightLinks": false, "useMonospaceFont": false, "preserveWhitespace": false, "displayName": "Current period" } ] }, "invisibleColumns": [ { "numberFormat": "0.00", "booleanValues": [ "false", "true" ], "imageUrlTemplate": "{{ @ }}", "imageTitleTemplate": "{{ @ }}", "imageWidth": "", "imageHeight": "", "linkUrlTemplate": "{{ @ }}", "linkTextTemplate": "{{ @ }}", "linkTitleTemplate": "{{ @ }}", "linkOpenInNewTab": true, "name": "usage_sort", "type": "float", "displayAs": "number", "order": 100001, "title": "usage_sort", "allowSearch": false, "alignContent": "right", "allowHTML": false, "highlightLinks": false, "useMonospaceFont": false, "preserveWhitespace": false }, { "numberFormat": "0", "booleanValues": [ "false", "true" ], "imageUrlTemplate": "{{ @ }}", "imageTitleTemplate": "{{ @ }}", "imageWidth": "", "imageHeight": "", "linkUrlTemplate": "{{ @ }}", "linkTextTemplate": "{{ @ }}", "linkTitleTemplate": "{{ @ }}", "linkOpenInNewTab": true, "name": "_order", "type": "integer", "displayAs": "number", "order": 100002, "title": "_order", "allowSearch": false, "alignContent": "right", "allowHTML": false, "highlightLinks": false, "useMonospaceFont": false, "preserveWhitespace": false } ], "allowHTMLByDefault": false, "itemsPerPage": 25, "paginationSize": "default", "condensed": false, "withRowNumber": false, "frame": { "showTitle": true, "title": "Usage over past periods" } } }, "position": { "x": 0, "y": 16, "width": 6, "height": 9 } }, { "widget": { "name": "2c9a5704", "queries": [ { "name": "main_query", "query": { "datasetName": "aed3d1aa", "fields": [ { "name": "success_process_count", "expression": "`success_process_count`" } ], "disaggregated": true } } ], "spec": { "version": 2, "widgetType": "counter", "encodings": { "value": { "fieldName": "success_process_count", "displayName": "success_process_count" } } } }, "position": { "x": 4, "y": 1, "width": 2, "height": 1 } }, { "widget": { "name": "6591cfba", "multilineTextboxSpec": { "lines": [ "Null group keys represent deleted configurations from DataForge or process operations not tied to a specific group key. When Group by is cluster_configuration, null group keys also represent Agent-based ingestions that do not use clusters." ] } }, "position": { "x": 0, "y": 15, "width": 5, "height": 1 } }, { "widget": { "name": "7e4d74fc", "queries": [ { "name": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef874b7edf1f71b9bdb5818941271a_param_start_date", "query": { "datasetName": "4fcc0aa1", "parameters": [ { "name": "param_start_date", "keyword": "param_start_date" } ], "disaggregated": false } }, { "name": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef87f7ed3212fa95af9277d7a1d6d0_param_start_date", "query": { "datasetName": "aed3d1aa", "parameters": [ { "name": "param_start_date", "keyword": "param_start_date" } ], "disaggregated": false } }, { "name": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef87fa73e5150698601d3661a62d18_param_start_date", "query": { "datasetName": "8bcca2d2", "parameters": [ { "name": "param_start_date", "keyword": "param_start_date" } ], "disaggregated": false } }, { "name": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef8812e25b1295899a8c78bf2141c8_param_start_date", "query": { "datasetName": "d50f5737", "parameters": [ { "name": "param_start_date", "keyword": "param_start_date" } ], "disaggregated": false } } ], "spec": { "version": 2, "widgetType": "filter-date-picker", "encodings": { "fields": [ { "parameterName": "param_start_date", "queryName": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef874b7edf1f71b9bdb5818941271a_param_start_date" }, { "parameterName": "param_start_date", "queryName": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef87f7ed3212fa95af9277d7a1d6d0_param_start_date" }, { "parameterName": "param_start_date", "queryName": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef87fa73e5150698601d3661a62d18_param_start_date" }, { "parameterName": "param_start_date", "queryName": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef8812e25b1295899a8c78bf2141c8_param_start_date" } ] }, "selection": { "defaultSelection": { "values": { "dataType": "DATE", "values": [ { "value": "now/y" } ] } } }, "frame": { "showTitle": true, "title": "Start Date" } } }, "position": { "x": 0, "y": 0, "width": 3, "height": 1 } }, { "widget": { "name": "8318ed48", "queries": [ { "name": "main_query", "query": { "datasetName": "8bcca2d2", "fields": [ { "name": "sum(usage_usd)", "expression": "SUM(`usage_usd`)" }, { "name": "monthly(usage_month)", "expression": "DATE_TRUNC(\"MONTH\", `usage_month`)" }, { "name": "provider", "expression": "`provider`" } ], "cubeGroupingSets": { "sets": [ { "fieldNames": [ "provider" ] }, { "fieldNames": [ "monthly(usage_month)" ] } ] }, "disaggregated": false, "orders": [ { "direction": "ASC", "expression": "`provider`" }, { "direction": "ASC", "expression": "DATE_TRUNC(\"MONTH\", `usage_month`)" } ] } } ], "spec": { "version": 3, "widgetType": "pivot", "encodings": { "rows": [ { "fieldName": "provider", "displayName": "Provider" } ], "columns": [ { "fieldName": "monthly(usage_month)", "displayName": "Month" } ], "cell": { "type": "multi-cell", "fields": [ { "fieldName": "sum(usage_usd)", "cellType": "text", "format": { "type": "number-currency", "currencyCode": "USD", "abbreviation": "none", "decimalPlaces": { "type": "exact", "places": 2 }, "hideGroupSeparator": false }, "displayName": "usage (USD)" } ] } } } }, "position": { "x": 0, "y": 9, "width": 6, "height": 5 } }, { "widget": { "name": "87e69934", "queries": [ { "name": "main_query", "query": { "datasetName": "aed3d1aa", "fields": [ { "name": "dataforge_usage_usd", "expression": "`dataforge_usage_usd`" } ], "disaggregated": true } } ], "spec": { "version": 2, "widgetType": "counter", "encodings": { "value": { "fieldName": "dataforge_usage_usd", "displayName": "dataforge_usage_usd" } }, "frame": { "contentAlignment": "center" } } }, "position": { "x": 0, "y": 1, "width": 2, "height": 1 } }, { "widget": { "name": "88c73b6a", "queries": [ { "name": "main_query", "query": { "datasetName": "4fcc0aa1", "fields": [ { "name": "total_usage_usd", "expression": "`total_usage_usd`" } ], "disaggregated": true } } ], "spec": { "version": 2, "widgetType": "counter", "encodings": { "value": { "fieldName": "total_usage_usd", "displayName": "total_usage_usd" } }, "frame": { "contentAlignment": "center" } } }, "position": { "x": 2, "y": 1, "width": 2, "height": 1 } }, { "widget": { "name": "8df5a0dc", "queries": [ { "name": "dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef8809ece91ef9846ab984f06083d8_group_key", "query": { "datasetName": "990cacac", "fields": [ { "name": "group_key", "expression": "`group_key`" }, { "name": "group_key_associativity", "expression": "COUNT_IF(`associative_filter_predicate_group`)" } ], "disaggregated": false } }, { "name": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef8812e25b1295899a8c78bf2141c8_param_group_key", "query": { "datasetName": "d50f5737", "parameters": [ { "name": "param_group_key", "keyword": "param_group_key" } ], "disaggregated": false } } ], "spec": { "version": 2, "widgetType": "filter-single-select", "encodings": { "fields": [ { "fieldName": "group_key", "displayName": "group_key", "queryName": "dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef8809ece91ef9846ab984f06083d8_group_key" }, { "parameterName": "param_group_key", "queryName": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef8812e25b1295899a8c78bf2141c8_param_group_key" } ] }, "selection": { "defaultSelection": { "values": { "dataType": "STRING", "values": [ { "value": "source_name" } ] } } }, "frame": { "showTitle": true, "title": "Group by" } } }, "position": { "x": 0, "y": 14, "width": 2, "height": 1 } }, { "widget": { "name": "a8fcd9e8", "queries": [ { "name": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef874b7edf1f71b9bdb5818941271a_param_end_date", "query": { "datasetName": "4fcc0aa1", "parameters": [ { "name": "param_end_date", "keyword": "param_end_date" } ], "disaggregated": false } }, { "name": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef87f7ed3212fa95af9277d7a1d6d0_param_end_date", "query": { "datasetName": "aed3d1aa", "parameters": [ { "name": "param_end_date", "keyword": "param_end_date" } ], "disaggregated": false } }, { "name": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef87fa73e5150698601d3661a62d18_param_end_date", "query": { "datasetName": "8bcca2d2", "parameters": [ { "name": "param_end_date", "keyword": "param_end_date" } ], "disaggregated": false } }, { "name": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef8812e25b1295899a8c78bf2141c8_param_end_date", "query": { "datasetName": "d50f5737", "parameters": [ { "name": "param_end_date", "keyword": "param_end_date" } ], "disaggregated": false } } ], "spec": { "version": 2, "widgetType": "filter-date-picker", "encodings": { "fields": [ { "parameterName": "param_end_date", "queryName": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef874b7edf1f71b9bdb5818941271a_param_end_date" }, { "parameterName": "param_end_date", "queryName": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef87f7ed3212fa95af9277d7a1d6d0_param_end_date" }, { "parameterName": "param_end_date", "queryName": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef87fa73e5150698601d3661a62d18_param_end_date" }, { "parameterName": "param_end_date", "queryName": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef8812e25b1295899a8c78bf2141c8_param_end_date" } ] }, "selection": { "defaultSelection": { "values": { "dataType": "DATE", "values": [ { "value": "now/d" } ] } } }, "frame": { "showTitle": true, "title": "End Date" } } }, "position": { "x": 3, "y": 0, "width": 3, "height": 1 } }, { "widget": { "name": "cc5cddcf", "queries": [ { "name": "main_query", "query": { "datasetName": "8bcca2d2", "fields": [ { "name": "usage_month", "expression": "`usage_month`" }, { "name": "sum(usage_usd)", "expression": "SUM(`usage_usd`)" }, { "name": "sum(success_process_count)", "expression": "SUM(`success_process_count`)" } ], "disaggregated": false } } ], "spec": { "version": 1, "widgetType": "combo", "encodings": { "x": { "fieldName": "usage_month", "axis": { "hideTitle": true, "hideLabels": false }, "scale": { "type": "categorical", "sort": { "by": "natural-order" } }, "displayName": "usage_month" }, "color": { "legend": { "position": "right" } }, "y": { "primary": { "fields": [ { "fieldName": "sum(usage_usd)", "displayName": "usage (USD)" } ], "scale": { "type": "quantitative" }, "format": { "type": "number-currency", "currencyCode": "USD", "abbreviation": "compact", "decimalPlaces": { "type": "max", "places": 2 } } }, "secondary": { "fields": [ { "fieldName": "sum(success_process_count)", "displayName": "Successful processes" } ], "scale": { "type": "quantitative", "reverse": false } } } }, "frame": { "showTitle": false }, "mark": { "layout": "group", "colors": [ "#151515", "#FE483C", "#00A972", "#FF3621", "#8BCAE7", "#AB4057", "#99DDB4", "#FCA4A1", "#919191", "#BF7080" ] } }, "specExtensions": { "widgetBackgroundColor": { "dark": "#3A3A3A" } } }, "position": { "x": 0, "y": 2, "width": 6, "height": 7 } }, { "widget": { "name": "da0510b6", "queries": [ { "name": "dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef874ac43410a49b1cbbc13859b9b7_time_key", "query": { "datasetName": "669e9598", "fields": [ { "name": "time_key", "expression": "`time_key`" }, { "name": "time_key_associativity", "expression": "COUNT_IF(`associative_filter_predicate_group`)" } ], "disaggregated": false } }, { "name": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef8812e25b1295899a8c78bf2141c8_param_time_key", "query": { "datasetName": "d50f5737", "parameters": [ { "name": "param_time_key", "keyword": "param_time_key" } ], "disaggregated": false } } ], "spec": { "version": 2, "widgetType": "filter-single-select", "encodings": { "fields": [ { "fieldName": "time_key", "displayName": "time_key", "queryName": "dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef874ac43410a49b1cbbc13859b9b7_time_key" }, { "parameterName": "param_time_key", "queryName": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef8812e25b1295899a8c78bf2141c8_param_time_key" } ] }, "selection": { "defaultSelection": { "values": { "dataType": "STRING", "values": [ { "value": "Week" } ] } } }, "frame": { "showTitle": true, "title": "View by" } } }, "position": { "x": 4, "y": 14, "width": 2, "height": 1 } }, { "widget": { "name": "f1efd603", "queries": [ { "name": "dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef880cd9ce1570b000f71e7d0f057f_metric_key", "query": { "datasetName": "152e6f1d", "fields": [ { "name": "metric_key", "expression": "`metric_key`" }, { "name": "metric_key_associativity", "expression": "COUNT_IF(`associative_filter_predicate_group`)" } ], "disaggregated": false } }, { "name": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef8812e25b1295899a8c78bf2141c8_param_metric_key", "query": { "datasetName": "d50f5737", "parameters": [ { "name": "param_metric_key", "keyword": "param_metric_key" } ], "disaggregated": false } } ], "spec": { "version": 2, "widgetType": "filter-single-select", "encodings": { "fields": [ { "fieldName": "metric_key", "displayName": "metric_key", "queryName": "dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef880cd9ce1570b000f71e7d0f057f_metric_key" }, { "parameterName": "param_metric_key", "queryName": "parameter_dashboards/01ef873bf79f1cc09e7df2838a07c546/datasets/01ef8812e25b1295899a8c78bf2141c8_param_metric_key" } ] }, "selection": { "defaultSelection": { "values": { "dataType": "STRING", "values": [ { "value": "total_cost_usd" } ] } } }, "frame": { "showTitle": true, "title": "Metric" } } }, "position": { "x": 2, "y": 14, "width": 2, "height": 1 } } ], "pageType": "PAGE_TYPE_CANVAS" } ], "uiSettings": { "theme": { "widgetHeaderAlignment": "ALIGNMENT_UNSPECIFIED" }, "genieSpace": { "isEnabled": false } } }