{ "cells": [ { "cell_type": "code", "execution_count": null, "id": "5bb1770b", "metadata": { "vscode": { "languageId": "plaintext" } }, "outputs": [], "source": [ "import sempy.fabric as fabric\n", "from datetime import datetime, timedelta\n", "import pandas as pd\n", "import requests \n", "\n", "def generate_dax_background_operation(date_today, capacity_id):\n", " \"\"\"\n", " Generate the DAX statement which is used to get the background operations of the Metrics App for a given Capacity and day. \n", " \n", " Arguments required: \n", " date_today (datetime) - Date on which the background operation should be extracted\n", " capacity_id (string) - Capacity ID on which the background operation should be extracted\n", "\n", " Returns:\n", " DAX Statement (string)\n", "\n", " \"\"\"\n", "\n", " #timepoint_start = date_today.replace(hour=0, minute=0, second=0, microsecond=0) #Set timepoint to the beginning of the day\n", " timepoint_start = date_today.replace(day=17, month=5, year=2025, hour=7, minute=58, second=30, microsecond=00) #Use this timepoint to get a specific one - used for testing purpose\n", " timepoint_next = timepoint_start\n", " i = 0 #Initialising iteration count to check if all timepoints (2880 in total for a day) has been covered\n", " all_results = []\n", " #while timepoint_next.day == timepoint_start.day: #As long as the day of the next timepoint is the same as start timepiont, loop will continue and add 30seconds at the end\n", " while timepoint_next <= datetime.strptime('17.05.2025 08:02:00', \"%d.%m.%Y %H:%M:%S\"): #Use this filter to get some specific timepoints only - used for testing purpose\n", " current_year = str(timepoint_next.year)\n", " current_month = str(timepoint_next.month)\n", " current_day = str(timepoint_next.day)\n", " starting_hour = str(timepoint_next.hour)\n", " starting_minutes = str(timepoint_next.minute)\n", " starting_seconds = str(timepoint_next.second)\n", "\n", " dax_background_operation = '''\n", " DEFINE\n", " MPARAMETER 'CapacityID' = \"''' + capacity_id + '''\"\n", " MPARAMETER 'TimePoint' = (DATE(''' + current_year + ''', ''' + current_month + ''', ''' + current_day + ''') + TIME(''' + starting_hour + ''', ''' + starting_minutes + ''', ''' + starting_seconds + '''))\n", "\n", " VAR varFilter_Capacity = TREATAS({\"''' + capacity_id + '''\"}, 'Capacities'[capacityId])\t\n", " VAR varFilter_TimePoint = \n", " TREATAS(\n", " {(DATE(''' + current_year + ''', ''' + current_month + ''', ''' + current_day + ''') + TIME(''' + starting_hour + ''', ''' + starting_minutes + ''', ''' + starting_seconds + '''))},\n", " 'TimePoints'[TimePoint]\n", " )\t\t\n", " VAR varTable_Details =\n", " SUMMARIZECOLUMNS(\n", " 'TimePointBackgroundDetail'[OperationStartTime],\n", " 'TimePointBackgroundDetail'[OperationEndTime],\n", " 'TimePointBackgroundDetail'[Status],\n", " 'TimePointBackgroundDetail'[Operation],\n", " 'TimePointBackgroundDetail'[User],\n", " 'TimePointBackgroundDetail'[OperationId],\n", " 'TimePointBackgroundDetail'[Billing type],\n", " 'Items'[WorkspaceName],\n", " 'Items'[ItemKind],\n", " 'Items'[ItemName],\n", " \n", " varFilter_Capacity,\n", " varFilter_TimePoint,\n", " \n", " \"Timepoint CU (s)\", SUM('TimePointBackgroundDetail'[Timepoint CU (s)]),\n", " \"Duration (s)\", SUM('TimePointBackgroundDetail'[Duration (s)]),\n", " \"Total CU (s)\", CALCULATE(SUM('TimePointBackgroundDetail'[Total CU (s)])),\n", " \"Throttling\", CALCULATE(SUM('TimePointBackgroundDetail'[Throttling (s)])),\n", " \"% of Base Capacity\", CALCULATE(SUM('TimePointBackgroundDetail'[% of Base Capacity]))\n", " )\n", " \n", " EVALUATE SELECTCOLUMNS(\n", " varTable_Details,\n", " \"BillingType\", [Billing type],\n", " \"Status\", [Status],\n", " \"OperationStartTime\", [OperationStartTime],\n", " \"OperationEndTime\", [OperationEndTime],\n", " \"User\", [User],\n", " \"Operation\", [Operation],\n", " \"OperationID\", [OperationId],\n", " \"WorkspaceName\", [WorkspaceName],\n", " \"Item\", [ItemKind],\n", " \"ItemName\", [ItemName],\n", " \"TimepointCUs\", [Timepoint CU (s)],\n", " \"DurationInS\", [Duration (s)],\n", " \"TotalCUInS\", [Total CU (s)],\n", " \"Throttling\", [Throttling],\n", " \"PercentageOfBaseCapacity\", [% of Base Capacity]\t\n", " )'''\n", " \n", " df_dax_result = fabric.evaluate_dax(\n", " dataset,\n", " dax_background_operation\n", " )\n", " row_count = len(df_dax_result)\n", " display(f\"DAX query executed for capacity ID: {capacity_id} on {timepoint_start.date()} — Rows returned: {row_count}\")\n", " if not df_dax_result.empty:\n", " \n", " df_dax_result['capacityId'] = capacity_id\n", " df_dax_result['Timepoint'] = timepoint_next\n", " df_dax_result['OperationType'] = 'background'\n", " display(df_dax_result)\n", " all_results.append(df_dax_result)\n", "\n", " \n", " \n", " #Don't change timepoint intervals, as 30sec intervals are given\n", " timepoint_next = timepoint_next + timedelta(seconds = 30)\n", "\n", " i = i + 1\n", "\n", " if all_results:\n", " return pd.concat(all_results, ignore_index=True)\n", " else:\n", " return pd.DataFrame()\n", "\n", "dataset = 'Fabric Capacity Metrics'\n", "start_time = datetime(datetime.today().year, datetime.today().month, 1)\n", "azure_function_url = \"https://.azurewebsites.net/api/?code=\"\n", "\n", "df_table = fabric.read_table(dataset, \"Capacities\")\n", "\n", "for idx, row in df_table.iterrows():\n", " capacity_id = row[\"capacityId\"] \n", "\n", " try:\n", " df_result = generate_dax_background_operation(start_time, capacity_id)\n", " \n", " if not df_result.empty:\n", " json_data = df_result.to_dict(orient=\"records\")\n", " response = requests.post(azure_function_url, json=json_data)\n", "\n", " # Optional: log success/failure\n", " if response.status_code == 200:\n", " print(f\"Data sent for capacity {capacity_id}\")\n", " else:\n", " print(f\"Failed to send for {capacity_id}: {response.status_code} {response.text}\")\n", " else:\n", " print(f\"No data returned for capacity {capacity_id}\")\n", " \n", " except Exception as e:\n", " print(f\"Error processing capacity {capacity_id}: {e}\")" ] } ], "metadata": { "language_info": { "name": "python" } }, "nbformat": 4, "nbformat_minor": 5 }