![\"\"](\"https:\/\/lh7-qw.googleusercontent.com\/docsz\/AD_4nXeIAEfDdFfYhyKFT6LjFLjHk-zddrnov0AnxFc0OnFyLph1rBn7zsxP9vI_CtwPEgt6EKLMx5chPQl-RwJ4SxvKlqc5vcGNPdqzizMFB97cLIdSBU90uURpXbdkTWxS_K-1439qz6DxH93raFtUjBrDFsA4?key=EnulmzUh85Dz0KFPiY6OSg\")
![\"\"\/](\"https:\/\/lh7-qw.googleusercontent.com\/docsz\/AD_4nXccjvOi8AUz2JOu5LZShuQvs4JsQt0bRVNqLENZwpM7InX6zMSWE4d01L46X79LW8za--a9LMIEeZjISOQq2YOK-Pu02myltmExmqgrnQUZfQekd8PhaYjONk4hngYaa3tyDaJhBeU6jqhOMpJ1gDAkro1W?key=EnulmzUh85Dz0KFPiY6OSg\")
![\"\"\/](\"https:\/\/lh7-qw.googleusercontent.com\/docsz\/AD_4nXdPkLWdq2L-1KNOYCmafLNXS3N3q9Yrv0OLWEfhwZ846zGp_sy6CqTI1DyEcvtyk5gpktmeDrcF6ZYBW1UV4n6TILVWc_gzZ7XnpvoR2uUYBFB_Kefjt8SPPlumEgFaHfHfdnizmJdRE2Z0NY8ogTIy-PU?key=EnulmzUh85Dz0KFPiY6OSg\")
![\"\"\/](\"https:\/\/lh7-qw.googleusercontent.com\/docsz\/AD_4nXcdOVgG5vh3mlZgTqALwJ24cBGDFfOQCrDBxDdgoyjFOdpoYZbBt-S1R2Ra8-EyEB6coLTi3rRnLTFy8_iupyZLEVJ7Q9lLDoEknei6sazdinuMkQQMX-oDZs0AeQROU28BGIicABI_AncElHkJKjXHH7br?key=EnulmzUh85Dz0KFPiY6OSg\")
![\"\"\/](\"https:\/\/lh7-qw.googleusercontent.com\/docsz\/AD_4nXcseSuL0VBUt3VNSP-UCQgcgLxq49ZUaL23EpRXrZhIZ4FDD3PsArri8G5y2I1LaQFNXvn6wuWr1zABEoa03SoKYOBm8ssNGeffoAnbsWoSzYeX2XSZgNP07K_Nt9jey1ZfDCTrp7lSwChfkmKe8bJtKa7E?key=EnulmzUh85Dz0KFPiY6OSg\")
![\"\"\/](\"https:\/\/lh7-qw.googleusercontent.com\/docsz\/AD_4nXf6NogKBnlBTT_9rH7eBtU2hYpcgtM91uGrhk2tgYAS8WlgAtZemSShFdaXHp743dfGXi8MsmX1Mq4pD7eLom5nIGf3hs21toYAZqZPLOsFG7VGd0y-9FDwGBe8_vo99ESiRJc9Vem_rpy1PfHxWqoj1LbP?key=EnulmzUh85Dz0KFPiY6OSg\")
![\"Databricks](\"https:\/\/devoteam.info\/wp-content\/uploads\/2024\/11\/GettyImages-1429384443_edited.jpg\")
<\/a><\/figure>\n\n\t\t\n\t\t- \n
- You should have at least one Unity Catalog-enabled workspace.<\/li>\n\n\n\n
- An account admin should enable system tables through the Unity Catalog REST API.<\/li>\n\n\n\n
- More information on enabling system tables can be found in the official Databricks documentation<\/a>.<\/li>\n\n\n\n
- To query tables with row filters (for RLS), use a cluster with runtime version 12.2 or higher.<\/li>\n<\/ul>\n\n\n\n
Coding Journey<\/h3>\n\n\n\n
Demo by Databricks<\/h4>\n\n\n\n
Here<\/a> you can find the Forecast Billing Demo by Databricks.<\/p>\n\n\n\n
Code and Visualisations<\/h3>\n\n\n\n
Import Necessary SQL Functions<\/h4>\n\n\n\n
from pyspark.sql.functions import sum , col, to_date, month, year, concat_ws, current_date, date_sub, when, lit, add_months, lower, regexp_extract, cast, format_number, avg , count<\/code><\/pre>\n\n\n\nCreate a Temporary View<\/h4>\n\n\n\n
I preferred to use a temporary view, but the same functionality can be achieved through a dataframe for this notebook.<\/p>\n\n\n\n
In a federated architecture, it is highly recommended to use tags, for example, to facilitate cross-charging. In the code, I added ‘department’ as an illustrative tag. You can create your own key-value pairs that align with your business.<\/p>\n\n\n\n
Moreover, I constructed a view layer atop both the billing.usage<\/em> and the compute.clusters<\/em> system tables to enforce row-level security. More information on the views:<\/p>\n\n\n\n
- \n
- vw_clusters<\/strong>
I created this view to enforce row-level security on the compute.clusters <\/em>system table. It is not possible to apply row functions directly on the system tables. That is the only extra layer that this view adds; the columns are the same. This table and view hold information about the clusters within the Databricks account like cluster ID, name, node types, scaling and other settings.<\/li>\n\n\n\n- vw_usage<\/strong>
I created vw_usage, too, to apply row-level security. It links to all the columns from the billing.usage<\/em> system table. It is an hourly fact log on Databricks consumption by SKU name and workspace. Other important columns are usage unit, usage quantity and usage metadata. <\/li>\n\n\n\n- dim_workspace<\/strong>
To be able to refer to the workspace names (instead of just the IDs) in Databricks monitoring dashboards, my colleague Chris created a mapping of those two variants of workspace references. The two columns in this table are workspace ID and workspace name. <\/li>\n<\/ul>\n\n\n\nI added additional explanatory comments in the script.<\/p>\n\n\n\n
%sql\nCREATE OR REPLACE TEMPORARY VIEW vw_cost\nAS\nWITH cte_cluster_tags AS (\n SELECT DISTINCT\n cluster_id\n ,tags.department as department\n ,ROW_NUMBER() OVER (\n PARTITION BY cluster_id\n ORDER BY\n create_time DESC\n ) AS rn -- This numbers rows based on the specified partition. 1 represents the newest tag value.\n FROM\n support.finops.vw_clusters -- View for secured access\n)\nSELECT\n coalesce(ws.workspace_name, ws.workspace_id) as workspace\n ,u.usage_date\n ,u.sku_name\n ,cast(u.usage_quantity AS DOUBLE) AS dbus\n ,cast(lp.pricing.default * usage_quantity AS DOUBLE) AS cost_at_list_price\n ,c.department\nFROM\n support.finops.vw_usage u\nINNER JOIN system.billing.list_prices lp \n ON u.cloud = lp.cloud\n AND u.sku_name = lp.sku_name\n AND u.usage_start_time BETWEEN lp.price_start_time AND COALESCE(lp.price_end_time, '2099-12-31') \nLEFT JOIN cte_cluster_tags c\n ON u.usage_metadata.cluster_id = c.cluster_id\n AND c.rn = 1 -- To make sure that incorrect duplicates are avoided if they ever exist.\nLEFT JOIN support.finops.dim_workspace ws\n ON u.workspace_id = ws.workspace_id\nWHERE\n u.usage_unit = 'DBU'\n AND u.usage_date BETWEEN current_date() - 93 -- 3 months\nAND current_date() -1 -- Not including today, because day is not finished\nORDER BY\n u.usage_date\n ,workspace\n ,u.sku_name<\/code><\/pre>\n\n\n\nPrepare Functions for Multiselect Widgets<\/h4>\n\n\n\n
As the built-in multi-select widget function lacks support for an easy ‘select all’ option, I created a custom function. With that function, you will have one option to select all. Figure 1 provides an example of a widget with an \u2018ALL\u2019 option.<\/p>\n\n\n\n
Figure 1: Widget with \u2018ALL\u2019 option<\/mark><\/em><\/figcaption><\/figure>\n\n\n\n The first function creates a distinct list of values from a certain column. This could be parameterised to a larger extent. <\/p>\n\n\n\n
def cost_col_dist_vals(column_name):\n distinct_values_df = spark.sql(f\"select distinct coalesce({column_name}, 'NULL') as {column_name} from vw_cost order by {column_name}\") # Query or view\/table name could be a parameter\n distinct_values = [row[column_name] for row in distinct_values_df.select(column_name).collect()] # convert df column to list\n\n\n return distinct_values\n\n\ndef create_multi_widget(distinct_values, label, default_choice='ALL'):\n if default_choice == 'ALL':\n distinct_values.insert(0,'ALL') # insert ALL option at the top\n \n multi_widget = dbutils.widgets.multiselect(label, default_choice, distinct_values)\n\n\n return multi_widget\n\n\ndef get_multiselect_vals(distinct_values, label):\n if getArgument(label) == 'ALL' or 'ALL' in getArgument(label): # if 'ALL' is selected plus one or more other options, \u2018ALL\u2019 overrides\n selected_values = distinct_values\n if isinstance(selected_values, list):\n selected_values = ','.join(map(\"'{0}'\".format, selected_values)) # convert to string\n elif getArgument(label).count(',') == 0: # in case 0 or 1 option(s) are selected \n selected_values = [getArgument(label),'dummy'] # add dummy value so IN statement does not break\n if isinstance(selected_values, list):\n selected_values = ','.join(map(\"'{0}'\".format, selected_values)) # convert to string\n else:\n selected_values = getArgument(label)\n if isinstance(selected_values, list):\n selected_values = ','.join(map(\"'{0}'\".format, selected_values)) # convert to string\n else: # if it\u2019s a string without quotes around the options\n selected_values = \"'\" + selected_values.replace(\",\", \"','\") + \"'\" \n\n\n return selected_values\n<\/code><\/pre>\n\n\n\nGet Distinct Values for Multiselect Filter and Create Widgets<\/h4>\n\n\n\n
# Get distinct values for multi-select widgets\ndist_departments = cost_col_dist_vals('department')\ndist_sku_names = cost_col_dist_vals('sku_name')\ndist_workspaces = cost_col_dist_vals('workspace')\n\n\n# Create widgets\ncreate_widget_department = create_multi_widget(dist_departments, 'Department')\ncreate_widget_sku_names = create_multi_widget(dist_sku_names, 'SKU Name')\ncreate_widget_workspaces = create_multi_widget(dist_workspaces, 'Workspace')<\/code><\/pre>\n\n\n\nInstall Prophet<\/h4>\n\n\n\n
!pip install prophet<\/code><\/pre>\n\n\n\nProphet is a time-series forecasting model developed by Facebook. <\/p>\n\n\n\n
Prepare Forecast Functions<\/h4>\n\n\n\n
This section has a couple of fundamental differences compared to the demo code.<\/p>\n\n\n\n
- \n
- Model type<\/strong>
No model is specified in the demo notebook, meaning the default linear model will be used (source: Prophet documentation<\/a>). With the linear model and a downward slope, you will eventually see negative predicted values (if the forecast period is long enough). In the current use case, it is impossible to have negative Databricks usage and thus negative cost. The Prophet documentation suggests that a logistic model, cap, and floor be specified. If a floor is set, a cap must also be in place.
<\/li>\n\n\n\n- Minimum training dataset size
<\/strong>The minimum number of training dataset records in the demo notebook is 10. The dataset here is workspace_hist_df. However, I noticed predictions that deviated largely from the actual trend. This is why I increased the minimum to 30, which I noticed is still too limited in some cases. You can play around with this, also with the workspace filter because this can affect the number of data points. In Figure 1, you can see that values above \u20ac500 are predicted, while the actual values only reach between \u20ac0 and \u20ac50.<\/li>\n<\/ul>\n\n\n\nFigure 2: Improbable Predictions<\/mark><\/em><\/figcaption><\/figure>\n\n\n\n - \n
- Country holidays
<\/strong>Prophet considers holidays in its forecasts. The country specified in the demo is US. However, since most of the people who use this Databricks account at my company are working in the Netherlands, I used \u2018NL\u2019.<\/li>\n<\/ul>\n\n\n\n- \n
- Do not show cap in the y-axis range of the graph<\/strong>
I added plot_cap = False as the function’s default setting. I used a cap value of 1,000,000, which is a multiple of the maximum actual daily cost that will likely never be achieved. If I had included the cap plot in the y-axis range, the actual and forecast fluctuations would be less visible.<\/li>\n<\/ul>\n\n\n\nfrom prophet import Prophet\n \n#Predict days, for the next 3 months\nforecast_frequency='d'\nforecast_periods=31*3\n \ninterval_width=0.8\ninclude_history=True\n \ndef generate_forecast(history_df, growth = 'logistic', convert_to_pd = False, display_graph = True):\n # convert to pandas df\n if convert_to_pd:\n history_pd = history_df.toPandas()\n else:\n history_pd = history_df\n # drop missing values\n history_pd = history_pd.dropna()\n if history_pd.shape[0] > 30:\n # train and configure the model\n model = Prophet(interval_width=interval_width, growth = growth) # for forecast values with cap (and floor) (source: https:\/\/facebook.github.io\/prophet\/docs\/saturating_forecasts.html)\n model.add_country_holidays(country_name='NL')\n model.fit(history_pd)\n \n # make predictions\n future_pd = model.make_future_dataframe(periods=forecast_periods, freq=forecast_frequency, include_history=include_history)\n\n\n if 'cap' in history_pd.columns:\n future_pd['cap'] = history_pd['cap'].iloc[0]\n \n if 'floor' in history_pd.columns:\n future_pd['floor'] = history_pd['floor'].iloc[0]\n\n\n forecast_pd = model.predict(future_pd)\n \n if display_graph:\n model.plot(forecast_pd, plot_cap = False, include_legend=True)\n # add back y to the history dataset \n f_pd = forecast_pd[['ds', 'yhat', 'yhat_upper', 'yhat_lower']].set_index('ds')\n # join history and forecast\n results_pd = f_pd.join(history_pd[['ds','y']].set_index('ds'), how='left')\n results_pd.reset_index(level=0, inplace=True)\n results_pd['ds'] = results_pd['ds'].dt.date\n else:\n # not enough data to predict, return history\n for c in ['yhat', 'yhat_upper', 'yhat_lower']:\n history_pd[c] = history_pd['y']\n results_pd = history_pd[['ds','y','yhat', 'yhat_upper', 'yhat_lower']]\n return results_pd<\/code><\/pre>\n\n\n\nCreate Forecast Visual<\/h4>\n\n\n\n
Since Prophet\u2019s documentation indicates that the input into the main functions should always be a data frame with two columns: ds (date) and y (output variable used for training), I adhered to this advice. I grouped the workspace-filtered query by date (ds). Additionally, Databricks trains an additional model for each independent variable. However, the information about adding variables is quite limited. Due to that and the fact that it would require additional effort to manage more models, especially considering the combination of filters, I skipped the addition of variables.<\/p>\n\n\n\n
The drawback is that the model does not take into account variation in the other variables. On the other hand, this blog describes a specialized model with filter functionalities that perform well in terms of predicted values.<\/p>\n\n\n\n
Error handling for empty datasets is implemented. It ensures that the output of the cell displays the following if there are no records: \u201cPlease verify if you have a workspace selected in the filter widget. Interrupt and re-run the dashboard if you make multiple selection changes.\u201d<\/p>\n\n\n\n
# Retrieve selected workspace_ids\nselected_workspaces = get_multiselect_vals(dist_workspaces, 'Workspace') \n\n\n# vw_cost is limited by the last three months\nworkspace_hist_df = spark.sql(f\"\"\"\n select \n usage_date as ds\n ,sum(cost_at_list_price) as y\n from vw_cost\n where workspace in ({selected_workspaces})\n group by ds\n order by ds\n \"\"\")\n\n\n# Specify cap and floor (source: https:\/\/facebook.github.io\/prophet\/docs\/saturating_forecasts.html#saturating-minimum)\nworkspace_hist_df = workspace_hist_df.toPandas()\nworkspace_hist_df['cap'] = 1000000\nworkspace_hist_df['floor'] = 0\n\n\nresults_pd = generate_forecast(workspace_hist_df, display_graph = False)\n\n\n# Although the floor is set to 0, predicted values reach below 0, so negative values are converted to 0\nzero_columns_to_convert = ['yhat', 'yhat_upper', 'yhat_lower']\nresults_pd[zero_columns_to_convert] = results_pd[zero_columns_to_convert].applymap(lambda x: max(0, x))\n\n\n# Set up date filter widgets\nfrom datetime import datetime, date\n\n\nif len(results_pd) == 0:\n import pandas as pd\n error_message = ['Please verify if you have a workspace selected in the filter widget. Interrupt and re-run the dashboard, if the you make multiple selection changes.']\n error_df = pd.DataFrame()\n error_df['Error Message'] = error_message\n display(error_df)\n\n\nelse:\n## Define default date values\n default_min_date = str(min(results_pd['ds'])) # results pd is a pandas df\n default_max_date = str(max(results_pd['ds'])) # results pd is a pandas df\n\n\n ## Define all widget options\n distinct_dates = results_pd['ds'].astype(str).unique() # retrieve distinct dates and convert to string for readability\n\n\n ## Create widgets. This order was applied, because the most recently created widget is created on the left of existing widgets. You can change widget order in the widgets pane (at the top of the notebook)\n dbutils.widgets.dropdown('Minimum Date', default_min_date , distinct_dates)\n dbutils.widgets.dropdown('Maximum Date', default_max_date , distinct_dates)\n\n\n ## Retrieve selected values from date widgets\n min_date = date.fromisoformat(getArgument('Minimum Date'))\n max_date = date.fromisoformat(getArgument('Maximum Date'))\n\n\n ## Filter results_pd by date\n filtered_results_pd = results_pd[(results_pd['ds'] >= min_date) & (results_pd['ds'] <= max_date)]\n\n\n # Create the visualization\n import plotly.graph_objects as go\n\n\n fig = go.Figure()\n fig.add_trace(go.Scatter(x=filtered_results_pd['ds'], y=filtered_results_pd['y'][:-4], name='actual usage'))\n fig.add_trace(go.Scatter(x=filtered_results_pd['ds'], y=filtered_results_pd['yhat'], name='forecast cost (pricing list)'))\n fig.add_trace(go.Scatter(x=filtered_results_pd['ds'], y=filtered_results_pd['yhat_upper'], name='uncertainty interval upper bound', line = dict(color='grey', width=1, dash='dot'))) # renamed this (compared to demo) for more clarity\n fig.add_trace(go.Scatter(x=filtered_results_pd['ds'], y=filtered_results_pd['yhat_lower'], name='uncertainty interval lower bound', line = dict(color='grey', width=1, dash='dot'))) # renamed this (compared to demo) for more clarity\n fig.update_layout(margin_b=15,margin_t=10) # to make it look better in the dashboard\n fig.show()<\/code><\/pre>\n\n\n\nThe following image (Figure 3) contains an example output.
<\/p>\n\n\n\nFigure 3: Visualisation \u2018Historical and Forecast Cost\u2019<\/em>
<\/mark><\/figcaption><\/figure>\n\n\n\nCreate Daily Cost by SKU<\/h4>\n\n\n\n
The last two visuals do not include forecasts, just like in the original demo. These visualisations differ from those in the demo and provide cost and SKU distribution information.<\/p>\n\n\n\n
- \n
- Daily Cost by SKU Family (Actual)<\/strong>
As an extension of the actual line in the forecast graph, I zoomed in on the cost by SKU over time (Figure 4). Since the SKU_name column would provide unreadable categories, I used the SKU mapping from the demo and referred to this as SKU Family.<\/li>\n<\/ul>\n\n\n\nFigure 4: Visualisation \u2018Daily Cost by SKU Family (Actual)\u2019<\/mark><\/em><\/figcaption><\/figure>\n\n\n\n - \n
- Total Cost by SKU Name (Actual)<\/strong>
To still be able to see the SKU name-specific cost distribution, users can study below visualization (Figure 5). To inspect the distribution for a specific date (range), people can adjust the minimum and maximum date filters.<\/li>\n<\/ul>\n\n\n\nFigure 5: Visualisation \u2018Total Cost by SKU Name (Actual)\u2019<\/mark><\/em><\/figcaption><\/figure>\n\n\n\n With the following code cell, I retrieve the filter values and insert them into the query string. For this reason, I use a data frame instead of SQL.
<\/p>\n\n\n\n# Get filter values\nselected_departments = get_multiselect_vals(dist_departments, 'Department')\nselected_sku_names = get_multiselect_vals(dist_sku_names, 'SKU Name')\nselected_workspaces = get_multiselect_vals(dist_workspaces, 'Workspace') # included in this cell enforce cell run, if widget selection changes\nmin_date_str = getArgument('Minimum Date') # included in this cell enforce cell run, if widget selection changes\nmax_date_str = getArgument('Maximum Date') # included in this cell enforce cell run, if widget selection changes\n\n\ncost_by_sku_family_df = spark.sql(f\"\"\" \n select \n sub.usage_date as `Date`\n ,sub.sku_name as `SKU Name`\n ,CASE WHEN sub.sku_name LIKE \"%ALL_PURPOSE%\" THEN \"ALL_PURPOSE\"\n WHEN sub.sku_name LIKE \"%JOBS%\" THEN \"JOBS\"\n WHEN sub.sku_name LIKE \"%DLT%\" THEN \"DLT\"\n WHEN sub.sku_name LIKE \"%SQL%\" THEN \"SQL\"\n WHEN sub.sku_name LIKE \"%INFERENCE%\" THEN \"MODEL_INFERENCE\"\n ELSE \"OTHER\" \n END AS `SKU Family`\n ,sum(sub.cost_at_list_price) as `Cost (List Prices)` -- based on the list_prices table, which does not include special price arrangements with Databricks\n from (\n select \n workspace\n ,usage_date\n ,sku_name\n ,cost_at_list_price\n ,coalesce(b.`department`, 'NULL') as `department`\n FROM vw_cost b\n ) sub\n where sub.workspace in ({selected_workspaces})\n and sub.usage_date between '{min_date_str}' and '{max_date_str}'\n and sub.sku_name in ({selected_sku_names})\n and sub.`department` in ({selected_departments})\n group by\n sub.usage_date\n ,sub.sku_name\n ,`SKU Family`\n\"\"\")\n\n\ndisplay(cost_by_sku_family_df)\n<\/code><\/pre>\n\n\n\nOmitted Elements<\/h4>\n\n\n\n
- \n
- billing_forecast <\/strong>table<\/strong>
I did not create a table or view to keep the catalog clean. In the demo, a table is created and then accessed to create the graph, but it worked well in terms of functionality without the table. <\/li>\n\n\n\n- detailed_billing_forecast view
I did not want to \u201csmoothen\u201d the data, as mentioned in the demo. I intended to display the data as it is.<\/li>\n<\/ul>\n\n\n\nNotebook Settings<\/h4>\n\n\n\n
- \n
- When filter input is changed, only commands with the getArgument() function of the changed widget are run. <\/li>\n\n\n\n
- The default notebook language is Python.<\/li>\n<\/ul>\n\n\n\n
Limitations and Opportunities for Improvement<\/h4>\n\n\n\n
Given that the Prophet documentation states that training and testing are automated, I did not spend effort tweaking the settings. This is still an opportunity to improve the model. Additionally, the model accuracy could be improved by adding regressors or training separate models per additional variable, although the latter option is not recommended in the official Prophet documentation. Another opportunity that I mentioned earlier is to improve model accuracy by specifying a larger minimum historical dataset.<\/p>\n\n\n\n
Dashboard<\/h4>\n\n\n\n
Below, you can see the final dashboard (Figure 6). The dashboard can be utilized to support decision-making related to budgeting and FinOps<\/p>\n\n\n\n
Figure 6: \u2018Cost Over Time and Forecast\u2019 Dashboard<\/mark><\/em><\/figcaption><\/figure>\n\n\n\n In Summary <\/h3>\n\n\n\n
- detailed_billing_forecast view
- billing_forecast <\/strong>table<\/strong>
- Total Cost by SKU Name (Actual)<\/strong>
- Daily Cost by SKU Family (Actual)<\/strong>
- Do not show cap in the y-axis range of the graph<\/strong>
- Minimum training dataset size
- vw_usage<\/strong>
- To query tables with row filters (for RLS), use a cluster with runtime version 12.2 or higher.<\/li>\n<\/ul>\n\n\n\n